数据库慢SQL优化实战：10个典型案例深度剖析

大部分后端开发第一次背上线上事故往往就栽在一条慢SQL上。我印象最深的一次业务高峰期数据库CPU直接被打满最后定位到一条跑了将近12秒的明细查询当时整张订单表几百万数据就因为它没走索引把库拖到报警。从那次以后我开始把所有经手过的慢SQL案例收集起来今天挑10个最典型的出来聊聊。这篇文章不打算只给你答案我会把每条SQL“慢在哪→怎么判断→怎么改→改完什么效果”完整还原一遍。案例覆盖了索引失效、深分页、大表统计、JOIN隐式转换、排序、锁等待、长事务这几条主线。如果你是后端开发、DBA或者负责系统运维这篇文章可以当一份排错手册来用如果你刚接触数据库优化我也会把原理尽量讲透不让你止步于“抄SQL”。1. 慢SQL排查的起点先把它从日志和监控里找出来1.1 慢查询日志怎么开、阈值怎么设很多人优化慢SQL的第一步就是EXPLAIN但我建议先确认一件事你手上到底有没有慢SQL的完整清单没有日志和监控优化就是大海捞针。以MySQL为例慢查询日志默认是关闭的需要手动打开。常见的配置是这样的[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time 1表示超过1秒的SQL会被记录。注意这个值是秒支持小数比如0.5就是500毫秒。log_queries_not_using_indexes建议打开它会把没走索引的SQL也记下来这类SQL即使耗时不高也可能是隐藏风险。阈值设置上有讲究。设太小比如0.1秒日志会被高频查询刷屏设太大比如10秒又容易漏掉那些“平时1.5秒、高峰期变5秒”的问题SQL。我一般初期会设1秒压测阶段再临时降到100毫秒集中抓一轮低效SQL。如果你用的是高斯数据库GaussDB也有对应的慢SQL记录能力可以通过系统视图或管理平台查看历史执行信息排查思路是一样的。具体视图名和查询方式不同版本有差异以官方文档为准就好。1.2 拿到EXPLAIN之后只盯这几个字段拿到一条慢SQL后第一时间就该看它的执行计划。MySQL里是EXPLAIN高斯数据库里也有类似命令展示格式略有区别但核心字段是相通的。EXPLAIN SELECT * FROM t_order WHERE user_id 10086;输出里我最关心的永远是四样东西type访问类型从好到差大致是system const eq_ref ref range index ALL。看到ALL基本等于全表扫描这是慢SQL的重灾区看到index也别高兴太早它是全索引扫描索引被遍历了一遍同样可能慢。key实际用到的索引。如果是NULL说明索引完全没用上。rows优化器预估需要扫描的行数。这个数字和表的数据量越接近越说明查询在硬扫。Extra如果出现Using filesort、Using temporary、Using join buffer基本意味着排序、去重或关联没法走索引需要重点处理。我自己的判断习惯是type range基本可以接受ref或eq_ref更优一旦看到ALL先别急着写优化方案先把“为什么没走索引”找出来再动手改。1.3 先排除数据库抖动再谈SQL问题有一类慢SQL非常坑执行计划完全正常索引也走了但线上就是慢。这种情况通常是库本身出了问题而不是SQL的问题。我见过太多人一上来就加索引结果问题根本不在索引上。比较常见的非SQL因素有这么几类数据库服务器CPU、IO打满所有查询都在排队连接池被打满查询在等待获取连接有大事务或长事务在运行触发了锁等待磁盘性能抖动或者跑了大量的全表备份任务。所以我会先看监控面板确认是不是只有这一条SQL慢还是整体都在慢。如果是整体慢优先排查数据库负载和锁等待只有单条慢才进入执行计划分析。2. 索引失效类四个“以为走了索引其实全表扫描”的经典案例2.1 案例一WHERE条件里对索引列套函数有一个订单表大约500万行created_at字段上有索引。业务方要查某一天的所有订单第一版SQL是这样写的SELECT * FROM t_order WHERE DATE(created_at) 2024-05-20;EXPLAIN结果出来type是ALLrows直接是几百万等于全表扫。索引明明在为什么没用上因为B树索引里存的是字段的原始值并且按原始值排好序。DATE(created_at)相当于先把每一行的created_at做了一次函数加工加工后的结果和索引里的有序值完全对不上优化器没办法直接定位只能老老实实全表扫描。改法非常简单把函数放在条件的一侧改成范围查询SELECT * FROM t_order WHERE created_at 2024-05-20 00:00:00 AND created_at 2024-05-21 00:00:00;改完再看执行计划type从ALL变成了range扫描行数降到了几万查询耗时从2.1秒降到80毫秒。这个案例是索引失效里最典型的一种在索引列上做函数运算、四则运算、格式化都会让索引失效。如果业务确实必须用函数再考虑建函数索引或者冗余一个加工后的字段但要先评估维护成本。2.2 案例二隐式类型转换让索引“静默失效”用户表t_user的user_id字段是varchar(20)上面有索引。业务代码在传参时框架把参数转成了数字类型于是SQL变成了这样SELECT * FROM t_user WHERE user_id 10086;EXPLAIN一看type是ALL索引没走。当时第一反应是索引坏了检查了一圈才发现是隐式类型转换。user_id是字符串查询条件是数字优化器会把两边统一成同类型再比较。在这个场景下相当于对user_id列做了类型转换和案例一里给列套函数是同一个道理——列上没有可用的有序值了索引自然就失效了。解决方案有两种。首选是改应用层传参让user_id以字符串形式传入SELECT * FROM t_user WHERE user_id 10086;如果问题出在框架层统一转换不好控制传参类型那就直接把字段类型从varchar改成bigint一劳永逸。不过改表之前一定要先检查历史数据如果表里存在非数字的脏数据类型转换会直接报错需要先清洗数据。这个案例的坑在于SQL看起来没有任何问题字段和条件都是“同一个值”EXPLAIN不细看都发现不了原因。排查时如果看到possible_keys里有索引但实际没走优先怀疑隐式转换。2.3 案例三LIKE %关键词% 让索引因为前导通配符失效商品表t_product名称字段做了普通索引业务想实现商品名模糊搜索SQL长这样SELECT * FROM t_product WHERE name LIKE %山茶花%;这个查询的执行计划type是ALL。原因也很直白B树索引是有序排列的它可以高效定位“山茶花”开头的记录但没法确定%山茶花%中前面那部分不确定的值无法通过索引直接跳转到匹配区域只能全表扫。如果业务只要求前缀匹配直接改成左前缀就能走索引SELECT * FROM t_product WHERE name LIKE 山茶花%;但现实是大部分业务做的都是真正的全文模糊搜索左右都有通配符。那就要认清一个事实数据库的B树索引不擅长这件事。数据量小的时候可以硬扛数据量到了百万级全表扫的代价会越来越大。我遇到过的一个系统商品搜索全量用LIKE %关键词%上线初期数据量不大还能忍后面数据涨到几百万数据库天天报警最后只能把搜索能力迁移到专门的搜索引擎上。从成本角度看如果数据量不大比如几千行保留全表扫描也未必是错但要在SQL注释里标明这是有意为之避免后来的人看到LIKE %就想帮你“优化”掉。2.4 案例四联合索引顺序没对齐索引用不上订单表上有两个高频查询条件status和created_at。运维同学建了一个联合索引但顺序建反了-- 错误的索引顺序 CREATE INDEX idx_created_status ON t_order(created_at, status);业务查询是按订单状态筛选并按创建时间排序SELECT id, order_no, status FROM t_order WHERE status 1 ORDER BY created_at DESC LIMIT 20;执行计划里type是ALL或最多只能做全索引扫描rows很大。为什么联合索引遵循最左前缀原则查询条件里没有带created_at索引就没法从第一个字段开始定位自然用不上。即使优化器有常数传递和条件重排的能力也没法把一个SQL里根本不出现的列“变”出来。正确的做法是让联合索引的第一个字段匹配等值查询条件CREATE INDEX idx_status_created ON t_order(status, created_at);这样status 1能直接定位到目标范围created_at又天然有序ORDER BY也能顺带走索引不用额外排序。这个改动之后同样的SQL从全表扫描变成索引范围扫描耗时从1.5秒降到30毫秒。设计联合索引的顺序时有一个简单原则先考虑等值条件再考虑排序字段最后考虑范围条件。等值条件放最左才能最大化索引的裁剪能力。3. 查询逻辑类大表查询里的四个隐藏敌人3.1 案例五深分页 LIMIT 100000, 20 越翻越慢后台管理系统的订单列表分页功能上线后一切正常但随着订单量增长翻到第100页以后就越来越慢。SQL长这样SELECT id, order_no, amount, created_at FROM t_order ORDER BY created_at DESC LIMIT 100000, 20;数据量300万这条SQL跑到100页时耗时2.8秒。原因很简单LIMIT 100000, 20的本质是先扫描出100020行然后丢掉前面的100000行只把最后20行返回给客户端。前面的100000行虽然不要但数据库已经实打实地扫描了而且因为查询是SELECT *还需要对每一行做回表把完整行数据从磁盘读出来代价就更高了。改法有两种第一种是延迟关联。先让子查询只查主键id跳过回表再通过主键关联回原表取完整字段SELECT o.id, o.order_no, o.amount, o.created_at FROM t_order o INNER JOIN ( SELECT id FROM t_order ORDER BY created_at DESC LIMIT 100000, 20 ) t ON o.id t.id;这个写法里子查询虽然还是要扫描100020个索引项但它只回表取20条记录的完整数据IO开销大大降低。实测同样的数据量优化后从2.8秒降到了0.6秒。第二种更彻底是改成游标分页。记住上一页最后一条记录的created_at下一页用条件查询SELECT id, order_no, amount, created_at FROM t_order WHERE created_at 2024-05-01 10:00:00 ORDER BY created_at DESC LIMIT 20;这种方式无论翻到多深扫描量都恒定为20行左右。代价是无法直接跳页只适合“加载更多”这类业务场景。如果是面向运营后台那种必须精确跳页的系统延迟关联是更稳妥的选择。3.2 案例六大表 COUNT(*) 的“实时统计”陷阱运营后台要做实时订单总数统计于是写了这么一条SQLSELECT COUNT(*) FROM t_order;订单表800万行这条SQL跑了3秒多。很多人第一反应是换COUNT(1)是不是更快其实不是COUNT(*)和COUNT(1)在MySQL里没有本质区别优化器都会选最小的二级索引来统计。真正的瓶颈在于InnoDB的MVCC机制为了支持事务隔离InnoDB不会像MyISAM那样保存一个精确的行数每次COUNT(*)都必须实时扫描索引并统计行数。哪怕走的是最小的二级索引也要把几百万个索引项过一遍。这个问题的解法要从业务需求出发。如果只是后台展示一个“总订单数”没必要实时精确统计有几个替代方案直接查information_schema.tables.table_rows拿到的是估算值秒回适合展示类场景建一张统计表在订单创建、状态变更时同步更新计数适合需要精确数字的场景用定时任务定期从订单表汇总把统计结果缓存到Redis适合实时性要求不高的报表。我当时给运营后台统计任务做的是“每日凌晨汇总 Redis缓存”把原来3秒的实时统计接口降到了50毫秒。但这个方案要注意缓存一致性问题订单状态有变化时要及时刷新或者容忍一定的延迟提前和业务方确认清楚。3.3 案例七JOIN 关联字段字符集不一致导致隐式转换订单表t_order和用户表t_user关联查询SQL很简单SELECT * FROM t_order o INNER JOIN t_user u ON o.user_id u.user_id WHERE o.status 1;执行计划里t_user表出现了Using join buffer (Block Nested Loop)而且关联字段明明有索引却没走。排查的时候发现两张表的user_id字段类型不同t_order.user_id是bigintt_user.user_id是varchar(20)。关联时数据库必须隐式转换转换后字段值没法直接匹配索引里的有序值所以驱动表扫描出的每一行都要去被驱动表里做全表匹配耗时从0.3秒涨到4秒。还有另一种更隐性的情况字段类型一样但字符集不一样。一张表是utf8mb4另一张是utf8也会产生隐式转换同样会导致索引失效。解决办法是统一两表的字段类型和字符集这是治本的方式。实在改不了表结构可以考虑在关联字段上加冗余列通过触发器或同步任务保证数据一致。排查时用SHOW FULL COLUMNS查看字段的Type和Collation对比一下就能发现问题。这个案例的教训是建表规范和评审非常重要。一开始就规定好所有表的公共字段用户ID、订单ID等必须类型一致、字符集一致能避免后续大量类似的问题。3.4 案例八ORDER BY 没走索引Extra 出现 Using filesort日志表t_log数据量200万查询是筛选某个服务ID按时间倒序取最近10条SELECT * FROM t_log WHERE server_id 1 ORDER BY created_at DESC LIMIT 10;表上只有两个单列索引idx_server_id和idx_created_at。执行计划里能看到走了idx_server_id但Extra列出现了Using filesort。Using filesort意味着查询虽然用索引定位到了 server_id1 的所有记录但created_at的排序需要额外做。如果 server_id1 有几十万条日志这几十万条就会在内存或磁盘临时文件里排一次序再取前10条代价很高。解决办法是建一个复合索引让过滤和排序同时走索引CREATE INDEX idx_server_created ON t_log(server_id, created_at DESC);这样server_id定位到目标集合created_at天然按顺序排列排序不需要再额外做。执行计划里Using filesort消失了查询耗时从1.2秒降到20毫秒。需要注意的是索引顺序server_id放前面做等值过滤created_at放后面做排序。如果反了等值过滤和排序就没办法同时命中索引。MySQL 8.0 支持降序索引建索引时可以显式写DESC避免反向扫描在低版本上反向扫描也可以顶一下但性能会略差一些。4. 更新与并发类慢SQL不只藏在SELECT里4.1 案例九一条 UPDATE 把 SELECT 拖到超时库存表t_stock在秒杀场景下大量并发请求都在执行库存扣减UPDATE t_stock SET stock stock - 1 WHERE product_id 100;结果数据库开始大量出现慢查询但慢的不只是UPDATE连普通的SELECT都变慢了。当时用SHOW PROCESSLIST查看发现大量线程处于Waiting for lock状态。这个问题的本质是行锁竞争。product_id上虽然有索引但所有并发请求都在更新同一行数据行锁只能一个个排队获取。除了并发本身过高之外还有一个容易被忽略的细节如果product_id没有索引UPDATE会全表扫描锁的范围会从一行放大到多行甚至全表问题会更严重。优化方向上先确认UPDATE条件有合适的索引让锁粒度精确到行。再说并发控制如果单行热点太严重可以考虑库存扣减异步化比如把扣减请求放到队列里串行处理或者在Redis里做预扣再定期把数据同步回数据库数据库的压力会小很多。还有一点事务里不要持有锁再去请求其他锁否则很容易死锁。批量扣减时要把多个UPDATE按相同顺序排列避免两个事务互相等待。这个案例里慢SQL只是表象真正的瓶颈是并发模型。所以排查慢SQL时不能只盯着执行计划还要看数据库当前的会话状态和锁等待情况。4.2 案例十长事务让所有查询集体变慢有一个场景特别有迷惑性数据库整体变慢了几乎所有查询的响应时间都翻了好几倍但单独看每条SQL的执行计划都正常。最典型的幕后黑手是长事务。我遇到过一个后台任务一次性DELETE某张表半年的历史数据几十万行一个事务从头到尾跑完才提交。这个事务运行期间SQL慢日志里全是各种“无辜”的查询。长事务的影响有三个层面第一它持有旧版本数据InnoDB的MVCC版本链会被拉得很长其他查询在判断行可见性时要做更多工作第二大批量DELETE会在行上留下锁其他事务访问同一行只能等待第三事务运行期间undo日志会持续膨胀磁盘IO和purge线程压力都会增加。排查方法是在MySQL里查活跃事务SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;重点看有没有长时间未提交的事务。在处理大规模清理任务时要分批提交比如每次处理1000条提交后短暂停顿再继续。事务里也绝对不要调用外部接口或RPC这种“长事务慢接口”的组合最容易拖垮数据库。所以当整个系统出现“原因不明”的集体变慢时先别急着优化SQL去查一下有没有长事务在幕后捣乱。5. 十个案例跑完后我觉得最值得养成的几个习惯5.1 每次优化都先记录基线优化慢SQL最忌讳没有对比基准。我现在每次改动前都会记录三条数据优化前的执行计划类型、实际耗时、扫描行数改完后在同环境、同数据量下再测一遍。我常用的记录格式很简单SQL优化前type优化前耗时扫描行数优化方式优化后type优化后耗时案例一ALL2.1s500万函数改为范围查询range80ms案例四ALL1.5s300万重建联合索引顺序ref30ms没有基线的优化都是在碰运气。有了这张表你才能知道哪些优化手段真正有效哪些只是自我感动。5.2 加索引不是银弹从上面的案例能看出来很多慢SQL的根源是写法问题、类型问题、业务设计问题而不是“缺一个索引”。索引带来的收益很直接代价也很直接写入变慢、存储空间增大、维护成本上升。一条SQL频繁用函数、左模糊或深分页问题大概率出在设计上强行加索引只能缓解表面症状。我一般会先问自己三个问题这个SQL能不能改写这个扫描能不能避免这个结果集能不能缩小都解决不了才考虑加索引。5.3 慢SQL治理是团队工程单点修一条SQL不难难的是防止新的慢SQL不断冒出来。我在团队里推行过几件事效果还不错要求任何SQL变更上线前必须附带EXPLAIN结果执行计划里出现ALL和Using filesort的必须说明原因把慢查询日志接入监控告警超过阈值触发通知而不是等问题被用户发现每次压测都顺带看一组慢SQL TOP10提前暴露高峰期才会出现的问题。这些习惯比掌握多少优化技巧更重要。慢SQL不会消失但你可以让它在被用户感知之前就被发现。5.4 高斯数据库中需要注意的执行计划差异最后单独说一下高斯数据库GaussDB落地这些思路时要注意的地方。GaussDB兼容MySQL的很多语法但在慢SQL观测手段上有所不同它不像MySQL那样有集中式slow_query_log文件更多是通过系统视图和运维平台查看历史执行信息比如通过gs_wlm_session_info、dbe_perf.statement_history等视图查询耗时、扫描行数、执行计划等。EXPLAIN和EXPLAIN ANALYZE的展示格式、统计信息也跟MySQL不完全一样。这意味着你在MySQL上积累的优化思路和判断标准可以平移过去但不能完全照搬。比如MySQL的优化器在某些场景下会自动做半连接优化GaussDB的行为可能不同一条SQL在MySQL里能走索引到了GaussDB里可能因为统计信息、分布键设计不同而走了另一条路。遇到慢SQL时还是要老老实实打开真实执行计划结合实际耗时分布来判断瓶颈在哪。这几个差异点是团队从MySQL迁移到GaussDB后踩过不少坑才总结出来的。工具在变但排查思路是一样的先确认日志和监控再看执行计划然后排除锁和长事务最后才动手改SQL或加索引。顺序对了大部分问题都能在半小时内定位到根因。