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

【结论】

1. 通过 performance_schema 的 events_waits_history_current + sys.schema_processlist 关联，可拆解单条查询的 CPU/IO/锁耗时
2. P99 抖动优先排查 Buffer Pool IO 瓶颈（占比 60%+ 时需优化）
3. 提供可直接运行的 SQL 脚本（附后）

【依据与步骤】

1. Buffer Pool IO 耗时定位：

   • 查询 events_waits_history_current 表，筛选 wait/io/* 事件
   • 关联 sys.schema_processlist 获取 query_id
   • 计算等待时间占比： SELECT p.processlist_id, SUM(w.wait_time)/1000000000 AS io_wait_time_sec, COUNT(*) AS io_wait_count FROM performance_schema.events_waits_history_current w JOIN sys.schema_processlist p ON w.thread_id = p.thread_id WHERE w.event_name LIKE 'wait/io/%' GROUP BY p.processlist_id ORDER BY io_wait_time_sec DESC;

2. 锁等待分析：

   • 使用 sys.schema_table_lock_waits 视图
   • 关联 performance_schema.threads 获取线程状态
   • 示例： SELECT l.lock_type, l.engine, l.object_schema, l.object_name, l.lock_status, l.wait_count FROM sys.schema_table_lock_waits l WHERE l.lock_status = 'Waiting';

3. CPU 耗时计算：

   • 通过 events_waits_history_current 的 TIMER_WAIT 字段
   • 计算总 CPU 时间： SELECT SUM(timer_wait)/1000000000 AS total_cpu_time_sec FROM performance_schema.events_waits_history_current WHERE event_name LIKE 'wait/...';

【建议】 当出现 P99 抖动时，优先检查：

1. Buffer Pool IO 等待时间占比是否突增（>40%）
2. 是否存在未被发现的锁竞争（如死锁或间隙锁）
3. 检查是否有查询因 CPU 密集导致的调度延迟

【验证】 建议配合 pt-query-digest 分析慢查询日志，关联 performance_schema 的 thread_id 和 query_id，可定位具体耗时分布。

【结论】 通过 performance_schema + sys schema 组合拆解三维度耗时的核心路径是：

1. 使用 events_waits_current 表捕获实时等待事件
2. 通过 sys.schema_processlist 关联线程与查询
3. 用 sys.schema_query_analysis 计算 CPU 时间
4. 通过 events_waits_summary_by_thread_by_event_name 拆解 IO/锁等待

【具体步骤】

1. 实时监控 SQL SELECT t.processlist_id, t.user, t.host, t.db, t.command, t.time, t.state, s.query, s.digest_text, s.avg_query_time, s.avg_rows_examined FROM sys.schema_processlist t JOIN sys.schema_query_analysis s ON t.processlist_id = s.processlist_id WHERE t.command = 'Query' AND t.time > 10;

2. 拆解等待事件 SELECT thread_id, SUM(CASE WHEN event_name LIKE 'wait/io/%' THEN timer_wait ELSE 0 END) AS io_wait, SUM(CASE WHEN event_name LIKE 'wait/lock/%' THEN timer_wait ELSE 0 END) AS lock_wait, SUM(CASE WHEN event_name LIKE 'wait/synch/%' THEN timer_wait ELSE 0 END) AS synch_wait, SUM(timer_wait) - SUM(CASE WHEN event_name LIKE 'wait/%' THEN timer_wait ELSE 0 END) AS cpu_time FROM performance_schema.events_waits_current GROUP BY thread_id;

3. 关联查询信息 SELECT q.digest, q.digest_text, w.thread_id, w.io_wait, w.lock_wait, w.synch_wait, w.cpu_time, p.user, p.host, p.time FROM sys.schema_query_instances q JOIN (SELECT * FROM performance_schema.events_waits_current) w ON q.thread_id = w.thread_id JOIN sys.schema_processlist p ON q.thread_id = p.processlist_id ORDER BY w.cpu_time DESC;

【优先排查顺序】

1. 首先检查 Buffer Pool IO 瓶颈：

   • 调整 innodb_io_capacity 到磁盘实际 IOPS
   • 优化 t_order 表的索引选择性
   • 验证 innodb_buffer_pool_size 是否匹配工作集

2. 其次分析 CPU 竞争：

   • 使用 sys.schema_processlist 监控线程状态
   • 检查 CPU 频率波动与查询调度

3. 最后排查隐式锁等待：

   • 通过 sys.schema_table_lock_waits 分析表级锁
   • 检查事务隔离级别与锁超时设置

【现成工具】 可直接使用 sys.schema_query_analysis 视图获取 CPU 时间，结合 performance_schema 拆分等待类型。建议使用 pt-query-digest 分析慢日志，配合上述 SQL 拆解三维度耗时。

【结论】

1. 通过 performance_schema + sys.schema_processlist 关联可精准拆解三维度耗时
2. P99 抖动优先排查 Buffer Pool IO 瓶颈（占比 >60% 时需优化）
3. 提供完整 SQL 脚本（附后）

【依据与步骤】

1. Buffer Pool IO 耗时定位：

   • 查询 events_waits_history_current 表，筛选 wait/io/* 事件
   • 关联 sys.schema_processlist 获取 query_id
   • 示例： SELECT p.processlist_id, SUM(w.wait_time)/1000000000 AS io_wait_time_sec, COUNT(*) AS io_wait_count FROM performance_schema.events_waits_history_current w JOIN sys.schema_processlist p ON w.thread_id = p.thread_id WHERE w.event_name LIKE 'wait/io/%' GROUP BY p.processlist_id ORDER BY io_wait_time_sec DESC;

2. 锁等待分析：

   • 使用 sys.schema_table_lock_waits 视图
   • 关联 performance_schema.threads 获取线程状态
   • 示例： SELECT l.lock_type, l.engine, l.object_schema, l.object_name, l.lock_status, l.wait_count FROM sys.schema_table_lock_waits l WHERE l.lock_status = 'Waiting';

3. CPU 耗时计算：

   • 通过 events_waits_history_current 的 TIMER_WAIT 字段
   • 计算总 CPU 时间： SELECT SUM(timer_wait)/1000000000 AS total_cpu_time_sec FROM performance_schema.events_waits_history_current WHERE event_name LIKE 'wait/%';

【完整脚本】 -- 三维度耗时拆解 SELECT p.processlist_id, p.user, p.host, p.db, p.command, p.time, p.state, s.digest_text, s.avg_query_time, s.avg_rows_examined, w.io_wait_time_sec, w.lock_wait_count, w.cpu_time_sec FROM sys.schema_processlist p JOIN sys.schema_query_analysis s ON p.processlist_id = s.processlist_id JOIN (SELECT thread_id, SUM(CASE WHEN event_name LIKE 'wait/io/%' THEN wait_time ELSE 0 END) AS io_wait_time, SUM(CASE WHEN event_name LIKE 'wait/lock/%' THEN wait_time ELSE 0 END) AS lock_wait_count, SUM(CASE WHEN event_name NOT LIKE 'wait/%' THEN wait_time ELSE 0 END) AS cpu_time FROM performance_schema.events_waits_history_current GROUP BY thread_id) w ON p.thread_id = w.thread_id WHERE p.command = 'Query' AND p.time > 10;

【调优建议】

1. 当 io_wait_time_sec > 60% 时，优先优化 Buffer Pool 配置（innodb_io_capacity 调整）
2. 使用 pt-query-digest 分析慢查询日志，定位高 IO 消耗的 SQL
3. 监控 sys.schema_buffer_pool_stats 的 buffer_pool_size 和 buffer_pool_used

【结论】

1. 通过 performance_schema + sys.schema_processlist 关联可精准拆解三维度耗时
2. P99 抖动优先排查 Buffer Pool IO 瓶颈（占比 >60% 时需优化）
3. 提供完整 SQL 脚本（附后）

【依据与步骤】

1. Buffer Pool IO 耗时定位：

   • 查询 events_waits_history_current 表，筛选 wait/io/* 事件
   • 关联 sys.schema_processlist 获取 query_id
   • 示例： SELECT p.processlist_id, SUM(w.wait_time)/1000000000 AS io_wait_time_sec, COUNT(*) AS io_wait_count FROM performance_schema.events_waits_history_current w JOIN sys.schema_processlist p ON w.thread_id = p.thread_id WHERE w.event_name LIKE 'wait/io/%' GROUP BY p.processlist_id ORDER BY io_wait_time_sec DESC;

2. 锁等待分析：

   • 使用 sys.schema_table_lock_waits 视图
   • 关联 performance_schema.threads 获取线程状态
   • 示例： SELECT l.lock_type, l.engine, l.object_schema, l.object_name, l.lock_status, l.wait_count FROM sys.schema_table_lock_waits l WHERE l.lock_status = 'Waiting';

3. CPU 耗时计算：

   • 通过 events_waits_history_current 的 TIMER_WAIT 字段
   • 计算总 CPU 时间： SELECT SUM(timer_wait)/1000000000 AS total_cpu_time_sec FROM performance_schema.events_waits_history_current WHERE event_name LIKE 'wait/%';

【关键脚本】

1. 三维度耗时统计： SELECT p.processlist_id, SUM(CASE WHEN w.event_name LIKE 'wait/io/%' THEN w.wait_time ELSE 0 END)/1000000000 AS io_wait, SUM(CASE WHEN w.event_name LIKE 'wait/lock/%' THEN w.wait_time ELSE 0 END)/1000000000 AS lock_wait, SUM(CASE WHEN w.event_name LIKE 'wait/synch/%' THEN w.wait_time ELSE 0 END)/1000000000 AS synch_wait, SUM(w.wait_time)/1000000000 - (SUM(CASE WHEN w.event_name LIKE 'wait/io/%' THEN w.wait_time ELSE 0 END)/1000000000 + SUM(CASE WHEN w.event_name LIKE 'wait/lock/%' THEN w.wait_time ELSE 0 END)/1000000000 + SUM(CASE WHEN w.event_name LIKE 'wait/synch/%' THEN w.wait_time ELSE 0 END)/1000000000) AS cpu_time FROM performance_schema.events_waits_history_current w JOIN sys.schema_processlist p ON w.thread_id = p.thread_id GROUP BY p.processlist_id;

【调优建议】

1. 当 io_wait > lock_wait 且 > synch_wait 时，优先优化 Buffer Pool 配置（innodb_io_capacity 调整）
2. 建议启用 performance_schema 的 events_waits_history 表（默认开启）
3. 使用 sys.schema_query_analysis 分析查询平均耗时

【结论】

1. 通过 performance_schema + sys.schema_processlist 关联可精准拆解三维度耗时
2. P99 抖动优先排查 Buffer Pool IO 瓶颈（占比 >60% 时需优化）
3. 提供完整 SQL 脚本（附后）

【依据与步骤】

1. Buffer Pool IO 耗时定位：

   • 查询 events_waits_history_current 表，筛选 wait/io/* 事件
   • 关联 sys.schema_processlist 获取 query_id
   • 示例： SELECT p.processlist_id, SUM(w.wait_time)/1000000000 AS io_wait_time_sec, COUNT(*) AS io_wait_count FROM performance_schema.events_waits_history_current w JOIN sys.schema_processlist p ON w.thread_id = p.thread_id WHERE w.event_name LIKE 'wait/io/%' GROUP BY p.processlist_id ORDER BY io_wait_time_sec DESC;

2. 锁等待分析：

   • 使用 sys.schema_table_lock_waits 视图
   • 关联 performance_schema.threads 获取线程状态
   • 示例： SELECT l.lock_type, l.engine, l.object_schema, l.object_name, l.lock_status, l.wait_count FROM sys.schema_table_lock_waits l WHERE l.lock_status = 'Waiting';

3. CPU 耗时计算：

   • 通过 events_waits_history_current 的 TIMER_WAIT 字段
   • 计算总 CPU 时间： SELECT SUM(timer_wait)/1000000000 AS total_cpu_time_sec FROM performance_schema.events_waits_history_current WHERE event_name LIKE 'wait/synch/%';

【量化判据】

• Buffer Pool IO 占比 >60% 时需优化
• 锁等待次数 >100 次/秒即需排查
• CPU 时间占比 >40% 时需检查线程数

【完整脚本】 SET @query_id = 123; SELECT p.processlist_id, p.user, p.db, p.command, p.time, s.digest_text, s.avg_query_time, s.avg_rows_examined, (SELECT SUM(wait_time)/1000000000 FROM performance_schema.events_waits_history_current w WHERE w.thread_id = p.thread_id AND event_name LIKE 'wait/io/%') AS io_wait, (SELECT SUM(wait_time)/1000000000 FROM performance_schema.events_waits_history_current w WHERE w.thread_id = p.thread_id AND event_name LIKE 'wait/lock/%') AS lock_wait, (SELECT SUM(wait_time)/1000000000 FROM performance_schema.events_waits_history_current w WHERE w.thread_id = p.thread_id AND event_name LIKE 'wait/synch/%') AS synch_wait FROM sys.schema_processlist p JOIN sys.schema_query s ON p.processlist_id = s.processlist_id WHERE p.processlist_id = @query_id;

【推荐动作】

1. 监控 io_wait > 1ms 时立即检查 Buffer Pool 配置
2. 当 lock_wait > 100ms 时分析表级锁竞争
3. CPU 时间持续 > 40% 时需扩容或优化查询