做MySQL性能优化我第一件事永远是翻慢查询日志。这不是什么高深的理论而是最朴素的入口数据库自己把“表现差”的语句一条条记下来你只需要去看、去分析、去改。但很多朋友对慢查询日志的用法其实停留在“打开开关、看有没有超时SQL”这个层面要么日志开了一周没去看要么阈值设得太低把磁盘撑爆要么分析的时候被一堆字段搞晕。这篇文章就把MySQL慢查询日志从配置到分析再到具体优化思路完整拆一遍既有可直接抄的参数配置也有我实际排查中踩过的坑希望能帮你把这条最基础也最关键的优化路径真正打通。很多人会把“慢查询日志”和“SQL优化”当成两件事其实它们是一条链路上的两个环节。日志负责暴露问题优化负责解决问题中间的分析过程才是真正拉开差距的地方。这篇文章适合刚接触数据库优化的新手也适合已经配置了慢日志但不知道怎么深入分析的同学看完之后你可以直接照着操作把你的慢SQL清单变成一张有节奏、可推进的优化计划。1. 慢查询日志到底是什么为什么值得花时间搞懂1.1 慢查询日志的定位与核心价值MySQL慢查询日志Slow Query Log本质是一个运行时记录器它会把执行时间超过指定阈值的SQL语句包括SELECT、INSERT、UPDATE、DELETE等连同执行耗时、锁等待时间、返回行数、扫描行数等关键信息一起写出来。默认情况下这个功能是关闭的原因很简单记录本身有开销MySQL不会默认帮你扛这笔成本。但一旦你决定做性能优化它是性价比最高的起点——不需要装额外的监控工具不需要改业务代码只要打开开关MySQL就会帮你把数据库里“最吃力”的语句全部标记出来。它的核心价值在于数据库自己最清楚自己哪里疼。你在应用层可能感知到某个接口变慢了但到底慢在哪个SQL上慢在哪个环节靠猜是不行的。慢查询日志把时间开销这个最客观的指标摆到台面上执行了3秒的查询就是3秒扫描了1000万行就是1000万行没有任何主观争议。基于这份客观清单再去优化你做的每一步都有据可查。1.2 能解决什么问题又解决不了什么问题慢查询日志在以下场景中几乎是第一排查手段上线刚发布完某个功能后接口突然超时翻慢日志基本能定位到是否是某个新SQL走了全表扫描运营后台的报表查询越来越慢慢日志能告诉你究竟是查询本身复杂还是表数据量增长后索引失效数据库CPU突增但业务量没有明显变化慢日志往往能看到某条平时很快的SQL突然执行计划改变。但它也有明显边界。第一它记录的是“执行时间超过阈值”的语句如果某条SQL执行很快但调用次数极其频繁比如每秒执行5000次、每次耗时20毫秒它的总开销可能比一条执行2秒的慢SQL更大但慢日志根本不会记它。第二它不直接告诉你为什么慢只告诉你它慢。是缺索引、是统计信息过期、是锁等待还是硬件资源问题都需要你继续用EXPLAIN、SHOW PROFILE、performance_schema等工具去深挖。第三在默认配置下它只记录语句文本和执行统计不记录执行计划如果SQL被业务框架“包装”过比如ORM生成的复杂关联查询你看到的可能是拼接后的不可读语句分析难度会上升。所以我的判断是慢查询日志是优化的入口凭证但不是全部答案。你需要把它和其他诊断手段配合起来使用这才是这套方法论真正完整的姿态。2. 慢查询日志的完整配置从参数到实操2.1 核心参数一览与配置示例慢查询日志相关参数其实不多但每个都值得仔细理解。最基础的是5个slow_query_log开关、slow_query_log_file日志文件路径、long_query_time阈值单位秒、log_queries_not_using_indexes是否记录没有走索引的查询、log_output日志输出方式FILE或TABLE也可以是两者。我一般会在MySQL配置文件Linux下是my.cnfWindows下是my.ini的[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 min_examined_row_limit 100 log_output FILE这里有几个细节要展开说明。long_query_time设置成1秒是我在绝大多数OLTP业务里的起步值。0.5秒可能过于激进会把很多正常业务查询都收进来日志量飞速膨胀5秒又太宽松很多真正影响用户体验的查询比如2秒、3秒会被漏掉。1秒是一个相对合理的“黄金起点”开启后运行一两周根据日志量和业务特征再微调。log_queries_not_using_indexes这个参数很值得开但也最容易出问题。它会把所有没有用到索引的查询都记录下来不管执行多快。这在定位“潜在隐患”时很有用但如果你某个表很小比如只有几百行MySQL大概率会走全表扫描并认为这样做效率最高于是这条SQL也会被记进来造成大量无关日志。所以需要配合min_examined_row_limit一起使用——这个参数表示只有扫描行数超过指定值的查询才会被记录我用它来过滤掉小表全表扫描这种“噪音”一般设100比较合理。2.2 动态开启与静态配置的取舍生产环境里你大概率会遇到一个尴尬场景数据库已经跑着业务不能随便重启但你又想马上开启慢日志。这时候MySQL提供了全局动态变量不需要重启即可在线修改SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log; SET GLOBAL log_queries_not_using_indexes ON; SET GLOBAL min_examined_row_limit 100;这里有一个关键细节long_query_time这类参数在动态修改后当前已经建立的连接不会立即生效需要新建立的连接才会用新值。很多人在测试环境里执行了SET GLOBAL long_query_time 1然后用当前已有连接执行一条耗时为2秒的查询结果发现没被记录第一反应是“参数没生效”其实是连接缓存的问题。你重新开一个mysql客户端连接去执行查询日志就有了。不过动态修改只对当前实例有效一旦MySQL重启就会回到配置文件里的值。所以正确姿势是先用动态参数在线开启并观察确认阈值和日志量都合理之后再把同样参数写入配置文件确保重启后依旧生效。线上操作我都是按这个顺序来的避免直接改配置文件导致重启后参数和预期不一致。2.3 进阶参数日志节流与其他补充项除了上面5个基础参数还有几个进阶参数建议掌握。log_throttle_queries_not_using_indexes用于限制没有使用索引的查询的记录频率。比如设置成10表示每分钟最多记录10条这类SQL避免大量同类无索引查询瞬间刷爆日志文件。这在排查“某个SQL突然全表扫描”的时候非常有用既能捕捉到问题语句又不会让日志文件在几分钟内涨到几个G。log_slow_admin_statements用于决定是否记录ALTER TABLE、ANALYZE TABLE等管理语句。默认情况下这些语句即使执行很慢也不会记录。如果你们业务有大表DDL操作建议开启因为一条ALTER TABLE执行了20秒对整个系统的冲击可能比普通查询更大。log_slow_slave_statements用于控制从库上执行的中继日志中的语句是否也记录慢日志。在主从架构下如果从库上应用主库传来的binlog时执行很慢默认不会记录这就导致你排查从库延迟问题时而看不到那些真正“拖后腿”的SQL。需要复制场景的话把这个参数打开能让你看清从库自己的执行瓶颈。2.4 用表存储慢日志的坑与适用场景log_output参数除了FILE还支持TABLE也就是把慢日志写到mysql.slow_log表里。好处是可以用SQL直接查询、排序、统计不需要再用命令行工具去解析日志文件。听起来很方便但我强烈不建议在常规生产环境里使用。原因是写表本身也是写操作而且还要走InnoDB事务提交。在慢查询特别多的情况下这相当于给数据库增加了额外负载反而可能拖慢整体性能。另外mysql.slow_log表里的SQL文本是文本类型用起来没有直接读文件来得轻量。如果两个都想要可以把log_output设置成FILE,TABLE但这会让“写日志”这件事本身成为新的瓶颈点除非你有非常明确的需求否则不要这么干。我的经验是日常采集用FILE需要统计聚合时候用mysqldumpslow或pt-query-digest离线分析文件效果比直接查表好得多。mysql.slow_log表更适合临时排查或者在一些没有文件系统权限的云数据库实例上被迫只能用表方式查看。3. 慢查询日志怎么读格式拆解与常用分析手段3.1 一条慢日志的完整字段解读配置好之后关键是读得懂日志。一条典型的MySQL慢查询日志长这样# Time: 2025-01-15T10:23:45.123456Z # UserHost: app_user[app_user] [10.0.0.12] Id: 88231 # Query_time: 3.201345 Lock_time: 0.000217 Rows_sent: 20 Rows_examined: 2847120 SET timestamp1736922225; SELECT id, order_no, user_id, amount FROM orders WHERE status PAID AND create_time 2025-01-01 00:00:00 ORDER BY create_time DESC LIMIT 20;每条日志分两部分前面几行以#开头的是头部信息后面是实际的SQL语句。Time是语句执行结束的时间戳UserHost是执行这条SQL的账号和客户端IPId是连接ID。Query_time是整条语句的总执行时间也是最核心的指标Lock_time是等待锁的时间——注意这个“锁”不仅包括行锁和表锁也包括MySQL内部一些元数据锁所以看到Lock_time比较大时不一定是业务锁竞争需要进一步确认Rows_sent是最终返回给客户端的行数Rows_examined是这条语句实际扫描的行数。Rows_examined这一项是判断优化空间最重要的信息之一。一条查询返回20行却扫描了284万行说明要么索引选择有误要么干脆就是全表扫描。如果Rows_examined和Rows_sent的比值过大比如超过1000:1基本可以断定这条SQL有非常大的优化空间。SET timestamp是这条SQL开始执行的时间戳常用来在分析时关联业务日志。3.2 使用mysqldumpslow快速统计日志文件一多逐条看是不可能的。MySQL自带一个日志统计工具叫mysqldumpslow不需要额外安装直接命令行调用mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log-s t表示按query time总耗时排序-t 10表示只显示前10条。这个工具会把结构相似、只有参数值不同的SQL自动归并比如上面那条查询会被抽象成SELECT id, order_no, user_id, amount FROM orders WHERE status N AND create_time S ORDER BY create_time DESC LIMIT N对应的统计结果里会出现两个时间指标count表示这类SQL在日志里出现了多少次Time是这一类SQL的总耗时和平均耗时Lock Time是锁等待总计Rows是扫描行数的总计和平均值。我通常先按总耗时排序看一遍把最耗时的SQL挑出来再按扫描行数排序看一遍把扫描行数异常大的SQL挑出来这两个维度基本能覆盖90%的重要问题。还可以加-a参数保留原始SQL中的具体值不过一般没必要归并之后的抽象语句反而更容易让你看清SQL的“模板”。3.3 pt-query-digest与performance_schema的配合如果mysqldumpslow满足不了需求可以试试Percona Toolkit里的pt-query-digest分析能力更强输出也更直观包括每个SQL模板的出现次数、总耗时、平均耗时、响应时间占比、行数比例等还支持按报告形式输出到文件里。pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt打开报告后第一件事看Profile部分它会按响应时间从高到低排列所有的SQL模板并给出每个模板的“Response time占比”。如果某一条SQL的响应时间占了总体的50%以上那优化这条SQL就是当晚唯一的任务。与performance_schema配合时我主要用它来做慢日志的补充——performance_schema里的events_statements_summary_by_digest表按SQL模板聚合了执行次数、总耗时、平均耗时、锁等待等指标即使慢查询日志没开着也能查到当前实例里的SQL负载分布。有些时候我发现某一类的总耗时很高但单条都没到1秒阈值这就要靠performance_schema来定位两者配合正好弥补慢日志“只记录超阈值语句”的盲区。4. 从日志到优化慢SQL的定位与实战改造4.1 用EXPLAIN还原慢SQL的真实执行路径拿到一条慢SQL之后第一件事不是凭经验猜为什么慢而是用EXPLAIN看它的执行计划。还是拿上面那条订单查询举例EXPLAIN SELECT id, order_no, user_id, amount FROM orders WHERE status PAID AND create_time 2025-01-01 00:00:00 ORDER BY create_time DESC LIMIT 20;执行结果里最重要的几列type表示访问类型从好到差大致是const、eq_ref、ref、range、index、ALL出现ALL意味着全表扫描key表示实际用到的索引rows是预估扫描行数Extra列里如果出现Using filesort说明排序没有走索引Using temporary说明使用了临时表。大部分慢SQL的问题通过这几列就能看出来。比如上面这条如果执行结果显示type是ALLkey是NULLrows是几百万那不用看了就是一个标准的全表扫描。如果status和create_time各有一个单列索引但type还是ALL或者只用了其中一个索引效果不好这就涉及索引选择的问题得把两个条件合成一个联合索引来优化。4.2 常见慢SQL模式与索引优化思路结合我实际处理过的慢SQL有几类模式特别常见每类都有相对固定的优化套路。第一类是无条件或低选择性条件的全表查询。比如一个订单表要查“本月所有已付款订单”如果直接在create_time上建索引那么范围查询可以走索引但如果还要加上statusPAID条件单列索引大概率只能做到“先按时间取范围再回表过滤状态”。更优的方案是建(status, create_time)联合索引让两个条件都在索引层面完成过滤回表行数大幅降低。第二类是函数包裹列导致索引失效。比如WHERE DATE(create_time) 2025-01-15这种写法MySQL没法直接用create_time上的索引因为每一行都得先运算再比较。改成create_time 2025-01-15 00:00:00 AND create_time 2025-01-16 00:00:00之后索引就能生效了这是成本最低的优化。第三类是隐式类型转换。比如user_id是varchar类型但查询写成WHERE user_id 12345MySQL会把列上所有值转成数字再比较索引失效。这种问题排查起来比较隐蔽因为它不会在慢日志里直接标注“类型转换”字样只能靠开发规范去约束或者在排查时特别留意输出中是否有警告。第四类是深度分页。ORDER BY create_time DESC LIMIT 100000, 20这种写法在数据量大时会先扫描和排序前100020行再丢掉前100000行效率极低。优化的思路是减少回表先用一个覆盖索引查询出主键或所需列再JOIN回原表取完整数据或者记住上一页最后一条记录的排序字段值做“键集分页”跳过深分页带来的无谓扫描。4.3 深分页、排序、临时表场景的优化案例我拆一个真实案例。某个运营报表页面上有一个按月汇总的查询SQL大概是SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE create_time BETWEEN 2025-01-01 AND 2025-01-31 GROUP BY user_id ORDER BY total_amount DESC LIMIT 100;慢日志显示这条SQL的Query_time是5.8秒Rows_examined是1200万。EXPLAIN之后发现orders表在create_time上有索引范围查询走的是索引但GROUP BY user_id需要用到临时表和文件排序1200万行全部塞进临时表再排序自然快不了。优化思路分两步。第一步既然create_time上有索引把索引扩展成(create_time, user_id, amount)联合索引这一步就能让查询走覆盖索引直接在索引上完成BETWEEN范围扫描不需要回表读取整行数据扫描成本大幅下降。第二步GROUP BY和ORDER BY不一样GROUP BY可以先用索引避免临时表但ORDER BY total_amount是聚合之后的结果排序索引帮不上忙只能接受filesort。优化后查询时间从5.8秒降到了0.9秒左右虽然最后的filesort还在但扫描行数从1200万降到了100万以内整体的IO开销完全不是一个量级。这个案例说明一个原则慢SQL优化不是追求“完全没有排序和临时表”而是把消耗最大的那部分去掉让成本曲线变得可接受。很多时候把扫描行数降下来排序再慢也有边际收益。5. 常见问题与排查技巧实录5.1 慢日志导致的磁盘暴涨与轮转方案很多人打开慢日志后最常遇到的事故就是第二天早上磁盘满了数据库直接不可写。原因通常是log_queries_not_using_indexes配合过低的min_examined_row_limit再加上业务里存在大量小表全表扫描导致日志文件在几个小时内写了十几G。解决方式分三个层面。第一个层面是参数层面把min_examined_row_limit调高到100或者更高用log_throttle_queries_not_using_indexes限制无索引查询的记录频率。第二个层面是文件层面MySQL本身的慢日志文件没有自动轮转能力我通常用Linux的logrotate来每日切割/var/log/mysql/mysql-slow.log { daily rotate 7 compress delaycompress missingok notifempty create 640 mysql mysql postrotate mysqladmin flush-logs endscript }这里的postrotate部分很关键mysqldump flush-logs会让MySQL重新打开日志文件如果只是把文件改名而不通知MySQLMySQL还会继续往旧文件里写磁盘照样一直被占用。最早我踩过这个坑切割完一看旧文件被句柄占着删不掉新文件一直创建失败。第三个层面是习惯层面不要把慢日志当成永久存档它只是一个诊断窗口期数据。优化完一批SQL之后重点应该转移到后续是否还会新增慢SQL上周的日志该清理就从清理掉。5.2 long_query_time设置不当导致日志失真long_query_time设得太低日志会被碎查询淹没关键慢SQL反而被淹没在大量噪音里设得太高又会让响应时间已经超标但还没到阈值的查询逃过你的视野。这里有一个实操技巧用百分比而不是绝对值来校准阈值。比如你运行一个周期后把日志按query time排序看一眼中位数和P95耗时。如果P95耗时是800毫秒那设置1秒阈值其实是合理的因为绝大多数请求都在1秒内完成只有真正异常的请求才会超1秒被记录但如果P95是2秒那超过2秒的查询才是你需要重点关注的阈值可以往2秒或以上调一点不然日志里全是“通常就这么慢”的语句没有任何区分度。另一个容易犯的错是把long_query_time设为0。MySQL文档里确实支持这个值含义是“记录所有语句”但这个行为会导致日志疯狂增长几乎没有生产环境适合这么做。会用performance_schema来分析全量SQL之前别把阈值压到这种程度。5.3 锁等待到底算不算慢查询很多新手看到日志里Lock_time比Query_time还大就断定是行锁竞争赶紧去查事务、查死锁。方向是对的但有一个细节要澄清Query_time是包含Lock_time的也就是说一条SQL的Query_time2秒其中Lock_time1.5秒它本身并不是“执行了2秒钟”而是“等了1.5秒锁执行了0.5秒”。这种情况下真正的问题不是SQL本身执行慢而是它在等待某个其他事务释放锁。排查时我会先查information_schema.innodb_trx看看当前有哪些长事务在跑然后查performance_schema.data_lock_waits找出到底是哪个事务锁住了目标行。如果锁持有者是某个长时间未提交的事务那就不是SQL优化的问题而是事务管理问题直接联系对应业务方确认是否可以提交或回滚。在这里慢日志的价值是“提醒你有个等待事件”但要继续往下挖一层才能找到真正的元凶。5.4 主从环境与容器环境下的日志管理主从架构下做优化有个容易忽略的点主库上执行很快的SQL到从库可能会变慢尤其是从库硬件规格低于主库或者从库上还有其他分析型查询在抢资源时。这时候在主库上开慢日志看不到问题必须在从库上把log_slow_slave_statements打开才能看到从库真正执行得慢的语句。排查从库延迟问题时这个参数几乎必开。容器化部署时慢日志文件一般要写到挂载卷里否则容器一重建日志就没了。但挂载卷的IO性能也会影响MySQL本身的写入所以很多云厂商的托管实例会直接把log_output固定为TABLE让你通过SQL查询慢日志。在那种环境里直接查mysql.slow_log表再导出分析也行只是要记得加条件限制不要一口气SELECT *容易被这张表的体量反噬。最后再分享一个我在实际项目里的习惯每季度做一次慢日志“体检”把这一季度出现过的Top 20慢SQL全部过一遍EXPLAIN能下推的过滤条件下推能合并的索引合并能去掉的全表扫描去掉。这个过程不需要多长时间但它能保证数据库性能不是靠运气而是有一套持续运转的复盘机制。慢查询日志不是用来“应付大促”的它是你日常运维里最忠实的哨兵。