1. 从一次真实的线上事故说起那天晚上十一点我正打算关电脑突然收到监控告警核心业务接口的响应时间从平时的50ms飙到了5秒以上应用服务器的CPU使用率也冲到了90%。这可不是小事用户正在下单呢。我第一时间登录服务器习惯性地先看了一眼数据库监控面板好家伙MySQL实例的CPU和IO都高得离谱活跃连接数也堆起来了。问题大概率出在数据库上。这时候第一反应就是去查慢查询日志。我连上数据库执行了SHOW PROCESSLIST;果然看到有几个查询已经执行了十几秒还没结束状态是“Sending data”。但PROCESSLIST只能看到当前正在执行的历史中哪些查询拖慢了系统还得靠慢查询日志这个“黑匣子”。我迅速调整了慢查询的阈值让它临时记录所有超过1秒的查询几分钟后日志文件里就清晰地躺着了那条罪魁祸首一个缺少关键索引的联表查询在全表扫描几百万行数据。这次经历再次印证了慢查询日志是DBA和开发人员定位数据库性能问题的“第一现场”和“终极证据”。它不像监控图表那样只给一个模糊的“慢”的结论而是直接把那条拖垮数据库的SQL语句、它的执行时间、扫描行数、返回行数等细节拍在你面前。无论你是刚接手一个老项目想优化其数据库性能还是在开发过程中想提前发现潜在的性能瓶颈学会开启、配置并分析慢查询日志都是一项必备的核心技能。这篇文章我就结合多年踩坑经验带你彻底搞懂MySQL慢查询日志从开启到分析的全流程让你下次遇到性能问题时能快速定位精准打击。2. 慢查询日志到底是什么为什么非用不可在深入操作之前我们得先搞清楚这个工具的本质。你可以把MySQL服务器想象成一个繁忙的餐厅厨房SQL查询就是客人点的菜。慢查询日志就是厨房经理MySQL的一个记录本专门记录那些做起来特别慢的“菜”查询。它不关心菜做得好不好吃查询结果正确与否只关心做这道菜花了太长时间。那么“慢”的标准是什么这就是一个可配置的阈值默认是10秒。也就是说任何执行时间超过10秒的查询都会被自动记录到这个日志里。这个阈值可以根据你的业务容忍度进行调整对于OLTP在线事务处理系统可能1秒就算慢了对于后台报表系统10秒或许可以接受。为什么说它不可或缺我总结有三大原因问题定位的“铁证”当应用变慢时原因可能有很多——代码逻辑、网络、中间件、数据库。慢查询日志能直接告诉你是不是数据库层面的问题并且精确到是哪一条或哪几条SQL语句导致的。这避免了在错误的方向上浪费时间。性能优化的“指南针”通过分析日志你能发现那些频繁出现且执行缓慢的查询。这些就是你需要优先优化的目标。优化后你还可以通过对比优化前后的日志直观地看到优化效果执行时间缩短、扫描行数减少。架构与代码的“照妖镜”长期分析慢查询日志你可能会发现一些规律比如某个功能模块的查询普遍较慢或者某些类型的查询在业务高峰时容易出问题。这能反过来推动应用架构的改进如引入缓存、读写分离或代码的重构如避免N1查询。很多人觉得开启了慢查询日志会影响性能。确实写入日志会有额外的I/O开销但在生产环境中这个开销与它带来的问题诊断价值相比通常是微不足道的。况且我们可以通过合理的配置如不记录管理语句、设置合适的阈值来最小化其影响。我的建议是在测试环境和生产环境都应该开启慢查询日志这是对自己系统负责的表现。3. 手把手配置与开启慢查询日志知道了“为什么”接下来就是“怎么做”。MySQL提供了全局变量来控制慢查询日志的行为我们可以通过命令行动态设置也可以写入配置文件使其永久生效。下面我分步骤详细说明。3.1 动态开启与基本配置首先我们登录MySQL服务器查看当前慢查询日志的状态-- 查看慢查询日志是否开启 SHOW VARIABLES LIKE slow_query_log; -- 查看慢查询日志文件路径 SHOW VARIABLES LIKE slow_query_log_file; -- 查看“慢”的阈值单位秒 SHOW VARIABLES LIKE long_query_time; -- 查看是否记录未使用索引的查询即使它执行得很快 SHOW VARIABLES LIKE log_queries_not_using_indexes;如果slow_query_log的值是OFF说明还没开启。我们来开启它并设置一个合理的阈值-- 1. 开启慢查询日志 SET GLOBAL slow_query_log ON; -- 2. 设置慢查询阈值比如设为2秒。注意对于超时时间设置可能需要新会话才能生效。 SET GLOBAL long_query_time 2; -- 3. 可选但推荐设置慢查询日志文件路径确保MySQL进程有写入权限 SET GLOBAL slow_query_log_file /var/log/mysql/slow.log; -- 4. 可选开启记录未使用索引的查询。这对于发现潜在的全表扫描很有用但日志量可能会变大。 SET GLOBAL log_queries_not_using_indexes ON;注意SET GLOBAL命令设置的变量只在当前MySQL服务实例运行期间有效重启后会失效。并且long_query_time这个变量有个特殊之处它对当前已建立的会话不生效只对新建立的会话生效。所以设置后你需要重新连接MySQL或者在你的应用连接池中断开重连才能让新的阈值生效。执行完上述命令后慢查询日志就已经开始记录了。你可以故意执行一个睡眠3秒的语句来测试SELECT SLEEP(3);然后去查看你设置的slow_query_log_file如/var/log/mysql/slow.log应该就能看到这条查询的记录。3.2 写入配置文件实现永久生效动态设置适合临时调试对于生产环境我们必须将配置写入MySQL的配置文件通常是my.cnf或my.ini以确保服务重启后配置依然有效。找到你的MySQL配置文件在[mysqld]段落下添加或修改如下配置[mysqld] # 开启慢查询日志 slow_query_log 1 # 指定慢查询日志文件路径 slow_query_log_file /var/log/mysql/slow.log # 设置慢查询时间阈值为2秒 long_query_time 2 # 记录未使用索引的查询可选根据情况开启 log_queries_not_using_indexes 1 # 慢查询日志的存储格式FILE表示存文件默认TABLE可以存到mysql.slow_log表但性能开销大一般不推荐。 log_output FILE修改保存后重启MySQL服务使配置生效# 对于使用systemd的系统如CentOS 7, Ubuntu 16.04 sudo systemctl restart mysqld # 或 sudo systemctl restart mysql # 对于老版本系统 sudo service mysql restart重启后再次使用SHOW VARIABLES命令检查配置是否已按预期加载。3.3 几个关键的高级配置项除了上面基本的还有几个参数能帮你更精细地控制日志记录min_examined_row_limit设置查询扫描过的最少行数阈值。即使查询时间超过了long_query_time但如果它扫描的行数少于这个值也不会被记录。这可以用来过滤掉那些虽然执行慢但代价很小的查询比如在极小表上因锁等待导致的慢查询。log_slow_admin_statements是否将管理语句如OPTIMIZE TABLE,ANALYZE TABLE,ALTER TABLE等记入慢查询日志。这些语句本身可能就很慢记录它们有助于你了解维护操作对系统的影响。log_throttle_queries_not_using_indexes当log_queries_not_using_indexes开启时可能会瞬间产生大量日志例如一个循环调用全表扫描。这个参数可以限制每分钟内相同SQL指纹即结构相同参数不同的未使用索引查询被记录的次数避免日志爆炸。配置示例写入my.cnf[mysqld] ... # 只记录扫描行数超过1000的慢查询 min_examined_row_limit 1000 # 记录慢的管理语句 log_slow_admin_statements 1 # 限制每分钟相同未使用索引的SQL最多记录10条 log_throttle_queries_not_using_indexes 104. 解读慢查询日志一行记录里藏着什么秘密配置好并运行一段时间后你的慢查询日志文件里就会积累不少记录。每一条记录都包含丰富的信息读懂它们是分析的关键。一条典型的记录长这样为了可读性已格式化# Time: 2023-10-27T14:23:01.123456Z # UserHost: app_user[app_user] [192.168.1.100] Id: 123456 # Query_time: 5.620583 Lock_time: 0.000120 Rows_sent: 1 Rows_examined: 1002543 SET timestamp1698416581; SELECT * FROM order WHERE user_id 123 AND status pending ORDER BY create_time DESC;我们来逐行拆解# Time: 查询执行完成的时间点。非常重要可以结合业务高峰时间分析。# UserHost: 执行该查询的数据库用户和来源主机。这能帮你定位是哪个应用服务发起的查询。# Query_time:查询执行总时间单位秒。这是判断“慢”的核心指标。本例是5.62秒。# Lock_time: 查询等待锁表锁、行锁的时间。如果这个时间占Query_time很大比例说明可能存在锁竞争。# Rows_sent: 返回给客户端的数据行数。本例是1行。# Rows_examined:为了返回结果服务器层检查的行数。这是另一个关键指标本例检查了超过100万行但只返回1行。这是一个非常危险的信号说明查询可能没有有效利用索引导致了大量的无效扫描。SET timestamp: 查询开始执行时的时间戳Unix时间戳。可以通过这个时间关联其他监控数据。最后一行: 就是那条“罪魁祸首”的SQL语句本身。重点理解Rows_examined与Rows_sent的关系理想情况下Rows_examined应该略大于或等于Rows_sent。如果Rows_examined远大于Rows_sent像例子中的100万 vs 1几乎可以肯定存在全表扫描或者索引使用不当的问题这是优化的首要目标。5. 化繁为简使用工具高效分析慢查询日志直接阅读原始的慢查询日志文件效率很低尤其是当日志文件很大时。我们需要借助工具来聚合、排序、分析。MySQL官方自带了一个非常强大的工具mysqldumpslow。此外更强大的第三方工具如pt-query-digestPercona Toolkit的一部分是专业DBA的标配。5.1 使用官方工具 mysqldumpslow 进行初步分析mysqldumpslow通常随MySQL客户端一起安装。它的基本语法是解析慢查询日志文件并按照各种方式进行汇总。# 查看使用帮助 mysqldumpslow --help # 1. 得到返回记录集最多的10条SQL按Rows_sent排序 mysqldumpslow -s r -t 10 /var/log/mysql/slow.log # 2. 得到访问次数最多的10条SQL按Count排序 mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # 3. 得到按照时间排序执行时间最长的前10条SQL这是最常用的 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 4. 得到总耗时Query_time * Count最长的前10条SQL mysqldumpslow -s at -t 10 /var/log/mysql/slow.log # 5. 组合排序并且将具体的数字如user_id用N代替字符串用S代替便于归类 mysqldumpslow -s t -t 10 -g SELECT /var/log/mysql/slow.log例如执行mysqldumpslow -s t -t 5 slow.log可能输出Count: 12 Time5.62s (67s) Lock0.00s (0s) Rows1.0 (12), app_user[app_user][192.168.1.100] SELECT * FROM order WHERE user_id N AND status S ORDER BY create_time DESC这告诉我们同一种模式的查询执行了12次每次平均耗时5.62秒总耗时67秒每次返回1行。这立刻让我们意识到这不是一个偶然的慢查询而是一个高频的、有严重性能问题的查询模式必须优先处理。5.2 使用专业神器 pt-query-digest 进行深度剖析pt-query-digest的功能比mysqldumpslow强大得多它能生成一份非常详细的HTML或文本报告包括总体概览总查询次数、唯一查询指纹数、总耗时、时间分布等。响应时间排名清晰地列出最耗时的查询。执行属性分析包括扫描行数、返回行数、临时表、排序等维度的统计。表与索引使用情况。查询语句的“指纹”将具体参数抽象化真正归类同一类查询。安装Percona Toolkit后使用非常简单# 生成一份详细的文本报告 pt-query-digest /var/log/mysql/slow.log slow_report.txt # 生成HTML报告更直观 pt-query-digest /var/log/mysql/slow.log --review hlocalhost,Dslow_query_log,tglobal_query_review --history hlocalhost,Dslow_query_log,tglobal_query_review_history --no-report --limit0% --filter \$event-{Bytes} length(\$event-{arg}) and \$event-{hostname}\$HOSTNAME\ slow_report.html生成的HTML报告会有一个“Response time distribution”图表直观展示查询时间的分布还有“Rank by response time”表格详细列出每个查询指纹的调用次数、总时间、平均时间、95%时间等是性能分析的利器。我的经验是在例行巡检时用pt-query-digest生成每日或每周的慢查询分析报告。在紧急故障排查时先用mysqldumpslow -s t快速找到最慢的几条SQL立刻着手分析。6. 从分析到行动常见的慢查询优化实战分析出慢查询后接下来就是优化。这里我结合几个最常见的慢查询模式给出具体的优化思路和操作。6.1 案例一缺失索引导致的全表扫描这是最经典的问题对应我们前面例子中的SELECT * FROM order WHERE user_id 123 AND status pending扫描了100万行。分析WHERE条件涉及user_id和status两个字段。我们需要判断现有索引情况。SHOW INDEX FROM order;如果发现没有索引或者只有一个单列索引比如只有user_id那么查询可能无法高效定位数据。优化创建联合索引。这里有一个重要的原则索引列的顺序。通常将选择性更高唯一值更多的列放在前面。我们可以先查看字段的选择性SELECT COUNT(DISTINCT user_id) / COUNT(*) as user_id_selectivity, COUNT(DISTINCT status) / COUNT(*) as status_selectivity FROM order;假设user_id选择性远高于status那么创建(user_id, status)的联合索引会更有效。ALTER TABLE order ADD INDEX idx_user_status (user_id, status);创建索引后查询会先通过user_id快速定位到一批数据再在这批数据中过滤statusRows_examined会从全表行数骤降到符合条件的行数性能提升立竿见影。6.2 案例二低效的分页查询慢查询日志里经常看到这样的语句SELECT * FROM article ORDER BY create_time DESC LIMIT 1000000, 20;Query_time很长Rows_examined巨大。分析LIMIT 1000000, 20意味着MySQL需要先排序然后跳过前100万行最后取20行。即使使用了ORDER BY create_time的索引这个“跳过”的操作OFFSET代价也非常高因为MySQL需要实际扫描并丢弃这100万行。优化使用“游标分页”或“基于上次ID的分页”。-- 传统分页慢 SELECT * FROM article ORDER BY id DESC LIMIT 1000000, 20; -- 优化后记录上一页最后一条记录的ID -- 假设上一页最后一条记录的id是 1234567 SELECT * FROM article WHERE id 1234567 ORDER BY id DESC LIMIT 20;这样查询可以利用id上的主键索引直接定位到id 1234567的位置开始扫描效率极高。前端需要配合传递“最后一个ID”作为参数。6.3 案例三复杂的联表查询与临时表日志中可能出现执行时间长且Rows_examined各表加起来很大的联表查询备注里可能还有Using temporary; Using filesort。分析例如SELECT a.*, b.name, c.category FROM orders a LEFT JOIN users b ON a.user_id b.id LEFT JOIN products c ON a.product_id c.id WHERE a.create_time 2023-10-01 ORDER BY a.amount DESC LIMIT 100;这个查询涉及三张表关联并按一个非索引字段amount排序。优化确保连接字段有索引检查orders.user_id,orders.product_id,users.id,products.id上是否有索引。连接条件ON子句的字段必须有索引这是联表查询性能的基石。为WHERE条件和ORDER BY创建索引本例中WHERE a.create_time xxx和ORDER BY a.amount DESC是性能关键。可以考虑创建(create_time, amount)的联合索引但需要注意范围查询后面的列可能无法用于排序。有时更好的选择是创建(create_time)索引用于过滤或者如果amount过滤性更好则创建(amount, create_time)。这需要根据实际数据分布来测试决定。考虑反范式或汇总表如果这类报表查询非常频繁且实时性要求不高可以定期将结果计算好存入一张汇总表查询直接查汇总表避免复杂的实时联表计算。使用EXPLAIN深入分析对优化后的SQL使用EXPLAIN命令查看执行计划确认是否真正用上了你创建的索引以及连接类型type是否从ALL全表扫描提升到了ref或range。7. 将慢查询分析融入日常运维监控与自动化手动分析日志毕竟不是长久之计我们应该将其自动化并纳入监控体系。1. 日志轮转与清理慢查询日志会不断增长需要定期轮转和清理避免撑满磁盘。可以使用Linux的logrotate工具。 创建/etc/logrotate.d/mysql-slow文件/var/log/mysql/slow.log { daily rotate 30 compress delaycompress missingok notifempty create 640 mysql mysql postrotate # 如果MySQL支持可以发送FLUSH LOGS信号但更简单的方式是在配置中不指定文件而用slow_query_log_file变量。 # 这里我们采用更通用的方法在my.cnf中配置固定文件名由logrotate负责切割和创建新文件。 # 切割后需要让MySQL重新打开日志文件。通常重启MySQL或执行mysqladmin flush-logs。 /usr/bin/mysqladmin -uroot -p${密码} flush-logs 2/dev/null || true endscript }2. 定期分析报告可以写一个简单的Shell脚本定期如每天凌晨使用pt-query-digest分析前一天的慢查询日志并将报告发送到邮箱或存入数据库。#!/bin/bash LOG_FILE/var/log/mysql/slow.log YESTERDAY$(date -d yesterday %Y-%m-%d) REPORT_FILE/path/to/reports/slow_report_${YESTERDAY}.html # 使用pt-query-digest分析 pt-query-digest $LOG_FILE --since $YESTERDAY 00:00:00 --until $YESTERDAY 23:59:59 $REPORT_FILE # 发送邮件需要配置mailx等 # mail -s MySQL Slow Query Report - $YESTERDAY teamexample.com $REPORT_FILE3. 与监控系统集成将慢查询的数量、最慢查询的执行时间等作为关键指标接入到Prometheus、Zabbix等监控系统中并设置告警。例如当每分钟慢查询数量超过某个阈值或出现执行时间超过30秒的“超级慢查询”时立即触发告警让运维人员能第一时间介入。开启和用好慢查询日志就像是给数据库装上了“心电图”和“黑匣子”。它不能直接治病但能精准地告诉你病根在哪里。从被动救火到主动预防慢查询日志是你构建稳定、高效数据库系统不可或缺的一环。花点时间把它配置好并养成定期分析的习惯你在处理数据库性能问题时会更有底气也更加从容。