做后端这几年，我见过太多次慢SQL排查的场景：业务方扔过来一条查询，说数据才几百万行就卡到几秒；开发同学现场加了一个索引，结果下次换个查询条件又慢回原形。很多人一听到“MySQL索引原理和B+树应用”就觉得这是背诵八股，实际上不搞懂B+树为什么被选中，你根本没法判断一条SQL到底该加什么索引，也没法解释为什么明明是同一张表、换个where条件索引就失效。这篇文章我会把从磁盘IO、数据页到联合索引、索引失效的整个链路讲清楚，所有结论都能直接用EXPLAIN验证，适合正在啃索引原理的开发者，也适合天天和慢查询打交道的后端同学。
写这篇文章之前我重新翻了InnoDB的存储实现，因为很多结论不能只看文档，得回到“页”这个最小单位去想。索引不是银弹，它是在空间和时间之间做交换；如果你不理解交换的底层逻辑，很容易加错索引，甚至把原本不慢的写入拖垮。先不急着写SQL，我们从最底层的问题开始：一个查找操作在MySQL里到底要花什么成本？
1. 从IO成本看索引本质：MySQL为什么非要用B+树？
1.1 磁盘IO才是真正的瓶颈
很多人对“索引查得快”的理解停留在时间复杂度上：红黑树是O(log n)，B+树也是O(log n)，好像差不多。但MySQL是跑在磁盘上的关系型数据库，存储引擎最关心的不是CPU计算多少步，而是到底要发生多少次磁盘IO。
一次随机磁盘IO普遍要5到10毫秒，而一次内存访问是几十到几百纳秒，两者相差好几个数量级。哪怕内存里算得再快，只要多读一个磁盘页，整个查询的耗时都会显著上升。索引的真正职责，是尽量减少查询过程中需要扫描的磁盘页块数，而不是单纯降低算法复杂度。这个观点一旦立住，后面很多选择就都说得通了。
InnoDB读写数据的最小单位是16KB的数据页，默认情况下innodb_page_size=16384。B+树的一个节点就是一个页。你要查一行数据，得从根节点出发，一层一层往下走，每经过一层都可能发生一次磁盘读取。所以在评估索引结构时，关键指标不是“树有多平衡”，而是“树有多矮”。
1.2 为什么不选哈希、红黑树或B树
哈希表在等值查询上确实强，一次计算就能定位到桶，但它的致命问题是不支持范围查询，也不支持排序。你写where user_id > 100或者order by create_time，哈希索引就完全派不上用场。而且InnoDB中的自适应哈希索引是建立在B+树之上的二次加速结构，不能替代B+树本身。
红黑树和AVL树是内存中优秀的数据结构，但它们每个节点只保存一个键和两个子节点指针。一千万行数据的AVL树，高度大约是24层，也就是一次等值查询最坏要读24个页。虽然几十次内存态访问还能接受，但放在磁盘上就是灾难。你可以把每个节点想象成图书馆目录柜里的一个卡片，传统二叉树只用一张卡片上的字母分叉，想看下一个分叉还得再抽一张卡片，查一次书要来回翻几十次柜子。
B树解决了“一个节点存多个键”的问题，但它把索引键和数据都塞在同一个节点里。非叶子节点存入大量行数据后，保存的键数量急剧减少，同样一个16KB页能延展的分支数变少，树还是压不下来。更麻烦的是，B树每个节点的子节点之间没有串联，做范围查询时中序遍历需要反复从父节点回溯到兄弟节点，一次大范围扫描会带来大量随机IO。
1.3 B+树胜出的三个核心细节
B+树和B树最明显的区别是：非叶子节点只保存索引键和指向子节点的指针，不保存行数据。所有数据都放在最底层的叶子节点中，叶子节点再通过链表按顺序串起来。这个设计带来三个实打实的好处。
第一，树更矮。以一个16KB的页为例，索引键用BIGINT占8字节，子节点指针占6字节，一条索引项约14字节，一个页大概能放1170条索引项。第二层有1170个页，也就是约1170乘1170约137万个叶子节点。假设每个叶子页平均放10行完整数据，三层B+树就能支撑上千万行。行数据越大，支持的行数会少一些，但百万级到千万级仍然很稳。对绝大多数业务表来说，三层足够。
第二，范围查询高效。因为叶子节点已经按索引列排好序，并且通过链表连接，只要定位到范围起点，就能沿着链表顺序往后读。这种顺序读非常契合磁盘预读机制，扫描大量连续数据时成本远低于B树。
第三，磁盘IO次数稳定可控。等值查询从根到叶子固定经过三层，也就是固定读取3页左右。页是预读的，相邻页之间也大概率顺序读。这个稳定的成本模型让优化器能够可靠地估算查询代价，也让DBA做容量规划时更有底。
B+树当然也有代价：数据插入位置不连续时会造成页分裂，页空间浪费到一定程度还会触发合并。但相对于它带来的查询收益，这些写放大是可以接受的，而且可以通过正确的主键设计来控制。
2. InnoDB里的两棵B+树：聚簇索引和二级索引怎么协同工作
2.1 建一张表，看看索引背后长什么样
只看理论容易飘，我们直接建一张订单表来验证。这张表很典型，包含订单号、用户ID、状态、创建时间，后面讲联合索引和排查都用它。
CREATE TABLE `t_order` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `order_no` varchar(32) NOT NULL, `user_id` bigint unsigned NOT NULL, `status` tinyint NOT NULL DEFAULT '0', `create_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `idx_user_create` (`user_id`, `create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;InnoDB里最核心的一个概念是：表数据本身就是一棵B+树。主键构成聚簇索引，聚簇索引的叶子节点存储整行数据。也就是说，你执行select * from t_order where id = 100时，走的是主键这棵B+树，一路定位到叶子页，直接拿回完整行。
同时，InnoDB还会维护第二棵B+树，也就是二级索引。这里我建了idx_user_create，这棵索引树的叶子节点不存整行，只存储user_id、create_time以及主键id三个字段。为什么二级索引不直接存行地址？因为行数据在聚簇索引里并不是固定的，插入、更新、页分裂都可能让行移动。如果二级索引保存行地址，每次移动都要回改所有二级索引。存主键值就稳定得多，代价只是每次通过二级索引定位后可能还要回聚簇索引再查一次。
2.2 数据页内部：不是简单链表
既然一个叶子节点是一个16KB的数据页，那页里面是啥样的？InnoDB数据页内部并不是把记录一条条零散放着，而是维护了一个“页目录”。你可以把页想象成一栋楼，目录是楼层索引牌，记录按照键值顺序排好，页目录里放槽，每个槽指向一组记录的起始位置。
当你要在某个叶子页内查找具体记录时，InnoDB会先在页目录里做二分查找，锁定可能存在的槽，再在组内顺序扫描。也就是说，即使定位到叶子节点这一层，也不是从页头一路扫到页尾。很多人在一个页里数据不多时感觉不到差别，但当一个页填了上百条记录后，页目录的二分查找能明显减少CPU消耗。
每次通过二级索引拿到主键值，再去聚簇索引里回查一次，就叫回表。一次回表只是多一次索引树的路径查找，成本不算高。真正的问题是在一个查询需要回表成千上万次时，原本一次范围扫描变成了大量随机IO，慢SQL就是从这里来的。后面会说，覆盖索引就是用来对付这个问题的。
2.3 主键怎么选，会直接影响B+树的写放大
理解了聚簇索引叶子节点存整行，就能理解为什么主键设计对性能影响那么大。如果主键是自增整数，新插入的行主键值单调递增，数据会一直追加到当前索引树最右侧的叶子页，页满了就去开新页，很少触发其他页的数据搬移。
如果主键是UUID或者随机字符串，插入位置随机落在B+树中间，就可能造成已经写满的中间页被分裂，数据被搬过来搬过去，产生大量磁盘碎片和日志写入。同样一张表，自增主键和UUID主键的写入性能差距可以有几倍。而且主键列会出现在每个二级索引的叶子节点里，主键越长，所有二级索引占用空间就越大，缓存命中率也会受影响。
所以在建表时我通常遵循三条简单原则：能用整数就不用字符串；能用自增主键就不要手工生成主键；如果没有自然主键，老老实实加一个AUTO_INCREMENT的单列主键。树越稳定，你的插入TPS越有保障。
3. 联合索引的排序逻辑与EXPLAIN实战
3.1 最左前缀：联合索引的第一个字段是命门
很多同学知道联合索引有“最左前缀”原则，但不知道为什么。回到B+树的排序规则就很好理解了。idx_user_create这棵B+树，先按user_id排，user_id相同的行再按create_time排，最后可能按主键id排。
也就是说，整棵索引树对“创建时间”是有序的，但只在user_id确定的前提下才成立。如果查询条件是where create_time > '2024-01-01'而没有user_id，索引第一层的排序对create_time完全没意义，优化器没法沿着索引做范围定位。
所以并不是where里条件写的顺序决定是否走索引，而是查询条件里是否包含了联合索引最左侧的列。如果where user_id = 1 and create_time > '2024-01-01'，user_id等值定位后，create_time就是局部有序的，可以继续在索引上做范围查询。如果交换写where create_time > ... and user_id = 1，优化器通常也能调整顺序，关键考察的仍然是列本身而不是书写顺序。
这里还要多说一个容易忽略的点：联合索引对查询有帮助的条件有两种。等值条件会锁定树上的多个连续区间，范围条件则会在某一段有序链路上进行扫描。如果跳过最左列直接查右边列，就没法利用索引的有序性，常规优化器只好放弃这棵索引。
3.2 索引下推和覆盖索引，两个低成本高收益的优化
MySQL 5.6之后引入了索引下推，这是一个经常被忽视的特性。还是看这条查询：
SELECT * FROM t_order WHERE user_id = 123 AND create_time > '2024-01-01';走idx_user_create时，二级索引里已经包含user_id和create_time。没有ICP时，存储引擎需要把所有满足user_id=123的索引记录都取出来，一条条回表，再由Server层过滤create_time。有了ICP，存储引擎在读取二级索引时就能直接用create_time条件过滤掉大量不合格记录，只有真正符合条件的主键才回表。这样一来回表次数明显减少，EXPLAIN里会看到Using index condition。
覆盖索引是另一把利器。如果查询只需要用到索引里已经包含的列，就不要回聚簇索引拿完整行了。比如这条SQL：
SELECT id, user_id, create_time FROM t_order WHERE user_id = 123 AND create_time > '2024-01-01';idx_user_create里已经包含id、user_id、create_time三个字段，查询结果全部可以从二级索引的叶子节点拿到，EXPLAIN的Extra列会显示Using index，这是非常理想的状态。相反，如果select所有字段，就必须回表，同样的条件会出现Using index condition而不是Using index。
select *本身没有错，但在高频查询、核心链路这种场景下，只取需要的字段，同时让索引覆盖查询，收益会非常可观。这不是为了少传两个字段省带宽，而是为了减少随机IO次数。
3.3 用EXPLAIN验证索引到底有没有用
理论说再多，不如看一条真实EXPLAIN。我把上面那条覆盖查询放进去：
EXPLAIN SELECT id, user_id, create_time FROM t_order WHERE user_id = 123 AND create_time > '2024-01-01' ORDER BY create_time;执行计划里大概率会出现这些关键信息：
key是idx_user_create，说明优化器真的用了这棵索引。type是range，说明在索引上做了范围扫描，比全表扫ALL好很多。Extra里有Using index，说明没有回表。- 没有
Using filesort，说明ORDER BY create_time也能利用索引完成排序。
EXPLAIN输出里的type项是判断扫描方式的最好入口。按性能从好到差大致是：system、const、eq_ref、ref、range、index、ALL。看到ALL基本就是全表扫描，看到index虽然扫了索引但也不一定高效，只有ref以上和range才是符合预期的。rows是优化器估算要扫描的行数，它不一定精确，但如果和实际预期差太多，就要看看统计信息是否存在偏差。
如果查询里加了ORDER BY create_time且没有先限定user_id，那么联合索引就无法保证全局按create_time有序，执行计划里大概率出现Using filesort，也就是MySQL要把数据取出来另做排序。遇到这种场景，要么调整查询让它命中索引前缀列，要么单独考虑排序字段的索引。
4. 我从慢查询里挖出来的索引失效坑与排查流程
4.1 六个典型失效现场
索引不是加了就一定生效，日常工作里最头疼的是那些“看起来该走索引却没走”的SQL。下面是我实际排查时反复踩过的六类坑。
| 场景 | 常见写法 | 失效原因 | 可行改写 |
|---|---|---|---|
| 索引列套函数 | WHERE DATE(create_time) = '2024-01-01' | 函数破坏了索引列的有序性 | create_time >= '2024-01-01' AND create_time < '2024-01-02' |
| 隐式类型转换 | WHERE phone = 13800138000 | varchar列被转成数值 | WHERE phone = '13800138000' |
| 左模糊查询 | WHERE name LIKE '%abc%' | 叶子节点有序，无法从中间定位 | 尽量避免，或改前缀模糊 |
| 跳过联合索引左列 | WHERE create_time > '2024-01-01' | 全局顺序不按第二列 | 再加单列索引或调整查询 |
| OR包含非索引列 | WHERE user_id=1 OR status=1 | 优化器难以合并访问路径 | 拆成两条SQL再用UNION ALL |
| 范围查询后的列 | WHERE user_id=1 AND create_time>'2024-01-01' AND status=1 | 范围后列的定位失效 | 把等值列放前，范围放后 |
第一类最典型。对索引列做函数运算时，MySQL无法直接利用B+树的有序排列，因为它要先把每一行的create_time都算出函数值才能比较。正确的做法是把函数运算从列上挪到常数上，写成范围条件。这样不仅能用索引，语义也更清晰。
隐式转换这个坑也很隐蔽。phone列是varchar，但查询传了数字，MySQL会把存储的字符串转成数字再比较，相当于对索引列做了转换，索引自然就废了。反过来，如果索引列是int，条件传字符串，MySQL会尝试把字符串转成int，因为转换发生在常数一侧，对索引列没有影响，索引通常还能用。判断规则很简单：看转换发生在哪一侧，发生在索引列上就危险。
OR条件需要单独说。如果OR两边都是索引列，MySQL在某些版本下会尝试Index Merge，但成本并不总是比全表扫低。如果OR里有一个列完全没有索引，那基本就没有走索引的希望了。业务上能拆就拆成两条查询再UNION ALL，也可以用IN重写部分场景，效果往往更稳。
4.2 索引不是越多越好：维护代价与冗余检查
B+树是读优化的结构，但写入时每一棵索引都要跟着更新。插入一行数据，如果有5个二级索引，就要同时往5棵B+树里插入对应记录。页满了还会触发页分裂，产生额外磁盘IO和日志。很多高频写入表加了五六个索引后，插入吞吐明显下降，这种代价在产品初期数据量小的时候根本看不出来，一到业务峰值就暴露。
所以每次新增索引前我会先问三个问题：这条查询是不是真的高频？查询里所有条件能不能复用现有索引？这个索引会不会和已有索引重复？比如已经有idx_user_create(user_id, create_time)，再单独建idx_user_id(user_id)就属于冗余，因为联合索引的最左列已经能覆盖单列user_id的查询需求。
排查冗余索引可以查系统表：
SELECT * FROM sys.schema_unused_indexes;这个视图能列出启动以来未使用过的索引，看到长期没有人命中的索引，可以考虑删除。删除索引比新增索引对业务的影响小一些，但仍然要选择低峰期操作，并且先确认没有别的查询依赖它。
4.3 一套可复用的慢查询排查流程
光知道理论还不行，真遇到线上慢查询，要有自己的排查顺序。我一般按下面五步走：
- 先开启慢查询日志，把超过阈值的SQL抓出来：
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SHOW VARIABLES LIKE 'slow_query_log%';线上环境建议直接在配置文件中固化，否则数据库重启后就失效了。
对慢SQL执行
EXPLAIN，先看type和key。如果type=ALL且没有可用索引，检查是不是缺索引，或者索引失效。如果
key显示用上了索引但还是很慢，重点看rows和Extra。Using filesort说明排序是额外做的，Using temporary说明可能产生了临时表，这些都要单独优化。想弄明白优化器为什么没选某个索引，可以临时打开优化器追踪：
SET optimizer_trace = 'enabled=on'; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_trace = 'enabled=off';这个操作有额外开销，生产环境一定要用完马上关掉。
- 根据结论修改SQL或者索引，再重复执行
EXPLAIN对比。只改索引不改写法，或者反过来，很多问题都解决不干净。
这套流程最大的价值是让排查有顺序，不会一上来就翻代码或者逐条试索引。多数情况下，一条慢SQL从问题出现到定位原因，五分钟内就能完成。
4.4 大表加索引的实操提醒
如果你要在一个千万行的大表上新增索引，直接执行ALTER TABLE ADD INDEX虽然会被Online DDL尽量优化，但大表操作仍然可能造成主从延迟、磁盘IO飙升，甚至阻塞写入。更稳妥的方式是使用在线改表工具，在低峰期通过后台任务同步数据，最终短暂切换完成。这里要注意，工具会额外占用磁盘空间，操作前先评估磁盘余量，并且准备好回滚方案。
还有一个小细节：新增索引前先看选择性。如果一个列的取值只有两种状态，比如status只有0和1，那单列索引的选择性太差，优化器很可能选择全表扫也不走索引。更好的做法是把低选择性列放在联合索引后面，配合高选择性列一起使用。这也是为什么我不建议一看到where status=1就立刻加索引的原因。
5. 一点个人门道：遇到慢SQL，我习惯先画B+树
说了这么多，最后还是分享一个我自己养成的小习惯。拿到一条慢SQL，我不会急着加索引，而是在草稿纸上把涉及的where条件拆成三组：等值条件、范围条件、排序字段，然后问自己一个问题：如果我要为这些条件设计一棵B+树，索引列应该怎么排列才合理？
这个习惯是从一次线上事故里逼出来的。当时某张表已经有好几个索引，但查询还是慢，后来发现是因为一个范围查询夹在联合索引中间，导致后面的列全部失去有序性。如果当时先从B+树的结构倒推，一眼就能看出问题。现在我的经验是：把等值条件放在索引最前面，范围条件放中间，需要排序的字段尽量排在范围条件之前，这样才能最大化利用B+树的排序能力。
B+树不是什么高深莫测的概念，它就是一棵矮胖树，叶子串成链表，非叶子只负责路由。搞清楚这一点，索引失效的原因、联合索引的设计、回表和覆盖索引的取舍，全都能串起来。遇到问题先画树，再写SQL，最后用EXPLAIN验证，这是我目前对抗慢查询最顺手的方法。
