组合索引的应用

等值条件和范围条件

对于组合索引，我们先理解两个概念，等值条件和范围条件。

等值条件

C1 = X
C1 = X OR C1 = Y (会被优化成C1 IN (X,Y))
C1 IN (X,Y……)

这种形式的我们都叫做等值条件。

范围条件

C1 > X
C1 <Y
C1 >X AND C1 < Y
C1 BETWEEN X AND Y

这种形式的我们都叫做范围条件。

组合索引可用的规则

对于单表的访问，条件可以是任意个等值条件和范围条件的组合（我们仅考虑 AND 的情况，OR 有单独的优化逻辑）。组合索引可用的规则是：所有的等值条件都在索引的前面只允许存在一个范围条件，且索引中范围条件的列需要紧跟在等值条件的所有列后面或者没有等值条件且范围条件的列索引中的第一列。

如存在等值条件列 C1 C3 C5 C7，范围条件列 C4，那么索引 index (C1,C3,C7,C4),index (C1,C5,C3,C7,C4)，dex (C7,C1,C3,C5,C4) 都是可用利用的。而 index (C1,C3,C5,C7,C6,C4)，index (C1,C3,C4,C5) 这种索引不可用，或者只能利用到部分条件。

如有索引 index (A,B,C)，下面条件可以用该组合索引查询：

A > 5
A = 5 AND B > 6
A =5 AND B = 6 AND C = 7
A =5 AND B IN (2,3) AND C > 5
A = 5 AND B IN (2,3) AND C > 5

下面条件将不能用该组合索引查询：

B > 5 ——查询条件不包含组合索引首列字段
B = 6 AND C = 7 ——查询条件不包含组合索引首列字段

下面条件将能用部分组合索引查询：

A>5 AND B=2 ——当范围查询使用第一列，查询条件仅仅能使用第一列
A=5 AND B>6 AND C=2 ——当范围查询使用第二列，查询条件仅仅能使用第二列

举例说明

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;

查询语句并查看执行计划：

--EXP1
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_stom(FALSE)
3    #SLCT2:[1,1,24];(TEST1.ID = 5 AND TEST1.ID1 = 5 AND TEST1.ID3 < 4)
4     #CSCN2:[1,15000,24];INDEX33575409(TEST1)

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

如果我们建立索引 id,id1,id2,id3

create index itest1 on test1(id,id1,id2,id3);
--EXP2
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_stom(FALSE)
3    #SLCT2:[0,1,24]:TEST1.ID3 < 4
4     #SSEK2:[0,9,24];scan_type(ASC),ITEST1(TEST1),
acan_range[(5,5,min,min),(5,5,max,max)]

可以看到，查询走了索引，但是索引仅利用两列 scan_range[(5,5,min,min),(5,5,max,-max)],且索引出来之后依然需要过滤（#SLCT2:[0,1,24];TEST1.ID3 < 4），所以索引并没有完全利用。

因为范围列 id3 和等值列 id,id1 中间隔了一个 id2，导致无法利用索引上的 id3 的信息。

看另一个查询：

explain select * from test1 where id = 5 and id1 = 5 and id2 in (3,4,5) and id3 < 4;

1  #NSET2:[1,1,24];
2   #PRJT2:[1,1,24];exp_num(5),is_stom(FALSE)
3    #NEST LOOP INDEX KOIN2:[0,1,24]
4     #CONST VALUE LIST:[0,3,0];row_num(3),col_num(1),
5     #SSEK2:[0,0,0];scan_type(ASC),ITEST1(TEST1),scan_range((5,5,DM-TEMPVIEW_16778286.colname,mull2),(5,5,DMTEMPVIEW_16778286.colname,4))

补上 id2 的条件后，对于 TEST1 的访问利用列索引上的所有信息 scan_range((5,5,DM-TEMPVIEW_16778286.colname,mull2) ,(5,5,DMTEMPVIEW_16778286.colname,4)，三个等值，一个范围。

形如 DM-TEMPVIEW_16778286.colname 这种是因为 IN 的列表被专成了一个常数表，先扫描这个常数表，对于这个常数表中的每一个值，都附加上其他的条件一起 (id=5 id1=5 id3<4) 组合成一个条件对 TEST1 表进行过滤。

换一种方式建立索引

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

--EXP3

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_stom(FALSE)
3    #BLKUP2:[0,1,24];ITEST2(TEST1)
4     #SSEK2:[0,1,24];scan_type(ASC),ITEST2(TEST1),scan_range((5,5,null2),(5,5,4)

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

如果建立索引

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

--EXP4

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_stom(FALSE)
3    #SSEK2:[0,1,24];scan_type(ASC),ITEST3(TEST1),scan_range[(5,5,null2,min),(5,5,4min)]

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

还是这几个索引，如果执行以下语句

--EXP5
1  #NSET2:[0,18，24]
2   #PRJT2:[0,18,24];exp_num(5),is_stom(FALSE)
3    #SLCT2:[0,18,24];TEST1.ID1 = 5
4     #SSEK2:[0,750,24];scan_type(ASC),ITEST1(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 进行范围过滤。