当组合索引的第一个索引列(通常称为先导列)没有出现在查询条件中时,一般情况下该索引无法被使用。组合索引最大的缺点就是当只为一部分索引列赋予了查询条件时,或者所使用的比较运算符大部分都是非“=”比较运算符时效率非常低下。
创建战略性的组合索引涉及两个非常重要的方面:第一,基于什么样的列创建索引;第二,按照什么样的顺序创建索引。
1.离散度和列序之间的关系
如果只是在“=”比较的情况下,离散度的好坏对列的组合顺序并没有什么大的影响。(对我们来说最重要的并不是离散度的好坏,而是是否使用了“=”比较的查询条件。)
2.等值比较对列的组合顺序的影响
问题的焦点就在于大量的等值比较运算符是否被连续使用。在实际工作中,多样化的数据读取需求使得很难为所有的列赋予等值比较,更为常见的是LIKE、BETWEEN、>、<等运算符。事实上,对执行速度影响最大的正是使用非等值比较的查询条件。在没有为组合索引的第一列赋予使用“=”比较查询条件时,即使为后面的列赋予了使用“=”查询条件也不会缩减数据的查询范围。
3.IN的垫脚石效果
从概念上看,BETWEEN 或者LIKE类似于“线段”的概念,IN类似于“点”的概念。由于线段是由无穷多个点构成的,如果无法将BETWEEN 或者LIKE转换为IN,也无法将线段概念的查询条件转换为“=”比较的查询条件。该“点”就像垫脚石一样,有助于只读取需要的部分。
例:INDEX:COL1+COL2+COL3
WHERE COL1=’A’ and COL3=’333’
如果没有为列COL2赋予查询条件,尽管为COL1赋予了“=”比较的查询条件,但是仍然扫描了满足COL1=’A’的整体范围。没有为COL2赋予查询条件与为COL2赋予LIKE’%’查询条件具有相同的效果。由于没有为COL3之前的COL2赋予“=”查询条件,COL3的查询条件只能起检验作用。假设,当COL2的值只有1,2,3时,可以修改查询条件解决此问题:
WHERE COL1=’A’ and COL2 IN (1,2,3) and COL3=’333’
在没有为C2赋予查询条件的情况下,由C1+C2所构成的索引和由C1+C2+C3所构成的索引不仅具有相同的查询范围,而且具有相同的执行结果。但是由于在C1+C2的索引中没有使用列C3,所以当查询条件WHERE C1=’A’ and C3=’333’,为了检验满足C1=’A’的行是否满足C3=’333’就必须从表中读取数据。因此,像C3这样的列,虽然对缩减查询范围没有直接性的影响,但是对查询却起着非常重要的作用。
在为某个组合索引选定列时,首先应该考虑的是对直接缩减查询范围骑着重要作用的列;其次,对附加性列是否会有利于提高执行效率等相关问题进行全面分析。