可索引谓词，索引，优化器

一次sql优化的机会，被where条件，索引，优化器这三者的决定关系混淆，借助这个问题整理如下：

1.概念

不可索引谓词，比如<>

可索引谓词,比如C1=5,in条也是可索引谓词，而or条件就是不可索引谓词

匹配到索引列的谓词称为匹配谓词

可索引谓词是匹配谓词的前提

不可索引谓词可以用做筛选谓词

 

复合索引:主索引列+从索引列，主索引列很关系，如果where条件中的所有可索引谓词都没有匹配到主索引列，从索引列就不用匹配了，索引不能发挥作用，只能扫表，这就是最左原则。

当有可索引谓词能匹配到主索引列，其它可索引谓词再依次匹配到从索引列，这些可索引谓词都统称为匹配谓词，但最左匹配谓词最关键。

 

所以，可索引谓词用来匹配索引列的，即索引列决定了where中的可索引谓词是否能匹配到索引，是索引的结构决定了where条件是否能使用索引， 但是一般建索引的时候，是需要逆向参考where条件中的可索引谓词，来决定索引主从索引列。所以在使用索引的过程中，索引决定了一切。但是在定义索引的时候，可以说where决定了索引。

 

在使用索引的时候，where的谓词顺序是可以被优化器按照索引的的列顺序进行调整的，所以，区别索引的创建与使用的不同阶段

 

定义索引时，特别强调可索引谓词: 

如果where条件中匹配到了多个索引，优化器会决定使用哪些索引。通常唯一索引要优于复合索引，比如c1,c2,c3,c4的复合索引被匹配谓词匹配到，c7的唯一索引也可以被匹配谓词匹配到，可能c7的唯一索引就会被优先使用，示例：

不同的列之间使用代数表达数同样是不可索引的， 比如C1=C2是不可索引谓词（C1，C2均为数据列）

 

2.特殊的可索引谓词： 

2.1.不一定非在where条件中出现. 比如max,min函数也是可以使用索引的， 比如select  max(C1) From T. 这称为One-Fetch Access.

2.2.in条件

2.3.通过转换做到可索引谓词，比如C1 like '%tion' 可以先将C1通过函数倒序，然后可以利用索引， 即C1 like 'noit%'

 

 

3.where条件，索引，优化器的关系

问题：认为优化器会自动调整where条件的顺序，所以，不确定索引的顺序主从顺序，即存在一个where条件决定索引还是索引决定where条件

理解：优化器显然只在使用阶段发挥作用，优化器调整where条件也是以已经存在的索引为依据。所以，索引决定了优化器自动调整where条件的顺序。但是在创建索引创建阶段，依据的是where中的可索引谓词，注意，创建复合索引并不依赖where条件的顺序。

优化器是还有一个场景是在应用索引阶段，如果有多个索引满足的情况下，选择一个最优的索引。

 

 

 

 

 

4.示例

 

对于索引中的主索引列必须至少对应一个可索引谓词才能使用索引的树结构进行高效查找（否则只能对页结点页进行逐个扫描或者放弃索引使用表扫描）。那么这个可索引的谓词就称为匹配谓词。如果一系列的可索引谓词都能够匹配复合索引中从左到右连续的索引列，那么这些可索引谓词都是匹配谓词。

表T1上有列C1、C2、C3、C4、C5、C6、C7、C8。其中在C1,C2,C3,C4上有索引C1234X。

A.查询语句：Select * From T1 Where C1=5 And C2=4 And C3=7 And C4=2

该语句使用索引C1234X，匹配谓词为C1=5、C2=4、C3=7、C4=2。使用这些谓词可以直接完成定位，无需进行索引页的逐个扫描。

D.查询语句：Select * From T1 Where C2=4 And C3=7 And C1=5

该语句使用索引C1234X，匹配谓词为C1=5、C2=4、C3=7。注意，对应Where子句后面的谓词，优化器会根据索引中列的顺序进行重新排序（查询重写）。

E.查询语句：Select * From T1 Where C1=5 And C2=4 And C4=2 And C6=10

该语句使用索引C1234X，匹配谓词为C1=5、C2=4，筛选谓词为C4=2，C6=10为普通谓词（没有索引与之对应）。可以看到，一旦谓词对应的索引列不连续，那么其后的索引列对应的谓词就只能是筛选谓词了。因为一旦不连续，那么该谓词就不能继续使用树结构进行定位了，只能对下层索引页进行逐个扫描。

F.查询语句：Select * From T1 Where  C1=5 And C2=4 And C3>7 And C4=2

该语句使用索引C1234X，匹配谓词为C1=5、C2=4、C3>7,筛选谓词为C4=2。一直到C3都能够使用树结构进行定位，但是C4就不行了，因为C3>7给的只是一个范围，该范围内的下层索引页只能由C4谓词进行逐个扫描筛选了。

表T上有列C1、C2、C3、C4、C5、C6、C7、C8。其中在C1,C2,C3,C4上有索引C1234X；C5、C6上有索引C56X；C7上有唯一索引C7X。

用ACCESSTYPE表示访问类型，ACCESEETYPE=I表示使用索引扫描，ACCESSTYPE=N表示使用带In-list谓词的索引扫描。ACCESSNAME表示使用的索引。MATCHCOLS表示匹配的索引列数。对照上面的规则，不再进行详细解释了。

多个索引可用的情况下，优化器会做优化

Select * From T Where C1=5 And C2=7 And C5=8 And C6=13

ACCESEETYPE=I  ,  ACCESSNAME=C56X  ,  MATCHCOLS=2   //至于为什么使用的是索引C56X而不是C1234X，这是由优化器的成本估算结果决定的。

Select * From T Where C1=5 And C2 in（5,6） And （C3=10 or C4=11）

ACCESEETYPE=N  ,  ACCESSNAME=C1234X  ,  MATCHCOLS=2    //"or"操作符连接的谓词会被当作不可索引谓词，因此不是匹配谓词，但是可以作为筛选谓词。

Select * From T Where C1=5 And C2=7 And C7=101

ACCESEETYPE=I  ,  ACCESSNAME=C7X  ,  MATCHCOLS=1      //通常唯一索引会优于普通索引，但也不是绝对的。

Select * From T Where C2=7 And C3=10 And C4=12 And C5=16

ACCESEETYPE=I  ,  ACCESSNAME=C1234X  ,  MATCHCOLS=0    //虽然C1234X上没有匹配谓词，C56X上有，但是优化器认为使用索引C1234X更优。

转自http://blog.csdn.net/idber/article/details/8182894