【结论】通过 performance_schema 的 events_waits 表联合 sys.schema,可拆解单条查询耗时为 Buffer Pool IO 等待、锁等待、CPU 时间三部分。关键步骤如下:
-
关联语句与等待事件
- 查询语句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; -- 筛选高耗时语句
- 查询语句ID:
-
拆解等待类型
- 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';
- Buffer Pool IO:
-
结合 sys.schema 分析
- 检查锁等待关联:
SELECT * FROM sys.schema_lock_waits WHERE object_schema = 'your_db' AND object_name = 't_order'; - 验证索引使用:
确认是否使用 idx_user_status 索引,若出现EXPLAIN SELECT * FROM t_order WHERE user_id = ? AND 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)
针对锁等待关联问题,建议使用 sys.schema_lock_waits 联查 object_schema/object_name 和等待线程ID。例如:SELECT * FROM sys.schema_lock_waits WHERE object_schema='your_db' AND object_name='t_order' AND waiting_thread=123456; 可直接定位到具体事务的锁等待链路。