罗罗编程学习记录
本页内容 展开查看

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 *、主键查询、索引下推减少回表次数。

原理流程

  1. 在二级索引树按索引列定位,叶子得到主键值。
  2. 若所需列不在索引中,用主键进入聚簇索引取整行。
  3. 覆盖索引场景下叶子项已含全部返回列,直接返回,不进入第二步。

代码 / 命令

-- 需要回表: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+ 树,聚簇索引是另一棵。
  • 查找链路:索引列 → 主键 → 整行。
  • 代价叠加:回表次数等于命中行数,每行一次聚簇查找。

原理流程

  1. 二级索引扫描命中 N 条记录,得到 N 个主键。
  2. 每个主键进入聚簇索引叶子取整行。
  3. 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 计划中表示覆盖索引,无需回表。
  • 覆盖不改变定位:覆盖只省去回表,访问路径仍由前缀匹配决定。

原理流程

  1. 列出 SQL 中出现的所有索引相关列。
  2. 核对这些列是否都在候选索引中。
  3. 计划确认 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 列表中不参与定位的展示列。
  • 宽度控制:覆盖列越多索引越宽,写入与空间成本越高。

原理流程

  1. 提取高频 SQL 的过滤列与返回列。
  2. 定位列按最左前缀排列,返回列追加尾部。
  3. 评估索引宽度与写入代价,剔除低频覆盖列。

代码 / 命令

-- 高频查询:按 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 覆盖索引的空间与写入代价是什么?

频率 ★★★☆☆ | 难度 进阶

一句话结论

覆盖索引把展示列冗余进索引,占用额外空间;写入时这些列的更新需要同步维护索引项,写入放大随覆盖列数量上升,需在查询收益与维护成本间权衡。

概念解释

  • 空间冗余:覆盖列在索引中再存一份。
  • 写入维护:覆盖列被更新时,对应索引项要删除旧值、写入新值。
  • 收益对冲:省去回表的随机读,换取写入与空间成本。

原理流程

  1. 评估覆盖索引省去的回表次数与查询 QPS。
  2. 估算索引体积与写入放大。
  3. 高频读、低频写的表更适合覆盖;高频写表谨慎追加覆盖列。

代码 / 命令

覆盖索引体积 ≈ 索引列宽度 × 行数
写入维护:覆盖列 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 层过滤。
  • 减少回表:只把满足条件的记录回表取整行。
  • 适用条件:条件涉及索引列,且可在索引值上直接比较。

原理流程

  1. 无 ICP:按索引前缀找到一批主键,全部回表,Server 层再过滤剩余条件。
  2. 有 ICP:引擎在二级索引上直接判断剩余索引列条件。
  3. 仅满足条件的记录回表,回表次数下降。

代码 / 命令

-- 索引 (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:利用索引上的剩余条件先过滤,缩小回表集合。
  • 叠加效果:既覆盖又下推时,计划标记可能同时体现两类优化。

原理流程

  1. 先判断返回列是否被索引覆盖。
  2. 未覆盖时,剩余索引列条件由 ICP 在引擎层过滤。
  3. 对比两者减少的成本:覆盖消除回表,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%' 等比较。
  • 不可下推:非索引列判断、列上函数/运算、外部函数。
  • 下推前提:条件能从索引项取值直接计算。

原理流程

  1. 解析条件涉及的列是否属于当前索引。
  2. 判断条件是否为可在索引值上计算的简单比较。
  3. 满足则下推引擎层过滤,否则回表后在 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 会固定一侧驱动,具体由代价估算决定。

概念解释

  • 驱动表:先被读取的表,其结果集决定内层查找次数。
  • 被驱动表:按连接列被反复查找的表,连接列索引是性能关键。
  • 连接算法:嵌套循环、块嵌套循环/哈希连接,索引影响算法选择。

原理流程

  1. 优化器估算各表过滤后行数与索引代价。
  2. 选择小结果集做驱动,被驱动表走连接列索引查找。
  3. 无合适索引时退化为缓冲块扫描或哈希连接。

代码 / 命令

-- 被驱动表 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 扫描。

概念解释

  • 查找次数:内层查找次数等于外层结果集行数。
  • 索引查找:连接列索引把单次内层访问收敛到索引定位。
  • 无索引退化:无索引时内层只能扫描全表或走缓冲块。

原理流程

  1. 外层输出 N 行。
  2. 每行用连接值在内层查找;有索引走索引定位,无索引扫描内层全表。
  3. 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:小表建哈希表,大表探测匹配,适合大结果集等值连接。

原理流程

  1. 优化器按数据量、索引与代价选择算法。
  2. NLJ 走索引查找;BNL 依赖连接缓冲;Hash Join 构建哈希表。
  3. 无索引的大连接优先评估哈希连接或补索引。

代码 / 命令

-- 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 全表顺序扫描,优化器取低者。

原理流程

  1. 优化器按统计估算索引命中行数。
  2. 累加索引扫描代价与每行回表随机读代价。
  3. 与全表扫描代价比较,选择更低路径。

代码 / 命令

索引路径代价 ≈ 索引扫描代价 + 命中行数 × 单次回表代价
全表扫描代价 ≈ 全表顺序读取代价
命中比例高时,回表随机读可能超过全表顺序扫描

常见误区

  • 误区:走索引一定比全表扫描快。正解:命中大量行时回表随机读更贵,优化器可能选全表扫描。
  • 误区:回表代价只看行数。正解:还受缓冲池命中率、行宽度、磁盘类型影响,估算与实测可能有偏差。

追问

  • 问:怎么降低回表代价?答:提高过滤力度减少命中行,或用覆盖索引消除回表。
  • 问:估算与实际偏差大说明什么?答:统计信息过期或分布倾斜,详见 05.1.3.4。

05.1.1.3-013 什么是包含列(INCLUDE)索引?

频率 ★★★☆☆ | 难度 高级

一句话结论

包含列索引把非定位的展示列附加到索引叶子,不参与排序与定位,只用于覆盖;PG 与 SQL Server 原生支持 INCLUDE 子句,MySQL 8.0 没有独立 INCLUDE 语法,覆盖列只能并入联合索引(支持情况待确认以版本文档为准)。

概念解释

  • 键列:参与索引排序与定位的列。
  • 包含列:只存于叶子用于覆盖,不影响索引顺序。
  • 语法差异:INCLUDE (col) 与把列放入联合索引的区别在于排序角色。

原理流程

  1. 确定定位键列与覆盖展示列。
  2. 支持 INCLUDE 的引擎把展示列附加到叶子。
  3. 不支持时用联合索引尾部列实现等价覆盖。

代码 / 命令

-- 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 明显,覆盖索引可减少对数据页的访问。

概念解释

  • 缓冲池命中:所需页在内存,回表为内存访问。
  • 未命中:页需从磁盘加载,随机寻道成本高。
  • 访问模式:覆盖索引只访问索引页,数据页压力下降。

原理流程

  1. 二级索引扫描访问索引页。
  2. 回表检查聚簇页是否在缓冲池。
  3. 命中则内存取行,未命中则读盘;覆盖索引跳过第二步。

代码 / 命令

-- MySQL:查看缓冲池命中情况(指标口径以版本为准)
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

常见误区

  • 误区:回表慢一定是磁盘问题。正解:缓冲池命中率、热点分布与行宽度都影响回表表现。
  • 误区:覆盖索引能提高数据页缓存命中。正解:覆盖减少数据页访问,但数据页仍被其他查询使用;不要把二者混为一谈。

追问

  • 问:缓冲池大小怎么影响回表?答:容量越大热点页越容易常驻,回表未命中率下降;容量不足时热点被频繁淘汰。
  • 问:冷备份恢复后第一次查询为什么慢?答:缓冲池尚未预热,回表与索引页都需从磁盘加载。

05.1.1.3-015 延迟关联的索引原理是什么?

频率 ★★★☆☆ | 难度 进阶

一句话结论

深分页场景先用覆盖索引在子查询中取到主键集合,再按主键回表取整行;回表只发生在最终少量记录上,避免大偏移后逐行回表的浪费。

概念解释

  • 窄索引扫描:子查询只走覆盖索引列与主键,扫描代价低。
  • 少量回表:最终 LIMIT 条记录才回表取整行。
  • 适用边界:无法跳页,本质仍是按索引顺序翻页;完整改写方案见 05.1.3.3。

原理流程

  1. 内层子查询按覆盖索引过滤排序,取本页主键。
  2. 外层按主键关联回表取整行。
  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 二级索引叶子存主键的设计原因是什么?

频率 ★★☆☆☆ | 难度 基础

一句话结论

聚簇数据重组后物理位置会变,二级索引存主键这种稳定标识可避免索引项随行移动而失效;主键冗余也使覆盖索引能够直接返回索引列与主键。

概念解释

  • 稳定标识:主键在行移动后不变。
  • 回表锚点:主键是聚簇索引的查找键。
  • 覆盖基础:主键天然存在于二级索引叶子。

原理流程

  1. 插入时二级索引记录索引键与主键。
  2. 聚簇页分裂/重建移动数据行,主键保持不变。
  3. 回表仍按主键定位,二级索引项无需改写。

代码 / 命令

二级索引叶子项:(索引列值, 主键值)
主键变化才需要重建相关二级索引项;物理位置变化不影响项内主键

常见误区

  • 误区:主键被更新后二级索引自动跟随。正解:主键值修改属于键变更,相关二级索引项要同步维护。
  • 误区:无主键表的二级索引也存稳定标识。正解:无主键时依赖隐藏 ROW_ID,稳定性与可维护性更差。

追问

  • 问:宽主键为什么放大所有二级索引?答:主键冗余进每个二级索引叶子,主键越宽每项越大。
  • 问:PG 为什么不存主键?答:PG 是堆表,索引存 TID 定位行版本,组织方式不同,详见 05.1.1.1。

05.1.1.3-017 索引跳过扫描(Skip Scan)是什么?

频率 ★★☆☆☆ | 难度 高级

一句话结论

跳过扫描是优化器在缺少首列条件时,把联合索引首列的不同值分组后逐组扫描后续列的策略,用于部分缓解最左前缀断裂;MySQL/PG 支持程度与触发条件不同,具体版本行为待确认。

概念解释

  • 分组扫描:先取首列不同值,再按每组扫描后续索引列。
  • 适用场景:首列基数低、后续列有条件时。
  • 版本差异:并非所有引擎与版本都支持,触发受优化器策略控制。

原理流程

  1. 优化器评估首列不同值数量。
  2. 对每个首列值分组扫描后续索引路径。
  3. 合并各组结果;分组过多时成本可能高于全表扫描。

代码 / 命令

-- 索引 (a, b),查询只给 b 条件
SELECT * FROM t WHERE b = 2;
-- 跳过扫描:按 a 的不同值分组,每组在 b 上定位(是否触发看版本与计划)

常见误区

  • 误区:跳过扫描能让所有缺首列查询走联合索引。正解:只在首列分组少、后续列可定位时触发,分组多则代价不划算。
  • 误区:跳过扫描等价于覆盖索引。正解:它解决定位路径问题,回表与覆盖是另一环节。

追问

  • 问:怎么判断是否触发跳过扫描?答:看执行计划中的访问类型与索引扫描方式;MySQL 与 PG 计划标识不同,以当前版本为准(待确认)。
  • 问:缺首列查询的根本解法是什么?答:按真实查询模式补建匹配前缀的索引,跳过扫描只是优化器兜底。

05.1.1.3-018 覆盖索引设计评审有哪些要点?

频率 ★★★★☆ | 难度 进阶

一句话结论

评审覆盖索引先核对返回列是否真的高频、覆盖列宽度与写入代价是否可接受,再确认定位前缀与覆盖列分离,避免把低频展示列固化进索引造成长期写入放大。

概念解释

  • 高频核对:覆盖列来自高频 SQL 的 SELECT 列表。
  • 宽度评审:覆盖列字节数乘行数估算体积。
  • 定位/覆盖分离:前缀列服务定位,尾部列服务覆盖。

原理流程

  1. 汇总候选覆盖索引服务的 SQL 与 QPS。
  2. 逐项核对返回列必要性,剔除低频列。
  3. 评估写入放大与索引体积,上线后用计划与使用统计复核。

代码 / 命令

评审清单:
  返回列是否高频出现?覆盖列总宽度是否可控?
  定位前缀是否满足最左规则?是否存在更窄的等价索引?
  写入放大与表更新频率是否匹配?

常见误区

  • 误区:评审只看查询是否被覆盖。正解:必须同时评估索引宽度、写入成本与长期维护收益。
  • 误区:覆盖索引建成后无需复查。正解:查询变化后尾部覆盖列可能失效,需定期清理。

追问

  • 问:一个覆盖索引服务多条查询怎么取舍列?答:取公共返回列,个别查询的私有列不纳入,避免索引过宽。
  • 问:怎么验证覆盖索引生效?答:计划出现 Using index(MySQL)或 Index Only Scan(PG),并核对命中行数与耗时。

参考来源