做MySQL运维和调优这些年我收到最多的私信大概就是“我的数据库突然变慢了怎么排查” 如果系统没有提前做性能基线采集第一件事我建议就是把慢查询慢日志打开。别看这功能听着基础一个生产环境里如果没开慢日志出了性能问题基本等于盲人摸象。今天我就把MySQL慢查询日志从原理到配置、从分析到优化完整地讲一遍想避开那些隐藏很深的坑这篇值得你收藏。慢查询日志是MySQL自带的性能诊断工具核心作用很简单把执行时间超过阈值的SQL语句记录下来。它能帮你回答三个最关键的问题哪些SQL在拖慢系统它们执行了多久扫描了多少行数据适合谁来用无论是刚入门的后端研发、日常维护的DBA还是做架构设计的同学在定位慢接口、优化索引、评估SQL质量时慢日志都是第一手证据来源。1. 慢查询日志本身不长但影响很大1.1 慢日志的底层逻辑一个记录器而已慢查询日志本质上就是一个文件或者一张表凡是满足你设定条件的SQL都会被记录进去。条件主要有两类执行时间超过long_query_time秒或者没走索引且开启了log_queries_not_using_indexes。它会记录的内容包括SQL执行时间、锁等待时间、发送和接收的字节数、扫描的行数、返回的行数以及完整的SQL语句。这个信息量已经能覆盖大部分排查需求。日志默认是关闭的。你可能会想既然这么好为什么默认不打开因为记录日志本身有开销每个查询执行完后都要判断是否需要写入高并发下还有写锁竞争。所以生产环境一般建议按需开启不要长期无条件开着。1.2 慢日志能解决的典型问题我平时排查问题慢日志主要用在下面几个场景接口突然变慢不知道是数据库还是应用层的问题。先查慢日志如果有大量慢SQL基本可以锁定数据库侧。新上线功能前评估SQL质量。把慢查询阈值调低跑一段时间看哪些SQL扫描行数异常。做索引优化时用慢日志筛选出高频慢SQL再用EXPLAIN逐条分析。判断数据库整体健康度。如果慢日志文件在增长说明系统里存在需要关注的SQL。这里提醒一句慢日志只是线索不是结论。它告诉你哪些SQL慢但为什么慢还需要结合执行计划、表结构、数据量一并分析。这个过程我会在后面的优化章节详细展开。2. 开启慢查询日志的完整过程2.1 临时开启排查问题时最快的办法临时开启的好处是不需要重启MySQL修改立即生效。适合排查线上问题时快速验证。-- 查看当前设置 SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; SHOW VARIABLES LIKE slow_query_log_file; -- 开启慢日志当前会话或全局 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;需要注意SET GLOBAL只影响后续新连接的会话当前已经连接的会话不会立即生效。你执行SHOW VARIABLES看到可能还是旧值重新连接一下即可。long_query_time的设置比较特殊如果你从1改成2已存在的会话仍按1判断新连接才按2判断。所以测试时建议新开一个会话或者改完后确认连接是最新参数。2.2 永久开启写进配置文件只靠临时开启一旦MySQL重启就失效了。生产环境需要长期开启的话要写在配置文件里让MySQL启动时自动加载。在Linux下一般是/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnfWindows下是my.ini。在[mysqld]段下添加以下内容[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1 min_examined_row_limit 100改完配置文件需要重启MySQL才能生效。如果是MySQL 8.0很多参数支持SET PERSIST可以动态修改并写入配置SET PERSIST slow_query_log ON; SET PERSIST long_query_time 1;这里提醒一个容易被忽略的事slow_query_log_file配置的目录必须存在并且MySQL进程用户通常是mysql要有写权限。否则日志不会生成而MySQL本身不会报错你可能会误以为配置没生效。2.3 核心参数解析为了让你快速查漏补缺我做了一个参数速查表参数名作用建议值slow_query_log是否开启慢日志ON / OFFslow_query_log_file慢日志文件路径磁盘剩余空间充足的路径long_query_time超过多少秒算慢查询开发环境0.1生产一般1-2log_queries_not_using_indexes未走索引的查询也记录建议开启log_slow_admin_statements是否记录管理语句如ALTER TABLE视情况开启min_examined_row_limit扫描行数小于此值不记录建议100log_throttle_queries_not_using_indexes每分钟最多记录多少条未走索引的SQL建议10log_output日志输出位置FILE或TABLE默认FILEmin_examined_row_limit这个参数很有用。如果你开了log_queries_not_using_indexes会发现很多扫描几行、几十行的SQL也被记录下来干扰视线。设置一个最小值就只记录扫描行数超过该值的未走索引查询日志质量会高很多。3. 参数怎么取经验值背后的门道3.1 long_query_time 到底设多少才合适这是新手最容易犯难的地方。设得太小日志量巨大还没什么参考价值设得太大又把问题SQL全漏过去了。我的建议分场景开发测试环境设置0.1秒甚至0.05秒把接口里所有SQL都记录下来用来做SQL审查。生产环境常规监控设置1秒比较均衡。大多数OLTP业务正常SQL应该在10毫秒级别超过1秒已经属于明显异常。对性能要求极高的金融类业务设置0.5秒。这类系统对延迟敏感需要更早发现隐患。还需要补充一点long_query_time支持小数。MySQL 5.7以上可以设置0.1、0.05这种值配合min_examined_row_limit可以有效过滤掉无意义的微小查询噪音。3.2 log_output文件还是表log_output有三个取值FILE、TABLE、FILE,TABLE。生产环境绝大多数情况用FILE就好。TABLE模式会把慢日志写进mysql.slow_log表。好处是可以用SQL查询比如按耗时排序、按用户聚合做统计很方便。坏处是慢日志量大的时候这张表的写入也会成为新的性能瓶颈而且表损坏后日志会写不进去。我的习惯是日常排查用FILE如果要做一次临时分析把慢日志导入到分析工具或临时表中再统计而不是直接开TABLE模式。3.3 几个被很多人忽略的关联参数开启慢日志之后我建议同时关注这几个参数否则日志信息会不完整log_slow_admin_statements如果关闭ALTER TABLE、OPTIMIZE TABLE这类的管理语句即使执行很久也不会记进慢日志。很多DDL导致的锁表现象就是因为这个参数没开日志里根本找不到蛛丝马迹。log_slow_slave_statements控制从库上执行SQL时产生的慢查询是否记录。如果你做了主从复制建议开启因为从库的查询延迟往往和主库不完全一样只查主库慢日志会漏掉从库的问题。log_throttle_queries_not_using_indexes是针对未走索引日志的限流参数。开了log_queries_not_using_indexes后如果一堆全表扫描SQL扎堆出现日志文件会在几分钟内爆掉。这个参数限定了每分钟最多记录多少条避免日志被刷爆。4. 慢日志分析别让一堆日志淹没了真相4.1 先用自带工具 mysqldumpslow 做粗筛日志文件一多直接打开看肯定是不现实的。MySQL自带了一个命令行工具mysqldumpslow可以按SQL语句的指纹聚合统计把同类SQL归并成一条记录输出执行次数、平均耗时等信息。常用命令示例# 按平均查询时间排序取前10条 mysqldumpslow -s at -t 10 /var/log/mysql/slow.log # 按执行次数排序取前20条 mysqldumpslow -s c -t 20 /var/log/mysql/slow.log # 只查看包含user表的慢查询 mysqldumpslow -g user /var/log/mysql/slow.log输出内容类似这样Count: 123 Time2.31s (284s) Lock0.00s (0s) Rows50000.0 (6150000), user[user][10.0.0.1] SELECT * FROM orders WHERE statuspending ORDER BY created_at DESC LIMIT 1000;这一行信息量很大出现了123次平均2.31秒总共扫描了615万行。基本可以断定这条SQL需要重点优化。需要注意mysqldumpslow的排序参数中c是次数t是查询时间at是平均时间al是平均锁时间ar是平均返回行数。记不住的时候可以mysqldumpslow --help查一下。4.2 pt-query-digest比官方工具更专业的报告如果你对慢日志有更深层次的分析需求推荐用Percona Toolkit里的pt-query-digest。它能生成一份结构化报告把SQL按指纹分组统计出每个查询的占比、响应时间分布、扫描行数和返回行数的对比。用法很简单pt-query-digest /var/log/mysql/slow.log slow_report.txt报告重点看三个部分Overall统计总查询数、总耗时、平均耗时以及百分位分布先全局感受一下严重程度。Profile表格按总查询时间排序的Top SQL列表每个查询有占比、平均耗时、扫描行数等。具体SQL详情针对某条慢SQL展示所有时间分布的直方图附带完整的SQL语句方便定位。我实际用下来pt-query-digest比自带工具更直观因为它会算出一个“响应时间占比”。如果某条SQL占到总慢查询时间的50%以上那它就是首选的优化目标。4.3 分析慢日志的三个核心维度看慢日志不是只看耗时排序就完了。我一般会从下面三个维度交叉判断第一个维度执行次数。次数多说明是高频SQL哪怕单次耗时不算特别高积少成多也会拖垮数据库。优先优化高频中等耗时的SQL收益往往比优化一条极少执行的超慢SQL更大。第二个维度扫描行数与返回行数。如果扫描10万行只返回10行说明查询走了全表扫描或索引选择性差存在明显的优化空间。如果扫描和返回行数都很少但耗时高那问题可能不在SQL本身而在锁等待、网络延迟或者是服务器资源争用。第三个维度时间分布。如果某条SQL只在每天某个固定时段出现很可能是定时任务或批处理在跑如果全天分散出现说明业务侧存在持续性问题。这个信息能从日志里的执行时间戳看出来。5. 找到慢SQL后到底怎么优化5.1 慢查询最常见的几类原因拿我处理过的真实案例来说慢SQL的典型病因基本就是这几个第一类查询条件没有合适的索引。最简单的例子WHERE status1但status字段没有索引全表扫描。这属于最常见也最好解决的问题。第二类隐式类型转换。比如字符串字段order_no存的是字符查询条件却写成order_no 123456MySQL会把字符串转成数字导致索引失效。这个问题非常隐蔽排查时如果不仔细看字段类型很容易漏掉。第三类函数包裹索引列。WHERE DATE(create_time)2024-01-01这种写法会让create_time上的索引失效。应该改成范围查询create_time 2024-01-01 AND create_time 2024-01-02。第四类深分页。LIMIT 100000, 20这类翻页很深的查询前面的10万行都得扫描一遍。优化方式可以用游标分页或子查询优化。第五类数据量增长后未及时调整索引。上线时数据量小查询没问题过了半年数据涨到千万级原来的索引不够用了慢日志才开始报警。5.2 EXPLAIN是慢SQL的体检报告遇到一条慢SQL别急着加索引。先用EXPLAIN分析执行计划EXPLAIN SELECT * FROM orders WHERE statuspending ORDER BY created_at DESC;重点看这些列type从const、ref、range到ALL级别逐渐变差。出现ALL基本就是全表扫描。key实际用到的索引。如果为NULL说明没有可用索引。rows预估扫描行数。这个数值越大说明查询越重。Extra出现Using filesort意味着排序没有走索引出现Using temporary意味着使用了临时表都是优化信号。我的建议是拿到一条慢SQL先EXPLAIN确认执行计划再决定改SQL还是加索引。不要凭感觉操作。5.3 几个典型慢SQL的改造案例案例一分页慢查询优化前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;这个写法的核心思想是先快速定位到目标的起始位置只扫描必要的20条记录而不是扫描前面的10万行。案例二隐式转换假设order_no是varchar(64)优化前SELECT * FROM orders WHERE order_no 202401010001;优化后SELECT * FROM orders WHERE order_no 202401010001;把查询常量改为匹配字段类型的字符串索引就能正常走。案例三函数导致索引失效优化前SELECT * FROM orders WHERE DATE(created_at) 2024-01-01;优化后SELECT * FROM orders WHERE created_at 2024-01-01 AND created_at 2024-01-02;改成范围查询后创建时间索引就能生效。6. 常见问题与排查技巧实录6.1 慢日志没生效或者找不到文件这个问题我在社群里见过太多次了。网上很多人说“开启了慢日志但没生成文件”一查基本是两个原因。一个是slow_query_log_file只指定了文件名没指定完整路径。MySQL会把这个文件写到数据目录下也就是datadir指定的位置。你可以在配置里写完整路径比如/var/log/mysql/slow.log。另一个是日志目录权限不够。记住MySQL进程是以mysql用户运行的如果目录是root所有它就没权限创建文件。验证是否生效的方法很简单执行一条耗时很长的查询SELECT SLEEP(3);然后去日志文件里看是否多出了一条记录。如果记录存在说明配置正确如果不存在依次检查slow_query_log、long_query_time、文件路径、目录权限。6.2 慢日志文件膨胀太快要不要清理慢日志文件如果长时间不清理会越攒越大占用磁盘空间。我见过最大的一个慢日志文件有80GB把磁盘直接写满了。处理方式有两种方案方案一用mysqldumpslow或者pt-query-digest分析完旧日志后手动清空文件。注意不要直接删除文件而是用truncate或echo slow.log清空内容。直接删除文件会导致MySQL继续往已删除的inode里写数据磁盘空间不会释放。方案二在系统层面配置日志轮转。Linux下可以用logrotate比如每天切割一次保留7天的历史日志/var/log/mysql/slow.log { daily rotate 7 compress copytruncate }放好配置后建议先运行logrotate -d /etc/logrotate.d/mysql-slow做一次模拟测试确认没有问题。6.3 用表记录慢日志时遇到的状态问题如果你好奇开了log_outputTABLE慢日志会写入mysql.slow_log表。这张表有时会出现打不开、查询很慢、甚至损坏的情况。原因是慢日志表本身也是InnoDB表在慢SQL数量很大的情况下INSERT INTO mysql.slow_log的操作排队反而拖慢了整个系统。而且这张表的默认表结构里没有主键数据量大了之后查询它自身也会慢。如果你确实需要用表来记录慢日志我建议定期清理这张表的数据比如只保留最近一周的记录SET GLOBAL slow_query_log OFF; TRUNCATE TABLE mysql.slow_log; SET GLOBAL slow_query_log ON;注意清空之前先确认你不需要这些历史数据。如果要做统计分析先把数据导出到一个独立库再清空。6.4 分析工具连不上数据库时怎么办用pt-query-digest或写脚本统计慢日志时有时会遇到命令行连接MySQL报错的问题。最常见的报错包括无法通过sock文件连接、SSL连接错误等。这类问题排查思路是这样的首先确认MySQL监听端口和socket文件路径用SHOW VARIABLES LIKE socket查看。如果命令行指定socket路径仍连不上就检查MySQL是否正常运行用ps -ef | grep mysqld查看进程状态。SSL报错通常发生在MySQL 8.0之后默认启用SSL的情况下。你可以临时关闭SSL连接参数来测试排查也可以确保连接时设置正确的SSL模式。当然这属于连接层的问题和慢查询本身关系不大但如果分析工具必须连上数据库才能工作这个环节卡住了后面的分析也就无从谈起。我个人的习惯是分析慢日志文件时优先用pt-query-digest直接读文件它不需要连接数据库就能生成报告。只有需要辅助获取表结构时才连库尽量减少对线上实例的连接干预。6.5 一个被大多数教程忽略的细节时间基准排查慢日志时很多人会忽略一个参数MySQL记录到慢日志的时间是基于语句开始执行的时间而不是结束时间。如果一个连接在long_query_time1的情况下执行了一条耗时2秒的SQL它在慢日志里的记录时间是启动时刻而不是2秒后的完成时刻。这意味着你去查“刚才某个时间点为什么出现高峰”可能需要多看前后几分钟的日志而不是只看准点那一条。尤其在做跨系统关联排查时这个细节容易让人产生误判。7. 慢日志的进阶玩法7.1 从慢日志里提取SQL做回归测试慢日志里的SQL是最真实的业务压力来源。我曾经把线上慢日志中的SQL整理出来做成一个回归测试集每次做索引变更或版本升级前在预发布环境重放一遍对比优化前后的执行时间。这个思路很简单用pt-query-digest从慢日志中提取Top SQL然后手动整理成固定的测试脚本再结合批量执行工具重放。执行前后分别取执行时间、扫描行数、执行计划做对比用数据验证优化效果。7.2 把慢日志接入监控告警体系慢日志的价值不只在排查问题更在提前发现问题。你可以写个脚本定期扫描慢日志文件统计最近5分钟内慢SQL的条数和最大耗时一旦超过阈值就推送告警。这样比等业务反馈“系统卡了”再事后排查要主动得多。一个简单的思路#!/bin/bash LOG/var/log/mysql/slow.log COUNT$(grep -c Time $LOG) if [ $COUNT -gt 100 ]; then echo 最近周期内慢查询数量异常: $COUNT | mail -s MySQL慢查询告警 opsexample.com fi这只是雏形生产环境建议用更完善的方案比如用脚本解析日志后写入监控系统的自定义指标或者通过采集器把日志接入日志平台配合可视化面板做趋势分析。7.3 不要忘了 performance_schema慢日志和performance_schema不是替代关系。慢日志记录的是超过阈值的SQL而performance_schema记录的是所有语句的统计信息包含了很多锁等待、IO等待的细节。当某条慢SQL的执行计划看起来找不到问题时我建议去performance_schema.events_statements_summary_by_digest里看更细的指标比如锁时间、IO时间、CPU时间。这类数据能帮你在“SQL本身没问题”的时候找到真正的元凶比如服务器内存不足导致的swap、磁盘IO抖动等等。写在最后慢日志这个功能表面看就是打开开关、设个阈值、分析几个文件但真正用好的关键在于明白每个参数背后的效果知道日志里每一列代表的含义并且能从大量日志中精准定位到真正需要优化的SQL。我个人在实际操作中的体会是慢日志最怕两件事一是没开等出了问题才后悔二是开了不管日志堆积成山却没人看。把它当成日常巡检的一部分定期分析、定期清理配合合理的索引设计大半的数据库性能问题都能提前暴露。最后再分享一个小技巧优化一条慢SQL后不要只对比优化前后的耗时一定要把EXPLAIN的执行计划也截图留存。这样下次再出现类似问题时你能快速判断到底是索引被删了、数据量变化了还是SQL被改动了。慢日志是线索执行计划是证据两者配合才能把问题彻底看透。