聚簇索引
InnoDB 使用聚簇索引组织行数据。本文讲解 MySQL 8.0 的聚簇键选择和维护成本。
1. 面试核心
先记住这几条,再读正文- InnoDB 的聚簇索引叶子保存完整的行,二级索引记录保存聚簇键,靠它找到行。卡 · 聚簇索引
- InnoDB 优先用主键做聚簇键,没有主键时选第一个所有列都非空的唯一索引。卡 · 没有主键时的聚簇索引
- 两者都没有时,InnoDB 生成 6 字节的隐藏行 ID 作为聚簇键。卡 · 没有主键时的聚簇索引
- 覆盖索引省掉的是取列的回表,MVCC 判断可见性时仍可能访问聚簇记录。卡 · 覆盖索引
- 更新主键时,聚簇记录和每个二级索引里的定位键都要跟着改。卡 · 主键更新
2. 聚簇索引
InnoDB 按聚簇键顺序组织叶子记录。叶子记录组织该行的所有列。DYNAMIC 行格式可将较长的变长值放入溢出页。叶子记录保存指向这些值的指针。
SELECT * FROM users WHERE id = 42;
若 id 是主键,查到聚簇叶子便能定位整行。存在页外值时,还需读取溢出页。
一张 InnoDB 表只有一个聚簇索引。键顺序是逻辑顺序,相邻页在磁盘上可以分散存放。
3. 没有主键时的聚簇索引
InnoDB 按以下顺序选择聚簇键:
- 表有显式主键时,使用主键。
- 否则使用第一个所有列都非空的唯一索引。
- 两者都缺少时,生成 6 字节隐藏行 ID。
隐藏聚簇索引名为 GEN_CLUST_INDEX。它的行 ID 由 InnoDB 内部维护。应用需要稳定引用一行时,应定义显式主键。
4. 二级索引与回表
二级索引包含聚簇键,查询缺失列时需要回表。访问步骤见联合索引与覆盖索引。
5. 覆盖索引
覆盖查询满足取列需求,版本判断仍可访问聚簇记录。机制见联合索引与覆盖索引。
6. 主键宽度
二级索引记录包含聚簇键。主键越宽,每个二级索引的定位信息就越大。同一页能容纳的记录更少,缓存也需承担更多空间。
短而稳定的键能减少这些成本。递增键把写入集中到右侧,随机键分散写入位置。页面调整的过程见 插入、删除与页分裂。
7. 主键更新
主键更新会改变行的聚簇键。InnoDB 要调整聚簇记录,并维护二级索引的定位信息。二级索引越多,维护范围越大。
业务编号经常变化时,可用稳定 ID 作为主键。业务编号另设唯一约束。编号修改便无需同时改变所有二级索引的定位键。
8. 范围查询与排序
主键范围查询可以沿聚簇叶子顺序读取。
SELECT * FROM users
WHERE id >= 10000 AND id < 10100;
二级索引范围查询需要完整行时,还需按聚簇键取列。返回行数很多时,回表成本随之增加。优化器据此比较二级索引访问和聚簇索引扫描。
9. 常见问题
9.1 主键与存储引擎
在 InnoDB 中,显式主键就是聚簇索引。其他存储引擎有各自的行组织方式。回答时应先说明数据库和存储引擎。
10. 面试题卡片
先想答案,再翻面先在心里答一遍,再点开卡片对照参考回答。卡片之间可以左右切换,看完会回到这里。
逐张翻看 4 张 →聚簇索引
InnoDB 聚簇索引和二级索引有什么区别,什么情况下会回表
考察 3 点 · 相关 §8未标记
覆盖索引
覆盖索引为什么仍可能访问聚簇索引?
本站自测考察 2 点 · 相关 §5未标记
没有主键时的聚簇索引
没有主键时 InnoDB 如何组织行,选择主键要考虑什么?
本站自测考察 2 点 · 相关 §3 §6未标记
主键更新
为什么频繁修改主键会增加二级索引维护成本?
本站自测考察 1 点 · 相关 §7未标记
未标记的卡读完后明天进入复习;已标记的按状态安排
11. 参考资料
- MySQL 8.0:Clustered and Secondary Indexes
- MySQL 8.0:The Physical Structure of an InnoDB Index
- MySQL 8.0:MVCC 与二级索引
- MySQL 8.0:行格式
- MySQL 8.0:Changing the Clustered Index
来源:Coding 101 原文 · 免费阅读,引用请保留出处。内容使用说明