05.1.3.2 索引设计与失效场景
所属:全栈开发面试题库 / 05 数据库与存储 / 05.1 关系型数据库通用能力 / 05.1.3 SQL 优化与执行计划
覆盖范围
- 索引选择原则:过滤列、排序列、返回列与联合索引顺序
- 最左前缀、覆盖索引、前缀索引、表达式索引
- 常见失效场景:函数运算、隐式转换、前导模糊、OR、否定条件、低区分度
- 索引数量、写入代价与碎片影响
- MySQL 与 PostgreSQL 索引行为差异对照
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.1.3.2-001 | 如何为查询选择索引 | 进阶 | ★★★★★ |
| 05.1.3.2-002 | 联合索引列顺序 | 进阶 | ★★★★★ |
| 05.1.3.2-003 | 最左前缀与断裂场景 | 进阶 | ★★★★★ |
| 05.1.3.2-005 | 索引列函数/运算失效 | 进阶 | ★★★★★ |
| 05.1.3.2-007 | 前导模糊为什么不走索引 | 进阶 | ★★★★★ |
索引决策流程
分析查询:过滤条件(WHERE/ON)+ 排序分组(ORDER BY/GROUP BY)+ 返回列
→ 过滤列区分度高的入索引前列
→ 排序列按顺序补入 → 返回列补成覆盖索引
→ 校验最左前缀、避免函数/转换/前导模糊
→ 评估写入代价与索引数量,定期复核
05.1.3.2-001 如何为一条查询设计/选择索引?
频率 ★★★★★ | 难度 进阶
一句话结论
先从查询里拆出过滤列、排序列、返回列:把高区分度过滤列放索引前面,排序列按顺序补入,能覆盖返回列就做成覆盖索引;再用执行计划验证是否命中、是否回表,而不是凭感觉加索引。
概念解释
- 过滤列:
WHERE/JOIN ON中用于缩小范围的列,决定索引能否快速定位。 - 排序列:
ORDER BY/GROUP BY涉及的列,索引有序可避免额外排序。 - 返回列:
SELECT取到的列,补进索引可形成覆盖索引、避免回表。 - 区分度:列值唯一程度;区分度越高,索引过滤效果越好。
原理流程
- 拆解 SQL:等值/范围过滤列、排序分组列、投影列。
- 等值过滤列优先于范围列放入联合索引前部。
- 补排序列与返回列,形成覆盖索引候选。
EXPLAIN验证命中索引、扫描行数与 Extra;不满足再调整。
代码 / 命令
-- 查询:按 user_id 等值、status 等值,按 created_at 倒序取前 20
SELECT id, amount, created_at
FROM orders
WHERE user_id = 1001 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 20;
-- 候选联合索引:(user_id, status, created_at),返回列 id 为主键、amount 视需要补入
-- MySQL: EXPLAIN 看 type=ref、key=idx_...、Extra 是否 Using index
常见误区
- 误区:给每个查询条件单列各建一个索引就行。正解:单列索引只能用其中一个,组合条件需要联合索引;多列单列索引的合并能力有限且代价高。
- 误区:先加索引观察,不行再删。正解:每个索引都有写入与维护代价;应先按查询模式设计,上线后用计划与慢日志复核,而非堆砌索引。
追问
- 问:范围条件列后面的索引列还能用吗?答:联合索引中范围条件(>、<、LIKE 范围)之后的列无法再用于索引定位,只能做回表后过滤。
- 问:JOIN 条件列要不要建索引?答:连接列通常需要索引,否则嵌套循环内层全扫;两侧连接列都应评估。
05.1.3.2-002 联合索引的列顺序怎么排?
频率 ★★★★★ | 难度 进阶
一句话结论
联合索引按等值过滤列在前、范围过滤列在后、排序列紧随其后的原则排列;高区分度等值列优先,范围列一旦出现,其后列不再参与索引定位;同时兼顾最左前缀覆盖其他高频查询。
概念解释
- 等值列在前:
=/IN条件列先放,能先把范围收缩。 - 范围列在后:
>、<、BETWEEN、前缀匹配之外的范围条件放在等值列之后。 - 排序列配合:排序列与索引顺序一致时可利用索引有序,免掉 filesort。
- 复用最左前缀:顺序设计要让其他高频查询也能从最左列用上该索引。
原理流程
- 列出所有条件及类型(等值/范围)。
- 等值列按区分度与复用价值排序,范围列靠后。
- 排序列按
ORDER BY方向补入;检查索引顺序与排序方向是否一致。 - 用计划验证:是否命中联合索引、排序是否走索引。
代码 / 命令
-- 等值:user_id, status;范围:created_at;排序:created_at DESC
SELECT * FROM orders
WHERE user_id = 1001 AND status = 'PAID'
AND created_at >= '2026-01-01'
ORDER BY created_at DESC;
-- 推荐顺序:(user_id, status, created_at)
-- 反例:(created_at, user_id, status) —— 范围列在前,后续等值列无法定位
常见误区
- 误区:区分度最高的列永远放第一列。正解:还要看查询是否总是带该列;从不出现的高区分度列放第一,其他查询无法走最左前缀。
- 误区:联合索引列顺序按 SQL 中
WHERE书写顺序排即可。正解:书写顺序与索引顺序无关,按等值优先、范围靠后、复用最左前缀来设计。
追问
- 问:
IN条件算等值还是范围?答:IN是多值等值,处理上比单等值多值展开;其在联合索引中的位置仍按等值列对待,但多值组合会影响后续列的有序性。 - 问:升降序混合的排序怎么建索引?答:可对相应列指定升降序索引(索引列方向);MySQL/PG 均支持列级排序方向,具体语法以版本文档为准。
05.1.3.2-003 什么是最左前缀?哪些情况会断裂?
频率 ★★★★★ | 难度 进阶
一句话结论
最左前缀指联合索引从最左列开始连续匹配才能用上索引;跳过某列、范围列之后断裂、或前导列条件缺失,都会导致索引前缀断裂,后续列无法用于定位。
概念解释
- 最左列:联合索引第一列;查询条件不含它,通常无法使用该联合索引。
- 连续匹配:从第一列起依次命中等值条件,中间不能跳过。
- 断裂点:遇到范围条件后,其后列不再用于定位;某列条件缺失,其后列也无法定位。
原理流程
- 索引按列顺序组成键序(先排第一列,同值再排第二列……)。
- 查询从最左列匹配;前导列无等值条件,键序无法定位区间。
- 范围条件之后的数据在索引中不再按后续列严格有序,故断裂。
代码 / 命令
-- 联合索引:(a, b, c)
-- 能用:WHERE a = 1
-- 能用:WHERE a = 1 AND b = 2
-- 能用:WHERE a = 1 AND b = 2 AND c = 3
-- 断裂:WHERE b = 2 AND c = 3(缺前导列 a)
-- 断裂:WHERE a = 1 AND c = 3(跳过 b)
-- 范围后断裂:WHERE a = 1 AND b > 2 AND c = 3(c 无法用于定位)
常见误区
- 误区:只要查询里出现了索引任意一列就能走该索引。正解:必须从最左列连续匹配;跳过前导列只能退化为索引扫描或用其他单列索引。
- 误区:范围列后面的列在回表后仍能在索引上过滤。正解:断裂后后续列只能作为回表后的服务层过滤,不能缩小索引扫描区间。
追问
- 问:前缀索引与最左前缀是一回事吗?答:不是;最左前缀是联合索引列的匹配规则,前缀索引是对长文本列取前 N 个字符建索引。
- 问:OR 连接的条件能分别走联合索引前缀吗?答:
OR两侧若各有索引,可能走索引合并;联合索引前缀需在单侧条件内满足最左匹配。
05.1.3.2-004 覆盖索引为什么能避免回表?怎么设计?
频率 ★★★★★ | 难度 进阶
一句话结论
覆盖索引包含查询所需的全部列(过滤列 + 返回列),优化器直接从索引项返回结果,不必回表取整行;设计时把高频查询的返回列补进联合索引尾部。
概念解释
- 回表代价:二级索引取主键/行指针后再回主表取整行,随机 IO。
- 覆盖条件:
SELECT列表与条件列都能在索引中找到。 - 判定标记:MySQL
Extra: Using index;PGIndex Only Scan。 - 可见性:PG 需判断索引项对行版本的可见性;InnoDB 二级索引叶子存主键。
原理流程
- 列出查询过滤列与返回列。
- 过滤列按规则放索引前部,返回列补入尾部。
- 计划显示覆盖标记即免回表;注意覆盖索引会增大索引体积与写入代价。
代码 / 命令
-- 高频查询:按 user_id 查订单状态与时间
SELECT user_id, status, created_at
FROM orders WHERE user_id = 1001;
-- 覆盖索引:(user_id, status, created_at)
-- MySQL Extra: Using index;PG: Index Only Scan
-- 若 SELECT *,返回列含未入索引列,无法覆盖
常见误区
- 误区:覆盖索引没有任何代价。正解:把更多列塞进索引会增大索引体积、拖慢写入与索引维护;只为确有高频收益的查询做覆盖。
- 误区:主键列自动包含在二级索引里,可以不算返回列。正解:InnoDB 二级索引叶子含主键,主键可免回表取;其他普通列仍需补进索引才覆盖。
追问
- 问:覆盖索引能覆盖
ORDER BY吗?答:排序列在索引中且顺序一致时,覆盖索引同时提供有序性,避免 filesort。 - 问:PG 的 Index Only Scan 依赖什么可见性机制?答:依赖可见性 map(VM)判断索引项对应行是否可见;过期版本较多时仍需回堆,详见 05.3.1。
05.1.3.2-005 在索引列上用函数或运算为什么会失效?
频率 ★★★★★ | 难度 进阶
一句话结论
索引存的是原始列值的有序结构;对索引列做函数、算术或类型运算后,比较的是变换后的值,无法直接按原始键序定位区间,索引定位失效,常退化为全表/索引扫描。
概念解释
- 左值变换:
WHERE func(col) = ...、WHERE col + 1 = ...,索引键被加工。 - 有序性破坏:变换后的值与原始键序不再单调对应,无法用 B+ 树范围查找。
- 改写方向:把运算移到常量侧,让索引列保持裸列比较。
原理流程
- 优化器识别条件列;若列被函数/运算包裹,无法使用索引键定位。
- 只能扫描索引或全表,逐行计算函数值再比较。
- 改写为
col = 表达式常量形式,恢复裸列范围比较。
代码 / 命令
-- 失效:对索引列 created_at 套函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-11-19';
-- 改写:改为裸列范围比较
SELECT * FROM orders
WHERE created_at >= '2026-11-19' AND created_at < '2026-11-20';
-- 失效:列上算术运算
SELECT * FROM t WHERE amount + 100 > 500;
-- 改写
SELECT * FROM t WHERE amount > 400;
-- 确需按函数值查询时:建表达式/函数索引(PG 原生;MySQL 8.0 支持函数索引)
CREATE INDEX idx_order_date ON orders (DATE(created_at));
常见误区
- 误区:只要列上有索引,套什么函数都能用。正解:裸列才能利用键序;函数索引是为特定表达式单独建的索引,不等于原普通索引生效。
- 误区:把函数移到右边就算改写成功。正解:右侧必须是可预计算的常量/表达式;右侧仍含列时比较关系不变,索引仍无法定位。
追问
- 问:
YEAR(create_time) = 2026怎么优化?答:改为create_time >= '2026-01-01' AND create_time < '2027-01-01'范围比较;或建表达式索引。 - 问:MySQL 与 PG 对函数索引支持差异?答:PG 长期支持表达式索引;MySQL 8.0 起支持函数索引,低版本行为待确认,需按版本文档核对。
05.1.3.2-006 隐式类型转换为什么会让索引失效?
频率 ★★★★☆ | 难度 进阶
一句话结论
当查询参数类型与列类型不一致时,数据库会对列做隐式类型转换,等价于在索引列上套函数,键序被破坏而无法定位;保持参数类型与列类型一致即可避免。
概念解释
- 隐式转换:比较两侧类型不同时,引擎按类型提升规则转换一侧的值。
- 转换方向问题:若被转换的是索引列,裸列条件变为变换后比较,索引失效。
- 典型场景:字符列用数字查询、字符串/数字混用、时区或字符集不一致。
原理流程
- 比较列值与参数值类型不同。
- 引擎按规则转换;列侧被转换时等价于
func(col)。 - 索引无法按原始键定位,退化为扫描。
代码 / 命令
-- phone 是 VARCHAR,传数字触发隐式转换,索引失效
SELECT * FROM users WHERE phone = 13800000000;
-- 修正:参数用字符串,与列类型一致
SELECT * FROM users WHERE phone = '13800000000';
-- 计划中若列侧出现 CAST/转换函数,即说明发生了类型提升
常见误区
- 误区:数字写成字符串或字符串写成数字结果一样,性能无所谓。正解:语义结果可能相同,但转换方向决定是否命中索引;字符列查数字常导致全表扫描。
- 误区:隐式转换只发生在字符串列。正解:整数/浮点、日期/字符串、字符集不同的两侧比较都可能触发转换。
追问
- 问:如何判断计划里是否有隐式转换?答:看计划/语句中列侧是否出现
CAST、转换函数;或对照类型规则定位两侧类型不一致的列。 - 问:应用层传参如何避免?答:ORM/SQL 中按列类型绑定参数(字符串绑定字符串、数字绑定数字),不要靠数据库自动转换兜底。
05.1.3.2-007 前导模糊匹配(LIKE '%xx')为什么走不了索引?
频率 ★★★★★ | 难度 进阶
一句话结论
B+ 树按键值前缀有序;LIKE 'abc%' 有固定前缀可定位区间,LIKE '%abc' 或 LIKE '%abc%' 前导无固定前缀,无法确定索引区间,只能逐行扫描比较。
概念解释
- 前缀匹配:
LIKE '前缀%',匹配范围在索引键序中连续,可走范围扫描。 - 前导通配:
%开头,匹配目标散布在索引各处,无连续区间可定位。 - 替代方案:全文索引/倒排检索、反转列索引后缀匹配、专用搜索引擎;通用 B+ 树不适合任意子串搜索。
原理流程
- 索引按键值有序排列,前缀相同的值聚在一起。
- 前导通配时无法从键序确定起始位置,只能遍历全部键逐行模式匹配。
- 子串/全文检索应改用倒排索引类机制。
代码 / 命令
-- 可走索引范围:固定前缀
SELECT * FROM goods WHERE name LIKE '无线耳机%';
-- 走不了普通 B+ 树索引:前导 %
SELECT * FROM goods WHERE name LIKE '%耳机%';
-- 替代:全文检索(MySQL FULLTEXT / PG GIN tsvector),或 ES 等专用搜索
常见误区
- 误区:给文本列建普通 B+ 树索引就能支持模糊搜索。正解:普通索引只支持前缀有序匹配;子串搜索需要倒排/全文索引机制。
- 误区:
LIKE '%abc%'加了索引后偶尔命中就是有效。正解:前导通配本质上无法按键序定位,计划命中普通索引通常只是索引全扫,收益有限。
追问
- 问:后缀匹配(以特定字符串结尾)怎么优化?答:可存一份反转列并对反转列建前缀索引;或用全文/倒排检索。
- 问:MySQL FULLTEXT 与 PG GIN 适合中文吗?答:需要配合合适分词器;中文分词质量影响召回,详见 05.5.2.3。
05.1.3.2-008 OR 条件下索引是怎么使用的?
频率 ★★★★☆ | 难度 进阶
一句话结论
OR 两侧条件若各有可用索引,优化器可能做索引合并分别扫描再合并结果;若任一侧无索引,整组 OR 常退化为全表扫描。把 OR 改写为 UNION ALL 可让各分支独立走索引。
概念解释
- 索引合并(index merge):对同一表多个索引分别扫描,按并集/交集合并行指针。
- OR 退化:一侧无索引时,优化器无法只靠索引覆盖该侧,只能全表扫描逐行判断。
- UNION ALL 改写:各分支独立查询、各自走索引,再合并结果;注意去重与语义等价。
原理流程
- 分析
OR两侧列是否各自有索引。 - 都有索引:评估索引合并代价;一侧缺失:倾向全表扫描。
- 改写为
UNION ALL(或UNION需去重)让分支独立命中索引。
代码 / 命令
-- OR:user_id 或 phone 各自有索引时可能索引合并
SELECT * FROM users WHERE user_id = 1001 OR phone = '13800000000';
-- 改写:UNION ALL 各走索引(需确认两侧不产生重复行;重复用 UNION)
SELECT * FROM users WHERE user_id = 1001
UNION ALL
SELECT * FROM users WHERE phone = '13800000000';
常见误区
- 误区:
OR一定比AND慢。正解:两侧都有索引且命中行数少时,索引合并可接受;关键看每侧能否走索引。 - 误区:
UNION ALL与UNION可以随意互换。正解:UNION会去重(临时表/排序),UNION ALL不去重;改写前确认结果是否允许重复行。
追问
- 问:索引合并一定比单列索引扫描快吗?答:不一定;多次索引扫描加合并去重有额外代价,优化器会比较;实际以计划与实测为准。
- 问:跨表的 OR 条件怎么处理?答:通常拆成两个查询再合并;跨表条件更适合用连接分别处理。
05.1.3.2-009 否定条件(!=、NOT IN、NOT NULL)的索引行为如何?
频率 ★★★☆☆ | 难度 进阶
一句话结论
!=、NOT IN、NOT LIKE 这类否定条件通常无法有效收缩索引区间(反向集合在键序上不连续),优化器常倾向全表扫描;IS NULL/IS NOT NULL 能否走索引取决于引擎与索引设计。
概念解释
- 否定集合不连续:不等于某值的行散布在索引各处,无法定位一个连续区间。
NOT IN与 NULL:列表含 NULL 时比较结果为空,NOT IN可能查不到任何行,语义需特别注意。- NULL 索引:B+ 树可存 NULL 值;
IS NULL在部分引擎上可走索引,具体行为随版本。
原理流程
- 优化器评估否定条件的选择率:通常很高(命中大部分行)。
- 索引随机回表代价高于顺序扫描,倾向全表扫描。
- 业务上尽量用正向范围/等值条件表达;否定条件大表需单独评估。
代码 / 命令
-- 否定条件:通常难走索引
SELECT * FROM orders WHERE status != 'CANCELLED';
SELECT * FROM orders WHERE id NOT IN (1, 2, 3);
-- 语义风险:NOT IN 列表含 NULL 时整体不返回行
SELECT * FROM orders WHERE id NOT IN (1, 2, NULL); -- 结果为空
-- 正向改写示例:按业务拆成明确的状态集合
SELECT * FROM orders WHERE status IN ('PAID', 'SHIPPED', 'DONE');
常见误区
- 误区:
!=只要列上有索引就能用。正解:否定条件命中集合不连续,索引定位价值低;低区分度下全表扫描常更优。 - 误区:
NOT IN与NOT EXISTS完全等价。正解:NOT IN受 NULL 语义影响(含 NULL 结果为空),NOT EXISTS对 NULL 处理不同;相关否定查询优先核对语义。
追问
- 问:
IS NULL能走索引吗?答:多数引擎支持索引中定位 NULL(NULL 作为一组键值);是否被选中仍看选择率与版本行为,计划实测为准。 - 问:大表必须按否定条件查询怎么办?答:结合其他正向过滤条件缩小范围;评估是否需要分区、单独索引或业务上改设计。
05.1.3.2-010 区分度很低的列要不要建索引?
频率 ★★★★☆ | 难度 进阶
一句话结论
单列低区分度列(如性别、状态)单独建索引通常收益很小:命中行多、回表代价高,优化器常放弃它;价值在于作为联合索引的前导/配合列,或配合其他强过滤条件使用。
概念解释
- 区分度(cardinality):列不同值数量与总行数之比;区分度越低,单值命中行数越多。
- 低区分度代价:索引扫描后大量回表,随机 IO 累积可能超过全表扫描。
- 联合价值:低区分度列与高区分度列组成联合索引,由后者收缩范围。
原理流程
- 统计列不同值数量与分布。
- 单列评估:单值命中占比高 → 索引价值低。
- 联合评估:作为联合索引中的过滤/覆盖列,与高区分度列配合。
代码 / 命令
-- 查看列区分度(PG)
SELECT count(DISTINCT status), count(*) FROM orders;
-- 低区分度列不建议单列索引;可放入联合索引后部
CREATE INDEX idx_user_status ON orders (user_id, status);
常见误区
- 误区:区分度低就绝对不能建索引。正解:单列价值低,但在联合索引、覆盖索引或与强条件组合时仍可能有用。
- 误区:索引基数(cardinality)越高越好,越低越差。正解:还要看查询是否真的按该列过滤;基数高但从不被查询的列,索引也无收益。
追问
- 问:布尔列、状态列常见优化手段?答:联合索引配合高区分度列;或按状态分区/归档冷热数据,减少需扫描的量。
- 问:优化器如何判断低区分度索引不值得用?答:按统计的选择率估算回表行数与代价,超过拐点即选全表扫描。
05.1.3.2-011 联合索引和多个单列索引如何取舍?
频率 ★★★☆☆ | 难度 进阶
一句话结论
固定组合条件优先用联合索引,可按列序连续定位并支持覆盖;查询模式零散、各条件独立出现时才考虑单列索引;多单列索引依赖索引合并,合并代价与顺序覆盖都弱于联合索引。
概念解释
- 联合索引:一列有序基础上再排下一列,支持最左前缀匹配。
- 单列索引:各列独立索引;查询组合条件时只能选其一或做索引合并。
- 索引合并局限:多索引分别扫描再合并行指针,无法利用列间有序关系,去重与回表代价高。
原理流程
- 梳理条件出现的固定组合。
- 高频固定组合 → 联合索引;独立出现的零散条件 → 单列索引补充。
- 评估索引总数与写入代价,避免重复与冗余。
代码 / 命令
-- 高频组合:WHERE user_id = ? AND status = ?
-- 联合索引 (user_id, status) 优于两个单列索引
CREATE INDEX idx_user_status ON orders (user_id, status);
常见误区
- 误区:每加一个条件列就建一个单列索引,组合起来自然快。正解:组合条件走不了多列有序定位,索引合并能力有限;固定组合应建联合索引。
- 误区:联合索引能替代所有单列索引。正解:只查第二列(无前导列)的查询用不上联合索引;需保留对应单列索引或调整设计。
追问
- 问:如何识别冗余索引?答:一个索引的列前缀已被另一索引覆盖,后者对无前缀查询才有独立价值;定期核对重复/冗余索引。
- 问:联合索引能服务多少种查询?答:能服务最左前缀覆盖到的查询模式;超出前缀的条件需其他索引。
05.1.3.2-012 索引建多了有什么代价?
频率 ★★★★☆ | 难度 进阶
一句话结论
每个索引都要随写入维护:插入/更新/删除需同步更新索引结构,占用存储与内存,加剧页分裂、锁与脏页压力;读快写慢的表堆索引会拖慢写入并拉长备份与恢复时间。
概念解释
- 写入维护:每次写操作要更新所有相关索引;索引越多,单条写入成本越高。
- 存储与缓存:索引占用磁盘与 Buffer Pool 空间,挤占热数据缓存。
- 优化器干扰:冗余/低质量索引增加候选计划评估成本,偶尔误导选型。
原理流程
- 写入行 → 更新聚簇/主表 → 逐一定位并更新各二级索引。
- 索引越多,B+ 树修改、页分裂与缓冲占用越多。
- 定期下线未被使用的索引(结合慢日志/计划统计)。
代码 / 命令
-- MySQL:查看索引使用情况需结合性能 schema / 慢日志(具体视图见 05.2.4)
-- PG:pg_stat_user_indexes 记录索引扫描次数
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;
常见误区
- 误区:索引越多查询越快。正解:只对被使用、有过滤收益的索引有益;未使用索引纯增写入与存储负担。
- 误区:删除索引只影响查询,不影响写入。正解:索引直接增加写入维护成本;高并发写入表对索引数量更敏感。
追问
- 问:如何判断一个索引是否长期未被使用?答:结合索引使用统计(扫描次数)与慢日志/计划;长时间零使用且无明确规划的索引可评估下线。
- 问:更新索引列会不会放大写入代价?答:会;被更新的索引列若属于多个索引,每次更新都要维护对应索引,应避免频繁更新索引键列。
05.1.3.2-013 索引碎片与统计信息如何影响索引选择?
频率 ★★★☆☆ | 难度 高级
一句话结论
数据大量增删改后页/块出现碎片,索引层级与占用页数膨胀,优化器按过期统计估算代价,可能放弃有效索引或选差计划;重建/整理索引并更新统计信息后,计划可能恢复正常。
概念解释
- 索引碎片:页内空闲空间、页分裂导致的不连续,扫描页数增多。
- 统计过期:行数、基数、分布未随数据更新,估算选择率偏差。
- 连锁影响:碎片抬高 IO 估算,过期统计扭曲行数估算,共同导致选型偏差。
原理流程
- 观察计划中索引扫描页数、估算与实际偏差。
- 检查索引碎片率与统计新鲜度。
- 重建/整理索引、更新统计信息后重跑计划对比。
代码 / 命令
-- MySQL:更新统计信息
ANALYZE TABLE orders;
-- PG:收集统计信息
ANALYZE orders;
-- PG:查看索引膨胀/使用情况(具体视图见 05.3.1)
SELECT relname, n_dead_tup, last_analyze FROM pg_stat_user_tables WHERE relname = 'orders';
常见误区
- 误区:计划选错索引一定是 SQL 写错了。正解:碎片与统计过期也会让优化器误判;先核对物理状态与统计,再谈改写。
- 误区:重建索引能解决所有慢查询。正解:重建只改善物理结构与统计;索引设计缺失、条件无法定位等问题重建解决不了。
追问
- 问:碎片多到什么程度需要整理?答:结合碎片率、扫描页数与业务窗口评估;不同引擎阈值与工具不同,具体见 05.2.4 / 05.3.4。
- 问:统计信息更新后计划会自动变好吗?答:估算修正后优化器更可能选对路径;计划缓存是否立即刷新随引擎机制,必要时重跑语句观察。
05.1.3.2-014 什么是表达式索引(函数索引)?
频率 ★★★☆☆ | 难度 高级
一句话结论
表达式索引把函数/表达式的计算结果作为索引键存储,支持按该表达式的值快速检索;当查询条件必须对列做函数计算、又需要索引时使用,PG 原生支持,MySQL 8.0 起支持函数索引。
概念解释
- 普通索引:键为列原始值。
- 表达式索引:键为表达式结果(如
DATE(created_at)、LOWER(name))。 - 匹配要求:查询表达式与索引表达式写法一致才能命中。
- 代价:额外存储与表达式计算维护成本;只对确有需求的表达式建立。
原理流程
- 识别必须用表达式过滤的高频查询。
- 按相同表达式创建索引。
- 查询条件保持表达式一致,优化器按表达式键定位。
代码 / 命令
-- PostgreSQL 表达式索引
CREATE INDEX idx_orders_date ON orders (DATE(created_at));
SELECT * FROM orders WHERE DATE(created_at) = '2026-11-19';
-- MySQL 8.0 函数索引(语法以当前版本文档为准)
CREATE INDEX idx_lower_name ON users ((LOWER(name)));
SELECT * FROM users WHERE LOWER(name) = 'alice';
常见误区
- 误区:建了普通列索引就能配合函数条件使用。正解:函数条件需要表达式索引;普通索引存原始值,无法按变换后值定位。
- 误区:表达式索引可以随意写任意复杂表达式。正解:表达式必须稳定可计算、结果可排序;复杂表达式维护成本高,且需与查询写法严格一致。
追问
- 问:表达式索引与生成列索引是什么关系?答:MySQL 早期可借助持久化生成列 + 普通索引实现等价效果;8.0 函数索引是直接支持,细节版本相关待确认。
- 问:表达式索引会覆盖普通查询吗?答:不会;它只服务对应表达式条件,裸列查询仍需普通索引。
05.1.3.2-015 长文本/长字符串列为什么用前缀索引?
频率 ★★★☆☆ | 难度 进阶
一句话结论
长字符串全列入索引体积大、维护贵;前缀索引只取列前 N 个字符建索引,减小体积,适合前缀区分度足够的场景;代价是无法用于覆盖索引与后缀/排序判断,前缀重复度高时区分度不足。
概念解释
- 前缀索引:索引键为
col(N)前 N 字符。 - 区分度校验:选 N 时平衡体积与区分度,前 N 字符已能区分大部分值。
- 局限:不能覆盖取整列的查询;不能支持按完整列排序;回表不可避免。
原理流程
- 评估前若干字符的区分度。
- 取合适长度建前缀索引。
- 前缀匹配查询走索引;完整列查询与排序仍需其他方案。
代码 / 命令
-- MySQL:前缀索引
CREATE INDEX idx_name_prefix ON goods (name(20));
-- 评估前缀区分度:比较不同长度下的唯一值比例
SELECT count(DISTINCT LEFT(name, 10)) / count(*),
count(DISTINCT LEFT(name, 20)) / count(*)
FROM goods;
常见误区
- 误区:前缀索引越长越好。正解:长度增加体积与维护成本;应选区分度足够的最小长度。
- 误区:前缀索引能做覆盖索引。正解:索引只存前缀,需要完整列时仍需回表,无法覆盖完整列查询。
追问
- 问:PG 有前缀索引等价物吗?答:PG 可用表达式索引取
LEFT(col, n)实现类似效果;完整长文本检索更适合倒排/全文索引。 - 问:前缀重复严重怎么办?答:说明该列前缀区分度低,前缀索引收益有限,需结合全文检索或调整建模。
05.1.3.2-016 索引合并与多列索引在计划中如何判断?
频率 ★★★☆☆ | 难度 高级
一句话结论
索引合并是对同一表多个单列/索引分别扫描后按并集或交集合并行指针;多列联合索引则在单一索引内按列序定位。计划中看到多个索引扫描节点合并,即索引合并;其效率通常弱于一条设计合理的联合索引。
概念解释
- 并集合并(OR):各索引命中行取并集。
- 交集合并(AND):各索引命中行取交集。
- 合并代价:多次索引扫描、行指针排序去重、回表。
- 对比联合索引:联合索引在键序上连续定位,单次扫描即可。
原理流程
- 计划识别是否出现多个索引扫描节点后接合并节点。
- 评估合并扫描量与回表代价。
- 若合并频繁且代价高,评估改造为联合索引。
代码 / 命令
-- 两个单列索引可能触发索引合并
EXPLAIN SELECT * FROM users WHERE city = '昆明' AND level = 5;
-- 改造为联合索引后,计划变为单条联合索引定位
CREATE INDEX idx_city_level ON users (city, level);
常见误区
- 误区:出现索引合并说明优化器很聪明,不用再优化。正解:合并往往是缺乏合适联合索引的折中;固定组合条件仍应优先设计联合索引。
- 误区:交集合并一定缩小结果就快。正解:两次扫描加指针交集排序有额外成本;小结果集下联合索引更直接。
追问
- 问:MySQL 索引合并有哪几种类型?答:并集、交集、排序并集等;具体名称与适用条件见 05.2.2 与官方文档。
- 问:PG 有类似索引合并吗?答:PG 常通过 Bitmap 组合多个索引条件形成位图再回堆,机制不同但目标相近。
05.1.3.2-017 为排序(ORDER BY)建索引要注意什么?
频率 ★★★★☆ | 难度 进阶
一句话结论
排序要利用索引免掉 filesort,需满足过滤列在前、排序列在后且方向与索引一致,排序键唯一或附加主键打破并列;范围过滤之后的排序列不再有序,多列排序方向混合时要对应建方向索引。
概念解释
- 索引有序:B+ 树叶子按键序排列,顺序扫描即有序输出。
- 过滤先行:先经等值条件收缩,排序列在索引内保持有序。
- 并列打破:排序字段有重复值时,补主键/唯一键作为次级排序保证稳定顺序。
- 方向一致:
ASC/DESC与索引列方向匹配;混合方向需对应方向索引。
原理流程
- 写出过滤条件与
ORDER BY列及方向。 - 等值过滤列入索引前部,排序列按顺序补入。
- 计划确认排序走索引顺序(无 Sort/filesort 节点)。
代码 / 命令
-- 过滤:user_id 等值;排序:created_at DESC, id DESC
SELECT * FROM orders
WHERE user_id = 1001
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 索引:(user_id, created_at DESC, id DESC)
-- 注意:若 user_id 后接范围条件,created_at 在范围内不再严格有序
常见误区
- 误区:只要排序列上有索引就能免 filesort。正解:必须从索引最左前缀连续有序;范围条件之后排序列无序,仍需排序。
- 误区:排序列加进索引就不用考虑方向。正解:升降序混合时索引方向需匹配;方向不一致时仍可能排序。
追问
- 问:深分页配合排序索引为什么仍慢?答:定位前 N 行需跳过大量索引项;改 keyset 分页见 05.1.3.3。
- 问:
GROUP BY也能利用索引有序吗?答:可以,分组列顺序与索引一致时可免临时表排序;见 05.1.3.1-012。
05.1.3.2-018 索引设计评审清单有哪些?
频率 ★★★★☆ | 难度 基础
一句话结论
评审索引时逐项核对:查询模式是否覆盖、列顺序是否等值优先、是否满足最左前缀、能否覆盖返回列、区分度是否足够、写入代价与索引总量是否可控,并以计划与实测验证。
概念解释
- 覆盖核对:高频查询的过滤/排序/返回列是否被索引覆盖。
- 结构核对:最左前缀、范围列位置、方向、前缀长度。
- 代价核对:索引数量、写入维护、存储与缓存占用。
- 验证核对:计划命中、扫描行数、回表、实测耗时。
原理流程
- 汇总线上高频查询与条件模式。
- 按本域规则提出索引候选,检查冗余与重复。
- 上线后用计划、慢日志与索引使用统计复核,定期下线无用索引。
代码 / 命令
-- 评审动作:列出表上全部索引与列构成
-- MySQL: SHOW INDEX FROM orders;
-- PG: \d orders 或查 pg_indexes
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';
常见误区
- 误区:评审只看现有查询,不看写入频率。正解:读写比决定索引承受能力;高写入表要更严格控制索引数量。
- 误区:索引一旦建成就长期有效。正解:查询模式与数据分布会变,索引需定期复核使用情况与冗余度。
追问
- 问:一个表多少个索引算合理?答:没有固定数字;取决于读写比与查询模式,原则是每个索引都有明确高频收益。
- 问:评审时如何区分冗余索引?答:被另一索引前缀完全覆盖、且不服务无前缀独立查询的索引,属冗余候选。
参考来源
- MySQL 8.0 Reference Manual: Indexes:https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html
- MySQL 8.0 Reference Manual: Index Hints / Function Key Parts:https://dev.mysql.com/doc/refman/8.0/en/create-index.html
- PostgreSQL Documentation: Indexes and Index Types:https://www.postgresql.org/docs/current/indexes-types.html
- JavaGuide: MySQL 索引:https://javaguide.cn/database/mysql/mysql-index.html