MySql性能调优之EXPLAIN

一、准备与基本检查

• 确认 MySQL 版本（不同版本 EXPLAIN 输出字段略有差异）。
• 在测试环境或对真实数据进行尽量相近的测试，避免线上直接改动带来风险。
• 将慢查询开启（slow_query_log）并收集慢查询日志，或通过应用/监控捕获慢 SQL。

二、获取执行计划

1. 使用 EXPLAIN 检查单条查询：
   • EXPLAIN [FORMAT=JSON] SELECT ... 可获得更详尽的 JSON 计划（推荐用于深入分析）。
   • 普通 EXPLAIN 返回的列常见包括: id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, Extra。
2. 使用 EXPLAIN ANALYZE（MySQL 8.0+）：
   • EXPLAIN ANALYZE 实际执行查询并返回实际时间与行数，比纯静态 EXPLAIN 更准，但会执行查询（注意副作用）。

三、逐项解读关键字段与定位问题

字段	说明	优化意义
id	查询序列号，相同id按顺序执行，不同id值越大优先级越高	定位复杂查询执行顺序，识别子查询层级
select_type	查询类型	识别简单查询或复杂子查询结构
table	访问的表名	确定查询涉及的表对象，含别名和<unionM,N>等特殊标记
partitions	匹配的分区	分区表查询时显示命中的分区名
type（关键）	访问类型，性能排序：system > const > eq_ref > ref > range > index > ALL	SQL优化的核心指标，决定数据检索效率
possible_keys	可能使用的索引	检查索引设计是否合理
key（关键）	实际使用的索引	验证优化器最终选择的索引
key_len(关键)	索引使用的字节数	计算复合索引中使用到的字段长度，验证索引利用率
ref	索引关联的列或常量	显示与索引比较的列或常量，检查关联条件
rows（关键）	预估扫描行数	数值越小性能越好，大数值需优化
filtered	存储引擎层过滤后的剩余比例	查询效率核心指标，100%表示完美过滤
Extra（关键）	额外执行信息	揭示潜在性能问题（如临时表/文件排序）

• id / select_type：判断是否有子查询、派生表、UNION、复杂子查询导致多次扫描或排序。
• table：被访问的表名，确认是预期表。
• type（访问类型，重要）：
  • 最优到最差： system > const > eq_ref > ref > range > index > ALL
  • 如果出现 ALL，说明全表扫描（可能是慢点）。
• possible_keys / key / key_len：
  • possible_keys：可能用到的索引；key：实际用到的索引。若 possible_keys 不为空但 key 为 NULL，说明优化器选择了全表扫描，可能是统计信息或索引选择问题。
  • key_len：使用索引的字节长度（可判断是否使用前缀索引或完整索引列）。
• ref：显示哪个列或常数与索引列比较。
• rows：估算需要读取的行数（越大越慢）。
• Extra：非常重要，常见值：
  • Using filesort：排序不能用索引，需文件排序（可能慢）。
  • Using temporary：使用临时表（GROUP BY、DISTINCT、ORDER BY 等可能导致）。
  • Using index：覆盖索引（只访问索引即可），通常性能好。
  • Using where：有 WHERE 过滤（正常）。
  • Impossible WHERE：查询条件永远不成立（返回空）。
  • Using MRR：多范围读取优化（InnoDB 相关）。

四、常见性能问题与对应优化策略

1. 全表扫描（type = ALL）

   • 检查是否缺少合适索引；为 WHERE / JOIN / ORDER BY / GROUP BY 用到的列创建索引。
   • 注意索引选择性：高选择性列更适合做索引；复合索引需考虑列顺序（最左前缀原则）。
   • 避免在索引列上使用函数/表达式或隐式类型转换（会导致索引失效）。

2. 未使用期望索引（possible_keys 有但 key 为 NULL）

   • 检查统计信息是否过旧：执行 ANALYZE TABLE 更新统计信息。
   • 考虑 FORCE INDEX（临时用于确认或绕过优化器选择），但普通使用应靠调整索引/查询结构。
   • 检查索引列类型是否与比较列类型一致。

3. 覆盖索引（Using index / Using index for group-by）

   • 如果可以，设计索引覆盖查询（SELECT 中的列都包含在索引里），避免回表（减少 IO）。

4. 排序/文件排序（Using filesort / Using temporary）

   • 为 ORDER BY / GROUP BY 使用合适的复合索引，注意排序方向与列顺序。
   • LIMIT 与 ORDER BY 一起用时，可通过索引用到排序避免 filesort（如 WHERE + ORDER BY 对应索引最左前缀）。
   • 对大结果集，考虑分页策略（避免大偏移 OFFSET），或用索引范围分页（keyset pagination）。

5. 大量回表（Using where 且没有 Using index）

   • 回表（访问主键行）开销大，尝试覆盖索引或减少查询列。
   • 如果需要多列，考虑调整索引使其包含所需列（覆盖索引或 INCLUDE 在支持的 DB 中）。

6. 关联（JOIN）效率问题

   • 优化 JOIN 顺序与驱动表（MySQL 优化器会选择，但通过索引与条件可影响）。
   • 确保用于 JOIN 的列有合适索引（通常在被驱动表上建立索引）。
   • 用 EXPLAIN 查看 join 的 type（尽量避免 ALL），尽量达到 ref / eq_ref 类型。

7. 子查询与派生表（derived tables）

   • 子查询（尤其非关联子查询）可改写为 JOIN 或使用 EXISTS。
   • 派生表（子查询作为表）默认会被临时表化，可能无法使用外层索引（视 MySQL 版本）。MySQL 8.0 在很多情况下能改进。
   • 使用 IN/EXISTS/ANY 根据场景选择更优方案。

8. 索引设计注意事项

   • 复合索引遵循最左前缀原则；将最常用于过滤/排序的列放前面。
   • 避免冗余或过多索引（写开销和维护成本）。
   • 对低基数列（如性别）单独建索引通常没太大效益，可通过组合索引提高选择性。
   • 考虑前缀索引（字符串）节省空间，但会影响索引选择性与排序。

五、使用 EXPLAIN 的进阶技巧

• EXPLAIN FORMAT=JSON 输出解析：查看 "cost_info", "used_columns", "attached_condition" 等字段，定位估算成本和列使用情况。
• EXPLAIN ANALYZE 获取真实执行时间，结合 PROFILE（已废弃）或 Performance Schema 分析锁等待、IO 等。
• 用 SHOW STATUS 与 Performance Schema 查看表/索引访问、IO、锁、临时表使用等系统指标。
• 结合 pt-query-digest、慢查询日志、监控（Prometheus/Grafana）定位热点 SQL 与频率。

六、迭代优化流程（实操流程）

1. 收集慢 SQL 与执行频率（优先优化高频/高耗的）。
2. 在测试环境用 EXPLAIN（JSON）和 EXPLAIN ANALYZE 分析执行计划与实际耗时。
3. 识别瓶颈（全表扫描、filesort、临时表、大回表、JOIN 异常等）。
4. 设计改进方案：调整索引、修改 SQL（重写 JOIN/子查询、限制列、分页优化）、更新统计信息、调整服务器参数（如 sort_buffer_size、join_buffer_size，但优先软件层面优化）。
5. 在测试环境验证改进效果（EXPLAIN、EXPLAIN ANALYZE、实际运行时间、IO/CPU 使用）。
6. 逐步上线并监控，回滚策略与变更审批。

七、常见调优误区

• 盲目增加内存参数（如 sort_buffer_size）而不看 SQL 结构。
• 为每个慢查询都强制 FORCE INDEX，而不检查根因。
• 过度索引导致写性能下降和更高存储成本。
• 忽视统计信息和数据分布变化（需定期 ANALYZE TABLE/OPTIMIZE）。

八、实用命令汇总

• EXPLAIN SELECT ...
• EXPLAIN FORMAT=JSON SELECT ...
• EXPLAIN ANALYZE SELECT ... （8.0+）
• SHOW INDEX FROM table;
• ANALYZE TABLE table; OPTIMIZE TABLE table;
• SHOW STATUS LIKE 'Handler%';（查看访问计数）
• SHOW ENGINE INNODB STATUS\G（查看锁、事务信息）