MySQL 高性能索引

  • MySQL 高性能索引
  • 索引小技巧 P183
  • 1.支持多种过滤条件
  • 2.避免多个范围条件
  • 3.优化排序
  • 维护索引和表 P187
  • 1.找到并修复损坏的表
  • 2.更新索引统计信息
  • 3.减少索引和数据的碎片
  • 总结


MySQL 高性能索引

索引小技巧 P183

1.支持多种过滤条件

当出现多个范围条件时，将其中一个范围查询使用in，例如sex、age，年龄一般都需要范围查询，所以创建索引时会优先将age放在最后，当我们要查询sex为1、2也就是男和女并且age范围在18-24时，直接写’where age between 18 and 24’会导致没有走索引，所以此时可以写’where sex in (1,2) and age between 18 and 24’来避免不走索引的情况；

2.避免多个范围条件

在上一个条件下，若sex也是一个范围查询呢，相同的原理将范围条件转换为in，可以避免最左匹配原则导致索引失效，但这在有些情况下并不永远都是一个好的解决方案，如四个范围查询，分别会出现3、4、2、时间范围时，优化器则会转换成4 * 3 * 2 = 24种组合，执行计划需要检查WHERE子句中所有的24种组合。现在24种可能并不是很夸张，若是上千个则需要特别小心。

where ele_color in ('brown', 'blue', 'hazel')
	and hair_color in('black', 'red', 'blonde', 'brown')
	and sex in('M', 'F')
	and last_online > DATE_SUB(NOW(), INTERVAL 7 DAY)
	and age BETWEEN 18 and 25

注意1、2案例都是将范围条件查询转化为等值查询，多个等值条件查询，后面就可以再跟着范围条件查询；

像sex这种种类比较少的字段一般不会放在索引的前方，如果sex用的比较少那么就可以去除，这里考虑的场景是大部分都要根据性别查询的交友样例；就算少部分不使用sex查询的语句，也可以使用in的小技巧来避免索引失效的状况。

3.优化排序

使用文件排序对小数据集是很快的，但如果一个查询匹配的结果有上百万行的话会怎样，例如如果where子句只有sex列，如何排序？

对于选择性非常低的列，可以增加一些特殊的索引来做排序。例如，可以创建(sex,rating)索引用于下面的查询：

SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 10;

这个查询同时使用了ORDER BY和LIMIT，如果没有索引的话会很慢。

使用limit进行分页，并且翻页到比较靠后时查询可能非常慢，无论如何创建索引，这种查询都是个非常严重的问题。因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。

解决方案：

  • 反范式化、预先计算和缓存
  • 限制用户能够翻页的数量
  • 使用延迟查询，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行:
select <cols> from profiles inner join (
 select <primary key cols> from profiles
  where x.sex = 'M' order by rating LIMIT 100000,10
) AS x USING(<primary key cols>);

维护索引和表 P187

1.找到并修复损坏的表

损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，如果遇到了古怪的问题——可以尝试运行 CHECK TABLE来检查是否发生了表损坏（有些存储引擎不支持该命令）。CHECK TABLE通常能够找出大多数的表和索引的错误。

可以使用REPAIR TABLE命令来修复损坏的表，但同样不是所有存储引擎都支持该命令。如果存储引擎不支持，也可通过一个不做任何操作（no-op）的ALTER操作来重建表，例如修改表的存储引擎为当前的引擎：

ALTER TABLE innodb_tbl ENGINE=INNODB;

2.更新索引统计信息

命令ANALYZE TABLE来生成或者更新统计信息，可以使用SHOW INDEX FROM命令来查看索引的基数（Cardinality）。

SHOW INDEX FROM database_name.table_name;

这个命令输出了很多关于索引的信息，在MySQL手册中对上面每个字段的含义都有详细的解释。这里需要特别注意的是索引列的基数（Cardinality），其显示了存储引擎估算索引列有多少个不同的取值。

InnoDB在打开某些INFORMATION_SCHEMA表，或者使用SHOW TABLE STATUS和SHOW INDEX等一些情况下会触发索引统计信息的更新。如果服务器上有大量的数据，这可能是个很严重的问题，尤其是I/O比较慢的时候。只用SHOW INDEX查看索引统计信息，就一定会触发统计信息的更新。可以关闭innodb_stats_on_metadata参数来避免上面提到的问题。

3.减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。

表的数据存储也可能碎片化。然而，数据存储的碎片化比索引更加复杂。有三种类型的数据碎片 。

  • 行碎片
  • 行间碎片
  • 剩余空间碎片

当出现这种情况导致查询响应速度下降时，可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。对于不支持OPTIMIZE TABLE的存储引擎，也可以通过ALTER TABLE操作来重建表：

ALTER TABLE <table> ENGINE=<engine>;

总结

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