索引的原理

作为一名资深 Java 开发，你一定在面试或性能调优中多次处理过索引。索引的本质是空间换时间，它通过维护一种特定的数据结构，将全表扫描（$O(n)$）降低到树级搜索（$O(\log n)$）。

在 MySQL (InnoDB) 的语境下，索引的原理可以拆解为以下三个核心层面：

1. 核心数据结构：B+ 树 (B+ Tree)

InnoDB 存储引擎使用 B+ 树作为索引结构。

• 非叶子节点：只存储索引键（Key）和指向下一层的指针（Pointer），不存储实际数据。这使得每个节点能容纳更多键，树的层级更低（通常 3-4 层即可支撑千万级数据）。

• 叶子节点：存储了完整的数据（聚簇索引）或主键值（二级索引），且叶子节点之间通过双向链表连接，极大提高了范围查询（Range Scan）的效率。

2. 聚簇索引 vs. 二级索引 (Clustered vs. Secondary)

理解这两者的区别是掌握索引原理的关键：

回表 (Look-up)：如果你通过 name 索引查询 select *，MySQL 会先在 name 的 B+ 树找到主键 ID，再拿着 ID 去主键 B+ 树找整行记录。为了减少回表，我们常用覆盖索引（只查索引里已有的字段）。

3. 索引查询的底层逻辑

当你执行 SELECT * FROM users WHERE age = 25 时，数据库发生了什么？

1. 二分查找：从 B+ 树的根节点开始，将 25 与节点内的键值对比，确定下一层指针。

2. I/O 减少：由于 B+ 树高度极低，查找过程只需要 3-4 次磁盘 I/O 即可定位到目标页（Page）。

3. 页内搜索：将 Page 加载进内存，通过槽（Slot）快速定位到具体记录。

4. 为什么索引会失效？（资深开发关注点）

即使有了 B+ 树，有些写法也会导致索引“哑火”：

• 违背最左前缀原则：组合索引 (a, b, c)，只查 b 和 c 是没用的，因为 B+ 树是按 a 排序的。

• 函数或运算操作：WHERE YEAR(create_time) = 2026 会导致索引失效，因为索引存的是原始值，不是计算后的值。

• 隐式类型转换：字符串字段不加引号（如 WHERE phone = 138...），MySQL 会进行 CAST 转换，破坏索引排序逻辑。

📈 性能对比演示

假设一张表有 1000 万行数据：

• 无索引（全表扫描）：平均需要比较 500 万次。

• 有索引（B+ 树）：$\log_{256}(10,000,000) \approx 3$。只需要 3 次磁盘寻道。

🛠 下一步建议

既然你正在处理 CSV 数据清洗的项目，如果以后这些数据要入库，你可能需要考虑：

• 唯一索引 (Unique Index)：在入库阶段利用数据库层级防止重复，而不只是靠 Python 脚本。

• 前缀索引：如果某个字符串字段（如 Email）很长，可以只对前 10 位建索引以节省空间。

需要我帮你写一段 SQL 语句，演示如何在高并发场景下通过 EXPLAIN 查看索引的命中情况吗？