MySQL 索引优化实战:看懂 EXPLAIN,慢查询从 3 秒到 30 毫秒

有张订单表,上线头一年一直跑得飞快,直到数据涨到两千多万行,一个再普通不过的查询——按用户 ID 查他最近的订单——突然慢到了三秒多,接口动不动就超时。我第一反应是"加索引啊",于是给 user_id 建了索引。结果呢?快了一点点,还是要一秒多,远没到该有的样子。那一刻我才意识到,自己对索引的理解一直停留在"加了就快"这种朴素信仰上,根本没搞懂它底下到底是个什么东西、为什么有时候加了也白加。

那天我把这条 SQL 用 EXPLAIN 摊开,顺着 B+ 树、回表、覆盖索引、最左前缀一路啃下来,最后把那条查询从三秒压到了三十毫秒——靠的不是加更多索引,而是建对一个索引。这篇就把这趟从"加了索引还是慢"到"三十毫秒返回"的排查过程讲清楚:索引到底是棵什么树、为什么会"回表"、什么叫"覆盖索引"、联合索引的"最左前缀"是怎么回事,以及那些"加了索引却不走"的高发场景。看完你应该能做到:不再迷信"加索引"三个字,而是能用 EXPLAIN 看清一条 SQL 到底走没走索引、为什么。

先认清:索引失效的高发场景,先有个全局印象

"加了索引却没用"几乎是每个后端都撞过的墙。在深入原理之前,先把最常见的几类"索引失效"摆出来,心里有个谱——后面的原理,基本都是在解释这张表里的"为什么":

这张表里其实藏着一条主线:索引的本质是一棵"按索引列排好序的树",它所有的快,都来自"有序"二字;而上面每一种失效,本质都是某个操作把这份"有序"破坏了或用不上了。理解了这棵树长什么样、查一次要付出什么代价,这些场景就不用死记硬背了。下面先从"怎么看一条 SQL 有没有走索引"这个最实用的工具说起。

第一件事:学会看 EXPLAIN,别再靠猜

判断一条 SQL 走没走索引、走得好不好,唯一可靠的办法是 EXPLAIN,而不是凭感觉。它会把 MySQL 的执行计划摊给你看。我当时那条慢查询,EXPLAIN 一上来就暴露了问题:

-- 当时的慢查询:查某用户最近的订单,按时间倒序取 10 条
EXPLAIN SELECT * FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 10;

-- 输出里几个要命的信号:
-- type:  ref        ← 用上了 user_id 索引(还行,不是 ALL 全表扫)
-- key:   idx_user   ← 走的是 user_id 这个单列索引
-- rows:  8000       ← 估算要扫 8000 行(这个用户历史订单多)
-- Extra: Using filesort  ← ❌ 致命!排序没用上索引,在内存/磁盘临时排序

关键就在 Extra 那一栏的 Using filesort。它的意思是:虽然 user_id 的索引帮我快速找到了这个用户的 8000 条订单,但 ORDER BY created_at DESC 这个排序没有任何索引能用,于是 MySQL 只能把这 8000 行全捞出来,在内存里(放不下就落磁盘)重新排一遍序,再取前 10 条。慢就慢在这"把 8000 行捞出来重排"上。读 EXPLAIN 时,这几个字段是重点:

-- EXPLAIN 必看的几个字段,以及它们的"好坏"信号
-- type:    访问类型,性能从好到差大致是:
--          system > const > eq_ref > ref > range > index > ALL
--          看到 ALL(全表扫)或 index(扫整棵索引)基本就要警惕了
-- key:     实际用到的索引;为 NULL 说明压根没走索引
-- rows:    预估要扫描的行数,越小越好,数量级很重要
-- Extra:   最有信息量的一栏,重点关注这几个:
--   Using index       ✅ 覆盖索引!数据全在索引里,不用回表,最理想
--   Using where       走了索引但还要额外过滤,通常正常
--   Using filesort    ❌ 额外排序,排序列没被索引覆盖
--   Using temporary   ❌ 用了临时表,常见于 group by / distinct,要优化

所以排查的第一步永远是 EXPLAIN:先看 key 有没有走索引、type 是不是退化成了 ALL、rows 是不是大得离谱、Extra 里有没有 Using filesort 或 Using temporary 这种红灯。看懂这四个字段,一条 SQL 慢在哪,八成就有数了。我那条查询的病根已经清楚:索引只解决了"找到这个用户",没解决"按时间排序"——而要把这两件事一次解决,得先搞懂索引这棵树到底长什么样。

第二件事:搞懂聚簇索引、二级索引和"回表"

要理解索引为什么有时快、有时白加,得先知道 InnoDB 里数据到底是怎么存的。InnoDB 的表数据本身就是按主键组织成一棵 B+ 树的,这棵树叫聚簇索引——它的叶子节点直接存着整行数据。而你给 user_id 之类的普通列建的索引,叫二级索引,它是另一棵 B+ 树,叶子节点里存的不是整行,而是索引列的值 + 这一行的主键。这个区别,正是"回表"的根源。下面这张图就是一次"走二级索引但要回表"的查询全过程:

看懂这张图,"回表"就不神秘了:走二级索引只能拿到主键,如果你要的列它没有,就得拿着主键再回聚簇索引树里捞一次整行——这多出来的一次查找,就是回表的代价。当二级索引筛出的行很多时(比如开头那个用户有 8000 条订单),就要回表 8000 次,慢就慢在这。这也解释了为什么 SELECT * 常被诟病:它逼着查询必须回表去取那些用不上的列。

第三件事:用覆盖索引和最左前缀,一次解决"找"和"排"

既然回表这么贵,有没有办法不回表?有,这就是覆盖索引:如果一个查询需要的所有列,二级索引里都有了,MySQL 就不必回表,直接从索引树返回结果,EXPLAIN 的 Extra 会显示那个梦寐以求的 Using index。而要让一个索引覆盖多个列、还能同时解决排序,靠的是联合索引。回到开头那条慢查询,真正的解药不是给 user_id 单独建索引,而是建一个 (user_id, created_at) 的联合索引:

-- ❌ 原来的单列索引:能定位用户,但排序得 filesort
CREATE INDEX idx_user ON orders(user_id);

-- ✅ 联合索引:把"过滤列"和"排序列"按顺序放进同一棵树
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- 再 EXPLAIN 同一条查询:
-- SELECT * FROM orders WHERE user_id=12345 ORDER BY created_at DESC LIMIT 10;
-- type:  ref
-- key:   idx_user_created
-- Extra: (不再有 Using filesort!)  ← ✅ 排序直接用索引的有序性完成

为什么 (user_id, created_at) 就能干掉 filesort?因为联合索引这棵 B+ 树,是先按 user_id 排序、user_id 相同的再按 created_at 排序的。所以当你 WHERE user_id=12345 定位到这一段后,这一段里的记录天然就是按 created_at 有序排列的——MySQL 直接顺着读、取前 10 条就行,根本不需要额外排序。这就是联合索引的威力:它把"过滤"和"排序"两件事,用一棵树的有序性一次性解决了。

但联合索引有个铁律必须懂——最左前缀原则。一个 (a, b, c) 的联合索引,只有当查询条件从最左边的列开始、且连续时才用得上。这是因为树是先按 a 排的,a 相同才按 b 排:

-- 假设有联合索引 (a, b, c)
WHERE a=1                      -- ✅ 用上 a
WHERE a=1 AND b=2              -- ✅ 用上 a,b
WHERE a=1 AND b=2 AND c=3      -- ✅ 用上 a,b,c(最理想)
WHERE a=1 AND c=3              -- ⚠️ 只用上 a;c 因为跳过了 b,用不上
WHERE b=2 AND c=3              -- ❌ 跳过了最左的 a,整个索引都用不上!
WHERE b=2                      -- ❌ 同上,不走这个索引

-- 还有个隐蔽的坑:范围查询会"截断"后面的列
WHERE a=1 AND b>10 AND c=3     -- ⚠️ 用上 a 和 b(范围),但 c 用不上
                               -- 因为 b 用了范围,b 相同这个前提没了,c 在树里就无序了

记住这个画面就不会错:联合索引像按"先 a、再 b、再 c"排序的电话簿,你必须从 a 开始按顺序查,一旦跳过某一列、或某一列用了范围查询(>、<、LIKE),它后面的列就失去了有序的前提,索引到那里就用不下去了。所以建联合索引时,列的顺序极其讲究:把等值查询、区分度高的列放左边,范围查询的列放右边,才能让尽可能多的列吃上索引。

第四件事:那几种"加了索引却不走"的高发坑,逐个拆掉

原理铺垫完,回头看开篇那张失效表,就能逐条解释、逐条拆解了。这几种是线上出现频率最高的,几乎每个人都中过招:

-- ❌ 坑一:在索引列上套函数 / 做运算 —— 索引里存的是原值,不是算过的值
WHERE YEAR(created_at) = 2026;          -- 索引失效,全表扫
-- ✅ 改成范围查询,让索引列保持"裸露"
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- ❌ 坑二:隐式类型转换 —— phone 是 varchar,却传了数字
WHERE phone = 13800138000;              -- MySQL 把每行的 phone 转成数字再比,索引废了
-- ✅ 类型对齐,该加引号就加引号
WHERE phone = '13800138000';

-- ❌ 坑三:LIKE 以通配符开头 —— 前缀不定,无法在有序树里定位起点
WHERE name LIKE '%张三%';                -- 失效
-- ✅ 前缀确定的 LIKE 能走索引;真要中间模糊,考虑全文索引/ES
WHERE name LIKE '张三%';                  -- 这个可以走

-- ❌ 坑四:OR 一边没索引 —— 只要有一边没索引,整体退化成全表扫
WHERE user_id = 1 OR remark = 'x';      -- remark 无索引 → 全表扫
-- ✅ 两边都建索引,或用 UNION 拆开让各自走各自的索引
SELECT ... WHERE user_id = 1
UNION
SELECT ... WHERE remark = 'x';

把这四个坑串起来看,会发现它们其实是同一句话的不同变体:任何让索引列"不再是它本来的、有序的样子"的操作,都会让索引失效。套函数、做运算改变了值;隐式转换偷偷在每行上套了一层转换函数;前导 % 让你无法确定从树的哪个位置开始找;OR 把一个能用索引的条件和一个不能用的捆在一起,只能就低不就高。理解了"索引靠有序",这些就不再是需要背的规则,而是能推导出来的结论。

第五件事:索引下推能少回表,但索引不是越多越好

MySQL 5.6 之后有个很值得一提的优化叫索引下推(ICP),它能让联合索引在"用不上最左前缀"的列上也榨出价值,减少回表次数:

-- 联合索引 (last_name, age),查询:
SELECT * FROM users WHERE last_name = '张' AND age = 30;

-- 没有索引下推时:用 last_name 在索引里定位到所有姓张的,拿主键全部回表,
--   回表后再逐行判断 age=30 —— 姓张的若有 1 万人,就要回表 1 万次
-- ✅ 有索引下推时:age 也在这个索引里!MySQL 在索引内部就先用 age=30 过滤,
--   只把真正满足的少数行才回表 —— EXPLAIN 的 Extra 会显示 Using index condition

索引下推的价值在于:虽然 age 因为前面是等值的 last_name 仍能用上索引,但即便在某些它"参与过滤却不能用于定位"的场景,MySQL 也会在存储引擎层、回表之前,先用索引里已有的列做一轮过滤,把回表的行数尽量压小。这是个默认开启的优化,你不用做什么,但理解它能帮你看懂 Using index condition 这个 Extra。

不过,索引带来的全是好处吗?并不是。这是另一个极其普遍的误区:觉得"反正索引能加速查询,那就多建几个"。索引是有成本的,而且成本不低:

-- 索引的代价,建之前必须想清楚:
-- 1. 写放大:每个索引都是一棵 B+ 树,INSERT/UPDATE/DELETE 时
--    每一棵相关的树都要跟着维护、可能分裂/合并节点 —— 索引越多,写越慢
-- 2. 占空间:索引本身要占磁盘,大表上几个联合索引能占掉可观的存储
-- 3. 低区分度的列建索引几乎没用:
--    比如 gender(只有'男'/'女')、status(只有几个值),
--    一个值就对应半张表,优化器算下来还不如直接全表扫,索引白建
-- 经验:区分度 = 不重复值数量 / 总行数,越接近 1 越值得建索引
SELECT COUNT(DISTINCT status) / COUNT(*) FROM orders;  -- 算算区分度再决定

所以建索引前要权衡:这张表是读多还是写多?这个列的区分度高不高?这个查询的频率值不值得用一棵树的写入代价去换?读多写少、高区分度、高频查询的列,值得建;写极频繁、低区分度(像性别、状态这种就几个值)的列,建了往往是负担。索引是用"写的代价和存储"去换"读的速度",不是免费的加速符。

一张图把"这条 SQL 为什么慢"走一遍

把前面所有东西串起来,其实就是一条遇到慢查询时机械可走的排查路径。下次再碰到某条 SQL 慢,别急着乱加索引,照这棵树从 EXPLAIN 开始往下走,绝大多数索引问题都能定位到根:

这棵树的入口判断最关键:先用 EXPLAIN 把"走没走索引"和"走了但好不好"这两件事分开。没走索引,就去查是哪种失效场景(函数、类型、最左前缀、LIKE),对症改写;走了索引但还慢,就看 Extra 的红灯——filesort 就把排序列纳入联合索引,回表太多就上覆盖索引。改完一定要再 EXPLAIN 一遍确认,别凭感觉以为优化了。这条路几乎是机械的,走顺了,你对慢查询就从"碰运气加索引"升级成了"看着执行计划下刀"。

沉淀成几条建索引、写查询的铁律

1. 慢查询先 EXPLAIN,再动手:用 key/type/rows/Extra 四个字段判断病根,绝不靠猜加索引;改完再 EXPLAIN 验证。
2. 能覆盖就覆盖,别滥用 SELECT *:让查询需要的列尽量都进索引,拿到 Using index 免回表;SELECT * 会强制回表、还可能撑垮覆盖索引的设计。
3. 联合索引按"等值在前、范围在后、排序列垫底"排列:遵守最左前缀,把区分度高的等值列放最左,范围列放后面,排序列紧跟过滤列以消除 filesort。
4. 别在索引列上套函数、做运算、留隐式转换:保持索引列"裸露",把 YEAR(col)=x 改成范围、给字符串列的值加引号。
5. 低区分度的列不建索引:性别、状态这类就几个取值的列,索引几乎无效;先用区分度公式估一下再决定。
6. 索引不是越多越好:每个索引都增加写入和存储成本,写密集的表尤其要克制;定期清理没被用到的冗余索引。
7. 大表加索引走线上工具:直接 ALTER 可能长时间锁表,用 pt-online-schema-change / gh-ost 之类的在线变更工具,避开业务高峰。

几个反复见到的认知误区

把这套方法分享出去时,有几个误区出现得特别频繁,值得专门点破。

第一个、也是最根本的:"加了索引,查询就一定会变快。" 这是开篇我自己栽过的跟头。索引能不能用上,取决于查询写得对不对、优化器愿不愿意走它。函数运算、隐式转换、不满足最左前缀、前导 % 的 LIKE,任何一个都能让你精心建的索引彻底失效。"建索引"和"用上索引"是两件事——前者是你建了一棵树,后者是这次查询真的走了这棵树,中间隔着一个 EXPLAIN 才能确认的鸿沟。

第二个误区:"索引越多,查询越快,那就多建点。" 索引是用写入和存储的代价换读取速度的,不是免费午餐。每多一个索引,每次增删改就要多维护一棵 B+ 树;低区分度的列建了也没用;冗余、重叠的索引还会干扰优化器选择。对写密集的表,过多索引甚至会让整体性能不升反降。索引要"少而精",每一个都该有明确服务的查询。

第三个误区:"SELECT * 和 SELECT 指定列,反正都要查这行,没区别。" 区别可能很大。当你只 SELECT 索引里已有的列时,查询能用覆盖索引直接从索引树返回、完全不回表;而 SELECT * 强制要取所有列,必然回表,在筛出大量行时,这一堆回表就是性能杀手。只取你需要的列,不只是"好习惯",它直接决定了能不能吃上覆盖索引这个大优化。

第四个误区:"主键用什么都行,UUID、随机串也无所谓。" 在 InnoDB 里大有所谓。因为表数据是按主键组织成聚簇索引的,主键若是随机无序的(如 UUID),每次插入都可能落在 B+ 树的中间,引发频繁的页分裂和数据迁移,既慢又让页变得稀疏;而自增主键是顺序追加,新数据永远插在树的最右边,代价最小。主键的选择直接影响整张表的写入性能,这是很多人完全没意识到的隐性成本。

附:一个高频的"深分页"慢查询,顺手也治了

把索引这棵树看明白之后,很多看似无关的慢查询也跟着有了解法。最典型的就是深分页——翻到第几万页时突然奇慢。它的病根和回表是同一个:

-- ❌ 深分页:OFFSET 越大越慢。下面这条要先扫描+回表 100010 行,再扔掉前 10 万行
SELECT * FROM orders WHERE user_id = 12345
ORDER BY created_at DESC LIMIT 100000, 10;
-- 慢在:为了拿到第 10 万行之后的 10 条,前面 10 万行也都被实实在在地回表捞了出来

-- ✅ 延迟关联:先在覆盖索引里只翻主键(不回表),定位到那 10 个主键,再回表取整行
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders WHERE user_id = 12345
    ORDER BY created_at DESC LIMIT 100000, 10   -- 子查询只走索引、只取 id,几乎不回表
) t ON o.id = t.id;

这招叫延迟关联:让分页定位这一步只在二级索引里翻主键(覆盖索引、不回表,极快),等真正确定了要哪 10 行,才拿这 10 个主键去回表取完整数据——把"回表 10 万次"压缩成了"回表 10 次"。这背后还是那套老道理:回表很贵,能在索引里解决的就别回表,实在要回也只回最少的行。你看,一旦把树和回表想透,新问题往往不需要新知识,旧的那几把钥匙就够用了。

写在最后

回到开头那条从三秒到三十毫秒的查询。最终的改动小得可笑——把 idx_user(user_id) 删掉,换成 idx_user_created(user_id, created_at),一个联合索引,既负责"找到这个用户",又用树的有序性顺手完成了"按时间排序",filesort 消失,回表也因为命中范围小而大幅减少。代码没动一行,SQL 没动一个字,只是把索引建对了。

这件事给我最大的改变,是不再把"加索引"当成一句口头禅式的万能药。索引底下是一棵 B+ 树,它的所有魔力都来自"有序",它的所有代价都来自"维护这份有序";覆盖索引省的是回表,最左前缀守的是有序的连续性,失效的那些场景无一不是破坏了有序。当你脑子里有了这棵树的样子,再看任何一条慢查询,都不再是凭感觉撞运气,而是能用 EXPLAIN 把它的执行计划摊开,一眼看出它走没走索引、为什么、该怎么救。从"加了索引还是慢"的困惑,到"看着执行计划下刀"的笃定,中间隔着的,不过是把索引这棵树真正看明白而已。

—— 别看了 · 2026