如何用 performance_schema 找到当前最慢的几条 SQL？

考察要点

这道题面试官想看你是否具备在线诊断和定位 MySQL 性能问题的实战能力。它考察的不仅仅是理论，而是你是否熟练使用 MySQL 自身提供的强大工具 performance_schema。核心是检验你对 SQL 聚合统计（Digest） 概念的理解，以及能否熟练查询 events_statements_summary_by_digest 这张关键表，并能区分总耗时与平均耗时两种分析维度。

核心概念

performance_schema (P_S) 是 MySQL 5.5+ 内置的性能监控引擎，它以较低的开销实时收集服务器运行过程中的性能数据。与慢查询日志（Slow Query Log）相比，它在内存中进行，对 I/O 影响更小，并且能提供更丰富的聚合信息。

1. SQL Digest (SQL 指纹) performance_schema 会将结构相同、仅参数不同的 SQL 语句进行“范式化”，生成一个唯一的哈希值（Digest）。这使得聚合分析成为可能。

   • 原始 SQL:
     sql

     ⧉⛶

      1SELECT name, age FROM users WHERE id = 1001;
      2SELECT name, age FROM users WHERE id = 2050;

   • 范式化后的 SQL (DIGEST_TEXT):
     sql

     ⧉⛶

      1SELECT name, age FROM users WHERE id = ?;

   所有符合这个模式的查询都会被归类到同一个 Digest 下进行统计。

2. 关键表：events_statements_summary_by_digest 这张表是定位慢 SQL 的核心。它存储了按 Digest 聚合后的 SQL 执行统计信息。

   关键列名	含义
   SCHEMA_NAME	数据库名
   DIGEST_TEXT	范式化后的 SQL 语句
   COUNT_STAR	此类 SQL 的总执行次数
   SUM_TIMER_WAIT	此类 SQL 的总执行耗时（单位：皮秒）
   AVG_TIMER_WAIT	此类 SQL 的平均执行耗时（单位：皮秒）
   SUM_ROWS_SENT	总返回行数
   SUM_ROWS_EXAMINED	总扫描行数（非常关键，反映了索引效率）
   SUM_SORT_ROWS	总排序行数
   FIRST_SEEN / LAST_SEEN	首次/末次出现时间

3. 开启与配置 默认情况下，performance_schema 是开启的，但具体的事件收集器（instrument）和消费者（consumer）可能需要手动开启。

   sql

   ⧉⛶

    1-- 开启 statement 相关的事件收集
    2UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'statement/sql/%';
    3-- 开启 statement 统计信息的消费者
    4UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%statements%';

   在 my.cnf 中持久化配置是更推荐的做法。

4. P_S vs. 慢查询日志

   特性	Performance Schema	Slow Query Log
   存储	内存（表）	文件
   开销	较低（CPU/内存）	较高（磁盘 I/O）
   模式	实时、聚合统计	离线、记录单条
   优点	适合发现高频、总体影响大的 SQL	适合捕获偶发、执行时间超长的单次查询
   重置	可通过 TRUNCATE 清空统计	文件追加，需手动或轮转

多种解法

解法 1：按总耗时排序，查找系统“病灶”

这种方法用于定位那些累积消耗资源最多的 SQL。可能单次执行很快，但执行频率极高，总体上是性能瓶颈。

• 适用场景：系统整体负载高，需要找到最主要的性能消耗点。
• SQL 实现：
  sql

  ⧉⛶

   1SELECT
   2    SCHEMA_NAME,
   3    DIGEST_TEXT,
   4    COUNT_STAR AS '执行次数',
   5    -- 将皮秒转换为更易读的毫秒
   6    ROUND(SUM_TIMER_WAIT / 1000 / 1000 / 1000, 2) AS '总耗时(ms)',
   7    ROUND(AVG_TIMER_WAIT / 1000 / 1000 / 1000, 2) AS '平均耗时(ms)',
   8    SUM_ROWS_EXAMINED AS '总扫描行数',
   9    SUM_ROWS_SENT AS '总返回行数',
  10    FIRST_SEEN,
  11    LAST_SEEN
  12FROM
  13    performance_schema.events_statements_summary_by_digest
  14WHERE
  15    SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME != 'performance_schema'
  16ORDER BY
  17    SUM_TIMER_WAIT DESC
  18LIMIT 10;

• 复杂度：查询本身是对内存表的一次扫描和排序，速度很快，复杂度为 $O(N \log N)$，其中 N 是 Digest 的数量。性能开销主要在 MySQL 后台收集数据时。
• 索引依赖：无，events_statements_summary_by_digest 是内存表，不依赖磁盘索引。

解法 2：按平均耗时排序，查找单次“慢”查询

这种方法用于定位那些单次执行就很慢的 SQL，通常是索引缺失、查询计划差的典型慢查询。

• 适用场景：解决用户反馈的单次操作卡顿问题。
• SQL 实现：
  sql

  ⧉⛶

   1SELECT
   2    SCHEMA_NAME,
   3    DIGEST_TEXT,
   4    COUNT_STAR AS '执行次数',
   5    ROUND(AVG_TIMER_WAIT / 1000 / 1000 / 1000, 2) AS '平均耗时(ms)',
   6    ROUND(SUM_TIMER_WAIT / 1000 / 1000 / 1000, 2) AS '总耗时(ms)',
   7    -- 平均扫描行数 = 总扫描行数 / 执行次数
   8    ROUND(SUM_ROWS_EXAMINED / COUNT_STAR) AS '平均扫描行数',
   9    LAST_SEEN
  10FROM
  11    performance_schema.events_statements_summary_by_digest
  12WHERE
  13    SCHEMA_NAME IS NOT NULL AND SCHEMA_NAME != 'performance_schema'
  14    -- 过滤掉执行次数过少的，避免偶然抖动干扰
  15    AND COUNT_STAR > 5
  16ORDER BY
  17    AVG_TIMER_WAIT DESC
  18LIMIT 10;

• 复杂度：同解法 1。
• 索引依赖：无。

解法 3：结合 sys schema，更便捷的查询（推荐）

MySQL 5.7.7+ 引入了 sys schema，它在 performance_schema 之上提供了一系列易于理解的视图和存储过程，是生产环境中的首选。

• 适用场景：日常性能排查，语法更友好，信息更全面。
• SQL 实现 (查询总耗时最高的 SQL)：
  sql

  ⧉⛶

   1-- sys.statement_analysis 视图已经做好了单位转换和可读性格式化
   2SELECT
   3    db,
   4    query,
   5    exec_count,
   6    total_latency,
   7    avg_latency,
   8    rows_sent_avg,
   9    rows_examined_avg,
  10    first_seen,
  11    last_seen
  12FROM
  13    sys.statement_analysis
  14-- 默认按总耗时排序，可以省略 ORDER BY
  15-- ORDER BY total_latency DESC
  16LIMIT 10;

• 说明：sys.statement_analysis 本质上就是封装了类似解法 1 的查询，但提供了更友好的列名和格式（如 total_latency 直接是 '1.23 s' 这样的字符串）。面试中提及 sys schema 会展示你跟进社区最佳实践的积极态度。

常见低分回答

1. 只知道慢查询日志：回答“我会开启慢查询日志，然后用 mysqldumpslow 工具分析”，这表明知识体系停留在 MySQL 5.5 之前，不了解现代 MySQL 的监控能力。
2. 概念模糊，说不出关键表：回答“我会去查 performance_schema”，但当被追问查哪张表时，无法说出 events_statements_summary_by_digest，这说明只是听过该概念，没有实操经验。
3. 混淆总耗时和平均耗时：无法清晰解释为何要区分 SUM_TIMER_WAIT 和 AVG_TIMER_WAIT 进行排序，不理解这两种排序背后不同的优化目标（优化系统总资源消耗 vs. 优化单次用户体验）。
4. 忽略统计周期：performance_schema 的数据是自服务启动或上次 TRUNCATE 以来累积的。如果不提这一点，可能会基于陈旧的数据做出错误判断。高分回答会提到在分析前，可能需要执行 TRUNCATE TABLE performance_schema.events_statements_summary_by_digest; 来开启一个新的统计窗口。
5. 无法获取带参数的 SQL：只知道 DIGEST_TEXT 是范式化的 SQL，但不知道如何找到一个具体的、可执行的慢查询实例来进行 EXPLAIN。高分回答会提到可以从 events_statements_history_long 表中根据 DIGEST 值找到具体的 SQL_TEXT。

工程实践与优化

• 使用 sys schema：在 MySQL 5.7.7+ 版本，应优先使用 sys schema。它不仅简化了查询，还提供了如 sys.diagnostics 存储过程，可以一键生成详细的诊断报告。
• 关注 rows_examined：在分析结果时，AVG_ROWS_EXAMINED（平均扫描行数）和 AVG_ROWS_SENT（平均返回行数）的比值是判断索引是否高效的关键指标。如果前者远大于后者，通常意味着存在巨大的优化空间。
• 结合 EXPLAIN ANALYZE：定位到慢 SQL 后，下一步是获取一个具体查询实例（可从 events_statements_history_long 获取），并使用 EXPLAIN ANALYZE (MySQL 8.0+) 或 EXPLAIN 来分析其执行计划，找出瓶颈（如全表扫描、未使用索引、文件排序等）。
• 控制 P_S 开销：在超高并发的生产环境中，P_S 的开销也需关注。可以通过 setup_instruments 和 setup_consumers 表动态地、精细地控制监控范围，只在需要时开启重量级的监控项（如 events_statements_history_long），分析结束后及时关闭。
• 周期性重置：对于长期运行的实例，P_S 中的统计数据会一直累积。可以设置定时任务（如每天凌晨）TRUNCATE 关键的 summary 表，以便分析特定时间窗口（如过去24小时）内的性能状况。

常见追问与延伸题目

1. 追问：Performance Schema 和慢查询日志，在生产环境中你如何选择和搭配使用？

   • 回答要点 1 (日常监控)：我会默认开启 Performance Schema，并依赖 sys schema 的视图（如 statement_analysis）进行常态化、低开销的监控。这能帮我快速发现系统中消耗资源最多的“热点”SQL。
   • 回答要点 2 (专项排查)：对于需要捕获特定偶发慢查询（例如，超过5秒的查询）并记录其完整上下文（执行时间、锁等待、具体参数）的场景，我会开启慢查询日志。慢日志文件便于离线分析和历史追溯。
   • 总结：两者是互补关系。P_S 用于“宏观”和“实时”的性能剖析，发现高频、累积影响大的问题；慢查询日志用于“微观”和“事后”的精确诊断，捕获单次执行慢的“罪魁祸首”。

2. 追问：P_S 的性能开销有多大？如果发现它影响了系统性能，你会如何调整？

   • 回答要点 1 (开销来源)：P_S 的开销主要来自两方面：内存（用于存储事件的环形缓冲区）和 CPU（执行检测逻辑的额外指令）。内存开销由 performance_schema_*_size 系列参数控制；CPU 开销与开启的 instruments 数量和服务器 QPS 正相关。
   • 回答要点 2 (调整策略)：
     • 精简 instruments：通过查询 setup_instruments 表，UPDATE ... SET ENABLED = 'NO' 来禁用不必要的监控项。例如，如果我只关心 SQL 语句，可以禁用所有 wait、stage、transaction 相关的监控。
     • 精简 consumers：events_statements_history_long 记录每条 SQL，内存和 CPU 开销最大。在日常监控中可以关闭它 (UPDATE ... SET ENABLED = 'NO')，仅在需要抓取具体 SQL 样本时临时开启。summary 表的开销则小得多。
     • 调整内存：如果内存是瓶颈，可以适当调小 performance_schema_events_statements_history_size 等参数的大小，但这会让你丢失更早的历史事件。

3. 追问：你从 P_S 找到了慢 SQL 的 Digest，下一步怎么拿到一个可以立即执行并 EXPLAIN 的具体 SQL？

   • 回答要点 1 (定位 Digest)：首先，通过解法 1 或 2 的查询，从 events_statements_summary_by_digest 表中找到目标慢 SQL 的 DIGEST 值。
   • 回答要点 2 (查询历史表)：然后，利用这个 DIGEST 值去查询 events_statements_history_long 表。这张表记录了最近执行过的 SQL 原文（带参数）。
     sql

     ⧉⛶

      1-- 假设从上一步查到 digest 值为 'abc...'
      2SELECT SQL_TEXT, CURRENT_SCHEMA, TIMER_WAIT, ROWS_SENT, ROWS_EXAMINED
      3FROM performance_schema.events_statements_history_long
      4WHERE DIGEST = 'the_digest_hash_you_found'
      5ORDER BY TIMER_WAIT DESC
      6LIMIT 1;

   • 回答要点 3 (执行分析)：从 SQL_TEXT 字段取出的就是一条完整的、带真实参数的 SQL。可以直接复制出来，在前面加上 EXPLAIN ANALYZE 来分析其详细的执行计划和资源消耗，从而进行针对性优化。