1. 慢查询日志数据库性能的“听诊器”干了这么多年数据库运维和开发我越来越觉得慢查询日志Slow Query Log就像是给MySQL数据库装上的一个全天候“听诊器”。它不生产数据它只是数据库执行状态的忠实记录者。当你发现应用响应变慢、服务器CPU莫名飙升或者用户开始抱怨页面加载卡顿时第一反应不应该是重启服务或者盲目加机器而是应该先听听这个“听诊器”的声音——看看慢查询日志里到底记录了些什么。简单来说慢查询日志会记录所有执行时间超过指定阈值long_query_time的SQL语句以及执行时的一些关键信息比如执行耗时、返回行数、扫描行数、锁等待时间等。这玩意儿在线上环境排查性能瓶颈时其价值无可替代。很多初级开发者可能会过度依赖各种监控图表但图表只能告诉你系统“病了”比如CPU高、慢接口多而慢查询日志却能直接告诉你“病因”是什么——是哪几条SQL语句拖垮了系统。今天我就结合自己踩过的坑和积累的经验把慢查询日志的查看、配置、分析这一整套流程掰开揉碎了讲清楚让你不仅能上手操作更能理解背后的门道。2. 核心配置让日志“说”你想听的话慢查询日志不是默认开启的需要你主动去配置和启用。配置的核心在于平衡日志记录得太细会占用大量磁盘I/O甚至影响数据库本身性能记录得太粗又可能漏掉关键的性能瓶颈线索。所以如何配置很有讲究。2.1 关键参数详解与配置方法MySQL中控制慢查询日志的核心参数主要有以下几个我们可以通过SHOW VARIABLES LIKE ‘%slow%’;和SHOW VARIABLES LIKE ‘long_query_time’;来查看当前设置。1.slow_query_log总开关这个参数控制慢查询日志功能的开启或关闭值可以是ON或OFF。这是第一步不开这个后面都白搭。2.slow_query_log_file日志文件路径指定慢查询日志文件存放的路径和文件名。如果不设置默认文件名通常是host_name-slow.log存放在数据目录datadir下。我强烈建议你明确指定一个路径最好放在一个独立、容量充足的磁盘分区上避免日志暴涨把系统盘写满导致数据库宕机这种低级又严重的事故。3.long_query_time时间阈值这是最重要的参数之一单位是秒。它定义了“慢”的标准。默认值是10秒这意味着执行超过10秒的查询才会被记录。对于现在的互联网应用10秒太宽松了等它报警用户早就流失光了。通常我会根据业务敏感度将其设置为0.1秒100毫秒、0.5秒甚至1秒。设置为0则会记录所有查询这在压测或深度性能剖析时有用但生产环境慎用。这里有个关键细节long_query_time的值可以精确到微秒。例如SET GLOBAL long_query_time 0.1;会将阈值设置为100毫秒。但要注意在命令行客户端设置后你需要新开一个会话才能看到这个变量的新值因为该值是基于会话的。4.log_queries_not_using_indexes记录未使用索引的查询当这个参数设置为ON时即使查询执行时间没有超过long_query_time但只要它没有使用任何索引也会被记录到慢查询日志中。这是一个非常有用的“辅助诊断”功能。很多时候一个查询本身执行很快但在大表上全表扫描对系统整体I/O和缓存是巨大负担。开启它可以帮助你发现那些潜在的、“慢”在资源消耗上的查询。5.log_throttle_queries_not_using_indexes限流记录这是MySQL 5.6.5版本引入的一个贴心功能。当log_queries_not_using_indexes开启后可能会瞬间产生大量日志比如一个循环调用全表扫描。这个参数用来限制每分钟最多记录多少条未使用索引的查询超出的部分只会被统计而不会记录SQL文本避免日志被刷爆。默认是0表示不限流。生产环境建议设置一个值比如60。6.min_examined_row_limit最小检查行数阈值查询需要检查的行数例如扫描的行数超过这个值并且执行时间也超过了long_query_time才会被记录。这可以帮你过滤掉那些虽然执行慢但本身扫描数据量就很大的“合理慢查询”聚焦于扫描行数不多却执行很慢的“异常慢查询”。7.log_slow_admin_statements记录管理语句是否将慢的管理语句如OPTIMIZE TABLE,ANALYZE TABLE,ALTER TABLE等记入慢查询日志。这类语句通常本来就很慢但记录它们有助于你了解管理操作对系统的影响。8.log_output日志输出格式这个参数决定了慢查询日志的输出目的地。主要有两种选择FILE输出到文件。这是最经典、最常用的方式。TABLE输出到mysql.slow_log表中。这种方式便于用SQL直接查询和分析但记录大量慢查询时对系统表空间有压力。NONE不记录即使slow_query_logON也无效。你可以同时指定FILE, TABLE但生产环境我通常只选FILE更稳定对数据库影响最小。2.2 动态配置与永久生效配置这些参数有两种方式动态设置和修改配置文件。动态设置立即生效重启失效在MySQL命令行中使用SET GLOBAL命令。例如SET GLOBAL slow_query_log ‘ON’; SET GLOBAL slow_query_log_file ‘/var/log/mysql/mysql-slow.log’; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ‘ON’;这种方式的好处是无需重启数据库立即生效适合临时开启排查问题。但MySQL服务一旦重启这些设置就会丢失恢复为配置文件中的值或默认值。修改配置文件永久生效需要编辑MySQL的配置文件通常是my.cnfLinux或my.iniWindows。在[mysqld]段落下添加或修改相应的参数。[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1 log_throttle_queries_not_using_indexes 60修改配置文件后必须重启MySQL服务才能使配置生效。这是生产环境的标准做法确保配置持久化。注意修改slow_query_log_file路径时务必确保MySQL进程的运行用户通常是mysql对该路径拥有读写权限否则日志无法生成这是一个非常常见的踩坑点。你可以使用chown -R mysql:mysql /var/log/mysql来修改目录归属。3. 日志解读从原始记录到问题线索配置好并运行一段时间后你的慢查询日志文件里就会开始积累内容。原始的日志文件是文本格式直接阅读比较吃力。一条典型的记录长这样# Time: 2023-10-27T08:15:23.123456Z # UserHost: app_user[app_user] [192.168.1.100] Id: 123456 # Query_time: 2.534120 Lock_time: 0.000123 Rows_sent: 1 Rows_examined: 1000000 SET timestamp1698394523; SELECT * FROM order_history WHERE user_id 12345 AND create_time ‘2023-01-01’ ORDER BY id DESC LIMIT 10;我们来拆解每一行的含义Time查询执行完成的时间点UTC时间。UserHost执行该查询的数据库用户、客户端主机。Id是连接线程ID。Query_time最关键指标查询执行的总耗时单位秒。Lock_time查询等待表锁的时间对于InnoDB主要是行锁等待时间。如果这个时间占Query_time很大比例说明锁竞争严重。Rows_sent返回给客户端的数据行数。例子中是1行。Rows_examined服务器层检查的行数。这是另一个黄金指标。例子中扫描了100万行才返回1行这几乎可以肯定是因为索引缺失或索引使用不当。Rows_examined与Rows_sent的比值越大查询效率通常越低。SET timestamp查询开始执行时的时间戳Unix时间戳。最后一行就是被记录的慢SQL语句本身。从这条记录我们能分析出什么问题定位查询耗时2.53秒对于用户交互来说太慢了。根源推测Rows_examined: 1000000对比Rows_sent: 1说明查询进行了巨大的数据扫描。很可能user_id或create_time字段上没有合适的索引或者虽然有索引但没能被有效利用比如因为ORDER BY id DESC导致排序无法利用索引。下一步行动需要去检查order_history表在user_id和create_time字段上的索引情况并考虑优化SQL写法或创建联合索引。4. 高效分析借助工具从海量日志中淘金面对动辄几个G的慢查询日志文件用grep、awk等命令手动分析效率太低。我们必须借助专门的工具。这里我首推pt-query-digest它是 Percona Toolkit 工具集里的王牌也是业界事实上的标准。4.1 安装与基本使用在Linux上你可以通过包管理器安装如yum install percona-toolkit或apt-get install percona-toolkit也可以直接下载二进制包。最基本的用法非常简单pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt这条命令会分析指定的慢查询日志文件并将一份结构化的分析报告输出到slow_report.txt中。报告内容极其丰富。4.2 解读分析报告pt-query-digest的报告分为几个主要部分我们挑核心的看1. 总体概览报告开头会给出日志的时间范围、总共有多少条独特的查询unique、总共花了多少时间total time等。比如# 120.6s user time, 1.4s system time, 99.00M rss, 263.32M vsz # Current date: Fri Oct 27 10:00:00 2023 # Hostname: db-server-1 # Files: mysql-slow.log # Overall: 1.02k total, 15 unique, 0.00 QPS, 0.00x concurrency ________ # Time range: 2023-10-26T12:00:00 to 2023-10-27T10:00:00 # Attribute total min max avg 95% stddev median # # Exec time 284s 1ms 12s 278ms 2s 1s 89ms # Lock time 8s 0 500ms 7ms 50ms 18ms 1ms # Rows sent 5.18M 0 100.00k 5.09k 49.41k 11.20k 964.52 # Rows examine 100.45M 0 2.50M 98.68k 874.02k 187.64k 10.02k # Query size 3.65M 6 450.66 3.67k 218.46 7.07k 158.98这里你能快速了解系统慢查询的总体负担总执行时间284秒以及各项指标的分布。2. 响应时间分布这部分以“直方图”的形式展示了所有查询执行时间的分布情况让你一眼看出慢查询主要集中在哪个时间区间。3. 排名靠前的SQL语句这是报告的核心。pt-query-digest会对SQL语句进行“指纹”化处理将具体变量值替换为占位符如WHERE id123变成WHERE id?然后按总执行时间Exec time或执行次数Calls进行排序。 对于每一条“指纹化”的SQL它会给出详细统计# Rank Query ID Response time Calls R/Call V/M Item # # 1 0xABCDEF123456789 112.1768 39.5% 101 1.1107 0.05 SELECT orders ... # 2 0xFEDCBA987654321 78.9012 27.8% 2456 0.0321 0.01 UPDATE user_log...Response time该模式SQL的总响应时间及其占总时间的百分比。优化就要从占比最高的“头号慢查询”开始收益最大。Calls该模式SQL被执行的次数。R/Call每次执行的平均响应时间。V/M响应时间的方差与均值之比反映执行时间的稳定性。值越大说明该查询执行时间波动越大可能受数据分布、缓存命中率影响大。报告还会展示该SQL的“指纹”样例、具体的执行统计包括Rows_sent、Rows_examined的分布以及一个简短的“摘要”可能提示“缺少索引”或“全表扫描”。4.3 进阶使用技巧分析最近一段时间的日志pt-query-digest --since ‘24h’ /var/log/mysql/mysql-slow.log只分析最近24小时的日志。定期分析并发送报告你可以将pt-query-digest命令加入crontab定期分析并将报告通过邮件发送给DBA或开发团队建立性能监控闭环。对比分析使用pt-query-digest --review功能可以将本次分析结果与历史基线进行对比发现新增的或变慢的查询模式。直接分析表如果log_output‘TABLE’可以直接分析mysql.slow_log表pt-query-digest --filter ‘$event-{fingerprint} ~ m/^SELECT/’ mysql.slow_log。5. 实战优化从分析到行动的完整闭环拿到分析报告找到了“罪魁祸首”SQL接下来就是真正的优化实战。优化不是盲目的需要遵循科学的步骤。5.1 优化步骤与思路第一步复现与确认不要直接在生产环境的大表上操作。尝试在测试环境或从库上用EXPLAIN命令分析该SQL的执行计划。EXPLAIN是SQL优化的必备工具它会展示MySQL打算如何执行这条查询用了哪个索引、访问类型是什么ALL全表扫描、index索引扫描、range范围扫描等、扫描了多少行、是否使用了临时表或文件排序。EXPLAIN SELECT * FROM order_history WHERE user_id 12345 AND create_time ‘2023-01-01’ ORDER BY id DESC LIMIT 10;重点关注type列访问类型应避免ALL、key列实际使用的索引、rows列预估扫描行数和Extra列额外信息如Using filesort、Using temporary都是危险信号。第二步索引优化这是解决慢查询最有效的手段。针对EXPLAIN的结果和WHERE、ORDER BY、GROUP BY子句设计或调整索引。联合索引对于WHERE user_id ? AND create_time ?这样的条件一个(user_id, create_time)的联合索引通常比两个单列索引更高效因为它能同时满足两个条件的过滤。覆盖索引如果查询只需要返回索引中包含的列MySQL可以直接从索引中获取数据无需回表这能极大提升性能。例如如果上面的查询只需要user_id,create_time,id字段创建一个(user_id, create_time, id)的索引就能实现覆盖索引。索引顺序原则联合索引中列的顺序至关重要。应该将等值查询的列放在最左边范围查询的列放在后面。同时要考虑ORDER BY和GROUP BY的列尽量让索引也能满足排序需求避免额外的filesort。第三步SQL语句重写有时候问题出在SQL写法上。避免SELECT *只查询需要的列减少数据传输量和可能的内存消耗。优化子查询很多情况下JOIN比子查询效率更高尤其是关联子查询。可以用EXPLAIN对比不同写法的执行计划。分页优化对于LIMIT 100000, 10这种深度分页偏移量巨大时效率极低。可以尝试改用“游标分页”WHERE id last_id LIMIT 10或者先通过子查询获取主键ID再进行关联。避免在索引列上使用函数或计算WHERE DATE(create_time) ‘2023-10-27’会导致索引失效。应改为WHERE create_time ‘2023-10-27’ AND create_time ‘2023-10-28’。第四步业务与架构层面考量如果SQL和索引已经优化到极致但性能仍不达标可能需要从更高层面思考数据归档order_history这类历史表是否可以将很早之前的数据迁移到归档库或冷存储中减少主表数据量读写分离将报表类、分析类的复杂查询路由到只读从库减轻主库压力。引入缓存对于实时性要求不高的数据能否使用Redis等缓存避免频繁查询数据库业务逻辑调整这个查询是否真的需要这么实时能否改为异步计算或批量处理5.2 一个完整的优化案例假设我们通过pt-query-digest发现最慢的查询是SELECT id, username, email FROM users WHERE age BETWEEN 20 AND 30 AND city ‘Shanghai’ ORDER BY last_login DESC LIMIT 100;EXPLAIN显示type为ALL进行了全表扫描rows预估 50 万行Extra显示Using filesort。分析查询条件涉及age范围和city等值排序依据是last_login。现有索引可能不匹配。优化方案创建联合索引由于city是等值条件age是范围条件根据最左前缀原则索引顺序应为(city, age)。但这样仍然无法避免last_login的filesort。创建覆盖索引为了同时满足过滤和排序并实现覆盖索引查询的id, username, email都在索引中我们可以创建一个更强大的索引(city, age, last_login, id, username, email)。这样数据库可以使用city进行等值匹配。在city相同的情况下用age进行范围过滤。对于过滤出的结果其last_login已经是按序排列的因为索引是(city, age, last_login, …)可以直接按序取出无需额外排序。由于索引包含了所有需要的列id, username, email无需回表。 这个索引虽然宽但针对这条特定查询性能提升是颠覆性的。当然需要权衡索引维护的代价。创建索引后再次EXPLAINtype会变为range或index使用了索引Extra中的Using filesort会消失变为Using index覆盖索引。慢查询日志里将再也看不到这条语句的身影。6. 日常维护与最佳实践慢查询日志的管理和分析应该是一个持续的过程而不是出了问题才临时抱佛脚。1. 日志轮转与清理慢查询日志会不断增长必须定期清理。不要直接用rm删除正在被MySQL写入的日志文件。正确做法是手动轮转先FLUSH LOGS;命令让MySQL关闭当前日志并打开一个新文件然后再备份或删除旧的日志文件。使用logrotate在Linux上配置logrotate工具是标准做法。可以设置按天或按大小切割并保留一定天数如7天或30天的日志。/var/log/mysql/mysql-slow.log { daily rotate 30 compress delaycompress missingok notifempty create 640 mysql mysql sharedscripts postrotate /usr/bin/mysqladmin flush-logs endscript }2. 监控与告警将慢查询数量、平均执行时间等指标纳入你的监控系统如 Prometheus Grafana。可以设置告警例如当每分钟慢查询数量超过阈值或出现执行时间超过5秒的“超级慢查询”时立即通知DBA。3. 配置经验值参考对于大多数OLTP在线事务处理类型的互联网应用我的经验配置是long_query_time 0.5或1500毫秒到1秒log_queries_not_using_indexes ONlog_throttle_queries_not_using_indexes 60min_examined_row_limit 100可根据表大小调整slow_query_log_file指向一个独立的、有足够空间的磁盘分区4. 一个容易忽略的坑瞬时高峰有时候慢查询日志里突然出现大量同一类型的慢查询但平时没有。这很可能是遇到了“瞬时高峰”或“缓存失效”。例如一个原本有索引的查询因为某种原因如统计信息过时导致优化器选错了索引变成了全表扫描。这时除了优化SQL还要考虑检查表的统计信息是否及时更新ANALYZE TABLE。检查是否存在锁竞争导致查询排队。检查数据库的缓冲池innodb_buffer_pool是否足够大能否缓存热点数据。慢查询日志是MySQL给予我们的一把利器但它提供的是“线索”而非“答案”。从开启日志、分析报告到实施优化每一步都需要结合具体的业务场景、数据特性和数据库知识进行判断。把它作为你性能优化工作流中常态化的一环定期审视持续优化你的数据库系统才会保持健壮和高效。