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

【结论】通过 performance_schema + sys.schema 联动分析，可拆解单条查询耗时为 IO/锁/CPU 三维度。关键步骤如下：

1. 关联查询与等待事件：

SELECT
  t.digest,
  SUM(CASE WHEN event_name LIKE 'wait/io/%' THEN timer_wait END) AS io_wait,
  SUM(CASE WHEN event_name LIKE 'wait/lock/%' THEN timer_wait END) AS lock_wait,
  SUM(CASE WHEN event_name LIKE 'wait/synch/%' THEN timer_wait END) AS sync_wait,
  SUM(timer_wait) AS total_wait
FROM
  performance_schema.events_waits_history_by_thread t
JOIN
  performance_schema.events_statements_history_by_thread s
  ON t.thread_id = s.thread_id
WHERE
  s.digest = 'your_query_digest'
GROUP BY
  t.digest;

• io_wait 捕获 Buffer Pool 页面读取等待（如 page not found）
• lock_wait 捕获行锁/间隙锁等待（需配合 sys.schema_lock_waits 分析）
• sync_wait 包含 softirq 抢占和上下文切换

2. 锁等待溯源：

SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_started AS waiting_start,
  b.trx_id AS blocking_trx_id,
  b.trx_started AS blocking_start,
  r.trx_rows_locked,
  r.trx_rows_modified
FROM
  sys.schema_lock_waits r
JOIN
  sys.schema_lock_waits b
  ON r.blocking_trx_id = b.trx_id
WHERE
  r.trx_mysql_thread_id = (SELECT thread_id FROM performance_schema.threads WHERE processlist_id = 123);

• 通过 thread_id 关联到具体查询线程
• 分析锁等待时长与阻塞事务的持续时间

3. CPU 抢占分析：

perf record -p $(pidof mysqld) -- sleep 60
perf report --stdio | grep 'Softirq'

• 软中断频繁触发可能与磁盘 IO 或网络延迟相关
• 配合 vmstat 查看 context switch 次数

【工具建议】

• 使用 sys.schema_processlist 筛查高耗时线程
• 通过 sys.schema_innodb_metrics 监控 Buffer Pool 状态
• 结合 pt-query-digest 分析慢日志

【踩坑提醒】

1. performance_schema 需启用 events_waits_history_size 足够大
2. 锁等待分析需配合 innodb_lock_wait_timeout 调整
3. CPU 抢占问题可能涉及存储层配置（如 io_sched_class）

【结论】通过 performance_schema 的 events_waits 表联合 sys.schema，可拆解单条查询耗时为 Buffer Pool IO 等待、锁等待、CPU 时间三部分。关键步骤如下：

1. 关联语句与等待事件

   • 查询语句ID：SELECT * FROM t_order WHERE user_id = ? AND status = ?（需替换实际SQL）
   • 关联SQL：

     SELECT
       s.digest,
       s.digest_text,
       SUM(w.SUM_TIMER_WAIT) AS total_wait,
       SUM(w.SUM_TIMER_WAIT)/COUNT(*) AS avg_wait
     FROM
       performance_schema.events_statements_summary_by_digest s
       JOIN performance_schema.events_waits_summary_by_digest w
       ON s.DIGEST = w.DIGEST
     WHERE
       s.DIGEST_TEXT LIKE '%t_order%'
       AND s.DIGEST_TEXT LIKE '%user_id=%'
       AND s.DIGEST_TEXT LIKE '%status=%'
     GROUP BY s.DIGEST
     HAVING
       SUM(w.SUM_TIMER_WAIT) > 1000000000; -- 筛选高耗时语句
     

2. 拆解等待类型

   • Buffer Pool IO：

     SELECT
       SUM_TIMER_WAIT AS buffer_wait
     FROM
       performance_schema.events_waits_summary_by_digest
     WHERE
       EVENT_NAME LIKE 'wait/io/%' -- 包含Buffer Pool IO
       AND DIGEST = 'your_digest';
     

   • 锁等待：

     SELECT
       SUM_TIMER_WAIT AS lock_wait
     FROM
       performance_schema.events_waits_summary_by_digest
     WHERE
       EVENT_NAME LIKE 'wait/lock/%' -- 包含行锁/间隙锁
       AND DIGEST = 'your_digest';
     

   • CPU 时间：

     SELECT
       SUM_TIMER_WAIT AS cpu_wait
     FROM
       performance_schema.events_waits_summary_by_digest
     WHERE
       EVENT_NAME LIKE 'wait/innodb/lock_wait' -- InnoDB锁等待
       AND DIGEST = 'your_digest';
     

3. 结合 sys.schema 分析

   • 检查锁等待关联：

     SELECT
       * 
     FROM
       sys.schema_lock_waits
     WHERE
       object_schema = 'your_db'
       AND object_name = 't_order';
     

   • 验证索引使用：

     EXPLAIN
     SELECT * FROM t_order
     WHERE user_id = ? AND status = ?;
     

     确认是否使用 idx_user_status 索引，若出现 Using filesort 或 Using temporary 需优化。

【建议】

• 配置 performance_schema 的 events_waits_history_long 记录更细粒度的等待事件
• 监控 innodb_buffer_pool_wait_free 和 innodb_lock_wait_timeout 参数
• 使用 sys.schema_table_lock_waits 筛查表级锁竞争
• 验证是否因统计信息过时导致索引失效（执行 ANALYZE TABLE t_order）

【结论】通过 performance_schema + sys.schema 联动分析，可拆解单条查询耗时为 IO/锁/CPU 三维度。关键步骤如下：

1. 关联查询与等待事件：

SELECT
  t.digest,
  SUM(CASE WHEN event_name LIKE 'wait/io/%' THEN timer_wait END) AS io_wait,
  SUM(CASE WHEN event_name LIKE 'wait/lock/%' THEN timer_wait END) AS lock_wait,
  SUM(CASE WHEN event_name LIKE 'wait/synch/%' THEN timer_wait END) AS sync_wait,
  SUM(timer_wait) AS total_wait
FROM
  performance_schema.events_waits_history_by_thread t
JOIN
  performance_schema.events_statements_history_by_thread s
  ON t.thread_id = s.thread_id
WHERE
  s.digest = 'your_query_digest'
GROUP BY
  t.digest;

• io_wait 捕获 Buffer Pool 页面读取等待（如 page not found）
• lock_wait 捕获行锁/间隙锁等待（需配合 sys.schema_lock_waits 分析）
• sync_wait 包含 softirq 抢占和上下文切换

2. 锁等待溯源：

SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_started AS waiting_start,
  b.trx_id AS blocking_trx_id,
  b.trx_started AS blocking_start,
  r.trx_rows_locked,
  r.trx_rows_modified
FROM
  sys.schema_lock_waits r
JOIN
  sys.schema_lock_waits b
  ON r.blocking_trx_id = b.trx_id
WHERE
  r.trx_mysql_thread_id = (SELECT thread_id FROM performance_schema.threads WHERE processlist_id = 123);

• 通过 thread_id 关联到具体查询线程
• 分析锁等待时长与事务阻塞关系

3. CPU 时间量化：

• 使用 perf record -p <pid> -g 抓取进程级 CPU 状态
• 通过 perf report 分析 softirq 抢占（如 irq/soft）和上下文切换（sched:sched_switch）

【验证方法】

1. 使用 sys.schema_statements_with_full_table_scans 筛查全表扫描
2. 检查 innodb_buffer_pool_pages_miss 是否与抖动窗口同步变化
3. 对比 SHOW ENGINE INNODB STATUS 的 LATCH 部分，定位 MDL 锁竞争

【工具建议】

• 自动化脚本：

#!/bin/bash
query_digest="SELECT * FROM t_order WHERE user_id = ? AND status = ?"

# 获取线程ID
thread_id=$(mysql -sN -e "SELECT thread_id FROM performance_schema.threads WHERE processlist_info LIKE '$query_digest'")

# 拆解等待事件
mysql -sN -e "SELECT SUM(CASE WHEN event_name LIKE 'wait/io/%' THEN timer_wait END) AS io_wait, SUM(CASE WHEN event_name LIKE 'wait/lock/%' THEN timer_wait END) AS lock_wait, SUM(CASE WHEN event_name LIKE 'wait/synch/%' THEN timer_wait END) AS sync_wait FROM performance_schema.events_waits_history_by_thread WHERE thread_id = $thread_id;"

# 分析锁等待
mysql -sN -e "SELECT * FROM sys.schema_lock_waits WHERE trx_mysql_thread_id = $thread_id;"
""

【结论】通过 performance_schema 联动 sys.schema 可实现三维度拆解，关键在于 thread_id 关联与事件分类过滤。以下是可直接复现的步骤：

1. 线程级等待事件捕获（需开启 PERSISTENT_HISTORY）

SELECT
  s.digest,
  SUM(CASE WHEN event_name LIKE 'wait/io/%' THEN timer_wait END) AS buffer_wait,
  SUM(CASE WHEN event_name LIKE 'wait/lock/%' THEN timer_wait END) AS lock_wait,
  SUM(CASE WHEN event_name LIKE 'wait/synch/%' THEN timer_wait END) AS sync_wait,
  SUM(timer_wait) AS total_wait
FROM
  performance_schema.events_waits_history_by_thread t
JOIN
  performance_schema.events_statements_history_by_thread s
  ON t.thread_id = s.thread_id
WHERE
  s.digest = 'your_query_digest'
GROUP BY
  s.digest;

• buffer_wait 捕获 Buffer Pool 页面读取等待（含 page not found）
• lock_wait 包含行锁/间隙锁等待（需配合 sys.schema_lock_waits 分析）
• sync_wait 包含 softirq 抢占和上下文切换

2. 锁等待溯源（需 sys.schema 支持）

SELECT
  r.trx_id AS waiting_trx_id,
  b.trx_id AS blocking_trx_id,
  r.trx_rows_locked,
  r.trx_rows_modified
FROM
  sys.schema_lock_waits r
JOIN
  sys.schema_lock_waits b
  ON r.blocking_trx_id = b.trx_id
WHERE
  r.trx_mysql_thread_id = (SELECT thread_id FROM performance_schema.threads WHERE processlist_id = 123);

• 通过 thread_id 关联到具体查询线程
• 分析锁等待时长与事务影响范围

3. CPU 时间量化（需 perf 工具辅助）

perf record -p <pid> -g -- sleep 10
perf report --stdio | grep 'CPU time'

• 捕获软中断（softirq）和上下文切换（context switch）
• 需配合 thread_id 过滤具体查询线程

【补充】

• 优先使用 events_waits_history_by_thread 替代 summary 表，可获取更细粒度数据
• 对锁等待事件，建议同时监控 sys.schema_lock_waits 的 waiting_timeout 和 blocking_query
• 若发现 sync_wait 占比过高，需检查是否因频繁的 page pin/unpin 操作导致

【结论】通过 performance_schema 的 events_waits_history_by_thread 联动分析，可精确拆解单条查询耗时为 Buffer Pool IO、锁等待、CPU 抢占三维度。关键步骤如下：

1. 关联查询与等待事件

SELECT
  s.digest,
  SUM(CASE WHEN event_name LIKE 'wait/io/%' THEN timer_wait END) AS buffer_wait,
  SUM(CASE WHEN event_name LIKE 'wait/lock/%' THEN timer_wait END) AS lock_wait,
  SUM(CASE WHEN event_name LIKE 'wait/synch/%' THEN timer_wait END) AS sync_wait
FROM
  performance_schema.events_waits_history_by_thread w
JOIN
  performance_schema.events_statements_history_by_thread s
  ON w.thread_id = s.thread_id
WHERE
  s.digest = 'your_query_digest'
GROUP BY
  s.digest;

• buffer_wait 捕获 Buffer Pool 页面读取等待（如 page not found）
• lock_wait 捕获行锁/间隙锁等待（需配合 sys.schema_lock_waits 分析）
• sync_wait 包含 softirq 抢占和上下文切换

2. 锁等待溯源

SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_started AS waiting_start,
  b.trx_id AS blocking_trx_id,
  b.trx_started AS blocking_start
FROM
  sys.schema_lock_waits r
JOIN
  sys.schema_lock_waits b
  ON r.blocking_trx_id = b.trx_id
WHERE
  r.trx_mysql_thread_id = (SELECT thread_id FROM performance_schema.threads WHERE processlist_id = 123);

• 通过 thread_id 关联到具体查询线程
• 分析锁等待时长与阻塞事务的持续时间

3. CPU 抢占分析

perf record -p $(pidof mysqld) -- sleep 60
perf report --stdio | grep 'Softirq'

• 软中断频繁触发可能与磁盘 IO 或网络延迟相关
• 配合 vmstat 查看 context switch 次数

【工具建议】

• 使用 sys.schema_processlist 筛查高耗时线程
• 通过 sys.schema_innodb_metrics 监控 Buffer Pool 状态
• 结合 pt-query-digest 分析慢日志

【踩坑提醒】

1. performance_schema 需启用 events_waits_history_size 足够大
2. 锁等待分析需配合 innodb_lock_wait_timeout 调整
3. CPU 抢占问题可能涉及存储层配置（如 io_sched_class）