MySQL 8.0 慢查询 P99 偶发抖动 20 倍：performance_schema 如何拆解单条查询耗时到 Buffer Pool IO / 锁 / CPU 三个维度？

【结论】通过组合使用 events_waits_summary_by_thread_by_event_name（IO/锁）+ sys.schema_processlist（CPU）+ thread_id 关联，可拆解单条查询三维度耗时。P99 抖动优先排查 Buffer Pool IO 竞争。

【依据与步骤】

1. Buffer Pool IO 拆解

   • 查询 events_waits_summary_by_thread_by_event_name 表，过滤 event_name='wait/io/buffer_pool' 的等待时间
   • 使用 sys.schema_processlist 视图获取线程 CPU 使用率
   • 通过 thread_id 关联 threads 表获取线程状态

2. 锁等待定位（虽已排除，但需验证）

   • 查询 table_lock_waits_summary_by_table 表，确认无锁等待
   • 检查 sys.schema_table_lock_waits 视图的 lock_wait_time

3. CPU 时间计算

   • 通过 sys.schema_processlist.time 字段获取线程 CPU 时间
   • 用总执行时间减去等待时间（IO + 锁）得 CPU 时间

4. 异常抖动排查

   • 使用 events_waits_history 表抓取突发等待事件
   • 分析 sys.schema_processlist 的 time 字段波动
   • 检查 Buffer Pool 状态：SHOW ENGINE INNODB STATUS | grep 'BUFFER POOL and memory'

【结论】

1. 通过 performance_schema.events_waits_history_current + sys.schema_processlist 关联可拆解单条查询的 Buffer Pool IO/锁/CPU 时间
2. P99 抖动优先排查 Buffer Pool IO 瓶颈（使用 sys.schema_buffer_pool_stats）
3. 附 SQL 脚本：SELECT * FROM (SELECT ... ) AS io JOIN (SELECT ... ) AS lock ON ...

【依据与步骤】

1. 启用 instrumentation 点： SET GLOBAL performance_schema_events_waits_history_long_size = 1000; SET GLOBAL performance_schema_events_waits_history_current_size = 1000;

2. 关键表关联： SELECT p.processlist_id, p.user, p.host, p.db, p.command, p.time, p.state, w.event_name, w.timer_wait AS total_time, w.count_star FROM sys.schema_processlist p JOIN performance_schema.events_waits_history_current w ON p.processlist_id = w.thread_id WHERE p.command = 'Query';

3. 三维度拆解：

   • IO：SELECT * FROM sys.schema_buffer_pool_stats WHERE ...
   • 锁：SELECT * FROM sys.schema_lock_waits WHERE ...
   • CPU：SELECT * FROM sys.schema_processlist WHERE ...

【验证】

1. 通过 sys.schema_processlist.time 字段监控单条查询耗时
2. 使用 sys.schema_buffer_pool_stats.memory_used / buffer_pool_size 计算 Buffer Pool 使用率
3. 检查 events_waits_history_current 的 timer_wait 总和与实际 P99 对比

【结论】通过 events_waits_history_current + sys.schema_processlist 关联可拆解单条查询的 Buffer Pool IO/锁/CPU 时间，P99 抖动优先排查 Buffer Pool IO 瓶颈（使用 sys.schema_buffer_pool_stats）。

【依据与步骤】

1. 启用 instrumentation 点 SET GLOBAL performance_schema_events_waits_history_long_size = 1000; SET GLOBAL performance_schema_events_waits_history_current_size = 1000;

2. 关键表关联查询 SELECT p.processlist_id, p.user, p.host, p.db, p.command, p.time, p.state, w.event_name, w.timer_wait AS total_time, w.count_star FROM sys.schema_processlist p JOIN performance_schema.events_waits_history_current w ON p.processlist_id = w.thread_id WHERE p.command = 'Query';

3. 三维度拆解

   • IO 瓶颈：SELECT * FROM sys.schema_buffer_pool_stats WHERE ...
   • 锁等待：SELECT * FROM sys.schema_lock_waits WHERE ...
   • CPU 时间：SELECT * FROM sys.schema_processlist WHERE ...

【验证】

1. 通过 sys.schema_processlist.time 字段监控单条查询耗时
2. 使用 sys.schema_buffer_pool_stats.memory_used / buffer_pool_size 计算 Buffer Pool 使用率
3. 检查 events_waits_history_current 的 timer_wait 总和与实际 P99 对比

【调优建议】

• 设置 innodb_io_capacity=2000 提升 IO 吞吐
• 通过 sys.schema_buffer_pool_stats.memory_free 监控 Buffer Pool 空闲率
• 使用 pt-query-digest 分析慢日志定位热点 SQL

【结论】通过 performance_schema + sys schema 组合可精准拆解三维度耗时，P99 抖动优先排查 Buffer Pool IO 瓶颈。

【依据与步骤】

1. 事件关联与维度拆解

• 使用 sys.schema_processlist 关联 performance_schema.threads 表，定位具体线程ID
• 查询 events_waits_summary_by_thread_by_event_name 表，过滤 event_name='wait/io/io_buffer' 的 Buffer Pool IO 等待时间
• 通过 events_waits_current 表实时监控锁等待事件（event_name='wait/synch/mutex/innodb/'）
• CPU 时间可通过 events_waits_summary_by_thread_by_event_name 中 event_name='wait/condition/innodb/innodb_cond_wait' 捕获

2. 关键 SQL 脚本

-- 三维度耗时拆解视图
CREATE OR REPLACE VIEW query_cost_analysis AS
SELECT
  t.THREAD_ID,
  SUM(CASE WHEN event_name = 'wait/io/io_buffer' THEN 1 ELSE 0 END) AS buffer_pool_wait,
  SUM(CASE WHEN event_name LIKE 'wait/synch/mutex/innodb%' THEN 1 ELSE 0 END) AS lock_wait,
  SUM(CASE WHEN event_name = 'wait/condition/innodb/innodb_cond_wait' THEN 1 ELSE 0 END) AS cpu_wait
FROM performance_schema.events_waits_summary_by_thread_by_event_name t
GROUP BY THREAD_ID;

-- 关联线程信息
SELECT
  p.THREAD_ID,
  p.NAME AS thread_name,
  q.buffer_pool_wait,
  q.lock_wait,
  q.cpu_wait
FROM performance_schema.threads p
JOIN query_cost_analysis q ON p.THREAD_ID = q.THREAD_ID;

3. P99 抖动排查优先级

• 首先检查 Buffer Pool IO：

  SELECT
    SUM( TIMER_WAIT ) / COUNT(*) AS avg_io_wait,
    MAX( TIMER_WAIT ) AS max_io_wait
  FROM performance_schema.events_waits_summary_by_thread_by_event_name
  WHERE event_name = 'wait/io/io_buffer';
  

• 其次分析锁等待：

  SELECT
    SUM( TIMER_WAIT ) / COUNT(*) AS avg_lock_wait
  FROM performance_schema.events_waits_summary_by_thread_by_event_name
  WHERE event_name LIKE 'wait/synch/mutex/innodb%';
  

• 最后确认 CPU 瓶颈：

  SHOW ENGINE INNODB STATUS\G
  

【验证方法】

1. 使用 pt-query-digest 分析慢日志，筛选出 P99 抖动时段的查询
2. 通过 sys.schema_table_lock_waits 查看锁等待分布
3. 对比调优前后 Buffer Pool 命中率（SHOW ENGINE INNODB STATUS）
4. 监控 performance_schema.threads 表中的 THREAD_STATE 字段