MySQL慢查询分析排障

1. 找出最耗时的 TOP 10 查询

　　从 performance_schema 取历史累计最慢的 10 条 SQL，观察 total_sec 字段，可得知这条 SQL 累计消耗了多少秒 CPU，累计时间高的比单次慢的更要命，因为前者可能是执行了一百万次的 10ms，后者可能只是偶尔一次的 5s

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
    ROUND(MAX_TIMER_WAIT / 1000000000, 2) AS max_ms,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

注意：DIGEST_TEXT 是参数归一化后的模板，比如 WHERE id = ?，不会把每个具体参数单独列一行。所以你看到的数量会比原始日志少得多——这是 feature 不是 bug。

 

2.找出没走索引的 SQL

SET GLOBAL log_queries_not_using_indexes = ON;

 

　　然后从 slow_log（或者 events_statements_summary_by_digest）里看 NO_INDEX_USED_COUNT，no_index_count 只要 > 0 就值得看一眼。不是说没走索引就一定有问题（小表全扫可能比走索引还快），但这是个强信号。

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_NO_INDEX_USED AS no_index_count,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC
LIMIT 20;

 

3.找出锁等待的 SQL

-- MySQL 8.0版本
SELECT * FROM sys.innodb_lock_waits\G


--  MySQL 5.7版本
SELECT
    r.trx_id AS waiting_trx,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

 

4. 找出创建磁盘临时表最多的

　　临时表本身不可怕，磁盘临时表才是性能杀手。内存装不下就会落盘，一落盘 IO 就爆。

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_CREATED_TMP_DISK_TABLES AS disk_tmp,
    SUM_CREATED_TMP_TABLES AS mem_tmp
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_DISK_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC
LIMIT 10;

阈值：disk_tmp 只要大于 0 就应该看。对应的常见病因是：GROUP BY / ORDER BY 没走索引、UNION 去重、长字段被塞进了中间结果。

修复方向： 优先给 GROUP BY / ORDER BY 的列加复合索引；改不动 SQL 的话就调大 tmp_table_size 和 max_heap_table_size，让它尽量留在内存里。

 

5. 找出没人用的冗余索引

　　索引不是越多越好。每个索引都会拖慢写入，还占磁盘和内存。线上跑了几年的库往往积累一堆"当年某个同事加上去再也没人看过"的索引。

-- 没人用的
SELECT * FROM sys.schema_unused_indexes;

-- 重复的索引
SELECT * FROM sys.schema_redundant_indexes;

注意：schema_unused_indexes 的"没用过"是自上次 MySQL 启动以来的统计。如果刚重启过，什么都没跑，它会把所有索引都列出来。至少让库稳定跑一周再看这张表，否则会误删。

 

6. 表碎片率——该不该 OPTIMIZE TABLE

　　InnoDB 的表用久了会有空洞（删除/更新留下的），占空间还拖慢扫描。看碎片率：

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb,
    ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS frag_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema')
    AND DATA_LENGTH > 0
ORDER BY frag_pct DESC
LIMIT 20;

阈值：frag_pct 超过 20% 可以考虑 OPTIMIZE TABLE。

注意：OPTIMIZE TABLE 在 InnoDB 下会重建整张表，期间锁表（虽然 5.6+ 号称支持 online DDL，但实际场景下还是会短暂阻塞）。大表千万别白天直接跑，用 pt-online-schema-change 或业务低峰期再操作。

 

7. 哪些查询在疯狂 filesortBuffer Pool 命中率够不够

　　InnoDB 所有读写先进 Buffer Pool，命中率低说明内存不够，数据库在疯狂刷盘：

SELECT
    ROUND(
        (1 - (
            (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
            /
            (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
        )) * 100, 2
    ) AS hit_rate_pct;

阈值： 健康值 ≥ 99%。低于 95% 说明 innodb_buffer_pool_size 配得太小了，考虑加内存或者上调这个参数（通常是物理内存的 50%–70%）。

注意： 这个计算是从启动到现在的全局累计值，刚启动不久的库命中率本来就低，看趋势比看单次快照更有意义。

 

8. 哪些查询在疯狂 filesort

　　排序这个操作在内存里做一般没事，一旦走磁盘 filesort 性能断崖式下跌：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_SORT_ROWS AS total_sorted,
    SUM_SORT_MERGE_PASSES AS merge_passes
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_SORT_ROWS > 0
ORDER BY SUM_SORT_ROWS DESC
LIMIT 10;

阈值： 单条 SQL 的 total_sorted 超过 10 万就该警惕，超过 100 万基本就是灾难。merge_passes 大于 0 意味着真的用了磁盘归并排序。

修复方向：ORDER BY 的列加索引，或者把排序改成扫索引（ORDER BY 的列和索引顺序一致时 MySQL 就不需要 filesort 了）。

 

9. 排查连接池情况

　　线上事故里有一类特别招人恨：应用连接池没回收干净，MySQL 连接数被打满，新请求全部报错。

SHOW STATUS LIKE 'Threads_%';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

• Threads_connected：当前连接数
• Max_used_connections：历史最高水位
• max_connections：上限

阈值：Max_used_connections / max_connections 超过 80% 就要警觉。两种原因：要么是应用突发流量要扩容，要么是连接泄漏——后者是 bug，扩容治标不治本。

 

10. 判断 JOIN 的效率

　　最后这条很多人不知道——performance_schema 里有个"每条 SQL 平均扫描了多少行、返回了多少行"的统计：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_ROWS_EXAMINED AS examined,
    SUM_ROWS_SENT AS sent,
    ROUND(SUM_ROWS_EXAMINED / GREATEST(SUM_ROWS_SENT, 1), 2) AS scan_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_ROWS_EXAMINED > 10000
ORDER BY scan_ratio DESC
LIMIT 10;

scan_ratio 就是扫了多少行才筛出一行结果。

阈值：

• < 10：健康
• 10–100：可优化
• > 100：索引有问题
• > 1000：赶紧改，要么驱动表错了，要么关联字段没索引

 

题外话

养成良好的习惯必不可少：

1. 写个 shell 脚本把这 10 条 SQL 串起来，每天凌晨跑一遍，结果发到告警群
2. 线上出性能问题时，先跑一遍这个脚本，用输出数据倒推是哪个环节出了问题
3. performance_schema 的统计是累计值，排查之前先 TRUNCATE TABLE performance_schema.events_statements_summary_by_digest，这样就能只看最近这段时间的数据，不被历史污染

另外提醒一下：performance_schema 本身会占 约 1% 的 CPU 和几十到几百 MB 内存。资源紧张的从库可以考虑按需开关，但生产主库强烈建议一直开着——它是你半夜接到电话时唯一能依赖的东西。