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 查看慢查询数量与内容# 统计慢查询次数 SHOW GLOBAL STATUS LIKE %Slow_queries%; # 查看最近慢查询日志文件路径 SHOW VARIABLES LIKE slow_query_log_file;第二步分析慢查询语句2.1 使用 EXPLAIN 分析执行计划EXPLAIN SELECT * FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 100;重点关注字段字段危险信号typeALL全表扫描、index索引全扫rows远大于预期返回行数ExtraUsing filesort文件排序、Using temporary临时表2.2 使用 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常见导致索引失效的操作对索引列使用函数WHERE DATE(created_at) 2024-01-01隐式类型转换WHERE user_id 123user_id 是 int前导模糊匹配WHERE name LIKE %张三3.3 数据量过大 → 分页优化 / 归档深分页优化示例-- 原始写法越往后越慢 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_TRX\G -- 查看锁等待关系 SELECT * FROM sys.schema_table_lock_waits; -- 强制结束阻塞事务慎用 KILL [trx_mysql_thread_id];3.5 SQL 写得烂 → 重写典型坏写法SELECT *→ 只取需要的列子查询嵌套过深 → 改用 JOIN 或临时表OR条件 → 拆成 UNION ALL循环查询 → 批量查询 IN第四步系统层面排查4.1 查看数据库配置是否合理-- 关键参数检查 SHOW VARIABLES LIKE innodb_buffer_pool_size; -- 建议设为内存的60%-70% SHOW VARIABLES LIKE tmp_table_size; -- 临时表大小限制 SHOW VARIABLES LIKE max_connections; -- 连接数是否过高4.2 查看服务器资源# 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、慢查询数量趋势一句话总结排查思路先确认慢在哪日志profile再看为什么慢explain索引最后对症下药加索引/改SQL/扩资源。