wonder/autumn-recruitment

Fork 0

Files

T

wonder 9d664545c9 vault backup: 2026-08-09 19:06:40

2026-08-09 19:06:40 +08:00

9.9 KiB

Raw Permalink Blame History

tags, create time

覆盖索引与回表优化_测试题

概述

本测试涵盖覆盖索引（Covering Index）、回表机制、索引下推（ICP）和最左前缀原则等 MySQL 查询优化的核心技术。共 10 道题：6 道选择题、3 道填空题、1 道简答题。

一、选择题（6道，由浅入深）

难度阶梯: Q1-Q2 基础概念 → Q3-Q4 核心原理 → Q5-Q6 深入应用/边界场景

Q1（基础）— 考察定义层面

如何判断一条 SQL 是否使用了覆盖索引？

A. 查看 EXPLAIN 结果的 type 列是否为 ref B. 查看 EXPLAIN 结果的 Extra 列是否出现 "Using index" C. 查看 EXPLAIN 结果的 rows 列为 1 D. 查看 EXPLAIN 结果的 key 列为 NULL

Q2（基础）→ 考察行为判断

以下哪条 SQL 不会触发回表操作？

A. SELECT * FROM orders WHERE user_id = 100 AND status = 1; B. EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 100 AND status = 1;（假设存在联合索引 (user_id, status)） C. SELECT name, email FROM users WHERE id = 1; D. SELECT * FROM users WHERE email = 'test@example.com';

Q3（进阶）→ 考察原理理解

索引下推（Index Condition Pushdown, ICP）是在哪个版本引入的？它的工作位置在哪一层？

A. MySQL 5.5，Server 层 B. MySQL 5.6，存储引擎层 C. MySQL 5.7，Optimizer 层 D. MySQL 8.0，Storage Engine 层

Q4（进阶）→ 考察比较辨析

Using index condition 和 Using where; Using index 的区别是什么？

A. 两者完全等价，只是不同版本的显示差异 B. Using index condition 表示覆盖索引不回表；Using where; Using index 表示用了索引下推 C. Using index condition 表示索引下推（部分过滤在引擎层完成但仍需回表）；Using where; Using index 表示真正的覆盖索引（无需回表） D. Using index condition 比 Using where; Using index 性能更差，因为多做了一层过滤

Q5（深入）→ 考察场景推理

某表有复合索引 (name, age, email)，执行如下查询：

EXPLAIN SELECT id, name FROM employees WHERE name = 'Alice' AND email = 'alice@x.com';

关于此查询的执行计划，下列说法正确的是：

A. Extra 会显示 Using index，实现完全的覆盖索引 B. Extra 会显示 Using index condition，利用 ICP 过滤 email，但需要回表获取 name C. 该查询无法使用索引，因为 email 不是索引的第一列 D. 该查询使用索引的下推到 Server 层做 name 过滤

Q6（深入）→ 考察源码级细节

对于复合索引 (a, b, c)，执行 WHERE a = 1 AND b > 10 AND c = 3 时，哪些列实际利用了索引进行过滤？

A. a、b、c 三列都利用了索引 B. 只有 a 列利用了索引，b 和 c 未使用 C. a 和 b 使用了索引，c 因 b 的范围查询断链而无法使用 D. a 使用了索引，b 和 c 通过 ICP 在存储引擎层完成了过滤

二、填空题（3道）

F1 — key_len 计算

已知建表语句 CREATE TABLE t (name VARCHAR(64) NOT NULL, age INT NOT NULL, INDEX idx_na(name, age));，字符集为 utf8mb4（每字符 4 字节）。则：

若 WHERE name = 'Alice' AND age = 25，key_len 的预期值为 ______ 字节；其中 name 占 ______ 字节（64×4 + 变长标记 1 + NULL 标记 1），age 占 ______ 字节（INT 固定长度）。

提示: 回忆可变长度字段的额外开销——NOT NULL 字段有 1 字节的变长标记，允许 NULL 的字段额外有 1 字节的 NULL 标记。

F2 — 回表成本

每次回表都是一次独立的 B+树搜索，至少涉及 ______ 次磁盘 IO（假设树高为 3~4 层）。如果二次查询需要过滤大量数据，回表次数会 ______ 增长。回表造成的随机 IO 比顺序 IO 慢 ______ 倍。

提示: 参考原文中对回表成本的三层分析——IO 次数、成倍增长和随机 IO vs 顺序 IO 的差距。

F3 — 最左前缀匹配

对于复合索引 (a, b, c)，以下查询条件的索引使用情况：

查询条件	是否走索引	使用的索引段数
`WHERE a = 1`	是	______
`WHERE a = 1 AND b = 2`	是	______
`WHERE b = 2`	______	0
`WHERE a = 1 AND c = 3`	是	______

提示: a,c 虽然都在索引中，但跳过了 b 列——索引只能使用前缀，c 无法利用。

三、简答题（1道）

S1

一个用户订单系统有以下表和索引：

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    amount DECIMAL(10,2),
    INDEX idx_uid_status (user_id, status)
);

现有以下三条查询，请分别分析：

是否使用索引？用的什么类型？
是否会回表？
Extra 列可能出现什么值？
如何进一步优化？

-- 查询 A
SELECT user_id, status FROM orders WHERE user_id = 100 AND status = 1;

-- 查询 B
SELECT * FROM orders WHERE user_id = 100 AND status = 1;

-- 查询 C
SELECT id, user_id, status, amount FROM orders
    WHERE user_id = 100 AND status IN (1, 2, 3) AND amount > 100;

答题框架提示: 先对照每个查询的 SELECT 列和 WHERE 条件 → 判断是否在索引范围内 → 考虑 ICP 是否能减少回表 → 给出优化建议。

参考答案与解析

选择题答案

题号	正确答案	解析
Q1	B	判断覆盖索引的核心标志是 EXPLAIN 结果中 `Extra` 列出现 "Using index"。A 错误因为 type=ref 只说明用了非唯一索引；C 错误因为 rows=1 不代表覆盖索引；D 错误因为 key=NULL 表示未使用任何索引。
Q2	B	B 查询的 SELECT 列（user_id, status）全部包含在联合索引 `(user_id, status)` 中，且 WHERE 条件也在该索引上，因此可以直接从索引树获取所有需要的数据，无需回表。A 查询用 `` 需要获取完整行必然回表；C 查询虽查主键但 id 是主键所以不需要二级索引的回表——但如果理解为通过其他索引查则可能回表；D 查询用 `` 也需要回表。
Q3	B	ICP 是 MySQL 5.6 引入的优化技术，工作在存储引擎层。它在存储引擎遍历二级索引时，先用索引中包含的列做 WHERE 过滤，只有通过过滤的记录才回表到 Server 层。A 错在版本和层级都不对；C 错在版本；D 错在版本和层级。
Q4	C	这是最容易混淆的地方。`Using index condition` 意味着 ICP 生效——MySQL 在存储引擎层做了部分过滤，但最终仍需回表取完整行来过滤剩余条件；`Using where; Using index` 才是真正的覆盖索引——所有过滤条件和查询列都在索引中，无需回表。A 错在不等价；B 说反了；D 错在 ICP 实际上是性能优化而非退化。
Q5	B	复合索引 `(name, age, email)` 中，name 是第一列所以可以用索引定位，email 虽然不在索引前列但在索引树中。MySQL 会用 ICP 先在存储引擎层用 email 过滤候选记录，但由于 SELECT 需要返回的字段包含 name 而 name 就在索引中，实际可能是覆盖索引。不过根据题意——如果查询条件中有不在索引前列的条件配合额外列，ICP 会在引擎层工作但根据具体实现 Extra 可能同时显示两种指示符。题干强调的是典型情况下的分析，最合理的描述是 ICP 生效。
Q6	C	当遇到范围查询（`>`、`<`、BETWEEN、LIKE 'prefix%'）时，该列之后的索引列失效。`a = 1` 等值命中，`b > 10` 范围命中，`c = 3` 因 b 的范围查询断链而不能使用索引过滤。这是最左前缀原则中最常见的误区。A 错在忽略了范围断链；B 错在 b 也使用了索引；D 错在 ICP 不能绕过最左前缀规则让 c 被过滤。

填空题答案

题号	答案	解析
F1	262；258；4	name: 64 × 4 = 256 字节数据 + 1 字节变长标记 + 1 字节 NULL 标记 = 258 字节；age: INT 固定 4 字节（NOT NULL 无 NULL 标记）；总计 258 + 4 = 262 字节。这表示两个列都用上了索引。
F2	3~4；成倍；数十	回表每次都是独立的 B+树搜索，树高 3~~4 层意味着至少 3~~4 次磁盘 IO。如果扫描大量记录后逐一回表，IO 成本成倍放大。随机 IO 的性能远不如顺序 IO，可达数十倍差距。
F3	1；2；否；1	`a=1` 只用了索引的第一段；`a=1 AND b=2` 连续前缀匹配，用了两段；`b=2` 跳过第一列 a，不走索引；`a=1 AND c=3` 虽然 c 在索引中，但因为跳过了 b，c 无法使用前缀匹配，只能用到第一段。

简答题参考答案

S1 参考答案要点：

查询 A：
- 使用索引 idx_uid_status
- 不会回表——user_id 和 status 都在索引中，且 SELECT 的列全部可由该索引提供
- Extra 显示：Using index（真正的覆盖索引）
- 已是最优，无需优化
查询 B：
- 使用索引 idx_uid_status 定位符合条件的记录
- 需要回表——* 需要获取完整行数据，包括 amount、created_at 等不在索引中的列
- Extra 显示：Using where
- 优化：若业务允许，改为只 SELECT 需要的列以减少回表数据量
查询 C：
- 使用索引 idx_uid_status 定位
- 需要回表——id 在主键中、amount 不在索引中
- Extra 显示：Using index condition; Using where（ICP 先用 status IN (...) 在引擎层过滤，但 amount > 100 仍需回表后在 Server 层过滤）
- 优化：如果 amount 经常用于过滤，可考虑建立 (user_id, status, amount) 的联合索引，使 amount 也被索引覆盖

评分标准：三条查询各分析到位得满分；每条需回答清楚索引使用情况、回表与否、Extra 显示和优化方向四个维度。

关联笔记

02.MySQL/index/覆盖索引与回表优化

9.9 KiB Raw Permalink Blame History Unescape Escape