深入解析 MySQL 执行计划：从 EXPLAIN 到实战优化

在 MySQL 性能优化领域，SQL 语句的执行效率往往是决定系统性能的关键因素。而精准分析 SQL 执行计划，则是揭开查询性能奥秘的核心手段。本文将结合具体案例，通过 EXPLAIN 工具剖析 SQL 执行逻辑，探讨索引选择、排序优化等核心问题，帮助开发者掌握从执行计划到实战优化的完整链路。

一、执行计划核心指标解析

在 MySQL 中，通过EXPLAIN（或DESC/DESCRIBE）语句可获取 SQL 的执行计划，其输出包含多个关键指标，这些指标是判断查询效率的重要依据：

1. possible_keys：潜在索引集合

该字段列出 MySQL 优化器认为可能适用的所有索引。需要注意的是，列出的索引未必都会被实际使用，优化器会根据统计信息（如索引基数、表数据量）选择最优索引。

2. key：实际使用的索引

若该字段不为NULL，表示优化器最终选择的索引。若预期索引未被使用，可能是由于索引选择性较差（如基数过低）或统计信息陈旧。

3. rows：预估扫描行数

此值为 MySQL 根据统计信息估算的扫描行数，是判断查询成本的核心指标。通常情况下，扫描行数越少，查询效率越高。

4. Extra：额外信息

该字段包含关键优化线索，例如：

• Using filesort：表示需要额外的文件排序，通常意味着排序操作未利用索引，需优化。
• Using temporary：使用临时表，可能影响性能，常见于复杂分组或多表连接场景。

二、案例分析：简单查询的执行计划优化

场景描述

现有表t1，包含字段id（主键）、r1、r2、r3、log_date，并建有索引idx_r1（单字段索引，r1）、idx_u1（联合索引，r1,r2,r3）。

原始查询：无排序的等值查询

EXPLAIN SELECT * FROM t1 WHERE r1 = 4\G

 

执行计划分析：

• possible_keys: idx_r1,idx_u1
• key: idx_u1
• rows: 29324
• Extra: NULL

关键点：

• 优化器选择联合索引idx_u1，但r1的基数仅为 5（通过INFORMATION_SCHEMA.STATISTICS查询可知），导致每个r1=4的匹配需扫描近 3 万行，索引选择性较差。

新增排序需求：引入Using filesort

当查询增加排序子句后：

EXPLAIN SELECT * FROM t1 WHERE r1 = 4 ORDER BY id DESC\G

 

执行计划变化：

• Extra: Using filesort
  问题：排序操作未利用索引，需额外的文件排序，可能导致性能下降。

优化尝试：添加覆盖排序的索引

为消除Using filesort，创建新索引idx_r1_id_desc（r1, id DESC）：

ALTER TABLE t1 ADD KEY idx_r1_id_desc (r1, id DESC);

 

再次执行计划：

• 优化器仍选择idx_u1，Extra仍为Using filesort。
  原因：优化器基于成本估算，认为使用原索引 + 文件排序的成本低于新索引的全扫描成本。

强制索引与成本对比

通过FORCE INDEX强制使用新索引：

 

EXPLAIN ANALYZE SELECT * FROM t1 FORCE INDEX(idx_r1_id_desc) WHERE r1 = 4 ORDER BY id DESC;

• 扫描行数：32176，成本：3434.35

EXPLAIN ANALYZE SELECT * FROM t1 WHERE r1 = 4 ORDER BY id DESC;

• 扫描行数：29324，成本：3149.15（含排序成本）

结论：原方案（idx_u1+ 排序）的总成本更低，新索引未带来实际优化效果，可删除。

三、优化实践：从执行计划到策略制定

1. 索引选择性优先原则

• 核心逻辑：索引基数（CARDINALITY）直接影响选择性，基数越低（如枚举值字段），索引价值越小。
• 操作建议：定期通过ANALYZE TABLE更新统计信息，避免优化器因陈旧数据做出错误选择。

2. 处理Using filesort的正确姿势

• 索引覆盖排序：确保排序字段在索引中，且顺序与排序方向一致（如ORDER BY id DESC对应索引包含id DESC）。
• 避免全字段查询：SELECT *会导致索引覆盖失效，建议仅查询必要字段。

3. 强制索引的适用场景

• 临时调试：验证特定索引的实际效果。
• 统计信息异常：当优化器因统计信息错误选择索引时，可临时干预。
• 注意事项：强制索引可能导致更差的性能，需结合EXPLAIN ANALYZE对比成本。

四、总结：执行计划分析的核心思维

1. 数据驱动优化：执行计划的本质是 MySQL 基于统计信息的决策结果，需优先关注索引基数、表数据量等核心指标。
2. 成本权衡意识：优化器的选择是 CPU、IO 等资源的综合成本考量，如 “扫描行数多但避免排序” 可能比 “扫描行数少但需排序” 更高效。
3. 业务逻辑联动：当索引优化到达瓶颈时（如案例中r1基数过低），需与业务方协作调整过滤条件或查询频率。

通过深入理解 EXPLAIN 的每个字段，结合实际案例的成本分析，开发者可系统化地定位 SQL 性能问题，制定精准的优化策略