性能优化（执行计划 / 慢查询 / 常见陷阱） · 慢查询定位

如何在生产环境抓取一条 SQL 的真实执行计划与耗时分布？

How to capture a SQL query's actual execution plan and performance profile in a production environment?

答案语言

考察要点

这道题考察的是候选人对 MySQL 性能诊断工具的熟练程度和实践经验，而不仅仅是理论知识。面试官想看你是否理解**估算执行计划（EXPLAIN）与真实执行计划（Actual Execution Plan）**的根本区别，以及是否掌握了在不严重影响生产系统的前提下，获取查询真实性能数据的能力。核心是考察你对 EXPLAIN ANALYZE、Performance Schema 和 pt-query-digest 等高级工具的运用。

核心概念

在生产环境中定位单条 SQL 的性能瓶颈，不能仅仅依赖 EXPLAIN。EXPLAIN 的结果是优化器的估算，它不实际执行 SQL，因此无法反映真实的 I/O、CPU 耗时和实际扫描行数。我们需要能获取真实执行数据的工具。

1. `EXPLAIN`: 估算执行计划

EXPLAIN 是最基础的工具，它展示了 MySQL 查询优化器为一条 SQL 选择的执行路径。

作用：在不执行查询的情况下，分析 SQL 语句，并返回优化器认为最高效的执行计划。
关键输出列：
- type: 连接类型，从 system (最优) 到 ALL (全表扫描，最差)。
- key: 实际使用的索引。
- rows: 估算的需要读取的行数。
- Extra: 额外信息，如 Using filesort, Using temporary, Using index。
局限性：
- 估算不准：rows 和 filtered 字段基于表的统计信息，可能因数据更新而严重失真。
- 无成本信息：无法显示查询的真实耗时、I/O 开销、CPU 开销。
- 无法体现缓存影响：无法知道数据页是否已在 Buffer Pool 中，这对 I/O 耗时影响巨大。

2. `EXPLAIN ANALYZE`: 真实执行计划与耗时 (MySQL 8.0.18+)

这是获取真实执行计划和耗时的最直接工具。它会实际执行 SQL 语句，并以树状结构返回每个执行节点的真实耗时、实际返回行数等信息。

语法：
sql
```
 1EXPLAIN ANALYZE SELECT ...;
```
输出解读：
text
```
 1-> Nested loop inner join  (cost=1.25 rows=1) (actual time=0.050..0.052 rows=1 loops=1)
 2    -> Table scan on t1  (cost=1.05 rows=1) (actual time=0.021..0.022 rows=1 loops=1)
 3    -> Index lookup on t2 using PRIMARY (id=t1.id)  (cost=0.20 rows=1) (actual time=0.025..0.026 rows=1 loops=1)
```
- (cost=... rows=...): 优化器的估算成本和行数（同 EXPLAIN）。
- (actual time=... rows=... loops=...): 真实执行数据。
  - actual time=0.050..0.052: (首次返回行的耗时)..(返回所有行的总耗时)，单位是毫秒。
  - rows=1: 该节点实际返回的行数。
  - loops=1: 该节点被执行的次数。对于嵌套循环的内层，loops 会大于 1。
版本要求：✅ MySQL 8.0.18+ / PostgreSQL。❌ MySQL 5.7 及更早版本不支持。
使用场景：非常适合在开发、测试环境或生产环境的只读副本上进行精确分析。在生产主库上对写操作（UPDATE, DELETE）使用需极其谨慎，因为它会实际修改数据。对于 SELECT 语句，可以包裹在事务中然后回滚：
sql
```
 1BEGIN;
 2EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;
 3ROLLBACK;
```

3. `Performance Schema`: 低开销的性能数据采集

Performance Schema 是 MySQL 内置的性能监控引擎，它通过在代码中埋点的方式，以较低的开销持续收集服务器运行过程中的详细事件数据。

工作原理：
1. Instruments (探针)：在服务器代码的关键路径（如解析 SQL、打开表、排序、发送数据）上设置的采集点。
2. Consumers (消费者)：用于存储探针采集到的事件数据的内存表。

核心步骤：

开启配置：确保相关的 instruments 和 consumers 已被启用。主要关注 events_statements_* 和 events_stages_*。

sql

 1-- 开启语句和阶段事件的收集
 2UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
 3WHERE NAME LIKE 'statement/sql/%' OR NAME LIKE 'stage/sql/%';
 4
 5-- 开启历史记录消费者
 6UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
 7WHERE NAME LIKE '%history%';

执行 SQL：运行你想要分析的慢查询。
查询结果：从 Performance Schema 的表中获取耗时分布。
- 获取总览：events_statements_history_long 表记录了每条 SQL 的执行摘要。
- 获取阶段耗时：events_stages_history_long 表记录了单个 SQL 执行过程中各个阶段（如 stage/sql/checking permissions, stage/sql/optimizing, stage/sql/executing, stage/sql/sending data）的耗时。
  sql
```
 1-- 假设你知道线程 ID (THREAD_ID) 和事件 ID (EVENT_ID)
 2SELECT event_name, timer_wait / 1000000000.0 AS duration_ms
 3FROM performance_schema.events_stages_history_long
 4WHERE thread_id = <your_thread_id> AND nesting_event_id = <your_statement_event_id>
 5ORDER BY timer_start;
```

优势：开销相对较低，可持续开启，是生产环境监控的基石。能提供非常细粒度的耗时分解。

4. 慢查询日志 + `pt-query-digest`

这是一种经典的、异步的分析方法，对线上服务影响最小。

慢查询日志 (Slow Query Log)：记录执行时间超过 long_query_time 秒的 SQL。
pt-query-digest：Percona Toolkit 中的一个工具，用于分析慢查询日志、通用日志或二进制日志。
工作流程：
1. 在生产环境中开启慢查询日志，并设置一个合理的 long_query_time（例如 0.5 秒）。
2. 收集一段时间的日志文件。
3. 使用 pt-query-digest 分析日志文件。
  bash
```
 1pt-query-digest /path/to/slow-query.log > report.txt
```
报告解读：pt-query-digest 会将结构相同、仅参数不同的 SQL 归为一类（通过 Digest），并提供详尽的统计报告，包括：
- Query Time: 总执行时间、平均时间、95% 分位时间。
- Lock Time: 等待锁的时间。
- Rows Sent / Rows Examined: 发送给客户端的行数与存储引擎扫描的行数，二者比值是关键优化指标。
- EXPLAIN 示例: 报告中会包含该类 SQL 的一个 EXPLAIN 结果。

常见低分回答

只知道 EXPLAIN：这是最常见的低分回答。当被问及“真实耗时”时，如果只回答 EXPLAIN，说明候选人缺乏实际的性能调优经验，混淆了估算计划和实际执行。
在生产主库随意使用 EXPLAIN ANALYZE：直接说在生产库上对任意 SQL 执行 EXPLAIN ANALYZE，而未提及对写操作的风险或使用事务回滚的技巧，会显得对生产环境缺乏敬畏心。
不知道 Performance Schema：对于一个资深 DBA 或后端工程师，不了解 Performance Schema 意味着其知识体系停留在比较旧的 MySQL 版本，缺乏对现代 MySQL 监控工具的了解。
混淆 Rows Examined 和 Rows Sent：在分析慢查询时，无法清晰解释这两个指标的区别和重要性。Rows Examined 远大于 Rows Sent 通常意味着存在索引问题或查询逻辑不佳。
认为 EXPLAIN 的 rows 字段是精确的：强调 EXPLAIN 的 rows 是精确的扫描行数，这是一个概念性错误。

工程实践与优化

工具组合拳：
1. 日常巡检：通过 pt-query-digest 定期分析慢查询日志，发现共性的慢查询问题。
2. 问题复现：在只读副本或测试环境中，使用 EXPLAIN ANALYZE 对 pt-query-digest 发现的典型慢查询进行精确分析，获取其真实的执行计划和耗时分布。
3. 深度诊断：如果问题难以复现或与锁、临时表等瞬时状态有关，可以在生产环境临时开启 Performance Schema 的相关 instruments，抓取特定会话的阶段耗时信息。
EXPLAIN ANALYZE 的安全使用：
- 优先在只读副本上执行，避免影响主库。
- 对 SELECT 语句，使用 BEGIN; ... ROLLBACK; 将其包裹，确保无副作用。
- 对 UPDATE/DELETE，如果必须在主库分析，可先 SELECT 出要修改的行的主键，然后在事务中对单条记录执行 EXPLAIN ANALYZE，并立即 ROLLBACK。
Performance Schema 的开销管理：
- 生产环境不要开启所有的 instruments，按需开启。
- 诊断结束后，及时关闭不再需要的 instruments 和 consumers，以减少性能开销。
- 监控 Performance Schema 本身的内存占用 (SHOW ENGINE PERFORMANCE_SCHEMA STATUS;)。
关注执行计划的稳定性：有时同一条 SQL 在不同时间执行计划会变化。可以使用 optimizer hints（如 USE INDEX, FORCE INDEX）或创建 SQL Plan Management (SPM) 基线来固定高效的执行计划。

常见追问与延伸题目

追问：EXPLAIN 和 EXPLAIN ANALYZE 的结果不一致，以哪个为准？为什么会不一致？
- 回答要点：
  - 以 EXPLAIN ANALYZE 为准，因为它展示的是真实的执行情况。
  - 不一致的原因：
    - 统计信息陈旧：EXPLAIN 依赖于表的统计信息（如 Cardinality），如果数据分布发生巨大变化而未及时 ANALYZE TABLE，优化器的估算（rows, cost）会严重偏离实际。
    - 成本模型局限：优化器的成本模型是基于一系列固定的成本常数（如 I/O 成本、CPU 成本）进行计算的，这个模型本身就是一种近似，可能与真实的硬件和负载情况不符。
    - 缓存命中率：EXPLAIN 无法预知执行时数据页是否在 Buffer Pool 中。EXPLAIN ANALYZE 第一次执行可能涉及大量物理 I/O，而第二次执行可能因为数据已在缓存中而变得飞快，其 actual time 会真实反映这一点。
    - 执行期间的优化：某些优化（如 InnoDB 的 AHI 自适应哈希索引）是在运行时动态生效的，EXPLAIN 无法预知。
追问：如果我用的是 MySQL 5.7，没有 EXPLAIN ANALYZE，如何精确分析一条 SQL 的耗时瓶颈？
- 回答要点：这是一个很好的问题，考察候选人对老版本工具的掌握。
  - 方法一：SHOW SESSION STATUS：
    1. FLUSH SESSION STATUS; 清空当前会话的计数器。
    2. 执行慢查询 SQL。
    3. SHOW SESSION STATUS LIKE 'Handler%'; 查看 Handler_read_* 系列状态变量。Handler_read_rnd_next 激增表示在进行全表扫描；Handler_read_key 表示使用了索引。通过这些底层计数器可以反推出存储引擎的真实访问模式。
  - 方法二：Performance Schema (5.7 版本)：MySQL 5.7 已经有 Performance Schema，虽然功能不如 8.0 完善，但核心的 events_stages_history_long 已经存在。可以按照前述方法，开启相关 instruments，然后查询该表来分析 SQL 执行的各个阶段耗时。
  - 方法三：EXPLAIN + SHOW WARNINGS：执行 EXPLAIN 后，立刻执行 SHOW WARNINGS。有时优化器会对查询进行重写（例如，类型转换），SHOW WARNINGS 会显示重写后的 SQL，这有助于发现一些隐式转换导致的索引失效问题。
追问：在生产环境，如何安全地、低成本地开启慢查询日志来抓取问题 SQL？
- 回答要点：
  - 动态设置，非永久：通过 SET GLOBAL long_query_time = 0.5; 动态开启，问题定位后可调回更高值或关闭 (SET GLOBAL long_query_time = 10;)。避免直接修改配置文件并重启服务。
  - 采样记录：在 MySQL 5.6+，可以使用 log_slow_rate_limit 参数进行采样。例如，SET GLOBAL log_slow_rate_limit = 100; 表示每 100 个满足条件的慢查询只记录 1 个，这可以在高 QPS 场景下大幅减少日志量和 I/O 开销。
  - 不记录未使用索引的查询：如果只想关注那些确实很慢的查询，而不是所有未使用索引的查询，确保 log_queries_not_using_indexes 是关闭的，以避免日志爆炸。
  - 日志轮转 (Log Rotation)：配置 logrotate 或类似工具对慢查询日志进行定期轮转和压缩，防止单个日志文件过大，消耗磁盘空间。

考察要点

核心概念

1. EXPLAIN: 估算执行计划

2. EXPLAIN ANALYZE: 真实执行计划与耗时 (MySQL 8.0.18+)

3. Performance Schema: 低开销的性能数据采集