★ FEATURED ARTICLE 做后端开发和数据库维护这几年比“系统又慢”更让人头大的问题大概就是“系统慢是因为哪条SQL”。慢SQL这东西平时不声不响一旦流量上来或者数据量涨到某个临界点就会突然变成整个系统的致命点——连接池被打满、接口超时、雪崩式故障往往都是从一条不起眼的慢查询开始的。这篇文章我想把慢SQL从定位到优化的完整思路捋一遍包括慢查询日志怎么配、执行计划怎么看、索引怎么建、SQL怎么改再附上几个我在真实系统里处理过的场景和踩坑记录。不管你是刚接触数据库优化的开发还是已经在负责线上系统稳定性的运维同学这套方法应该都能直接拿去用。1. 慢SQL的危害一条查询拖垮整个服务先说个我实际经历过的场景。某个业务系统平时接口响应都在100ms以内某天下午突然报警订单列表接口大面积超时紧接着整个应用的服务都卡住了。当时排查了很久最后定位到是一条统计SQL出了问题因为业务增长订单表数据量跨过了某个量级全表扫描耗时从几百毫秒涨到了八九秒把连接池里的连接全部占住了。后面所有请求拿不到连接只能排队等超时整个应用就跟着“假死”。这就是慢SQL最可怕的地方它的破坏力不是“单次查询慢一点”而是会形成连锁反应。1.1 连接被长时间占用是最大隐患应用访问数据库是通过数据库连接池来管理的池子里的连接数量是有限的。正常情况下一条查询执行50ms连接很快就能放回池子里供下一个请求使用。但如果某条SQL要执行5秒意味着这条连接在这5秒里没法服务其他请求。假设连接池有50个连接只需要10条这样的慢SQL并发执行池子就空了其他正常请求全部排队而排队的请求又会占用应用服务器的线程线程池也被拖垮雪崩就是这么来的。1.2 慢SQL通常不是“突然”出现的很多慢SQL一开始并不慢。数据量小的时候全表扫描也就几十毫秒没有任何问题。但随着业务增长表数据量从几万涨到几百万、几千万执行计划选择的方式如果还是全表扫描耗时就从毫秒级跳到了秒级。所以我一直有个观点慢SQL不是优化一次就结束了它是一种需要持续监控、持续治理的系统性问题尤其要关注数据量增长带来的“非线性恶化”——指数级上涨的数据量带来的是指数级上涨的查询耗时。1.3 换一个视角看慢SQL的代价慢SQL直接影响的指标是接口响应时间进而影响用户体验、订单转化率、报表生成时间。对内部系统来说慢SQL会导致操作卡顿、任务超时对外部系统来说就是直接的收入损失和用户流失。这也是为什么把慢SQL治理当回事的团队通常都有专门的数据库规范而不是等出了故障才去救火。提示判断一条SQL是不是慢SQL不能只看绝对耗时还要结合业务并发量。一条只在凌晨跑的批处理SQL耗10秒也许可以接受但一条每秒钟被调用几十次的接口SQL耗1秒就是灾难。2. 定位慢SQL先把“嫌疑人”抓出来优化慢SQL的第一步是把慢SQL从海量请求里捞出来。我一般按三个层次来做数据库慢查询日志、监控系统、执行计划分析。2.1 开启慢查询日志大多数关系型数据库都提供慢查询日志。以使用最广泛的MySQL为例可以通过配置开启slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1其中long_query_time表示超过多少秒的查询会被记录。我建议线上环境先设成1秒不要设得太小否则日志量太大会影响磁盘IO等治理得差不多之后再逐步收紧到0.5秒甚至0.2秒。log_queries_not_using_indexes这个参数值得开它能记录那些没走索引的查询哪怕执行时间不长也很有参考价值——因为一旦数据量涨起来这些SQL就会变成真正的慢SQL。日志开启后可以用数据库自带的汇总工具来归类比如mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log-s at表示按平均查询时间排序-t 10表示取前10条。这个工具会把结构相似、只有参数不同的SQL归并成一条方便快速找到“最应该优先处理”的那批SQL。2.2 用监控和链路追踪辅助定位慢查询日志适合事后分析但在故障现场很多团队还会配合监控平台和链路追踪组件比如把每条SQL的执行耗时、调用次数、当前连接数做成指标看板慢SQL一出现就能直接看到它来自哪个接口、哪个调用链。这类工具我建议在系统规模上来之后就尽早引入不要等靠日志人工翻——人工效率太低了而且故障时根本来不及。2.3 用EXPLAIN读懂执行计划捞到具体SQL之后下一步就是看它到底是怎么执行的。MySQL里用EXPLAIN加在SQL前面即可EXPLAIN SELECT id, order_no, user_id, amount FROM order_info WHERE user_id 12345 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 10;执行计划里几个关键字段我按重要程度排个序字段关注点通俗解释type是否全表扫描ALL表示从头翻到尾range/ref表示走索引范围/等值查询const表示主键或唯一键命中key命中了哪个索引如果是NULL说明没有可用索引rows预估扫描行数这个数字越大查询通常越慢Extra是否有额外开销Using filesort是文件排序Using temporary是临时表都是要重点警惕的信号这里重点提醒一句EXPLAIN给出的rows是估算值不是精确值但用来判断量级和趋势完全够用。我自己习惯先看type和Extra这两个字段能直接暴露90%的问题typeALL基本意味着全表扫描Extra里有Using filesort基本意味着排序没走索引。2.4 按总耗时排序而不是只看单次耗时在分析慢查询日志时我发现很多团队容易犯一个错误只看耗时排名不看调用频率。一条耗时8秒但一天只跑一次的SQL和一条耗时500ms但每秒跑20次的SQL后者对系统的伤害往往更大。所以我一般会把“总耗时 单次耗时 × 调用次数”作为排序依据优先优化总耗时高、且调用频繁的SQL。这也是为什么先看日志汇总工具、而不是一条条翻原始日志的原因。3. 对症下药索引、SQL写法、表结构三大方向拿到慢SQL并看懂执行计划后就到了动手优化的环节。我把这些年处理过的慢SQL大致归成三类索引问题、SQL写法问题、表结构与数据分布问题。3.1 索引优化从无索引到好索引先说最常见的场景——该走索引却没走索引。典型场景是筛选条件里的列没有索引。比如订单表经常按user_id查询但表里只建了主键那查询就只能全表扫。这时直接加个单列索引就能解决ALTER TABLE order_info ADD INDEX idx_user_id (user_id);但更常见的坑是“有索引但用不上”。我整理几个高频失效场景对索引列做函数运算。比如WHERE DATE(create_time) 2024-01-01create_time即使有索引也会失效因为数据库要先对每行算一遍DATE才能比较。正确写法是范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。隐式类型转换。比如把数字列user_id和字符串比较WHERE user_id 123某些时候会导致索引失效。建议写SQL时保证类型一致从源头避免。前模糊匹配。LIKE %关键词%因为前后都有通配符索引无法定位只能扫全表。如果业务确实需要全文搜索建议引入专门的搜索引擎来做而不是在数据库里硬扛。OR连接的条件里有一个没索引。比如WHERE user_id 1 OR status 2如果status没索引优化器可能整体放弃索引。可以改成UNION ALL拆分或者给两个列都建上合适的索引。复合索引不符合最左前缀原则。比如创建了复合索引(a, b, c)查询条件是WHERE b 1用不上这个索引WHERE a 1 AND b 1可以用上。复合索引里列的顺序非常讲究建议把等值查询的列放前面范围查询的列放后面。注意加索引不是万能的索引也是要占空间、要维护的。每次写操作都要同步更新索引索引太多反而会让写入变慢。线上加索引建议用在线DDL方式避免长时间锁表影响业务。3.2 SQL写法优化用更小的代价拿到同样的结果有些慢SQL执行计划看起来没问题但写法本身有优化空间。我说几个高频例子。第一避免SELECT *。只查询业务需要的列。这不只是省流量更重要的是“覆盖索引”的机会——如果查询的所有列都包含在某个索引里数据库可以直接从索引里取数连回表都省了。回表就是找到索引后再去原始数据行里捞数据减少回表次数对性能影响很大。第二深分页问题。像LIMIT 100000, 20这样的写法数据库需要先找出前100020条数据再把前100000条丢弃前面的扫描全是无用功。数据量越大越慢。常见优化方式是“子查询分页”或“游标分页”-- 先把主键捞出来再关联取数据 SELECT * FROM order_info WHERE id IN ( SELECT id FROM order_info WHERE create_time 2024-01-01 ORDER BY create_time DESC LIMIT 100000, 20 );不过IN子查询在某些数据库版本里也可能有执行计划不稳定的情况更稳妥的是利用上一页最后一条记录的IDSELECT * FROM order_info WHERE create_time 上一页最小create_time ORDER BY create_time DESC LIMIT 20;这种“游标翻页”方式在数据量大的场景下比LIMIT深分页快几个数量级但前提是排序字段唯一且稳定否则会漏数据。第三能不排序就不排序排序尽量用索引。ORDER BY的字段如果包含在索引里数据库可以直接按索引顺序取数不需要额外文件排序。这也是为什么复合索引设计要尽量覆盖“WHERE ORDER BY”的字段组合。3.3 表结构与数据分布优化到根子上有些慢SQL无论怎么建索引、怎么改写法都收效有限这时候要回头看表结构和数据分布。最常见的是表里垃圾数据太多。比如有大量逻辑删除的记录、异常写入的脏数据导致表行数虚高。我遇到过一张订单表有效数据只有几百万行但因为历史原因存了几千万条无效记录导致所有查询都变慢。清理数据后性能直接回升。另一个问题是统计信息不准确。数据库优化器是靠统计信息选执行计划的如果统计信息过期它可能选错索引或者误判扫描行数。定期执行ANALYZE TABLE刷新统计信息虽然是小操作但能避免很多“莫名其妙”的慢SQL。还有一种情况是业务数据天然倾斜。比如某个热门商家的订单量占全表的80%按商家查询时即便走了索引返回的数据量依然巨大。这种要从业务层面考虑是不是要做汇总表、引入缓存或者调整分页策略而不是指望数据库层面能魔法般解决。4. 进阶场景锁等待、长事务与连接池恶性循环前面聊的偏“常规武器”但在真实系统里还有几类更难缠的慢SQL它们的耗时往往不在查询本身而在数据库的并发机制和资源竞争上。4.1 锁等待查询不慢但就是出不来结果你有没有遇到过这种情况一条SQL单独执行只要10ms但在线上就是超时。这种大概率是锁等待——它被别的事务堵住了。事务锁表或锁行期间其他要修改同一行或同一范围数据的会话只能等待等待超过innodb_lock_wait_timeout默认约50秒就会报锁超时错误。这类问题排查有个标准动作执行SHOW ENGINE INNODB STATUS查看当前事务和锁信息或者查performance_schema下的锁等待表看谁在等谁。这类慢SQL的解法不是改SQL而是优化业务逻辑缩短事务时间、减少锁的粒度、避免在事务里做慢查询和外部接口调用。特别要提醒一点很多人习惯在一个大事务里先查一堆数据再逐条更新这个习惯很容易造成长事务长事务不仅锁住大量行还会让数据库的undo日志膨胀直接影响整体性能。4.2 长事务与空闲连接长事务指的是长时间不提交的事务。它不一定一直在跑可能代码里开了事务处理完业务却忘了提交或者等待外部接口返回。这种事务的危害是它持有的锁不释放日志不清理还会让连接池里的连接被白白占用。排查长事务MySQL里可以查information_schema.innodb_trx看事务开始时间早不早、锁定行数多不多。治理方式上一方面靠代码规范事务尽量短小另一方面靠监控告警发现超过N秒未提交的事务立即通知。我见过因为一个事务里调了外部接口、接口超时30秒导致整张表更新被堵死的案例事后想想真的很冤。4.3 连接池的放大效应慢SQL和连接池有个互相放大的恶性循环慢SQL占用连接连接不足导致请求排队排队请求占用线程线程耗尽导致健康检查失败健康检查失败又触发重启或熔断。处理这种故障时我建议先把“受害”流量挡掉或降级优先保住核心链路而不是急着去优化那条慢SQL——先止损再根治。另外连接池的参数最小连接数、最大连接数、最大等待时间等也需要结合慢SQL场景调整但参数调整只是缓解不能根治慢SQL核心还是得把慢SQL干掉否则池子调多大都不够用。4.4 慢SQL排查速查表把日常遇到的现象和排查方向整理成一张表遇到问题可以对着找现象可能原因排查动作SQL单独执行快线上却超时锁等待查innodb_trx和锁等待表平时快数据量增长后突然变慢执行计划恶化/全表扫描EXPLAIN看type和rows变化同样的SQL时快时慢缓存命中率波动/统计信息过期看buffer pool命中率刷新统计信息单条SQL不慢整体吞吐量下降长事务占用连接检查事务执行时间和提交状态深分页接口越来越慢LIMIT偏移量过大改为游标分页记录上一页边界5. 一个完整案例从日志到优化到验证理论说了不少我挑一个印象比较深的真实案例把这套方法的完整流程走一遍。为保护业务信息字段做了脱敏处理。5.1 案发现场某后台系统的“订单汇总列表”接口用户按时间范围查询指定状态下的订单按创建时间倒序分页。随着业务数据量增长这个接口逐渐从几百毫秒恶化到3秒以上用户操作明显卡顿。慢查询日志里这条SQL平均耗时约4.2秒每天执行几万次。5.2 定位分析把SQL捞出来加上EXPLAIN看执行计划发现几个关键问题订单表走的是typeALL全表扫描预估扫描行数约200万行Extra里还有Using filesort说明排序也是额外做的。再看SQL本身SELECT把所有字段都查出来了WHERE里只用了create_time范围查询和status等值查询但这两列上没有任何索引。5.3 优化方案第一步建复合索引。因为查询条件里status是等值查询create_time是范围查询按照“等值在前、范围在后”的原则建了复合索引ALTER TABLE order_info ADD INDEX idx_status_create_time (status, create_time);第二步改写SQL。把SELECT *改成只查需要的列排序字段create_time已包含在索引里ORDER BY create_time DESC可以直接走索引不再需要文件排序分页参数也换成了基于游标的翻页方式。5.4 优化效果改造后同一个接口的SQL执行时间从4.2秒降到约20msEXPLAIN里type从ALL变成了rangerows从200万级降到几千级Extra里的Using filesort也消失了。更关键的是这条SQL不再长时间霸占连接接口整体响应时间明显回落也没有再出现连接池告警。这个案例说明一个道理慢SQL优化往往不是单一动作而是“索引 写法 分页策略”的组合拳。先看懂执行计划再针对每一项开销逐个消除效果是可以被精确量化的。6. 慢SQL治理要靠体系监控、评审、定期巡检最后想聊点“道”层面的东西。很多团队处理慢SQL都是一次性救火这个月优化完下个月新需求又写出新的慢SQL。所以除了掌握具体技术更要建立一套能持续运转的治理机制。6.1 上线前SQL评审在代码评审环节把SQL纳入检查范围是最便宜、最有效的防线。我总结了一个简单的评审清单是否只查询需要的字段有没有SELECT *WHERE条件的列是否有索引是否符合索引最左前缀原则是否有对索引列的函数运算、隐式类型转换JOIN的字段类型和字符集是否一致是否涉及深分页、大范围IN、多表关联的笛卡尔积风险是否在循环里执行SQL这些点不需要评审人多懂数据库原理按清单逐条过一遍就能拦下大部分问题SQL。6.2 慢SQL监控告警与定时汇总慢查询日志不能开着不管。建议把慢日志定时采集汇总按“总耗时单次耗时×调用次数”排序每周产出一份TOP慢SQL清单分发给对应的负责人去优化。同时设置告警单条SQL超过阈值或者慢SQL数量在短时间内激增立即通知到人。我见过最有效的做法是把“慢SQL数量”作为发布回滚的参考指标之一——一旦发布后慢SQL显著变多说明新代码有问题直接回滚。6.3 定期巡检与容量规划数据库的索引和表结构也需要“年检”。比如检查有没有冗余索引多个索引前缀重复、有没有长期不用的索引、表数据量增长趋势如何、是否需要归档冷数据、是否需要考虑分表。这些事情看起来不起眼但能避免很多未来必然会发生的慢SQL。我个人的经验是把数据库巡检做成固定节奏比如每季度做一次每个业务核心表都要过一遍数据量和索引使用情况慢SQL治理才能真正从“救火”变成“防火”。最后分享一个小技巧优化慢SQL之前一定要先记录优化前的耗时、执行计划、扫描行数的基线数据改完之后逐项对比。有了基线你才能知道自己改的每一步到底有没有效果哪些改动是白费功夫。慢SQL优化这件事最大的成就感不是把SQL改快了而是你完全清楚它为什么快。
阅读完成 · 觉得有帮助？
