05.1.1.2 联合索引与最左前缀
所属:全栈开发面试题库 / 05 数据库与存储 / 05.1 关系型数据库通用能力 / 05.1.1 索引原理与设计
覆盖范围
- 多列联合索引的排序结构与命中规则
- 等值列、范围列、排序列的放置顺序
- 最左前缀断裂场景与范围截断
- 联合索引服务排序、分组与 JOIN
- 列顺序设计、低区分度列与索引复用
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.1.1.2-001 | 联合索引按什么顺序排序 | 进阶 | ★★★★★ |
| 05.1.1.2-002 | 最左前缀原则 | 进阶 | ★★★★★ |
| 05.1.1.2-003 | 哪些条件会断裂最左前缀 | 进阶 | ★★★★★ |
| 05.1.1.2-005 | 等值列在前还是范围列在前 | 进阶 | ★★★★★ |
联合索引命中主线
索引 (a, b, c):先按 a 有序,a 相同按 b,b 相同按 c
定位路径:必须从最左列开始逐列匹配
等值列 → 等值列 → 范围列(范围之后的列不能继续用于定位)
排序:排序列按索引顺序接续在等值列之后,方向一致才可免 filesort
05.1.1.2-001 联合索引在 B+ 树里按什么顺序排序?
频率 ★★★★★ | 难度 进阶
一句话结论
联合索引 (a, b, c) 先按第一列 a 排序,a 相同再按 b,b 相同再按 c;B+ 树只能从最左列开始逐列定位,这是最左前缀规则的结构根源。
概念解释
- 字典序排列:多列组合值按列顺序比较,类似字符串字典序。
- 列顺序固定:索引一旦建成,列的前后顺序不可动态调整。
- 前缀有序:只有前导列确定后,后续列的有序性才有意义。
原理流程
- 叶子项按
(a, b, c)组合值排序。 - 查询先在
a上定位区间,a相同的项内部再按b有序。 - 缺少前导列时,后续列的值分散在整棵树中,无法连续定位。
代码 / 命令
索引 (tenant_id, status, create_time)
叶子项顺序:
(10, 'PAID', 09:00) (10, 'PAID', 10:30) (10, 'REFUND', 11:00)
(20, 'PAID', 09:10) (20, 'PAID', 12:00) ...
常见误区
- 误区:联合索引中各列各自独立排序。正解:只有前导列相同的分组内,后续列才有序。
- 误区:调整查询里的列书写顺序就能改变索引命中。正解:优化器会重排条件,但索引列顺序在建索引时已经固定。
追问
- 问:为什么
b单独查用不上(a,b)索引?答:b的值在树中跨多个a分组分散排列,不存在连续区间可定位。 - 问:联合索引项宽度怎么估算?答:为各索引列字节数加总,再加主键与开销,宽索引扇出下降。
05.1.1.2-002 什么是最左前缀原则?联合索引是如何命中的?
频率 ★★★★★ | 难度 进阶
一句话结论
使用联合索引必须从最左列开始连续匹配;跳过某一列后,该列及其后续列无法用于索引定位,但部分场景可在索引下推阶段做过滤。
概念解释
- 连续前缀:
(a,b,c)可支持a、a,b、a,b,c三种前缀路径。 - 断裂:缺少
a,或a之后跳过b直接用c,c不能参与定位。 - 下推过滤:断裂列上的条件可能被 ICP 在索引层过滤,但不改变定位路径。
原理流程
- 取出
WHERE条件中的索引列,按索引顺序对齐。 - 连续等值/范围条件用于定位,遇到断裂列即停止定位。
- 未参与定位的列若在索引中,可由索引下推在回表前过滤。
代码 / 命令
-- 索引 idx_abc(a, b, c)
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3; -- 全命中
SELECT * FROM t WHERE b = 2; -- 不走(缺最左列)
SELECT * FROM t WHERE a = 1 AND c = 3; -- 只用到 a,c 由 ICP 过滤
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3; -- a、b 定位,c 截断
常见误区
- 误区:条件里写了索引列就一定命中该索引。正解:必须满足连续最左前缀,否则只能部分使用或全表扫描。
- 误区:被截断的列完全没用。正解:定位之外仍可能在 ICP 阶段过滤,减少回表次数,但不能用它缩小扫描区间。
追问
- 问:
WHERE a=1 AND b=2与WHERE b=2 AND a=1有区别吗?答:优化器会重排条件,命中效果相同。 - 问:
LIKE 'abc%'为什么能走索引而LIKE '%abc'不能?答:前者有确定前缀可定位起点,后者无法在有序结构中确定起点。
05.1.1.2-003 哪些查询条件会断裂最左前缀?
频率 ★★★★★ | 难度 进阶
一句话结论
缺少最左列、中间列缺失、前导列被函数/运算包裹、前导模糊匹配、前导列类型隐式转换,都会使索引无法按前缀连续定位。
概念解释
- 缺列:条件不包含索引首列。
- 跳列:包含首列与第三列、缺第二列。
- 前导列失效:首列被函数、运算、隐式类型转换处理,索引原值无法参与比较。
- 模糊方向:
LIKE '%x'无前缀起点。
原理流程
- 检查条件列与索引列的对齐关系。
- 逐列判断是否能以索引原值做等值/范围比较。
- 任一前导列失效,后续列的有序性都无法利用。
代码 / 命令
-- 索引 (a, b, c)
WHERE b = 2; -- 断裂:缺 a
WHERE a = 1 AND c = 3; -- 断裂:缺 b
WHERE DATE(a) = '2026-01-01'; -- 断裂:a 被函数包裹
WHERE a = 1 AND b LIKE '%x'; -- b 前导模糊,无法定位
WHERE phone = 13800000000; -- 字符串列与数字比较,隐式转换
常见误区
- 误区:只要索引列出现在
WHERE里就满足最左前缀。正解:列必须以可比较的原值、按索引顺序连续出现。 - 误区:断裂后索引完全被忽略。正解:可能退化为索引全扫描或部分列过滤,具体访问路径看执行计划,详见 05.1.3.2。
追问
- 问:函数包裹前导列怎么改写?答:把条件转为对原始列的范围比较,如
create_time >= ... AND create_time < ...;改写细节见 05.1.3.3。 - 问:隐式类型转换为什么等于函数包裹?答:比较时列被隐式转成另一类型,索引存的原值无法直接比较,等价于对列做了转换函数。
05.1.1.2-004 联合索引的字段顺序应该怎么设计?
频率 ★★★★☆ | 难度 高级
一句话结论
按查询模式排序:等值条件列在前、范围条件列在后,排序列接续其后,再考虑区分度与覆盖需求;同时控制索引宽度,不把大字段纳入索引。
概念解释
- 等值优先:等值匹配能精确定位前缀区间,范围条件放在其后。
- 排序接续:
ORDER BY/GROUP BY列接在等值列后,利用索引有序性免排序。 - 区分度参考:区分度高的列通常更靠前,但要服从实际查询条件形态。
- 覆盖权衡:
SELECT列表列纳入索引可形成覆盖,但需评估宽度成本。
原理流程
- 列出高频查询的过滤条件、排序方向与返回列。
- 把等值列按区分度排列,范围列置于等值之后。
- 排序列按索引顺序接续,必要时纳入展示列做覆盖,控制总宽度。
代码 / 命令
-- 高频查询
SELECT id, status, amount FROM orders
WHERE tenant_id = ? AND status = ? AND create_time >= ? AND create_time < ?
ORDER BY create_time DESC;
-- 推荐索引:等值 tenant_id、status,范围与排序 create_time
ALTER TABLE orders ADD INDEX idx_tenant_status_time (tenant_id, status, create_time);
常见误区
- 误区:区分度最高的列永远放第一列。正解:首先服从查询条件形态,某列从不作为条件出现时,区分度再高也不能当前导列。
- 误区:联合索引列越多覆盖越全越好。正解:宽索引放大写入与空间成本,应只纳入高频查询所需列。
追问
- 问:索引列顺序能在线调整吗?答:不能,需重建索引;大表变更走在线流程,详见 05.1.1.5。
- 问:一条服务多个查询的联合索引怎么取舍?答:找公共前缀列,优先覆盖 QPS 最高的查询,次要查询接受部分命中。
05.1.1.2-005 等值列在前还是范围列在前?
频率 ★★★★★ | 难度 进阶
一句话结论
等值条件列放在范围条件列之前:等值能精确定位前缀,范围列放在其后;一旦某列是范围条件,它之后的列就不能再用于索引定位。
概念解释
- 等值定位:
=、IN在前缀上圈定确定区间。 - 范围条件:
>、<、BETWEEN、LIKE 'x%'界定区间边界。 - 截断效应:范围列之后的索引列在 B+ 树中不再连续有序。
原理流程
- 先把所有等值条件列排入索引前缀。
- 范围列放在等值列之后。
- 范围列之后的列只能用于过滤或覆盖,不能参与定位。
代码 / 命令
-- 推荐:(tenant_id, status, create_time)
WHERE tenant_id = ? AND status = ? AND create_time >= ?;
-- 不推荐:范围列在前,后续等值列被截断
索引 (create_time, status)
WHERE create_time >= ? AND status = ?; -- status 无法用于定位
常见误区
- 误区:
IN算等值还是范围?答:IN是多等值列表,通常按等值处理;多项IN对后续列有序性的影响需结合计划确认。 - 误区:范围列后的条件可以靠索引下推弥补定位缺失。正解:ICP 只在回表前过滤,不能把被截断的列变成定位路径。
追问
- 问:范围列同时是排序列怎么办?答:放在等值列之后,可同时服务范围过滤与索引排序,如示例中的
create_time。 - 问:多个范围条件怎么排?答:一般只能有一个范围列有效定位,其余范围条件作为过滤;多范围查询需结合执行计划评估。
05.1.1.2-006 范围条件为什么会截断后续列?
频率 ★★★★☆ | 难度 进阶
一句话结论
联合索引中前导列等值时后续列才有有序性;前导列进入范围后,后续列的值分散在多个区间内不再连续,因此范围列之后的索引列无法用于定位。
概念解释
- 有序前提:只有前导列确定为同一值,组内后续列才按键有序。
- 范围展开:前导列覆盖多个值时,每个值组内后续列各自有序,整体不连续。
- 截断位置:从第一个范围条件列开始,其后列失去定位价值。
原理流程
- 等值前缀把记录收敛到一个小组。
- 范围条件在小组内圈定区间,但小组内后续列的顺序不再跨区间连续。
- 后续列条件只能逐行过滤,不能再缩小索引区间。
代码 / 命令
-- 索引 (a, b, c)
WHERE a = 1 AND b > 2 AND c = 3;
-- a 等值定位;b 范围圈定;c 分散在 b 的多个值中,无法按 c 定位,仅可 ICP 过滤
常见误区
- 误区:把 c 加到索引里就能让
c=3走定位。正解:截断由 b 的范围条件造成,加列不能恢复定位能力。 - 误区:范围截断后该索引对 c 完全无效。正解:c 可作为覆盖列或 ICP 过滤条件,只是不能用于索引定位。
追问
- 问:如何让多列都能定位?答:调整查询条件形态,或为不同过滤模式分别建索引;不能指望一个联合索引覆盖所有组合。
- 问:
BETWEEN也算范围截断吗?答:是,区间条件同样属于范围,其后列被截断。
05.1.1.2-007 WHERE a=? AND b=? 与 WHERE b=? AND a=? 有区别吗?
频率 ★★★☆☆ | 难度 基础
一句话结论
没有区别:优化器会自动重排 WHERE 条件,索引命中取决于列与索引前缀的匹配,与条件书写顺序无关。
概念解释
- 条件重排:优化器基于索引与代价重新组织谓词顺序。
- 书写顺序:SQL 文本中列的先后不影响逻辑等价性。
- 真正决定命中:索引列顺序与谓词类型(等值/范围)。
原理流程
- 解析 SQL 得到谓词集合。
- 优化器按可用索引对齐前缀,忽略书写顺序。
- 选择代价最低的访问路径。
代码 / 命令
WHERE a = 1 AND b = 2; -- 等价于
WHERE b = 2 AND a = 1; -- 同一谓词集合
常见误区
- 误区:按索引顺序写条件才能走索引。正解:条件顺序由优化器重排,写反不影响命中。
- 误区:
ON关联条件顺序也可随意调换。正解:关联字段与索引设计相关,但关联表的驱动顺序由优化器按代价决定。
追问
- 问:什么时候书写顺序会影响执行?答:一般不影响;存储过程或触发器中的语句顺序影响加锁与执行流程,属于另一回事。
- 问:重排后谓词语义会变吗?答:等值与
AND组合满足交换律,语义不变;OR与非确定性函数需单独分析。
05.1.1.2-008 (a, b) 与 (b, a) 是同一个索引吗?
频率 ★★★☆☆ | 难度 基础
一句话结论
不是同一个索引:两者最左列不同,能服务的查询前缀不同;(a,b) 可支持 a、a,b,(b,a) 可支持 b、b,a,不能互相替代。
概念解释
- 前缀差异:首列不同,索引树的排序起点不同。
- 复用边界:
(a,b)不能服务只按b的查询。 - 空间成本:两个方向的联合索引是两棵独立 B+ 树。
原理流程
- 分别检查查询首列匹配哪个索引前缀。
- 只按
b查询时走(b,a);只按a走(a,b)。 - 两类查询都高频时,才考虑建两个方向,否则评估是否冗余。
代码 / 命令
INDEX idx_ab (a, b);
INDEX idx_ba (b, a);
-- 前者服务:a=? / a=? AND b=?
-- 后者服务:b=? / b=? AND a=?
常见误区
- 误区:建了
(a,b)就不用再考虑b单独查询。正解:缺最左列时b查询走不到该索引。 - 误区:两个索引列相同就是重复索引。正解:顺序不同不是重复索引,能否保留取决于对应查询是否真实存在。
追问
- 问:什么情况下需要两个方向都建?答:
a与b各自作为高频首列过滤时;否则优先合并到一个公共前缀索引。 - 问:如何判断两个索引是否冗余?答:检查列集合与顺序、唯一性语义和实际使用情况,详见 05.1.1.5。
05.1.1.2-009 联合索引如何服务 ORDER BY?
频率 ★★★★☆ | 难度 进阶
一句话结论
当 ORDER BY 列顺序与索引列顺序一致、方向匹配,且 WHERE 前缀为等值条件时,可直接利用索引有序性输出,避免 filesort。
概念解释
- 顺序匹配:排序列与索引列前后顺序一致。
- 方向一致:所有排序列升序或降序方向匹配(降序索引除外)。
- 前缀等值:排序之前的索引列在
WHERE中为等值条件。
原理流程
WHERE等值前缀收敛到索引中一个小组。- 小组内记录按后续索引列有序。
ORDER BY沿该顺序输出,无需额外排序;不满足条件时触发filesort。
代码 / 命令
-- 索引 (a, b)
SELECT * FROM t WHERE a = 1 ORDER BY b; -- 可用索引排序
SELECT * FROM t WHERE a > 1 ORDER BY b; -- 范围后 b 无序,可能 filesort
SELECT * FROM t ORDER BY b, a; -- 顺序不符,filesort
常见误区
- 误区:索引包含排序列就一定免排序。正解:还需前缀等值、顺序与方向同时匹配。
- 误区:
ORDER BY命中索引就不会回表。正解:免排序只解决排序环节,查询列不在索引中时仍要回表。
追问
- 问:
EXPLAIN怎么看是否用了索引排序?答:Extra不出现Using filesort,且key命中对应索引;计划字段解读见 05.1.3.1。 - 问:
ORDER BY配合LIMIT还要注意什么?答:排序成本与扫描行数相关,深分页下即使免排序也可能扫描大量行,见 05.1.3.3。
05.1.1.2-010 排序列方向不一致时怎么办?
频率 ★★★☆☆ | 难度 进阶
一句话结论
普通 B+ 树叶子按统一方向有序,混合升降序(如 a DESC, b ASC)无法直接利用单一索引;可使用降序索引为各列指定方向,或接受 filesort。
概念解释
- 方向问题:索引叶子是单向有序结构,混合方向意味着两组相反遍历要求。
- 降序索引:MySQL 8.0 起支持列级
DESC索引定义,分别按方向存储。 - filesort 兜底:方向不匹配时由数据库在内存或磁盘排序。
原理流程
- 检查
ORDER BY各列方向与索引方向是否一一对应。 - 一致则沿索引方向输出;不一致则读取后排序。
- 混合方向高频出现时评估降序索引。
代码 / 命令
-- 降序索引(MySQL 8.0;低版本支持情况待确认)
ALTER TABLE t ADD INDEX idx_a_b (a DESC, b ASC);
SELECT * FROM t WHERE a = 1 ORDER BY a DESC, b ASC;
常见误区
- 误区:普通索引可以同时服务正序与倒序。正解:反向遍历叶子链表虽可行,但混合方向组合无法双向同时满足。
- 误区:降序索引就是把索引反过来存。正解:列级方向定义决定叶子项排序方向,与反向遍历不是一回事。
追问
- 问:PG 对降序索引的支持如何?答:支持列级
DESC索引定义,具体优化器行为以当前版本文档为准(待确认)。 - 问:方向不匹配时 filesort 一定很慢吗?答:取决于参与排序的行数,小集合在内存排序成本可控。
05.1.1.2-011 联合索引与 GROUP BY 有什么关系?
频率 ★★★☆☆ | 难度 进阶
一句话结论
GROUP BY 按索引列顺序分组时,可利用索引有序性避免临时表与排序;前缀条件等值、分组列顺序与索引一致时效果最好。
概念解释
- 分组有序:相同分组键在索引中连续,可顺序聚合。
- 临时表标记:计划出现
Using temporary表示需要临时表分组。 - 排序依赖:分组无序时先聚合再排序,成本上升。
原理流程
- 检查
GROUP BY列是否与索引前缀顺序一致。 - 一致则顺序扫描索引,连续项归入同一组。
- 不一致则建临时表聚合,必要时再排序。
代码 / 命令
-- 索引 (tenant_id, status)
SELECT status, COUNT(*) FROM orders
WHERE tenant_id = 1 GROUP BY status; -- 可利用索引有序分组
常见误区
- 误区:建了索引
GROUP BY就一定免临时表。正解:还需前缀等值与列顺序匹配,聚合函数与HAVING不改变这一前提。 - 误区:
GROUP BY与ORDER BY要求完全相同。正解:分组只要求同组连续,排序要求全局有序,两者利用索引的条件略有差异。
追问
- 问:计划里
Using temporary常见于什么场景?答:分组/排序列与索引顺序不符,或聚合涉及多表连接后的结果集。 - 问:宽分组列适合进索引吗?答:分组列通常是过滤维度列,宽度可控时适合;大字段分组本身就应先评估业务合理性。
05.1.1.2-012 低区分度列放前面会怎样?
频率 ★★★☆☆ | 难度 进阶
一句话结论
低区分度列做前缀时,单个前缀值对应大量记录,索引过滤力度弱;但它若高频作为等值条件且后续列能继续过滤,前置仍可能合理,需结合整体查询评估。
概念解释
- 区分度:列不同值数量占总行数比例,越接近 1 区分度越高。
- 前缀选择性:首列先把记录分成几组,组内再靠后续列收敛。
- 倾斜影响:某个值占绝大多数时,该前缀几乎不过滤。
原理流程
- 评估首列等值能收敛到多少行。
- 区分度低时观察后续列能否在组内继续过滤。
- 若整组记录都需扫描,考虑调整列顺序或更换索引策略。
代码 / 命令
-- status 只有 3 个值,区分度低
-- 若高频条件是 status = ? AND tenant_id = ?,需结合二者区分度与查询频率排顺序
ALTER TABLE orders ADD INDEX idx_status_tenant (status, tenant_id);
常见误区
- 误区:低区分度列绝对不能进索引。正解:作为后续过滤列或与高区分度列组合仍有价值,问题在于把它单独当首列。
- 误区:区分度数字可以脱离查询场景判断。正解:选择性要结合命中后的回表成本与后续列过滤效果一起看。
追问
- 问:性别、状态这类列单独建索引值得吗?答:通常不值得,除非查询占比极端且数据量小;更适合并入联合索引前缀。
- 问:怎么量化某列过滤力度?答:估算等值条件命中行数占比,结合执行计划
rows与filtered判断,详见 05.1.3.1。
05.1.1.2-013 多个查询如何共享一个联合索引?
频率 ★★★★☆ | 难度 进阶
一句话结论
从多条高频查询中提取公共过滤前缀,把公共等值列放在最前,差异列依次向后;一个联合索引可服务前缀逐渐变短的一组查询,但要控制总宽度与写入成本。
概念解释
- 公共前缀:多条查询都作为等值条件的列。
- 逐步退化:更长索引可服务短前缀查询,反之不行。
- 覆盖复用:展示列纳入索引后可同时服务覆盖查询。
原理流程
- 汇总高频 SQL 的过滤列、排序列与返回列。
- 找公共等值前缀,按区分度排列,差异列后置。
- 用各查询计划验证前缀命中,剔除无人使用的尾部列。
代码 / 命令
查询 A:tenant_id = ? AND status = ?
查询 B:tenant_id = ? AND status = ? AND create_time >= ?
共享索引:(tenant_id, status, create_time)
A 命中前两列;B 命中三列
常见误区
- 误区:为每条查询单独建一个索引最稳妥。正解:索引过多放大写入与空间,公共前缀复用更经济。
- 误区:尾部列一定会被用到。正解:需按真实 SQL 验证;长期未被命中的尾部列应评估移除。
追问
- 问:共享索引与多个单列索引相比如何?答:联合索引在多条件过滤时定位更连续;单列组合依赖索引合并,通常代价更高。
- 问:怎么判断共享索引被哪些查询使用?答:结合慢日志、执行计划与索引使用统计综合分析。
05.1.1.2-014 前缀索引列可以被后续列复用吗?
频率 ★★★☆☆ | 难度 进阶
一句话结论
前缀索引只保存列的前 N 个字符,后续联合列基于完整值排序时无法衔接;前缀索引不能参与覆盖,也不能保证后续列的有序衔接。
概念解释
- 截断存储:索引项只含前缀字符,完整值需回表获取。
- 衔接限制:前导列不完整,后续列的组合排序无法建立在前缀之上。
- 适用边界:前缀索引主要服务前缀匹配查询,不做排序与覆盖。
原理流程
- 长字符串列取前 N 字符建前缀索引。
LIKE 'abc%'可按前缀定位。- 需要完整值或排序时回表,联合后续列的有序性不可依赖。
代码 / 命令
-- 前缀索引:只索引 email 前 10 字符
ALTER TABLE users ADD INDEX idx_email_prefix (email(10));
-- 不能:依赖完整 email 排序或做覆盖索引
常见误区
- 误区:前缀索引可以和普通联合列一样组合。正解:前缀截断使前导列不完整,后续列有序性无法保证。
- 误区:前缀越长越好。正解:过长则体积接近完整索引,失去前缀索引的空间意义;长度按选择性曲线选择。
追问
- 问:前缀长度怎么选?答:对比不同长度下的选择性,取接近完整列选择性的最短长度,详见 05.1.1.4。
- 问:表达式索引能否替代前缀索引?答:可按生成表达式建索引,PG 与 MySQL 8.0 支持程度不同,版本边界待确认。
05.1.1.2-015 联合索引的基数(Cardinality)怎么理解?
频率 ★★★☆☆ | 难度 进阶
一句话结论
联合索引基数是前缀组合的不同值数量,按列前缀递增理解:单列基数、两列组合基数、三列组合基数依次扩大;该值是采样估算,用于优化器判断过滤力度。
概念解释
- 前缀基数:
(a,b,c)中a的基数、(a,b)组合基数、(a,b,c)组合基数分别统计。 - 采样估算:InnoDB 通过采样页估算,不是精确计数。
- 选择性关联:组合基数越高,前缀过滤越有力。
原理流程
- 采样索引页,统计各前缀组合的不同值数量。
- 优化器按前缀基数估算条件命中行数。
- 数据大幅变化后需更新统计,避免估算偏差。
代码 / 命令
-- MySQL:查看索引基数(估算值)
SHOW INDEX FROM orders;
-- 更新统计信息
ANALYZE TABLE orders;
常见误区
- 误区:联合索引基数等于首列基数。正解:它是各级前缀组合的不同值数,越靠后组合基数通常越大。
- 误区:基数是精确实时值。正解:采样估算在大量增删后可能过期,需结合
ANALYZE与计划偏差复核。
追问
- 问:基数低说明什么?答:前缀区分度差,等值过滤命中行数多,索引可能不被优先选择。
- 问:统计过期会有什么后果?答:优化器低估或高估过滤力度,可能选错索引,详见 05.1.3.4。
05.1.1.2-016 降序索引解决什么问题?
频率 ★★★☆☆ | 难度 进阶
一句话结论
降序索引允许为索引列分别指定排序方向,解决 ORDER BY 混合升降序无法匹配普通索引的问题;单列倒序遍历本身可由叶子链表反向完成,混合方向才需要降序索引。
概念解释
- 普通索引方向:叶子按统一升序或降序组织。
- 列级方向:降序索引在定义中为每列标注
ASC/DESC。 - 混合排序:
a DESC, b ASC这类组合无法由单一普通索引满足。
原理流程
- 识别
ORDER BY各列方向组合。 - 全部一致时普通索引可服务;混合方向时评估降序索引。
- 建索引时按查询方向标注列级方向。
代码 / 命令
-- MySQL 8.0 降序索引
ALTER TABLE t ADD INDEX idx_desc (a DESC, b ASC);
SELECT * FROM t WHERE a = 1 ORDER BY a DESC, b ASC;
常见误区
- 误区:不用降序索引就无法倒序查询。正解:单列倒序可反向遍历链表;问题只出现在多列混合方向。
- 误区:降序索引会存两份普通索引。正解:它是按指定方向组织的一棵独立索引树,存储与维护成本与普通索引相当。
追问
- 问:降序索引在哪些版本可用?答:MySQL 8.0 支持,早期版本与其他引擎支持情况以版本文档为准(待确认)。
- 问:混合方向排序高频但数据量小怎么办?答:可先接受 filesort,用内存排序成本与索引维护成本对比后再决定。
05.1.1.2-017 联合索引覆盖 JOIN 时怎么设计?
频率 ★★★☆☆ | 难度 进阶
一句话结论
JOIN 场景下,被驱动表的连接列应有索引;若过滤列、连接列与返回列能组成联合索引,可进一步避免回表,降低嵌套循环中的内层查找次数。
概念解释
- 连接列索引:被驱动表
ON条件列是内层查找入口。 - 覆盖 JOIN:查询所需列都在索引中,内层查找不回表。
- 驱动顺序:优化器按结果集大小与索引代价选择驱动表。
原理流程
- 确定连接关系与各表过滤条件。
- 为被驱动表连接列建索引,必要时加入过滤列与返回列。
- 验证计划中内层访问类型是否为
ref/eq_ref,回表次数是否可控。
代码 / 命令
-- t1 驱动,t2 被驱动;t2 连接列 t1_id 需有索引
SELECT t1.id, t2.name
FROM t1 JOIN t2 ON t1.id = t2.t1_id
WHERE t1.status = 1;
-- t2 上可建:(t1_id, name) 覆盖连接与返回列
ALTER TABLE t2 ADD INDEX idx_t1id_name (t1_id, name);
常见误区
- 误区:只要驱动表有索引 JOIN 就快。正解:嵌套循环中被驱动表每行查找一次,连接列索引才是关键。
- 误区:覆盖索引能消除 JOIN 本身的代价。正解:覆盖只减少回表,连接算法与驱动顺序仍由优化器决定。
追问
- 问:被驱动表连接列无索引会怎样?答:每次内层查找退化为全表扫描(BNL/Hash Join 兜底),大表连接性能急剧下降。
- 问:JOIN 索引与单表查询索引冲突时怎么取舍?答:按 QPS 与计划代价综合评估,必要时分别建索引并监控冗余。
05.1.1.2-018 联合索引设计常见反例有哪些?
频率 ★★★★☆ | 难度 进阶
一句话结论
常见反例包括:范围列前置、低区分度列单独前置、把大字段纳入索引、多列堆叠但尾部无人使用、为每条 SQL 重复建近似索引;评审时按查询前缀与宽度逐项检查。
概念解释
- 顺序反例:范围列放在等值列之前,截断后续定位。
- 宽度反例:宽列、大文本进索引,扇出下降。
- 冗余反例:近似索引重复建设,写入放大。
- 失配反例:索引列与真实查询条件对不上。
原理流程
- 列出索引各列的角色(等值/范围/排序/覆盖)。
- 检查顺序、宽度与重复度。
- 对照真实 SQL 与使用统计,淘汰无人命中的索引。
代码 / 命令
-- 反例 1:范围列前置
INDEX (create_time, status) -- WHERE create_time >= ? AND status = ?,status 截断
-- 反例 2:宽列进索引
INDEX (title, content) -- content 为 TEXT,索引体积过大
-- 反例 3:尾部列无人使用
INDEX (a, b, c) -- 无任何查询用到 c
常见误区
- 误区:反例索引只要存在就有收益。正解:错误顺序或过宽的索引可能比不建更差,徒增写入成本。
- 误区:上线后不用再复查索引。正解:查询模式与数据分布会变化,需定期评审清理,详见 05.1.1.5。
追问
- 问:评审时最先检查哪一项?答:先看最左前缀是否匹配高频查询,再看列顺序与宽度。
- 问:怎么发现尾部无人使用的列?答:结合计划使用的索引前缀与性能_schema 索引统计,长期未覆盖的尾部列评估移除。
参考来源
- MySQL 8.0 Reference Manual: Multiple-Column Indexes:https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html
- MySQL 8.0 Reference Manual: Order by Optimization:https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
- PostgreSQL Documentation: Indexes on Expressions:https://www.postgresql.org/docs/current/indexes-expressional.html
- JavaGuide:MySQL 索引:https://javaguide.cn/database/mysql/mysql-index.html