MySQL慢查询排查实现方法

先说结论：MySQL慢查询的排查与优化并不复杂，按照系统化流程操作，从问题发现到根因定位，再到优化落地，均可有效解决。以下是一套可直接上手的全链路排查方案，覆盖慢查询识别的全过程。

第一步：确认慢查询是否存在

1.1 开启慢查询日志

先检查当前MySQL实例的慢查询日志状态以及阈值设置。

-- 查看当前状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 临时开启（重启后失效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒的记录
SET GLOBAL log_queries_not_using_indexes = ON;

1.2 查看慢查询数量与内容

开启慢查询日志后，需要统计慢查询次数，并获取具体的慢查询语句。

# 统计慢查询次数
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

# 查看最近慢查询日志文件路径
SHOW VARIABLES LIKE 'slow_query_log_file';

第二步：分析慢查询语句

2.1 使用 EXPLAIN 分析执行计划

获取慢查询语句后，先使用 EXPLAIN 查看其执行计划，判断是否使用了索引、扫描行数等关键信息。

EXPLAIN SELECT * FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 100;

执行计划中的以下字段是慢查询的常见“红灯信号”，需要重点关注：

字段	危险信号
type	ALL（全表扫描）、index（索引全扫）
rows	远大于预期返回行数
Extra	Using filesort（文件排序）、Using temporary（临时表）

2.2 使用 SHOW PROFILE 查看耗时分布

执行计划无法反映具体时间消耗时，SHOW PROFILE 可以精确显示每个步骤的耗时。

-- 开启 profiling
SET profiling = 1;

-- 执行你的慢查询
SELECT * FROM orders WHERE ...;

-- 查看所有查询的耗时
SHOW PROFILES;

-- 查看具体某个 Query_ID 的详细耗时
SHOW PROFILE FOR QUERY 1;

结果中 Sending data、Sorting result、Creating tmp table 等步骤的耗时占比是关键的观察点。

第三步：常见原因与对应解决方案

3.1 没走索引 → 加索引

这是最常见的原因之一。先检查表上的现有索引，再根据查询条件添加合适的索引。

-- 检查是否有可用索引
SHOW INDEX FROM orders;

-- 添加复合索引（注意字段顺序：等值条件在前，范围条件在后）
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

3.2 索引失效 → 改写 SQL

有时索引存在但无法使用，通常是因为SQL写法导致索引失效。常见情况包括：

• 对索引列使用函数：WHERE DATE(created_at) = '2024-01-01'
• 隐式类型转换：WHERE user_id = '123'（user_id 是 int 类型）
• 前导模糊匹配：WHERE name LIKE '%张三'

3.3 数据量过大 → 分页优化 / 归档

数据量增长后，深分页成为性能瓶颈。优化思路是使用子查询代替直接 LIMIT 偏移。

-- 原始写法（越往后越慢）
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 优化写法（子查询用覆盖索引）
SELECT * FROM orders WHERE id > (SELECT id FROM orders ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 20;

若数据量极大，可考虑将历史数据迁移至归档表或分区表。

3.4 锁等待 → 排查锁冲突

锁等待引发的慢查询排查难度较高，但可通过以下SQL查找锁冲突。

-- 查看当前正在等待锁的事务
SELECT * FROM information_schema.INNODB_TRXG

-- 查看锁等待关系
SELECT * FROM sys.schema_table_lock_waits;

-- 强制结束阻塞事务（谨慎使用）
KILL [trx_mysql_thread_id];

3.5 SQL 写得烂 → 重写

许多慢查询本质上源于SQL语句不够高效。常见优化点：

• SELECT * → 只取需要的列
• 子查询嵌套过深 → 改用 JOIN 或临时表
• OR 条件 → 拆成 UNION ALL
• 循环查询 → 批量查询 + IN

第四步：系统层面排查

4.1 查看数据库配置是否合理

有时问题不在SQL本身，而是数据库参数配置不当。

-- 关键参数检查
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';    -- 建议设为内存的60%-70%
SHOW VARIABLES LIKE 'tmp_table_size';              -- 临时表大小限制
SHOW VARIABLES LIKE 'max_connections';             -- 连接数是否过高

4.2 查看服务器资源

服务器硬件资源（CPU、内存、磁盘IO）是性能的基础。

# CPU、内存、IO 情况
top
iostat -x 1
free -h

若CPU高但IO低，说明SQL计算量大或索引不合理；若IO高但CPU低，说明磁盘是瓶颈，可考虑升级SSD或增大buffer pool。

第五步：建立长效机制

5.1 定期巡检脚本

完成一次排查优化后，应建立定期巡检机制，避免问题复发。

-- 查询当前运行时间最长的SQL
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC LIMIT 10;

-- 查询全表扫描次数最多的表
SELECT * FROM sys.schema_unused_indexes;

5.2 监控告警

完善的监控告警体系是保障MySQL稳定运行的最后一道防线。

• 设置 long_query_time = 1，持续采集慢查询日志
• 使用 Percona Toolkit 的 pt-query-digest 分析日志规律
• 接入 Prometheus + Grafana 监控 QPS、慢查询数量趋势

一句话总结排查思路

先确认慢在哪（日志+profile），再看为什么慢（explain+索引），最后对症下药（加索引/改SQL/扩资源）。