MySQL Explain详解：利用执行计划优化SQL性能

使用场景与核心目标

当面对执行缓慢的SELECT语句或复杂的JOIN查询时，首要任务是了解MySQL服务器是如何处理这条SQL的。EXPLAIN的输出展示了查询处理的顺序、使用的索引以及预估的行数。其核心目标是：

• 确认是否使用了预期的索引进行查找。
• 发现导致全表扫描（ALL）的低效写法。
• 评估多表关联（JOIN）的顺序是否合理。

EXPLAIN输出字段深度解析

执行 EXPLAIN SELECT ... 后，返回的结果行按MySQL执行读取表的顺序排列。以下是关键字段的详细解读：

第1步：检查ID与Select Type

目的：理解查询的结构复杂度。

id：SELECT查询的序列号。Id越大优先级越高，如果相同则按从上到下顺序执行。这有助于判断子查询或派生表的层级关系。

select_type：标识查询的类型，常见值及其含义如下：

• SIMPLE：简单的SELECT查询，不使用UNION或子查询。
• PRIMARY：最外层的SELECT。
• UNION：UNION语句中第二个或随后的SELECT。
• SUBQUERY：子查询中的第一个SELECT。
• DERIVED：FROM子句中的子查询（衍生表）。

第2步：评估联接类型 (Type)

目的：判断访问数据的效率，这是性能优化的核心指标。效果从好到坏依次为：

1. system/const：系统级或常量级匹配，速度最快。通常主键或唯一索引被比较且仅有一行匹配。
2. eq_ref：对于前一个表的每一行组合，当前表只读取一行。这是最好的非const连接类型，常用于主键或唯一索引的连接。
3. ref：对于前一个表的每一行，在当前表中查找所有匹配索引值的行。适用于普通索引或索引的最左前缀。
4. range：仅检索给定范围内的行，利用索引快速定位。常见于 BETWEEN、=、IN 等操作符。
5. index：遍历整个索引树。虽然比全表扫描快，但依然较低效。
6. ALL：全表扫描。必须逐行检查以找到匹配记录，性能极差，是优化的主要排除对象。

理想情况下，查询应至少达到 RANGE 级别，最好能达到 REF 或更高。

第3步：分析索引使用情况 (Key & Possible Keys)

目的：确认优化器实际选择了哪个索引。

• possible_keys：查询可能用到的索引列表。若为 NULL 说明没有相关索引可用。
• key：MySQL实际决定使用的索引。
• key_len：使用的索引长度。它能反映在复合索引中，MySQL到底使用了哪些列。长度越短说明筛选性越好；若为NULL则表示未使用该索引。

第4步：查看Extra附加信息

目的：发现潜在的额外开销。

• Using filesort：需要对结果进行额外排序。这通常意味着ORDER BY无法利用索引，需要优化索引结构。
• Using temporary：使用了临时表来保存中间结果，常见于GROUP BY和ORDER BY混用的情况，性能损耗较大。
• Using where：存储引擎返回的记录不符合WHERE条件，由MySQL Server层过滤。如果 type 为 ALL 且出现此提示，通常表示需增加索引。
• Using index：执行覆盖索引查询，无需回表，性能极佳。

实战：基于EXPLAIN的多表查询优化案例

下面通过一个多表JOIN的例子，展示如何根据EXPLAIN结果进行针对性优化。

原始问题分析

假设我们有以下查询语句：

EXPLAIN SELECT tt.ticketnumber, tt.timein, et.country 
FROM tt, et, et AS et_1, do 
WHERE tt.submittime IS NULL 
  AND tt.actualpc = et.employid 
  AND tt.assignedpc = et_1.employid 
  AND tt.clientid = do.custnmbr;

初始EXPLAIN结果显示，所有表的 Type 均为 ALL（全表扫描）。由于字段类型长度不一致（如 char(10) vs varchar(15)），MySQL无法有效利用索引，导致笛卡尔积式的灾难性性能表现。

优化步骤演示

第1步：统一字符集长度定义

MySQL在进行字符串比较时，如果字段长度定义不一致（例如 char 和 varchar 长度不同，或者长度差异大），可能导致无法使用索引。

将 tt.actualpc 的长度调整为与 et.employid 一致（假设为15）：

ALTER TABLE tt MODIFY actualpc VARCHAR(15);

再次执行EXPLAIN，部分表的连接类型变为 eq_ref 或 ref，扫描行数大幅下降。

第2步：修复剩余字段差异

继续解决 assignedpc 和 clientid 的长度不一致问题：

ALTER TABLE tt MODIFY assignedpc VARCHAR(15), MODIFY clientid VARCHAR(15);

第3步：更新统计信息与验证

有时数据分布不均匀会导致MySQL优化器选择次优路径。强制刷新表的统计信息，让优化器做出更准确的判断：

ANALYZE TABLE tt;

最终优化后的EXPLAIN结果中，连接类型主要呈现为 eq_ref 和 ref，彻底消除了 ALL（全表扫描），查询效率显著提升。

总结

MySQL EXPLAIN是SQL优化的“听诊器”。通过观察 Type 避免全表扫描，关注 Key 确认索引命中，警惕 Extra 中的 Filesore 和 Temporary 警告，可以系统化地提升数据库性能。在实际操作中，保持关联字段的数据类型和长度完全一致是使用索引的前提条件。

以上就是MySQL中EXPLAIN用法的详细内容，更多关于MySQL索引分析与SQL优化的资料请关注本站其它相关文章！