创建高性能索引策略

    高效的选择和使用索引有很多方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。使用哪个索引，以及如何评估选择不同索引的性能影响的技巧。

一、独立的列

    通常会看到一些查询不当地使用索引，或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。例如：

    select ... where to_days(current_date) - to_days(date_col) <=10;

二、前缀索引和索引选择

    有时候需要索引很长的字符列，这会让索引变得很大且慢。一个策略是前面提到过的模拟哈希索引，但是这还是不够的。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查询时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

    一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于blob、text或者很长的varchar类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。通过计算，选择适合的前缀长度。

注意：前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有缺点，MySQL无法使用前缀索引做order by和Group by，也无法使用前缀索引做覆盖扫描。

三、多列索引

    对多列索引，常有一个错误的理解，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。先来看为每个列创建独立的索引，这种索引策略，一般是由于人们听到一些专家诸如“把where条件里面的列都建上索引”这种模糊的建议导致的。实际上这个建议是非常错误的。这样一来最好的情况下也只能是“一星”索引，其性能比起真正优秀的索引可能差几个数量级。有时如果无法设计一个“三星”索引，那么就不如忽略掉where子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。

     在多个列上建立的单列索引大部分情况下并不能提高MySQL的查询性能。mysql5.0和更新版本引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位制定的行。有些情况下，查询能够同时使用单列索引进行扫描，并将结果进行合并，这种算法有三个变种：OR条件的联合，AND条件的相交，组合前两种情况的联合及相交。

    索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕， 应该通过explain好好检查一下查询和表的结构：

• 当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
• 更重要的是，优化器不会把这些计算到“查询成本”中，优化器只关心随机页面的读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫描。

四、选择合适的索引顺序

    通常最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。 在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。索引，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的oder by、group by和distinct等字句的查询需求。所以多列索引的顺序至关重要。

    如何选择索引的列顺序有一个经验法则：如果不需要考虑排序和分组时，将选择性最高的列放在前面通常很好，这时索引的作用只是用于优化where条件的查询，选择性比较好。但是，性能不只是依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值得分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要你根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性能最高。

五、注意采用聚簇索引和覆盖索引

《详解聚簇索引》和《详解覆盖索引》

六、使用索引扫描来做排序

    mysql有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描。如果explain出来的type列的值为“index"，则说明mysql使用了索引扫描来做排序（不要和extra列的using index搞混了）。扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不会覆盖查询所需的全部列，那就不得不扫描一条索引记录就都会表查询一次对应的行。这基本上都是随机I/O，因为索引顺序读取数据的速度通常要比顺序的全表扫描慢，尤其是在I/O密集型的工作负载时。

    mysql可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能的同时满足这两种任务，这样是最好的。只有当索引顺序和order by字句的顺序完全一致，并且所有列的排序方向都一样时，mysql才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引排序。order by子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，mysql都需要执行排序操作，而无法利用索引排序。

注意：有一种情况下order by子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果where子句或者join子句对这些列指定了常量，就可以”弥补“索引的不足。

七、压缩（前缀压缩）索引

    MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大的提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。

    MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同前缀部分，把这部分存储起来即可。

    压缩块使用了更少的空间，代价是某些操作可以更慢。因为每个值得压缩前缀都依赖前面的值，索引MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描--例如order by desc--就不是很好了。

    测试表明，对于cpu密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在cpu内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是I/O密集型应用，对某些查询带来的好处会比成本多很多。

八、冗余和重复索引

    mysql允许在相同列上创建多个索引，无论是有意还是无意的。mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个的进行考虑，这回影响性能。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

   冗余索引和重复索引有一些不同，如果创建了索引（a,b），在创建索引（a)就是冗余索引，因为这是前一个索引的前缀索引。因为索引（a,b）也可以当做索引（a)来使用。但是如果在创建索引（b,a)，则不是冗余索引，索引（b）也不是，因为b不是索引（a,b）的最左前缀列。另外，其他不同类型的索引也不会是B-Tree索引的冗余索引，而无论覆盖索引列是什么。

    冗余索引通常发生在添加新索引的时候。大多数情况下都不需要冗余索引，应该尽可能扩展已有的索引而不是新创建索引。但也有时候处于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询性能。

九、索引和锁

    索引可以让查询锁定更少的行。如果你得查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；其次，锁定超过需要的行会增加锁争并减少并发性。

    InnoDB只有在访问行的时候才会对其加锁，而索引能减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在innoDB检索到数据并返回给服务器层以后，mysql服务器才能应用where子句。这时已经无法避免锁定行了；InnoDB已经锁住了这些行，到适当的时候才释放。在mysql5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早期的mysql版本中，InnoDB只有在事务提交后才能释放掉锁。

    关于InnoDB、索引和锁有一些很少有人知道的细节：InnoDB在二级索引上使用共享锁，但访问主键索引需要排他锁。这消除了使用覆盖索引的可能性，并且使得select for update 比lock in share mode或非锁定查询要慢很多。