Performance Schema

导学

慢查询日志告诉你"哪条 SQL 慢"，但不知道"为什么慢"——是锁等待？磁盘 I/O？还是临时表太大？Performance Schema 是 MySQL 5.5+ 内置的性能监控引擎，以极低的开销实时记录服务器内部的每一次等待、每一个阶段、每一条语句的执行细节。掌握 Performance Schema，是进阶 MySQL 性能诊断的必经之路。

定义

Performance Schema：MySQL 内置的性能监控数据库，通过内存中的性能表（非磁盘表）实时记录服务器事件（语句执行、阶段耗时、锁等待、I/O 操作等）。默认启用，开销可控，适合生产环境持续监控。

核心概念

概念说明
Consumer（消费者）数据的消费端，如 events_statements_current 表。通过 setup_consumers 配置启用。
Instrument（仪器）被监控的事件类型，如 statement/sql/select、wait/io/file/innodb/innodb_data_file。通过 setup_instruments 配置。
Actor（参与者）被监控的线程/用户，通过 setup_actors 配置。
Timer（计时器）事件耗时使用高精度计时器（默认皮秒 picosecond），通过 performance_schema 系统变量配置。

SQL 示例

场景一：查看 Performance Schema 是否启用

执行语句：

-- 查看 Performance Schema 状态
SHOW VARIABLES LIKE 'performance_schema';

-- 查看当前启用的消费者
SELECT * FROM performance_schema.setup_consumers WHERE ENABLED = 'YES';

操作后结果：

VARIABLE_NAMEVALUE
performance_schemaON

结果解读：

  • performance_schema = ON 表示已启用（MySQL 5.7 默认开启）
  • 如果为 OFF，需要在 my.cnf 中设置 performance_schema = ON 并重启
  • 消费者（consumers）决定数据记录到哪些表，默认只启用部分，可按需开启

场景二：查看最近执行的 SQL 及其耗时

执行语句：

-- 查看当前正在执行的语句
SELECT 
    THREAD_ID,
    SQL_TEXT,
    TIMER_WAIT / 1000000000000 AS wait_sec,
    LOCK_TIME / 1000000000000 AS lock_sec,
    ROWS_SENT,
    ROWS_EXAMINED
FROM performance_schema.events_statements_current
WHERE SQL_TEXT IS NOT NULL
LIMIT 5;

操作后结果（示例）：

THREAD_IDSQL_TEXTwait_seclock_secROWS_SENTROWS_EXAMINED
25SELECT * FROM employees WHERE ...0.00120.0001210000
26INSERT INTO logs ...0.00050.000200

结果解读：

  • events_statements_current 记录每个线程当前正在执行的语句
  • TIMER_WAIT 是总耗时（皮秒，除以 10^12 转为秒）
  • ROWS_SENT 是返回给客户端的行数，ROWS_EXAMINED 是服务器扫描的行数
  • 如果 ROWS_EXAMINED 远大于 ROWS_SENT，说明查询扫描了大量无用数据，需要优化索引或 SQL

场景三：查看历史执行的慢语句

执行语句：

-- 查看语句历史（每个线程最近 10 条）
SELECT 
    THREAD_ID,
    SQL_TEXT,
    TIMER_WAIT / 1000000000000 AS wait_sec,
    ROWS_SENT,
    ROWS_EXAMINED,
    CREATED_TMP_TABLES,
    CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_history
WHERE SQL_TEXT IS NOT NULL
ORDER BY TIMER_WAIT DESC
LIMIT 10;

操作后结果（示例）：

THREAD_IDSQL_TEXTwait_secROWS_SENTROWS_EXAMINEDCREATED_TMP_TABLESCREATED_TMP_DISK_TABLES
25SELECT * FROM big_table ...5.2341100500000011

结果解读：

  • events_statements_history 记录每个线程最近执行的语句（默认每个线程保留 10 条）
  • CREATED_TMP_TABLES：是否创建了内存临时表；CREATED_TMP_DISK_TABLES：是否创建了磁盘临时表
  • 磁盘临时表（CREATED_TMP_DISK_TABLES > 0）通常意味着 GROUP BY/ORDER BY 的缓冲区不够大或数据量过大，需要优化

场景四：查看表 I/O 统计（找出热点表）

执行语句：

-- 查看各表的读写次数和 I/O 耗时
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    SUM_TIMER_WAIT / 1000000000000 AS total_wait_sec
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

操作后结果（示例）：

OBJECT_SCHEMAOBJECT_NAMECOUNT_READCOUNT_WRITEtotal_wait_sec
companyemployees15000200012.5
companyorders500050008.3

结果解读：

  • table_io_waits_summary_by_table 按表汇总 I/O 等待事件
  • COUNT_READ / COUNT_WRITE：读/写操作次数
  • SUM_TIMER_WAIT：累计 I/O 等待时间，值越大说明该表越"热"或越"慢"
  • 结合 COUNT 和 WAIT 可以判断：是访问量大（正常）还是单次 I/O 慢（需要优化，如索引缺失、表太大）

场景五：查看锁等待事件（排查死锁和锁竞争）

执行语句：

-- 查看最近的锁等待事件
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    THREAD_ID,
    EVENT_NAME,
    TIMER_WAIT / 1000000000000 AS wait_sec,
    SPINS,
    OBJECT_INSTANCE_BEGIN
FROM performance_schema.events_waits_history
WHERE EVENT_NAME LIKE '%lock%'
ORDER BY TIMER_WAIT DESC
LIMIT 10;

结果解读：

  • events_waits_history 记录等待事件历史（如锁等待、文件 I/O 等待）
  • EVENT_NAME LIKE '%lock%' 过滤锁相关事件
  • SPINS：自旋锁的自旋次数，值高说明锁竞争激烈
  • 结合 THREAD_ID 和 OBJECT_NAME 可以定位哪张表、哪个线程在竞争锁

场景六：启用更多监控仪器（按需开启）

执行语句：

-- 查看当前启用的仪器
SELECT NAME, ENABLED, TIMED 
FROM performance_schema.setup_instruments 
WHERE NAME LIKE '%statement%' AND ENABLED = 'YES';

-- 启用所有语句监控（如果之前被关闭）
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

-- 启用所有等待事件监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/%';

结果解读：

  • setup_instruments 控制监控哪些类型的事件。默认只启用部分，全开会有性能开销
  • ENABLED = 'YES' 表示记录该事件，TIMED = 'YES' 表示记录耗时
  • 生产环境建议只开启需要的仪器（如 statement/%、wait/io/file/%），避免全开导致性能下降
  • 修改 setup_instruments 立即生效，无需重启

常见误区

误区正解
"Performance Schema 会拖慢数据库"默认配置开销很低（< 5%）。但如果开启所有 instruments 和 consumers，开销会显著增加。生产环境按需开启。
"Performance Schema 的数据持久化到磁盘"不是。所有数据存储在内存中，重启后清空。需要持久化历史数据应配合 events_statements_history_long 或外部监控工具。
"events_statements_current 能看到所有历史 SQL"不能。current 只看当前正在执行的；history 看每个线程最近几条；history_long 看全局最近更多条（默认可能未启用）。
"Performance Schema 可以替代慢查询日志"不能替代，是互补。慢查询日志记录超过阈值的 SQL 文本；Performance Schema 记录所有语句的详细执行指标（锁、I/O、临时表等）。
"TIMER_WAIT 的单位是秒"不是。默认是皮秒（picosecond，10^-12 秒），需要除以 10^12 转换为秒。
"Performance Schema 表可以用普通 SQL 查询"可以。Performance Schema 表就是普通表，可以用 SELECT、WHERE、ORDER BY、JOIN 等所有 SQL 语法查询。

面试考点

Q：Performance Schema 和慢查询日志的区别？

慢查询日志只记录超过 long_query_time 阈值的 SQL 文本和执行时间；Performance Schema 记录所有语句的详细执行指标（锁等待、I/O、临时表、扫描行数等），以及阶段事件、等待事件。两者互补：慢查询日志定位"哪些 SQL 慢"，Performance Schema 分析"为什么慢"。

Q：ROWS_SENT 和 ROWS_EXAMINED 的区别？

ROWS_SENT 是返回给客户端的行数；ROWS_EXAMINED 是服务器实际扫描的行数。如果 ROWS_EXAMINED 远大于 ROWS_SENT，说明查询做了大量无用扫描（如全表扫描后只返回几行），需要加索引或优化 WHERE 条件。

Q：如何找出数据库中最热的表？

查询 performance_schema.table_io_waits_summary_by_table，按 SUM_TIMER_WAIT 或 COUNT_READ + COUNT_WRITE 排序。结合 COUNT 和 WAIT 判断是访问量大（正常）还是单次 I/O 慢（需要优化）。

Q：Performance Schema 默认开启吗？如何控制开销？

MySQL 5.7 默认开启。通过 setup_instruments 控制监控哪些事件类型，通过 setup_consumers 控制数据输出到哪些表。生产环境建议只开启 statement/% 和必要的 wait/%，避免全开。

Q：磁盘临时表怎么通过 Performance Schema 发现？

查询 events_statements_history 或 events_statements_history_long，关注 CREATED_TMP_TABLES 和 CREATED_TMP_DISK_TABLES 列。如果 CREATED_TMP_DISK_TABLES > 0，说明该语句创建了磁盘临时表，通常是因为 GROUP BY/ORDER BY/DISTINCT 的数据量超过 tmp_table_size 或包含 BLOB/TEXT 列。

小结

  • Performance Schema 是 MySQL 内置的内存级性能监控引擎，默认启用，开销可控
  • events_statements_current/history 记录 SQL 执行详情（耗时、扫描行数、临时表）
  • table_io_waits_summary_by_table 按表汇总 I/O，找出热点表
  • events_waits_history 记录锁等待等事件，排查锁竞争
  • 生产环境按需开启 instruments，避免全开导致性能下降
  • 与慢查询日志互补：慢日志定位慢 SQL，Performance Schema 分析慢的原因

下一章引子：Performance Schema 的数据非常底层，需要复杂的 SQL 才能提取有用信息。sys Schema 基于 Performance Schema 构建了一系列视图和存储过程，让你用一条简单的 SQL 就能获取直观的性能诊断报告。