05.2.2.1 索引设计与覆盖索引实战
所属:全栈开发面试题库 / 05 数据库与存储 / 05.2 MySQL / 05.2.2 SQL 优化与高可用运维
覆盖范围
- 联合索引与最左前缀、索引列顺序
- 覆盖索引、回表与索引下推(ICP)
- 前缀索引、函数索引、不可见索引
- 冗余/重复索引识别、隐式转换与失效场景
- 多值/JSON 索引、索引设计评审清单
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.2.2.1-001 | 覆盖索引实战 | 进阶 | ★★★★★ |
| 05.2.2.1-002 | 隐式转换导致索引失效 | 进阶 | ★★★★★ |
| 05.2.2.1-003 | OR 与 UNION 改写 | 进阶 | ★★★☆☆ |
| 05.2.2.1-006 | 索引下推 ICP | 进阶 | ★★★★☆ |
| 05.2.2.1-010 | 函数索引(8.0) | 进阶 | ★★★☆☆ |
索引设计主线
查询条件 / 排序列 / 连接列
→ 评估选择性 → 建联合索引(高选择性在前,匹配最左前缀)
→ 覆盖索引避免回表 → 排查失效:函数/隐式转换/前导模糊/最左断裂
05.2.2.1-001 覆盖索引在实战中怎么用?
频率 ★★★★★ | 难度 进阶
一句话结论
覆盖索引指查询所需列都包含在索引中,引擎不必回表取整行,二级索引叶子直接返回结果;计划 Extra 出现 Using index 即走覆盖索引,是减少回表 IO 的主要手段。
概念解释
- 回表:二级索引找到主键后再查聚簇索引取整行。
- 覆盖索引:查询列已在索引键/主键中,无需回表。
- Extra 标识:
Using index表示覆盖索引扫描。
原理流程
- 按查询列设计联合索引,覆盖 WHERE、JOIN、ORDER BY 与 SELECT 列。
- 计划走该二级索引,叶子直接提供所需列。
- 无需访问聚簇索引页,减少随机回表 IO。
代码 / 命令
-- 索引覆盖 user_id 与 status,查询不回表
CREATE INDEX idx_uid_status ON orders(user_id, status);
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 1001;
-- Extra: Using index
常见误区
- 误区:索引包含 WHERE 列就算覆盖索引。正解:SELECT 输出列也需在索引中,否则仍要回表。
- 误区:覆盖索引越多越好。正解:索引增加写入维护成本与存储,只覆盖高频查询。
追问
- 问:二级索引叶子携带什么?答:索引键值 + 主键值,可利用主键避免回表取主键列。
- 问:覆盖索引能覆盖排序吗?答:联合索引顺序同时支撑过滤与排序时可避免 filesort。
05.2.2.1-002 隐式类型转换为什么会导致索引失效?
频率 ★★★★★ | 难度 进阶
一句话结论
当查询列类型与传入参数类型不一致,引擎对列做隐式类型转换/函数运算,索引列上的有序性被破坏,无法按索引前缀定位,退化为全表扫描。
概念解释
- 隐式转换:字符串列用数字比较、数值列传字符串等,引擎按类型规则转换。
- 索引失效机理:对索引列施加函数/转换后,索引键值不能直接用于范围匹配。
- 典型场景:
varchar手机号/订单号用数字传入。
原理流程
- 解析比较表达式,发现两侧类型不一致。
- 引擎对列侧做转换,索引列值被函数包裹。
- 优化器无法使用该索引前缀,选择全表扫描。
代码 / 命令
-- 失效:phone 是 varchar,传入数字触发转换
SELECT * FROM users WHERE phone = 13800000000;
-- 正确:参数类型与列一致
SELECT * FROM users WHERE phone = '13800000000';
常见误区
- 误区:隐式转换只是小问题,索引还能走一部分。正解:列侧被函数包裹后索引前缀不可用,通常整列失效。
- 误区:参数侧转换也会失效。正解:转换发生在列侧才破坏索引;参数侧调整不影响索引使用。
追问
- 问:如何判断是否隐式转换?答:看计划 type 为 ALL,对比列类型与参数类型,必要时在表达式上核对转换规则。
- 问:字符集不一致会有类似问题吗?答:跨字符集比较也可能触发转换使索引失效,需统一连接与列字符集。
05.2.2.1-003 OR 条件怎么优化?
频率 ★★★☆☆ | 难度 进阶
一句话结论
OR 连接的多个条件若分别有索引,优化器可能做索引合并;否则 OR 会退化为全表扫描。可把 OR 改写为 UNION/UNION ALL,让每个分支独立走索引,再合并结果。
概念解释
- 索引合并:多个索引分别扫描后合并交集/并集。
- OR 改写 UNION:每个分支单独走索引,结果去重或合并。
- 去重成本:UNION 去重,UNION ALL 不去重,按业务是否重复选择。
原理流程
- 分析 OR 各条件列是否有独立索引。
- 无合并能力时改写为 UNION 分支。
- 核对结果集与原 OR 语义一致。
代码 / 命令
-- 原 OR
SELECT * FROM users WHERE name = 'a' OR email = 'b@x.com';
-- 改写为 UNION(去重)
SELECT * FROM users WHERE name = 'a'
UNION
SELECT * FROM users WHERE email = 'b@x.com';
常见误区
- 误区:OR 一定比 AND 慢。正解:各分支有索引且能合并时,OR 也可高效执行。
- 误区:UNION 与 UNION ALL 可互换。正解:UNION 额外去重排序,确定无重复用 UNION ALL 更省。
追问
- 问:OR 两边列在同一联合索引里能用上吗?答:联合索引按最左前缀,OR 跨列通常不能整体走一条索引。
- 问:索引合并有哪几种?答:交集、并集、排序并集合并,具体形态见 05.2.2.2。
05.2.2.1-004 联合索引怎么建?列顺序如何排?
频率 ★★★★★ | 难度 进阶
一句话结论
联合索引按「等值条件列在前、范围条件列在后」排列,高选择性与常用过滤列优先;遵循最左前缀,索引顺序与查询条件顺序匹配才能用上前缀。
概念解释
- 最左前缀:从索引最左列开始连续匹配,断裂后后续列无法用于定位。
- 等值在前:等值匹配先缩小范围,再用后续列过滤/排序。
- 选择性:列唯一值越多选择性越好,靠前更利于过滤。
原理流程
- 列出高频查询的 WHERE/JOIN/ORDER BY 列。
- 等值列优先,范围列置于其后,兼顾排序需求。
- 避免为每条 SQL 建独立索引,合并重复模式。
代码 / 命令
-- 常用:user_id 等值 + status 等值 + created_at 范围/排序
CREATE INDEX idx_uid_status_time ON orders(user_id, status, created_at);
常见误区
- 误区:把区分度最低的列放最左。正解:最左列决定索引能否被条件命中,应选常用等值过滤列。
- 误区:联合索引列顺序与书写顺序无关。正解:条件顺序不影响,但索引列顺序决定最左前缀能否匹配。
追问
- 问:范围条件后面的列为什么用不上?答:范围扫描后后续列在索引中无序,无法继续定位。
- 问:联合索引能支撑多个排序方向吗?答:索引列顺序与排序方向一致才有效,反向排序需反向索引,详见 05.2.2.3。
05.2.2.1-005 最左前缀规则有哪些常见反例?
频率 ★★★★☆ | 难度 进阶
一句话结论
跳过最左列、对最左列做函数/范围断裂、前导模糊匹配都会使联合索引前缀失效;范围列之后的列不能用于索引定位。
概念解释
- 跳过最左列:条件从第二列开始,无法使用该联合索引。
- 函数包裹:最左列被函数运算破坏有序性。
- 前导模糊:
LIKE '%xx'无法按前缀定位。
原理流程
- 优化器按索引列顺序检查条件能否连续匹配前缀。
- 任一前缀列条件不可用,后续列定位中断。
- 计划退化为索引扫描/全表扫描并回表过滤。
代码 / 命令
-- idx(a,b,c):以下均无法完整走前缀
WHERE b = 2; -- 跳过 a
WHERE DATE(create_time) = '2026-01-01'; -- 函数包裹列
WHERE name LIKE '%abc'; -- 前导模糊
常见误区
- 误区:联合索引任意列单独查都能用索引。正解:必须从最左列连续匹配。
- 误区:
LIKE 'abc%'也走不了索引。正解:后缀模糊(前缀匹配)可利用索引范围扫描。
追问
- 问:前导模糊怎么优化?答:全文索引/倒排检索,或业务上改后缀匹配,详见 05.3.2.1 对照。
- 问:最左前缀断裂后索引完全无用吗?答:可能退化为扫描该索引,覆盖索引场景仍有回表规避价值。
05.2.2.1-006 什么是索引下推(ICP)?
频率 ★★★★☆ | 难度 进阶
一句话结论
索引下推把索引中可判断的过滤条件下推到存储引擎层,在遍历索引时先过滤掉不匹配记录,减少回表次数;5.7 引入,计划 Extra 显示 Using index condition。
概念解释
- 无 ICP:存储引擎按索引取记录回表,Server 层再过滤条件,回表次数多。
- 有 ICP:索引列上的条件在引擎层先判断,不匹配不回表。
- 适用条件:过滤条件涉及索引中已有列。
原理流程
- 遍历联合索引条目,先用索引列条件过滤。
- 通过过滤的条目才回表取整行。
- 减少无效回表随机 IO。
代码 / 命令
-- Extra: Using index condition 表示触发 ICP
EXPLAIN SELECT * FROM users
WHERE name LIKE '张%' AND phone LIKE '138%';
常见误区
- 误区:ICP 就是覆盖索引。正解:ICP 减少回表次数,覆盖索引是不回表,两者机制不同。
- 误区:所有索引条件都能下推。正解:只下推引擎可在索引层判断的条件,表达式/函数能力受版本限制(待确认细节以手册为准)。
追问
- 问:ICP 在哪个层级生效?答:存储引擎层,把过滤提前到索引遍历阶段。
- 问:如何确认是否用了 ICP?答:看 EXPLAIN Extra 是否含
Using index condition。
05.2.2.1-007 回表与覆盖索引如何判定?
频率 ★★★★☆ | 难度 进阶
一句话结论
二级索引扫描后,若所需列不在索引中,需拿主键回聚簇索引取整行即回表;查询列全部落在索引(含主键)中则覆盖索引,Extra 为 Using index。
概念解释
- 回表路径:二级索引叶子 → 主键 → 聚簇索引页取整行。
- 覆盖判定:SELECT 列、条件列是否都在索引键与主键中。
- 代价:回表是随机 IO,大范围回表代价高。
原理流程
- 计划选择二级索引扫描。
- 检查输出列是否被索引覆盖。
- 未覆盖则逐行回表;覆盖则直接返回索引数据。
代码 / 命令
EXPLAIN SELECT id, name FROM users WHERE phone = '138...';
-- 索引含 phone;若 name 不在索引则回表,Extra 无 Using index
常见误区
- 误区:走了二级索引就没有回表。正解:非覆盖索引必然回表取非索引列。
- 误区:聚簇索引查询也叫回表。正解:聚簇索引叶子即数据,不存在回表。
追问
- 问:大范围扫描为什么回表代价高?答:逐行随机访问聚簇页,随机 IO 远多于顺序扫描。
- 问:减少回表的手段有哪些?答:覆盖索引、INCLUDE/扩展索引列、避免 SELECT *。
05.2.2.1-008 索引下推在 5.7/8.0 行为有差异吗?
频率 ★★☆☆☆ | 难度 高级
一句话结论
ICP 自 5.7 引入,8.0 延续并在部分场景扩展可下推条件;具体支持的表达式与边界随版本调整,不确定行为标待确认,以目标版本优化器手册为准。
概念解释
- 版本基线:5.7 起默认支持 ICP。
- 扩展范围:新版可能支持更多索引条件下推形式。
- 验证方式:在目标版本用 EXPLAIN 观察
Using index condition是否出现。
原理流程
- 对比目标版本计划 Extra 标识。
- 核对条件类型是否被下推。
- 记录版本差异,不跨版本套用结论。
代码 / 命令
-- 待确认:具体可下推表达式范围以目标版本官方手册为准
EXPLAIN <带索引列条件的查询>;
常见误区
- 误区:ICP 行为各版本完全一致。正解:下推能力随版本演进,需在实际版本验证。
- 误区:关闭 ICP 会显著提升性能。正解:ICP 通常减少回表,关闭后回表增多,仅在异常计划下评估。
追问
- 问:如何关闭/开启 ICP?答:通过优化器开关参数控制,参数名随版本以手册为准。
- 问:ICP 与覆盖索引能同时出现吗?答:可以,先索引层过滤再覆盖返回。
05.2.2.1-009 前缀索引适合什么场景?
频率 ★★★☆☆ | 难度 进阶
一句话结论
长字符串列只索引前 N 个字符,减小索引体积;适目前缀区分度足够的场景,代价是无法用于覆盖索引与后缀/完整值精确匹配,需评估前缀选择性。
概念解释
- 前缀索引:
INDEX(col(N)),索引存列前 N 字符。 - 选择性评估:前缀唯一值占比接近全列才划算。
- 限制:不能回表取完整列,不能用于索引覆盖该列。
原理流程
- 统计不同前缀长度的区分度。
- 选区分度达标且最短的前缀长度建索引。
- 等值/前缀范围查询可用,后缀匹配无效。
代码 / 命令
CREATE INDEX idx_name_prefix ON users(name(10));
-- 评估前缀区分度
SELECT COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) FROM users;
常见误区
- 误区:前缀索引能替代完整索引做覆盖。正解:索引只存前缀,完整值仍需回表。
- 误区:前缀越短越好。正解:过短区分度低,索引过滤效果差。
追问
- 问:前缀索引能用于 ORDER BY 该列吗?答:只能按前缀排序,完整列顺序不保证。
- 问:长文本列还有什么索引手段?答:函数索引、全文索引或拆短列,按场景选择。
05.2.2.1-010 MySQL 8.0 函数索引怎么用?
频率 ★★★☆☆ | 难度 进阶
一句话结论
8.0 支持对表达式结果建索引,解决「对列做函数/运算导致普通索引失效」的问题;表达式需确定且可索引,查询条件使用相同表达式才能命中。
概念解释
- 函数索引:索引键为表达式结果,而非原始列值。
- 命中条件:查询 WHERE 的表达式与索引表达式一致。
- 适用场景:
DATE(col)、表达式归一化、生成列配合索引。
原理流程
- 识别高频被函数包裹的查询条件。
- 对相同表达式建函数索引。
- 查询使用一致表达式时按表达式索引定位。
代码 / 命令
CREATE INDEX idx_create_date ON orders(DATE(created_at));
-- 命中:表达式一致
SELECT * FROM orders WHERE DATE(created_at) = '2026-11-19';
常见误区
- 误区:建了函数索引,原始列上的任意函数都能走。正解:表达式必须与索引定义完全一致。
- 误区:函数索引可替代列索引。正解:它服务特定表达式查询,其他条件仍需普通索引。
追问
- 问:函数索引与生成列索引区别?答:生成列先落列再建索引,函数索引直接存表达式结果,实现路径不同。
- 问:表达式有哪些限制?答:必须是确定性表达式,具体限制清单以官方手册为准(待确认)。
05.2.2.1-011 不可见索引有什么用?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
不可见索引对优化器不可见,用于先摘除疑似冗余索引观察计划与性能,确认无用后再删除;比直接删除更安全,可随时恢复可见。
概念解释
- 不可见(invisible):索引存在但优化器默认不选。
- 灰度摘除:先置不可见观察业务 SQL 计划与性能。
- 回滚方便:改回可见即恢复索引。
原理流程
- 怀疑某索引冗余,先设为不可见。
- 观察慢查询、计划与负载变化。
- 无负面影响再正式删除;有问题恢复可见。
代码 / 命令
ALTER TABLE users ALTER INDEX idx_phone INVISIBLE;
ALTER TABLE users ALTER INDEX idx_phone VISIBLE;
常见误区
- 误区:不可见索引不占写入维护成本。正解:索引仍在维护,只是不被优化器选用。
- 误区:不可见索引等于删除。正解:结构仍在,删除前先不可见是灰度手段。
追问
- 问:不可见索引影响约束吗?答:唯一约束相关索引行为需单独评估,约束是否随索引隐藏以手册为准。
- 问:如何判断索引冗余?答:结合计划使用记录、重复索引与写入开销综合评估。
05.2.2.1-012 如何识别冗余与重复索引?
频率 ★★★★☆ | 难度 进阶
一句话结论
重复索引指列顺序完全相同的多条索引;冗余索引指某索引是另一联合索引的最左前缀,可被覆盖。通过列顺序、前缀包含关系与实际使用记录识别,删除前确认无特殊依赖。
概念解释
- 重复索引:同表同列同序重复创建,浪费写入与空间。
- 冗余索引:索引 A 的列是索引 B 的最左前缀,A 功能被 B 覆盖。
- 使用记录:长期未被优化器选用的索引优先评估删除。
原理流程
- 列出表上全部索引及列顺序。
- 比对重复与前缀包含关系。
- 结合使用统计与不可见灰度,安全清理。
代码 / 命令
SELECT index_name, column_name, seq_in_index
FROM information_schema.statistics
WHERE table_schema='your_db' AND table_name='orders'
ORDER BY table_name, index_name, seq_in_index;
常见误区
- 误区:存在前缀关系就一定能删。正解:需确认长索引不能被短索引替代的反向场景,以及计划依赖。
- 误区:删除索引不影响写入。正解:减少索引会降低写入维护开销,但查询可能走其他计划,需回归验证。
追问
- 问:主键索引会被冗余删除吗?答:主键是聚簇组织依据,不能按普通冗余索引处理。
- 问:外键会自动建索引吗?答:外键列通常需要索引支撑级联检查,删除前需确认。
05.2.2.1-013 自增主键对二级索引有什么影响?
频率 ★★★☆☆ | 难度 进阶
一句话结论
二级索引叶子携带主键值,主键越窄二级索引体积越小;自增主键顺序插入避免聚簇页分裂,宽主键(如 UUID)会放大所有二级索引并增加随机页分裂。
概念解释
- 叶子携带主键:二级索引叶子存索引键 + 主键。
- 主键宽度:主键列长度直接乘到每个二级索引条目上。
- 插入顺序:自增主键顺序写入聚簇尾部,UUID 随机插入导致页分裂。
原理流程
- 选窄有序主键降低二级索引体积。
- 顺序插入保持聚簇页紧凑。
- 随机主键引发页分裂与碎片,写入性能下降。
代码 / 命令
-- 自增有序主键
CREATE TABLE t (id BIGINT AUTO_INCREMENT PRIMARY KEY, ...);
-- UUID 随机主键需评估二级索引体积与分裂
常见误区
- 误区:主键只影响聚簇索引。正解:所有二级索引叶子都携带主键,主键宽放大全局索引体积。
- 误区:UUID 做主键没有副作用。正解:随机插入导致页分裂与碎片,二级索引也更宽。
追问
- 问:业务唯一键为什么不直接当主键?答:可能宽或变更,用代理自增主键更稳,业务唯一键建二级索引。
- 问:页分裂是什么?答:页满后插入新行拆分页,详见 05.2.1.4 与碎片整理。
05.2.2.1-014 外键与索引维护有什么开销?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
外键约束要求子表外键列有索引以支撑关联检查与级联操作;约束维护在写入路径上增加检查开销,高并发写入场景需权衡约束与应用层校验。
概念解释
- 外键检查:插入/更新子表时校验父表存在;删改父表时检查子表引用。
- 配套索引:外键列索引加速反向关联检查。
- 写入开销:约束检查与索引维护增加写路径成本。
原理流程
- 写入子表按外键查父表校验存在性。
- 父表变更检查子表引用,必要时限制或级联。
- 相关索引随写入维护。
代码 / 命令
-- 查看外键
SELECT * FROM information_schema.referential_constraints
WHERE constraint_schema='your_db';
常见误区
- 误区:外键能提升查询性能。正解:外键是约束机制,性能来自配套索引,本身增加写检查。
- 误区:分布式库表适合用外键。正解:跨库无法建外键,一致性由应用/事务方案保证。
追问
- 问:大表删除父行为什么慢?答:需遍历子表引用检查,外键索引缺失会全表扫描。
- 问:外键与应用层校验怎么选?答:强一致单库可用外键,高并发/分布式多由应用层保证。
05.2.2.1-015 索引选择性与统计信息有什么关系?
频率 ★★★☆☆ | 难度 进阶
一句话结论
选择性指索引列唯一值占比,越高越能过滤数据;优化器依据统计信息估算选择性与行数,统计过期会低估/高估,导致选错索引。
概念解释
- 选择性:
distinct(col) / rows,接近 1 选择性好。 - 基数(cardinality):索引唯一值估算数量。
- 统计偏差:采样不足或数据倾斜时估算失真。
原理流程
- 采样收集列基数与分布。
- 优化器按选择性估算各索引代价。
- 统计过期后
ANALYZE TABLE更新,详见 05.2.2.2。
代码 / 命令
SHOW INDEX FROM orders; -- Cardinality 列
ANALYZE TABLE orders;
常见误区
- 误区:低选择性列绝对不该建索引。正解:联合索引中低选择性列可配合等值过滤,单独建才低效。
- 误区:Cardinality 是精确值。正解:采样估算,与实际唯一值有偏差。
追问
- 问:数据倾斜怎么影响选择性?答:热点值占比极高,按普通分布估算会偏差,需直方图辅助。
- 问:复合选择性怎么看?答:看多列组合区分度,而非单列值数量。
05.2.2.1-016 多值索引与 JSON 索引在 8.0 怎么用?
频率 ★★☆☆☆ | 难度 高级
一句话结论
8.0 支持对 JSON 数组建多值索引,以及对 JSON 路径表达式建索引,支撑数组包含/元素查询;具体语法、可索引类型与限制随版本演进,待确认处以官方手册为准。
概念解释
- 多值索引:为 JSON 数组每个元素建索引条目,支撑数组成员查询。
- JSON 路径索引:对指定 JSON 路径表达式建索引。
- 限制:并非所有 JSON 查询形态都能命中索引。
原理流程
- 确定高频 JSON 查询路径/数组字段。
- 按版本支持语法建对应索引。
- 查询表达式匹配索引形态时命中。
代码 / 命令
-- 待确认:多值索引语法与支持范围以 8.0 目标版本手册为准
-- CREATE INDEX idx_tags ON orders((CAST(tags AS ...)));
常见误区
- 误区:JSON 字段随便建个索引就能加速。正解:需匹配查询路径与索引形态,函数提取不匹配仍失效。
- 误区:JSON 索引与普通列索引维护成本相同。正解:数组多值条目放大索引体积与写入开销。
追问
- 问:复杂 JSON 检索更适合什么存储?答:大规模/全文/向量检索评估 ES、专用向量库,详见 05.5.2。
- 问:PG 的 GIN 索引对照?答:PG 用 GIN 索引支撑 JSONB/数组,见 05.3.2.1。
05.2.2.1-017 索引设计评审清单包含哪些项?
频率 ★★★★☆ | 难度 进阶
一句话结论
评审时核对:高频查询是否有匹配索引、索引列顺序是否等值在前、是否存在重复/冗余、是否覆盖高频输出列、写入代价与索引数量是否可控、失效场景是否规避。
概念解释
- 查询覆盖:核心 WHERE/JOIN/ORDER BY 列有索引支撑。
- 冗余控制:不建重复前缀索引,单表索引数量克制。
- 失效规避:避免列上函数、隐式转换、前导模糊。
原理流程
- 收集高频 SQL 与执行计划。
- 按联合索引规则设计/合并索引。
- 评估写入放大与冗余,上线后观察使用统计。
代码 / 命令
-- 评审输入:EXPLAIN 计划 + information_schema.statistics 索引清单
EXPLAIN <核心SQL>;
常见误区
- 误区:评审只看有没有索引。正解:还要看顺序、覆盖、冗余与写入代价。
- 误区:一次性给所有条件列建索引。正解:过多索引拖慢写入,应按高频模式合并。
追问
- 问:单表索引数量上限有经验值吗?答:无硬性上限,按写入负载与查询收益权衡,通常控制在个位数到十余条,实测为准。
- 问:上线后如何跟踪索引是否被使用?答:结合计划与性能 schema 统计(具体视图随版本以手册为准)。
05.2.2.1-018 与 PostgreSQL 索引体系如何对照?
频率 ★★★☆☆ | 难度 进阶
一句话结论
MySQL 以 B+ 树索引为主;PG 除 B-tree 外还有 Hash、GIN、GiST、SP-GiST、BRIN 等多类索引,适配数组/JSONB/几何/邻近/大表顺序扫描等场景。B+ 树设计思路相通,选型时按查询形态对照。
概念解释
- B-tree 对应:两者都有 B+ 树类索引支撑等值与范围。
- PG 专用索引:GIN 倒排、GiST 邻近、BRIN 精简摘要。
- 覆盖索引:两边都支持索引覆盖,实现细节不同。
原理流程
- 按查询形态(等值/范围/全文/邻近/大表扫描)选索引类型。
- 对照两边索引能力与限制。
- 迁移时不假设 MySQL 索引类型在 PG 有等价物。
代码 / 命令
-- PG:\d 表名 查看索引;MySQL:SHOW INDEX FROM 表名
常见误区
- 误区:MySQL 也有 GIN/GiST 索引。正解:这些是 PG 生态索引类型,MySQL 对应能力有限或需其他方案。
- 误区:索引命名与列顺序规则两边通用。正解:最左前缀思想相通,具体索引类型与参数需分别核对。
追问
- 问:PG Hash 索引为什么少用?答:历史上不支持 WAL 安全与并发,现代版本改进后仍有适用边界,详见 05.3.2.1。
- 问:大表低频列扫描用什么索引?答:PG BRIN 摘要索引,MySQL 侧评估分区/其他方案。