找出开销最大的查询（performance_schema digest）

命令

SELECT SCHEMA_NAME AS db,
       LEFT(DIGEST_TEXT, 80) AS query,
       COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec,
       ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms,
       ROUND(QUANTILE_95 / 1e9, 2) AS p95_ms,
       ROUND(100 * SUM_TIMER_WAIT / SUM(SUM_TIMER_WAIT) OVER (), 1) AS pct_total,
       ROUND(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0)) AS rows_exam_per_call,
       ROUND(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0)) AS rows_sent_per_call,
       SUM_NO_INDEX_USED AS no_index,
       SUM_CREATED_TMP_DISK_TABLES AS tmp_disk,
       LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- Server-wide top wait events
SELECT events, total, total_latency, avg_latency FROM sys.waits_global_by_latency LIMIT 10;

作用

按总耗时对规范化查询排序，并给出调用次数、平均和 p95 延迟、占总耗时的比例、每次调用的扫描行数/返回行数、未使用索引的执行次数以及溢出到磁盘的临时表。第二个查询显示时间消耗在哪些等待上（IO、锁、redo）。

适用场景

CPU/IO 负载升高或用户反映“数据库很慢”时的第一站；即使慢查询日志关闭也可使用。

注意

数值是自上次重启或 TRUNCATE TABLE performance_schema.events_statements_summary_by_digest 以来的累计值；采集两次快照并求差值才能看到当前活动。该表最多 performance_schema_digests_size 行；写满后，新查询会汇总到一行 DIGEST 为 NULL 的记录中。DIGEST_TEXT 截断在 performance_schema_max_digest_length（1024）字节。时间单位为皮秒。rows_exam_per_call / rows_sent_per_call 比值高是缺少索引最明显的信号。