复合索引

索引合并和组合索引的比较

 

 

复合索引的优势只有查询复合索引的全部列，并且按索引的设置顺序查询；最重要的是一定要有首列的查询条件，如果没有将用不到索引，其他列顺序乱了的话，只能用到一部分索引

 

 

在教务系统补考子系统的开发过程中，安排考场的算法运算时间非常慢，需要32秒才能完成考场的分配。查找原因，由于需要在学生补考科目表里面进行查找数据——该表的数据目前已达200万条，所以算法的大部分时间都花在查找数据方面。为了解决以上问题，我们决定对数据表进行分析，创建合适的索引。

在创建索引过程中，我们发现查询语句已经为查询列创建了索引，但是是为每个列单独创建的——SELECT  *  FROM TABLE_NAME  WHERE COL1=’ABC’  AND  COL2=123;

为了重点说明原理，我简化了示例。在以上示例中，查询语句的查询条件有2个列，分别是COL1和COL2。我们为COL1（INDEX1）和COL2(INDEX2)都分别创建了一个索引。创建好索引后，我们进行了测试，发现速度没有非常明显的加速，还是需要20多秒。优化效果不明显。

然后我们尝试了在COL1和COL2上创建组合索引，效果非常明显，从32秒到目前的8秒。针对这种情况，我深入研究了一下，在2个列上单独创建索引，如果查询语句使用到，叫合并索引；如果是在2个列上创建组合索引，就叫组合索引。下面通过原理来解释一下合并索引和组合索引的区别，也讲解一下为什么组合索引会比合并索引要快许多。

首先解释一下合并索引的搜索步骤，如下图所示：

 

 

上面步骤可以用下图进行说明：

 

如下图所示，单独的2个索引进行查找——索引合并，需要反复在2个索引表间进行跳转，这是造成速度慢的第一个影响。第2个影响是，假设满足COL1=’ABC’的数据有5行，满足COL2=123的数据有1000行。最坏的情况下（那5行在COL2的1000行最后面）需要扫描完COL2的1000行才能找到需要的数据，并不能达到快速查找的目的。

下面我们来讲解一下组合索引，由于组合索引综合保存了COL1和COL2的数据，它不需要在2个索引表之间跳转，所以速度会更快，组合索引的搜索步骤如下图所示：

 

上图是索引合并和组合索引的对比图。组合索引不需要像索引合并那样对索引的ROWID进行比较合并。

以上就是对索引合并于组合索引区别的说明，另外补充一点，在什么情况下使用组合索引比较好——在查询条件中对索引列使用了等值比较时，组合索引才能发挥最大的效能。

 

来自 <http://blog.csdn.net/ltylove2007/article/details/21084809>

 

两个或更多个列上的索引被称作复合索引。

利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

如：建立 姓名、年龄、性别的复合索引。

 

复合索引的建立原则：

 

如果您很可能仅对一个列多次执行搜索，则该列应该是复合索引中的第一列。如果您很可能对一个两列索引中的两个列执行单独的搜索，则应该创建另一个仅包含第二列的索引。

如上图所示，如果查询中需要对年龄和性别做查询，则应当再新建一个包含年龄和性别的复合索引。

包含多个列的主键始终会自动以复合索引的形式创建索引，其列的顺序是它们在表定义中出现的顺序，而不是在主键定义中指定的顺序。在考虑将来通过主键执行的搜索，确定哪一列应该排在最前面。

请注意，创建复合索引应当包含少数几个列，并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值，其后果是内存溢出和性能降低。

 

来自 <http://blog.csdn.net/linminqin/article/details/44342205>