如何用 EXPLAIN ANALYZE / SHOW WARNINGS 看真实执行成本？

考察要点

这道题面试官想看你是否具备实际的 SQL 性能诊断能力，而不只是停留在理论层面。它考察你是否理解并能熟练运用 MySQL 提供的两大诊断利器：

1. EXPLAIN ANALYZE：评估 SQL 真实执行成本与预估执行计划之间差异的能力。
2. SHOW WARNINGS：洞察 MySQL 查询优化器重写行为、发现隐式转换等导致索引失效根源的能力。

掌握这两者，是区分“知道”EXPLAIN和“精通”EXPLAIN的关键分水岭。

核心概念

标准的 EXPLAIN 只显示优化器预估的执行计划，其成本和行数（rows）是基于统计信息的估算，可能与真实情况有很大偏差。EXPLAIN ANALYZE 和 SHOW WARNINGS 则是深入真实执行细节的工具。

1. EXPLAIN ANALYZE：获取真实执行指标

EXPLAIN ANALYZE 在 MySQL 8.0.18 版本中引入，它会实际执行查询，然后展示带有真实成本和行数的执行计划树。

核心价值：对比“预估”与“现实”，精准定位性能瓶颈。

输出格式与关键指标解读： 它采用“火山模型”或“迭代器模型”的树状格式展示。

 1-- 示例表
 2CREATE TABLE employees (
 3    id INT PRIMARY KEY AUTO_INCREMENT,
 4    name VARCHAR(100),
 5    department_id INT,
 6    salary INT,
 7    INDEX idx_dept_salary (department_id, salary)
 8);
 9-- 插入一些数据...
10
11-- 执行分析
12EXPLAIN ANALYZE SELECT name, salary FROM employees WHERE department_id = 10 AND salary > 80000;

输出示例：

 1-> Index range scan on employees using idx_dept_salary (cost=... rows=...) (actual time=0.042..0.085 rows=15 loops=1)
 2    -> Filter: (employees.salary > 80000) (cost=... rows=...)

关键指标解读：

• actual time=0.042..0.085: 这是最重要的指标。
  • 0.042 (first row time): 该迭代器节点返回第一行数据所花费的时间（毫秒）。
  • 0.085 (last row time): 该迭代器节点返回所有行数据所花费的总时间（毫秒）。
  • 如果 first row 时间很长，说明启动成本高；如果 first row 和 last row 时间差距大，说明数据传输或处理耗时。
• rows=15: 该迭代器节点实际返回的行数。这是与 EXPLAIN 中预估 rows 最核心的区别。如果预估值和实际值差距巨大，说明统计信息不准，可能导致优化器选择错误的执行计划。
• loops=1: 该迭代器节点被其父节点执行的次数。在嵌套循环连接（Nested Loop Join）中，内层表的 loops 数会很高，等于外层表返回的行数。

2. SHOW WARNINGS：揭示优化器重写细节

在 EXPLAIN 执行后，MySQL 会在会话中记录一些额外信息，通过 SHOW WARNINGS 可以查看。其中最重要的信息是优化器重写后的最终 SQL。

核心价值：发现因“隐式转换”导致的索引失效。

数据示例： 假设 employees 表中的 name 字段是 VARCHAR 类型。

 1-- DDL
 2CREATE TABLE employees (
 3    id INT PRIMARY KEY,
 4    name VARCHAR(100),
 5    INDEX idx_name (name)
 6);
 7INSERT INTO employees VALUES (1, '10086');
 8
 9-- 错误的查询（用数字去匹配字符串列）
10EXPLAIN SELECT * FROM employees WHERE name = 10086;

EXPLAIN 的结果可能显示走了全表扫描（type: ALL），但没有直接告诉你为什么。

使用 SHOW WARNINGS 揭示真相：

 1-- 紧跟在 EXPLAIN 之后执行
 2SHOW WARNINGS;

输出示例：

关键信息解读： Message 字段展示了重写后的 SQL：WHERE (cast(employees.name as unsigned) = 10086)。 MySQL 为了比较 VARCHAR 类型的 name 列和数字 10086，对 name 列应用了 CAST 函数。对索引列使用函数，是导致索引失效的典型场景。SHOW WARNINGS 明确地证实了这一点。

正确做法：保证查询参数与列类型一致。

 1EXPLAIN SELECT * FROM employees WHERE name = '10086'; -- 使用字符串

此时 EXPLAIN 会显示 type: ref，正确用上了 idx_name 索引。

常见低分回答

1. 混淆 EXPLAIN 和 EXPLAIN ANALYZE：错误地声称 EXPLAIN 显示的是真实执行时间或行数。
2. 不知 EXPLAIN ANALYZE 的版本限制：在面试中提及该命令但不知道它在 MySQL 8.0 才可用，会显得知识陈旧。
3. 对 EXPLAIN ANALYZE 的成本一无所知：认为 EXPLAIN ANALYZE 和 EXPLAIN 一样是低成本操作。错误！它会真实执行查询，对于慢查询或 UPDATE/DELETE 语句，在生产环境中使用是高风险行为。
4. 只知索引失效规则，不知如何验证：能背出“列上用函数索引会失效”，但当被问及“如何证明是函数导致的？”时，无法回答出使用 SHOW WARNINGS 查看重写后的 SQL 这一关键步骤。
5. 误读 actual time=X..Y：将其理解为一个时间范围或不确定值，而不是“首行返回时间..全行返回时间”的精确含义。

工程实践与优化

• 诊断流程：
  1. 使用 EXPLAIN 初步判断执行计划，关注 type, key, rows, Extra。
  2. 如果 type 不佳（如 ALL, index）或 rows 预估值与业务经验严重不符，怀疑计划有误。
  3. 在开发/测试环境，使用 EXPLAIN ANALYZE 运行查询。
  4. 对比 EXPLAIN 的 rows 和 ANALYZE 的 rows，若差异巨大，考虑运行 ANALYZE TABLE your_table; 更新统计信息。
  5. 检查 ANALYZE 输出中 actual time 最高的节点，这就是性能瓶颈。
  6. 如果怀疑有隐式转换，紧跟 EXPLAIN 后运行 SHOW WARNINGS，查看 Message 中的重写 SQL，确认是否有 CAST 或 CONVERT 函数包裹了索引列。
• 生产环境注意事项：
  • 禁止在生产环境对 UPDATE/DELETE/INSERT 语句使用 EXPLAIN ANALYZE，因为它会真实修改数据。
  • 对生产环境的 SELECT 语句使用 EXPLAIN ANALYZE 也需谨慎，因为它会消耗真实资源，可能影响线上服务。最佳实践是将其 SELECT 部分复制到隔离的只读副本或测试环境执行。
  • 如果必须在生产环境诊断，优先使用 Performance Schema 和慢查询日志。慢查询日志中的 Rows_examined 字段也能提供接近于真实扫描行数的信息。
• 优化案例：
  • 问题：EXPLAIN 显示 rows=10，EXPLAIN ANALYZE 显示 rows=1,000,000。优化器以为只需要扫描 10 行，选择了 Nested Loop Join，但实际扫描了 100 万行，导致内层循环执行 100 万次，性能极差。
  • 解决方案：
    1. 运行 ANALYZE TABLE ...; 更新统计信息。
    2. 如果数据分布极不均匀，考虑在 MySQL 8.0+ 中创建直方图：ANALYZE TABLE ... UPDATE HISTOGRAM ON ...;。
    3. 作为最后手段，使用优化器提示（如 STRAIGHT_JOIN）强制改变连接顺序。

常见追问与延伸题目

1. 追问：如果 EXPLAIN ANALYZE 显示优化器的行数估算严重失准，你有哪些手段去纠正它？

   • 回答要点 1 (更新统计信息)：首先尝试执行 ANALYZE TABLE tbl_name;。这是最直接、最常规的方法，它会重新采样并更新表的基数和索引的统计数据，优化器下次就会基于更新后的信息做决策。
   • 回答要点 2 (调整采样率)：如果默认的采样率不足以反映真实数据分布，可以调整 innodb_stats_persistent_sample_pages 参数，增加采样页数，让统计信息更精确，然后再次运行 ANALYZE TABLE。
   • 回答要点 3 (使用直方图)：对于数据分布极不均匀的列（如状态列，99% 的订单是“完成”，1% 是“待支付”），仅靠基数统计是不够的。在 MySQL 8.0+，可以为这些列创建直方图 ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name;，让优化器了解值的分布情况，从而做出更精准的行数估算。

2. 追问：你不能在生产库上对一个非常慢的 UPDATE 语句执行 EXPLAIN ANALYZE，你该如何诊断它的性能问题？

   • 回答要点 1 (分析 SELECT 部分)：将 UPDATE tbl SET ... WHERE condition 转换为 SELECT ... FROM tbl WHERE condition。这个 SELECT 语句的 WHERE 部分和 UPDATE 的访问路径是完全相同的。可以在只读副本或测试环境对这个 SELECT 安全地执行 EXPLAIN 和 EXPLAIN ANALYZE，分析其索引使用和真实扫描行数。
   • 回答要点 2 (利用 Performance Schema)：在生产环境，开启 Performance Schema 的 statements 和 stages 相关监控。执行 UPDATE 后，查询 events_statements_history_long 和 events_stages_history_long 表。这可以告诉你查询在各个阶段（如 optimizing, executing, Innodb engine, row lock wait）的精确耗时，从而定位是卡在 CPU 计算、I/O 还是锁等待。
   • 回答要点 3 (检查锁信息)：UPDATE 慢的常见原因是锁等待。通过 SHOW ENGINE INNODB STATUS 查看 LATEST DETECTED DEADLOCK 和 TRANSACTIONS 部分，或者查询 information_schema 下的 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 表，可以明确看到查询是否在等待其他事务释放行锁。

3. 追问：PostgreSQL 也有 EXPLAIN ANALYZE，它和 MySQL 的版本相比，有什么更强大的地方？

   • 回答要点 1 (成熟度和历史)：PostgreSQL 的 EXPLAIN ANALYZE 功能历史悠久，非常成熟。而 MySQL 是在 8.0 版本后期才引入，相对较新。
   • 回答要点 2 (Buffer/IO 统计)：PostgreSQL 有一个杀手级特性：EXPLAIN (ANALYZE, BUFFERS)。执行后，它不仅显示时间、行数，还会显示每个节点的缓冲区使用情况（Buffers: shared hit=..., shared read=...）。shared hit 表示数据块在共享内存（Buffer Pool）中命中，shared read 表示需要从磁盘读取。这使得诊断 I/O 瓶颈变得极其直观和简单，这是 MySQL EXPLAIN ANALYZE 目前不具备的。
   • 回答要点 3 (其他详细信息)：PostgreSQL 的 EXPLAIN 选项更丰富，例如 (ANALYZE, TIMING) 可以分别显示每个节点的启动和运行时间，(ANALYZE, WAL) 可以显示 WAL（预写日志）记录生成量，SETTINGS 可以显示影响查询计划的非默认配置参数。总体而言，PostgreSQL 在执行计划分析的精细度和深度上通常优于 MySQL。