MySQL EXPLAIN ANALYZE ( MySQL 8.0+ 新工具 )-优快云博客

MySQL 的 EXPLAIN ANALYZE 是一个非常强大的工具，它将 EXPLAIN 的执行计划展示与实际查询执行的性能度量结合起来。它在 MySQL 8.0.18 版本中引入，是分析查询性能、诊断瓶颈的现代首选方法。

核心概念与作用：

实际执行：与传统的 EXPLAIN（只预测执行计划）不同，EXPLAIN ANALYZE 会实际执行你指定的查询语句。
真实度量：它在执行过程中收集详细的、实际发生的性能指标（如实际耗时、实际处理的行数、循环次数等）。
执行计划集成：输出结果以树形结构清晰地展示查询的执行计划（类似于 EXPLAIN FORMAT=TREE），并在每个执行计划节点（操作符）上附加实际的度量信息。
精准定位瓶颈：通过对比优化器的预估（EXPLAIN）与实际执行结果（ANALYZE），可以精确发现：
- 优化器估算行数（rows）与实际处理行数的巨大差异（可能导致选择了错误的连接顺序或索引）。
- 查询计划中哪个具体操作（如某个表的访问、排序、连接、临时表创建）消耗了最多时间。
- 是否存在意外的全表扫描、低效索引使用、昂贵的排序或临时文件操作。
- 循环次数（loops）是否过多。
更直观的树形结构：以层次缩进的方式展示执行流程，比传统的表格形式更易理解操作之间的依赖关系（如嵌套循环连接）。

基本语法：

EXPLAIN ANALYZE [FORMAT=TREE] <你的 SELECT 语句>;

FORMAT=TREE 是默认且目前唯一支持的格式（在 8.0.18+ 版本中），所以通常可以省略 FORMAT 子句。
警告： 它会实际执行你的查询！对于生产环境的大查询或修改数据的语句（INSERT, UPDATE, DELETE），务必极其谨慎，最好在测试环境或副本上使用。

输出解读（关键部分）：

EXPLAIN ANALYZE 的输出是一个文本树，每个节点代表执行计划中的一个操作符（如 Table scan, Index lookup, Sort, Nested loop inner join 等）。每个节点行包含以下关键信息：

-> [操作符名称]  [表名]  [其他操作符特定信息]  (cost=预估成本 rows=预估行数) (actual time=实际单次迭代时间..实际总时间, rows=实际行数, loops=循环次数)
   [子操作符1]
   [子操作符2]

->: 表示一个执行计划节点。缩进表示节点间的父子/依赖关系（父节点操作依赖于子节点操作的结果）。
操作符名称: 如 Table scan on t1, Index range scan on t2 using idx_name, Sort, Nested loop inner join, Filter, Group aggregate 等。描述了数据库引擎在该步骤执行的操作。
(cost=... rows=...): 这是优化器在查询执行前的预估信息（与传统 EXPLAIN 的输出一致）。
- cost: 优化器估算的该操作相对成本（值越大通常表示越昂贵，但单位是抽象的，用于比较）。
- rows: 优化器估算该操作会处理/返回的行数。
(actual time=... , rows=... , loops=...): 这是查询实际执行后收集的度量信息，是分析的重点！
- actual time= [单次迭代平均时间] .. [该操作符总时间] (单位：毫秒 - ms)
  - 第一个数值 (0.025)：表示该操作符每次循环迭代（loop）所花费的平均时间（毫秒）。
  - 第二个数值 (0.027)：表示该操作符在整个查询执行中花费的总时间（毫秒）。
  - 重要： 总时间 = 平均单次时间 * 循环次数。如果循环次数 (loops) 是 1，那么这两个值通常非常接近。如果循环次数很多，总时间会显著大于单次时间。
- rows= [实际处理的行数]: 该操作符实际处理或传递到父操作符的行数。
- loops= [循环次数]: 该操作符被执行的次数。对于最底层的表访问操作（如扫描、索引查找），loops 通常是 1。对于嵌套循环连接中的驱动表（外层表），loops 表示它扫描了多少行（每行会驱动一次内层表的查找/扫描）。对于基于驱动表结果执行的操作（如排序、聚合），loops 也通常是 1。

一个简单示例及解读：

假设在 sakila 示例数据库上执行：

EXPLAIN ANALYZE
SELECT first_name, last_name
FROM customer
WHERE active = 1 AND store_id = 1
ORDER BY last_name;

可能输出类似：

-> Sort: customer.last_name  (cost=127.34 rows=326) (actual time=1.234..1.234 rows=326 loops=1)
    -> Table scan on customer  (cost=127.34 rows=326) (actual time=0.087..0.682 rows=326 loops=1)
        Filter: ((customer.active = 1) and (customer.store_id = 1))

根节点 (Sort):
- 操作: 对结果按 last_name 排序。
- 预估: 成本 127.34, 处理 326 行。
- 实际:
  - 单次排序平均耗时: 1.234 ms (因为 loops=1, 所以排序只执行了一次)。
  - 排序总耗时: 1.234 ms。
  - 实际排序了 326 行。
  - 执行了 1 次 (loops=1)。
子节点 (Table scan on customer):
- 操作: 对 customer 表进行全表扫描 (Table scan)。注意：这通常表示没有使用到合适的索引！
- 预估: 成本 127.34, 扫描 326 行。
- 实际:
  - 单次扫描平均耗时: 0.087 ms。
  - 扫描总耗时: 0.682 ms (因为 loops=1, 所以 总耗时 ≈ 单次耗时 * 1)。
  - 实际扫描了 326 行。
  - 执行了 1 次 (loops=1)。
- 过滤条件 (Filter):
  - 在扫描过程中应用了 WHERE 条件 (active = 1 AND store_id = 1)。这个信息附加在扫描节点上，说明扫描后立即进行了过滤。

关键分析点：

性能: 总执行时间约 1.234 ms (主要是排序耗时)。
瓶颈: 排序操作消耗了主要时间（1.234 ms），但考虑到它处理了 326 行，这个时间可能不算过分。更大的问题是全表扫描 (Table scan)！
索引问题: WHERE 条件 active = 1 AND store_id = 1 被用于过滤。当前没有索引支持这个条件，导致必须扫描全表（customer 表通常有几百到几千行）。优化建议：在 (store_id, active) 上创建索引。
估算准确性: 优化器对扫描行数的预估 (rows=326) 与实际 (rows=326) 完全一致，说明统计信息准确。排序行数也一致。

更复杂示例（嵌套循环连接）：

EXPLAIN ANALYZE
SELECT c.first_name, c.last_name, a.address
FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE c.store_id = 1;

可能输出：

-> Nested loop inner join  (cost=... rows=...) (actual time=0.123..2.345 rows=326 loops=1)
    -> Table scan on c  (cost=... rows=326) (actual time=0.056..0.789 rows=326 loops=1)
        Filter: (c.store_id = 1)
    -> Single-row index lookup on a using PRIMARY (address_id=c.address_id)  (cost=... rows=1) (actual time=0.004..0.004 rows=1 loops=326)

根节点 (Nested loop inner join):
- 实际: 总耗时 2.345 ms, 返回 326 行, 执行 1 次循环。
驱动表 (Table scan on c):
- 实际: 扫描总耗时 0.789 ms, 扫描出 326 行 (WHERE store_id=1 过滤后), 执行 1 次循环。这个 loops=1 意味着它扫描一次得到了 326 行结果。
被驱动表 (Single-row index lookup on a):
- 操作: 使用 PRIMARY KEY 通过 address_id 查找 address 表。
- 预估: 每次查找返回 1 行。
- 实际:
  - 单次查找平均耗时: 0.004 ms (非常快)。
  - 查找总耗时: 0.004 ms * 326 loops = 1.304 ms (虽然单次很快，但执行了 326 次！)。
  - 每次查找返回 1 行 (符合预期)。
  - 循环次数 (loops=326): 这是关键！因为它是嵌套循环的内层操作，它被执行的次数等于驱动表 (c) 扫描后满足条件的行数 (326 行)。所以总耗时 = 平均单次耗时 * 循环次数 = 0.004 ms * 326 ≈ 1.304 ms。

关键分析点：

连接性能: 连接总耗时 2.345 ms = 驱动表扫描 (0.789 ms) + 内层查找总耗时 (1.304 ms) + 少量连接操作本身开销。
瓶颈: 虽然内层单次索引查找很快 (0.004 ms)，但因为它被执行了 326 次 (loops=326)，其累积总耗时 (1.304 ms) 成为了主要开销。
优化思考: 能否减少驱动表返回的行数？能否改变连接方式（如哈希连接）？本例中驱动表扫描 (c.store_id=1) 可能缺少索引。

EXPLAIN ANALYZE 的优势 vs PROFILE/SHOW PROFILES:

更直观集成：直接将实际度量绑定到具体的执行计划步骤上，一目了然看出哪个操作（甚至哪个索引查找）耗时多。
更丰富的指标：提供 actual time, rows, loops 等关键指标，特别是 loops 对于理解嵌套操作的开销至关重要。
现代 & 推荐：是 MySQL 8.0+ 官方推荐的分析工具，替代了已弃用/移除的 PROFILE。
树形结构：清晰展示执行流程的层次和依赖关系。
包含 EXPLAIN 信息：同时提供优化器的预估 (cost, rows)，便于对比发现优化器估算错误。

重要注意事项：

实际执行查询：这是最重要的！它会修改数据（如果语句是 INSERT/UPDATE/DELETE）或消耗资源（如果是大 SELECT）。绝对不要在生产环境直接分析不熟悉的大查询或写操作！ 在测试环境或副本中使用。
MySQL 8.0.18+：仅适用于 MySQL 8.0.18 及更高版本。
关注 actual time 和 loops：理解 actual time= [avg per loop ms]..[total ms] 和 loops 的关系是解读性能的关键。高 loops 乘上一个中等单次耗时可能导致很高的总耗时。
对比 rows (预估 vs 实际)：大的差异往往是性能问题的根源（优化器选错计划）。
结合 EXPLAIN (传统格式)：对于快速检查执行计划是否合理（是否有全表扫描、是否用到期望的索引），传统的 EXPLAIN 仍然是第一步快速检查的好工具。EXPLAIN ANALYZE 用于更深入的性能分析和验证。
性能开销：收集详细的执行度量本身会带来一些额外开销，但通常远小于查询本身的耗时，且为了诊断是值得的。
格式化输出：一些 MySQL 客户端（如 MySQL Shell, Workbench 8.0+）可能会以更易读的树形或图形化方式展示 EXPLAIN ANALYZE 的结果。

总结：

EXPLAIN ANALYZE 是 MySQL 8.0+ 中分析和优化 SQL 查询性能的利器。它通过实际执行查询，将精确的性能度量（耗时、处理行数、循环次数）直接关联到执行计划的每一步操作符上，并以清晰的树形结构展示。它能精准定位性能瓶颈（特别是高 loops 或高 actual time 的操作），并揭示优化器估算与实际的差异。务必牢记它会真实执行查询，使用时需谨慎，尤其是在生产环境。对于现代 MySQL 性能调优，EXPLAIN ANALYZE 应成为你的核心工具之一。