05.1.1.3 覆盖索引与回表
所属:全栈开发面试题库 / 05 数据库与存储 / 05.1 关系型数据库通用能力 / 05.1.1 索引原理与设计
覆盖范围
- 回表的两次查找过程与触发条件
- 覆盖索引判定、设计与代价权衡
- 索引下推(ICP)原理、可下推条件与边界
- JOIN 场景下的索引使用与驱动表选择
- 包含列索引、回表代价估算与跳过扫描概览
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.1.1.3-001 | 什么是回表,如何避免 | 进阶 | ★★★★★ |
| 05.1.1.3-002 | 回表为什么是两次 B+ 树查找 | 进阶 | ★★★★★ |
| 05.1.1.3-003 | 覆盖索引怎么判定 | 进阶 | ★★★★★ |
回表与覆盖主线
二级索引叶子:(索引列, 主键)
命中索引 → 拿到主键 → 聚簇索引取整行 = 回表
查询列全部在索引中 → 直接返回,不回表 = 覆盖索引
索引层先过滤非定位列 → 减少回表次数 = 索引下推 ICP
05.1.1.3-001 什么是回表?如何避免?
频率 ★★★★★ | 难度 进阶
一句话结论
通过二级索引找到主键后,再拿主键去聚簇索引查整行,这一过程就是回表;避免方式是把查询所需列纳入索引形成覆盖索引、只查必要列,或直接走主键/聚簇索引查询。
概念解释
- 回表:二级索引查询后再访问聚簇索引取非索引列。
- 两次查找:二级索引 B+ 树一次,聚簇索引 B+ 树一次。
- 避免路径:覆盖索引、减少
SELECT *、主键查询、索引下推减少回表次数。
原理流程
- 在二级索引树按索引列定位,叶子得到主键值。
- 若所需列不在索引中,用主键进入聚簇索引取整行。
- 覆盖索引场景下叶子项已含全部返回列,直接返回,不进入第二步。
代码 / 命令
-- 需要回表:idx_name(name) 叶子只有 name + id
SELECT id, name, age FROM users WHERE name = 'Ann';
-- 覆盖索引:把查询需要的列放进索引
ALTER TABLE users ADD INDEX idx_name_age (name, age);
SELECT name, age FROM users WHERE name = 'Ann'; -- 覆盖,不回表
常见误区
- 误区:
SELECT *也可能不回表。正解:星号取全部列,二级索引几乎不可能覆盖,基本必然回表。 - 误区:覆盖索引对前导模糊查询有效。正解:
LIKE '%x%'无法定位索引起点,覆盖无从谈起。
追问
- 问:覆盖索引在执行计划里怎么看?答:
Extra出现Using index表示覆盖索引;字段解读见 05.1.3.1。 - 问:索引下推能完全替代覆盖索引吗?答:不能,ICP 仍可能回表,只是先过滤减少次数;覆盖索引完全不回表。
05.1.1.3-002 回表为什么是两次 B+ 树查找?
频率 ★★★★★ | 难度 进阶
一句话结论
二级索引树只保存索引列与主键,整行数据在聚簇索引树;先在二级树定位主键,再在聚簇树按主键取行,两棵独立 B+ 树各一次查找,因此回表是两次索引查找。
概念解释
- 两棵树:每个二级索引是一棵独立 B+ 树,聚簇索引是另一棵。
- 查找链路:索引列 → 主键 → 整行。
- 代价叠加:回表次数等于命中行数,每行一次聚簇查找。
原理流程
- 二级索引扫描命中 N 条记录,得到 N 个主键。
- 每个主键进入聚簇索引叶子取整行。
- N 较大时回表随机读成为主要成本,覆盖索引可消除第二步。
代码 / 命令
二级索引树:name → id
聚簇索引树:id → 整行
回表次数 ≈ 命中二级索引的行数(未被覆盖/过滤减少的部分)
常见误区
- 误区:回表只是多读一次磁盘。正解:聚簇查找可能命中缓冲池,但逻辑上仍是一次独立 B+ 树定位;随机 I/O 成本取决于数据是否在内存。
- 误区:覆盖索引把数据复制了一份。正解:覆盖索引叶子保存相关列值,属于索引项的一部分,不是整行副本。
追问
- 问:命中行数多时回表为什么特别贵?答:每行一次聚簇随机读,N 次随机访问叠加;优化器可能因此改选全表扫描。
- 问:PG 的回表链路有何不同?答:PG 索引存 TID,按 TID 回堆取行;位图扫描先合并位图再批量回堆,详见 05.3.1。
05.1.1.3-003 覆盖索引怎么判定?
频率 ★★★★★ | 难度 进阶
一句话结论
查询所需的过滤列与返回列全部包含在所选索引中,即可直接从索引叶子返回结果,执行计划 Extra 显示 Using index;判定时按 SELECT 列表、WHERE、ORDER BY 涉及列逐一核对。
概念解释
- 覆盖列集合:过滤列、连接列、返回列的并集。
- Using index:MySQL 计划中表示覆盖索引,无需回表。
- 覆盖不改变定位:覆盖只省去回表,访问路径仍由前缀匹配决定。
原理流程
- 列出 SQL 中出现的所有索引相关列。
- 核对这些列是否都在候选索引中。
- 计划确认
Using index,则该查询被覆盖。
代码 / 命令
-- 索引 (name, age)
SELECT name, age FROM users WHERE name = 'Ann'; -- 覆盖:列都在索引中
EXPLAIN SELECT name, age FROM users WHERE name = 'Ann';
-- Extra: Using index
常见误区
- 误区:只要
WHERE列在索引中就是覆盖。正解:SELECT返回列也必须在索引中,否则仍要回表取列。 - 误区:覆盖索引一定比回表快。正解:覆盖省去回表,但宽索引扫描本身也有成本;低命中场景需结合计划比较。
追问
- 问:主键列需要显式加入二级索引吗?答:InnoDB 二级索引叶子已冗余主键,按主键返回时无需额外加入。
- 问:PG 计划里覆盖索引怎么标识?答:
Index Only Scan表示仅索引扫描,可见性由可见性映射或堆检查辅助,详见 05.3.2。
05.1.1.3-004 覆盖索引怎么设计?
频率 ★★★★☆ | 难度 进阶
一句话结论
先确定高频查询的过滤前缀与返回列,把等值过滤列放前缀,返回列追加在尾部形成覆盖;只纳入高频必需列,避免为个别查询建超宽索引。
概念解释
- 前缀定位列:服务
WHERE匹配的等值/范围列。 - 尾部覆盖列:
SELECT列表中不参与定位的展示列。 - 宽度控制:覆盖列越多索引越宽,写入与空间成本越高。
原理流程
- 提取高频 SQL 的过滤列与返回列。
- 定位列按最左前缀排列,返回列追加尾部。
- 评估索引宽度与写入代价,剔除低频覆盖列。
代码 / 命令
-- 高频查询:按 name 过滤,返回 name、age
SELECT name, age FROM users WHERE name = 'Ann';
ALTER TABLE users ADD INDEX idx_name_age (name, age);
常见误区
- 误区:把所有查询列都塞进一个索引做全覆盖。正解:宽索引放大写入与空间,应按查询频率分层覆盖。
- 误区:覆盖列可以放在定位列之前。正解:尾部覆盖列不参与定位,前置会破坏前缀匹配。
追问
- 问:覆盖索引能包含函数表达式结果吗?答:可借助表达式索引或生成列,MySQL 8.0 与 PG 支持程度不同,版本边界待确认。
- 问:大字段适合作为覆盖列吗?答:不适合,宽列显著放大索引体积,应评估外置或拆分。
05.1.1.3-005 覆盖索引的空间与写入代价是什么?
频率 ★★★☆☆ | 难度 进阶
一句话结论
覆盖索引把展示列冗余进索引,占用额外空间;写入时这些列的更新需要同步维护索引项,写入放大随覆盖列数量上升,需在查询收益与维护成本间权衡。
概念解释
- 空间冗余:覆盖列在索引中再存一份。
- 写入维护:覆盖列被更新时,对应索引项要删除旧值、写入新值。
- 收益对冲:省去回表的随机读,换取写入与空间成本。
原理流程
- 评估覆盖索引省去的回表次数与查询 QPS。
- 估算索引体积与写入放大。
- 高频读、低频写的表更适合覆盖;高频写表谨慎追加覆盖列。
代码 / 命令
覆盖索引体积 ≈ 索引列宽度 × 行数
写入维护:覆盖列 UPDATE → 索引项删除 + 插入(含 undo/redo)
常见误区
- 误区:覆盖索引只在查询时有成本。正解:建索引后的每次写入都承担维护成本。
- 误区:覆盖列更新不影响索引。正解:覆盖列值变化必须同步更新索引项,否则索引与数据不一致。
追问
- 问:怎么估算覆盖索引体积?答:按索引列字节数乘行数,结合页填充率估算;实际大小可查表空间统计。
- 问:覆盖索引与冗余索引如何区分?答:覆盖索引服务特定查询的返回列;冗余索引是前缀可被另一索引完全复用,判定见 05.1.1.5。
05.1.1.3-006 什么是索引下推(ICP)?
频率 ★★★★☆ | 难度 高级
一句话结论
索引下推(Index Condition Pushdown)把能在索引列上判断的 WHERE 条件下推到存储引擎层,在回表前先过滤,减少需要回表的行数;MySQL 5.6 起支持,计划 Extra 显示 Using index condition。
概念解释
- 下推位置:过滤条件在引擎层索引项上判断,而不是回表后在 Server 层过滤。
- 减少回表:只把满足条件的记录回表取整行。
- 适用条件:条件涉及索引列,且可在索引值上直接比较。
原理流程
- 无 ICP:按索引前缀找到一批主键,全部回表,Server 层再过滤剩余条件。
- 有 ICP:引擎在二级索引上直接判断剩余索引列条件。
- 仅满足条件的记录回表,回表次数下降。
代码 / 命令
-- 索引 (name, age)
SELECT * FROM users WHERE name LIKE 'A%' AND age = 18;
-- 无 ICP:name 命中的记录全部回表后再过滤 age
-- 有 ICP:索引层先判断 age = 18,只回表满足条件者
-- Extra: Using index condition
常见误区
- 误区:ICP 就是覆盖索引。正解:ICP 仍可能回表,只是减少次数;覆盖索引完全不回表。
- 误区:所有
WHERE条件都能下推。正解:非索引列条件、对索引列的函数运算无法在索引项上直接判断,不能下推。
追问
- 问:ICP 由哪个参数控制?答:
optimizer_switch中的index_condition_pushdown,默认开启;具体开关名以版本文档为准。 - 问:ICP 与最左前缀断裂的关系?答:断裂列条件可在 ICP 阶段过滤,但不能恢复索引定位路径。
05.1.1.3-007 ICP 与覆盖索引的区别是什么?
频率 ★★★☆☆ | 难度 进阶
一句话结论
覆盖索引解决"要不要回表"(列都在索引中,不回表);ICP 解决"回表前先过滤"(仍可能回表,但减少回表行数),两者可以同时出现。
概念解释
- 覆盖索引:返回列全部在索引,叶子直接给出结果。
- ICP:利用索引上的剩余条件先过滤,缩小回表集合。
- 叠加效果:既覆盖又下推时,计划标记可能同时体现两类优化。
原理流程
- 先判断返回列是否被索引覆盖。
- 未覆盖时,剩余索引列条件由 ICP 在引擎层过滤。
- 对比两者减少的成本:覆盖消除回表,ICP 减少回表次数。
代码 / 命令
覆盖索引:SELECT name, age ... 索引 (name, age) → 不回表
ICP: SELECT * ... 索引 (name, age),age 条件先过滤 → 部分回表
常见误区
- 误区:出现
Using index condition就是覆盖索引。正解:该标记表示下推过滤;覆盖看Using index。 - 误区:有了 ICP 就不需要覆盖索引。正解:需要取整行的查询无法覆盖,ICP 只能缓解;返回列可控时覆盖仍更优。
追问
- 问:两者能在同一条 SQL 上同时生效吗?答:可以,先下推过滤再判断覆盖,计划标记按实际优化项显示。
- 问:PG 有类似机制吗?答:PG 在索引层做条件过滤的机制与口径不同,详见 05.3.2;不要直接套用 MySQL 的 ICP 术语。
05.1.1.3-008 哪些条件可以被下推?
频率 ★★★☆☆ | 难度 进阶
一句话结论
涉及索引列、可在索引值上直接比较的等值/范围条件可以下推;非索引列条件、对索引列的函数运算、存储过程/非确定性函数不能下推。
概念解释
- 可下推:索引列上的
=、>、<、LIKE 'x%'等比较。 - 不可下推:非索引列判断、列上函数/运算、外部函数。
- 下推前提:条件能从索引项取值直接计算。
原理流程
- 解析条件涉及的列是否属于当前索引。
- 判断条件是否为可在索引值上计算的简单比较。
- 满足则下推引擎层过滤,否则回表后在 Server 层处理。
代码 / 命令
-- 索引 (name, age)
WHERE name LIKE 'A%' AND age = 18 -- 两列都在索引,可下推
WHERE name LIKE 'A%' AND city = 'NY' -- city 非索引列,不可下推
WHERE LEFT(name, 2) = 'An' -- 列上函数,不可下推
常见误区
- 误区:只要条件出现在索引相关查询里就能下推。正解:非索引列或函数运算条件必须回表后判断。
- 误区:下推后条件就不再参与定位。正解:下推只改变过滤位置,定位路径仍由最左前缀决定。
追问
- 问:隐式转换后的条件能下推吗?答:列被隐式转换后索引原值无法直接比较,通常不能下推,且可能导致索引失效。
- 问:怎么确认条件是否下推?答:看计划
Extra是否出现Using index condition,并核对过滤列归属。
05.1.1.3-009 JOIN 时索引怎么用?驱动表怎么选?
频率 ★★★★☆ | 难度 进阶
一句话结论
嵌套循环连接中被驱动表的连接列必须有索引,否则每行都要全表扫描;优化器通常选择结果集小的表做驱动表,LEFT/RIGHT JOIN 会固定一侧驱动,具体由代价估算决定。
概念解释
- 驱动表:先被读取的表,其结果集决定内层查找次数。
- 被驱动表:按连接列被反复查找的表,连接列索引是性能关键。
- 连接算法:嵌套循环、块嵌套循环/哈希连接,索引影响算法选择。
原理流程
- 优化器估算各表过滤后行数与索引代价。
- 选择小结果集做驱动,被驱动表走连接列索引查找。
- 无合适索引时退化为缓冲块扫描或哈希连接。
代码 / 命令
-- 被驱动表 t2 的 join_col 必须有索引
SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id WHERE t1.status = 1;
-- 计划关注:type 为 ref/eq_ref 较好,ALL 表示全表扫描;
-- Extra 出现 Using join buffer 表示没有合适索引
常见误区
- 误区:驱动表越大越好。正解:外层结果集越大,内层查找次数越多;通常小结果集驱动更优。
- 误区:
LEFT JOIN的左表一定是驱动表且无需评估索引。正解:左表保留左连接语义,但右表连接列索引仍直接影响性能。
追问
- 问:
STRAIGHT_JOIN有什么用?答:强制左表驱动,用于优化器选错驱动顺序时手动干预,长期使用需评估维护成本。 - 问:怎么判断被驱动表是否走了索引?答:看计划中被驱动表的
type与key,ref/eq_ref表示命中连接索引。
05.1.1.3-010 被驱动表连接列索引为什么关键?
频率 ★★★★☆ | 难度 进阶
一句话结论
嵌套循环中驱动表每输出一行,就要在被驱动表按连接列查找一次;连接列有索引则每次定位为索引查找,无索引则每次全表扫描,复杂度从 N 查找放大为 N × M 扫描。
概念解释
- 查找次数:内层查找次数等于外层结果集行数。
- 索引查找:连接列索引把单次内层访问收敛到索引定位。
- 无索引退化:无索引时内层只能扫描全表或走缓冲块。
原理流程
- 外层输出 N 行。
- 每行用连接值在内层查找;有索引走索引定位,无索引扫描内层全表。
- N 大、M 大时无索引连接代价不可接受。
代码 / 命令
有索引:N 次索引查找(每次 O(log M))
无索引:N 次全表扫描(每次 O(M))→ O(N×M)
常见误区
- 误区:连接列类型一致就不需要索引。正解:类型一致只避免隐式转换,定位仍需索引支持。
- 误区:小表可以不建连接索引。正解:小表扫描成本低,但被驱动为大表时索引依然关键;角色由优化器决定。
追问
- 问:连接列字符集不一致会怎样?答:隐式转换导致索引失效,等价于连接列无索引。
- 问:联合索引能否服务连接列?答:连接列作为联合索引最左前缀时可以命中,尾部列不参与连接定位。
05.1.1.3-011 NLJ、BNL 与 Hash Join 对索引的依赖有什么不同?
频率 ★★★☆☆ | 难度 高级
一句话结论
嵌套循环(NLJ)强依赖被驱动表连接列索引;块嵌套循环(BNL)在无索引时把外层块放入缓冲逐行匹配;哈希连接(MySQL 8.0 / PG)用构建表哈希表匹配,不要求连接列索引,但构建侧需内存与哈希开销。
概念解释
- NLJ:外层每行驱动内层索引查找,索引是性能前提。
- BNL:无索引时缓存外层块,内层扫描与块比较,减少重复扫描。
- Hash Join:小表建哈希表,大表探测匹配,适合大结果集等值连接。
原理流程
- 优化器按数据量、索引与代价选择算法。
- NLJ 走索引查找;BNL 依赖连接缓冲;Hash Join 构建哈希表。
- 无索引的大连接优先评估哈希连接或补索引。
代码 / 命令
-- MySQL 8.0:计划 Extra 可能出现 Using join buffer (hash join)
EXPLAIN SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.id = t2.t1_id;
常见误区
- 误区:哈希连接可以替代所有连接索引。正解:哈希连接只解决等值连接匹配,范围连接、小结果集嵌套循环仍可能更优。
- 误区:BNL 是高性能算法。正解:它是无索引时的兜底方案,扫描次数仍多,应优先补索引。
追问
- 问:PG 的连接算法有哪些?答:嵌套循环、哈希连接、归并连接;归并连接要求两侧按连接列有序,详见 05.3.2。
- 问:Hash Join 受什么资源限制?答:构建侧哈希表占用内存,超阈值落盘,大表连接需关注内存与临时空间。
05.1.1.3-012 索引扫描回表代价怎么估算?
频率 ★★★☆☆ | 难度 进阶
一句话结论
回表代价取决于命中行数与每行聚簇随机读成本;命中行数多、回表随机 I/O 大时,优化器可能认为全表扫描更便宜,这也是低区分度索引被放弃的常见原因。
概念解释
- 命中行数:索引过滤后需要回表的记录数。
- 随机读成本:聚簇查找多为随机访问,单次成本高于顺序扫描。
- 代价比较:索引扫描 + 回表 vs 全表顺序扫描,优化器取低者。
原理流程
- 优化器按统计估算索引命中行数。
- 累加索引扫描代价与每行回表随机读代价。
- 与全表扫描代价比较,选择更低路径。
代码 / 命令
索引路径代价 ≈ 索引扫描代价 + 命中行数 × 单次回表代价
全表扫描代价 ≈ 全表顺序读取代价
命中比例高时,回表随机读可能超过全表顺序扫描
常见误区
- 误区:走索引一定比全表扫描快。正解:命中大量行时回表随机读更贵,优化器可能选全表扫描。
- 误区:回表代价只看行数。正解:还受缓冲池命中率、行宽度、磁盘类型影响,估算与实测可能有偏差。
追问
- 问:怎么降低回表代价?答:提高过滤力度减少命中行,或用覆盖索引消除回表。
- 问:估算与实际偏差大说明什么?答:统计信息过期或分布倾斜,详见 05.1.3.4。
05.1.1.3-013 什么是包含列(INCLUDE)索引?
频率 ★★★☆☆ | 难度 高级
一句话结论
包含列索引把非定位的展示列附加到索引叶子,不参与排序与定位,只用于覆盖;PG 与 SQL Server 原生支持 INCLUDE 子句,MySQL 8.0 没有独立 INCLUDE 语法,覆盖列只能并入联合索引(支持情况待确认以版本文档为准)。
概念解释
- 键列:参与索引排序与定位的列。
- 包含列:只存于叶子用于覆盖,不影响索引顺序。
- 语法差异:
INCLUDE (col)与把列放入联合索引的区别在于排序角色。
原理流程
- 确定定位键列与覆盖展示列。
- 支持
INCLUDE的引擎把展示列附加到叶子。 - 不支持时用联合索引尾部列实现等价覆盖。
代码 / 命令
-- PostgreSQL / SQL Server 风格
CREATE INDEX idx_tenant_status ON orders (tenant_id, status)
INCLUDE (amount, create_time);
-- MySQL:覆盖列并入联合索引尾部(无独立 INCLUDE 子句,版本边界待确认)
ALTER TABLE orders ADD INDEX idx_tenant_status_amount (tenant_id, status, amount);
常见误区
- 误区:包含列也参与最左前缀定位。正解:包含列只用于覆盖,不改变索引排序与定位路径。
- 误区:MySQL 可以直接写
INCLUDE。正解:8.0 语法支持情况需以版本文档核对;未确认前按联合索引实现。
追问
- 问:包含列能建为索引键列吗?答:可以但会影响排序与前缀,定位不需要的列放包含位置更合理。
- 问:PG 覆盖扫描与包含列什么关系?答:
Index Only Scan可借助包含列避免回堆,可见性检查机制见 05.3.2。
05.1.1.3-014 回表与缓冲池命中率有什么关系?
频率 ★★★☆☆ | 难度 进阶
一句话结论
回表访问聚簇数据页,若相关页已在缓冲池则成本低,否则触发磁盘随机读;热点表回表多但命中内存时表现尚可,冷数据回表则随机 I/O 明显,覆盖索引可减少对数据页的访问。
概念解释
- 缓冲池命中:所需页在内存,回表为内存访问。
- 未命中:页需从磁盘加载,随机寻道成本高。
- 访问模式:覆盖索引只访问索引页,数据页压力下降。
原理流程
- 二级索引扫描访问索引页。
- 回表检查聚簇页是否在缓冲池。
- 命中则内存取行,未命中则读盘;覆盖索引跳过第二步。
代码 / 命令
-- MySQL:查看缓冲池命中情况(指标口径以版本为准)
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
常见误区
- 误区:回表慢一定是磁盘问题。正解:缓冲池命中率、热点分布与行宽度都影响回表表现。
- 误区:覆盖索引能提高数据页缓存命中。正解:覆盖减少数据页访问,但数据页仍被其他查询使用;不要把二者混为一谈。
追问
- 问:缓冲池大小怎么影响回表?答:容量越大热点页越容易常驻,回表未命中率下降;容量不足时热点被频繁淘汰。
- 问:冷备份恢复后第一次查询为什么慢?答:缓冲池尚未预热,回表与索引页都需从磁盘加载。
05.1.1.3-015 延迟关联的索引原理是什么?
频率 ★★★☆☆ | 难度 进阶
一句话结论
深分页场景先用覆盖索引在子查询中取到主键集合,再按主键回表取整行;回表只发生在最终少量记录上,避免大偏移后逐行回表的浪费。
概念解释
- 窄索引扫描:子查询只走覆盖索引列与主键,扫描代价低。
- 少量回表:最终
LIMIT条记录才回表取整行。 - 适用边界:无法跳页,本质仍是按索引顺序翻页;完整改写方案见 05.1.3.3。
原理流程
- 内层子查询按覆盖索引过滤排序,取本页主键。
- 外层按主键关联回表取整行。
- 回表次数从偏移量级降到页大小级。
代码 / 命令
SELECT o.*
FROM orders o
JOIN (
SELECT id FROM orders
WHERE status = 1 ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;
常见误区
- 误区:延迟关联解决了深分页的扫描量问题。正解:内层仍需跳过前 100 万行,只是把回表代价延后到少量行;根本方案是游标/续传分页。
- 误区:子查询可以任意选列。正解:内层应只保留主键与必要过滤列,保持覆盖窄索引。
追问
- 问:为什么延迟关联比直接
LIMIT回表快?答:直接分页时前 100 万行都要回表取行;延迟关联先在窄索引上跳过,只回表 20 行。 - 问:游标分页和延迟关联哪个更彻底?答:游标记住上一页末尾位置,直接从该位置继续扫描,无偏移跳过;适合"下一页"场景,见 05.1.3.3。
05.1.1.3-016 二级索引叶子存主键的设计原因是什么?
频率 ★★☆☆☆ | 难度 基础
一句话结论
聚簇数据重组后物理位置会变,二级索引存主键这种稳定标识可避免索引项随行移动而失效;主键冗余也使覆盖索引能够直接返回索引列与主键。
概念解释
- 稳定标识:主键在行移动后不变。
- 回表锚点:主键是聚簇索引的查找键。
- 覆盖基础:主键天然存在于二级索引叶子。
原理流程
- 插入时二级索引记录索引键与主键。
- 聚簇页分裂/重建移动数据行,主键保持不变。
- 回表仍按主键定位,二级索引项无需改写。
代码 / 命令
二级索引叶子项:(索引列值, 主键值)
主键变化才需要重建相关二级索引项;物理位置变化不影响项内主键
常见误区
- 误区:主键被更新后二级索引自动跟随。正解:主键值修改属于键变更,相关二级索引项要同步维护。
- 误区:无主键表的二级索引也存稳定标识。正解:无主键时依赖隐藏
ROW_ID,稳定性与可维护性更差。
追问
- 问:宽主键为什么放大所有二级索引?答:主键冗余进每个二级索引叶子,主键越宽每项越大。
- 问:PG 为什么不存主键?答:PG 是堆表,索引存 TID 定位行版本,组织方式不同,详见 05.1.1.1。
05.1.1.3-017 索引跳过扫描(Skip Scan)是什么?
频率 ★★☆☆☆ | 难度 高级
一句话结论
跳过扫描是优化器在缺少首列条件时,把联合索引首列的不同值分组后逐组扫描后续列的策略,用于部分缓解最左前缀断裂;MySQL/PG 支持程度与触发条件不同,具体版本行为待确认。
概念解释
- 分组扫描:先取首列不同值,再按每组扫描后续索引列。
- 适用场景:首列基数低、后续列有条件时。
- 版本差异:并非所有引擎与版本都支持,触发受优化器策略控制。
原理流程
- 优化器评估首列不同值数量。
- 对每个首列值分组扫描后续索引路径。
- 合并各组结果;分组过多时成本可能高于全表扫描。
代码 / 命令
-- 索引 (a, b),查询只给 b 条件
SELECT * FROM t WHERE b = 2;
-- 跳过扫描:按 a 的不同值分组,每组在 b 上定位(是否触发看版本与计划)
常见误区
- 误区:跳过扫描能让所有缺首列查询走联合索引。正解:只在首列分组少、后续列可定位时触发,分组多则代价不划算。
- 误区:跳过扫描等价于覆盖索引。正解:它解决定位路径问题,回表与覆盖是另一环节。
追问
- 问:怎么判断是否触发跳过扫描?答:看执行计划中的访问类型与索引扫描方式;MySQL 与 PG 计划标识不同,以当前版本为准(待确认)。
- 问:缺首列查询的根本解法是什么?答:按真实查询模式补建匹配前缀的索引,跳过扫描只是优化器兜底。
05.1.1.3-018 覆盖索引设计评审有哪些要点?
频率 ★★★★☆ | 难度 进阶
一句话结论
评审覆盖索引先核对返回列是否真的高频、覆盖列宽度与写入代价是否可接受,再确认定位前缀与覆盖列分离,避免把低频展示列固化进索引造成长期写入放大。
概念解释
- 高频核对:覆盖列来自高频 SQL 的
SELECT列表。 - 宽度评审:覆盖列字节数乘行数估算体积。
- 定位/覆盖分离:前缀列服务定位,尾部列服务覆盖。
原理流程
- 汇总候选覆盖索引服务的 SQL 与 QPS。
- 逐项核对返回列必要性,剔除低频列。
- 评估写入放大与索引体积,上线后用计划与使用统计复核。
代码 / 命令
评审清单:
返回列是否高频出现?覆盖列总宽度是否可控?
定位前缀是否满足最左规则?是否存在更窄的等价索引?
写入放大与表更新频率是否匹配?
常见误区
- 误区:评审只看查询是否被覆盖。正解:必须同时评估索引宽度、写入成本与长期维护收益。
- 误区:覆盖索引建成后无需复查。正解:查询变化后尾部覆盖列可能失效,需定期清理。
追问
- 问:一个覆盖索引服务多条查询怎么取舍列?答:取公共返回列,个别查询的私有列不纳入,避免索引过宽。
- 问:怎么验证覆盖索引生效?答:计划出现
Using index(MySQL)或Index Only Scan(PG),并核对命中行数与耗时。
参考来源
- MySQL 8.0 Reference Manual: Covering Indexes:https://dev.mysql.com/doc/refman/8.0/en/innodb-secondary-indexes.html
- MySQL 8.0 Reference Manual: Index Condition Pushdown:https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown.html
- PostgreSQL Documentation: Index-Only Scans:https://www.postgresql.org/docs/current/indexes-index-only-scans.html
- JavaGuide:MySQL 索引:https://javaguide.cn/database/mysql/mysql-index.html