组合索引创建准则

基于某个列所创建的新索引并不是只会影响某个特定的查询语句，而是将影响到所有使用到该列的查询语句。

在索引构建中，我们追求的不是多而是精。

就像通过革新可以为组织注入新的活力一样，在RDBMS中，通过索引的革新往往就可以获得惊人的执行速度。

茫然推测或者为了特定要求而随意创建索引的行为应当被坚决禁止。通过实事求是的原则，对实际信息【搜集的数据读取类型，读取频度，查询范围，离散度，表的大小等】全面分析来构建综合性的战略性索引才是成功之道。

 

1.索引的选定准则—— 不同类型表的索引应用准则

1.1拥有数据量较小的小型表

• 如果从单一的小型表中读取数据，那么索引的有无不会对执行速度有任何关系。但是在该小型表以不同方式与其他表执行连接的情况下，索引的有无将会对执行速度有非常大的影响。
• 不妨用索引组织表来存储这种类型的表。

1.2主要起参考作用的大中型表

• 这种类型的表一般以小范围扫描为主，处于内循环并且通过主键来执行表连接。
• 在这种类型的表中，主要为各个索引分配合理的任务【也就是创建了战略性索引】，即使需要创建大量的索引也应当果断作出决定。
• 这种表的数据量变更的可能性非常小，不是特殊情况下不需要执行重构操作。

1.3管理具体业务行为的大中型表

• 这种类型的表在查询时经常处于循坏的外侧，并且随着时间的推移数据量不断增加。
• 构建战略性索引需要遵循的准则：

         1.搜集正在使用的所有数据读取类型；

         2.寻找能够满足所有读取要求的比较理想的索引组合。

1.4存储用的大型表

        这种类型的表主要用来存储和管理日志性数据。

• 数据量大且不断增加；
• 只有INSERT和SELECT两种操作类型；
• SELECT次数不多，并多是范围查询；
• PCTFREE可以设置为0；
• 可以不设置主键，以减小INSERT的开销；
• 可以为表创建分区【一般以时间列创建】

 

2.索引的选定准则——离散度和损益分界点

   创建索引的目的是快速从整体集合中选择性地读取满足条件的一小部分集合。

   在查询条件中既有对缩减查询范围起主要作用的驱动查询条件，也有单纯起检验作用的过滤查询条件。

   我们所追求的目标是创建全表扫描所无法比拟的有效索引

   如果各个列的离散度都不够好，那么可以将这些列进行有效的组合，通过合力的有效使用可以达到意想不到的效果【组合索引】。

   另外，这里的损益分界点都是在利用索引读取满足条件的全部数据的情况下分析的，如果只读取部分数据【局部范围扫描】，则高于损益点也可以使用索引。

 

3.索引的选定准则——索引合并和组合索引的比较

   为了实现以最少的索引满足对某个表的多样化的数据读取要求，应当为每个索引分配合理的任务，基本思路：

•    对离散度较好的列创建单列索引；
•    对离散度不好的多个列创建足额和索引；

   索引合并的举例

3.1 TAB1为字段COL1，COL2创建了索引分别为index1、index2，利用索引合并的方式查询数据步骤如下

（1），利用索引index1获取满足条件的COL1=’ABC’数据，按照随机方式读取第一行数据ROWID=15，记录ROWID=15位置

（2），利用索引index2获取满足条件的COL1=123数据，按照顺序方式查找数据，直到查找到比ROWID=15大的数据ROWID=32为止，记录ROWID=32位置

（3），顺序读取索引index1，查找ROWID=15位置之后数据，直到查找到ROWID比32大的数据

（4），依次循环读取数据，直到查询出COL1=’ABC’ and COL2=123的数据     图如下所示，其时间复杂度为O（m+n）

只有当合并的索引具有相似的离散度时索引合并才比较有效，当索引的离散度相差比较大时使用索引合并的方法反倒容易影响执行效率【不如采用离散度大的索引做驱动查询条件，另一个条件只做过滤】。

3.2 为COL1，COL2字段创建组合索引index(COL1,COL2)，查询如右图所示，查询索引数据，扫描两行数据获取结果

3.3 只有在查询条件中对索引列使用了等值比较时组合索引才能够有突出的表现

 

4.索引的选定准则——组合索引的特征

 

4.1离散度与索引组合顺序之间关系

sql语句  SELECT * FROM Table WHERE COL1=’A’ AND COL2=115

字段COL1离散度大，COL2离散度小

建立组合索引index1(COL1,COL2)，离散度大的列COL1放在前面，离散度小的列COL2放在后面，查询执行步骤如下

（1），根据索引index1随机获取COL1=’A’ and COL2=115的第一行记录ROWID

（2），依次顺序获取下一行记录，判断不满足条件位置。

如图所示，影响查询结果2行数据

 

建立组合索引index2(COL2,COL1)，离散度小的列COL2放在前面，离散度小的列COL1放在后面，查询执行步骤如下

（1），根据索引index2随机获取COL2=115 and COL1=’A’ 的第一行记录ROWID

（2）,依次顺序获取下一行记录，判断不满足条件位置。

如图所示，影响查询结果2行数据

 

由上图可以看出，离散度的好坏在组合索引中并不是很重要，重要的是是否使用了“=”查询条件。

 

4.2 等值比较对列的组合顺序的影响

sql语句 SELECT * FROM Table WHERE COL1=’A’ AND COL2 between 113  and 115

COL1列离散度大，COL2离散度小

建立组合索引index1(COL1,COL2)，等号查询的列COL1放在前面，非等号查询的列COL2放在后面，查询执行步骤如下

（1），根据索引index1随机获取COL1=’A’ AND COL2=113的第一行记录ROWID

（2），顺序读取下一行记录，直到获取不满足条件的数据为止

如下左图所示，影响查询结果4行记录

 

建立组合索引index2(COL2,COL1)，非等号查询的列COL2放在前面，等号查询的列COL1放在后面，查询执行步骤如下

（1），根据索引index1随机获取COL2=113 AND COL1=’A’的第一行记录

（2），顺序读取下一行记录，满足条件记录获取，不满足条件的跳过

如下左图所示，影响查询结果13行记录

由上图可以看出，在没有为组合索引的第一列赋予使用“=”比较查询条件时，即使为后面的列赋予了使用“=”查询条件也不会缩减数据的查询范围

 

4.3 in查询对组合索引的影响

sql语句 SELECT * FROM Table WHERE COL1=’A’ AND COL2 between 113 and 115

为列COL1、COL2创建组合索引index(COL2、COL1)，其查询执行步骤如下

（1），根据索引随机获取COL2=111 and COL1=’A’的第一行记录

（2），顺序读取下一行记录，满足条件记录获取，不满足条件的跳过

如下左图所示，影响查询结果6行记录

 

sql语句 SELECT * FROM Table WHERE COL1=’A’ AND COL2 in(111,112)

为列COL1、COL2创建组合索引index(COL2、COL1)，其查询执行步骤如下

（1），根据索引随机获取COL2=111 and COL1=’A’的第一行记录

（2），顺序读取下一行记录，直到获取不满足条件的数据为止

（3），根据索引随机获取COL2=112 and COL1=’A’的记录

（4），顺序读取下一行记录，直到获取不满足条件的数据为止

如下右图所示，影响查询结果4行记录

由上图可以看出，使用in查询明显比Between查询有效。 从概念上讲，Between或者Like类似于“线段“的概念，IN类似于”点“的概念。从逻辑上讲，由于线段是由无穷多个点组成的，所以无法确保可以将BETWEEN或者LIKE转换成IN，也即无法确保始终可以将具有线段概念的查询条件转换为”=“查询条件。

使用IN可以将其他查询转化为等值查询。

 

4.3 对查询范围不构成直接影响的列的添加准则

转载于:https://www.cnblogs.com/yangqing/archive/2012/11/13/2768372.html