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 # UserHost: user1[user1] [192.168.1.100] Id: 12345 # Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 1 Rows_examined: 500000 SET timestamp1692548625; SELECT * FROM orders WHERE user_id123 AND statuspending 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 Time1.23s (30s) Lock0.00s (0s) Rows10.0 (250), user1[user1][192.168.1.100] SELECT * FROM products WHERE categoryelectronics 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_outputTABLE可以直接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 phone13800138000; -- 优化后 ALTER TABLE users ADD INDEX idx_phone (phone); SELECT * FROM users WHERE phone13800138000;4.2 复杂排序导致性能下降症状含有ORDER BY多字段大数据量分页查询优化方案-- 原查询性能差 SELECT * FROM orders WHERE user_id123 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_id123 ORDER BY create_time DESC, amount DESC LIMIT 10000, 20 ) AS tmp ON o.idtmp.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.ido.user_id JOIN products p ON o.product_idp.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_idp.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_time0定期清理mysql.slow_log表默认配置会占用系统表空间不要过度依赖慢查询日志要结合其他监控工具警惕log_queries_not_using_indexes带来的性能开销7. 性能优化检查清单每次分析慢查询日志后按这个清单检查[ ] 是否所有查询都使用了合适的索引[ ] 是否有可以移除的全表扫描[ ] 大表JOIN是否可以优化[ ] 复杂排序是否有更好的实现方式[ ] 是否可以引入查询缓存[ ] 是否可以考虑分库分表最后分享一个心得慢查询优化不是一次性的工作而应该成为日常运维的一部分。我们团队每周都会review慢查询日志这让我们在用户投诉前就能发现并解决90%的性能问题。 
长河君 · 2026/11/3 6:08:24 · 阅读 1.2k MySQL慢查询日志配置与优化实战
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 # UserHost: user1[user1] [192.168.1.100] Id: 12345 # Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 1 Rows_examined: 500000 SET timestamp1692548625; SELECT * FROM orders WHERE user_id123 AND statuspending 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 Time1.23s (30s) Lock0.00s (0s) Rows10.0 (250), user1[user1][192.168.1.100] SELECT * FROM products WHERE categoryelectronics 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_outputTABLE可以直接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 phone13800138000; -- 优化后 ALTER TABLE users ADD INDEX idx_phone (phone); SELECT * FROM users WHERE phone13800138000;4.2 复杂排序导致性能下降症状含有ORDER BY多字段大数据量分页查询优化方案-- 原查询性能差 SELECT * FROM orders WHERE user_id123 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_id123 ORDER BY create_time DESC, amount DESC LIMIT 10000, 20 ) AS tmp ON o.idtmp.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.ido.user_id JOIN products p ON o.product_idp.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_idp.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_time0定期清理mysql.slow_log表默认配置会占用系统表空间不要过度依赖慢查询日志要结合其他监控工具警惕log_queries_not_using_indexes带来的性能开销7. 性能优化检查清单每次分析慢查询日志后按这个清单检查[ ] 是否所有查询都使用了合适的索引[ ] 是否有可以移除的全表扫描[ ] 大表JOIN是否可以优化[ ] 复杂排序是否有更好的实现方式[ ] 是否可以引入查询缓存[ ] 是否可以考虑分库分表最后分享一个心得慢查询优化不是一次性的工作而应该成为日常运维的一部分。我们团队每周都会review慢查询日志这让我们在用户投诉前就能发现并解决90%的性能问题。