MySQL索引优化实战：从慢SQL到执行计划的调优全攻略

生产告警群在凌晨两点炸了。核心订单表的慢查询数从每分钟几十条跳到几千条，数据库 CPU 飙到 99%，后面排队的接口一个接一个超时。翻开慢日志，罪魁祸首是一条分页 SQL，而这 SQL 的查询条件其实对应着现成的索引。

这大概是做 SQL 调优最反直觉的地方：明明有索引，查询还是慢；数据量在几百万的时候毫无感觉，一过千万就全线崩溃。索引策略从来不是“加个索引”这么简单，建错索引、用错索引、设计索引时没考虑查询模式，代价都会在数据量上来之后一次性爆发。

这篇文章我把这些年处理过的慢 SQL 场景浓缩成了一套可复用的排查方法：从索引失效的底层逻辑，到执行计划的每个关键字段，再到四个有代表性的实战案例和索引运维清单。不管你是后端开发、数据分析师还是兼职 DBA，看完都能直接拿去解决实际问题。我尽量不堆理论，每个结论都给现场。

1. 一个深分页拖死整个库之后：先从事故认识索引

1.1 事故现场：LIMIT 一百万行到底发生了什么

那次事故的 SQL 长这样：

SELECT order_no, pay_amount, create_time, receiver_name FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 1000000, 20;

单看这条 SQL 没有任何毛病，where 条件 status 上有索引，排序字段 create_time 也有索引。但问题是 status = 1 的订单有 55 万行，LIMIT 偏移量到了一百万，MySQL 必须先把前 1000020 行全部找出来，再丢掉前 100 万行，只返回最后 20 行。这个查询在订单表 200 万行时耗时 200 多毫秒还能忍，等表涨到 1200 万行，耗时直接变成 2.8 秒，接口超时阈值是 1 秒，于是雪崩。

这里的瓶颈不在单条数据检索上，而在“无效扫描+回表”的组合。InnoDB 的二级索引叶子节点只存索引列和主键值，要拿 order_no、pay_amount 这些非索引列，必须拿着主键再去聚簇索引查一行完整数据，这个动作叫回表。一页只返回 20 条数据的接口背后，MySQL 实际回表了几十万行。

1.2 B+ 树选路的本质：为什么索引不是万灵药

要理解索引为什么失效、为什么深分页无解，得稍微看一下 B+ 树选路的本质。

MySQL 里 InnoDB 的索引底层是 B+ 树，每个节点对应一个 16KB 的页。主键索引的叶子节点存的是整行数据，二级索引的叶子节点存的是索引键值加主键值。B+ 树的扇出很大，一个 16KB 的页能放下上千个键值对，所以一棵三层高的 B+ 树就能支撑上千万行数据。查询一条记录，走主键索引通常只需要 3 次磁盘 I/O，这就是索引快的原因。

但也得看清另一面：索引树是独立于表数据存在的物理结构。每多建一个索引，插入、更新、删除时就要多维护一棵 B+ 树。二级索引从定位到主键再到回表取完整行，中间同样有成本。很多“加了索引还是很慢”的问题，本质上就是索引能帮你缩小范围，但缩小后的范围依然大得离谱，或者范围不大却要回表太多次。搞清楚这两点，后面的优化才算真的能落地。

1.3 索引分类与设计索引前的两个问题

动手设计索引之前，先问两个问题：这张表主要的查询条件是什么？查询之后用户真正要拿哪些列？这两个问题的答案决定了一个索引值不值得建。

从结构上分，索引有主键索引、唯一索引、普通索引、联合索引、全文索引、空间索引这几类。日常调优最常用到的是普通索引和联合索引。InnoDB 里主键索引就是聚簇索引，数据按主键顺序存放；二级索引都是非聚簇索引，独立存储，叶子节点指向主键值。

从使用效果上分，索引可以做到覆盖查询、排序、分组、去重这四件事。一个设计得当的联合索引，往往能同时满足等值查询和排序需求，让排序不再单独走 filesort；一个覆盖索引能让查询完全不回表，把 I/O 压到最低。设计索引时如果只盯着 where 条件，忽略了 select 列和 order by 排序，这个索引大概率还能再优化一层。

2. 索引策略的底层逻辑：失效场景全复盘

2.1 最左前缀原则：联合索引为什么不能跳列

联合索引 (a, b, c) 的匹配规则是最左前缀。什么意思呢？查询条件里只有 a，能走这个索引；只有 a 和 b，也能走；但只有 b 或者只有 c，走不了这个索引。更微妙的是，当 a 和 b 用的是范围查询时，c 的匹配会被打断；只有 a 是等值、b 是范围，c 恰好也是等值，这种组合才可能继续用到 c 的索引列。

很多调优事故出在联合索引列的顺序上。比如有个表建了索引 (status, create_time)，日常查询是 where status = 1 order by create_time desc limit 20，这个索引同时解决了筛选和排序，很好。

但如果有人改成 where status = 1 and pay_type = 2 order by create_time desc，而索引还是 (status, create_time)，那 pay_type 的条件就只能做回表后的过滤了。这种情况应该建 (status, pay_type, create_time) 三列联合索引，把等值条件放在前面，排序字段跟在后面。等值字段放前、范围字段放后、排序字段放在合适的位次，这是联合索引设计最基本的排序规则。

MySQL 8.0 引入了索引跳跃扫描，在某些条件下可以跳过联合索引最左列直接使用后面列，但限制比较多，比如前面列的选择性要低、查询要能匹配到足够小的范围。实际调优不要指望它兜底，该重排索引顺序就重排。

2.2 隐式类型转换和函数运算：不改 SQL 也能杀掉索引

索引列参与运算，是另一种常见的“索引杀了等于没杀”的坑。

最典型的是隐式类型转换。表里 mobile 字段定义是 varchar，查询条件写 where mobile = 13800138000，数字类型和字符串类型比较时，MySQL 会把字符串转成数字再比较，等于对索引列做了 cast，索引直接失效。另一个更隐蔽的场景是字符集不一致，两张表的关联字段一个 utf8mb4 一个 utf8，关联时同样会触发索引列上的隐式转换，导致关联查询走不动索引。这个用 explain 看不出来，得检查表结构对比字符集。

函数运算同理。where date(create_time) = '2024-05-01' 看着很直观，但 create_time 上的索引完全用不上，因为优化器没法对一个列的函数结果建立索引匹配。正确写法是范围查询：

WHERE create_time >= '2024-05-01 00:00:00' AND create_time < '2024-05-02 00:00:00'

这个改动对窗口内查询几乎无损命中索引。我见过不少统计报表被 date 函数拖慢的案例，一张千万级日志表，把函数写法改成范围写法后，查询时间从 5 秒降到 0.2 秒。排查时如果 discover 某条 SQL 执行计划是全表扫描，先优先检查 where 条件里是不是有函数、隐式转换、通配符前导 % 这类优化器“看不懂”的用法。

2.3 回表、覆盖索引与索引下推：Extra 里的隐藏信息

执行计划里的 Extra 字段，很多人扫一眼就略过，但这里藏着大部分调优线索。

Using index 表示查询所需列全部在索引树里，不需要回表，这就是覆盖索引。典型场景是 select 只查索引列，比如联合索引 (status, create_time) 上执行 select status, create_time from orders where status = 1，直接读索引即可。调优统计类 SQL 时，把 select 改写为只查覆盖列，会让全索引扫描成本明显下降。

Using index condition 对应索引下推。MySQL 5.6 以后的优化，允许在索引遍历过程中就把一部分 where 条件过滤掉，减少回表次数。比如联合索引 (status, pay_type)，查询 where status = 1 and pay_type in (2, 3)，回表前就能过滤掉不对的 pay_type。看到这个字段说明索引使用还算健康，但还能检查是否覆盖了 select 的所有列。

最危险的是 Using filesort 和 Using temporary。前者代表排序没法走索引，只能额外排一次；后者代表查询使用了临时表，常见于 group by 或 distinct 没走索引的情况。这两兄弟出现时，SQL 离“需要优化”已经不远了，下文案例 4.3 再展开。

3. 执行计划是调优的放大镜：explain 关键字段逐项拆解

3.1 type 字段：访问类型的代价排序

执行计划里最重要的字段我始终认为是 type，它描述 MySQL 找到目标行的方式。代价从好到坏大致是 system、const、eq_ref、ref、range、index、ALL。

system 是只有一行数据的特殊情况，实际很少见。const 是主键或唯一索引等值匹配，最多返回一行，这是最理想的状态。eq_ref 常见于关联查询中，被驱动表用主键或唯一索引做等值关联。ref 是非唯一索引等值匹配，可能返回多行，但范围已经锁得很小。range 是索引范围扫描，比如 between、in、大于小于的查询。

index 代表全索引扫描，遍历整棵索引树，虽然不用回表，但索引树有多大就扫多少，通常出现在覆盖索引但条件无法进一步筛选的情况。ALL 是全表扫描，每分钟扫百万行并不夸张。调优的基本目标就是尽量让 type 能从 ALL 和 index 提升到 range 甚至 ref。看到 ALL 别急着加索引，先确认是不是函数、隐式转换这种“SQL 写法杀索引”的问题，如果是，改掉写法比加索引更彻底。

3.2 rows 与 filtered：估算值的两条腿

rows 是优化器估计要扫描的行数，filtered 表示经过条件过滤后剩余的比例，两者相乘可以粗略估计最终返回的行数。注意这是基于统计信息的估算，不是实际值。如果统计信息没有及时更新，或者索引基数跳变，估算可能偏差很大，导致优化器选错执行计划。

排查慢 SQL 时，对比估算 rows 和实际返回行数很有用。如果估算行数只有几百，实际却返回几十万，说明统计信息严重失真，跑一下 analyze table 重新收集统计信息往往就能解决。另一类问题是索引基数不佳，比如 sex 字段只有两个值，选择性太差，优化器宁可扫全表也不走索引，这属于低选择性字段不适合单列建索引，更适合放进联合索引做前缀过滤。

3.3 一次完整执行计划走查：从派生表到索引命中

拿文章开头事故里那类 SQL 做一个完整走查。假设订单表结构如下：

执行这条查询 explain，关键字段大概长这样：

EXPLAIN SELECT order_no, pay_amount, create_time, receiver_name FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 500000, 20;

这里已经能看出两个问题：rows 55 万行实在太多，而且 Extra 里有 filesort。为什么会 filesort？虽然 create_time 有单列索引，但 MySQL 只能同时使用一个索引做访问路径，选了 idx_status 就无法同时利用 idx_create_time 排序，于是只能在内存或磁盘里对 55 万行做排序。解决思路是把 status 和 create_time 放进同一个联合索引 (status, create_time)，这样既能用 status 定位，又能直接用索引顺序完成排序，filesort 才能彻底消失。

4. 实战案例复盘：四个慢 SQL 从定位到提速的完整过程

4.1 案例 A：深分页优化——延迟关联与游标翻页

场景是订单管理后台的分页列表，翻到 50 页以后接口响应超过 3 秒，监控里持续告警。现象在第 1.1 节里已经说过，核心问题就是 LIMIT 偏移量过大导致的无效扫描与回表。

第一个可行的改法是延迟关联：先利用覆盖索引快速定位主键，再关联回原表取完整行，把回表动作延迟到确定要返回的那 20 行之后。

SELECT o.order_no, o.pay_amount, o.create_time, o.receiver_name FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 500000, 20 ) t ON o.id = t.id ORDER BY o.create_time DESC;

子查询里 select id 可以走到联合索引 (status, create_time) 上，排序也顺手解决，扫描的 55 万行不需要回表。但问题还在：50 万行扫描加排序的成本依然存在，只是不用回表而已。数据库配置差一点，这个方案也只能把 3 秒压到 1 秒出头。

更彻底的方案是游标翻页，也就是把“页数偏移”换成“上一页的最后一条记录”。每次查询带上上一页最后一条数据的 create_time 和 id，利用联合索引的有序性直接定位到目标位置，MySQL 只需要扫 20 行。

SELECT order_no, pay_amount, create_time, receiver_name FROM orders WHERE status = 1 AND (create_time, id) < ('2024-05-01 23:59:59', 10086) ORDER BY create_time DESC, id DESC LIMIT 20;

这个写法对客户端来说和传统翻页不兼容，但它彻底绕开了深分页这个无解命题。实测同一列表第 50 页数据，传统 LIMIT 查询 2.8 秒，改成游标翻页后稳定在 0.02 秒。业务端只要能用“加载更多”替代页码跳转，我强烈建议用这个方案。

4.2 案例 B：OR 条件让索引失效，改写后查询时间从秒级到毫秒级

一条订单管理 SQL 查询某门店的待付款或待发货订单，写法是这样：

SELECT id, order_no, status FROM orders WHERE store_id = 123 AND (status = 1 OR status = 2) ORDER BY create_time DESC LIMIT 20;

执行计划显示 type 是 ALL，全表扫描。为什么明明 store_id 和 status 上都有索引，OR 却把索引杀死了？因为 OR 的语义是“满足任一条件即可”，优化器要把 store_id = 123 且 status = 1 和 store_id = 123 且 status = 2 两个集合做合并。如果两个条件各自能走索引，优化器可以使用索引合并，但案例里 store_id 是单列索引，status 是单列索引，合并后的代价不一定比全表扫描低，于是优化器直接选择 ALL。

改写思路有两个方向。条件集合小的话，最简单的是把 OR 换成 IN：

WHERE store_id = 123 AND status IN (1, 2)

IN 会触发索引范围扫描，type 变成 range，因为 status 和 store_id 分开建索引还是不够优。更好的方案是建联合索引 (store_id, status, create_time)，等值 + 等值 + 排序，一次到位。

那 OR 是不是一定不能用？并非如此。当两个条件各自能高效命中的时候，比如用主键等值 OR 另一列的高选择性条件，优化器会考虑选择 index merge。实际调优里我通常会先用 explain 看 type 和 key_len，再决定改写为 IN、UNION 还是保持 OR。记住一句话：优化器给出的选择未必最优，但执行计划里出现 ALL 且 rows 巨大时，基本可以确定要动 SQL 写法或者索引结构。

4.3 案例 C：ORDER BY 排序慢，filesort 频发

运营后台有一张商品表，按门店加载商品列表，需要按更新时间倒序。SQL 简化后是这样：

SELECT id, product_name, price FROM products WHERE store_id = 456 ORDER BY updated_at DESC LIMIT 20;

执行计划里 Extra 出现 Using filesort，响应时间在翻页后飙到 1.2 秒。问题依旧是：store_id 有单列索引，但 updated_at 不在这棵索引上，排序只能额外做一次。给 store_id 和 updated_at 建联合索引 (store_id, updated_at)，MySQL 定位门店数据时，索引本来就是按 updated_at 排序好的，物理顺序直接可用，filesort 彻底消失。

改造后执行计划显示 type 是 ref，key 是新的联合索引，Extra 里不再有 Using filesort。这条 SQL 从 1.2 秒降到 0.03 秒，效果立竿见影。

这里有个细节值得多提一句：如果排序方向是 DESC，联合索引设计时可以不额外处理，因为 InnoDB 的索引可以在 B+ 树内部从后往前读取，并不需要单独建 DESC 索引。新版 MySQL 8.0 支持降序索引更好，但大多数场景下把排序字段放在等值字段之后，就已经能解决问题。

4.4 案例 D：统计查询让 COUNT 也飞起来

某报表功能每天跑一次全量统计，逻辑是统计某品类下近 30 天订单数。SQL 长这样：

SELECT COUNT(*) FROM orders WHERE category_id = 78 AND create_time >= '2024-04-01 00:00:00' AND create_time < '2024-05-01 00:00:00'

订单表 1200 万行，这条 SQL 跑了 4.6 秒。执行计划里 type 是 ref，其实已经走到了 category_id 的单列索引，但 count(*) 必须数完范围内所有记录，而且每一条还要回表判断是否满足 create_time 范围。真正的优化点是让这颗索引树本身就能过滤掉大部分错误数据。建联合索引 (category_id, create_time) 之后，等值 + 范围一次定位，执行计划 type 变成 range，key_len 加长，Extra 里出现 Using index，说明数据直接从索引读取，全程不需要回表。优化后耗时 0.85 秒。

如果表再大一个量级，比如上亿行，count 这种全量统计就该考虑换思路了。维护一张汇总表，每天定时把各品类的订单数累加进去，业务查询只读汇总表，响应直接做到毫秒级。索引优化本质上是降低单次扫描的成本，但当扫描本身就是设计问题时，换个数据组织方式是更优解。

5. 索引设计取舍与运维避坑清单

5.1 冗余索引和重复索引，删掉就是无本万利

很多表一边前期随手加了单列索引，后期为了覆盖某个联合查询又加了联合索引，结果单列索引成了联合索引左侧子集，完全被浪费。举一个真实的例子：某用户表有 idx_mobile 单列索引，后来又建了 idx_mobile_status (mobile, status)。前者完全属于后者的最左前缀，两张索引树维护的代价白白多付一份。

MySQL 5.7 以后可以利用系统库帮忙查垃圾索引：

SELECT * FROM sys.schema_redundant_indexes;

这张视图会直接告诉你哪些索引互相冗余、谁是保留对象、谁是可删对象。我在一次索引清理中，从一个 3000 万行的大表里发现 3 个冗余索引，删除后写入延迟明显下降，日志刷盘压力也小了。注意删除索引前一定先在测试环境确认没有代码路径依赖它，别删了之后半夜被叫醒。

5.2 写入放大：索引不是越多越好

每一次 insert、update 都要同步维护该表的所有索引。表上索引越多，写入放大越严重。典型的高并发写入表，订单流水、日志表、消息表，这些场景里每多一个索引，写入的 I/O 就多一层。单表索引数量到底多少合适没有绝对标准，但从业者经验上，OLTP 表把索引控制在 5 个以内是比较稳妥的节奏，超过这个数就得认真评估每个索引的使用收益。

我见过最离谱的一个配置表只有 6 个字段，建了 8 个索引，其中 6 个都是单列索引。写入每秒只到 300 行，数据库 I/O 就已经报警了。删掉 5 个冗余索引后，同样是 300 行每秒，写入耗时下降了 70%。所以加索引前先问一次：这个索引到底能支撑哪些高频查询？写得好还是一次性覆盖多个查询条件更好。

5.3 日常体检：慢日志、基数与统计信息

索引策略做得再好，数据量涨上来之后也会慢慢失效，日常体检很重要。第一件事是把慢查询日志开着，阈值从默认的 10 秒降到 1 秒，最好压到 0.5 秒，多抓几次样本比事后分析故障快照要主动得多。日志里反复出现某条 SQL，就要优先分析它的执行计划，看是不是索引选择性下降了。

第二件事是定期检查索引基数。执行 show index from 表名，看 Cardinality 字段。如果某索引基数远低于实际行数，说明这个列的重复值很多，查询命中范围可能很大，这时候要重新评估该索引是否还有存在价值。统计信息也别忘了更新，大表数据量剧烈变化后跑一次 analyze table，可以减少优化器误判的概率。

第三件事是建立变更记录。每一条 SQL 优化、每一次索引调整，都记录当时的执行计划截图和耗时数据，方便后续对比。数据库调优最怕的就是“凭感觉改”，有了历史数据，效果好坏一查便知。

写在最后的一点实操心得

做了这些年数据库优化，我最大的体会是：大多数慢 SQL 不是优化器不够聪明，而是当初设计索引时没有想清楚数据量涨上去之后的执行路径。加索引、调 SQL、重排执行计划，这些手段在千万级数据面前都只是基本功，真正考验人的是你能不能在写第一版 create table 的时候，就把查询模式和数据增长趋势想明白。

最后再分享一个小技巧：每次排查慢 SQL，别急着改代码，先在本地把 explain 拿下来，把 type、rows、Extra 三个字段拍个照，优化完再拍一张。对比着看效果，比任何“我觉得快了”都靠谱。这个习惯我坚持了很多年，数据库出问题的概率大幅下降，也希望你能用得上。