29. 如何使用EXPLAIN命令来分析MySQL查询中的索引使用情况？

PangHu大约 3 分钟

使用 EXPLAIN 命令可以帮助你分析 MySQL 查询的执行计划，了解查询中使用了哪些索引，以及 MySQL 是如何处理查询的。通过 EXPLAIN，你可以查看查询的执行计划，包括表扫描方式、使用的索引、行数估计等信息，从而优化查询性能。

EXPLAIN 命令的基本用法是在查询语句前加上 EXPLAIN 关键字。例如：

EXPLAIN SELECT * FROM employees WHERE last_name = 'Doe';

执行后，MySQL 会返回一张表格，显示查询的执行计划。每一行对应查询中的一个表或子查询，列则展示了该查询如何处理数据。

EXPLAIN 输出包含多列信息，下面是其中一些关键列的解释：

列名	含义
id	查询中的每个选择部分的标识符。标识查询中执行的顺序。
select_type	查询的类型（例如 SIMPLE、PRIMARY、UNION、SUBQUERY 等）。
table	查询访问的表。
type	表示连接类型，说明MySQL如何查找表中的行。重要的值包括：
	- ALL：全表扫描。
	- index：扫描索引（比全表扫描效率高）。
	- range：索引范围扫描。
	- ref：使用非唯一索引查找匹配的行。
	- eq_ref：使用唯一索引查找单行数据。
	- const/system：表示查询最多有一个匹配行，可以认为是常量。
possible_keys	查询中可能使用的索引。
key	查询实际使用的索引。如果为空，则未使用索引。
key_len	MySQL 使用的索引部分的长度。这个值表示索引中实际用来查找的字节数。
ref	用于查找索引列的值。如果使用了多列索引，`ref` 列显示的是用来匹配索引的列或常量。
rows	MySQL 估计需要读取的行数。通常值越小越好。
filtered	该表中返回结果的行占表中满足条件的行的百分比。表示条件过滤效果。
Extra	其他额外信息。例如 `Using index` 表示使用了覆盖索引，`Using where` 表示使用了 WHERE 过滤条件。

假设有以下查询：

EXPLAIN SELECT * FROM employees WHERE last_name = 'Doe';

假设执行 EXPLAIN 后得到的输出如下：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	employees	ref	last_name_idx	last_name_idx	102	const	10	Using index

解释：

type 列为 ref：表示使用了非唯一索引查找匹配的行。MySQL 通过 last_name_idx 索引查找与 last_name = 'Doe' 匹配的行。
key 列为 last_name_idx：实际使用的索引为 last_name_idx。
key_len 为 102：表示索引中使用了102个字节来查找匹配的行。
rows 列为 10：表示 MySQL 估计需要扫描10行来满足查询条件。
Extra 列为 Using index：表示查询中使用了覆盖索引（即只访问索引而不访问表数据行）。

查看 type 列：理想情况下，type 的值应该是 ref、eq_ref、range 或 const。如果是 ALL（全表扫描），则可能需要创建合适的索引以优化查询。
检查 key 列：确保查询使用了合适的索引。如果 key 列为空，表示未使用索引，可能需要检查是否有适合的索引。
减少 rows 列的值：rows 值越小，查询性能越好。可以通过优化查询条件或调整索引来减少这个值。
注意 Extra 列中的信息：例如，Using temporary 表示使用了临时表，Using filesort 表示使用了文件排序，这些通常是需要优化的信号。

使用 EXPLAIN 命令，可以详细分析 MySQL 查询的执行计划，了解索引的使用情况，并找到潜在的性能瓶颈。通过这种分析，可以采取相应措施来优化查询，比如添加合适的索引、重构查询等，从而提高数据库的执行效率。