---

一、MySQL EXPLAIN ANALYZE 执行计划指南

MySQL 的 EXPLAIN ANALYZE 是一个强大的查询分析工具，它提供了比传统 EXPLAIN 更详细的查询执行信息。

主要功能

实际执行性能分析

• 不仅显示预估的执行计划，还会实际执行查询并收集真实的性能数据
• 提供每个操作的实际执行时间、处理行数等精确信息

详细的执行统计

• 显示每个步骤的实际成本(actual cost)
• 提供实际处理的行数与估计行数的对比
• 展示每个操作的实际执行时间

性能瓶颈识别

• 快速定位查询中最耗时的操作
• 识别全表扫描、不必要的排序等性能问题
• 找出索引使用不当的情况

与普通 EXPLAIN 的区别

EXPLAIN 只是分析执行计划而不实际执行查询，而 EXPLAIN ANALYZE 会真正执行查询，因此：

• 提供更准确的性能数据
• 能发现优化器估算错误的情况
• 但执行时间会更长，特别是对于复杂查询

使用场景

查询优化

• 对比不同索引策略的实际效果
• 验证查询重写是否真正提升性能
• 分析复杂 JOIN 查询的执行效率

问题诊断

• 排查慢查询的根本原因
• 识别数据分布不均匀导致的性能问题
• 发现统计信息过时的情况

总结

EXPLAIN ANALYZE 是数据库性能调优和查询优化的重要工具，能帮助你从估算转向基于实际数据的优化决策。通过提供真实的执行统计信息，它让数据库性能分析更加准确和可靠。

---

二、EXPLAIN ANALYZE 执行计划

样例

-> Sort: <temporary>.Population DESC (actual time=8.306..8.431 rows=125 loops=1)-> Stream results (actual time=0.145..8.033 rows=125 loops=1)-> Nested loop inner join (cost=241.12 rows=205) (actual time=0.141..7.787 rows=125 loops=1)-> Filter: (world.country.Continent = 'Asia') (cost=25.40 rows=34) (actual time=0.064..0.820 rows=51 loops=1)-> Table scan on country (cost=25.40 rows=239) (actual time=0.059..0.359 rows=239 loops=1)-> Filter: (world.city.Population > 1000000) (cost=4.53 rows=6) (actual time=0.030..0.131 rows=2 loops=51)-> Index lookup on city using CountryCode (CountryCode=world.country.`Code`) (cost=4.53 rows=18) (actual time=0.023..0.096 rows=35 loops=51)

分析

我们来详细解读这个 MySQL 的 EXPLAIN ANALYZE 执行计划。

这个执行计划展示了数据库为了执行一个查询而采取的具体步骤、成本估算以及（因为有 actual time）实际的执行时间和处理行数。

首先，根据执行计划我们可以推断出原始的 SQL 查询大致是这样的：

EXPLAIN ANALYZE
SELECT-- ... some columns from city and country
FROMcity
INNER JOINcountry ON city.CountryCode = country.Code
WHEREcountry.Continent = 'Asia'AND city.Population > 1000000
ORDER BYcity.Population DESC;

---

执行顺序解读

数据库执行计划的读取顺序是 从内到外，从上到下。也就是说，缩进最深的步骤最先执行。执行流程如下：

1. 扫描 country 表 (Line 5)
2. 过滤出 Continent = 'Asia' 的国家 (Line 4)
3. 对于每一个亚洲国家，去 city 表中查找对应的城市 (Line 7)
4. 过滤出人口大于一百万的城市 (Line 6)
5. 将上面两步（3和4）组合成一个嵌套循环连接 (Line 3)
6. 将连接后的结果进行流式处理 (Line 2)
7. 对最终结果按 Population 降序排序 (Line 1)

---

逐行详细解释

第 7 行 (最内层)

-> Index lookup on city using CountryCode (CountryCode=world.country.`Code`) (cost=4.53 rows=18) (actual time=0.023..0.096 rows=35 loops=51)

• 操作: Index lookup on city using CountryCode
  • 含义: 这是嵌套循环的内层操作。数据库正在使用 city 表上的 CountryCode 索引来查找与外层（country 表）匹配的行。连接条件是 city.CountryCode = country.Code。
• 成本估算: (cost=4.53 rows=18)
  • 含义: 优化器估计每次执行这个查找操作的成本是 4.53，并且估计平均每次能找到 18 行。
• 实际执行: (actual time=0.023..0.096 rows=35 loops=51)
  • actual time=0.023..0.096: 第一次执行此操作耗时 0.023 毫秒，所有执行中最长的一次耗时 0.096 毫秒。
  • rows=35: 实际上平均每次查找返回了 35 行。这说明优化器的估计（18行）偏低了。
  • loops=51: 这个操作被执行了 51 次。这非常关键，它告诉我们上一步（过滤国家）产生了 51 行结果。

第 6 行

-> Filter: (world.city.Population > 1000000) (cost=4.53 rows=6) (actual time=0.030..0.131 rows=2 loops=51)

• 操作: Filter: (world.city.Population > 1000000)
  • 含义: 对上一步（Index lookup）返回的城市结果进行过滤，只保留人口 (Population) 大于 1,000,000 的城市。
• 成本估算: (cost=4.53 rows=6)
  • 含义: 优化器估计在找到的城市中，平均有 6 个城市的人口会超过一百万。
• 实际执行: (actual time=0.030..0.131 rows=2 loops=51)
  • rows=2: 实际上平均每次只有 2 个城市满足人口条件。
  • loops=51: 这个过滤操作同样被执行了 51 次，与上一步的循环次数一致。

第 5 行

-> Table scan on country (cost=25.40 rows=239) (actual time=0.059..0.359 rows=239 loops=1)

• 操作: Table scan on country
  • 含义: 这是嵌套循环的外层驱动操作的起点。数据库正在执行全表扫描，即读取 country 表中的每一行。
• 成本估算: (cost=25.40 rows=239)
  • 含义: 优化器估计全表扫描的成本是 25.40，并估计 country 表总共有 239 行。
• 实际执行: (actual time=0.059..0.359 rows=239 loops=1)
  • rows=239: 实际上确实扫描了 239 行。
  • loops=1: 这个全表扫描操作只执行了 1 次。

第 4 行

-> Filter: (world.country.Continent = 'Asia') (cost=25.40 rows=34) (actual time=0.064..0.820 rows=51 loops=1)

• 操作: Filter: (world.country.Continent = 'Asia')
  • 含义: 对上一步（全表扫描）的结果进行过滤，只保留 Continent 字段为 ‘Asia’ 的国家。
• 成本估算: (cost=25.40 rows=34)
  • 含义: 优化器估计会有 34 个亚洲国家。
• 实际执行: (actual time=0.064..0.820 rows=51 loops=1)
  • rows=51: 实际上找到了 51 个亚洲国家。这个数字（51）成为了内层循环（Index lookup 和 Filter）的 loops 次数。
  • loops=1: 这个过滤操作也只执行了 1 次。

第 3 行

-> Nested loop inner join (cost=241.12 rows=205) (actual time=0.141..7.787 rows=125 loops=1)

• 操作: Nested loop inner join
  • 含义: 这是一个总结行，表示数据库使用了嵌套循环连接算法。它将上面两个分支（过滤后的 country 表和过滤后的 city 表）的结果连接起来。
• 成本估算: (cost=241.12 rows=205)
  • 含义: 优化器估计整个连接操作的总成本是 241.12，最终会产生 205 行结果。
• 实际执行: (actual time=0.141..7.787 rows=125 loops=1)
  • rows=125: 实际上，整个连接操作最终产生了 125 行结果（51个亚洲国家中，总共有125个城市人口超百万）。
  • loops=1: 整个连接过程作为一个整体，执行了 1 次。

第 2 行

-> Stream results (actual time=0.145..8.033 rows=125 loops=1)

• 操作: Stream results
  • 含义: 这是一个中间步骤，将连接操作产生的 125 行结果以流的形式传递给下一个操作（排序）。
• 实际执行: (actual time=... rows=125 loops=1)
  • 它处理了 125 行数据，耗时反映了从接收第一行到传递完最后一行的时间。

第 1 行 (最外层)

-> Sort: <temporary>.Population DESC (actual time=8.306..8.431 rows=125 loops=1)

• 操作: Sort: <temporary>.Population DESC
  • 含义: 这是查询的最后一步。数据库对前序步骤传来的 125 行结果，按照 Population 字段进行降序排序 (DESC)。
  • <temporary>: 这个标记意味着 MySQL 需要使用一个临时表（可能在内存或磁盘上）来完成排序操作。这通常发生在 ORDER BY 的字段没有可用索引时。
• 实际执行: (actual time=8.306..8.431 rows=125 loops=1)
  • actual time: 从开始接收数据到排序完成并输出最后一行，总共耗时约 8.431 毫秒。这是整个查询的主要耗时部分。
  • rows=125: 排序了 125 行数据。

---

总结与性能分析

1. 连接策略: 查询使用了 Nested Loop Join。对于外层结果集不大（51行）的情况，这是一个合理的选择。
2. 外层扫描: 对 country 表进行了全表扫描。由于该表只有 239 行，这几乎没有性能影响。如果 country 表非常大，那么在 Continent 字段上建立索引将是首要的优化点。
3. 内层查找: 对 city 表的查找使用了 CountryCode 索引，这是非常高效的。
4. 排序: 查询的最后一步是排序，并且使用了临时表。这是因为结果集是动态生成的，无法利用现有索引来避免排序。这是查询总耗时的主要来源。
5. 优化器估算: 优化器在行数估算上存在一些偏差（如亚洲国家34 vs 51，每个国家的城市数18 vs 35），但这些偏差没有导致选择错误的执行计划。
6. 潜在优化: 如果要进一步优化，可以考虑在 city 表上创建一个复合索引 (CountryCode, Population)。这样数据库可以在索引层面就完成对 Population > 1000000 的过滤，减少从磁盘读取的数据页，可能会略微提升性能。

文章如有问题，请彦祖帮忙指正！感激不尽！

---