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

痛定思痛。 2023-07-02 03:13 42阅读 0赞

# 引言 #

本文翻译自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][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()`][MIN] or [`MAX()`][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()`][MIN] or [`MAX()`][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

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

[How MySQL Uses Indexes]: https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html
[https_dev.mysql.com_doc_refman_5.7_en_index-btree-hash.html]: https://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html
[MIN]: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_min
[MAX]: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_max