MySql索引知识点汇总 二（个人总结）

一、 索引为什么是 B+ 树？

在计算机科学中，有很多数据结构可以实现快速查找，比如哈希表、平衡二叉树、B 树。为什么 MySQL 最终选择了 B+ 树？

1. 对比哈希表 (Hash)

• 优势： O(1) 的查询速度，快到极致。
• 劣势： 只能处理“等值查询”（where id = 1）。一旦遇到范围查询（where id > 10），哈希表就彻底废了，因为它内部是无序的。

2. 对比二叉搜索树 (BST / AVL / 红黑树)

• 劣势：树太高了。
• 物理意义上的痛点： 数据库数据存在磁盘上。每经过一个树节点，都要进行一次磁盘 IO。如果数据有百万级，二叉树的高度可能是 20 层，意味着查一个数据要 20 次 IO。这在性能上是不可接受的。

3. 对比 B 树 (B-Tree)

• 区别： B 树在非叶子节点也存储数据。
• B+ 树的绝杀：
• 更胖更矮： 因为非叶子节点只存索引键，不存数据，所以一个 16KB 的页能存更多索引，树的高度通常只有 3-4 层。
• 范围查询极强： 所有的叶子节点通过双向链表连接。查范围时，只需找到起点，然后顺着链表往后拉就行，不需要回溯到父节点。

二、 索引的生效机制：最左匹配原则

这是构建联合索引（Composite Index）时的灵魂逻辑。

如果你建立了一个索引 INDEX(name, age, score)，你可以把它想象成一个多级排序：先按 name 排，name 相同按 age 排，age 相同再按 score 排。

• 生效情况：
• WHERE name = '盖伦' (有效)
• WHERE name = '盖伦' AND age = 35 (有效)
• 失效情况：
• WHERE age = 35 (无效，因为没有 name，索引无法开始匹配)
• WHERE name = '盖伦' AND score = 100 (只有 name 走索引，score 走不了，因为中间断了 age)

三、 索引失效的“避坑指南”

即便你建了索引，写 SQL 不注意也会导致“全表扫描”。常见的坑位有：

1. 计算与函数：WHERE YEAR(birthday) < 2000（索引字段被函数包裹，无法走索引）。
2. 类型转换： 字段是字符串，你传个数字：WHERE phone = 138xxx（隐式转换会导致失效）。
3. 模糊查询：LIKE '%keyword'（左模糊必失效，就像查字典你只记得结尾，必须重头翻）。
4. 范围查询后的失效： 在联合索引中，范围查询（>、<）之后的字段无法使用索引。

四、 进阶技巧：覆盖索引 (Covering Index)

如果你建立了一个索引 INDEX(name, age)

• 普通情况： 你查 SELECT * FROM users WHERE name = '盖伦'。即使走索引找到了，还要拿着主键去“回表”查整行数据。
• 覆盖索引： 你查 SELECT age FROM users WHERE name = '盖伦'。
• 因为你的索引 (name, age) 里已经包含了你想要的 age 字段，MySQL 直接从索引树里就把结果给你了，不需要回表。

物理意义： 减少了一次从“非聚簇索引树”跳到“聚簇索引树”的磁盘 IO。

总结一下：

1. 定义： 高效获取数据的数据结构（目录）。
2. 物理： 磁盘上的 B+ 树，页存储，双向链表。
3. 结构对比： 选 B+ 树是为了降低树高、支持范围查询。
4. 实战规则： 遵守最左匹配，避开函数和类型转换，尽量使用覆盖索引。