[!info] related notes

• 所属 MOC：数据库 MOC
• 前置：B+Tree 数据库索引
• 后续：联合索引与覆盖索引、索引代价
• 产品：MySQL、PostgreSQL

聚簇索引与二级索引

[!abstract] 核心问题 索引叶子最终怎样定位真实行？聚簇索引让表数据按某个索引的叶子结构组织；二级索引保存另一组 Key 和行定位信息。不同数据库实现不同，“回表”和覆盖查询的成本也随之不同。

MySQL InnoDB 的典型模型

聚簇主键索引

InnoDB 表的主键 B+Tree 叶子保存完整行记录：

Primary Key B+Tree
→ leaf: [primary key | full row]

按主键查询定位叶子后通常已经得到整行。

如果没有合适显式主键，InnoDB 会选择唯一非空索引或生成隐藏 Row ID 作为聚簇 Key。工程上仍建议选择稳定、短小、不会频繁变化的主键。

二级索引

InnoDB Secondary Index 叶子通常保存：

secondary key + primary key

查询非覆盖列时：

secondary index seek
→ get primary key
→ primary index lookup
→ full row

这个第二次查找通常称为回表。

为什么二级索引不保存整行

如果每个 Secondary Index 都复制完整行：

• 索引体积巨大；
• 每个 Page 容纳更少 Entry；
• Fan-out 降低；
• 更新任何列都要维护更多副本；
• Buffer Pool 压力增加。

保存主键作为 Row Locator，在空间与查找成本之间取平衡。

主键长度为什么影响所有二级索引

InnoDB Secondary Leaf 包含 Primary Key，因此主键越宽：

all secondary indexes become wider
→ fewer entries per page
→ more memory / storage / I/O

这也是不建议使用巨大、频繁变化的聚簇主键的原因之一。

覆盖索引为什么避免回表

如果查询所需字段都能从 Index Entry 直接得到：

CREATE INDEX idx_users_email_name ON users(email, name);
SELECT name FROM users WHERE email = ?;

数据库可以完成 Index-only / Covering Scan，不必再定位完整行。

但是否真正 Index-only 还取决于数据库实现、MVCC 可见性和执行计划。

PostgreSQL 不能直接套 InnoDB 模型

PostgreSQL 普通表使用 Heap Storage，B-tree Index Entry 指向 Heap Tuple 的 TID：

B-tree leaf
→ indexed key + TID
→ heap page tuple

表数据不会持续按某个主键 B-tree 叶子组织。CLUSTER 可以按某索引一次性重排表，但后续写入不会永久维持该顺序。

PostgreSQL 的 Index-only Scan 还要结合 Visibility Map 判断是否需要访问 Heap 来确认 MVCC 可见性。

[!warning] “主键就是聚簇索引”不是跨数据库铁律 在 InnoDB 中通常成立；在 PostgreSQL、SQLite 或其他引擎中，物理组织和 Row Locator 模型不同。

聚簇组织的收益

按聚簇 Key 邻近的数据物理/逻辑位置更接近，适合：

• 主键范围扫描；
• 按递增 Key 插入；
• 访问完整行；
• 利用局部性和预读。

聚簇组织的代价

• 聚簇 Key 更新可能导致行移动；
• 随机 Key 插入引起 Page Split 和 Fragmentation；
• 二级索引必须携带较宽 Row Locator / Primary Key；
• 只能有一种主要物理组织顺序。

回表为什么会贵

大量 Secondary Entry 对应分散的 Primary / Heap Page：

scan many index entries
→ many random row fetches
→ cache misses / random I/O

返回行比例很高时，优化器可能直接选择全表/顺序扫描。

对比表

维度	InnoDB 聚簇主键	InnoDB 二级索引	PostgreSQL B-tree
叶子主要保存	完整行	Secondary Key + PK	Key + Heap TID
获取非覆盖列	已有整行	再查主键树	通常访问 Heap
表组织	围绕聚簇主键	不改变表组织	Heap 为主，Index 独立
Index-only 条件	查询列均在 Index Entry	同左	还受 Visibility Map 影响

掌握标准

• InnoDB 聚簇主键叶子保存整行；
• Secondary Index 为什么保存 Primary Key；
• 回表和覆盖索引；
• 主键长度怎样放大二级索引；
• PostgreSQL Heap + TID 与 InnoDB 的差异；
• 为什么高命中比例时 Index Scan 可能更慢。

Related notes

• 联合索引与覆盖索引
• 索引选择性、基数与代价
• MySQL 与 PostgreSQL 的事务与并发差异