达梦数据库聚集索引与普通索引的对比

目录

1、创建测试表

2、未指定聚集索引键

2.1 未建普通索引时

2.1.1查询表和索引定义

2.1.2加载数据

2.1.3查询数据(1%)

2.1.4查询数据(10%)

2.2 建立普通索引

2.2.1条件字段上建立B+树索引

2.2.2 加载数据

2.2.4查询数据(定位0.1%数据)

2.2.5查询数据(定位1%数据)

2.2.5查询数据(定位10%数据)

2.2.6查询数据(定位20%数据)

3、用户自定义键聚集索引

3.1条件字段上创建聚集索引

3.2加载数据

3.3查询数据(定位0.1%数据)

3.4查询数据(定位1%数据)

3.5查询数据(定位10%数据)

3.6查询数据(定位20%数据)

3.7查询数据(定位50%数据)

3.8查询数据(定位80%数据)

4、总结


达梦数据库,表和聚集索引一体两面。整个表就是一个大的组合索引,这个索引包含表中所有字段,按照索引键排序。建表时指定聚集索引键可以明显提高以它为条件的查询速度。但同时也会增加写入时间。这需要权衡。当根据条件搜索的数据占比非常少时(比如千分之一)推荐建立普通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更快,但写入代价较低。当定位的数据较多时推荐建立聚集索引。只能有一个聚集索引,选择查询频率最高的字段作为索引键。

  • 1
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值