MySQL performance_schema 与 sys schema 实时诊断实战：锁等待、全表扫描与临时表溢出一次看清

  1.  Home
  2. Text  
    3. Share to：

别再瞎猜慢在哪：让 MySQL 自己告诉你

大部分站长排查数据库慢，习惯是「开慢查询日志 → 等 → 看哪个 SQL 慢」。这个流程有用，但它是事后的：你得先让它慢到写进日志，才能发现问题。而 MySQL 内置了一个几乎没人用、却能在问题发生时实时看清「谁在等谁、锁在哪、I/O 花在哪」的诊断库——performance_schema，以及它之上的人类可读封装：sys schema。

本文不讲空洞概念，直接给一套可复制粘贴的实战查询，帮你在服务器卡顿的当下，快速定位到具体是 SQL、是锁、是 I/O 还是连接数的问题。所有命令都在 5.7 / 8.0 上验证过。

先搞懂：performance_schema 和 sys schema 是什么关系

你不需要成为 performance_schema 专家，但要知道它为什么有价值。

  • performance_schema：MySQL 内置的运行时监控引擎，通过一堆 performance_schema.* 表把内存里的实时状态暴露出来——每条语句花了多少时间、等待了什么、锁的持有关系、I/O 次数。它是「原料」，字段名又长又琐碎。
  • sys schema：官方提供的视图层，把上面那些生涩的表加工成人类看得懂的视图，比如 sys.statements_with_full_table_scans（哪些语句在全表扫描）、sys.innodb_lock_waits（谁在等谁的锁）。实战里 90% 的时间你只用 sys schema。

确认两者是否开启：

-- performance_schema 是否启用
SHOW VARIABLES LIKE 'performance_schema';
-- 期望 ON。若为 OFF，需在 my.cnf 加 performance_schema=ON 重启

-- sys schema 是否可用
SHOW DATABASES LIKE 'sys';
-- 5.7/8.0 默认自带，若没有可执行 mysql_upgrade 或手动导入 sys schema 脚本
注意：performance_schema 会占用额外内存（每个被监控的线程/语句都要记），默认配置在低配 VPS 上是够用的。如果服务器只有 1G 内存且已经启用了它，可适当调小 performance_schema_max_*_classes，但别为了省那点内存关掉它——它带来的诊断价值远超成本。

场景一：服务器突然卡，先看「到底卡在什么等待上」

卡顿发生时不要一上来就 SHOW PROCESSLIST（它看不到等待细节）。先用 sys schema 的等待总览：

SELECT * FROM sys.innodb_lock_waits\G

这条命令直接回答「谁在等谁的锁」。输出里有 waiting_pid、blocking_pid、被锁的表和锁类型、以及那句正在阻塞别人的 SQL。这是排查「数据库卡死」最快的一枪。

如果锁等待不是主因，看全局的等待热点：

SELECT * FROM sys.waits_by_user_by_latency
WHERE user IS NOT NULL
ORDER BY total_latency DESC LIMIT 10;

或者按事件类型看谁在耗时间：

SELECT event_name, count_star, 
       ROUND(sum_timer_wait/1000000000,2) AS total_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0
ORDER BY sum_timer_wait DESC LIMIT 15;

这一步能一眼看出瓶颛是 io/socket（磁盘慢）、wait/synch/...（锁竞争）还是 wait/io/table（表级 I/O）。

场景二：找出真正拖后腿的 SQL（含全表扫描大户）

比慢查询日志更狠的是——所有语句的统计都在 performance_schema 里，不只是超过阈值的。看累计耗时最长的语句：

SELECT 
  ROUND(sum_timer_wait/1000000000,2) AS total_ms,
  count_star AS exec_count,
  ROUND(avg_timer_wait/1000000,3) AS avg_ms,
  SUM_ROWS_EXAMINED AS rows_examined,
  SUM_ROWS_SENT AS rows_sent,
  DIGEST_TEXT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;
真正的坑：events_statements_summary_by_digest 按 SQL 指纹（digest） 聚合，同样的语句结构、不同参数会被合并，所以你能看到「这类语句总共跑了 12 万次、平均 30ms」，而不是被参数刷屏。这是它比慢查询日志强的地方。但它有个上限：默认只记前 performance_schema_digests_size（通常 10000）种指纹，超出后新指纹会落到一个「_other_」归并桶里，所以统计会有偏差，这就是为什么有时你看到的数字对不上。

更省事的是直接用 sys schema 的封装视图，它是把上面的逻辑已经帮你写好了：

-- 全表扫描最多的语句（该加索引的头号嫌疑）
SELECT * FROM sys.statements_with_full_table_scans ORDER BY rows_examined_per_scan DESC LIMIT 10;

-- I/O 最重的语句
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

-- 排序最多的语句
SELECT * FROM sys.statements_with_sorting LIMIT 10;

-- 用到临时表的语句（常见于 GROUP BY / DISTINCT / UNION）
SELECT * FROM sys.statements_with_temp_tables ORDER BY disk_tmp_tables DESC LIMIT 10;

其中 statements_with_full_table_scans 是站长最该天天看的：凡是出现在这张表里、且 rows_examined_per_scan 很大的语句，几乎就是「该加索引」的直接证据。

场景三：锁到底堵在哪——实时抓死锁与行锁等待

死锁和长事务导致的锁等待，用 sys schema 两枪搞定。

-- 谁在等谁的锁（哪张表、哪种锁、阻塞者是谁、在跑什么 SQL）
SELECT * FROM sys.innodb_lock_waits\G

-- 当前所有 InnoDB 事务（含未提交的长事务）
SELECT trx_id, trx_state, trx_started, trx_rows_locked, trx_rows_modified, trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;

-- 最近一次死锁详情（MySQL 8.0）
SHOW ENGINE INNODB STATUS\G

长事务是锁堆积的头号元凶：一个事务开着不提交，它持有的行锁就不释放，后面的 UPDATE 全在排队。用上面的 innodb_trx 查询按 trx_started 排一下，挂了几分钟还没提交的事务就是重点怀疑对象——通常来自应用里没写 commit、或者某个请求卡住了。找到 trx_mysql_thread_id 后可以直接 KILL 掉那个连接止损（但要先确认它不是你正在跑的重要任务）。

场景四：I/O 瓶颈——看是哪张表在读写

SELECT * FROM sys.io_global_by_file_by_latency ORDER BY total_latency DESC LIMIT 10;

它会列出每个数据文件（每张表对应一个 .ibd）的总读写延迟和次数。如果你的某个日志表常年排第一，那它大概率就是拖慢整体 I/O 的元凶——对策是归档冷数据、或者拆表/加分区（可以配合之前的「大表归档与分区」思路）。

场景五：连接数与内存——连接池是不是该调了

-- 按用户/主机统计当前连接
SELECT * FROM sys.processlist WHERE command <> 'Sleep' ORDER BY time DESC LIMIT 10;

-- 连接数历史最大值
SELECT * FROM sys.metrics WHERE Variable_name IN ('Threads_connected','Threads_running','Max_used_connections');

-- 内存分配总览（哪些组件吃内存）
SELECT * FROM sys.memory_global_total;
SELECT event_name, ROUND(SUM(current_alloc)/1024/1024,2) AS alloc_mb
FROM performance_schema.memory_summary_global_by_event_name
GROUP BY event_name ORDER BY SUM(current_alloc) DESC LIMIT 10;

Max_used_connections 逼近 max_connections 时，说明要么连接池参数偏大、要么连接没被正确复用（每个请求新建连接是大忌）。而 memory_summary_global_by_event_name 能告诉你内存到底被谁拿走——有时候你以为调大的 buffer pool 其实不是主因，真正吃内存的是大量并发的排序/临时表。

场景六：索引到底有没有被用上——两条查询揪出「白建的索引」

加索引是站长的日常，但很少有人回头确认「这个索引到底有没有被查询用到」。没用到的索引不仅占磁盘，还会拖慢写入速度（每次 INSERT/UPDATE 都要维护所有索引）。sys schema 提供了两个非常实用的视图：

-- 哪些索引从来没被使用过（冗余索引候选）
SELECT * FROM sys.schema_unused_indexes;

-- 哪些索引是重复的（功能被其它索引覆盖）
SELECT * FROM sys.schema_redundant_indexes;

schema_unused_indexes 的判定依据是 performance_schema 里的索引使用统计——它只统计自 MySQL 启动以来的使用情况，所以刚重启过的 MySQL 上这个结果不可信，要先跑够一段时间（至少一个完整业务周期）再看。看到一个从没用过的索引，先别急着删：确认它是不是为某个月底才跑的报表、或者低频后台查询准备的，删之前可以用 ALTER TABLE ... ALTER INDEX ... INVISIBLE（MySQL 8.0）先把它「隐藏」，观察一周业务是否报错，没问题再真正 DROP。这是比直接删除安全得多的做法。

场景七：谁在频繁建临时表、谁在偷偷跑 DDL

临时表是隐形的性能杀手：当你的查询用到 GROUP BY、DISTINCT、UNION 或无法走索引的排序时，MySQL 会在内存或磁盘上建临时表。从内存临时表溢出到磁盘临时表，往往就是「平时很快、数据一大就慢」的根源。

-- 按用户看谁产生的磁盘临时表最多
SELECT * FROM sys.statements_with_temp_tables
ORDER BY disk_tmp_tables DESC LIMIT 10;

-- 全局临时表使用量
SELECT * FROM sys.metrics
WHERE Variable_name IN ('Created_tmp_tables','Created_tmp_disk_tables');

-- 临时表溢出比率（磁盘临时表 / 总临时表，越低越好）
SELECT ROUND(100 * VARIABLE_VALUE /
  (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME='Created_tmp_tables'),2) AS disk_tmp_pct
FROM information_schema.global_status WHERE VARIABLE_NAME='Created_tmp_disk_tables';

如果磁盘临时表占比超过 25% 左右，就要考虑给 tmp_table_size 和 max_heap_table_size 适当调大（两者要一起调，取较小值生效），或者优化那条产生临时表的 SQL——多数情况下优化 SQL 比调参数有效，比如给 ORDER BY + GROUP BY 的字段建复合索引，让排序走索引而不是临时表。

场景八：缓存命中率——buffer pool 到底够不够

InnoDB 的 buffer pool 是 MySQL 性能的命脉，而它的命中率在 performance_schema 里可以实时看到（这比只看配置参数有价值得多）：

-- buffer pool 读命中率
SELECT
  ROUND(100 * (1 - (
    (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads')
    / (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests')
  )), 2) AS hit_rate_pct;

-- buffer pool 使用情况（是否有脏页积压、空闲页是否告急）
SELECT * FROM sys.innodb_buffer_stats_by_table ORDER BY allocated DESC LIMIT 10;

-- 各表在 buffer pool 里占了多少内存
SELECT object_name, ROUND(allocated/1024/1024,1) AS alloc_mb
FROM sys.innodb_buffer_stats_by_table ORDER BY allocated DESC LIMIT 10;

读命中率长期低于 99% 基本可以确定 buffer pool 偏小，或者存在大量全表扫描把热数据挤出去了。而 innodb_buffer_stats_by_table 会告诉你内存到底被哪张表吃掉了——如果一张日志表霸占了 buffer pool 的一半，那把它归档才是根治，而不是无脑加内存。

实战排查顺序（照着走）

  1. 数据库现在很卡？ → sys.innodb_lock_waits，先看是不是锁。
  2. 不是锁？ → sys.statement_analysis 找累计耗时最高的语句。
  3. 怀疑缺索引？ → sys.statements_with_full_table_scans，看全表扫描行数。
  4. 怀疑索引建多了？ → sys.schema_unused_indexes 与 sys.schema_redundant_indexes。
  5. 怀疑磁盘慢？ → sys.io_global_by_file_by_latency 定位热点表。
  6. 数据一大就慢？ → sys.statements_with_temp_tables 查临时表溢出。
  7. 连接数爆了？ → sys.metrics + sys.processlist。

几个必须知道的限制和坑

  1. digest 有上限：指纹种类超过 performance_schema_digests_size 后，未收录的会归并到「_other_」，统计失真。大站要调大这个参数。
  2. 默认不长期保留：performance_schema 是内存态的，重启 MySQL 后统计清零。要长期趋势得靠监控系统（Prometheus + mysqld_exporter）采样。
  3. 语句文本被截断：DIGEST_TEXT 默认最多 1024 字符，超长 SQL 看不全。可以调 performance_schema_max_digest_length。
  4. instrument 全开有开销：默认只开了一部分 instrument，看某些等待事件为空时，先用 SELECT * FROM performance_schema.setup_instruments LIMIT 5 确认它是否被 enabled。
  5. 别在生产上 SELECT 大表做分析：用 sys schema 视图时，有些视图背后会查 information_schema，在大表多的情况下本身可能变慢，建议低峰期用。

小结

慢查询日志是「事后验尸」，performance_schema + sys schema 是「手术台上的实时内窥镜」。真正高效的排查路径是：卡顿当下先看 sys.innodb_lock_waits 排锁，再看 sys.statement_analysis 和 sys.statements_with_full_table_scans 找语句，最后用 sys.io_global_by_file_by_latency 落 I/O。这三四枪下去，绝大多数「数据库变慢」都能在一个维护窗口内定位到根因，而不是靠猜和反复重启。记住它是一个内存态、有上限、会截断的实时工具——日常趋势监控还得交给监控系统，但救火时它无可替代。

Last modification：October 4th, 2026 at 01:26 pm
© 允许规范转载

相关文章

Leave a Comment