1. MySQL慢查询日志排查实战指南
作为数据库管理员，最头疼的就是遇到系统性能下降却找不到原因。上周我们的订单系统突然响应变慢，页面加载时间从200ms飙升到3秒以上。经过层层排查，最终发现是几个未被优化的慢查询拖累了整个数据库。今天我就来分享如何通过MySQL慢查询日志快速定位性能瓶颈，这套方法在电商、金融等高频查询场景中特别实用。
慢查询日志是MySQL内置的性能诊断工具，它会记录所有执行时间超过指定阈值的SQL语句。与EXPLAIN分析单条SQL不同，慢日志能帮我们发现生产环境中实际存在的性能问题。接下来我会从配置、分析到优化，带你走完整个排查流程。无论你是刚接触MySQL的新手，还是遇到过"凌晨三点数据库报警"的老DBA，这些实战经验都能直接套用。
2. 慢查询日志核心配置详解
2.1 基础参数设置
在my.cnf配置文件中，这几个参数控制着慢查询日志的行为：
[mysqld] slow_query_log = 1 # 启用慢查询日志 slow_query_log_file = /var/log/mysql/mysql-slow.log # 日志文件路径 long_query_time = 1 # 阈值设为1秒 log_queries_not_using_indexes = 1 # 记录未使用索引的查询 min_examined_row_limit = 100 # 仅记录检查行数超过100的查询重要提示：生产环境建议将long_query_time设为0.5-1秒，开发环境可以设为0.1秒。过低的阈值会导致日志量暴增，反而影响性能。
我习惯把日志文件放在/var/log/mysql/目录下，记得给mysql用户写入权限：
mkdir -p /var/log/mysql chown mysql:mysql /var/log/mysql2.2 动态调整参数
不想重启MySQL服务？可以用SET命令实时调整：
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.5; SET GLOBAL log_output = 'FILE,TABLE'; # 同时输出到文件和mysql.slow_log表这种设置方式会立即生效，但重启后会丢失。建议先在动态调整测试效果，确认无误后再写入my.cnf。
2.3 日志格式解析
一条典型的慢查询日志记录如下：
# Time: 2023-08-20T14:23:45.123456Z # User@Host: user1[user1] @ [192.168.1.100] Id: 12345 # Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 1 Rows_examined: 500000 SET timestamp=1692548625; SELECT * FROM orders WHERE user_id=123 AND status='pending' ORDER BY create_time DESC;关键字段说明：
- Query_time：查询执行总时间
- Lock_time：等待锁的时间
- Rows_sent：返回给客户端的行数
- Rows_examined：扫描的行数
- 最后一行是完整的SQL语句
3. 高效分析慢查询日志
3.1 使用mysqldumpslow工具
MySQL自带的mysqldumpslow可以统计慢查询：
# 按查询时间排序 mysqldumpslow -s t /var/log/mysql/mysql-slow.log # 显示前10条最频繁的慢查询 mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log # 解析特定类型的查询 mysqldumpslow -g "ORDER BY" /var/log/mysql/mysql-slow.log输出示例：
Count: 25 Time=1.23s (30s) Lock=0.00s (0s) Rows=10.0 (250), user1[user1]@[192.168.1.100] SELECT * FROM products WHERE category='electronics' ORDER BY price DESC3.2 使用pt-query-digest进阶分析
Percona Toolkit中的pt-query-digest更强大：
pt-query-digest /var/log/mysql/mysql-slow.log它会生成一份详细报告，包含：
- 查询响应时间分布
- 执行频率统计
- 表扫描情况
- 索引使用建议
我曾用这个工具发现一个每月执行百万次的全表扫描查询，优化后系统负载直接下降40%。
3.3 直接查询slow_log表
如果配置了log_output='TABLE'，可以直接SQL分析：
-- 查询最耗时的10个慢查询 SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10; -- 统计不同用户的慢查询数量 SELECT user_host, COUNT(*) as query_count FROM mysql.slow_log GROUP BY user_host ORDER BY query_count DESC;4. 常见慢查询场景与优化方案
4.1 全表扫描问题
症状：
- Rows_examined远大于Rows_sent
- 日志中出现"Using filesort"或"Using temporary"
优化方案：
-- 原查询（无索引） SELECT * FROM users WHERE phone='13800138000'; -- 优化后 ALTER TABLE users ADD INDEX idx_phone (phone); SELECT * FROM users WHERE phone='13800138000';4.2 复杂排序导致性能下降
症状：
- 含有ORDER BY多字段
- 大数据量分页查询
优化方案：
-- 原查询（性能差） SELECT * FROM orders WHERE user_id=123 ORDER BY create_time DESC, amount DESC LIMIT 10000, 20; -- 优化方案1：使用覆盖索引 ALTER TABLE orders ADD INDEX idx_user_create_amount (user_id, create_time, amount); -- 优化方案2：延迟关联 SELECT o.* FROM orders o JOIN ( SELECT id FROM orders WHERE user_id=123 ORDER BY create_time DESC, amount DESC LIMIT 10000, 20 ) AS tmp ON o.id=tmp.id;4.3 大表JOIN操作
症状：
- 多表JOIN且表数据量大
- 执行计划中出现"Using join buffer"
优化方案：
-- 原查询 SELECT u.name, o.order_no, p.product_name FROM users u JOIN orders o ON u.id=o.user_id JOIN products p ON o.product_id=p.id WHERE u.register_time > '2023-01-01'; -- 优化方案1：添加适当索引 ALTER TABLE orders ADD INDEX idx_user_product (user_id, product_id); -- 优化方案2：拆分成多个查询（适合OLTP场景） -- 先查用户ID列表 SELECT id FROM users WHERE register_time > '2023-01-01'; -- 再分批查询订单数据 SELECT o.order_no, p.product_name FROM orders o JOIN products p ON o.product_id=p.id WHERE o.user_id IN (123,456,789) LIMIT 1000;5. 高级排查技巧与实战案例
5.1 临时开启全量日志捕获
当遇到间歇性性能问题时，可以临时开启全量日志：
SET GLOBAL general_log = 'ON'; SET GLOBAL general_log_file = '/var/log/mysql/mysql-general.log'; -- 问题复现后立即关闭 SET GLOBAL general_log = 'OFF';然后用grep过滤出问题时间段的查询：
grep "2023-08-20T14:" /var/log/mysql/mysql-general.log > suspect-queries.log5.2 结合Performance Schema分析
MySQL 5.7+的performance_schema提供了更细粒度的监控：
-- 查看哪些SQL消耗最多CPU SELECT digest_text, sum_timer_wait/1000000000 as sec FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10; -- 查看锁等待情况 SELECT * FROM performance_schema.events_waits_current WHERE event_name LIKE 'wait/io/table/sql/%';5.3 真实案例：电商大促前的优化
去年双11前，我们的商品搜索接口突然变慢。通过慢查询日志发现：
- 一个JOIN 5张表的查询平均执行2.3秒
- 该查询每分钟执行2000+次
- 缺少(product_id, status)的联合索引
优化措施：
- 添加缺失的复合索引
- 引入查询缓存
- 对大表进行分片
优化后查询时间降至0.1秒以下，整个大促期间数据库平稳运行。
6. 慢查询日志管理策略
6.1 日志轮转配置
避免日志文件过大：
# 使用logrotate每日轮转 cat > /etc/logrotate.d/mysql-slow <<EOF /var/log/mysql/mysql-slow.log { daily rotate 30 missingok compress delaycompress notifempty create 640 mysql mysql postrotate /usr/bin/mysqladmin flush-logs endscript } EOF6.2 长期监控方案
建议将慢查询日志纳入监控系统：
- 使用Filebeat收集日志到ELK
- 配置Grafana监控慢查询趋势
- 设置阈值告警（如慢查询数量突增）
我们团队用这套方案，能在用户感知前发现性能退化问题。
6.3 避免的常见错误
- 不要在生产环境设置long_query_time=0
- 定期清理mysql.slow_log表（默认配置会占用系统表空间）
- 不要过度依赖慢查询日志，要结合其他监控工具
- 警惕log_queries_not_using_indexes带来的性能开销
7. 性能优化检查清单
每次分析慢查询日志后，按这个清单检查：
- [ ] 是否所有查询都使用了合适的索引？
- [ ] 是否有可以移除的全表扫描？
- [ ] 大表JOIN是否可以优化？
- [ ] 复杂排序是否有更好的实现方式？
- [ ] 是否可以引入查询缓存？
- [ ] 是否可以考虑分库分表？
最后分享一个心得：慢查询优化不是一次性的工作，而应该成为日常运维的一部分。我们团队每周都会review慢查询日志，这让我们在用户投诉前就能发现并解决90%的性能问题。
