写一条 CREATE INDEX 语句只需要 5 秒钟，但在拥有千万级数据的生产表中，这条索引是让查询“飞起来”，还是让数据库直接宕机，取决于你是否真正理解它的底层逻辑。
很多开发人员对索引的认知停留在“给经常查询的列加个索引”的阶段，但在复杂的业务场景中，索引经常会“莫名其妙”地失效。本文将剥开 MySQL InnoDB 引擎的底层，讲清楚 B+ 树的运作机制，并盘点那些最容易让索引失效的日常代码写法。
一、 为什么数据库偏爱 B+ 树？
当我们给一个列加索引时，本质上是让数据库把这一列的数据抽出来，重新排个序，存进一个特定的数据结构里。业界有那么多数据结构，为什么关系型数据库不选 Hash 或者红黑树，偏偏选了 B+ 树？
核心原因只有一个：为了减少磁盘 I/O。 数据库的数据是存在机械硬盘或固态硬盘上的，每一次去磁盘拿数据的代价，比在内存中计算慢几十万倍。
- 为什么不用 Hash 表？ Hash 表做等值查询（WHERE id = 10）极快，时间复杂度是 O(1)。但是它毫无顺序可言，一旦遇到范围查询（WHERE id > 10 AND id < 20），Hash 表直接抓瞎，只能全表扫描。
- 为什么不用红黑树（二叉搜索树）？ 二叉树每个节点只有两个分支。如果表里有 1000 万行数据，这棵树会变得非常“高”（树高可能超过 20 层）。这意味着查一条数据，最坏情况要进行 20 多次磁盘 I/O，数据库根本扛不住。
- B+ 树的绝对优势：又矮又胖 B+ 树是一种多路平衡查找树。它的非叶子节点只存索引键，不存真实数据，这使得一个仅占 16KB 大小的节点页面，能塞下上千个索引键。 结论就是： 一棵 3 层高的 B+ 树，就能存下两千多万行数据。这意味着，在千万级的大表中找一条记录，最多只需要 3 次磁盘 I/O。并且，B+ 树的所有叶子节点用双向链表连在了一起，这让范围查询（>, <）变得像遍历数组一样简单。
二、 InnoDB 的两棵树：聚集索引与二级索引
在分析索引失效前，必须搞懂 InnoDB 是怎么存数据的。在 InnoDB 中，表数据本身就是一棵 B+ 树。
- 聚集索引（主键索引）： 这棵树的叶子节点里，存的是完整的一行真实数据。也就是说，找到了主键，就找到了整行数据。
- 二级索引（普通非主键索引）： 假设你给 phone 字段加了索引。这棵树的叶子节点里存的不是完整数据，而是 phone 的值 + 对应行的主键 ID。
什么是“回表”？ 当你执行 SELECT name FROM users WHERE phone = '13800000000' 时，数据库的执行轨迹是： 先去 phone 这棵二级索引树里查，找到了对应的主键 ID（比如 ID = 5）；然后再拿着 ID = 5 去聚集索引树里再查一次，把 name 取出来。这多出来的一次查询，在工程上被称为回表（Table Lookup）。
三、 经典坑位：常见索引失效场景大盘点
了解了 B+ 树“必须有序”和“回表”的原理，我们来看看平时写的哪些 SQL 会打破规则，导致数据库放弃走索引，退化为灾难性的全表扫描（Full Table Scan）。
1. 隐式类型转换（致命指数：⭐⭐⭐⭐⭐）
这是线上出事故频率最高的场景，没有之一。
-- 假设 phone 字段在设计表时是 VARCHAR(20) 类型，并且建了单列索引
-- 错误写法（前端传了个整型过来，ORM 框架直接拼装下发）：
SELECT id, name FROM users WHERE phone = 13800000000;
为什么失效？ 因为等号左边是字符串，右边是数字。MySQL 遇到类型不匹配时，不会报错，而是会默默地进行隐式类型转换。它的底层逻辑是把字符串转成数字再比对，等价于执行了： WHERE CAST(phone AS SIGNED INT) = 13800000000 这就引出了下一条铁律：破坏了等号左边字段的“干净”，索引必然失效。
2. 在索引列上做运算或套函数（致命指数：⭐⭐⭐⭐⭐）
-- 假设 create_time 加了索引
-- 错误写法 1（套函数）：
SELECT id FROM orders WHERE DATE(create_time) = '2026-04-09';
-- 错误写法 2（做运算）：
SELECT id FROM products WHERE price * 0.8 < 100;
为什么失效？ B+ 树里老老实实按原本的 create_time 和 price 排着序。你现在套了个函数或者做了乘法，数据库怎么知道计算后的结果在树的哪个分支？它只能把几百万条数据全部掏出来，每条都算一遍再比较。 正确姿势（把等号左边保持干净）：
WHERE create_time >= '2026-04-09 00:00:00' AND create_time < '2026-04-10 00:00:00'
WHERE price < 100 / 0.8
3. 违背“最左前缀法则”（致命指数：⭐⭐⭐⭐）
对于联合索引（比如给 A, B, C 三个字段建了一个组合索引），它的排序规则是：先按 A 排序；A 相同的情况下，再按 B 排序；B 相同再按 C 排。
-- 联合索引：idx_abc (a, b, c)
-- 可以走索引（完全匹配，或前缀匹配）：
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b = 2
-- 索引失效（或只有部分生效）：
WHERE b = 2 AND c = 3 -- 失效：没有 a，不知道在树的哪里找 b
WHERE a = 1 AND c = 3 -- 截断：只有 a 能走索引，c 走不了，因为跳过了 b
为什么失效？ 就像查汉语词典，你不知道第一个拼音字母，怎么可能直接去翻第二个拼音？
4. 左模糊查询（致命指数：⭐⭐⭐⭐）
-- 假设 name 加了索引
-- 错误写法：
SELECT id FROM users WHERE name LIKE '%张三';
为什么失效？ B+ 树是按从左到右的字符顺序排序的。'张三%'（右模糊）是可以走索引的，因为数据库知道顺着“张”字往下找；但如果写成 '%张三'（左模糊）或者 '%张三%'（全模糊），首字符都不确定，B+ 树的二分查找就彻底废了。
四、 总结：写出高质量 SQL 的一句铁律
要避开大部分索引失效的坑，开发人员在敲下 WHERE 关键字时，只需在脑子里默念一句铁律：
“永远保持等号左边的索引列干净。”
不要在索引列上加减乘除，不要套函数，不要让它发生类型转换。老老实实地把所有的运算逻辑，都扔到等号的右边，扔到 Java/Go 的应用代码层去做，让数据库只干它最擅长的事——通过一棵矮胖的 B+ 树，迅速找到那个主键。
