MySQL慢SQL优化五板斧：执行计划、索引与深分页实战

做后端开发和数据库运维的同学，对“接口突然变慢”这件事应该都不陌生。线上告警一响，排查半天，最后定位到一条SQL把CPU打满或者把慢查询日志刷屏，这种场景我遇到过太多次。MySQL的SQL语句优化，说白了就是想办法让数据库用最少的时间、最少的资源把结果集给你捞出来，而不是让它在几百万行数据里做无差别扫描。我这几年的实践经验里，真正高频、有效、能复用的手段，基本可以归成五类：分析执行计划、优化索引、精简查询字段、处理深分页、改写JOIN和子查询。这篇文章就把这五板斧完整拆开，结合真实场景讲清楚每一步怎么做、为什么这么做，适合刚接触数据库优化的开发同学，也适合想系统梳理一遍优化思路的后端工程师。

1. 为什么你的SQL慢？先搞清楚优化到底在优化什么

1.1 慢SQL的本质：扫描行数、回表次数和排序开销

一条SQL慢，表象千奇百怪，但底层原因翻来覆去就那么几类。第一个是扫描行数太多，比如一张表里有两千万行，你按一个没索引的普通字段去查，MySQL只能从第一行开始挨个比对，直到把整张表翻完，这个成本是O(n)，数据量一大必死。第二个是回表次数太多，走索引找到了主键，但查询的字段不在索引里，还得拿着主键再回聚簇索引查一次，每次回表都是一次随机IO，数据量大了自然快不起来。第三个是排序和临时表，ORDER BY、GROUP BY、DISTINCT这些操作如果没利用好索引的有序性，MySQL就得在内存或磁盘上建临时表排序，一旦数据量大到超过内存阈值，磁盘排序的速度会慢到你怀疑人生。

理解这些本质之后，所谓SQL优化就不再是死记硬背“该用索引”“别用SELECT *”这种口号，而是变成一道有清晰目标的计算题：让MySQL用更少的扫描行数、更少的随机IO、更少的临时空间来完成任务。我习惯在评估任何一条慢SQL时，先问三个问题：这条SQL要处理多少行？这些行是怎么被找到的？找到之后还要做哪些额外动作？把这三个问题搞清楚了，优化的方向基本就出来了。

1.2 五种优化方式的整体定位

这五种方式不是互相独立的，它们是同一个问题从不同角度的解法，实际使用的时候往往是组合拳。执行计划分析是诊断手段，先看它才知道病在哪；索引优化是治本手段，让查询路径从根本上变短；查询字段精简是低成本高收益的手段，改动最小但要养成习惯；分页优化针对的是深分页这个特定痛点；JOIN和子查询改写针对的是多表查询场景。我用个不恰当的比喻：执行计划是拍CT，索引是换好的血管通路，字段精简是减轻负重，分页和JOIN改写是重新设计路线。后面每一节都会配合实际案例来讲，照着操作就能上手。

2. 方式一：EXPLAIN分析执行计划——所有优化的起点

2.1 执行计划关键字段怎么读

我不会优化一条我不理解执行过程的SQL。EXPLAIN就是MySQL给的执行计划说明，它告诉你这条SQL会以什么顺序、什么方式去访问哪些表。你不需要把每个字段都背下来，真正决定一个查询生死的字段就那么几个。

type字段是访问类型，直接反映了查询效率的档次。从上到下分别是system、const、eq_ref、ref、range、index、ALL。system和const是极少数情况，比如按主键查一行；eq_ref是JOIN时按主键或唯一索引匹配，性能很好；ref是非唯一索引等值匹配，也不错；range是索引范围扫描，比如 BETWEEN、>、<这类条件；index是扫全索引，虽然比扫描全表好一点，但依然把索引整个过了一遍；ALL是全表扫描，这是你最不想看到的。我平时的判断准则很简单：看到range以上还能接受，看到index和ALL就得问自己为什么没走到更优的路径。

rows字段是估算的需要读取的行数，这个数字不是精确值，但作为数量级参考极有价值。一条SQL优化前预估扫描两百万行，优化后变成两千行，哪怕EXPLAIN的其他字段没变，你也知道方向对了。Extra字段也要重点看，出现了Using filesort或者Using temporary，基本可以断定这条SQL在排序或分组上要付出额外代价，通常需要从索引设计上想办法消除。

2.2 实操案例：一条慢查询的EXPLAIN诊断

某项目的订单表有一千八百万行，线上有个查询最近下单用户信息的接口经常超时，SQL长这样：

SELECT * FROM order_info WHERE user_level = 3 AND order_status = 1 ORDER BY create_time DESC LIMIT 20;

拿EXPLAIN一看，type是ALL，rows估算是一千八百万，Extra里还有个Using filesort。看到这三个信号我基本就判定：全表扫描加上文件排序，不慢才有鬼。user_level和order_status这两个字段没有索引，ORDER BY create_time也没利用上索引的有序性。优化方案直接围绕这两个问题展开：先建联合索引，让等值条件走索引过滤，同时让排序走索引顺序。这个案例放到下一节具体展开。

3. 方式二：索引优化——让查询走对路

3.1 哪些字段值得建索引

很多新手对建索引的态度走两个极端，要么能不建就不建，要么不管三七二十一所有字段都建一遍。这两种都很坑。我的经验是，判断一个字段要不要建索引，就看它出现的位置：出现在WHERE条件里、JOIN的关联字段上、ORDER BY或GROUP BY里，这三个位置只要有任意一个，这个字段就值得认真考虑索引。

但要注意区分度。区分度太低的话，比如性别字段只有“男”“女”两个值，建了索引之后走索引扫描拿到的还是半个表的数据，优化器算一下发现还不如全表扫，索引等于白建。区分度的判断可以直接跑一条SQL：

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

这个比值越接近1说明区分度越高，起码要大于0.1才值得单独建索引。实际项目里，区分度评估要多测几个字段对比着看，比如状态字段、类型字段这类取值少的，通常更适合放进联合索引作为前缀，而不是单独建索引。

3.2 索引失效的几种常见场景

建了索引不等于一切搞定，索引被“废掉”的场景我几乎每个月都能遇到。最经典的就是在索引字段上做函数运算或表达式计算，比如WHERE DATE(create_time) = '2024-01-01'，MySQL无法直接使用create_time上的索引，因为每一行都得先算函数才能比较。正确的写法是改成范围条件WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'，让索引能够直接定位。

隐式类型转换也是重灾区。某个表字段是varchar类型，但查询时写了WHERE phone = 13812345678，没加引号，MySQL会把字段转成数字类型去比较，这一转索引就失效了。字符集不一致导致的隐式转换同样要留意，两张表JOIN时字段类型一样但字符集不同，一样会让你吃到全表扫描的苦果。还有前导通配符，LIKE '%abc%'这种写法，因为不知道前面匹配什么，B+树索引根本没法定位，只能放弃索引。后面专门有优化方案，这里先记住这个基本认知。

3.3 覆盖索引与联合索引的妙用

回到前面那个订单查询案例，我的优化方式是建一个联合索引，把查询、过滤、排序全部覆盖到：

ALTER TABLE order_info ADD INDEX idx_user_level_status_create_time (user_level, order_status, create_time);

这里为什么要强调顺序？联合索引遵循最左前缀原则，MySQL会从第一个字段开始匹配。查询里user_level和order_status都是等值条件，所以放在前面，create_time用于排序，放在最后面，这样MySQL可以在同一个索引里完成定位和排序，Using filesort自然就没了。

更进一步，如果查询的字段全部包含在索引里，就走上了覆盖索引，Extra里会出现Using index，连回表都省了。我见过一个报表查询，把十几个SELECT字段都塞进联合索引之后，查询从三秒直接降到几十毫秒。当然不是说字段越多越好，索引太大反而浪费空间和写入成本，但针对高频查询精心设计覆盖索引是性价比极高的一招。

索引设计在项目里的规划要遵循几条基本原则：每张表的索引数量控制在五六个以内，避免冗余索引，比如已经建立了(a, b, c)联合索引，就没必要再单独建a字段的索引；写入频繁的表要权衡索引带来的写入压力；大字段加前缀索引而不是整个字段建索引。这些都是常规文档里不太会细讲、但实际踩坑后才懂的经验。

4. 方式三：改写SQL——减少不必要的开销

4.1 避免SELECT *，只取必要字段

SELECT *是偷懒式写法的典型代表，它会带来的问题其实远超很多人的认知。最直接的是IO浪费，如果一张表有二十个字段但查询只需要三个，把二十个字段全部捞出来传输到应用层，网络和内存开销都在白白消耗。更重要的是它直接破坏了覆盖索引的收益。前面讲的覆盖索引要求查询字段全部在索引内，你一写SELECT *，几乎必然要回表去聚簇索引拿剩余字段，本来可以利用索引完成的查询硬生生多了大量随机IO。

我见过最离谱的案例，一个查询其实只需要id和状态两个字段，统计逻辑用这两个就能跑完，结果因为SELECT *把一张宽表的二十几个字段全查出来了，导致每行都要回表，表数据量一大整个接口就废了。改成只查两个字段后，再配合覆盖索引，查询直接从索引里拿数据，性能完全不在一个量级。我的习惯是任何SQL写完都检查一遍SELECT的字段列表，只要有多余字段就删掉。这个习惯一点都不复杂，但它可能是五种方式里改动最小、收益最直观的一个。

4.2 优化LIKE查询与隐式转换问题

LIKE查询的优化要分场景看。LIKE 'abc%'这种后缀模糊匹配是可以走索引的，只要确认前缀固定就行。真正麻烦的是必须做包含匹配的场景，业务上需要LIKE '%abc%'查中间内容。这时的常规方案分两种：数据量小的话干脆接受全表扫描，但要在业务层面加缓存限制频率；数据量大且查询频率高的话，更靠谱的思路是引入全文索引或单独的信息检索服务来处理。我自己在项目里处理过一次商品名称搜索，就是用全文索引替代了原来的LIKE '%关键词%'，效果立竿见影。

隐式转换的问题前面提过，这里再给个实际排查方法。当你怀疑某条查询因为隐式转换没有走索引，可以用EXPLAIN看执行计划，然后在条件字段上手动加CAST再对比一次。比如字段是varchar，分别执行WHERE phone = 13812345678和WHERE phone = '13812345678'，看type字段的变化，通常前者是ALL后者是ref，对比结果一目了然。养成所有字符串字段都加引号的习惯，不只是在SQL注入安全上重要，对索引使用同样关键。

4.3 用EXISTS替代IN的适用场景

关于IN和EXISTS到底哪个快的争论，网上的结论众说纷纭，实际上它是和表的数据量分布强相关的。核心原则是：小表驱动大表。当外部表数据量小而子查询的表数据量很大时，用EXISTS往往更合适；反过来当外部表很大而子查询返回的结果集很小的时候，IN通常更快。MySQL优化器在5.6以后对IN做了很多优化，会把子查询转成半连接处理，所以这个对比已经不是简单的二选一。

我给一个更实用的判断方式：直接看EXPLAIN，别凭感觉。把IN和EXISTS两种写法分别跑一遍EXPLAIN，对比驱动表和被驱动表的扫描行数，哪个更少就用哪个。还有一个通用原则是尽量把子查询改写为JOIN，因为JOIN的优化手段更多，索引利用更直接。但改写JOIN要注意去重问题，IN本身有去重语义，JOIN可能产生重复行，改写后要确认是否需要DISTINCT。

5. 方式四：分页查询优化——深分页的坑

5.1 LIMIT深分页为什么慢

业务里最常见的分页写法就是LIMIT offset, size。数据量小的时候看不出问题，一旦offset涨到十万、百万，你会明显感觉到页面越翻越慢。原理其实不复杂：LIMIT 1000000, 20的意思不是“从第一百万行开始取二十行”，而是“把前一百万零二十行全查出来，然后丢掉前一百万行”。MySQL必须扫描并排序全部这些行才能拿到目标区间，扫描行数随着offset线性增长，这本身就是一种巨大的浪费。

我做过一次压测，一张三百万行的表，LIMIT 10查询耗时不到十毫秒，LIMIT 200000, 10直接飙到八百多毫秒。同样的查询，只是翻页到后面，性能差了七八十倍，这就是深分页坑的所在。解决思路的方向其实很明确：不要让数据库去扫描那些注定要被丢掉的行。

5.2 延迟关联与游标分页方案

延迟关联是解决深分页非常有效的一个方案。它的思路是先只查主键，利用主键索引快速确定起始位置，然后再回表拿完整的数据。改成这样：

SELECT t1.* FROM order_info t1 INNER JOIN ( SELECT id FROM order_info ORDER BY create_time DESC LIMIT 1000000, 20 ) t2 ON t1.id = t2.id;

子查询里只取了主键id，走的是索引覆盖扫描，不需要回表，所以虽然还是扫描了一百万行索引，但成本比回表拿全字段低得多。外层再通过主键JOIN回去拿具体字段，每行都是一次主键查询，非常快。我在实际项目里用这个方案优化过一个翻页接口，耗时从九百毫秒降到了八十毫秒左右。

如果业务场景允许，游标分页是更彻底的办法。不做页数跳转，而是记录上一页最后一条数据的排序字段值，下一页查询时带上这个值做条件，比如：

SELECT * FROM order_info WHERE create_time < '2024-06-01 10:00:00' ORDER BY create_time DESC LIMIT 20;

这种方案每页只扫描目标区间内的数据，不管翻到第几页，扫描行数都不会膨胀。它的代价是需要业务方接受“不能跳页”的交互变化，适合移动端下拉加载这种场景。多数项目里，延迟关联和游标分页可以同时保留，一个给PC端跳页用，一个给移动端加载更多用。

6. 方式五：JOIN与子查询优化

6.1 小表驱动大表

多表查询的性能重点在于驱动表的选择和关联字段的索引。在JOIN中，MySQL一般会先读取一张表，然后拿着关联字段的数据去另一张表里匹配。所谓小表驱动大表，就是让数据量小、扫描成本低的那张表作为驱动表，用驱动表的每一行去大表里按索引查找，这样被驱动表的查找次数等于驱动表的行数，扫描次数才可控。

举个例子，一个只有一百行的分类表和一个一千万行的商品表做关联，应该让分类表做驱动表，拿一百个分类ID去商品表的索引里查，总共一百次索引查找。如果反过来让商品表做驱动表，那就得拿一千万行去分类表里匹配，成本完全不可同日而语。MySQL优化器大部分时候会自动选对，但表结构复杂、统计信息不准的时候也会选歪。这时候可以通过STRAIGHT_JOIN或者调整SQL结构来干预，不过在干预之前一定要用EXPLAIN确认驱动顺序确实是错的。

6.2 JOIN字段的类型与索引要求

被驱动表的关联字段上必须建索引，否则MySQL每拿一行关联值，都得对被驱动表做全表扫描来匹配，这就是经典的嵌套循环地狱。关联字段的类型要一致，int对int，varchar对varchar，长度和字符集也要一致，否则会出现隐式转换，索引照样失效。我接手过一个历史项目，用户表的id是int类型，订单表里存的user_id字段却是varchar，两条表JOIN的时候MySQL把int转成varchar去匹配，导致用户表的主键索引完全用不上，查询直接变成全表关联。改表结构很麻烦，最后是通过在SQL层面先显式CAST对齐类型解决的，但这只是无奈之举，最好在设计阶段就保持字段类型统一。

6.3 避免在JOIN中使用函数

在JOIN的ON条件或者被驱动表的关联字段上套函数，是另一个常见的性能杀手。比如ON DATE(a.create_time) = b.create_date，这种写法会导致a表创建时间字段的索引无法利用，必须全表计算。解决办法是把函数从索引字段上移走，改成范围条件。如果确实需要对日期格式化后关联，建议把格式化的结果冗余存储到一个字段里，然后直接等值关联。冗余字段虽然占一点空间，但换来的查询性能提升非常明显。

关于子查询的改写要特别强调一点：在MySQL 5.6版本之前，子查询的性能普遍比较差，很多子查询会被重写成逐行执行的方式。新版本优化器对子查询的处理已经很好了，但依然建议用EXPLAIN确认执行方式。看到DEPENDENT SUBQUERY这个关键词就要警惕了，它意味着子查询对外层每一行都重新执行一次，这种一定要改成JOIN或者用临时表的方式处理。我之前处理过一个统计报表，里面三个关联子查询嵌套，EXPLAIN里全是DEPENDENT SUBQUERY，改成JOIN加临时表之后，整个报表从跑几分钟变成几秒钟。

7. 常见问题与排查技巧实录

7.1 常见问题速查表

把我在项目里遇到过的典型问题和排查思路整理成了一张表，方便你遇到类似情况时快速定位：

7.2 我的几个实战建议

排查慢SQL第一步永远是开慢查询日志。MySQL的slow query log配置好之后，把超过阈值的SQL都记录下来，线上问题就不会无从下手。一般业务系统把long_query_time设成1秒或者0.5秒比较合理，太低日志量太大，太高又容易漏掉真正的问题SQL。拿到慢SQL之后，先从EXPLAIN开始分析，不要上来就改索引或者改代码结构，先搞清楚执行计划再动手。

优化索引的时候，核心看两个维度：一是查询字段的过滤性，二是查询频率。过滤性高但查询频率极低的字段，建索引的意义不大；查询频率极高但过滤性很差的字段，需要结合联合索引的前缀设计来考量。多字段组合查询场景，一定要善用联合索引而不是每个字段单独建索引，后者既浪费空间又形成冗余。在测试环境验证索引效果的时候，要把数据量造到线上量级，数据少的时候索引和全表扫描根本看不出差别。

优化完SQL之后，不要只看单次执行时间，还要关注执行计划是否稳定。MySQL优化器会根据统计信息选择执行计划，统计信息不准确的时候，偶尔会出现一个之前很好的SQL突然走错计划的情况。定期更新表的统计信息是数据库日常维护的一部分，业务高峰期之前做一次ANALYZE TABLE，能避免很多莫名其妙的性能波动。

还有一点我想特别提醒：SQL优化和业务逻辑设计是绑在一起的。很多时候一条SQL怎么都优化不动，是因为业务需求本身允许了不必要的计算量。比如一个实时统计接口，业务上其实可以接受五分钟内的延迟，那完全没必要在数据库里跑复杂的实时聚合，换成定时汇总表或者缓存层的方案，比任何SQL优化都有效。先看业务逻辑，再看SQL写法，最后才谈具体优化手段，这个顺序能帮你避免在错误的方向上浪费大量时间。

多次调优下来我的体会是，SQL优化没有银弹，也不存在一条所有场景都成立的万能规则。真正有价值的能力，是你拿到一条慢SQL后能快速定位瓶颈方向，知道该往哪个环节下手。EXPLAIN、索引、字段精简、分页、JOIN改写这五类手段，基本上覆盖了日常90%以上的优化需求。建议你下次在处理慢SQL的时候，把这五类手段当作一个固定的检查清单过一遍，配合慢查询日志反复验证，很快就能建立起自己的优化直觉。另外平时写新业务的SQL，也按照这套思路来，从源头上减少慢SQL出现的概率，比事后救火要舒服得多。