MySQL / 系统课时

用 Performance Schema 观察语句

用 Performance Schema 观察语句

单条慢日志适合还原一次异常,但要判断哪些 SQL 长期高频、高耗时或扫描量大,更适合看 Performance Schema 的语句事件与 digest 聚合。本篇会从采集状态检查开始,建立候选 SQL 排序方法,再回到单次事件补齐执行上下文。

学习目标

  • 确认语句事件与 digest 聚合是否正在采集
  • 用累计耗时、平均耗时、扫描行数识别高价值 SQL
  • 从 digest 回到单次事件,补充线程、错误与行数信息

先确认语句采集状态

以下示例按 MySQL 8.x 说明。先确认 Performance Schema 已启用,并检查与语句观察直接相关的 consumer。statements_digest 决定是否维护 digest 聚合,events_statements_history 与 events_statements_history_long 则决定是否保留近期单次事件。

sql
SHOW VARIABLES LIKE 'performance_schema';

SELECT NAME, ENABLED
FROM performance_schema.setup_consumers
WHERE NAME IN (
  'events_statements_current',
  'events_statements_history',
  'events_statements_history_long',
  'statements_digest'
)
ORDER BY NAME;

预期结果

text
performance_schema  ON

events_statements_current      <VALUE>
events_statements_history       <VALUE>
events_statements_history_long <VALUE>
statements_digest               <VALUE>

实际的 YES、NO 组合取决于实例配置。若 statements_digest 为 NO,后面的 digest 汇总不会持续积累;若只需要临时回看单次语句,可按需开启 history consumer,避免把“没有历史事件”误判成“业务没有执行 SQL”。

用 digest 建立稳定视角

events_statements_summary_by_digest 会把结构相同、仅字面量不同的语句归并到同一个 digest,因此比盯一条完整 SQL 更适合找长期热点。下面以 app 库为例,按累计执行时间找最值得先看的十类语句。

sql
SELECT
  SCHEMA_NAME,
  DIGEST_TEXT,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'app'
  AND DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

预期结果

text
+-------------+----------------------------------------------+------------+---------------+----------+-------------------+---------------+
| SCHEMA_NAME | DIGEST_TEXT                                  | COUNT_STAR | total_seconds | avg_ms   | SUM_ROWS_EXAMINED | SUM_ROWS_SENT |
+-------------+----------------------------------------------+------------+---------------+----------+-------------------+---------------+
| app         | SELECT ... FROM `orders` WHERE `user_id` = ? | <VALUE>    | <VALUE>       | <VALUE>  | <VALUE>           | <VALUE>       |
| app         | UPDATE `inventory` SET ... WHERE `sku` = ?   | <VALUE>    | <VALUE>       | <VALUE>  | <VALUE>           | <VALUE>       |
+-------------+----------------------------------------------+------------+---------------+----------+-------------------+---------------+

COUNT_STAR 看频率,SUM_TIMER_WAIT 看总资源贡献,AVG_TIMER_WAIT 看单次延迟,SUM_ROWS_EXAMINED 与 SUM_ROWS_SENT 的差距可帮助识别“大量扫描、少量返回”的语句。不要只按平均耗时排序:一条平均 5 ms、每分钟执行几十万次的 SQL,可能比偶发的 500 ms 查询更值得优先优化。

把聚合指标转成排查优先级

同一条 digest 至少从三个角度判断:是否执行得多、是否累计耗时高、是否扫描得重。实践中可以先按总耗时筛一轮,再计算平均每次扫描行数,区分“高频轻查询”和“低频重扫描”。

sql
SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
  ROUND(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 1) AS rows_examined_per_exec,
  ROUND(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 1) AS rows_sent_per_exec
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'app'
  AND COUNT_STAR > 0
ORDER BY rows_examined_per_exec DESC
LIMIT 10;

预期结果

text
+----------------------------------------------+------------+---------------+------------------------+--------------------+
| DIGEST_TEXT                                  | COUNT_STAR | total_seconds | rows_examined_per_exec | rows_sent_per_exec |
+----------------------------------------------+------------+---------------+------------------------+--------------------+
| SELECT ... FROM `orders` WHERE `status` = ?  | <VALUE>    | <VALUE>       | <VALUE>                | <VALUE>            |
+----------------------------------------------+------------+---------------+------------------------+--------------------+

如果单次扫描行数很高,而返回行数很少,应优先检查过滤条件、索引顺序和执行计划;如果扫描量并不大但累计耗时高,则还要考虑锁等待、排序、临时表、I/O 抖动或调用频率。digest 是“候选发现器”,不是根因判定器。

回到单次事件补上下文

聚合结果发现可疑 SQL 后,再看近期事件可以补上线程、错误码、实际返回行数等上下文。events_statements_history_long 是否有数据取决于对应 consumer 是否开启以及历史缓冲区是否已被覆盖。

sql
SELECT
  THREAD_ID,
  EVENT_ID,
  CURRENT_SCHEMA,
  SQL_TEXT,
  MYSQL_ERRNO,
  ROWS_EXAMINED,
  ROWS_SENT,
  ROUND(TIMER_WAIT / 1000000000, 2) AS elapsed_ms
FROM performance_schema.events_statements_history_long
WHERE CURRENT_SCHEMA = 'app'
  AND SQL_TEXT IS NOT NULL
ORDER BY TIMER_START DESC
LIMIT 10;

预期结果

text
+-----------+----------+----------------+----------------------+-------------+---------------+-----------+------------+
| THREAD_ID | EVENT_ID | CURRENT_SCHEMA | SQL_TEXT             | MYSQL_ERRNO | ROWS_EXAMINED | ROWS_SENT | elapsed_ms |
+-----------+----------+----------------+----------------------+-------------+---------------+-----------+------------+
| <ID>      | <ID>     | app            | <SQL_TEXT>           | <VALUE>     | <VALUE>       | <VALUE>   | <VALUE>    |
+-----------+----------+----------------+----------------------+-------------+---------------+-----------+------------+

这里的 THREAD_ID 是 Performance Schema 线程标识,不等同于客户端连接 ID。需要继续关联会话时,可再与 performance_schema.threads 通过 THREAD_ID 对接,取得 PROCESSLIST_ID 等信息。

避免把累计值当成瞬时值

digest 表中的计数和计时是自统计周期开始后的累计值,因此一次查询只能说明“到现在为止谁贡献最多”,不能直接证明“最近五分钟谁突然变慢”。要看趋势,应由监控系统周期采样后计算差值,或在受控实验中明确记录清零前后的时间窗口。

TRUNCATE TABLE performance_schema.events_statements_summary_by_digest 可以重置 digest 汇总,但会影响实例上的其他观察者,不应把它当作日常查询步骤。生产分析更稳妥的做法是保留累计表,用外部采样形成区间增量,再对进入候选集的 digest 结合 EXPLAIN ANALYZE、索引信息和锁等待继续定位。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

用 Performance Schema 观察语句

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通