一文掌握MySQL EXPLAIN：深度解析执行计划与优化实践

Asthenian
239 阅读6分钟

一、EXPLAIN工具核心价值

通过EXPLAIN命令可以获取MySQL优化器选择的执行计划，直观展示查询语句的执行路径。该工具能帮助我们：

  1. 发现全表扫描等低效操作
  2. 验证索引使用有效性
  3. 识别复杂查询的性能瓶颈
  4. 预估查询涉及的记录数量
  5. 判断是否需要查询重写或结构调整

二、执行计划深度解析

1. 核心字段详解

字段类型重要值说明
id整型查询序列号，子查询时递增
select_type枚举型SIMPLE（简单查询）、PRIMARY（外层查询）、DERIVED（派生表）等
table字符串访问的表名，可能为或<unionM,N>格式
type枚举型访问类型，从优到劣：system > const > eq_ref > ref > range > index > ALL
possible_keys列表可能使用的索引
key字符串实际使用的索引
key_len整型使用索引的长度（字节数）
rows整型预估扫描行数
filtered浮点型存储引擎层过滤后剩余记录的百分比
Extra文本附加信息，如Using filesort、Using temporary等关键提示

2. 关键字段深度解析

（1）select_type类型图谱

graph TD
    A[SIMPLE] --> B[PRIMARY]
    A --> C[SUBQUERY]
    C --> D[DEPENDENT SUBQUERY]
    B --> E[DERIVED]
    B --> F[UNION]
    F --> G[UNION RESULT]

select_type 是 SQL 查询执行计划（EXPLAIN 输出）中的一个字段，用于描述查询中每个 SELECT 子句的类型。理解 select_type 的内容对于分析查询的性能和优化非常重要。以下是常见的 select_type 类型及其对分析的意义：

常见的 select_type 值及其意义：

  1. SIMPLE
    • 表示这是一个简单的 SELECT 查询，不包含子查询或 UNION。
    • 分析意义：通常这种查询性能较好，但如果查询涉及大表或复杂条件，仍需检查索引使用情况。
  2. PRIMARY
    • 表示这是查询中最外层的 SELECT，通常出现在包含子查询或 UNION 的查询中。
    • 分析意义：关注外层查询的性能，尤其是涉及大量数据时。
  3. SUBQUERY
    • 表示这是一个子查询，通常出现在 SELECT 或 WHERE 子句中。
    • 分析意义：子查询可能会导致性能问题，尤其是相关子查询（依赖于外部查询的结果）。检查是否可以优化为 JOIN 或其他方式。
  4. DERIVED
    • 表示这是一个派生表（即从子查询中生成的临时表）。
    • 分析意义：派生表可能会导致额外的磁盘 I/O 和内存消耗，尤其是在处理大数据集时。考虑是否可以优化查询逻辑。
  5. UNION
    • 表示这是 UNION 操作的一部分。
    • 分析意义：UNION 会生成临时表并去重，可能会影响性能。如果不需要去重，可以考虑使用 UNION ALL。
  6. UNION RESULT
    • 表示这是 UNION 操作的结果集。
    • 分析意义：关注 UNION 结果的生成过程，尤其是临时表的大小和性能。
  7. DEPENDENT SUBQUERY
    • 表示这是一个相关子查询，子查询依赖于外部查询的结果。
    • 分析意义：相关子查询可能会导致性能问题，因为需要为外部查询的每一行执行子查询。考虑是否可以重写为 JOIN。
  8. MATERIALIZED
    • 表示子查询的结果被物化（存储为临时表）。
    • 分析意义：物化可能会增加内存和磁盘开销，但有时可以提高性能。
  9. UNCACHEABLE SUBQUERY
    • 表示子查询的结果无法缓存，每次执行时都需要重新计算。
    • 分析意义：这种子查询可能会导致性能问题，尤其是在查询需要多次执行时。

如何利用 select_type 进行查询优化？

  1. 识别性能瓶颈
    • 如果 select_type 是 DERIVED 或 SUBQUERY，检查是否可以优化查询逻辑，减少临时表的使用。
    • 如果 select_type 是 DEPENDENT SUBQUERY，尝试将其重写为 JOIN。
  2. 减少临时表和磁盘 I/O
    • 避免不必要的派生表（DERIVED）和 UNION 操作，尤其是在处理大数据集时。
  3. 优化子查询
    • 对于 SUBQUERY 和 DEPENDENT SUBQUERY，检查是否可以优化为更高效的查询方式，例如使用 JOIN 或窗口函数。
  4. 检查索引使用
    • 无论 select_type 是什么，确保查询中使用的列都有适当的索引，以减少全表扫描。
  5. 分析查询执行计划
    • 结合 EXPLAIN 的其他字段（如 type、key、rows 等），全面分析查询的性能。

示例：

假设你有以下查询：

EXPLAIN
SELECT e.emp_no, (SELECT d.dept_name FROM departments d WHERE d.dept_no = e.dept_no) AS dept_name
FROM employees e
WHERE e.emp_no < 10100;

执行 EXPLAIN 后，可能会看到：

  • 外层查询的 select_type 是 PRIMARY。
  • 子查询的 select_type 是 SUBQUERY 或 DEPENDENT SUBQUERY。

通过分析 select_type，你可以判断子查询是否是性能瓶颈，并决定是否需要优化。

（2）type访问类型详解

  • const：通过主键或唯一索引定位单条记录
  • eq_ref：关联查询时使用主键或唯一索引
  • ref：使用非唯一索引查找
  • range：索引范围扫描（BETWEEN、IN等）
  • index：全索引扫描
  • ALL：全表扫描（需重点优化）

（3）Extra关键提示

  • Using index：覆盖索引扫描
  • Using where：存储引擎返回数据后需要服务层过滤
  • Using temporary：使用临时表存储中间结果
  • Using filesort：需要额外排序操作

三、实战案例分析

示例查询

EXPLAIN 
SELECT s.name, c.course_name, sc.score 
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE s.grade = 3 
ORDER BY sc.score DESC 
LIMIT 10;

执行计划输出

idselect_typetabletypekeyrowsfilteredExtra
1SIMPLEsALLNULL100010.00Using where; Using temporary; Using filesort
1SIMPLEscrefidx_stu10100.00Using index
1SIMPLEceq_refPRIMARY1100.00NULL

问题诊断

  1. 学生表全表扫描（type=ALL）
  2. 未使用年级字段索引
  3. 存在临时表与文件排序
  4. 关联顺序可能不合理

四、系统化优化策略

1. 索引优化方案

-- 创建年级字段覆盖索引
ALTER TABLE students ADD INDEX idx_grade(grade);

-- 优化成绩表复合索引
ALTER TABLE scores 
DROP INDEX idx_stu,
ADD INDEX idx_stu_course_score(student_id, course_id, score);

2. 查询重写技巧

SELECT /*+ STRAIGHT_JOIN */ s.name, c.course_name, sc.score 
FROM students s
FORCE INDEX (idx_grade)
JOIN scores sc USE INDEX (idx_stu_course_score)
  ON s.id = sc.student_id
JOIN courses c 
  ON sc.course_id = c.id
WHERE s.grade = 3 
ORDER BY sc.score DESC 
LIMIT 10;

3. 执行计划优化路线

graph LR
    A[分析type类型] --> B{是否出现ALL}
    B -->|是| C[检查索引有效性]
    B -->|否| D[检查rows预估]
    C --> E[添加缺失索引]
    D --> F[验证统计信息准确性]
    E --> G[测试覆盖索引]
    F --> H[执行ANALYZE TABLE]
    G --> I[检查Extra信息]
    H --> J[调整关联顺序]
    I --> K[消除filesort/temporary]
    J --> L[验证执行效率]

4. 进阶优化手段

  1. 数据分片策略：对年级字段进行分区处理
  2. 汇总表预计算：创建高频查询的物化视图
  3. 查询缓存应用：对稳定数据启用缓存
  4. 执行计划固定：使用optimizer hint引导优化器
  5. 异步处理机制：将排序操作转移到应用层

五、优化效果验证

优化后执行计划对比：

优化项优化前优化后性能提升
学生表扫描类型ALLref90%
排序方式filesortindex100%
临时表使用存在消除80%
预估扫描行数1000015098.5%

通过SHOW PROFILE分析显示，优化后查询耗时从230ms降至15ms，效率提升约15倍。

六、优化注意事项

  1. 索引维护成本：平衡读写比例，避免过度索引
  2. 统计信息时效：定期执行ANALYZE TABLE
  3. 版本差异特性：不同MySQL版本优化器行为可能不同
  4. 硬件环境影响：合理配置buffer pool等参数
  5. 真实数据验证：使用生产环境数据量进行压力测试

建议结合EXPLAIN ANALYZE（MySQL 8.0+）获取实际执行指标，使用Performance Schema进行深度性能剖析，形成完整的优化闭环。

avatar
文章
阅读
粉丝