MySQL索引优化：从B+树原理到EXPLAIN慢查询排查

做数据库运维这几年，最常被问到的一个问题就是：为什么同样的 SQL，别人库上几十毫秒，我们库上跑出两秒多？排查到最后，十次里面有八次都落在索引身上。我第一次意识到 MySQL 索引不是“建了就没事了”的时候，正好赶上某后台项目的一个慢查询告警。当时那条语句逻辑不复杂，就是按用户、状态、时间翻订单，但线上查询从平时的 30 毫秒一路飙到 2 秒多。折腾了一晚上，最后加了一个组合索引，耗时直接掉回 30 毫秒。这次经历让我对这个标题特别有共鸣：索引真的就像是数据库的导航仪，它不改变你“开车”的极限速度，但它决定了你走的是国道还是高速。

拿导航来打比方特别贴切：一条 SQL 从客户端发到 MySQL，引擎要做的事本质上是在数据堆里找到你要的那几行。没有索引时，MySQL 只能从头到尾把一个表的数据翻一遍，这相当于没有导航，只能在城市里一条街一条街地扫；有了索引，引擎就能按照索引树上的路径，直接定位到目标数据附近，相当于提前规划好了路线，该走高架走高架，该走辅道走辅道。

本文就把 MySQL 索引这层“导航”拆开来看：B+ 树底牌是什么，建索引有哪些看得见摸得着的规则，EXPLAIN 怎么读，索引为什么经常“失效”，以及索引维护阶段最容易踩的坑。适合两种人：一种是刚接触 MySQL 优化、想系统搞懂索引原理的后端同学；另一种是被慢查询磨过几次，想从执行计划层面真正把索引用明白的开发者。

1. 慢查询告警背后，索引决定的是“绕路”而不是“车速”

1.1 一个典型的线上慢查询长什么样

先还原一下我前面说的那个场景。某个订单表的表结构大概长这样：

CREATE TABLE old_pay_order ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, amount DECIMAL(10,2) NOT NULL, KEY idx_user_status_ctime (user_id, status, create_time) ) ENGINE=InnoDB;

当时业务方反馈，后台列表页打开很慢，接口偶尔超时。抓到的 SQL 类似：

SELECT id, user_id, amount FROM old_pay_order WHERE user_id = 888 AND status = 2 AND create_time >= '2024-01-01' ORDER BY create_time DESC;

单看这条 SQL，条件里有 user_id、status、create_time，排序也是 create_time，看起来非常合理。一开始大家怀疑是表数据量太大了，但实际上这张表当时也就几百万行，问题出在索引上：线上只有主键索引和 user_id 上的单列索引，没有组合索引。user_id 过滤完之后，status 和 create_time 的条件只能靠回表后的逐行判断，排序也得单独用 filesort 处理。数据量一涨，这条路就走不通了。

1.2 “导航仪”这个比方的边界：索引不加速，只减少绕路

把索引比喻成导航仪，有一个很重要的边界条件：索引本身并不会让单次磁盘读取变快，它做的是“减少必须访问的数据量”。

MySQL 的最小存储单位是页，默认 16KB。一个几百万行的表，数据页分散在磁盘上。如果没有索引，一次全表扫描意味着从第一页翻到最后一页，即使数据都在内存里，CPU 也在做大量无意义的行比较。索引则是额外维护了一套“目录数据”，它本身也占用空间，也消耗写入成本，但换回来的是：查询时可以跳过绝大多数数据页，直接从根节点走到叶子节点，定位到目标行所在的数据页附近。

所以判断一个索引到底有没有价值，永远要回归到两个问题：它帮你过滤掉了多少比例的数据？它帮你省掉了多少回表和排序动作？如果一条 SQL 本来就只需要查全表的 50% 数据，索引带来的收益往往很有限，甚至不如直接扫表。这也是为什么我一直强调，索引选型不是“见一个 WHERE 加一个索引”，而是要围绕具体 SQL 的查询路径来做设计。

2. B+ 树是 InnoDB 的赌注：索引为什么能又快又省

2.1 从磁盘页的视角看，B+ 树输赢在“矮壮”

InnoDB 的索引默认用 B+ 树，也就是 balanced tree 的变体。它为什么能在一众数据结构里胜出？答案就三个字：矮、壮、顺序。

B+ 树是一种多路平衡搜索树，每个节点可以容纳多个键值，而不是像二叉树那样一个节点只分两叉。16KB 的页如果用来存储整型的索引键，一个节点就能放下成百上千个键。这样整棵树的层数会非常低。三到四层的 B+ 树，就能支撑几千万甚至上亿行数据的索引。

低层数直接意味着查询时的磁盘访问次数少。MySQL 访问一个索引页，本质上就是一次磁盘 I/O。一个三层 B+ 树，哪怕从根节点开始找叶子节点，也只涉及三次 I/O 左右。作为对比，如果用 AVL 树或者红黑树，几百万行数据可能要十几次甚至更多的树跳转，每次都对应一次随机 I/O。B+ 树在这个对比里几乎是无悬念的胜者。

另一个关键设计是：B+ 树的叶子节点用链表串起来，而且叶子内部的数据是按索引键有序排列的。这意味着只要命中了某个叶子节点，范围查询、排序、分页都能沿着链表“按顺序”往后取，而不需要每次重新从根节点出发。这个特征对“极速查询”非常重要，因为 MySQL 里很多慢查询其实不是点查慢，而是范围查询、排序查询把大量时间耗在了随机访问上。

2.2 聚簇索引和二级索引：回表到底是怎么回事

InnoDB 表本身就是一个按照主键组织的 B+ 树，这个结构叫聚簇索引。主键索引的叶子节点存放的是整行数据，所以通过主键查询时，找到叶子节点就等于拿到了所有列，不需要额外操作。

你手动建的普通索引，叫二级索引，也叫辅助索引。二级索引的叶子节点存放的不是整行数据，而是索引列的值加上主键值。也就是说，走二级索引的查询通常要分两步：先在二级索引的 B+ 树里找到目标主键，再根据主键回到聚簇索引里取整行，这个动作就叫回表。

回表不是免费的。每回表一次，就多一次随机点查。如果查询命中了非常多的二级索引记录，再挨个回表，性能甚至可能不如直接全表扫描。所以优化时经常会追求“覆盖索引”，也就是让查询所需要的所有列都包含在二级索引的叶子节点里。这样引擎直接在二级索引树上读完数据返回，连回表都省了，Extra 里会显示Using index。

2.3 哈希索引和 B+ 树的取舍

有些人会问，既然点查那么频繁，为什么不直接用哈希索引？哈希索引的定位确实极快，等值比较是 O(1) 级别，但它在两点上完全输给 B+ 树：不支持范围查询，不支持按索引顺序扫描。你只要写一个WHERE create_time >= '2024-01-01'，哈希索引就废了，还是要回到全表扫描。

B+ 树的索引键有序性，让它天然兼容等值、范围、排序这三大类查询。这也是 MySQL 默认在 InnoDB 表上选择 B+ 树索引的根本原因。当然，InnoDB 内部在自适应哈希索引上有优化，但那只是引擎自己做的一个缓存加速，对用户而言，建索引时默认考虑 B+ 树、考虑有序结构就够了。

3. 把多列索引设计成“可用导航路线”：列顺序和覆盖方式

3.1 组合索引的“最左前缀”物理直觉

很多人背过“最左前缀原则”，但不知道它为什么存在。组合索引本质上是一棵排序规则很特殊的 B+ 树：先按第一个列排序，第一个列相同的记录之间按第二个列排序，依此类推。这个物理规则决定了，查询条件只有从第一列开始连续匹配，才能大概率利用到索引的有序性。

举个例子，索引(user_id, status, create_time)，SQL 条件写成：

WHERE status = 2 AND create_time >= '2024-01-01'

这棵索引树的第一列是 user_id，但你根本没有限定 user_id，那 B+ 树根本不知道从哪一段开始扫描。它只能把整个索引树当成一个普通的有序列表，挨个检查 status 和 create_time。也就是说，这个组合索引在这个查询里基本形同虚设。你可以发现 MySQL 优化器最后帮我们搜了一个索引原则：偶欢可能会走 index 全索引扫描或直接全表。所以设计组合索引第一准则是：把查询里最常出现的等值条件放在最前面，范围条件尽量往后放。

3.2 列顺序怎么排：等值在前，范围在后，排序插队

再回到上一节那个线上 SQL。索引(user_id, status, create_time)能同时覆盖过滤条件和排序条件，关键在于三列的排列顺序和查询方式是吻合的：user_id 和 status 都做等值匹配，create_time 做范围匹配，同时 ORDER BY create_time 也能直接利用索引叶子节点的有序性。

如果反过来建(create_time, status, user_id)，那么 create_time 这个范围条件一旦生效，后面的 status 和 user_id 都无法继续利用索引有序性继续过滤，执行计划很可能变成全表扫描加 filesort。所以路径的取舍上，范围列本身是有“一刀切”性质的：列一旦在查询中走到范围条件，它后面的列在这个索引里基本就“站岗”了。

还有一点容易被忽略：如果把 SELECT 里的列都塞进索引，覆盖索引的效果会非常明显。比如某张表只有一列 user_id 不作为主键的 SQL，查询是SELECT user_id, status FROM table WHERE user_id = 888，如果你建(user_id, status)，二级索引的叶子节点里既有 user_id 又有 status，这套查询完全不用回表。线上一些高频点查能压到个位数毫秒，往往就是从回表变成覆盖索引之后的结果。

3.3 冗余索引：看起来多个索引，其实两棵树的成本都要扛

很多人建索引的习惯是：看到一个 WHERE 条件就单独加一个单列索引，再来一个排序又加一个组合索引。结果表上可能同时出现这类组合：

KEY idx_user_id (user_id), KEY idx_user_status (user_id, status)

第二个索引其实已经完全可以替代第一个。因为idx_user_status的最左前缀就是 user_id，所有基于 user_id 的查询都能走第二个索引。保留两个索引只会带来额外的维护成本：每次写操作，两棵 B+ 树都得同步更新。索引是读的加速器，同时也是写的减速器。检查正式环境表结构时，看到这种冗余索引，我会直接建议删掉左边那个无用的旧索引。

4. EXPLAIN 是一张体检单，慢查询排查得先读懂这五列

4.1 type 列：从 system 到 ALL 的“体检指标”

分析一条 SQL 为什么慢，我第一件事永远是EXPLAIN。EXPLAIN 结果里的列很多，但优先看 type、key、key_len、rows、Extra 这五列就够了。

type 列表示 MySQL 是怎么找数据的，它也是最有信息量的一列。常见的取值大致可以这样理解：

如果 EXPLAIN 最后出现 ALL，而且表是大表，那慢查询基本实锤了。优先要做的就是看能不能把 type 推到 ref 或 range。

4.2 rows 和 key_len：两张“反推地图”

rows 是优化器根据统计信息估算出来的扫描行数，它不等于实际行数，但能反映查询路径的“选择性”。如果预估 rows 是几十万，那就说明当前的索引条件过滤性太差了。

key_len 是个容易被忽略但非常实用的字段。它表示 MySQL 计划在索引里使用的字节数，能反推出组合索引实际用到了哪几列。比如(user_id, status, create_time)，如果 user_id 是 INT 占 4 字节，status 是 TINYINT 占 1 字节，create_time 是 DATETIME 占 5 字节，那么 key_len 等于 10 意味着三列都用上了；如果 key_len 只有 4，说明只用了 user_id。这能帮你判断“最左前缀”到底实际走到哪一步，不用靠猜。

4.3 完整看一个执行计划

还是用前面那张订单表：

EXPLAIN SELECT id, user_id, amount FROM old_pay_order WHERE user_id = 888 AND status = 2 AND create_time >= '2024-01-01' ORDER BY create_time DESC;

执行计划里 type 是 range，key 是 idx_user_status_ctime，rows 估算在一个较小范围内，Extra 里没有出现Using filesort。这说明查询已经在索引上完成了排序，范围条件是有效利用的。如果 rows 估算仍然很大，还需要检查该索引列的基数是否太低，比如 status 只有几个固定值，那么即使走到索引，过滤效果也非常有限，这种情况就得重新调整查询方案。

5. 索引失效现场还原：常见的写法是怎么把索引“废掉”的

5.1 隐式类型转换：最隐蔽的索引杀手

最常见也最容易忽略的问题，是字段类型和查询值类型不一致。比如手机号在表里是 VARCHAR，SQL 却写成：

SELECT * FROM user_info WHERE phone = 13800138000;

MySQL 在做比较时，会尝试把字符串列转成数字来比，相当于在索引列上发生了隐式转换，结果就是索引没法正常走。这种问题很难一眼看出来，因为单条数据少时全表扫描也能跑几十毫秒，数据量一大就原形毕露。排查时如果长字符串字段的 WHERE 查询突然变慢，先确认两边类型是不是一致的。

改法很简单：查询参数不要省略引号，写成phone = '13800138000'。更根本的办法是字段类型本身就用数字存手机号，这样就不会有类型纠纷。

5.2 函数包裹索引列：让范围查询失效的惯犯

对索引列做计算，是另一类常见失效场景。典型写法：

SELECT * FROM pay_order WHERE DATE(create_time) = '2024-06-01'; SELECT * FROM pay_order WHERE YEAR(create_time) = 2024;

表面上 create_time 在索引里，但 DATE()、YEAR() 都要先对每一行的索引列做一次运算，才能拿到结果参与比较。这样一来，原本有序的索引树对于“运算后的值”来说就是无序的，优化器只能放弃范围扫描。

针对日期条件，正确写法是查成一个区间：

SELECT * FROM pay_order WHERE create_time >= '2024-06-01' AND create_time < '2024-06-02';

这样 create_time 的有序性就保住了，索引还能走 range。类似的还有 SUBSTRING、LEFT、UPPER 等函数，IMPORTANT 原则是：不要让索引列单独出现在函数或表达式的左侧。

5.3 前缀模糊搜索、OR 拼接和负向查询

LIKE '%关键词'这种写法会让 B+ 树无法判断起点，只能扫全索引甚至全表。如果你真的需要从后往前匹配的业务，别硬扛着靠 MySQL，要么考虑反向冗余字段，要么上专门的检索方案。能用LIKE '关键词%'就用这个，至少它能走索引的 range 扫描。

OR 条件也经常把索引“憋死”：

SELECT * FROM order_table WHERE user_id = 888 OR status = 2;

OR 的语义是两边满足任意一边都可以，MySQL 想用索引就得同时处理两棵索引并合并结果。如果没有合适的合并策略，优化器会干脆选择全表扫描。这种场景建议拆成两条 SQL 再 UNION ALL，让每个分支都能充分利用自己的索引。

负向条件如!=、NOT IN，并不是绝对不能用索引，但优化器通常会低估它的可选择收益，尤其当负向条件对应的数据量很大时，全表扫描可能反而更快。遇到这类查询，如果线上确认走了 ALL，可以考虑改成 EXISTS 或反连接写法，让执行计划的可控性更高。

5.4 排序和索引冲突：filesort 是怎么冒出来的

ORDER BY 和 WHERE 组合在一起时，最容易踩的坑是“顺序对不上”。比如：

WHERE user_id = 888 ORDER BY create_time DESC;

如果组合索引是(user_id, create_time)，那么等值条件命中 user_id 之后，create_time 的扫描顺序天然就是这个分区内的有序顺序，倒序扫描也一样能利用索引。但如果条件里 create_time 是范围，排序又变成按另一个字段排，比如：

WHERE create_time >= '2024-01-01' ORDER BY amount DESC;

索引(create_time, amount)只能在 create_time 范围扫描时保证 create_time 有序，无法保证 amount 也有序，于是 MySQL 只能在内存或磁盘上重新做一次排序，Extra 里就会出现Using filesort。数据量一大，filesort 就会成为查询的主要耗时来源。

6. 维护期比建索引更难：冗余、基数和碎片的处理

6.1 读多写多，索引收益要重新算账

索引不是建完就一劳永逸。运维到后期，最需要做的不是继续加索引，而是给“索引资产”做减法。

每多一个索引，INSERT、UPDATE、DELETE 时 InnoDB 都要多维护一棵 B+ 树。如果一张表本身是高频写入的流水表，而索引收益只在某个低频后台查询上出现，那这笔交易就要重新算。我的习惯是：每次想加索引前，先看看这张表的写 QPS 和读 QPS 的比例，以及这条慢查询一天跑几次。如果索引只是为一个每天跑一次的后台导出服务，那增加写入成本可能得不偿失。

6.2 基数不高，索引选了也白选

索引列的值越分散，索引选择性越好。比如 user_id 有几十万种取值，过滤效果好；status 只有几种取值，即使建了索引，优化器也知道这一列过滤完还剩一大片数据，很可能不愿意走索引。查看基数用的是：

SHOW INDEX FROM old_pay_order;

看 Cardinality 这一列。数值相对表行数来说越大越分散。如果一个组合索引最前面的列基数极低，那后续列设计得再精妙也发挥不出多少效果。这也是为什么设计索引时，我更关注“哪些列区分度高”而不是“哪些列经常出现在 WHERE 里”。

6.3 碎片和统计信息：保持索引路况良好

InnoDB 的索引页在长期增删改后会产生碎片。碎片多了，索引树的扫描范围会变大，物理上不连续，性能就会下滑。这类问题通常不是 SQL 写法能解决的。对于大表，可以使用在线工具执行重建索引或者重组表的操作，避免锁表。小表直接跑一下：

ALTER TABLE old_pay_order ENGINE=InnoDB;

也能达到重建表、整理碎片的效果，但执行前一定确认表的大小和维护窗口。

另外，MySQL 的优化器判断走哪个索引，依赖的是统计信息。如果表的统计信息长期未更新，优化器可能拿到一个过期的行数，做出错误判断。遇到“索引明明在，优化器不走”的情况，先用ANALYZE TABLE old_pay_order刷新统计信息，再重新看执行计划，往往比急着改 SQL 更有效。

最后聊一个我自己的经验：索引优化做得越久，越觉得它是一门“取舍”的学问。不是所有慢查询都值得用索引救，也不是所有索引都要保留到天荒地老。线上数据库每次结构变更前，我都会做三件事：把 EXPLAIN 重新跑一遍，确认新索引能在目标 SQL 上真正改变 type 和 Extra；比对写放大是否在可接受范围；观察一周后再检查索引的实际使用频率，低频无用索引果断清理。这套流程看起来笨，但真的能挡住很多“加了索引反而更慢”的尴尬局面。给数据库装导航仪这件事，重点从来不是导航仪有多花哨，而是它在你最常跑的那几条路上，能不能给出最短的路线。