05.1.1 索引原理与设计
所属:全栈开发面试题库 / 05 数据库与存储 / 05.1 关系型数据库通用能力
这个知识域解决什么问题
索引是关系型数据库性能最主要的杠杆,本域回答"索引为什么长这样、怎么建、为什么会失效、上线后怎么治理"。内容聚焦数据结构与设计原理:B+ 树组织方式、聚簇与二级索引、联合索引最左前缀、回表与覆盖、选择性统计、索引评审与线上维护。计划解读、慢查询改写、批量操作等已在 05.1.3 SQL 优化与执行计划 展开,本域只在必要处交叉引用;MySQL 引擎实战见 05.2 MySQL,PostgreSQL 索引类型对比见 05.3 PostgreSQL。
知识地图
表数据(堆/聚簇组织)
→ 索引结构:B+ 树(非叶子存键+指针,叶子有序链表)
├─ 聚簇索引:叶子即整行,每表一个
└─ 二级索引:叶子存索引列 + 主键,查询可能回表
索引设计主线:
结构原理(1)→ 联合索引与最左前缀(2)→ 覆盖索引与回表(3)
→ 选择性统计与失效判断(4)→ 设计评审与线上治理(5)
子主题
| 编号 | 子主题 | 核心内容 | 题目数 |
|---|---|---|---|
| 05.1.1.1 | B+ 树与索引结构 | 页与 I/O、B+ 树层数与扇出、聚簇/二级索引、主键选择、页分裂、哈希索引与 AHI、MySQL/PG 存储组织差异 | 18 |
| 05.1.1.2 | 联合索引与最左前缀 | 多列排序结构、等值/范围/排序列放置、最左前缀命中与断裂、列顺序设计、联合索引排序与分组 | 18 |
| 05.1.1.3 | 覆盖索引与回表 | 回表两次查找、覆盖索引判定、索引下推 ICP、JOIN 与索引、包含列、回表代价权衡 | 18 |
| 05.1.1.4 | 索引选择性统计与失效 | 基数与选择性、采样估算、函数/隐式转换/前导模糊等失效原理、前缀索引、索引合并、唯一与普通索引、选错索引 | 18 |
| 05.1.1.5 | 索引设计评审与线上治理 | 查询梳理、索引数量与写入放大、无用/冗余索引识别、上线验证、大表加索引流程、索引监控与回滚 | 18 |
合计 90 题。 每题按「一句话结论 → 概念解释 → 原理流程 → 代码/命令 → 常见误区 → 追问(带答案)」组织;SQL 片段为可核对语句,MySQL 与 PostgreSQL 差异分别标注,版本行为不确定处标「待确认」。
学习路径
B+ 树与索引结构(1)
→ 联合索引与最左前缀(2)
→ 覆盖索引与回表(3)
→ 索引选择性统计与失效(4)
→ 索引设计评审与线上治理(5)
自检清单
- [ ] 能说清 B+ 树非叶子与叶子各存什么,估算三层树能承载的行数规模
- [ ] 能区分聚簇索引与二级索引,解释二级索引叶子为什么存主键
- [ ] 能按最左前缀规则判断一条查询能否命中联合索引,说清范围列对后续列的截断
- [ ] 能判断回表发生条件,设计覆盖索引并说明其空间与写入代价
- [ ] 能计算索引选择性,列出常见失效场景及其原理
- [ ] 能按查询模式评审索引方案,识别重复/冗余/无用索引并走线上变更流程