05.3.2.1 索引类型与索引设计
所属:全栈开发面试题库 / 05 数据库与存储 / 05.3 PostgreSQL / 05.3.2 索引优化与高可用运维
覆盖范围
- B-tree / Hash / GIN / GiST / SP-GiST / BRIN 选型
- 部分索引、表达式索引、INCLUDE 覆盖索引
- 唯一/多列索引与列顺序、索引维护成本
- JSONB 路径索引、全文检索索引
- 无用索引发现、与 MySQL 索引体系对照
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.3.2.1-001 | PG 索引类型概览 | 进阶 | ★★★★★ |
| 05.3.2.1-002 | 部分/表达式/覆盖索引 | 高级 | ★★★★★ |
| 05.3.2.1-006 | GIN 适用场景 | 进阶 | ★★★★☆ |
| 05.3.2.1-010 | BRIN 大表顺序扫描 | 进阶 | ★★★★☆ |
| 05.3.2.1-013 | 无用索引发现 | 进阶 | ★★★★☆ |
选型主线
等值/范围/排序 → B-tree
纯等值 → Hash(并发哈希表)
数组/JSONB/全文多值 → GIN
几何/范围/邻近 → GiST / SP-GiST
超大顺序表(时间/ID)→ BRIN
覆盖回表 → INCLUDE;过滤子集 → 部分索引;函数查询 → 表达式索引
05.3.2.1-001 PostgreSQL 有哪些索引类型?
频率 ★★★★★ | 难度 进阶
一句话结论
PG 内置 B-tree(等值/范围/排序)、Hash(等值)、GIN(数组/JSONB/全文多值)、GiST(几何/范围/模糊)、SP-GiST(非平衡空间结构)、BRIN(超大表物理顺序相关列);按查询条件与数据形态选索引类型。
概念解释
- B-tree:默认通用索引,支持
=、<、>、BETWEEN、IN、排序。 - Hash:仅等值,并发哈希表实现。
- GIN:倒排多值索引,适合包含/存在查询。
- GiST/SP-GiST:空间与邻近检索结构。
- BRIN:页级摘要索引,体积极小。
原理流程
- 分析查询操作符与列数据类型。
- 匹配支持该操作符的索引访问方法。
- 评估索引体积与写入维护成本。
代码 / 命令
CREATE INDEX idx_orders_time ON orders USING btree (create_time);
CREATE INDEX idx_doc_gin ON docs USING gin (data jsonb_path_ops);
CREATE INDEX idx_geo ON places USING gist (location);
CREATE INDEX idx_big ON events USING brin (created_at);
常见误区
- 误区:B-tree 能索引 JSONB 内部键值。正解:普通 B-tree 无法索引文档内部结构,需 GIN 或表达式索引。
- 误区:BRIN 适合精确等值。正解:BRIN 适合范围扫描,等值选择性差。
追问
- 问:Hash 索引相比 B-tree 优势?答:纯等值场景体积/结构更轻,范围与排序不支持。
- 问:哪种索引写入维护最贵?答:GIN 倒排索引写放大明显,见 05.3.2.1-006。
05.3.2.1-002 部分索引、表达式索引、覆盖索引怎么用?
频率 ★★★★★ | 难度 高级
一句话结论
部分索引只索引满足谓词的子集,体积小;表达式索引对函数/表达式结果建索引,需查询表达式与索引一致;INCLUDE 把附加列纳入索引实现覆盖扫描,不参与排序。
概念解释
- 部分索引:
WHERE谓词限定索引范围,查询需蕴含该谓词才被使用。 - 表达式索引:索引列上的函数/表达式结果。
- INCLUDE 覆盖:附加列存索引叶子,免回表。
原理流程
- 识别常用过滤子集或函数查询。
- 建部分/表达式索引匹配查询写法。
- 高频回表列纳入 INCLUDE 实现 index-only scan。
代码 / 命令
CREATE INDEX idx_active ON orders (create_time) WHERE status='ACTIVE';
CREATE INDEX idx_lower_email ON users (lower(email));
CREATE INDEX idx_cover ON orders (tenant_id, status) INCLUDE (amount);
常见误区
- 误区:部分索引任何查询都能用。正解:优化器需证明查询条件蕴含索引谓词,否则不用。
- 误区:表达式索引列随便写函数。正解:查询表达式必须与索引表达式完全一致,且函数具不变性。
追问
- 问:INCLUDE 列参与排序吗?答:不参与索引键顺序,只用于覆盖取值。
- 问:index-only scan 依赖什么?答:可见性映射,见 05.3.2.2-004。
05.3.2.1-003 B-tree 结构与适用场景?
频率 ★★★☆☆ | 难度 进阶
一句话结论
B-tree 按键有序组织索引项,支持等值、范围扫描与有序输出;多列 B-tree 遵守列顺序前缀,适合过滤 + 排序组合查询;是关系型最常用的索引类型。
概念解释
- 有序树:键值有序,范围扫描沿叶子顺序。
- 多列前缀:查询需匹配最左列顺序。
- 有序输出:索引顺序可免 Sort 节点。
原理流程
- 等值查询定位键位置。
- 范围查询沿叶子链扫描连续区间。
- 有序结果直接按索引顺序返回。
代码 / 命令
CREATE INDEX idx_ts ON orders (tenant_id, status, create_time);
常见误区
- 误区:多列索引任意列都能单独用。正解:需满足最左前缀,跳过前列后后续列无法有效定位。
- 误区:B-tree 索引项存整行。正解:存键值 + ctid,回表取行。
追问
- 问:列顺序怎么排?答:等值匹配列在前,排序列在后,结合区分度,见 05.3.2.1-008。
- 问:降序排序需要降序索引吗?答:B-tree 双向扫描,特定多列方向组合才需对应方向索引。
05.3.2.1-004 Hash 索引与并发哈希表?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
Hash 索引只支持等值匹配,基于哈希表定位键;现代 PG 为并发哈希表,解决早期 Hash 索引并发与崩溃问题;范围、排序、前缀匹配不能用 Hash。
概念解释
- 等值定位:键哈希后定位桶。
- 不支持顺序:无键有序性。
- 并发实现:并发哈希表支持多会话访问。
原理流程
- 等值条件计算键哈希。
- 定位哈希桶查找匹配键。
- 不支持范围遍历与排序。
代码 / 命令
CREATE INDEX idx_users_email_hash ON users USING hash (email);
常见误区
- 误区:Hash 索引比 B-tree 万能。正解:仅等值可用,范围/排序场景仍需 B-tree。
- 误区:Hash 索引节省回表。正解:等值命中后仍按 ctid 回表取行。
追问
- 问:什么场景选 Hash?答:纯等值高并发查询、无范围排序需求。
- 问:Hash 冲突怎么处理?答:桶内链/结构处理,内部实现以手册为准。
05.3.2.1-005 GIN 索引原理与场景?
频率 ★★★★☆ | 难度 高级
一句话结论
GIN 是倒排索引,把数组元素、JSONB 键值、词项映射到包含它们的行;适合包含查询 @>、存在判断 ?、全文 @@;代价是写入时维护倒排项,写放大明显。
概念解释
- 倒排结构:键项 → 行集合。
- 多值列:数组、JSONB、tsvector。
- 写放大:每行多值需维护多个倒排项。
原理流程
- 插入行时把各元素加入倒排项。
- 查询按元素定位行集合求交/并。
- 再回表取元组。
代码 / 命令
CREATE INDEX idx_tags ON articles USING gin (tags);
SELECT * FROM articles WHERE tags @> ARRAY['pg','db'];
常见误区
- 误区:GIN 适合高频写入多值列。正解:倒排维护成本高,写密集场景权衡。
- 误区:GIN 支持范围排序。正解:它面向包含/存在匹配,不提供有序扫描。
追问
- 问:jsonb_path_ops 与默认 GIN 区别?答:路径操作符类索引更小更快,支持操作符子集,见 05.3.2.1-012。
- 问:GIN 索引膨胀怎么维护?答:保障 vacuum,必要时 REINDEX。
05.3.2.1-006 GiST 与 SP-GiST 适用场景?
频率 ★★☆☆☆ | 难度 高级
一句话结论
GiST 是通用平衡树索引框架,支持几何包含/相交、范围重叠、邻近与模糊匹配;SP-GiST 面向非平衡空间划分(四叉树/前缀树等),适合特定邻近检索;二者都不是通用 B-tree 替代品。
概念解释
- GiST:平衡结构,多种距离/相交操作符。
- SP-GiST:空间划分树,非平衡。
- 领域类型:几何点/范围、网络地址、字符串前缀。
原理流程
- 类型提供距离/相交操作符实现。
- 索引按结构划分键空间。
- 查询按操作符剪枝检索候选。
代码 / 命令
CREATE INDEX idx_geo ON places USING gist (location);
常见误区
- 误区:GiST 能替代 B-tree 做普通等值范围。正解:它面向空间/领域操作符,普通场景 B-tree 更稳。
- 误区:SP-GiST 与 GiST 可互换。正解:结构与适用数据划分不同。
追问
- 问:PostGIS 用什么索引?答:几何对象基于 GiST 索引做空间检索。
- 问:范围类型用什么索引?答:range 类型可用 GiST(或 B-tree 等值),按操作符选。
05.3.2.1-007 BRIN 索引原理与前提?
频率 ★★★★☆ | 难度 进阶
一句话结论
BRIN 按物理页范围记录列最小/最大值摘要,体积极小;前提是列值与物理存储顺序高度相关(如自增 ID、时间递增写入),适合超大表范围扫描,不适合无序数据与精确等值。
概念解释
- 页范围摘要:每段页存列 min/max。
- 顺序相关:物理顺序与列值单调相关才有效。
- 小体积:索引远小于 B-tree。
原理流程
- 按块范围记录摘要。
- 范围条件先排除摘要不匹配的块。
- 匹配块再扫描数据。
代码 / 命令
CREATE INDEX idx_events_time ON events USING brin (created_at);
常见误区
- 误区:BRIN 适合任意大表。正解:列值乱序时摘要覆盖全范围,裁剪失效。
- 误区:BRIN 能加速点查。正解:它只粗粒度裁剪块,点查仍需扫描匹配块。
追问
- 问:BRIN 与 B-tree 怎么选?答:超大顺序写入表要范围扫描选 BRIN;小表/等值/排序选 B-tree。
- 问:BRIN 块范围参数?答:按页数控制摘要粒度,具体参数以手册为准。
05.3.2.1-008 唯一索引、多列索引与列顺序?
频率 ★★★★☆ | 难度 进阶
一句话结论
唯一索引保证键值唯一;多列索引列顺序按等值过滤列在前、排序列在后,并结合区分度;前缀列选择性高利于裁剪,唯一约束自动建唯一索引。
概念解释
- 唯一约束:对应唯一索引,防重复键。
- 多列顺序:最左前缀决定可用范围。
- 区分度:高选择性列优先定位。
原理流程
- 列出过滤等值、范围与排序列。
- 等值列在前,范围/排序列在后。
- 校验索引谓词与查询匹配。
代码 / 命令
CREATE UNIQUE INDEX uk_email ON users (email);
CREATE INDEX idx_q ON orders (tenant_id, status, create_time DESC);
常见误区
- 误区:把高区分度列放最后。正解:前缀列决定能否快速定位,顺序不当索引失效。
- 误区:多列索引覆盖所有组合。正解:仅满足前缀的查询有效,不同组合可能需新索引。
追问
- 问:范围列后面的列能用吗?答:范围条件之后的列无法再用索引定位,只能过滤。
- 问:唯一索引能当普通查询索引吗?答:可以,但同时承担约束用途。
05.3.2.1-009 Index Only Scan 的条件?
频率 ★★★★☆ | 难度 高级
一句话结论
查询所需列都在索引内,且可见性映射标记页全可见,即可只读索引不回表;缺列或 VM 未标记全可见时仍需 Heap Fetch 回表。
概念解释
- 覆盖列:索引键 + INCLUDE 列包含查询输出列。
- VM 可见:页内元组全可见才免回表确认。
- Heap Fetches:回表次数指标,越低越好。
原理流程
- 检查输出列是否都在索引。
- 按 VM 判断页是否全可见。
- 全可见走 index-only,否则回表。
代码 / 命令
EXPLAIN (ANALYZE) SELECT tenant_id, amount FROM orders WHERE tenant_id=1;
-- Index Only Scan; Heap Fetches: 0 理想
常见误区
- 误区:建了 INCLUDE 就一定 index-only。正解:VM 未更新全可见时仍回表,需 vacuum。
- 误区:index-only 不读堆。正解:不可见页仍需回堆确认版本。
追问
- 问:Heap Fetches 高怎么优化?答:保障 vacuum 更新 VM,或该表更新频繁时评估覆盖策略。
- 问:INCLUDED 列能做过滤条件吗?答:可过滤,但不参与索引键顺序定位。
05.3.2.1-010 无用索引如何发现?
频率 ★★★★☆ | 难度 进阶
一句话结论
用 pg_stat_user_indexes 的 idx_scan、idx_tup_read、idx_tup_fetch 找长期未扫描索引;需覆盖完整业务周期,唯一/主键索引即使零扫描也不能删(约束用途);大索引用 DROP INDEX CONCURRENTLY 在线删除。
概念解释
- idx_scan:索引被扫描次数。
- 约束索引:唯一/主键服务完整性,不依赖扫描计数。
- 在线删除:CONCURRENTLY 避免长锁表。
原理流程
- 采集完整周期内索引使用统计。
- 零扫描且非约束的候选索引列出。
- 确认无周期/隐藏查询后并发删除。
代码 / 命令
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;
常见误区
- 误区:idx_scan=0 立刻删。正解:可能是周期任务、统计未覆盖或约束索引。
- 误区:删索引不影响写入。正解:删除冗余索引减轻写放大,但误删会致慢查询。
追问
- 问:统计被重置怎么判断?答:对比统计时间基线,覆盖完整业务周期再下结论。
- 问:冗余索引怎么识别?答:列前缀被另一索引覆盖的重复索引。
05.3.2.1-011 索引维护成本与写入放大?
频率 ★★★☆☆ | 难度 进阶
一句话结论
每个索引在插入/更新/删除时都需维护,索引越多写入越慢、占用空间与 vacuum 负担越大;非 HOT 更新还会产生索引膨胀。应定期清理冗余无用索引,按真实查询建索引。
概念解释
- 写入维护:DML 同步更新所有相关索引。
- 空间占用:索引页与 TOAST 外的独立体积。
- vacuum 负担:失效索引项需回收。
原理流程
- 评估每条 DML 触发的索引维护数量。
- 清理冗余/无用索引。
- 监控索引大小与写放大趋势。
代码 / 命令
SELECT relname, indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes ORDER BY indexrelid;
常见误区
- 误区:多建索引只有读收益无代价。正解:写入放大与空间成本随索引数线性增长。
- 误区:GIN/B-tree 维护成本相同。正解:GIN 倒排写放大更高。
追问
- 问:高频更新列为什么不宜建索引?答:改索引列破坏 HOT 并反复维护索引项。
- 问:怎么权衡索引?答:读频率/收益 vs 写入代价与空间,结合业务峰值。
05.3.2.1-012 JSONB 索引路径怎么设计?
频率 ★★★★☆ | 难度 高级
一句话结论
整体文档包含查询用 GIN(jsonb_path_ops 更小更快,仅支持 @>;默认类支持更多操作符);固定路径高频查询用表达式 B-tree 索引;JSONB 更新重写整文档,强结构字段应建普通列。
概念解释
- GIN 文档索引:索引整个 JSONB 结构。
- 表达式索引:对固定路径提取值建 B-tree。
- 更新代价:JSONB 修改需解析重写整文档。
原理流程
- 区分查询是整体包含还是固定路径取值。
- 包含查询建 GIN,固定路径建表达式 B-tree。
- 评估字段结构化程度决定是否拆列。
代码 / 命令
CREATE INDEX idx_doc_gin ON docs USING gin (data jsonb_path_ops);
CREATE INDEX idx_doc_type ON docs ((data ->> 'type'));
SELECT * FROM docs WHERE data @> '{"type":"order"}';
常见误区
- 误区:JSONB 随便更新不影响性能。正解:更新重写整文档并可能动 TOAST,高频变更字段应结构化。
- 误区:json 类型也能建 GIN。正解:GIN 面向 jsonb 解析后二进制结构,json 文本需先转。
追问
- 问:json 与 jsonb 区别?答:json 存原文保留顺序空格,每次解析;jsonb 二进制去重无序、可索引。
- 问:路径查询用哪个操作符?答:
->>取文本值配表达式索引,@>包含配 GIN。
05.3.2.1-013 全文检索索引怎么建?
频率 ★★★★☆ | 难度 进阶
一句话结论
把文档转 tsvector 并存生成列,对其建 GIN 索引,用 @@ 匹配查询;排序用 ts_rank 配合 LIMIT;中文需分词扩展(能力以版本/扩展为准,待确认);复杂检索外置搜索引擎。
概念解释
- tsvector:词项 + 位置的倒排文本。
- GIN 倒排:支撑
@@包含匹配。 - 分词:按词典切词,中文依赖扩展。
原理流程
- 标题/内容拼接转 tsvector 生成列。
- GIN 索引词项。
- 查询转 tsquery 匹配并排序取 TopN。
代码 / 命令
ALTER TABLE articles ADD COLUMN tsv tsvector
GENERATED ALWAYS AS (to_tsvector('simple', coalesce(title,'')||' '||coalesce(content,''))) STORED;
CREATE INDEX idx_tsv ON articles USING gin (tsv);
SELECT * FROM articles
WHERE tsv @@ plainto_tsquery('simple', 'database index')
ORDER BY ts_rank(tsv, plainto_tsquery('simple','database index')) DESC LIMIT 20;
常见误区
- 误区:
LIKE '%x%'能走索引。正解:前缀通配无法用 B-tree,需全文索引。 - 误区:GIN 全文索引写入无成本。正解:倒排维护写放大,高频更新文档需权衡。
追问
- 问:中文分词方案?答:分词扩展或应用层预处理,具体可用扩展以生态版本为准(待确认)。
- 问:复杂相关检索适合 PG 内做吗?答:多字段权重、高亮、近邻排序复杂时外置 ES 类引擎。
05.3.2.1-014 表达式索引与函数不变性?
频率 ★★☆☆☆ | 难度 高级
一句话结论
表达式索引要求所用函数是不可变(immutable)的,同一输入恒得同一结果;查询表达式必须与索引表达式写法一致才能命中;不稳定函数(时间、随机、会话相关)不能建表达式索引。
概念解释
- immutable:函数结果仅由输入决定,不受环境影响。
- 表达式一致:索引表达式与 WHERE 表达式逐字匹配。
- 稳定/可变函数:不能用于索引键。
原理流程
- 确认表达式函数具不变性。
- 建表达式索引。
- 查询使用相同表达式写法命中。
代码 / 命令
CREATE INDEX idx_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'ann@x.com';
常见误区
- 误区:
now()类函数能建索引。正解:结果随时间变化,不满足不变性。 - 误区:表达式大小写/括号不同也能命中。正解:表达式必须一致,否则优化器不识别。
追问
- 问:类型转换要注意什么?答:转换表达式需与索引一致,避免隐式转换失效。
- 问:表达式索引占空间吗?答:存表达式结果,体积与普通索引类似评估。
05.3.2.1-015 部分索引过滤条件设计?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
部分索引谓词应匹配高频查询子集(如活跃记录、未删除),让索引只索引小部分行;查询 WHERE 必须能蕴含索引谓词,否则优化器不使用;多套子集可分别建部分索引。
概念解释
- 子集索引:谓词过滤后建索引。
- 蕴含关系:查询条件覆盖索引谓词才可用。
- 体积收益:只索引活跃数据,体积小、维护轻。
原理流程
- 识别高频访问的数据子集。
- 设计部分索引谓词。
- 核对业务查询都带该谓词。
代码 / 命令
CREATE INDEX idx_active ON orders (create_time) WHERE status='ACTIVE';
-- 查询需带 status='ACTIVE' 才命中
常见误区
- 误区:部分索引谓词随意写。正解:查询不带对应条件时索引失效,白建。
- 误区:部分索引能加速全量历史查询。正解:它只覆盖子集,历史数据需其他索引。
追问
- 问:部分索引与普通索引怎么选?答:访问集中在明确子集用部分索引;全量查询用普通索引。
- 问:谓词含多列怎么写?答:WHERE 组合条件,查询需同时蕴含全部谓词。
05.3.2.1-016 索引与约束的关系?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
主键与唯一约束自动创建对应唯一索引;外键列建议建索引加速引用检查与关联;约束索引承担完整性职责,即使统计零扫描也不可随意删除。
概念解释
- 主键/唯一:约束 = 唯一索引。
- 外键索引:子表外键列索引加速父表变更检查。
- 职责区分:约束索引不能按使用计数删除。
原理流程
- 建约束时自动生成索引。
- 外键关联列评估补索引。
- 索引治理保留约束索引,清理纯冗余普通索引。
代码 / 命令
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
CREATE INDEX idx_orders_uid ON orders (user_id);
常见误区
- 误区:外键不需要索引。正解:父表删除/更新时无索引会扫描子表检查引用。
- 误区:唯一索引没人查就删。正解:它在保证约束,删除破坏完整性。
追问
- 问:主键索引能删吗?答:不能,主键约束依赖其唯一索引。
- 问:唯一约束与唯一索引区别?答:约束是逻辑定义,索引是实现,多数情况一并创建。
05.3.2.1-017 与 MySQL 索引体系对照?
频率 ★★★☆☆ | 难度 进阶
一句话结论
两边都以 B-tree 为核心;MySQL 聚簇组织表、二级索引存主键;PG 堆表索引存 ctid。PG 索引类型更丰富(GIN/GiST/BRIN/表达式/部分索引),MySQL 索引类型与引擎绑定,具体能力以各自版本为准。
概念解释
- B-tree 共性:等值/范围/有序。
- 存储差异:聚簇叶子行 vs 索引 ctid 回表。
- 类型差异:PG 多值/空间/摘要索引生态更全。
原理流程
- 对比查询操作符选择索引访问方法。
- 按表组织方式理解回表路径。
- 跨库迁移核对索引类型支持度。
代码 / 命令
对照:InnoDB 聚簇 B-tree(叶子行/二级存主键) ↔ PG 堆表 B-tree(ctid 回表)
常见误区
- 误区:两边索引语法与类型一一对应。正解:访问方法、存储结构与回表路径不同。
- 误区:MySQL 也有 GIN/BRIN。正解:对应能力随引擎/扩展不同,迁移需核对。
追问
- 问:MySQL 覆盖索引怎么做?答:索引列覆盖查询免回表,见 05.2.2.1。
- 问:哪边表达式索引更成熟?答:PG 原生支持函数/表达式索引,MySQL 生成列 + 索引路径不同。
05.3.2.1-018 索引设计评审清单?
频率 ★★★☆☆ | 难度 进阶
一句话结论
评审核对:查询操作符匹配索引类型、多列前缀与列序、覆盖回表列、部分/表达式索引谓词一致性、唯一约束保留、写入维护成本与冗余重复;新索引上线前出计划验证命中。
概念解释
- 命中验证:EXPLAIN 确认实际使用索引。
- 冗余检查:前缀被覆盖的重复索引。
- 写入权衡:读收益 vs 写放大。
原理流程
- 汇总高频查询条件、排序与输出列。
- 设计索引类型与列序。
- 出计划验证命中,评估写入代价后上线。
代码 / 命令
EXPLAIN (ANALYZE, BUFFERS) <SQL>;
常见误区
- 误区:评审只看 WHERE 列。正解:排序、关联与输出覆盖列都影响索引设计。
- 误区:上线后不回看索引使用。正解:周期回收未使用与冗余索引。
追问
- 问:一个查询建几个索引合适?答:按过滤/排序/关联组合评估,避免为单条 SQL 堆冗余索引。
- 问:大表建索引注意什么?答:在线构建方式、写阻塞与空间余量,参考 05.3.2.5 运维章节。