MySQL explain命令的作用

在MySQL中，EXPLAIN是分析SQL查询性能的核心工具，它可以模拟MySQL执行查询语句的过程，输出执行计划（Execution Plan），帮助开发者了解MySQL如何解析SQL、是否使用索引、表的连接顺序等关键信息，从而定位性能瓶颈并优化查询。

一、基本用法

在SELECT、DELETE、UPDATE语句前加上EXPLAIN关键字，执行后即可得到该语句的执行计划。例如：

-- 分析单表查询
EXPLAIN SELECT * FROM employees WHERE department_id = 3 AND salary > 5000;

-- 分析多表关联查询
EXPLAIN SELECT e.name, d.department_name 
FROM employees e 
JOIN departments d ON e.department_id = d.id 
WHERE e.hire_date > '2020-01-01';

执行后，MySQL会返回一个表格，包含12个字段（不同MySQL版本可能略有差异），每个字段都反映了查询执行的关键细节。

二、核心字段详解

以下是EXPLAIN输出中最关键的字段及含义（按重要性排序）：

1. type：访问类型（最核心字段）

表示MySQL如何访问表中的数据（即如何查找行），直接决定查询效率。从优到劣的顺序为：
system > const > eq_ref > ref > ref_or_null > range > index > ALL

• system：表中只有一行数据（如系统表），是const的特例，性能最优。
• const：通过主键或唯一索引查询，最多匹配一行数据（如WHERE id=1），速度极快。
• eq_ref：多表关联时，被关联表通过主键或唯一索引匹配，每行只匹配一次（如JOIN条件为a.id = b.a_id，且b.a_id是主键）。
• ref：通过非唯一索引查询，可能匹配多行（如WHERE department_id=3，department_id是普通索引）。
• range：索引范围查询（如WHERE id BETWEEN 1 AND 10、WHERE age > 30），只扫描索引的某一范围。
• index：扫描整个索引树（全索引扫描），比ALL好（索引文件通常比数据文件小），但仍需优化。
• ALL：全表扫描（Full Table Scan），逐行检查所有数据，性能最差，必须避免（通常是因为没有使用索引）。

2. key：实际使用的索引

• 显示MySQL实际选择的索引（若为NULL，表示未使用任何索引）。
• 若possible_keys（可能使用的索引）有值但key为NULL，说明索引未被使用（可能因索引失效，如违反最左原则、使用函数操作索引列等）。

3. rows：预估扫描行数

• MySQL预估需要扫描的行数（非精确值），行数越少，查询效率越高。
• 该值受表统计信息影响，若统计信息过时，可能不准确（可通过ANALYZE TABLE table_name更新统计信息）。

4. Extra：额外信息（重要优化线索）

包含MySQL执行查询的额外细节，常见关键值及含义：

• Using index：使用了覆盖索引（索引包含查询所需的所有列），无需回表查询数据行，性能极佳。
• Using where：MySQL使用WHERE条件过滤行（可能是全表扫描后过滤，或索引扫描后过滤）。
• Using filesort：MySQL需要对结果进行排序，但无法利用索引排序，只能在内存/磁盘中完成排序（耗时，需优化，通常是因为ORDER BY的列未建索引）。
• Using temporary：MySQL需要创建临时表存储中间结果（如GROUP BY未使用索引、DISTINCT处理等），临时表会消耗内存/磁盘，性能差。
• Using join buffer：多表关联时未使用索引，MySQL使用连接缓冲区存储中间结果，需优化关联条件的索引。
• Range checked for each record (index map: N)：没有可用的索引，MySQL为每行数据检查是否有合适的索引，性能极差。

5. 其他重要字段

• id：查询中每个SELECT子句的唯一标识（用于多表关联或子查询），id相同表示同一层级查询，id越大优先级越高（先执行）。
• select_type：查询类型，如SIMPLE（简单查询，无子查询/关联）、PRIMARY（主查询）、SUBQUERY（子查询）、DERIVED（派生表）等。
• table：当前行对应的表名（或派生表的别名，如derived2）。
• possible_keys：MySQL认为可能使用的索引（供参考，不一定实际使用）。
• key_len：实际使用的索引长度（字节），长度越短，索引效率越高（可判断联合索引使用了前缀几列）。
• ref：表示哪些列或常量与key配合使用来查询数据（如const表示使用常量，employees.department_id表示使用其他表的列）。

三、实战分析示例

通过一个案例理解如何用EXPLAIN优化查询：

原始查询（性能差）

-- 查询部门3中工资>5000的员工
SELECT name, salary FROM employees WHERE department_id = 3 AND salary > 5000;

EXPLAIN输出关键信息

分析问题

• type=ALL：全表扫描，未使用索引；
• key=NULL：无索引被使用；
• Extra=Using where：全表扫描后过滤数据，效率低。

优化方案

创建联合索引(department_id, salary)（匹配查询条件的顺序）：

CREATE INDEX idx_dept_salary ON employees (department_id, salary);

优化后EXPLAIN输出

优化效果

• type=range：使用索引范围查询，无需全表扫描；
• key=idx_dept_salary：实际使用了创建的索引；
• rows=100：扫描行数从10000减少到100，性能大幅提升。

四、常见优化场景（基于EXPLAIN）

1. type=ALL或index：检查是否缺少索引，或索引失效（如WHERE中使用!=、NOT IN、IS NULL等可能导致索引失效的操作）。
2. Extra=Using filesort：为ORDER BY的列创建索引（或包含在联合索引中），利用索引排序避免文件排序。
3. Extra=Using temporary：优化GROUP BY或DISTINCT，确保分组/去重的列有索引，避免创建临时表。
4. key为NULL但possible_keys有值：检查是否违反联合索引的“最左原则”（如联合索引(a,b)，查询WHERE b=1无法使用索引），或索引列被函数操作（如WHERE SUBSTR(name,1,3)='abc'）。