MySQL慢查询排查的详细实现与优化方法

总结来说，MySQL慢查询的排查与优化并不复杂。遵循系统化的步骤：从发现慢查询现象，到定位根本原因，再到实施优化方案，通常都能有效解决。本文提供一套完整可操作的排查方案，覆盖全链路环节。

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

1.1 启用慢查询日志

首先检查当前数据库配置，确认慢查询日志是否已启用，以及阈值设置情况。

-- 查看当前状态
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 查看慢查询数量与具体内容

启用慢查询日志后，需要统计慢查询的总数，并查看具体是哪些SQL语句导致了性能问题。

# 统计慢查询次数
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;

结果中的以下几项是需要重点关注的指标——它们往往是慢查询的“红灯信号”：

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 锁等待 → 排查锁冲突

锁等待导致的慢查询排查难度较大，但仍可通过以下方法定位。

-- 查看当前正在等待锁的事务
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 监控告警

最后，构建完善的监控告警体系是保障数据库稳定运行的关键。

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

总结：MySQL慢查询排查核心思路

先定位慢查询（日志+Profile），再分析原因（EXPLAIN+索引），最后针对优化（加索引/改SQL/扩资源）。