达梦数据库调优组合索引的使用

调优工程中对组合索引的使用的一个小总结。

一、组合索引的执行计划
首先关于几个概念，等值条件和范围条件：
1、等值条件
C1 = X
C1 = X OR C1 = Y （会被优化成C1 IN (X,Y)）
C1 IN (X,Y…)
这种形式的我们都叫做等值条件。
2、范围条件
C1 > X
C1 < Y
C1 > X AND C1 < Y
C1 BETWEEN X AND Y
这种形式的我们都叫做范围条件。
对于单表的访问，条件可以是任意个等值条件和范围条件的组合（我们仅考虑AND的情况,OR有单独的优化逻辑）。组合索引可用的条件是：
1)所有的等值条件都在索引的前面；
2)只允许存在一个范围条件，且在索引中，范围条件的列需要紧跟在等值条件的所有列后面；
比如存在等值条件 C1 C3 C5 C7，范围条件C4，那么索引(C1,C3,C5,C7,C4),(C1,C5,C3,C7,C4),(C7,C1,C3,C5,C4)都是可以利用的
而 (C1,C3,C5,C7,C6,C4),(C1,C3,C4,C5)这种不可用，或者只能利用到部分条件。
以下举例说明：
执行以下sql语句

DROP TABLE TEST1;
CREATE TABLE TEST1(ID INT,ID1 INT,ID2 INT,ID3 INT);
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;
commit;

全表扫描后过滤，需要建立索引。

create index itest1 on test1(id,id1,id2,id3);

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

补上id2的条件后，对于TEST1的访问利用了索引上的所有信息scan_range((5,5,DMTEMPVIEW_16778286.colname,null2),(5,5,DMTEMPVIEW_16778286.colname,4)，三个等值，一个范围，
形如DMTEMPVIEW_16778286.colname这种是因为IN的列表被转成了一个常数表，先扫描这个常数表，对于这个常数表中的每一个值，都附加上其他的条件一起（id =5 id1 =5 id3 <4）组合成一个条件对TEST1表进行过滤。

如果使用另一种方式建立索引：

create index itest2 on test1(id,id1,id3);

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

如果再用另一种方式建立索引：

create index itest3 on test1(id,id1,id3,id2);

如图可见，BLKUP操作也可以消除了。
*这也是组合索引的另一个重要作用，附加上查询中需要的列可以减少BLKUP操作，一些场景下可以大幅提升并发性能。

二、连接中组合索引的使用
建立一张测试表：

CREATE TABLE TEST2(ID INT);
insert into test2 select level from dual connect by level < 3;
commit;

可以看到对于 TEST1的访问，完全利用了 TEST1.ID = TEST2.ID AND TEST1.ID1 < 5 ,scan_range[(TEST2.ID,null2,min,min),(TEST2.ID,5,min,max))。
当待访问表为连接右表时，等值连接可以作为一个特殊的等值条件，建立索引时可以考虑，规则和单表上的多条件是一致的。

查询中TEST1.ID2作为等值连接条件，对于TEST1所有的过滤为ID2的等值以及ID1的范围，但前面建立的索引没有满足可以利用条件的，索引对于TEST1表的访问走了全表扫描（SSCN,索引扫描）；
且扫描后仅利用 TEST1.ID1 < 5 条件，因为等值连接的条件没有好的索引可以利用，等值条件转换为比嵌套连接更快的HASH来处理；
如果我们建立ID2,ID1索引，那么这个索引是可以被完全利用的。

可以看到，对于TEST1的访问完全利用了索引ITEST1的所有信息scan_range((4,5,TEST2.ID,null2),(4,5,TEST2.ID,7))，三个等值一个范围，连接条件作为等值条件不在索引的第一列没有问题，只要和其他的等值条件一起出现在范围条件前面就可以了。

这个计划中，利用索引ITEST1，但是只利用到了第一列scan_range[(DMTEMPVIEW_16778271.colname,min,min,min),(DMTEMPVIEW_16778271.colname,max,max,max)，
其中DMTEMPVIEW_16778271.colname就是对于 IN(3,4,5)这种，服务器会把他转变成一个表，这个COLNAME就表示这个表上的列。
前面我们说 等值条件、连接等值条件 如果都在前面，范围在后面，是可以完全利用索引的，这里为什么不行呢？。
因为IN(XX,XX,XX …)是特殊的等值条件
优化器的处理是把IN 转变成 原表与常量表的连接，单表访问时，没有问题，但是如果原表还需要和另外一个表连接，若另外的表在右，则不能IN和连接条件同时作为原表的访问条件，若原表在左边，则没有另外一张表的连接列条件可用，所以连接列卡在包含IN等值条件中间的话，不能两者都利用索引定位。