单键索引还是组合索引

转: http://book.51cto.com/art/200906/132455.htm

8.4.6  单键索引还是组合索引

在大概了解了MySQL 各种类型的索引，以及索引本身的利弊与判断一个字段是否须要创建索引之后，就要着手创建索引来优化Query 了。在很多时候，WHERE 子句中的过滤条件并不只是针对于单一的某个字段，经常会有多个字段一起作为查询过滤条件存在于 WHERE 子句中。在这种时候，就必须要判断是该仅仅为过滤性最好的字段建立索引，还是该在所有字段（过滤条件中的）上建立一个组合索引。

对于这种问题，很难有一个绝对的定论，须要从多方面来分析考虑，平衡两种方案各自的优劣，然后选择一种最佳的方案。因为从上一节中已了解到索引在提高某些查询的性能同时，也会让某些更新的效率下降。而组合索引中因为有多个字段存在，理论上被更新的可能性肯定比单键索引要大很多，这样带来的附加成本也就比单键索引要高。但是，当WHERE 子句中的查询条件含有多个字段时，通过这多个字段共同组成的组合索引的查询效率肯定比只用过滤条件中的某一个字段创建的索引要高。因为通过单键索引过滤的数据并不完整，和组合索引相比，存储引擎须要访问更多的记录数，自然就会访问更多的数据量，也就是说需要更高的 IO 成本。

可能有朋友会说，那可以创建多个单键索引啊。确实可以将 WHERE 子句中的每一个字段都创建一个单键索引。但是这样真的有效吗？在这样的情况下，MySQL Query Optimizer 大多数时候都只会选择其中的一个索引，然后放弃其他的索引。即使他选择了同时利用两个或更多的索引通过 INDEX_MERGE 来优化查询，所收到的效果可能并不会比选择其中某一个单键索引更高效。因为如果选择通过 INDEX_MERGE 来优化查询，就须要访问多个索引，同时还要将几个索引进行 merge 操作，这带来的成本可能反而会比选择其中一个最有效的索引更高。

在一般的应用场景中，只要不是其中某个过滤字段在大多数场景下能过滤90%以上的数据，而其他的过滤字段会频繁的更新，一般更倾向于创建组合索引，尤其是在并发量较高的场景下。因为当并发量较高的时候，即使只为每个Query节省了很少的 IO 消耗，但因为执行量非常大，所节省的资源总量仍然是非常可观的。

当然，创建组合索引并不是说就须要将查询条件中的所有字段都放在一个索引中，还应该尽量让一个索引被多个 Query 语句利用，尽量减少同一个表上的索引数量，减少因为数据更新带来的索引更新成本，同时还可以减少因为索引所消耗的存储空间。

此外，MySQL 还提供了另外一个优化索引的功能，那就是前缀索引。在 MySQL 中，可以仅仅使用某个字段的前面部分内容做为索引键索引该字段，以达到减小索引占用的存储空间和提高索引访问效率的目的。当然，前缀索引的功能仅仅适用于字段前缀随机重复性很小的字段。如果须要索引的字段前缀内容有较多的重复，索引的过滤性自然也会随之降低，通过索引所访问的数据量就会增加，这时候前缀索引虽然能够减少存储空间消耗，但是可能会造成 Query 访问效率的极大降低，得不偿失。