慢SQL优化10大实战案例：索引失效与深分页问题全解析

这个选题我挺有感触的。干后端这些年最常被拉去救火的场景之一就是线上某个接口突然变慢了一查慢查询日志全是某几条 SQL 在拖后腿。慢 SQL 这个问题说难不难但确实琐碎——同样的错误换个表结构、换个查询条件就换一副面孔光背优化原则不够还是得靠案例喂经验。这篇就用 10 个我实际排查过的案例场景把慢 SQL 优化这件事从头到尾捋一遍。覆盖索引失效、排序分组、深分页、多表关联、优化器误判这些高频问题每个案例都有问题现象、排查过程和最终的优化方案。适合正在处理线上性能问题、或者想系统补齐 SQL 优化经验的后端和 DBA 朋友。1. 慢SQL排查前的准备先把工具和基线意识装进兜里1.1 开慢查询日志别再用眼睛抓慢SQL排查慢 SQL 的第一步永远是先确认慢查询日志开着。你说你靠经验猜或者翻应用日志一条条找那都是事倍功半。MySQL 里把红打开-- 查看当前状态 SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time;如果没开找 DBA 在配置里加上这几行需要说明修改立即生效但不跨重启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 才记日志。我见过有些团队图省事设成 10 秒结果真正有价值的 200ms 语句全都漏掉了。对于绝大多数互联网业务建议从 1 秒起步收集一段时间后再根据实际情况调整。日志打开之后别直接打开文件硬翻几百 MB 的慢日志人眼根本看不过来。优先用mysqldumpslow聚合或者工具分析# 按执行次数排序 mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # 或按总耗时排序 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 或使用更专业的分析工具做指纹聚合 # pt-query-digest /var/log/mysql/slow.log慢日志的每条记录都包含执行时间、锁等待时间、返回行数、扫描行数等关键信息。拿到日志后别急着逐条优化先把耗时最高的前十个 SQL 指纹聚合出来你会发现有些语句重复出现了几百次单次虽慢但总量不大有些语句单条执行次数少但每次都两秒以上。两类问题的优化优先级完全不同类型特点优化策略高频慢SQL扫描行多、单次几十ms但调用频繁尽量避免重复执行考虑缓存或减少查询次数低频但极慢单条秒级甚至几十秒索引、改写 SQL、调整执行计划收益最大1.2 EXPLAIN 是照妖镜重点看四个字段拿到慢 SQL 文本后EXPLAIN是下一步的必备动作。很多人只看结果不重视方法其实EXPLAIN每个字段都有用但抓不住重点就会变成看天书。我最常让新人关注四个字段type、key、rows、Extra。字段含义重点关注type访问类型ALL是全表扫描需要警惕ref/range正常const/eq_ref理想key实际使用的索引NULL说明没走索引回去查条件rows预估扫描行数和实际行数对比偏差太大会导致优化器选错计划Extra附加信息Using filesort、Using temporary都是性能瓶颈信号比如type从好到坏的顺序大致是consteq_refrefrangeindexALL。如果你看到ALL第一反应应当是索引没建对而不是SQL 写得不用心。Extra里出现Using filesort说明 MySQL 要为查询结果做额外排序数据量一大就会在磁盘上搞临时文件Using temporary说明查询产生了内部临时表一般是GROUP BY或DISTINCT引起的。这两样都是优化重点。1.3 优化前先量化没有基线谈不上快慢做慢 SQL 优化最怕没有量化意识。有一次我接手一个项目同事说这个接口优化后快多了我看代码发现他把一条查询拆成了十几次小查询单条是快了整体接口反而多了几十次网络往返。这种自我感觉良好的优化还不如不做。正确的做法是优化任何 SQL 之前先留下三样东西当前执行计划、当前执行耗时、当前扫描行数。优化之后按同样的方式回放验证用数字说话。如果是 MySQL 8.0还可以用EXPLAIN ANALYZE直接拿到真实执行时间和实际行数EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;它会输出每个算子实际消耗的时间和行数比干看EXPLAIN的预估 rows 准确得多。2. 索引失效的五种现场执行计划如何一步步被打穿2.1 案例1500万行订单表被全表扫描缺的不是SQL是索引现象某订单查询接口日常稳定在 150ms某天突然飙到 7~8 秒。日志里卡住的 SQL 是SELECT * FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;排查EXPLAIN的结果里type ALLrows 5200000Extra里还有Using filesort。500 万行全部扫一遍还给每行做了一次排序怎么可能不慢。表结构里user_id和create_time都没有索引查询只能全表扫描。这个案例并不是 SQL 写得多烂纯粹是表设计阶段没有预判到这个查询模式。优化建立联合索引ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time);联合索引建立后WHERE user_id ?先通过索引快速定位到该用户的所有记录ORDER BY create_time又直接利用索引的有序性连排序都省了。优化后查询耗时稳定在 20ms 以内EXPLAIN中type变成了refExtra里也没有Using filesort。2.2 案例2传错参数类型索引悄悄罢工现象另一张用户表users主键id是BIGINT但某同事在接口里把id传成了字符串SQL 写成SELECT * FROM users WHERE id 10086;排查这条 SQL 单独执行速度尚可但更大的问题是另一个字段phone上。phone字段是VARCHAR(20)查询却用了数字SELECT * FROM users WHERE phone 13800138000;phone字段明明有索引EXPLAIN却显示type ALL扫描行数等于全表。原理MySQL 在比较字符串字段和数字字面量时会把字符串转换成数字再比较。转换过程发生在字段这一侧意味着对索引列的隐式函数调用索引自然失效。这就好比电话号码存的是010-12345678这种带连字符的格式你用纯数字去找数据库必须先把所有数据格式化一遍才能比对。优化让参数类型和字段类型保持一致。接口层统一处理SQL 写成SELECT * FROM users WHERE phone 13800138000;这条改动之后查询走了ref访问耗时从 800ms 降到 5ms。排查这类问题时我习惯用SHOW WARNINGS看一下优化器改写后的 SQLMySQL 会把隐式转换的细节暴露出来比自己猜要快。2.3 案例3DATE() 包住索引列范围扫描失去了范围现象报表接口要查某天的订单数据SQL 写成SELECT * FROM orders WHERE DATE(create_time) 2024-05-20;create_time明明在联合索引里执行计划却没走索引type ALL。原理DATE(create_time)是在索引列上做了一次函数计算函数结果是无序的B 树索引的排序特性完全没法利用优化器只能放弃索引。这其实是函数包裹索引列问题的统一场景——不管你是用DATE、YEAR、MONTH、DATE_FORMAT还是算术运算只要索引列被包在函数里就基本告别索引了。优化把函数包在条件值一侧改写为区间查询SELECT * FROM orders WHERE create_time 2024-05-20 00:00:00 AND create_time 2024-05-21 00:00:00;改写后走range扫描扫描行数从全表 500 万降到当天订单数耗时从 4.2s 降到 30ms。类似的场景还包括WHERE DATE_FORMAT(create_time, %Y-%m) 2024-05都按这个思路处理。2.4 案例4OR 一条条件没索引整条SQL回到解放前现象订单查询接口加了新筛选条件后突然变慢日志捕获的 SQLSELECT * FROM orders WHERE user_id 12345 OR status PAID ORDER BY create_time DESC LIMIT 20;user_id有索引status没有索引。排查EXPLAIN显示type ALL。核心问题出在OR条件——MySQL 要对OR两侧的结果做集合合并如果有一侧无法使用索引整条语句最终可能回到全表扫描。某些版本即使两侧都有索引也可能触发index_merge的额外排序开销并不一定比全表扫描快。优化SQL 改写为两个独立查询 合并同时去掉重复记录SELECT * FROM orders WHERE user_id 12345 UNION ALL SELECT * FROM orders WHERE status PAID AND user_id ! 12345 ORDER BY create_time DESC LIMIT 20;两个子查询分别走各自索引再把结果合并。优化后整体执行时间从 3.1s 降到 60ms。这个场景给我们的经验是OR条件看似简单实际是最容易让优化器左右为难的写法。能用UNION ALL拆分就拆分。2.5 案例5JOIN 的两张表字符集不一样索引像不存在现象订单库和用户库的表做关联查询SQL 长这样SELECT o.order_id, u.nickname FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.create_time 2024-05-01;orders.user_id是utf8mb4_unicode_ciusers.id是utf8_unicode_ci两张表的关联字段都有索引但被驱动表的关联查询却一直走全表扫描。原理字符集或排序规则不一致时MySQL 需要对一侧字段做隐式字符集转换导致被转换侧的索引失效。这和案例 2 的隐式类型转换是同一类问题只是这次不是数字和字符串而是两个不同字符集之间的换算。优化统一字符集把users表整体转换为与orders一致的编码ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;执行完再跑同一句 SQL关联查询顺利走索引耗时从 5.6s 降到 120ms。这个案例提醒我不止一次多表关联查询除了看 JOIN 条件、驱动表还要顺手确认关联字段的字符集、排序规则是否一致。一张表改了字符集、另一张忘了改灾难就很容易悄悄埋下。3. 排序、分组与深分页慢的往往不是查找是“整理”3.1 案例6LIMIT 100000, 20 的翻页接口越翻越慢现象列表页翻到第 5000 页后接口明显卡顿某条 SQL 的耗时可以到 9 秒SELECT * FROM orders WHERE user_id 12345 ORDER BY id DESC LIMIT 100000, 20;排查EXPLAIN走的是range索引也没问题。但rows显示先扫描了 100020 行然后丢弃前 100000 行只返回最后 20 行。也就是说每翻一页MySQL 都要从头把所有数据找一遍再扔掉。深分页的本质是前面翻过的页每次都被重复计算。数据量越大偏移量越大这笔账就越贵。优化书签法也叫键集分页法。客户端记住上一页最后一条记录的 id翻页 SQL 改成SELECT * FROM orders WHERE user_id 12345 AND id {上一次翻页的最大ID} ORDER BY id DESC LIMIT 20;这样 MySQL 可以直接从索引定位到目标位置跳过前面所有无效行扫描量从 10 万行降为 20 行。优化后耗时稳定在 10ms 左右。如果你没法改前端翻页逻辑还能用延迟关联的写法先用覆盖索引查出目标 id再回表取完整数据SELECT * FROM orders JOIN (SELECT id FROM orders WHERE user_id 12345 ORDER BY id DESC LIMIT 100000, 20) t ON orders.id t.id;子查询里只查id可以用覆盖索引快速完成偏移操作回表的行数只有最终需要的 20 行。两种方案都是深分页场景的标准解法。3.2 案例7ORDER BY 字段没进索引MySQL 要额外 filesort现象活动报表接口执行时间不稳定短则 600ms长则 3s。SQLSELECT order_id, user_id, amount FROM orders WHERE user_id IN (1001, 1002, 1003) ORDER BY create_time DESC LIMIT 50;分析EXPLAIN中Extra出现了Using filesort。user_id IN (...)可以走二级索引但ORDER BY create_time不在这个索引里MySQL 只能把命中的记录全部读出来丢到排序缓冲里做文件排序。优化把排序字段一起装进联合索引。既然 WHERE 前提是user_id排序条件又是create_time建立ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);这里有个细节联合索引的字段顺序必须是user_id在前、create_time在后这样索引本身就是按 user 分组、组内按时间有序排列的ORDER BY create_time可以直接从索引顺序中读取完全省略排序步骤。Extra里的Using filesort消失查询耗时稳定在 80ms。3.3 案例8GROUP BY 背后藏了一个临时表现象运营要按城市统计用户量SQL 极其简单SELECT city, COUNT(*) FROM users GROUP BY city;users表 800 万行这条语句要跑 12 秒。排查EXPLAIN里Extra显示Using temporary; Using filesort。GROUP BY city在没有索引的情况下需要把所有记录加载到内存临时表并在临时表上继续分组和排序。800 万行数据全量进入临时表开销一下就上去了。优化给city加索引ALTER TABLE users ADD INDEX idx_city (city);有了索引之后相同城市的用户在索引中是连续排列的GROUP BY可以直接沿着索引顺序扫描并聚合不需要额外临时表。优化后耗时从 12s 降到 1.2s。如果查询还带上COUNT(*)加索引还有一个额外收益——索引体积通常比表小扫描时的 IO 成本更低。4. 多表关联与子查询优化器也是吃“成本估算”这碗饭的4.1 案例9LEFT JOIN 选错了驱动表1000万行的大表被反复扫现象后台权限查询接口跑一次要 8 秒SQL 是一个典型的三表联查SELECT u.name, r.role_name FROM user_roles ur LEFT JOIN users u ON ur.user_id u.id LEFT JOIN roles r ON ur.role_id r.id WHERE ur.user_id 10086;单看业务逻辑没什么问题但小表user_roles只有几千行而users表有 1000 万行。优化器因为统计信息不够准确选择了users作为驱动表等于用 1000 万行的大表去逐行探测关联条件数据在内存里来回倒腾能不慢么。而Explain显示被驱动表user_roles的关联字段索引没有生效进一步加剧了扫描成本。优化多表关联优化的第一原则——小表驱动大表保证被驱动表的关联字段有索引。先给user_id、role_id都补上索引然后执行计划交给优化器重新选择驱动表。如果遇到统计信息不准或优化器固执己见的情况也可以使用STRAIGHT_JOIN强制指定驱动表和连接顺序但这种改法只做临时应急不建议长期保留。最佳方案还是让统计信息和索引状态回到健康状态让优化器自然选择正确路径。4.2 顺带聊透 IN 子查询的“物化临时表”问题和 JOIN 类似的还有IN子查询。我第一次遇到这个问题时SQL 长这样SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM users WHERE level 5)users表 200 万行orders表 500 万行。执行时间 6.8 秒EXPLAIN显示会先将子查询结果物化成一个临时表临时表没有合适索引再拿orders全表逐行去临时表匹配。优化对于 MySQL 5.6 以上版本优化器会对半连接做多种处理包括半连接物化、半连接重写等但并不是每次都能选对策略。最稳妥的做法是手动改写为EXISTSSELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.user_id o.user_id AND u.level 5)EXISTS是外循环一行、内层探测一次的语义如果内层user_id有索引性能会好很多。某些情况下改用JOIN再加上去重也是一条路SELECT DISTINCT o.* FROM orders o JOIN users u ON u.user_id o.user_id AND u.level 5这几种写法不是绝对谁比谁快核心是让执行计划避免大表 无索引临时表的组合。拿到慢 SQL 后把三种写法都EXPLAIN一遍选择rows最小的那个版本这才是正路。5. 统计信息过期执行计划犯糊涂时先别急着改SQL5.1 案例10同一句SQL单独执行快批量压测慢现象压测环境跑一个登录日志查询单条 SQL 单独执行耗时 30ms一到压测就变成 3 秒以上。SQL 文本完全相同没有任何变化。分析单独执行快、并发场景慢大多数人第一反应是有锁竞争或连接池阻塞。但我抓了压测期间的EXPLAIN发现执行计划直接选了另一条更差的索引rows的预估也从实际几万行变成了数百万行。原因MySQL 优化器依赖统计信息估算成本统计信息由定期采样更新维护。某个大表刚发生过大规模数据清理行数从 800 万掉到 50 万但统计信息还没更新。优化器还是按 800 万行的老数据评估路径成本选了一条它认为便宜、实际非常贵的路线。尝试强制走优索引SELECT * FROM login_logs FORCE INDEX (idx_user_time) WHERE user_id 123 AND create_time 2024-01-01;耗时可恢复但这不是长久之计。真正解决问题的命令很简单ANALYZE TABLE login_logs;执行后统计信息刷新EXPLAIN重新选出正确索引并发压测恢复正常。经验排查慢 SQL 时如果有条件一定要拿到同一时段的执行计划而不是用非高峰期的EXPLAIN来验证。尤其在大批量数据变更后执行计划突然变差的第一嫌疑就是统计信息过期。5.2 怎么避免统计信息再次“犯糊涂”没有人会天天手动ANALYZE TABLE要让执行计划稳定就得有固定维护机制大数据量变更后批量导入、分区清理、大事务删除尽快触发表分析。周期性维护窗口执行ANALYZE TABLE时间可以放在业务低峰期。关注information_schema.tables中last_analyze_time之类的元数据及时发现久未更新的表。这里也想提醒一点不要为了追求执行计划绝对正确就频繁全表分析它会消耗额外 IO也是一种成本。一个平衡的做法是对高频大表每周一次例行 ANALYZE对于数据量明显波动的表在数据变动后单独触发。6. 优化完之后的回归验证我踩过的坑和现在的固定动作把 10 个案例逐个收尾后我发现慢 SQL 优化的回报往往不在于某一条 SQL 提速了多少而在于你有没有形成一套固定的验证流程。我现在每个优化改动上线前必须跑完这三步第一步回放执行计划。优化前后同一时刻、同一数据快照下对比EXPLAIN的type、rows、Extra三个关键字段是否如预期变化。不达标绝不提优化完成。第二步回放真实流量。找几条线上慢日志里的真实 SQL在预发环境按生产数据量回放记录 P50、P95 耗时。只测一条 SQL 最快不是目标整个接口的耗时分布变好才是。第三步观察一段时间线上的慢日志。优化上线后的前三天重点看慢日志里是否还持续出现同类 SQL。我遇到过一种情况一条 SQL 优化好了但因为代码里还有一个地方拼了类似但略不同的 SQL慢计算根本没消失。慢日志就是最后的裁判。分享一个我个人的教训有一次我只盯着一条慢 SQL 改了索引上线后单条 SQL 是快了但那条 SQL 高频执行新索引反而让写放大和锁等待更严重整体性能不升反降。后来我养成了一个习惯——加索引之前先确认这张表的读写比、写入频率。一个索引带不来毁灭性影响但高并发写入表上的每个新索引都是一笔额外开销。优化永远要做整体评估别被单点数据迷了眼。最后给一个小技巧画 SQL 优化排查时我习惯先问自己三个问题——这个查询能不能少扫描行数、能不能少回表次数、能不能绕过排序。只要这三个问题里有任何一个能给出可以的答案这条 SQL 大概率就有优化空间。你照着这个思路去读EXPLAIN的结果十次里有八次都能找到答案。