MySQL 优化 —— MySQL 如何使用索引

引言

本文翻译自MySQL 官网 ：How MySQL Uses Indexes ，MySQL 版本 5.7 。

提升 SELECT 操作性能最好的方式就是在查询的一列或多列上建立索引。索引的行为类似指向表数据的指针，可以让查询能够快速判断哪个记录满足 WHERE 子句中的条件，然后取得这些记录的其他字段的值。所有的 MySQL 数据类型都支持索引。

虽然在可能用于查询的所有字段上都建立索引的做法是非常诱人的，但是不必要的索引既浪费存储空间，同时也浪费了MySQL 决定用哪个索引的时间。索引也会增加 insert、update、delete 等更新操作的开销，因为每个索引都必须更新。你必须找到恰当的平衡点使用最理想的索引集合来实现快速的查询。

MySQL 如何使用索引

索引被用于快速查找特定的列值对应的记录。没有索引，MySQL 就必须得从表的第一行开始，然后读取整张表才能找到符合条件的记录。表越大，花费的时间就越多。如果表里有一个正好适合查询情况的索引，MySQL 就可以快速在表中确定对应的位置而不需要搜索所有数据。这比连续读取每一行要快不少。

绝大多数MySQL 索引（PRIMARY KEY，UNIQUE ，INDEX  和 FULLTEXT）都以 B 树的形式存储。例外的情况：空间数据类型（spatial data type）的索引使用 R 树，MEMORY 存储引擎也支持 hash 索引。InnoDB 使用倒排表（inverted lists）来实现 FULLTEXT 索引。

下面的讨论描述了索引使用的一般情况。关于 hash 索引的具体特征请移步至：https://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html （未来我会对该章进行单独翻译，并会更新此处的连接）

MySQL 会在以下操作中使用索引：

1、用于快速找到匹配 WHERE 子句的记录。

2、用于缩小数据检索范围。如果有多个索引可供选择，MySQL normally uses the index that finds the smallest number of rows (the most selective index). MySQL 通常会使用可以找到最小记录数的索引（最具选择性的索引）。

3、如果表有一个复合索引，那么索引中任何最左侧的前缀都可以被优化器使用。例如，如果你有一个三列复合索引如(col1, col2, col3) ，那么你有三种索引搜索的可选方案：(col1)、(col1,col2)、以及(col1,col2, col3)。

4、当执行连接查询时取得其他表中的记录，如果索引字段声明了同样的类型和大小，那么 MySQL 会更有效地利用该列上的索引。在这种语境下，VARCHAR 和 CHAR 如果大小一致，那么就可以认为是同种类型。例如， VARCHAR(10) 和 CHAR(10) 具有相同的大小，但是 VARCHAR(10) 和 CHAR(15) 就不是了。

对于比较非二进制字符串列值，两个列必须具有相同的字符集。例如，比较 utf8 的字段和 latin1 的字段就会影响到使用索引。

比较不同类型的字段（如字符串和时间类型或数值类型等），如果两个值不经过转换就无法直接比较的话，那么同样会无法使用索引。有一个给定的数值类型 1 ，可能会与'1'，'  1'，'00001' 或 '01.e1' 这样的字符串比较。那么这种情况就无法使用任何索引。

5、To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX()expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once. For example: 

SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;

（上面这段话我没有理解官方文档的意思，有能翻译的同学帮忙评论区留个言！非常感谢）

6、用于排序或分组已经使用索引的左前缀排好序或分好组的表（例如，ORDER BY key_part1, key_part2）。如果所有字段都使用 DESC ，那么索引就会以相反的顺序读取。

7、有些情况，优化器可以优化查询，不需要访问原始记录，就可以获取数据。（可以获取全部查询必要信息的索引叫做覆盖索引）如果查询列表只查询了那些包含在索引中的字段，那么查询的值可以以更快的速度从索引树中获取:

SELECT key_part3 FROM tbl_name
  WHERE key_part1=1

索引对于小表不那么重要，对于那些需要查询绝大多数行或全部行的大表也不那么重要。如果查询需要访问绝大多数记录，那么按序读取会比使用索引更快。连续的读取可以最小化磁盘搜索，即使并不是所有记录都需要查询。