MySQL EXPLAIN ANALYZE 详解：阅读执行计划并优化查询（8.0 指南）

1. 引言

执行计划：数据库性能优化的关键

在 Web 应用和业务系统中，数据库性能是直接影响整体响应时间的关键因素。尤其在使用 MySQL 时，了解“执行计划”对于评估查询效率至关重要。传统的 EXPLAIN 命令在执行 SQL 语句之前显示执行计划，长期为开发者提供了宝贵的洞察。

MySQL 8.0 引入的 “EXPLAIN ANALYZE”

自 MySQL 8.0.18 起，引入了 EXPLAIN ANALYZE，它是对传统 EXPLAIN 的强大增强。EXPLAIN 只提供“理论计划”，而 EXPLAIN ANALYZE 实际执行查询并返回 执行时间、处理行数等测量数据。这使得更准确地定位瓶颈并验证查询优化结果成为可能。

为什么 EXPLAIN ANALYZE 很重要

例如，JOIN 顺序、索引使用以及过滤条件都会显著影响执行时间。使用 EXPLAIN ANALYZE，你可以直观地确认 SQL 语句的实际表现，找出低效之处并确定需要优化的环节。这在处理大数据集或复杂查询时尤为不可或缺。

本文目的与受众

本文从 MySQL EXPLAIN ANALYZE 的基础概念到解读其输出、以及实用的优化技巧，进行全方位讲解。目标读者为经常使用 MySQL 的开发者和基础设施工程师，以及对性能调优感兴趣的工程师。为确保即使是初学者也能清晰理解，我们在全文中加入了术语解释和具体示例。

2. EXPLAIN 与 EXPLAIN ANALYZE 的区别

EXPLAIN 的作用与基本用法

MySQL 的 EXPLAIN 是一种分析工具，用于提前了解 SQL 语句（尤其是 SELECT 语句）将如何执行。它可以帮助你确认索引使用、连接顺序、搜索范围等执行计划细节。

例如：

EXPLAIN SELECT * FROM users WHERE age > 30;

执行此命令时，MySQL 并不会真正运行查询，而是以表格形式展示它计划如何处理该查询。输出中包括使用的索引 (key)、访问方式 (type) 以及估计的行数 (rows) 等信息。

EXPLAIN ANALYZE 的作用与特性

相对地，EXPLAIN ANALYZE（自 MySQL 8.0.18 引入）会实际执行查询，并基于真实测量值显示执行计划。这使得可以确认传统 EXPLAIN 看不到的细节，如实际的处理时间和实际处理的行数等。

示例：

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

该命令执行查询并返回的输出包括：

• 每一步计划的执行时间（例如 0.0022 sec）
• 实际读取的行数（rows）
• 处理结构（可通过 TREE 格式直观可视化）

关键差异概览

应该使用哪一个？

• 当你想快速检查查询结构时，使用 EXPLAIN。
• 当你需要关于执行时间和查询代价的具体细节时，使用 EXPLAIN ANALYZE。

在性能调优场景中，EXPLAIN ANALYZE 能基于真实执行数据而非估算进行优化，是一项极其强大的工具。

3. EXPLAIN ANALYZE 的输出格式

三种输出格式：TRADITIONAL、JSON 与 TREE

MySQL 的 EXPLAIN ANALYZE 可以根据你的需求以不同格式输出结果。在 MySQL 8.0 及以后版本，提供以下三种格式。

下面我们详细看看它们之间的差异。

TRADITIONAL 格式（默认）

TRADITIONAL 输出类似于经典的 EXPLAIN 样式，让您以熟悉的形式查看执行计划。如果在未指定格式的情况下运行 EXPLAIN ANALYZE，结果通常会以此格式显示。

示例输出（摘录）：

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

• cost : 估计成本
• actual time : 实际测量时间
• rows : 估计的处理行数（执行前）
• loops : 循环次数（对 JOIN 尤其重要）

TRADITIONAL 格式易于人类快速浏览和理解，适合初学者和快速检查。

JSON 格式

JSON 格式更为详细，且更易于以编程方式处理。输出是结构化的，每个节点都表示为一个嵌套对象。

命令：

EXPLAIN ANALYZE FORMAT=JSON SELECT * FROM users WHERE age > 30;

输出的一部分（美化打印）：

{
  "query_block": {
    "table": {
      "table_name": "users",
      "access_type": "range",
      "rows_examined_per_scan": 100,
      "actual_rows": 80,
      "filtered": 100,
      "cost_info": {
        "query_cost": "0.35"
      },
      "timing": {
        "start_time": 0.001,
        "end_time": 0.004
      }
    }
  }
}

这种格式在视觉上不太易读，但在需要解析数据并将其输入分析工具或仪表板时极其方便。

TREE 格式（可读且适合可视化结构）

TREE 格式以树形展示查询执行结构，使得更容易理解 JOIN 和子查询的处理顺序。

命令：

EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM users WHERE age > 30;

示例输出（简化版）：

-> Table scan on users  (actual time=0.002..0.004 rows=8 loops=1)

对于复杂查询，嵌套可能会呈现如下：

-> Nested loop join
    -> Table scan on users
    -> Index lookup on orders using idx_user_id

TREE 格式特别适用于包含大量 JOIN 或复杂嵌套的查询，帮助您把握处理流程。

应该使用哪种格式？

选择最符合您目标的格式，以最易读、最易分析的方式查看执行计划。

4. 如何解读执行计划

为什么需要阅读执行计划

MySQL 查询性能会因数据量和索引可用性而有很大差异。通过正确解读 EXPLAIN ANALYZE 的执行计划输出，您可以客观地识别出工作浪费的地方以及需要改进的方面。这项技能是性能调优的基石，尤其针对处理大数据集或复杂 JOIN 的查询。

执行计划的基本结构

EXPLAIN ANALYZE 的输出包含如下信息（以下基于 TRADITIONAL 风格的输出进行解释）：

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

此单行包含多个重要字段。

如何阅读关键字段

1. cost 与 actual time

• cost 是 MySQL 计算的内部估计，用于相对评估。
• actual time 反映真实的耗时，对性能分析更为重要。

例如：

(cost=0.35 rows=100) (actual time=0.002..0.004 rows=100)

如果估计值与测量值高度吻合，执行计划可能是准确的。若差距较大，可能是表统计信息不准确导致的。

2. rows 与 actual rows

• rows 是 MySQL 预测将读取的行数。
• actual rows 是实际读取的行数（在 TRADITIONAL 风格的输出中以括号形式显示）。

如果差异很大，您可能需要刷新统计信息或重新考虑索引设计。

3. loops

如果 loops=1，该步骤只运行一次。使用 JOIN 或子查询时，可能会看到 loops=10 或 loops=1000。数值越大，嵌套循环导致的重处理可能性越高。

理解执行计划的嵌套结构

当多个表进行连接时，执行计划会以树形结构展示（在 TREE 格式下尤为清晰）。

示例：

-> Nested loop join
    -> Table scan on users
    -> Table scan on orders

问题

• 两个表都被全表扫描，导致较高的连接成本。

对策

• 在 users.age 上添加索引，并提前过滤，以降低连接工作量。

如何识别性能瓶颈

关注以下要点可以更容易发现瓶颈：

• 实际时间长且行数多的节点：这些节点消耗了大部分执行时间
• 出现全表扫描的地方：可能缺少或未使用索引
• 循环次数多的步骤：表明 JOIN 顺序或嵌套效率低下
• 行数与实际行数之间的差距大：暗示统计信息不准确或数据访问过多

将这些洞察作为下一节中“查询优化”技术的基础。

5. 实际查询优化示例

什么是查询优化？

查询优化是指审查并改进 SQL 语句，使其能够更高效地执行。基于 MySQL 在内部处理查询的方式（执行计划），您可以进行诸如 添加索引、调整连接顺序以及消除不必要的处理 等改进。

下面，我们通过具体示例演示如何使用 EXPLAIN ANALYZE 来改进查询。

示例 1：使用索引提升速度

优化前

SELECT * FROM users WHERE email = 'example@example.com';

执行计划（摘录）

-> Table scan on users  (cost=10.5 rows=100000) (actual time=0.001..0.230 rows=1 loops=1)

问题

• 输出显示 Table scan，意味着执行了全表扫描。对于大数据集，这会导致显著的延迟。

解决方案：添加索引

CREATE INDEX idx_email ON users(email);

优化后的执行计划

-> Index lookup on users using idx_email  (cost=0.1 rows=1) (actual time=0.001..0.002 rows=1 loops=1)

结果

• 执行时间显著缩短。
• 通过使用索引避免了全表扫描。

示例 2：优化连接顺序

优化前

SELECT * FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

执行计划（摘录）

-> Nested loop join
    -> Table scan on orders
    -> Table scan on users

问题

• 两个表都被全表扫描，导致高连接成本。

解决方案

• 在 users.age 上添加索引，并先进行过滤，以减小连接目标的规模。

  CREATE INDEX idx_age ON users(age);
  

优化后的执行计划

-> Nested loop join
    -> Index range scan on users using idx_age
    -> Index lookup on orders using idx_user_id

结果

• 先过滤 JOIN 目标，降低整体处理负载。

示例 3：改写子查询

优化前

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

问题

• 子查询可能被重复评估，导致性能下降。

解决方案：改写为 JOIN

SELECT DISTINCT users.*
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.total > 1000;

结果

• 执行计划针对 JOIN 处理进行了优化，且更有可能使用索引。

前后对比的重要性

使用 EXPLAIN ANALYZE，您可以通过实际测量值验证优化结果。通过比较改进前后的执行时间和行数，确保调优工作基于真实的性能提升，而非假设。

优化中的重要考虑因素

• 添加过多索引可能适得其反（INSERT/UPDATE 性能变慢）。
• 执行计划取决于数据量和统计信息，因此需要在每个环境中进行验证。
• 单一的优化很少能解决所有问题。瓶颈分析应放在首位。

6. 注意事项和最佳实践

使用 EXPLAIN ANALYZE 时的重要注意事项

虽然 EXPLAIN ANALYZE 功能强大，但不当使用可能导致误解甚至运营风险。牢记以下要点可确保查询分析的安全性和有效性。

1. 避免在生产环境中随意运行

因为 EXPLAIN ANALYZE 实际上会执行查询，如果误将其用于修改语句（INSERT/UPDATE/DELETE），可能会更改数据。

• 通常情况下，只在 SELECT 语句上使用它。
• 最好在预发布或测试环境中运行，而非生产环境。

2. 考虑缓存的影响

如果同一查询被重复执行，MySQL 可能会从缓存返回结果。因此，EXPLAIN ANALYZE 报告的执行时间可能与实际情况不符。

对策：

• 在执行前清除缓存（RESET QUERY CACHE;）。
• 多次运行并基于平均值进行评估。

3. 保持统计信息最新

MySQL 根据表和索引的统计信息生成执行计划。如果统计信息过时，EXPLAIN 和 EXPLAIN ANALYZE 都可能提供误导性信息。

在大量 INSERT 或 DELETE 操作后，使用 ANALYZE TABLE 更新统计信息。

ANALYZE TABLE users;

4. 索引并非万能钥匙

虽然索引通常能提升性能，但过多的索引会减慢写入操作。

在 复合索引和单列索引 之间的选择也很重要。应根据查询模式和使用频率谨慎设计索引。

5. 不要仅凭执行时间判断

EXPLAIN ANALYZE 的结果仅反映单个查询的性能。在实际应用中，网络延迟或后端处理可能才是瓶颈所在。

因此，应在整个系统架构的上下文中分析查询。

最佳实践总结

7. 常见问题解答 (FAQ)

Q1. EXPLAIN ANALYZE 从哪个版本开始可用？

A.
MySQL 的 EXPLAIN ANALYZE 在 8.0.18 及以后版本 中引入。8.0 之前的版本不支持，因此在使用前应确认 MySQL 版本。

Q2. 运行 EXPLAIN ANALYZE 会修改数据吗？

A.
EXPLAIN ANALYZE 会在内部执行查询。
在使用 SELECT 语句时，它不会修改数据。

因此，在使用 SELECT 语句时，它不会修改数据。

但如果误将其用于 INSERT、UPDATE 或 DELETE，数据将像普通查询一样被修改。

为安全起见，建议 在测试或预发布数据库中进行分析，而非生产环境。

Q3. 单独使用 EXPLAIN 不够吗？

A.
EXPLAIN 足以查看“估计”的执行计划。但它不提供诸如 实际执行时间或实际行数 等测量值。

如果需要进行深入的查询调优或验证优化效果，EXPLAIN ANALYZE 更为有用。

Q4. “loops” 和 “actual time” 等值的准确性如何？

A.
actual time 和 loops 等值是 MySQL 内部测量的真实执行指标。但它们可能会因操作系统状况、缓存状态和服务器负载而略有波动。

For this reason, do not rely on a single measurement. Instead, run the query multiple times and evaluate trends.

Q5. “cost”到底代表什么？

A.
cost 是 MySQL 内部成本模型计算的估计值。它反映了 CPU 和 I/O 成本的相对评估。它并不是以秒为单位的。

例如，如果你看到 (cost=0.3) 和 (cost=2.5)，后者在相对意义上被估计为更昂贵。

Q6. 使用 JSON 或 TREE 格式有什么好处？

A.

• JSON 格式：结构化输出，便于程序化解析。适用于自动化工具和仪表盘。
• TREE 格式：使执行流程和嵌套在视觉上清晰。非常适合理解复杂查询和 JOIN 顺序。

请选择最适合你需求的格式。

Q7. 在审查执行计划后仍无法提升性能，我该怎么办？

A.
考虑其他方法，例如：

• 重新设计索引（复合索引或覆盖索引）
• 重写查询（子查询 → JOIN，去除不必要的 SELECT 列）
• 使用视图或临时表
• 审查 MySQL 配置（缓冲区大小、内存分配等）

性能调优很少靠单一技术就能成功。需要综合且迭代的方法。