1. 项目概述慢查询到底慢在哪里做了几年 MySQL 的查询优化我现在拿到一条慢查询基本扫一眼执行计划就能猜个八九不离十。这不是什么天赋纯粹是坑踩多了。最近整理一个后台订单列表的调优记录这条查询从最初的 1500 毫秒压到了 15 毫秒左右全程没有用任何高深工具就是执行计划加索引调整加 SQL 重写三板斧。这篇文章就把这套思路完整拆开讲一遍适合刚接触优化、或者已经会写 SQL 但总被慢查询折腾的同学。看完你至少能回答三个问题一条 SQL 为什么慢、怎么定位慢的环节、用什么手段能安全提速。1.1 一条SQL从提交到返回到底卡在哪很多人一提到查询优化就想到加索引但索引只是优化链条里的一环。一条 SQL 从客户端发出去到拿到结果中间大致要经过连接器、分析器、优化器、执行器和存储引擎。连接器和分析器基本不耗时真正的耗时大头在优化器选路和执行器读数据。优化器决定用哪个索引、以什么顺序连接表执行器则按照这个计划去存储引擎里取数据。理解这个流程有个类比你去一个超大的仓库找一件特定商品库管员优化器先判断从哪个货架开始找最快然后搬运工执行器一趟趟把候选箱子搬出来翻。如果库管员选错了货架搬运工再勤快也没用。慢查询的根源绝大多数就两个一是候选的箱子太多扫描行数过大二是搬运工干了很多无用功排序、临时表、回表。明确这个框架之后优化就不再是玄学而是一个精准定位耗时点、再逐个击破的工程问题。1.2 先给慢查询分个类碰到慢查询我习惯先把它分到下面四类里分类决定了后续的排查方向。直接在开头就把问题归好类能少走很多弯路。类型典型表现核心原因扫描型全表扫描或扫描行数远超返回行数缺索引、索引失效、统计信息不准排序型Extra 里出现 Using filesort排序字段和索引顺序不匹配连接型大表驱动小表、Join 缓冲溢出连接字段没索引、表连接顺序不佳资源型CPU 打满、IO 延迟高、锁等待并发压力、慢 SQL 堆积、锁竞争实际项目里一道查询往往同时踩中两三类。比如我优化的那个订单列表最初的执行计划既有 Using filesort又因为深分页导致扫描行数爆炸属于典型的“扫描型 排序型 连接型”混合体。面对混合问题一定不要想着一条 SQL 一次改到位而是先压扫描行数再消排序最后处理连接和分页每一步都用 EXPLAIN 验证。这个顺序我后面展开讲。2. 看懂执行计划优化不是靠猜优化之前必须先学会读执行计划。执行计划是优化器最终选择的执行路径它告诉你每一张表是怎么被访问的、预估扫了多少行、有没有额外的排序和临时表操作。不看执行计划就改 SQL跟蒙着眼睛开车没有区别。2.1 EXPLAIN 的关键字段先记牢在查询前面加 EXPLAINMySQL 会返回一张结果表不需要真正执行查询。新手最容易盯着的字段是 type、key、rows、Extra这四个字段几乎决定了问题的成因。key 表示优化器最终选的索引rows 是预估扫描行数Extra 是额外操作信息。rows 和最终返回行数差距越大说明浪费越严重。type 则代表访问类型我会单独展开说。除此之外key_len 也值得看它表示用到了联合索引中多少列索引越靠左的列参与度越高key_len 也会越大。通过 key_len 可以判断联合索引是否真的被充分利用。注意EXPLAIN 里面的 rows 只是优化器的预估值基于统计信息算出来的不是真实扫描行数。如果发现预估值和实际表现严重不符往往需要 ANALYZE TABLE 更新统计信息。2.2 type 字段访问路径的质量等级type 字段从好到差大致是system、const、eq_ref、ref、range、index、ALL。日常调优记住一个原则目标是让 type 至少达到 ref 或 range尽量避免 index 和 ALL。const 和 eq_ref 通常出现在主键或唯一索引等值匹配的场景比如WHERE id 100这是最高效的路径直接定位一行。ref 是普通索引等值匹配返回多行但符合条件的数据都集中在索引的某一小段效率很高。range 是索引范围扫描比如WHERE created_at 2024-01-01只要范围不大也可接受。index 表示扫描了整个索引树虽然比全表扫描好一点但本质上还是把索引里所有条目都读了一遍。ALL 就是全表扫描通常意味着没有可用索引或者优化器认为索引还不如全表扫。我见过很多人一看到 type 是 ALL 就慌了其实不一定需要改 SQL。如果表本身只有几百行全表扫描可能比走索引更快因为回表也需要随机 IO。判断的标准是看 rows只要扫描行数小type 差一点也没关系。反之如果 rows 几十万type 再好看也得想办法降下来。2.3 Extra 字段藏在角落的性能警告Extra 字段里最能暴露问题的是两个关键词Using filesort 和 Using temporary。前者代表 MySQL 需要额外做一次排序后者代表需要建临时表这两件事都意味着一次查询要被拆成多个阶段内存和磁盘开销都会上来。出现这两个关键词时即便返回的行数不多查询也可能很慢因为排序和建临时表是行数越多越致命的操作。Using index 是好消息表示查询所需的列都在索引中不需要回表读取完整行这叫覆盖索引。Using index condition 表示用到了索引条件下推存储引擎在索引层面先做一层过滤减少回表次数MySQL 5.6 以后才有通常也是正向信号。还有一种需要警惕的是出现 Using where它可能表示存储引擎返回了大量候选行再由 Server 层做二次过滤意味着索引利用不充分。3. 索引设计与失效排查索引是查询优化最核心的手段但前提是设计合理、没有失效。很多慢查询的现场是索引建了一堆查询还是慢。原因要么是最左前缀没遵守要么是查询条件把索引给“废”了。3.1 联合索引的最左前缀原则联合索引本质上是先按第一列排序再按第二列、第三列排序像一本先按姓氏再按名字排列的通讯录。如果你建了(user_id, status, created_at)这个索引那么查询条件里必须包含 user_id 才能用到这个索引只用 status 或只用了 created_at 却没用 user_id索引就无法生效。这就像查通讯录时只知道名字不知道姓氏翻书也没法快速定位。最左前缀还有一个隐藏细节一旦条件里出现范围查询比如user_id 1 AND created_at 2024-01-01 AND status 1那么范围列之后的 status 索引参与度会打折扣。这里关键是索引列的顺序设计等值条件列放前面范围条件列放中间用于排序或覆盖的列放最后。所以索引设计不是“给每个字段建一个”而是要结合具体查询模式设计联合索引。基于此我建议每次设计索引之前先把业务里最高频的几种查询模式列出来再反推索引顺序。3.2 六个让索引失效的典型操作下面这六种情况是我在项目里实际遇过的每种都对应一个“建了索引等于没建”的场景。对索引列使用函数或表达式WHERE DATE(created_at) 2024-03-01。正确写法是created_at 2024-03-01 00:00:00 AND created_at 2024-03-02 00:00:00后者还能走 range 扫描。隐式类型转换电话号码字段是 varchar查询条件写成WHERE phone 13800138000MySQL 会把字符串列转成数字再比较索引直接失效。必须写成WHERE phone 13800138000。LIKE 以通配符开头WHERE name LIKE %张无法走索引只有张%这种前缀匹配可以利用索引。OR 连接非索引列WHERE user_id 1 OR status 2如果 status 没有索引优化器可能放弃走 user_id 的索引改成全表扫描。这种情况优先考虑拆成两条 SQL 再用 UNION ALL 合并。使用 NOT IN、、!这些操作符通常会让索引失效因为优化器会认为不满足条件的行太多索引收益不大。如果确实要取少数例外值用反向排除未必划算可以结合业务重写。违背最左前缀索引是(a, b, c)查询条件只写 b 和 c索引只能被部分利用或完全不用。还有一类容易被忽略的如果字段本身区分度太低比如一个只有 0/1 两个值的标记位优化器也可能不选索引因为走索引要回表还不如直接扫表。索引不是越多越细越好要综合考虑区分度和回表代价。3.3 覆盖索引让查询少回一次表InnoDB 的二级索引叶子节点存的是索引列和主键查询如果只用到索引里的列MySQL 可以直接从索引拿到数据这就是覆盖索引。覆盖索引最大的收益是避免了回表操作。回表意味着对每一行符合条件的记录都要根据主键再去聚簇索引里随机读一次行数一多随机 IO 的开销会非常惊人。比如这条查询SELECT user_id, status, created_at FROM orders WHERE user_id 1如果索引是(user_id, status, created_at)那么 type 是 refExtra 里会显示 Using index整个过程只需要在索引这棵树上操作。如果加上了 total_amount 这个非索引列就必须等找到所有符合条件的 id 后回表读取金额性能差异在几十万行的表上非常明显。所以当查询固定只取几列时把这几列塞进索引的末段是最经济的提速手段。注意覆盖索引是把双刃剑。索引列越多占用的磁盘空间越大写入时 B 树结构调整的成本也越高。只给高频查询设计覆盖索引不要为了覆盖而覆盖。4. SQL重写不动索引也能提速的技巧索引优化解决的是“怎么读”的问题SQL 重写解决的是“读什么”和“怎么组合”的问题。两者的关系有点像优化仓库动线和精简取货清单双管齐下效果最好。这里说的重写不是鼓励把 SQL 写花哨而是让语句结构和优化器能力对齐。4.1 SELECT * 是很多慢查询的起点SELECT *看着省事代价却很大。第一它会把所有列的数据都从存储引擎读出来哪怕业务页面只用三列第二它会破坏覆盖索引的效果让本来可以在索引里解决的查询被迫回表第三多出的字段会加重网络传输和结果集处理的负担。我处理过的案例里很多查询改成只取需要的列之后即使索引结构不变延迟也下降了 20% 以上。所以第一条纪律就是手写每一列别用星号。4.2 子查询改JOIN但小心重复行子查询在某些版本和场景下会导致临时表性能不理想。经典做法是把WHERE user_id IN (SELECT user_id FROM vip_users WHERE level 3)改成JOIN vip_users。但改 JOIN 有个隐蔽的坑如果 vip_users 里同一个 user_id 出现多行JOIN 会让结果翻倍。IN 的语义是“存在即可”而 JOIN 是“匹配几行返回几行”。所以改写前必须确认关联列的唯一性如果不唯一要么先用子查询去重要么用 EXISTS。EXISTS和IN在优化器手里通常会被转换为半连接semi-join语义和性能都比较稳改写后务必用执行计划确认 rows 没有异常放大。4.3 深分页的三种解法LIMIT 100000, 20这种写法是后台系统最常见的性能杀手。它先扫描前 100020 行再丢弃前 100000 行最后只留下 20 行。页码越深浪费越大而且这部分浪费在业务上毫无意义。针对深分页我常用三种解法。第一种是游标分页也叫 keyset pagination。上一页返回最后一条记录的排序字段值下一页直接把它带进查询比如WHERE created_at 上一页最后时间 ORDER BY created_at DESC LIMIT 20。它能稳定利用索引快速定位到续点页数再深也只会扫 20 多行。第二种是延迟关联先查出主键 id再 JOIN 回原表取完整数据。第三种是在业务层限制最大翻页深度比如只允许翻到 100 页超过就放宽条件或改用游标。三种方案可以叠加使用我个人最推荐游标分页因为它从根上规避了偏移量问题。5. 完整调优案例从1500ms到15ms前面的方法论讲得再多不如完整走一遍案例。这个案例是某个模拟后台系统里的订单列表接口逻辑不复杂但线上表现很差属于非常典型的“看起来没问题实际慢得离谱”。5.1 案例背景与慢查询定位订单表有大约 200 万行当时为了演示我按一个高频用户取了数据分布该用户某个月份的订单记录接近 5000 条。原始需求是按用户、下单时间范围筛选订单按创建时间倒序分页返回。当时为了偷懒分页参数直接传了偏移量第 50 页的 SQL 长这样SELECT id, order_no, user_id, total_amount, status, created_at FROM orders WHERE user_id 123456 AND created_at 2024-03-01 00:00:00 AND created_at 2024-03-31 23:59:59 ORDER BY created_at DESC LIMIT 980, 20;表上原来只有一个idx_user(user_id)单列索引。EXPLAIN 的结果是 type 为 refkey 用了 idx_user但 rows 是 12453Extra 里同时出现了 Using where 和 Using filesort。这意味着优化器拿了该用户所有订单逐条过滤时间范围再排序再丢弃前面的 980 行。这个用户历史订单超过 1 万条大量无效行都被读了出来1500 毫秒基本都花在这里。5.2 索引调整过程定位到问题后第一刀就是加联合索引。因为查询里 user_id 是等值条件created_at 是范围条件排序字段也是 created_at最合理的索引是(user_id, created_at)。加了 idx_user_created 之后EXPLAIN 变成 type 为 rangerows 降到 4830Using filesort 消失。原因是索引本身就是先按 user_id 分区、再按 created_at 排序的等值 user_id 之后created_at 范围恰好对应索引里的连续一段倒序遍历这一段就能直接拿到排序结果。这一步做完查询延迟从 1500 毫秒降到 300 毫秒左右。可以看到索引调整解决的是扫描行数和排序问题而且完全不动业务逻辑风险很低。如果查询还需要回表取很多列rows 从 12000 降到 4800回表次数也同步下降所以收益是双重的。接着我又把 status 作为一个附加过滤条件加进去为了不破坏排序没有把 status 放在 created_at 前面而是让它继续走索引条件下推实测效果稳定。5.3 深分页改造与收尾收益索引调整之后单页查询变快了但第 50 页依然要扫 1000 行索引再丢 980 行这部分开销还在。于是把分页改成游标模式。前端翻页时携带上一页最后一条记录的 created_at下一页的 SQL 变成SELECT id, order_no, user_id, total_amount, status, created_at FROM orders WHERE user_id 123456 AND created_at 2024-03-01 00:00:00 AND created_at 2024-03-15 10:30:00 ORDER BY created_at DESC LIMIT 20;这一步让扫描行数直接降到 20 左右查询延迟稳定在 15 毫秒以内。整个过程下来优化最大的收益其实来自“少干活”先把无效行数从 1 万多降到 5 千以内再去掉排序最后消灭偏移量浪费。三条措施没有一条是炫技全部是围绕执行计划里的 rows 和 Extra 做减法。6. 常见问题与排查技巧实录优化的日常不是一帆风顺的很多问题在文档里写不清楚只有翻车之后才会记得牢。我把项目里反复遇到的几类问题整理成速查表顺便把排查工具链的使用方法说一下。6.1 索引明明建了为什么不生效这是被问得最多的一个问题。排查顺序我总结成一个口诀看条件、看类型、看统计。先看 WHERE 条件里有没有对索引列做函数运算、隐式转换或者用 OR 连接了无索引列再看字段类型是否匹配比如 varchar 字段忘了加引号最后看统计信息。曾经遇到过一个案例索引建得很合理查询条件也没问题但 EXPLAIN 就是显示全表扫描。最后发现表数据更新太频繁统计信息严重过期导致优化器低估了全表扫描成本。执行一遍ANALYZE TABLE orders之后执行计划立刻恢复正常。这类问题在 MySQL 8.0 里也不少见因为统计信息不是实时更新的。6.2 统计信息不准导致选错索引统计信息不准的另一个典型表现是优化器放弃更优索引选了区分度更差的索引。此时除了 ANALYZE TABLE还可以用 FORCE INDEX 强制指定索引但这是给紧急情况用的临时方案不能长期写在代码里因为数据分布变化后强行指定的索引可能又会变成坏选择。根本办法是让统计信息保持新鲜或者在索引设计层面让更多候选索引失效让优化器没得选错。另外MySQL 8.0 支持不可见索引可以先建索引但不让它参与执行计划拿真实业务流量测试确认没问题再把它设为可见这是很实用的灰度手段。6.3 慢查询日志与优化工具链如果不知道系统里哪些 SQL 慢优化就无从谈起。建议把慢查询日志打开阈值先设成 1 秒slow_query_log ON slow_query_log_file /data/mysql/log/slow.log long_query_time 1 log_queries_not_using_indexes ON日志里会记录耗时、锁等待时间、扫描行数和返回行数。拿到原始日志后用 MySQL 自带的 mysqldumpslow 按执行次数或耗时排序很快就能找出真正值得优化的 TOP SQL。在 MySQL 8.0 里还可以用 EXPLAIN ANALYZE 查看每一步的实际耗时和传统 EXPLAIN 的预估值对比能精准定位到底卡在哪一行操作上。这个工具组合足够覆盖绝大多数查询优化场景没有必要一上来就上重型监控系统。6.4 我平时坚持的几个优化习惯最后分享几个我自己的习惯可能比具体语法更有长期价值。每次改 SQL 之前先写清预期比如“预计 rows 从 12000 降到 100”改完立刻看 EXPLAIN 验证而不是只看接口耗时因为耗时受缓存和机器负载影响执行计划的 rows 和 Extra 才反映真实执行路径。上线前把新查询放到测试库跑一遍然后用EXPLAIN ANALYZE观察实际行数是否和预估一致如果偏差过大就要查统计信息。还有索引变更永远挑低峰期做加索引虽然不锁全表但大表加索引耗时很长要在维护窗口单独处理。最后就是别迷信“万能优化技巧”每一条 SQL 的数据分布和业务语义都不一样老老实实对着执行计划做决策远比套用任何模板都可靠。