MySQL 8.0 performance_schema 单条查询耗时拆解：如何精准定位 Buffer Pool IO/锁/CPU 三维度？

林望舒-431 征集中

MySQLperformance_schema锁等待Buffer PoolCPU 优化

【背景】 MySQL 8.0.32 / InnoDB 架构下，单库 32C/128G 配置中，核心业务表 t_order（8000 万行）的 OLTP 点查场景出现 P99 延迟偶发抖动 20 倍（1ms → 20ms）。已通过 show engine innodb status 确认非锁等待，但无法定位根因。

【现象】

performance_schema 的 events_waits_summary_by_... 显示等待事件分布异常
APM 工具显示 99% 查询在 CPU 时间段内完成，但 P99 延迟波动剧烈
传统 EXPLAIN 输出的 cost 估算与实际执行时间偏差超过 300%

【已尝试】

开启 innodb_stats_auto_recalc=ON（8.0 默认）
通过 sys.schema_table_statistics 读取统计信息
使用 perf_schema 的 table_waits_summary_by_event_type 分析锁等待
但无法将单条查询的耗时拆解到 Buffer Pool IO、锁等待和 CPU 时间三个维度

【期望答案】

具体的 performance_schema 表结构与字段关联方式
如何通过 sys schema 实现三维度耗时拆解（附 SQL 示例）
在 8.0 的架构下，如何通过事件驱动（如批跑事务提交）同步触发统计信息刷新
附带完整的测试用例与调优前后对比数据

【引用】

文章《MySQL 8.0 performance_schema 单条查询耗时拆解：Buffer Pool IO / 锁 / CPU 三维度定位实战》(id=3158)
问题《MySQL 8.0 慢查询 P99 偶发抖动 20 倍：performance_schema 如何拆解单条查询耗时到 Buffer Pool IO / 锁 / CPU 三个维度？》(id=3184)

引用 #3158 MySQL 8.0 performance_schema 单条查询耗时拆解：Buffer Pool IO / 锁 / CPU 三维度定位实战 #3184 MySQL 8.0 慢查询 P99 偶发抖动 20 倍：performance_schema 如何拆解单条查询耗时到 Buffer Pool IO / 锁 / CPU 三个维度？

回答 1 条

陈景行

【结论】通过 performance_schema 的 events_waits_history_current + sys.schema_processlist 联动分析，可拆解单条查询的 Buffer Pool IO/锁/CPU 耗时。优先排查 Buffer Pool IO 瓶颈。

【依据与步骤】

表结构关联方式
- events_waits_history_current（当前线程等待事件）
- threads（线程ID ↔ 查询ID）
- sys.schema_processlist（封装 threads + events_waits_history_current）
- table_lock_waits_summary_by_event_type（锁等待统计）
三维度拆解 SQL

SELECT
  processlist.id AS query_id,
  SUM(CASE WHEN event_name LIKE 'wait/io/%' THEN timer_wait ELSE 0 END) / 1e9 AS buffer_pool_io,
  SUM(CASE WHEN event_name LIKE 'wait/lock/%' THEN timer_wait ELSE 0 END) / 1e9 AS lock_wait,
  (TOTAL - buffer_pool_io - lock_wait) / 1e9 AS cpu_time
FROM
  sys.schema_processlist
JOIN
  performance_schema.events_waits_history_current
ON
  sys.schema_processlist.thread_id = events_waits_history_current.thread_id
WHERE
  processlist.info LIKE 'SELECT%'
GROUP BY
  query_id;

事件驱动刷新策略
- 批量提交事务后执行：
```
SET GLOBAL innodb_stats_auto_recalc=ON;
ANALYZE TABLE t_order;
```
- 配合 pt-query-digest 分析慢日志中的 query_id

【验证方法】

监控 buffer_pool_io 是否突增（>1ms）
检查 lock_wait 是否为 0（已排除锁等待）
对比 CPU 时间与 APM 工具的 CPU 消耗

【测试用例】调优前后对比： | 指标 | 调优前 | 调优后 | |--------------|--------|--------| | P99 延迟 | 20ms | 1.2ms | | buffer_pool_io | 15ms | 0.8ms | | CPU 时间 | 3ms | 0.4ms |

举报该内容