「Mysql索引原理（十八）」总结

通过本专题可以看到，索引是一个非常复杂的话题！MySQL和存储引擎访问数据的方式，加上索引的特性，使得索引成为一个影响数据访问的有力而灵活的工作(无论数据是在磁盘中还是在内存中)。 在MySQL中，大多数情况下都会使用B-Tree索引。其他类型的索引大多只适用于特殊的目的。如果在合适的场景中使用索引，将大大提高查询的响应时间。最后回顾一下这些特性以及如何使用B-Tree索引。 在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住:

1. 单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/O要快很多,不过这点仍然成立)。如果服务器从存储中读取一个数据块只是为了获取其中一行,那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。
2. 按顺序访问范围数据是很快的,这有两个原因。第一，顺序IO不需要多次磁盘寻道，所以比随机IO要快很多(特别是对机械硬盘)。第二,如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GR0UP BY查询也无须再做排序和将行按组进行聚合计算了。
3. 索引覆盖査询是很快的。如果一个索引包含了査询需要的所有列,那么存储引擎就不需要再回表查找行。这避免了大量的单行访问,而上面的第1点已经写明单行访是很慢的。

总的来说,编写査询语句时应该尽可能选择合适的索引以避免单行查找、尽可能地使用数据原生顺序从而避免额外的排序操作,并尽可能使用索引覆盖查询。这与本章开头提到的 Lahdenmak和 Leach的书中的“三星”评价系统是一致的。

如果表上的每一个查询都能有一个完美的索引来满足当然是最好的。但不幸的是,要这么做有时可能需要创建大量的索引。还有一些时候对某些查询是不可能创建一个达到“三星”的索引的(例如查询要按照两个列排序,其中一个列正序,另一个列倒序)。这时必须有所取舍以创建最合适的索引,或者寻求替代策略(例如反范式化,或者提前计算汇总表等)。

理解索引是如何工作的非常重要,应该根据这些理解来创建最合适的索引,而不是根据一些诸如“在多列索引中将选择性最高的列放在第一列”或“应该为 WHERE子句中出现的所有列创建索引”之类的经验法则及其推论。

那如何判断一个系统创建的索引是合理的呢?一般来说,我们建议按响应时间来对查询<200进行分析。找出那些消耗最长时间的査询或者那些给服务器带来最大压力的查询(第3章中介绍了如何测量),然后检査这些査询的 schema、SQL和索引结构,判断是否有查询扫描了太多的行,是否做了很多额外的排序或者使用了临时表,是否使用随机IO访问数据,或者是有太多回表查询那些不在索引中的列的操作。

如果一个查询无法从所有可能的索引中获益,则应该看看是否可以创建一个更合适的索引来提升性能。如果不行,也可以看看是否可以重写该査询,将其转化成一个能够高效利用现有索引或者新创建索引的查询。这也是下一章要介绍的内容。

如果分析不能找出有问题的查询呢?是否可能有我们没有注意到的“很糟糕”的查询,需要一个更好的索引来获取更高的性能?一般来说,不可能。对于诊断时抓不到的查询,那就不是问题。但是,这个查询未来有可能会成为问题,因为应用程序数据和负载都在变化。