03.3 阶段 2:索引与执行计划
所属:全栈开发面试题库 / 学习路径 / 03 MySQL 与 PostgreSQL
本页目录
一、阶段定位
阶段 1 能写出正确的 SQL,但"能跑"和"快"是两回事。本阶段解决慢查询问题:为什么一条查询要扫 100 万行、怎么用 B+Tree 索引把它压到 10 行、优化器为什么有时候选错索引。这是后端面试出现频率最高的板块之一。
判断自己是否卡在本阶段:
- 看到慢查询只会"加个索引",但加完更慢,或者根本没走——这是不懂 B+Tree 与最左前缀。
EXPLAIN输出一堆字段,只看type=ALL就说全表扫描,看不懂rows、key、Extra各代表什么——这是执行计划解读不过关。- 给查询字段加了索引但查询还是慢,不知道"回表"这一步开销——这是聚簇索引与二级索引结构没搞懂。
- 联合索引
(a,b,c)写了WHERE b=?以为能命中——这是最左前缀原则没吃透。
衔接关系:本阶段在阶段 1 的电商表上灌 100 万行数据做实验。下一阶段 阶段 3 事务、MVCC 与锁 会在锁等待场景下分析为什么索引没走对会放大锁范围(间隙锁)。不理解索引结构,就解释不了"为什么范围查询会加 Gap Lock"。
二、阶段目标与投入
| 能力目标(可验证的行为) | 验证方式 |
|---|---|
| 能画出 B+Tree 的页、非叶节点、叶子链表结构 | 白板画出 3 层 B+Tree 并说明一次查询走几个页 |
| 能解释 InnoDB 聚簇索引与二级索引的区别,说清回表过程 | 对一条二级索引查询画出"二级索引找主键 → 聚簇索引找行"两步 |
能解读 EXPLAIN 每一列(type、key、rows、Extra) |
对一条慢查询逐列解释 |
能判断联合索引 (a,b,c) 下哪些 WHERE 条件能命中 |
给 5 种 WHERE 组合判断命中与否 |
| 能识别索引失效场景(函数包裹、隐式转换、前导通配) | 给出 10 条 SQL 指出哪些失效 |
| 能用覆盖索引消除回表,把查询从 2s 压到 10ms | 实验前后 EXPLAIN 对比 |
建议学时:
- 全职:2 周,约 50 小时。B+Tree 结构 8 小时、聚簇与回表 8 小时、EXPLAIN 解读 10 小时、联合索引与最左前缀 10 小时、索引失效与覆盖索引 8 小时、PG 索引差异 6 小时。
- 在职:4 周,约 55 小时。每周一个主题,必须在 100 万行表上实验。
学时分配:B+Tree 原理 15%、聚簇/二级索引 20%、EXPLAIN/EXPLAIN ANALYZE 25%、联合索引与最左前缀 20%、索引失效 10%、PG 差异(GIN/GIN/BRIN)10%。
三、先修自检
| 先修项 | 合格标准 | 不合格时回到 |
|---|---|---|
| SQL 与 JOIN | 能写多表查询与聚合 | 03.2 阶段 1 |
| 示例库 | orders 表已建,有 user_id、status、amount、created_at | 03.1 阶段 0 |
| 数据类型 | 知道 VARCHAR/BIGINT/DATETIME 区别 | 03.1 阶段 0 |
| 页与磁盘概念 | 知道数据库按固定大小块读盘 | 01.1 操作系统 |
| 命令行客户端 | 能执行 EXPLAIN 并复制输出 | 03.1 阶段 0 |
经验判断:不要只背"最左前缀"四个字,要在 100 万行表上亲手验证——加索引前后 rows 列的数字对比,比看十篇博客都管用。
四、知识地图
| 模块 | 知识点 | 掌握程度 | 对应题库章节 |
|---|---|---|---|
| B+Tree | 页 16KB、非叶存路由、叶子双向链表、层数估算 | 能讲清原理 | 05.2 MySQL |
| 聚簇索引 | InnoDB 按主键组织数据、无主键时选唯一键或隐藏 rowid | 能讲清原理 | 05.2 MySQL |
| 二级索引 | 叶子存主键值、回表、覆盖索引 | 能讲清原理 | 05.2 MySQL |
| 联合索引 | 最左前缀、索引列顺序、索引下推 ICP | 能讲清原理 | 05.2 MySQL |
| 执行计划 | EXPLAIN 列、type 访问类型、rows、Extra | 能讲清原理 | 05.2 MySQL |
| PG 索引 | B-tree、Hash、GIN、GiST、BRIN、EXPLAIN ANALYZE | 会用 | 05.3 PostgreSQL |
| 索引失效 | 函数包裹、隐式转换、前导通配、OR、不满足最左 | 能讲清原理 | 05.2 MySQL |
| 统计信息 | ANALYZE、统计信息过期选错索引 | 会用 | 05.2 MySQL |
进度管理建议:用 orders 表灌 100 万行,每个知识点都做"加索引前 EXPLAIN vs 加索引后 EXPLAIN"对比。全量清单见 03.11 知识点全清单。
五、分模块精讲
5.1 B+Tree 索引结构
为什么需要它:数据库索引用 B+Tree 而不是二叉树、哈希表,是有硬原因的。理解结构才能回答"为什么索引能把扫描行数从 100 万压到几十",以及"为什么范围查询快、前导通配慢"。
核心机制:
- InnoDB 页(Page)大小 16KB,是读写的最小单位。
- B+Tree 非叶节点只存键值 + 子节点指针(不存整行),所以一个 16KB 页能放几百个键,3 层 B+Tree 就能索引千万行。
- 叶子节点存主键 + 二级索引列(或完整行),且叶子之间用双向链表串联,范围查询沿链表顺序扫描。
- 树高估算:1000 万行、每行主键 8 字节、非叶节点每页约 1000 个指针 → 高度约 3–4,即一次点查最多 3–4 次 IO。
- 为什么不用二叉树:树太高,IO 次数多;为什么不用哈希:哈希不支持范围与排序。
最小可运行示例:
-- 灌 100 万行测试数据(MySQL)
CREATE TABLE orders_1m (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
amount DECIMAL(12,2) NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATETIME NOT NULL
);
-- 用存储过程灌数据
DELIMITER $$
CREATE PROCEDURE fill_orders(IN n INT)
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < n DO
INSERT INTO orders_1m VALUES (i, i MOD 10000, i MOD 500, ROUND(RAND()*100,2),
ELT(1 + FLOOR(RAND()*4), 'PAID','PENDING','SHIPPED','REFUNDED'),
DATE_ADD(NOW(), INTERVAL -i MINUTE));
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL fill_orders(1000000);
ANALYZE TABLE orders_1m;
-- PostgreSQL 等价灌数
INSERT INTO orders_1m
SELECT i, i % 10000, i % 500, round((random()*100)::numeric,2),
(ARRAY['PAID','PENDING','SHIPPED','REFUNDED'])[1 + floor(random()*4)::int],
now() - (i || ' minutes')::interval
FROM generate_series(1, 1000000) i;
ANALYZE orders_1m;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 索引占的空间比数据还大 | 二级索引存了主键,联合索引列多 | 控制联合索引列数,删冗余索引 |
| 加索引后写入变慢 | 每次写要同步维护 B+Tree | 控制索引数量,单表索引不超过 5–6 个 |
| 树高 4 层但点查还是慢 | 缓冲池未命中,每次都磁盘 IO | 调大 innodb_buffer_pool_size |
面试考法:"为什么数据库索引用 B+Tree 不用二叉树/哈希"。要点:B+Tree 矮胖(3–4 层索引千万行)、叶子链表支持范围、非叶不存行所以单页键多;二叉树树高、哈希不支持范围排序。
5.2 聚簇索引与回表
为什么需要它:InnoDB 的表本身就是一棵按主键组织的 B+Tree,叫聚簇索引。二级索引的叶子节点存的是主键值而不是行位置,所以通过二级索引查到主键后还要再回聚簇索引查一次完整行——这就是"回表"。
核心机制:
- 聚簇索引(Clustered Index):叶子节点存完整行,一张表只有一个。
- 二级索引(Secondary Index):叶子节点存"索引列 + 主键值"。
- 通过二级索引查询:先在二级 B+Tree 找到主键值,再用主键回聚簇 B+Tree 取行,两步。
- 如果查询的列都在二级索引里(包含索引列),就不用回表,这叫覆盖索引(Covering Index)。
- 没有主键时:InnoDB 选第一个非空唯一索引,都没有则生成隐藏 6 字节 rowid。
最小可运行示例:
-- 表:id 主键,user_id 建二级索引
CREATE TABLE orders_1m (...) ;
CREATE INDEX idx_user ON orders_1m(user_id);
-- 回表查询:SELECT * 需要回表
EXPLAIN SELECT * FROM orders_1m WHERE user_id = 123;
-- type=ref, key=idx_user, Extra 可能是 "Using where"(没有 Using index)
-- 流程:idx_user 找到 user_id=123 的主键 → 回聚簇索引取整行
-- 覆盖索引:只查 user_id 和 id,不回表
EXPLAIN SELECT id, user_id FROM orders_1m WHERE user_id = 123;
-- Extra 出现 "Using index",说明覆盖索引,不回表
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 二级索引查询仍慢 | 回表次数多(扫描了 1 万行二级索引) | 用覆盖索引,或优化 WHERE 缩小范围 |
| Extra 里 "Using index condition" | ICP 生效,不是覆盖索引 | 区分:Using index = 覆盖;Using index condition = ICP |
| 没主键的表插入慢 | 用隐藏 rowid,所有插入追加到末尾但无业务意义 | 建显式主键 |
面试考法:"什么是回表""什么是覆盖索引""InnoDB 没有主键会怎样"。要点答到两步查找与 Using index 的含义。
5.3 联合索引与最左前缀
为什么需要它:实际查询常带多个条件,WHERE user_id=? AND status=? AND amount>?。把多个列做成一个联合索引,比建三个单列索引更高效。但联合索引列顺序错了等于白建。
核心机制:
- 联合索引
(a, b, c)的 B+Tree 先按 a 排序,a 相同再按 b,b 相同再按 c。 - 最左前缀:查询条件必须从最左列开始连续命中。
a=?命中;a=? AND b=?命中;a=? AND b=? AND c=?命中;b=?不命中;a=? AND c=?只命中 a。 - 范围查询截断:
a=? AND b>? AND c=?只用到 a、b,c 用不上(b 是范围,后面 c 无法继续用索引有序性)。 - 索引下推(ICP, Index Condition Pushdown,MySQL 5.6+):
a=? AND b=? AND c LIKE?,即使 c 不能用索引定位,存储引擎层会把 c 条件在索引上过滤,减少回表次数。
最小可运行示例:
CREATE INDEX idx_user_status_amount ON orders_1m(user_id, status, amount);
-- 命中:全列
EXPLAIN SELECT * FROM orders_1m WHERE user_id=1 AND status='PAID' AND amount>100;
-- key_len 最大,type=ref
-- 命中前两列
EXPLAIN SELECT * FROM orders_1m WHERE user_id=1 AND status='PAID';
-- 只命中 user_id
EXPLAIN SELECT * FROM orders_1m WHERE user_id=1 AND amount>100;
-- key_len 只算 user_id;status 缺失,amount 是范围但中间断了
-- 不命中(跳过最左列)
EXPLAIN SELECT * FROM orders_1m WHERE status='PAID';
-- type=ALL 全表扫描
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 建了联合索引但查询没走 | WHERE 跳过了最左列 | 调整索引列顺序,或把高频列放最左 |
a=? AND b=? AND c>? 但 c 没用上 |
b 是等值、c 是范围,c 本就用不上 | 正常,别再调整;或把 c 换等值列 |
| OR 两侧条件 | a=? OR d=? d 无索引 |
改 UNION ALL,或给 d 建索引 |
| 索引列顺序与 WHERE 不一致 | 顺序不影响命中,B+Tree 按索引列序排 | 不必把 WHERE 顺序写得和索引一样 |
面试考法:"联合索引 (a,b,c),WHERE b=? AND c=? 能走吗""WHERE a=? AND b>? AND c=? 用到几列"。要点:最左前缀、范围截断。
5.4 EXPLAIN 逐列解读(MySQL)
为什么需要它:拿到一条慢查询,第一步是 EXPLAIN,不是"加个索引试试"。看懂 EXPLAIN 才能判断是全表扫描、走了哪个索引、预估扫多少行、有没有 Using filesort/Using temporary。
核心机制:
id:查询序号,子查询/UNION 时区分。select_type:SIMPLE(简单查询)、PRIMARY(外层)、DERIVED(派生表)、SUBQUERY。table:访问的表。type(重要):从好到差依次system > const > eq_ref > ref > range > index > ALL。const主键/唯一索引等值;ref非唯一索引等值;range范围;index扫整个索引树;ALL全表扫描。possible_keys:可能用到的索引。key:实际选中的索引。rows:预估扫描行数(不是实际行数)。filtered:按条件过滤后剩余百分比。Extra:Using index(覆盖索引)、Using where(服务层过滤)、Using filesort(额外排序)、Using temporary(用临时表)、Using index condition(ICP)。
最小可运行示例:
EXPLAIN SELECT id, user_id FROM orders_1m WHERE user_id=123 ORDER BY amount;
-- 典型输出(MySQL 8):
-- id=1, select_type=SIMPLE, table=orders_1m
-- type=ref, possible_keys=idx_user_status_amount, key=idx_user_status_amount
-- rows=100, filtered=100.0, Extra=Using where; Using filesort
-- 解读:走了 idx_user 的 user_id 前缀,但 ORDER BY amount 无法用索引有序,产生 filesort
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
type=ALL |
无索引或优化器认为索引更慢 | 加索引;或统计信息过期先 ANALYZE |
Using filesort |
ORDER BY 列不在索引里 | 建 (user_id, amount) 联合索引 |
Using temporary |
GROUP BY 无索引或 DISTINCT | 给分组列建索引 |
rows 预估与实际差 100 倍 |
统计信息过期 | ANALYZE TABLE orders_1m; |
key=NULL |
优化器没选任何索引 | 检查 WHERE 条件,或用 FORCE INDEX |
面试考法:"EXPLAIN 里 type 有哪些级别""Using filesort 一定不好吗""rows 是实际行数吗"。要点:type 层级、filesort 是额外排序不是磁盘文件、rows 是预估值。
5.5 EXPLAIN ANALYZE(PostgreSQL)与 PG 索引类型
为什么需要它:PG 的 EXPLAIN ANALYZE 会真实执行查询并输出实际耗时,比 MySQL 的预估 rows 更可靠。PG 还提供 B-tree 之外的多种索引类型,适合不同场景。
核心机制:
EXPLAIN:只输出计划,不执行。EXPLAIN ANALYZE:实际执行,输出实际行数、实际耗时、循环次数。注意会真的执行写操作(用EXPLAIN ANALYZE跑 UPDATE 会改数据)。BUFFERS:输出缓冲命中情况,判断是否走缓存。- PG 索引类型:B-tree(默认,等值/范围/排序)、Hash(只等值)、GIN(倒排,适合 JSONB、数组、全文)、GiST(多维、范围)、BRIN(块范围索引,适合超大表时序数据,体积小)。
最小可运行示例:
-- PG:实际执行并看耗时
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id FROM orders_1m WHERE user_id = 123;
-- 输出:
-- Index Scan using idx_user on orders_1m (cost=0.29..8.31 rows=1 width=16)
-- (actual time=0.05..0.08 rows=2 loops=1)
-- Buffers: shared hit=4
-- Planning Time: 0.10 ms
-- Execution Time: 0.12 ms
-- GIN 索引示例:JSONB 查询
CREATE TABLE doc (id INT PRIMARY KEY, data JSONB);
CREATE INDEX idx_data ON doc USING GIN (data);
SELECT * FROM doc WHERE data @> '{"city":"北京"}';
-- BRIN:超大时序表
CREATE INDEX idx_created ON orders_1m USING BRIN (created_at);
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| EXPLAIN ANALYZE 跑了 UPDATE 把数据改了 | ANALYZE 真执行 | 用 BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; |
| JSONB 查询慢 | 没建 GIN | USING GIN (data) |
| 大表 BRIN 索引不生效 | 查询条件与物理顺序无关 | BRIN 只对物理有序列有效(如时序) |
| 顺序扫描 Seq Scan 但表不大 | PG 认为顺序扫比索引快(小表) | 小表正常,不必强上索引 |
面试考法:"EXPLAIN ANALYZE 与 EXPLAIN 区别""GIN/B-tree/BRIN 各适合什么"。要点:ANALYZE 真执行给实际耗时;GIN 适合文档/数组;BRIN 适合超大时序表。
5.6 索引失效场景
为什么需要它:线上"加了索引但没走"是最高频的慢查询原因。本模块把常见失效场景列全,遇到时能一次定位。
核心机制:
- 对索引列做函数运算:
WHERE DATE(created_at)='2026-01-01'不走索引(要改成范围created_at >= '2026-01-01' AND created_at < '2026-01-02')。 - 隐式类型转换:
user_id是 BIGINT,WHERE 写user_id='123'(字符串)会隐式转,索引失效。 - 前导通配:
name LIKE '%abc'不走 B+Tree(最左前缀无法定位);name LIKE 'abc%'可以。 - OR 条件两侧有一列无索引。
- 不满足最左前缀(见 5.3)。
- 优化器认为全表扫描更划算(表太小、或索引区分度太低)。
!=、NOT IN、IS NOT NULL通常难以用索引定位。
最小可运行示例:
-- 失效:函数包裹
EXPLAIN SELECT * FROM orders_1m WHERE DATE(created_at)='2026-01-01'; -- ALL
-- 改写:范围
EXPLAIN SELECT * FROM orders_1m
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'; -- range
-- 失效:隐式转换(user_id 是 BIGINT)
EXPLAIN SELECT * FROM orders_1m WHERE user_id='123'; -- 可能 ALL
-- 改:去掉引号
EXPLAIN SELECT * FROM orders_1m WHERE user_id=123;
-- 失效:前导通配
EXPLAIN SELECT * FROM orders_1m WHERE status LIKE '%PAID%'; -- ALL
-- 有效:后导通配
EXPLAIN SELECT * FROM orders_1m WHERE status LIKE 'PAID%'; -- ref/range
-- OR 一侧无索引
EXPLAIN SELECT * FROM orders_1m WHERE user_id=1 OR product_id=2; -- product_id 无索引
-- 改 UNION ALL
SELECT * FROM orders_1m WHERE user_id=1
UNION ALL
SELECT * FROM orders_1m WHERE product_id=2;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
WHERE DATE(create_time)=? 全表扫 |
函数包裹 | 改为范围查询 |
| 字符串列存数字但查询传数字 | 隐式转换 | 统一类型,或建函数索引 |
LIKE '%xxx' 全表扫 |
前导通配 | 用全文索引/倒排,或改业务 |
| 低区分度列(如 status 4 个值)建索引 | 优化器认为回表成本高 | 联合索引把 status 放后面;或不建 |
FORCE INDEX 后更慢 |
强制了错误索引 | 删掉 hint,先 ANALYZE |
面试考法:"索引失效有哪些场景""怎么排查"。要点:函数包裹、隐式转换、前导通配、OR、最左前缀、低区分度。
5.7 统计信息与优化器成本模型
为什么需要它:明明有索引,优化器却选了全表扫描,十有八九是统计信息过期。优化器靠统计信息估算行数与成本,估算错了计划就错。
核心机制:
- MySQL:
ANALYZE TABLE t刷新索引统计信息( Cardinality 值)。InnoDB 用随机采样估算,批量灌数据后必须刷新。 - PG:
ANALYZE t收集列统计(n_distinct、相关性、直方图)。自动 autovacuum 在批量写后触发。 - 优化器成本:
rows × IO 成本 + 比较成本,全表扫描如果 rows 预估很小(如 1000 行表),可能比走索引回表还快。 - 固定 hint:
FORCE INDEX(idx)、IGNORE INDEX(idx),但能不用就不用。
最小可运行示例:
-- 批量灌数据后
CALL fill_orders(1000000);
-- 不刷新直接 EXPLAIN,rows 可能预估为 10
EXPLAIN SELECT * FROM orders_1m WHERE user_id=123;
-- 刷新后
ANALYZE TABLE orders_1m;
EXPLAIN SELECT * FROM orders_1m WHERE user_id=123; -- rows 预估更准
-- PG
ANALYZE orders_1m;
常见坑:
| 现象 | 原因 | 修法 |
|---|---|---|
| 大批量导入后查询走错索引 | 统计信息未更新 | 立即 ANALYZE |
| 晚上定时任务后慢查询集中 | 批量写未触发自动分析 | 任务末尾手动 ANALYZE |
SHOW INDEX FROM t 的 Cardinality 是小数 |
InnoDB 采样估算 | 正常,不影响;偏差大时 ANALYZE |
面试考法:"优化器怎么选索引""为什么有时候有索引不走"。要点:基于成本估算,统计信息过期或回表成本高时会选全表扫描。
六、分层练习
6.1 复现型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 灌 100 万行 | 按 5.1 脚本建 orders_1m 并灌数据 | 行数 = 100 万,ANALYZE 后 | 1.5 小时 |
| 全表扫描对比 | 对 user_id=123 先无索引 EXPLAIN 再加索引 | type 从 ALL 变 ref | 1 小时 |
| 回表演示 | SELECT * vs SELECT id,user_id 的 Extra 对比 | 看到 Using index 出现 | 1 小时 |
| 最左前缀验证 | 建 (user_id,status,amount) 索引,跑 5 种 WHERE 组合 | key_len 变化能解释 | 2 小时 |
| 索引失效复现 | 复现函数包裹、隐式转换、前导通配三种失效 | 每种都能用 EXPLAIN 看到 type=ALL | 2 小时 |
6.2 改造型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 覆盖索引优化 | 把一条回表查询改成覆盖索引 | Extra 出现 Using index | 1.5 小时 |
| 修函数包裹 | 把 WHERE DATE(created_at)=? 改成范围 |
type 从 ALL 变 range | 1 小时 |
| 修 OR 查询 | 把 a=? OR b=? 改 UNION ALL |
两侧都走索引 | 1 小时 |
| filesort 消除 | 给 ORDER BY 建联合索引 | Extra 不再出现 Using filesort | 1.5 小时 |
6.3 设计型
| 练习 | 要求 | 验收标准 | 预计耗时 |
|---|---|---|---|
| 索引方案设计 | 对电商订单表列出 5 条高频查询,设计最少索引满足 | 索引数 ≤ 4,每条查询都能走 | 3 小时 |
| PG 索引选型 | 对 JSONB 字段、时序 created_at 选 GIN/BRIN | 能说出选型理由 | 2 小时 |
| 慢查询复盘 | 找一条真实慢 SQL,写出 EXPLAIN 解读与优化方案 | 优化前后耗时对比有数据 | 3 小时 |
七、排障表
| 现象 | 可能原因 | 排查步骤 | 解决办法 |
|---|---|---|---|
type=ALL 全表扫 |
无索引、失效、或优化器判断索引更慢 | EXPLAIN 看 possible_keys、key、rows | 加/改索引;ANALYZE |
加索引后仍 Using filesort |
ORDER BY 列不在索引中 | 看 Extra 与排序列 | 建 (过滤列, 排序列) 联合索引 |
Using temporary |
GROUP BY/DISTINCT 无索引 | 看 SQL 分组列 | 给分组列建索引 |
| 索引选择与预期不符 | 统计信息过期 | SHOW INDEX 看 Cardinality |
ANALYZE TABLE |
| 回表次数多导致慢 | 二级索引扫描行数大 | 看 rows 与实际耗时 | 覆盖索引,或缩小范围 |
| PG 用 Seq Scan 而非索引 | 表小或成本估算 | EXPLAIN ANALYZE 看实际 | 小表正常;大表则关 enable_seqscan 测试 |
前导通配 LIKE '%xx' 慢 |
B+Tree 不支持 | 改全文索引/ES | 换检索方案 |
| 隐式转换失效 | 列类型与参数不匹配 | 看 EXPLAIN 的 key |
统一类型,去掉引号 |
八、出口条件
全部勾选后再进入 阶段 3 事务、MVCC 与锁。
- [ ] 能画出 B+Tree 结构,说出 3 层 B+Tree 索引千万行的原因
- [ ] 能解释聚簇索引与二级索引的区别,说清回表与覆盖索引
- [ ] 能对一条 SQL 的 EXPLAIN 逐列解读 type、key、rows、Extra
- [ ] 能判断联合索引 (a,b,c) 下 5 种 WHERE 组合的命中情况
- [ ] 能列出至少 5 种索引失效场景并给出改写
- [ ] 能用 EXPLAIN ANALYZE 解读 PG 计划,区分 GIN/B-tree/BRIN
- [ ] 能在 100 万行表上把一条全表扫描压到 range/ref
自测题:
- 为什么 InnoDB 索引用 B+Tree 而不是二叉树或哈希?
- 要点:B+Tree 矮胖(3-4 层索引千万行)、IO 次数少、叶子链表支持范围;哈希不支持范围。
- 什么是回表?怎么消除?
- 要点:二级索引查到主键后再回聚簇索引取行;用覆盖索引(查询列都在二级索引里)消除,Extra 出现 Using index。
- 联合索引 (a,b,c),
WHERE a=? AND c=?用到几列?- 要点:只用到 a,b 缺失导致 c 无法继续利用索引有序性。
WHERE DATE(created_at)='2026-01-01'为什么不走索引?怎么改?- 要点:函数包裹使索引列无法定位;改范围
created_at >= ... AND < ...。
- 要点:函数包裹使索引列无法定位;改范围
- EXPLAIN 里
Using filesort是写磁盘文件吗?怎么消除?- 要点:是额外排序操作,不一定写磁盘;给 ORDER BY 列建联合索引让结果天然有序。
- PG 的 GIN 与 BRIN 各适合什么场景?
- 要点:GIN 倒排,适合 JSONB、数组、全文检索;BRIN 块级索引,适合超大物理有序的时序表。
- 明明有索引,优化器却选全表扫描,可能原因?
- 要点:统计信息过期(ANALYZE)、表太小走索引回表更贵、索引区分度低、WHERE 列有失效场景。