第二章、SQL
第一节、模式
sql>CREATEUSER"tpc"WITHPASSWORD'tpc'NAME'TPCExplorer'SCHEMA"sys";
sql>CREATESCHEMA"tpc"AUTHORIZATION"tpc";
sql>ALTERUSER"tpc"SETSCHEMA"tpc";
第二节、数据类型和表
1、原始数据类型
2、建表示例
sql>CREATETABLEtest(
more>idint,
more>datavarchar(30)
more>);
sql>\dtest
CREATETABLE"voc"."test"(
"id"int,
"data"varchar(30)
);
第三节、函数
基本与Postgresql兼容,只是类型转换不一样。
1、类型转换函数
str_to_date(sstring,formatstring)
date_to_str(ddate,formatstring)
第四节、加载数据
1、普通装载
方式一:
直接使用Insertinto,可以通过STARTTRANSACTION和COMMIT减少事物提交。
这种方式因为每次查询都是独立的,所以每次只能使用到一个CPU核。
方式二:
COPYINTO
COPYINTOTABLEFROM‘FILE’;
大量数据插入式,server不知道需要分配多少内存,因此只会分配很少,也就是在插入过程中,需要不停的分配内存,这个开销会非常大。
因此,最好能给定一个值多少条记录会被插入。
COPYnRECORDSINTOtableFROM‘file’
N必须比实际插入的数字要大,如果文件实际的值大于N,只会有N条记录会被插入。
在同一表同时有多个COPYINTO查询,给一个更大的值会非常有效。
offset值指定数据加载开始位置,第一条记录offset为1
完整性约束最好在文件被加载完了之后再添加,因为ALTER命令是批检查和处理,性能会更好。
2、导出
COPYINTO命令把表dump成一个ASCII文件。
导入导出可以指定gz和bz2的压缩算法。
3、二进制批加载
COPY命令,性能主要消耗在将ASCII值转化为二进制,MONETDB针对多核进行了高度优化,多个线程会并行处理。
用户直接根据BAT模型,生成二进制文件。
createtableTmp(iinteger,freal,sstring);
copybinaryintoTmpfrom('path_to_file_i','path_to_file_f','path_to_file_s');
文件名是列明的绝对路径,这个路径需要和farm同样的文件系统。
他们会直接替换TMP的内容。
文件被拷贝完了之后,原来的空间就可以被回收利用。
每个文件直接用二进制表示,是一个C语言数组的DUMP。
Char(1byte)tinyint(8-bits)smallint(16bits)int(32bits)bigint(64bit)
Real和double映射C语言的float和double类型。
可变字符串,文件中存放的对应的C语言的字符串,每行通过分割符分割,并且没有转义字符。
所有文件需要对其,有多个值在文件中,表中就有多少条记录。
其他的类型(包括UTF-8和转义字符)必须要用COYPINTO加载。
第五节、JDBC
1、驱动
JDBC下载:
http:
//dev.monetdb.org/downloads/Java/Latest/
2、加载驱动
//makesuretheClassLoaderhastheMonetDBJDBCdriverloaded
Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
//requestaConnectiontoaMonetDBserverrunningon'localhost'
Connectioncon=DriverManager.getConnection("jdbc:
monetdb:
//localhost/database","monetdb","monetdb");
3、示例
importjava.sql.*;
publicclassMJDBCTest{
publicstaticvoidmain(String[]args)throwsException{
//makesurethedriverisloaded
Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
Connectioncon=DriverManager.getConnection("jdbc:
monetdb:
//localhost/database","monetdb","monetdb");
Statementst=con.createStatement();
ResultSetrs;
rs=st.executeQuery("SELECTa.var1,COUNT(b.id)astotalFROMa,bWHEREa.var1=b.idANDa.var1='andb'GROUPBYa.var1ORDERBYa.var1,total;");
//getmetadataandprintcolumnswiththeirtype
ResultSetMetaDatamd=rs.getMetaData();
for(inti=1;i<=md.getColumnCount();i++){
System.out.print(md.getColumnName(i)+":
"+
md.getColumnTypeName(i)+"\t");
}
System.out.println("");
for(inti=0;rs.next()&&i<5;i++){
for(intj=1;j<=md.getColumnCount();j++){
System.out.print(rs.getString(j)+"\t");
}
System.out.println("");
}
//tellthedrivertoonlyreturn5rows,itcanoptimizeonthis
//value,andwillnotfetchanymorethan5rows.
st.setMaxRows(5);
//weaskthedatabasefor22rows,whilewesettheJDBCdriverto
//5rows,thisshouldn'tbeaproblematall...
rs=st.executeQuery("select*fromalimit22");
//readtillthedriversaystherearenorowsleft
for(inti=0;rs.next();i++){
System.out.print("["+rs.getString("var1")+"]");
System.out.print("["+rs.getString("var2")+"]");
System.out.print("["+rs.getInt("var3")+"]");
System.out.println("["+rs.getString("var4")+"]");
}
//unsettherowlimit;0meansasmuchasthedatabasesendsus
st.setMaxRows(0);
//weonlyask10rows
rs=st.executeQuery("select*fromblimit10;");
//andsimplyprintthem
while(rs.next()){
System.out.print(rs.getInt("rowid")+",");
System.out.print(rs.getString("id")+",");
System.out.print(rs.getInt("var1")+",");
System.out.print(rs.getInt("var2")+",");
System.out.print(rs.getString("var3")+",");
System.out.println(rs.getString("var4"));
}
st.executeUpdate("deletefromawherevar1='zzzz'");
con.close();
}
}
第六节、语法树
详细内容参见:
“http:
//www.monetdb.org/Documentation/Manuals/SQLreference/DataDefinition”
第三章、基础管理
第一节、执行计划
可以用Explain语句查看SQL编译器产生的中间代码。
它给出了详细处理过程的动作描述。
下面是展示的是一个例子。
输出依赖于优化器的设置。
sql>selectcount(*)fromtables;
sql>explainselectcount(*)fromtables;
+---------------------------------------------------------------------------------------------+
|mal|
+=============================================================================================+
|functionuser.s3_2{autoCommit=true}():
void;|
|_2:
=sql.mvc();|
|barrier_143:
=language.dataflow();|
|_23:
bat[:
oid,:
sht]:
=sql.bind(_2,"sys","_tables","type",1);|
|_24:
=algebra.thetauselect(_23,2:
sht,"<");|
|_25:
bat[:
oid,:
oid]:
=sql.bind_dbat(_2,"sys","_tables",1);|
|_27:
=bat.reverse(_25);|
|_97:
=algebra.kdifference(_24,_27);|
|_110:
=algebra.markT(_97,5,4);|
|_117:
=bat.reverse(_110);|
|_30:
bat[:
oid,:
int]:
=sql.bind(_2,"sys","_tables","id",1);|
|_142:
=algebra.leftjoin(_117,_30);|
|_64:
bat[:
oid,:
sht]:
=sql.bind(_2,"sys","_tables","type",0,27@0,nil:
oid);|
|_72:
=algebra.thetauselect(_64,2:
sht,"<");|
|_20:
bat[:
oid,:
sht]:
=sql.bind(_2,"sys","_tables","type",2);|
|_76:
=algebra.kdifference(_72,_20);|
|_22:
=algebra.thetauselect(_20,2:
sht,"<");|
|_80:
=algebra.semijoin(_22,_64);|
|_85:
=algebra.kunion(_76,_80);|
...
|_13:
=algebra.kdifference(_8,_12);|
|_14:
=algebra.markT(_13,0@0);|
|_15:
=bat.reverse(_14);|
|_16:
bat[:
oid,:
int]:
=sql.bind(_2,"tmp","_tables","id",0);|
|_18:
=algebra.leftjoin(_15,_16);|
|exit_143;|
|_33:
bat[:
oid,:
int]:
=bat.new(nil:
oid,nil:
int);|
|barrier_146:
=language.dataflow();|
|_32:
=mat.pack(_134,_136,_138,_140,_142);|
|_36:
=bat.append(_33,_32,true);|
|_38:
=bat.append(_36,_18,true);|
|_39:
=aggr.count(_38);|
|exit_146;|
|sql.exportValue(1,".tables","L6","wrd",64,0,6,_39,"");|
|ends3_2;|
+---------------------------------------------------------------------------------------------+
86tuples
sql>
第二节、数据字典
参见:
http:
//www.monetdb.org/Documentation/SQLcatalog/TablesColumns
第三节、监控数据库活动
1、正在执行的任务
select*fromsys.queue();
第四节、监控存储使用
磁盘空间可以通过du命令查看dbfarm目录或者数据库中执行select*fromstorage();
第五节、备份还原
1、SQL转储
SQL转储是对数据库镜像或者迁移的常用的方法。
如在两个版本之间。
SQLdump是ASCII编码的SQL的集合。
执行这些脚本,可以重新创建一样的数据库。
MonetDB不提供全局锁,并发情况下这个转储可能无效。
shell>mclient-lsql--database=voc--dump>/tmp/voc.sql
可以检察/tmp/voc.sql,确认它是可读的。
如果空间是问题,可以用管道把输出给一个压缩工具,或者使用linux工具直接发送到另外一台机器,移动数据文件到新机器。
MonetDB可以用于创建数据库。
shell>mclient-lsql--database=voc/tmp/voc.sql
2、快速备份
shell>monetdbstopdemo
shell>monetdblockdemo
shell>monetdbreleasedemo
第四章、技巧
第一节、单步调试执行
SQL语句被翻译成为Mal程序,优化存储为用户模块。
代码可以用MAL_debugger跟踪,以下是一个调试的例子。
>debugselectcount(*)fromtables;
#mdb.start()
mdb>next
#user.s1_0()
mdb>next
#_2:
bat[:
oid,:
int]:
=sql.bind(_3="sys",_4="ptables",_5="id",_6=0)
mdb>next
#_8:
bat[:
oid,:
int]:
=sql.bind(_3="sys",_4="ptables",_5="id",_9=1)
mdb>...
.
第二节、集群
第三节、索引
支持标准SQL的索引创建,但是不起作用,MonetDB会自动创建和维护索引。
外键约束会内部自动创建索引。
尽量创建外键约束,外联结时有1倍以上性能提高。
第四节、事务
显示事物:
STARTTRANSACTION
COMMIT
ROOLBACK。
也可以将session参数设置为auto_commit为true,这样单SQL会是一个独立的事物。
一行被删除,只是标记一下,不会降低表的大小,需要通过回收算法回收。
事物使用的是乐观并发控制:
Optimisticconcurrencycontrol,提交前,每个事物检测没有其他事物修改数据,如果检查到了冲突修改,提交的事物就被回滚。