1203_MySQL InnoDB 中 B+ 树的存储结构

MySQL InnoDB 中 B+ 树的存储结构

InnoDB 使用了 B+ 树作为索引的默认数据结构。无论是主键索引（聚簇索引） 还是二级索引（辅助索引），都是 B+ 树。它们的结构有细微但关键的区别。

1. 聚簇索引 (Clustered Index)

• 存储方式：表数据本身就直接存储在聚簇索引的叶子节点上。一张表只能有一个聚簇索引。
• 构建依据：通常由主键（PRIMARY KEY）构建。如果没有主键，InnoDB 会选择一个唯一的非空索引代替。如果也没有，则会隐式创建一个 rowid 作为聚簇索引。
• B+ 树结构：
  • 非叶子节点：只存储索引键（主键值） 和指向子节点的指针（通常是页号）。
  • 叶子节点：存储了完整的行记录（包括所有列的值）。正因为如此，叶子节点包含了所有数据。

2. 二级索引 (Secondary Index)

• 存储方式：二级索引的叶子节点不存储完整的行数据，而是存储该索引的键值 + 对应的主键值。
• 构建依据：由用户定义的 CREATE INDEX 或 UNIQUE 约束创建。
• B+ 树结构：
  • 非叶子节点：只存储索引键（例如你创建的 name 字段的值）和指向子节点的指针。
  • 叶子节点：存储索引键 + 主键值。

这个区别是理解查询流程的关键。通过二级索引查找时，如果需要的列不在索引中，就需要进行 “回表” 操作。

---

查询语句时间复杂度分析

SQL 查询的时间复杂度主要取决于数据访问路径，而访问路径由索引和查询条件共同决定。

访问方式	时间复杂度	场景举例	说明
主键等值查询 / 唯一索引等值查询	O(1) - O(log n)	SELECT * FROM t WHERE id = 123;	通过 B+Tree 索引直接定位，理想情况下复杂度为树的高度 O(log n)。由于索引缓存，常被视为 O(1)。
非唯一索引等值查询	O(log n) + (M)	SELECT * FROM t WHERE name = 'Alice';	索引查找 O(log n)，加上在叶子节点链表上扫描所有匹配项 M（匹配行数）。
索引范围扫描	O(log n) + (M)	SELECT * FROM t WHERE age > 30;	找到起始点 O(log n)，然后沿叶子节点链表扫描 M 条记录。M 是范围内记录数。
全索引扫描	O(n)	SELECT indexed_col FROM t;	顺序读取整个索引树的所有叶子节点，n 是表总记录数。
全表扫描	O(n)	SELECT * FROM t WHERE unindexed_col = 1;	顺序读取整个表的所有数据页，n 是表总记录数。性能最差。
多表连接 (JOIN)	可达 O(n * m)	SELECT * FROM a JOIN b ON ...	复杂度取决于连接算法（NLJ, BNL, HJ）和驱动表的选择。无索引时最坏情况是笛卡尔积。有索引可大幅降低复杂度。
排序 (ORDER BY)	O(n log n)	SELECT * FROM t ORDER BY col;	如果无法利用索引排序，则需要在内存或磁盘进行排序操作。
磁盘临时表	高	GROUP BY, 复杂子查询	如果操作无法在内存中完成，需要创建磁盘临时表，涉及大量 I/O，性能急剧下降。

核心要点：

• 索引的本质是将随机 I/O 转换为顺序 I/O，并通过减少需要扫描的数据量来降低时间复杂度。
• O(log n) 是索引查询的理想复杂度，n 是表记录数，log n 就是索引 B+Tree 的高度（通常非常低，3-4层即可存储亿级数据）。
• 优化查询的首要目标就是避免 O(n) 级别的全表扫描，并将其优化为 O(log n) 的索引查找。

---

查询一条数据的详细流程（以等值查询 SELECT * FROM users WHERE id = 123; 为例）

假设 id 是主键，users 表上有一个聚簇索引。

第1步：从根节点开始

• 数据库会固定地持有索引的根页（Root Page） 的位置信息。
• 执行器请求存储引擎获取 id = 123 的记录，引擎从根页面开始查找。

第2步：逐层遍历非叶子节点（在内存中完成）

• 非叶子节点可以看作一个有序的键值对列表，例如：(key, child_page_pointer)。
• 查找策略：在节点内部使用二分查找法快速定位。
• 比较逻辑：将目标值 123 与节点中的各个 key 进行比较。
  • 如果 123 小于第一个 key，则沿着第一个指针指向的页继续查找。
  • 如果 123 大于等于某个 key 且小于下一个 key，则沿着这个 key 对应的指针指向的页继续查找。
  • 如果 123 大于等于最后一个 key，则沿着最后一个指针指向的页继续查找。
• 这个过程会从根节点 -> 中间节点 -> ... 不断重复，直到最终定位到叶子节点。

第3步：在叶子节点中定位记录

• 到达叶子节点后，同样在叶子节点内的记录中使用二分查找法进行精确匹配。
• 叶子节点中的记录在聚簇索引上是根据主键严格排序的。
• 如果找到 id = 123 的精确记录，由于这是聚簇索引，该叶子节点上就直接存储着这条记录所有列的数据。存储引擎将这条完整记录返回给执行器。
• 如果遍历完该叶子节点也没找到，则返回“记录不存在”。

第4步：返回结果

• 执行器将得到的数据返回给客户端，流程结束。

---

带有二级索引和回表的查询流程（SELECT * FROM users WHERE name = 'Alice';）

假设在 name 字段上有一个二级索引 idx_name，id 是主键。这个流程更复杂，完美结合了两种索引的结构。

flowchart TD A["SELECT * FROM users<br>WHERE name = 'Alice'"] --> B["通过二级索引 idx_name 查询"] B --> C{"在 idx_name 的 B+Tree 中<br>查找 'Alice'"} C -- 找到 --> D["叶子节点结果:<br>索引键: 'Alice'<br>主键值: id_123"] C -- 未找到 --> E[返回空结果] D --> F["回表 (Bookmark Lookup):<br>使用主键值 id_123<br>到聚簇索引中查找"] F --> G["通过聚簇索引查询<br>定位主键 id_123 的记录"] G --> H["在聚簇索引叶子节点<br>获取完整行数据"] H --> I["返回完整数据行"]

为什么需要“回表”？
因为二级索引的叶子节点只存储了 name 和 id，而查询要求的是 SELECT *，需要所有列的数据。这些数据只存储在聚簇索引的叶子节点上，因此必须用查到的 id 再去聚簇索引里查一次。

范围查询的流程

对于像 SELECT * FROM users WHERE id > 100 AND id < 200; 这样的查询：

1. 定位起点：在聚簇索引的 B+ 树中查找 id = 100 的记录（即使不存在，也会找到第一个大于 100 的记录的位置）。
2. 叶子节点扫描：从该位置开始，沿着叶子节点之间的双向链表向后扫描。因为叶子节点是按主键顺序链接的，所以非常高效。
3. 过滤与返回：每拿到一条记录，就判断是否满足 id < 200 的条件。如果满足，则返回；如果不满足，则停止扫描。

总结

特性	聚簇索引	二级索引
数量	每表1个	每表多个
叶子节点内容	完整数据行	索引列 + 主键值
查询性能	主键查询极快	需回表，可能慢
覆盖索引	天然覆盖	若查询字段全在索引中，可不回表

• B+ 树优势：矮胖树形结构，层级很低（通常3-4层就能存下亿级数据），每次查询的 I/O 次数非常稳定（基本就是树的高度），非常适合磁盘访问。
• 查询核心：二分查找（节点内） + 指针遍历（节点间）。
• 关键概念：回表 是性能优化的重点，应尽量使用覆盖索引（索引包含所有需要查询的字段）来避免它。