目录
达梦数据库,表和聚集索引一体两面。整个表就是一个大的组合索引,这个索引包含表中所有字段,按照索引键排序。建表时指定聚集索引键可以明显提高以它为条件的查询速度。但同时也会增加写入时间。这需要权衡。当根据条件搜索的数据占比非常少时(比如千分之一)推荐建立普通B+树索引,虽然无论任何时候SSEK2都比不上CSEK2更快,但写入代价较低。当定位的数据较多时推荐建立聚集索引。只能有一个聚集索引,选择查询频率最高的字段作为索引键。测试如下:
1、创建测试表
create table LIU.T1
( A number,
B INT,
C VARCHAR(10),
D VARCHAR(10),
E VARCHAR(10)
);
当建表语句未指定时,DM 的默认聚集索引键是逻辑ROWID,即记录默认以 ROWID在页面中排序。ROWID 是逻辑递增序号,最新插入的记录 ROWID 最大。
2、未指定聚集索引键
2.1 未建普通索引时
2.1.1查询表和索引定义
SQL> (select 'INDEX',a.index_name,index_type,column_name,bytes/1024/1024
2 from dba_segments b,dba_indexes a,dba_ind_columns c
3 where a.table_name='T1' and a.index_name=segment_name(+)
and a.index_name=c.index_name(+)
4 limit 2)
5 union all
6 select 'TABLE',segment_name,NULL,NULL,bytes/1024/1024 from dba_segments where segment_name='T1'
7 /
行号 'INDEX' INDEX_NAME INDEX_TYPE COLUMN_NAME BYTES/1024/1024
---------- ------- ------------- ---------- ----------- --------------------
1 INDEX INDEX33560546 CLUSTER NULL NULL
2 TABLE T1 NULL NULL 0
可见表T1表上只有一个cluster类型的索引,索引键是逻辑rowid,所以column_name显示为空。
2.1.2加载数据
SQL> set autotrace off
SQL> insert into liu.t1
2 select DBMS_RANDOM.value(0,100),10000,'test111','test222','test333'
3 from dual connect by level <=10000000
4 /
影响行数 10000000
已用时间: 00:00:52.135. 执行号:916.
加载表,第一列是0到100之间的随机数。共用时52秒。
查询表和索引定义:
行号 'INDEX' INDEX_NAME INDEX_TYPE COLUMN_NAME BYTES/1024/1024
---------- ------- ------------- ---------- ----------- --------------------
1 INDEX INDEX33560546 CLUSTER NULL NULL
2 TABLE T1 NULL NULL 611
2.1.3查询数据(1%)
SQL> set autotrace trace
SQL> select count(c) from liu.t1 where a<=1;
行号 COUNT(C)
---------- --------------------
1 99547
1 #NSET2: [1304, 1->1, 78]
2 #PRJT2: [1304, 1->1, 78]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [1304, 1->1, 78]; grp_num(0), sfun_num(1)
4 #SLCT2: [1304, 99947->99547, 78];
5 #CSCN2: [1304, 10000000->10000000, 78]; INDEX33560546(T1)
已用时间: 00:00:01.016. 执行号:950.
执行计划访问路径为CSCN2聚集索引全扫描(CLUSTER SCAN),用时1.016秒。
2.1.4查询数据(10%)
SQL> select count(c) from liu.t1 where a<=10;
行号 COUNT(C)
---------- --------------------
1 1000104
1 #NSET2: [1304, 1->1, 78]
2 #PRJT2: [1304, 1->1, 78]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [1304, 1->1, 78]; grp_num(0), sfun_num(1)
4 #SLCT2: [1304, 999870->1000104, 78];
5 #CSCN2: [1304, 10000000->10000000, 78]; INDEX33560546(T1)
已用时间: 00:00:01.066. 执行号:951.
用时1.066秒,跟查询1%时相差无几。因为同样是全索引扫描。不管是查询多少行数据,都是从聚集索引的最左边扫描到最右边,每一行数据再根据查询条件筛选(SLCT2)。
达梦里的CSCN2相当于ORACLE里的全表扫描。达梦里默认创建的是聚集索引组织的表。
2.2 建立普通索引
2.2.1条件字段上建立B+树索引
先truncate表数据,在a字段上创建索引。
SQL> truncate table liu.t1;
操作已执行
已用时间: 4.407(毫秒). 执行号:922.
SQL> create index ind_a on liu.t1(a);
操作已执行
已用时间: 10.300(毫秒). 执行号:923.
2.2.2 加载数据
加载数据
SQL> insert into liu.t1
2 select DBMS_RANDOM.value(0,100),10000,'test111','test222','test333'
3 from dual connect by level <=10000000
4 /
影响行数 10000000
已用时间: 00:04:32.916. 执行号:927.
共用时4分32秒。
查询表和索引定义:
行号 'INDEX' INDEX_NAME INDEX_TYPE COLUMN_NAME BYTES/1024/1024
---------- ------- ------------- ---------- ----------- --------------------
1 INDEX IND_A NORMAL A 388
2 INDEX INDEX33560546 CLUSTER NULL NULL
3 TABLE T1 NULL NULL 611
可见表T1表上有一个rowid默认键的聚集索引,还有a字段上的普通索引
2.2.4查询数据(定位0.1%数据)
SQL> select count(c) from liu.t1 where a<0.1;
行号 COUNT(C)
---------- --------------------
1 9921
1 #NSET2: [10, 1->1, 78]
2 #PRJT2: [10, 1->1, 78]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [10, 1->1, 78]; grp_num(0), sfun_num(1)
4 #BLKUP2: [10, 10042->9921, 78]; IND_A(T1)
5 #SSEK2: [10, 10042->9921, 78]; scan_type(ASC), IND_A(T1)
已用时间: 49.413(毫秒). 执行号:960.
执行计划访问路径为IND_A二级索引定位,用时49毫秒
2.2.5查询数据(定位1%数据)
SQL> select count(c) from liu.t1 where a<=1;
行号 COUNT(C)
---------- --------------------
1 99547
1 #NSET2: [105, 1->1, 78]
2 #PRJT2: [105, 1->1, 78]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [105, 1->1, 78]; grp_num(0), sfun_num(1)
4 #BLKUP2: [105, 99947->99547, 78]; IND_A(T1)
5 #SSEK2: [105, 99947->99547, 78]; scan_type(ASC), IND_A(T1)
已用时间: 347.190(毫秒). 执行号:956.
执行计划同上,用时347毫秒
QL> et(956);
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB)
---------- ------ -------------------- ------- -------------------- ----------- ----------- --------------------
DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT
-------------------- -------------------- -------------------- ----------------- ---------------
..................
5 SSEK2 6315 1.82% 2 5 101 0
0 0 0 NULL NULL
6 BLKUP2 339266 98.04% 1 4 202 0
0 0 0 NULL NULL
ET分析显示98.04%的时间用在了BLKUP2(bookmark lookup)回表操作上。
2.2.5查询数据(定位10%数据)
SQL> select count(c) from liu.t1 where a<=10;
行号 COUNT(C)
---------- --------------------
1 1000104
1 #NSET2: [1050, 1->1, 78]
2 #PRJT2: [1050, 1->1, 78]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [1050, 1->1, 78]; grp_num(0), sfun_num(1)
4 #BLKUP2: [1050, 999870->1000104, 78]; IND_A(T1)
5 #SSEK2: [1050, 999870->1000104, 78]; scan_type(ASC), IND_A(T1)
已用时间: 00:00:03.403. 执行号:958.
执行计划同上,用时3.4秒
QL> et(958);
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB)
---------- ------ -------------------- ------- -------------------- ----------- ----------- --------------------
DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT
-------------------- -------------------- -------------------- ----------------- ---------------
................
5 SSEK2 37685 1.11% 2 5 1002 0
0 0 0 NULL NULL
6 BLKUP2 3360031 98.77% 1 4 2004 0
0 0 0 NULL NULL
ET分析显示98.77%的时间用在了BLKUP2(bookmark lookup)回表操作上。
2.2.6查询数据(定位20%数据)
SQL> insert into liu.t2 select * from liu.t1 where a<=20;
影响行数 1998785
1 #INSERT : [0, 0, 0]; table(T2), type(select) mpp_opt(0)
2 #PRJT2: [1509, 1999867->1998785, 178]; exp_num(5), is_atom(FALSE)
3 #SLCT2: [1509, 1999867->1998785, 178];
4 #CSCN2: [1509, 10000000->10000000, 178]; INDEX33560546(T1)
已用时间: 00:00:02.945. 执行号:935.
查询20%数据反而耗时比10%少。仔细看执行计划,查询优化器经过判断认为通过二级索引定位后在回表(BLKUP2)代价太高,所以自动选择了聚集索引全扫描(CSCN2)。
3、用户自定义键聚集索引
3.1条件字段上创建聚集索引
先truncate表数据,在a字段上创建索引。
create cluster index ind_t1 on liu.t1(a);
3.2加载数据
SQL> insert into liu.t1
2 select DBMS_RANDOM.value(0,100),10000,'test111','test222','test333'
3 from dual connect by level <=10000000
4 /
影响行数 10000000
已用时间: 00:06:57.940. 执行号:968.
共用时6分57秒。
查询表和索引定义:
行号 'INDEX' INDEX_NAME INDEX_TYPE COLUMN_NAME BYTES/1024/1024
---------- ------- ---------- ---------- ----------- --------------------
1 INDEX IND_T1 CLUSTER A NULL
2 TABLE T1 NULL NULL 890
可见表T1表以A字段为聚集索引键。索引字节数为NULL,因为表和聚集索引是一体的。t1表的字节数比默认聚集索引时大很多。因为聚集索引是排序的。逻辑rowid顺序递增。而a字段的插入数据是随机的,所以数据会被放置到不同的位置,不像前者排列机密,因此占空间较大。
相应地可以看到插入数据的耗时明显增长。
3.3查询数据(定位0.1%数据)
SQL> select count(c) from liu.t1 where a<0.1;
行号 COUNT(C)
---------- --------------------
1 10000
1 #NSET2: [1, 1->1, 78]
2 #PRJT2: [1, 1->1, 78]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [1, 1->1, 78]; grp_num(0), sfun_num(1)
4 #CSEK2: [1, 10042->10000, 78]; scan_type(ASC), IND_T1(T1)
已用时间: 3.244(毫秒). 执行号:970.
执行计划访问路径为IND_A聚集索引定位,用时3.2毫秒
3.4查询数据(定位1%数据)
SQL> select count(c) from liu.t1 where a<1;
行号 COUNT(C)
---------- --------------------
1 100384
1 #NSET2: [14, 1->1, 78]
2 #PRJT2: [14, 1->1, 78]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [14, 1->1, 78]; grp_num(0), sfun_num(1)
4 #CSEK2: [14, 99946->100384, 78]; scan_type(ASC), IND_T1(T1)
已用时间: 11.934(毫秒). 执行号:971.
执行计划同上,用时11.93毫秒
3.5查询数据(定位10%数据)
SQL> select count(c) from liu.t1 where a<10;
行号 COUNT(C)
---------- --------------------
1 999450
1 #NSET2: [146, 1->1, 78]
2 #PRJT2: [146, 1->1, 78]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [146, 1->1, 78]; grp_num(0), sfun_num(1)
4 #CSEK2: [146, 999869->999450, 78]; scan_type(ASC), IND_T1(T1)
已用时间: 91.268(毫秒). 执行号:972.
执行计划同上,用时91.268毫秒
3.6查询数据(定位20%数据)
SQL> select count(c) from liu.t1 where a<20;
行号 COUNT(C)
---------- --------------------
1 2000402
1 #NSET2: [293, 1->1, 78]
2 #PRJT2: [293, 1->1, 78]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [293, 1->1, 78]; grp_num(0), sfun_num(1)
4 #CSEK2: [293, 1999865->2000402, 78]; scan_type(ASC), IND_T1(T1)
已用时间: 186.267(毫秒). 执行号:973.
执行计划同上,用时186毫秒
3.7查询数据(定位50%数据)
SQL> select count(c) from liu.t1 where a<50;
行号 COUNT(C)
---------- --------------------
1 5000101
1 #NSET2: [732, 1->1, 78]
2 #PRJT2: [732, 1->1, 78]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [732, 1->1, 78]; grp_num(0), sfun_num(1)
4 #CSEK2: [732, 4997347->5000101, 78]; scan_type(ASC), IND_T1(T1)
已用时间: 454.020(毫秒). 执行号:974.
执行计划同上,用时454毫秒
3.8查询数据(定位80%数据)
SQL> select count(c) from liu.t1 where a<80;
行号 COUNT(C)
---------- --------------------
1 8000690
1 #NSET2: [1171, 1->1, 78]
2 #PRJT2: [1171, 1->1, 78]; exp_num(1), is_atom(FALSE)
3 #AAGR2: [1171, 1->1, 78]; grp_num(0), sfun_num(1)
4 #CSEK2: [1171, 7997749->8000690, 78]; scan_type(ASC), IND_T1(T1)
已用时间: 688.478(毫秒). 执行号:976.
执行计划同上,用时688毫秒
4、总结
达梦数据库里,表(列存储表和堆表除外)都是使用 B+树索引结构管理的。每一个普通表都有一个聚集索引,数据通过聚集索引键排序,根据聚集索引键可以快速查询任何记录。
表和聚集索引是一体两面。整个表就是一个组合索引,这个组合索引包含表中所有字段。
当建表语句未指定时,DM 的默认聚集索引键是逻辑ROWID,即记录默认以 ROWID在页面中排序。ROWID 是逻辑递增序号,最新插入的记录 ROWID 最大。通常情况下,以 ROWID 建的默认聚集索引并没有什么实际意义。不能提高查询速度,因为实际情况下很少人根据逻辑ROWID来查找数据。
根据业务需要考虑是否创建表时指定索引键。(本文章为方便起见在建表后建的聚集索引,但实际情况几乎不会这么做。因为后续创建聚集索引相当于把整个表和上面的二级索引重建,非常耗时)
指定聚集索引键可以明显提高以它为条件的查询速度。但同时也会增加写入时间。这需要权衡。测试小结如下:
以条件字段为键值创建了聚集索引时查询效率最高(CSEK)。查询50%的数据用时与SSEK查询1%接近。因为不需要进行回表(BLKUP2)。显然聚集索引是有强大优势的。不过修改数据的代价则提高了不少。默认表加载测试数据用时52秒,其上建立了一个普通索引后加载同样数据用时4分32秒。不创建普通索引,代之为在该字段上创建聚集索引,加载同样数据则用时6分57秒。
当根据条件搜索的数据占比非常少时(比如千分之一)推荐建立普通B+树索引,虽然无论任何时候SSEK2都比不上CSEK2更快,但写入代价较低。当定位的数据较多时推荐建立聚集索引。只能有一个聚集索引,选择查询频率最高的字段作为索引键。