高性能MYSQL（学习笔记）-索引篇4

索引案例学习

理解索引的最好办法是结合示例，这里准备了一个索引的案例。

假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色等等。网站必须支持上面的这些特征的各种组合来搜索用户，还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序对结果进行限制。

支持多种过滤条件

Countr列的选择性不高，但是可能很多查询都会用到，sex列的选择性很低，但是很多查询也会用到。所以考虑到使用的频率，还是建议创建不同组合索引的时候将（sex，country）列作为前缀。选择的理由：

几乎所有的查询都会用到sex列，如果某个查询不限制性别，我们可以通过新增AND SEX IN （‘m’,’f’）来让MYSQL选择索引，虽然这样写没有过滤任何行和不加时候返回结果一样，但是加上去后，MYSQL才能够匹配索引的最左前缀。这个诀窍在这类场景中非常有效，但是仅限于IN（）列表比较短。

原则：考虑表上的所有选项。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化，如果发现某些查询需要创建新的索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否能优化原来的查询。应该同时优化查询和索引找到最佳平衡！

接下来考虑哪些where条件的组合，并需要了解哪些组合在没有合适索引的情况下会变得很慢。（sex，country，age）上的索引就是一个明显的选择，还有可能是（sex，country，region，age）和（sex，country，region，city，age）这样的组合索引。可以使用IN（）技巧来避免同时需要不同的索引。而且我们把age 索引放到最后一列是有原因的，我们总是尽可能让mysql 使用更多的索引列，因为查询只能使用索引的最左前缀，直到遇到第一个范围查询。Age多半是范围查询，between 18 and 25.

原则：组合索引中，把范围索引放到最后一列，因为最左匹配原则。可以再索引中加入更多列，通过IN（）方式覆盖哪些不在where句子中的列。但是不可滥用。

避免多个范围条件

  Where eye_color in(‘brown’，‘blue’，‘Hazel’)

And hair_colorin(‘black’,’red’,’blonde’,’brown’)

And sex in(‘F’,’M’)

And last_online>DATE_SUB(NOW(),INTERVAL7 DAY)

And age between 18 and 25

什么是范围条件？ Wehre actor_id>12是范围条件查询，无法再使用范围后面的其他索引列了，where actor_idin(1,4,99)是多个等值查询。

优化排序：

当需要排序的时候，对于哪些选择性很低的列，可以增加一些特殊的索引来做排序，可以创建(sex,rating)索引用于一下查询：

Select cols from profiles where sex=’M’order by rating limit 10;

但是，当用户界面上需要翻页，并且比较靠后的时候会非常慢，随着偏移量的增加，mysql需要花费大量的时间来扫面需要丢弃的数据，反范式化、预先计算缓存可能是解决这类查询的仅有策略，有两个建议：1、限制用户的翻页数量；2、使用延迟关联，通过索引查询返回需要的主键，再根据这些主键关联原表获得需要的行，例如：

Select cols from profiles inner join(select<primary key cols> from profiles where x.sex=’M’ order by rating limit100000,10) as x using(<primary key cols>);

维护索引和表

找到并修复损坏的表参考Mysql部分，这里不是很懂。。。。

总结

MySQL索引是一个非常复杂的话题！MYSQL和存储引擎访问数据的方式，加上索引的特性，使得索引成为一个影响数据访问的有力而灵活的工作。

在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住：

1、  单行访问是很慢的。特别是在机械硬盘存储中（SSD的随机I/O要快很多，不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多的行。使用索引可以创建位置引用以提升效率。

2、  按顺序访问范围数据是很快的，这里有两个原因：第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是 对机械硬盘）第二，如果服务器能够按需要顺序读取数据，那么就不要在需要额外的排序操作，并且group by查询也无需再做排序和按行按组进行聚合计算了。

3、  索引覆盖查询是很快的，如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查询行。这避免了大量的单行访问。

总的来说，编写查询语句应该间可能的选择合适的索引来避免单行查询，尽可能的使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询，这个就是开头提出来的“三星”评价系统是一致的。

如何判断一个系统创建的索引是否合理：

建议按照响应时间来对查询进行分析，找出消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的 schema,SQL和索引结构，判断是否有查询扫描了太多的行是否做了太多额外的排序或者使用了临时表，是否使用随机I/O访问数据，或者返回了太多不需要的列操作。