达梦关于组合索引的使用

一、构建实验环境

SQL> CREATE TABLE TEST1(ID INT,ID1 INT,ID2 INT,ID3 INT);
SQL> insert into test1 select dbms_random.value(1,15000),dbms_random.value(1,15000),dbms_random.value(1,15000),dbms_random.value(1,15000) from dual connect by level <= 15000;
SQL> commit;

二、查看执行计划

SQL> explain select * from test1 where id = 5 and id1 = 5  and id3 < 4;

1   #NSET2: [1, 1, 24] 
2     #PRJT2: [1, 1, 24]; exp_num(5), is_atom(FALSE) 
3       #SLCT2: [1, 1, 24]; (TEST1.ID = 5 AND TEST1.ID1 = 5 AND TEST1.ID3 < 4)
4         #CSCN2: [1, 15000, 24]; INDEX33555723(TEST1)

对于多个查询条件的SQL，组合索引的可用条件是所有的等值条件都在索引的前面，只允许存在一个范围条件，且在索引中，范围条件的列需要跟在等值条件的所有列后面

1）建立包含id,id1,id2,id3的组合索引

SQL> create index itest1 on test1(id,id1,id2,id3);
操作已执行
已用时间: 237.178(毫秒). 执行号:15209.

explain select * from test1 where id = 5 and id1 =5  and id3 < 4;
1   #NSET2: [0, 1, 24]
2     #PRJT2: [0, 1, 24]; exp_num(5), is_atom(FALSE)
3       #SLCT2: [0, 1, 24]; TEST1.ID3 < 4
4         #SSEK2: [0, 9, 24]; scan_type(ASC), ITEST1(TEST1), scan_range[(5,5,min,min),(5,5,max,max))

可以看到，查询走了索引，但是索引仅利用两个索引列scan_range[(5,5,min,min),(5,5,max,max))，而且索引出来之后依然需要进行过滤ID3的范围条件（#SLCT2: [0, 1, 24]; TEST1.ID3 < 4），所以组合索引并没有完全被利用，因为范围列id3和等值列id,id1中间隔了id2，导致无法利用索引上id3的信息

SQL> explain select * from test1 where id = 5 and id1 =5 and id2 = 3  and id3 < 4;

1   #NSET2: [1, 1, 24] 
2     #PRJT2: [1, 1, 24]; exp_num(5), is_atom(FALSE) 
3       #SSEK2: [1, 1, 24]; scan_type(ASC), ITEST1(TEST1), scan_range((5,5,3,null2),(5,5,3,4))

当在等值条件中加入ID2的查询条件，可见此时scan_range((5,5,3,null2),(5,5,3,4)扫描利用了索引上的所有信息，并没有在扫描索引之后进行SLCT范围过滤

2）建立包含id,id1,id3的组合索引

SQL> create index itest2 on test1(id,id1,id3);
操作已执行

SQL> explain select * from test1 where id = 5 and id1 =5  and id3 < 4;

1   #NSET2: [1, 1, 24] 
2     #PRJT2: [1, 1, 24]; exp_num(5), is_atom(FALSE) 
3       #BLKUP2: [1, 1, 24]; ITEST2(TEST1)
4         #SSEK2: [1, 1, 24]; scan_type(ASC), ITEST2(TEST1), scan_range((5,5,null2),(5,5,4))

这样建立索引，扫描利用了索引上的所有信息scan_range((5,5,null2),(5,5,4))，但是多出BLKUP的rowid反表操作，因为索引不能提供select *需要的所有列，索引上不包含ID2，所以需要索引上提供的ROWID去原表（TEST1）上查找

3）建立id,id1,id3,id2顺序的组合索引

SQL> create index itest3 on test1(id,id1,id3,id2);
操作已执行

SQL> explain select * from test1 where id = 5 and id1 =5  and id3 < 4;

1   #NSET2: [1, 1, 24] 
2     #PRJT2: [1, 1, 24]; exp_num(5), is_atom(FALSE) 
3       #SSEK2: [1, 1, 24]; scan_type(ASC), ITEST3(TEST1), scan_range[(5,5,null2,min),(5,5,4,min))

附加上select *查询中需要的列，可以减少BLKUP操作，无需在经过条件列过滤后使用ROWID回原表进行查找，一些场景下可以大幅提升并发性能

SQL> explain select * from test1 where id < 100 and id1 = 5;

1   #NSET2: [1, 18, 24] 
2     #PRJT2: [1, 18, 24]; exp_num(5), is_atom(FALSE) 
3       #SLCT2: [1, 18, 24]; TEST1.ID1 = 5
4         #SSEK2: [1, 750, 24]; scan_type(ASC), ITEST3(TEST1), scan_range[(null2,min,min,min),(100,min,max,max))

可以看到索引扫描时仅利用了 < 100的条件，scan_range[(null2,min,min,min),(100,min,max,max))，扫描后需要再次过滤#SLCT2: [0, 18, 24]; TEST1.ID1 = 5，因为ID作为范围条件，在现有的索引中是第一列，且不存在ID1为第一列的索引，所以只能选择一条对ID字段进行范围过滤