如何使用 MySQL 的 EXPLAIN 语句进行查询分析？

回答重点

explain 主要用来 SQL 分析，它主要的属性详解如下：

id：查询的执行顺序的标识符，值越大优先级越高。简单查询的 id 通常为 1，复杂查询（如包含子查询或 UNION）的 id 会有多个。
select_type（重要）：查询的类型，如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
table：查询的数据表。
type（重要）：访问类型，如 ALL（全表扫描）、index（索引扫描）、range（范围扫描）等。一般来说，性能从好到差的顺序是：const > eq_ref > ref > range > index > ALL。
possible_keys：可能用到的索引。
key（重要）：实际用到的索引。
key_len：用到索引的长度。
ref：显示索引的哪一列被使用。
rows（重要）：估计要扫描的行数，值越小越好。
filtered：显示查询条件过滤掉的行的百分比。一个高百分比表示查询条件的选择性好。
Extra（重要）：额外信息，如 Using index（表示使用覆盖索引）、Using where（表示使用 WHERE 条件进行过滤）、Using temporary（表示使用临时表）、Using filesort（表示需要额外的排序步骤）。

type 详解：

system：表示查询的表只有一行（系统表）。这是一个特殊的情况，不常见。
const：表示查询的表最多只有一行匹配结果。这通常发生在查询条件是主键或唯一索引，并且是常量比较。
eq_ref：表示对于每个来自前一张表的行，MySQL 仅访问一次这个表。这通常发生在连接查询中使用主键或唯一索引的情况下。
ref：MySQL 使用非唯一索引扫描来查找行。查询条件使用的索引是非唯一的（如普通索引）。
range：表示 MySQL 会扫描表的一部分，而不是全部行。范围扫描通常出现在使用索引的范围查询中（如 BETWEEN、>, <, >=, <=）。
index：表示 MySQL 扫描索引中的所有行，而不是表中的所有行。即使索引列的值覆盖查询，也需要扫描整个索引。
all（性能最差）：表示 MySQL 需要扫描表中的所有行，即全表扫描。通常出现在没有索引的查询条件中。

扩展知识

实际 explain 例子

1）创建 employees 表

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE,
    INDEX (department_id)
);

我们要执行以下查询来查找部门 ID 为 5 且薪水在 50000 到 100000 之间的员工，并按薪水降序排序：

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;

2）我们先使用 explain 分析计划进行分析：

EXPLAIN SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 5 AND salary BETWEEN 50000 AND 100000
ORDER BY salary DESC;

输出结果如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	ref	department_id	department_id	4	const	5000	20.00	Using where; Using filesort

3）分析执行计划

从执行计划中看出，type 为 ref，表示使用了 department_id 索引，这是个非唯一索引。key 为 department_id 这个索引，而且 rows 为 5000，表示扫描了 5000 行数据。从 Extra 看出在应用 WHERE 条件后，还需要进行文件排序来满足 ORDER BY 子句。

4）找出问题

尽管查询使用了索引，但由于索引不完全覆盖查询的条件和排序，查询需要进行额外的文件排序。这可能会导致性能瓶颈，特别是在结果集较大时。

5）优化解决它！

创建复合索引

创建一个包含 department_id 和 salary 的复合索引，这样可以覆盖查询的 WHERE 和 ORDER BY 条件：

CREATE INDEX idx_department_salary ON employees (department_id, salary);

复合索引可以使查询在扫描 department_id 列时，同时按 salary 列排序，避免额外的文件排序。

再次执行计划分析

优化后的 EXPLAIN 输出如下：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	range	idx_department_salary	idx_department_salary	5	NULL	500	100.00	Using where

6）分析优化后的结果

从新的 EXPLAIN 输出中可以看出：

type: range，表示使用范围扫描，这是个相对高效的访问类型。
key: idx_department_salary，表示实际使用了复合索引。
rows: 500，估计扫描的行数减少了，因为索引更精确地覆盖了查询条件。
Extra: 仅显示 Using where，不再需要文件排序，因为索引已经覆盖了排序需求。