罗罗编程学习记录
本页内容 展开查看

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+ 树只能从最左列开始逐列定位,这是最左前缀规则的结构根源。

概念解释

  • 字典序排列:多列组合值按列顺序比较,类似字符串字典序。
  • 列顺序固定:索引一旦建成,列的前后顺序不可动态调整。
  • 前缀有序:只有前导列确定后,后续列的有序性才有意义。

原理流程

  1. 叶子项按 (a, b, c) 组合值排序。
  2. 查询先在 a 上定位区间,a 相同的项内部再按 b 有序。
  3. 缺少前导列时,后续列的值分散在整棵树中,无法连续定位。

代码 / 命令

索引 (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 在索引层过滤,但不改变定位路径。

原理流程

  1. 取出 WHERE 条件中的索引列,按索引顺序对齐。
  2. 连续等值/范围条件用于定位,遇到断裂列即停止定位。
  3. 未参与定位的列若在索引中,可由索引下推在回表前过滤。

代码 / 命令

-- 索引 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' 无前缀起点。

原理流程

  1. 检查条件列与索引列的对齐关系。
  2. 逐列判断是否能以索引原值做等值/范围比较。
  3. 任一前导列失效,后续列的有序性都无法利用。

代码 / 命令

-- 索引 (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 列表列纳入索引可形成覆盖,但需评估宽度成本。

原理流程

  1. 列出高频查询的过滤条件、排序方向与返回列。
  2. 把等值列按区分度排列,范围列置于等值之后。
  3. 排序列按索引顺序接续,必要时纳入展示列做覆盖,控制总宽度。

代码 / 命令

-- 高频查询
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+ 树中不再连续有序。

原理流程

  1. 先把所有等值条件列排入索引前缀。
  2. 范围列放在等值列之后。
  3. 范围列之后的列只能用于过滤或覆盖,不能参与定位。

代码 / 命令

-- 推荐:(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 范围条件为什么会截断后续列?

频率 ★★★★☆ | 难度 进阶

一句话结论

联合索引中前导列等值时后续列才有有序性;前导列进入范围后,后续列的值分散在多个区间内不再连续,因此范围列之后的索引列无法用于定位。

概念解释

  • 有序前提:只有前导列确定为同一值,组内后续列才按键有序。
  • 范围展开:前导列覆盖多个值时,每个值组内后续列各自有序,整体不连续。
  • 截断位置:从第一个范围条件列开始,其后列失去定位价值。

原理流程

  1. 等值前缀把记录收敛到一个小组。
  2. 范围条件在小组内圈定区间,但小组内后续列的顺序不再跨区间连续。
  3. 后续列条件只能逐行过滤,不能再缩小索引区间。

代码 / 命令

-- 索引 (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 文本中列的先后不影响逻辑等价性。
  • 真正决定命中:索引列顺序与谓词类型(等值/范围)。

原理流程

  1. 解析 SQL 得到谓词集合。
  2. 优化器按可用索引对齐前缀,忽略书写顺序。
  3. 选择代价最低的访问路径。

代码 / 命令

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+ 树。

原理流程

  1. 分别检查查询首列匹配哪个索引前缀。
  2. 只按 b 查询时走 (b,a);只按 a 走 (a,b)。
  3. 两类查询都高频时,才考虑建两个方向,否则评估是否冗余。

代码 / 命令

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 中为等值条件。

原理流程

  1. WHERE 等值前缀收敛到索引中一个小组。
  2. 小组内记录按后续索引列有序。
  3. 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 兜底:方向不匹配时由数据库在内存或磁盘排序。

原理流程

  1. 检查 ORDER BY 各列方向与索引方向是否一一对应。
  2. 一致则沿索引方向输出;不一致则读取后排序。
  3. 混合方向高频出现时评估降序索引。

代码 / 命令

-- 降序索引(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 表示需要临时表分组。
  • 排序依赖:分组无序时先聚合再排序,成本上升。

原理流程

  1. 检查 GROUP BY 列是否与索引前缀顺序一致。
  2. 一致则顺序扫描索引,连续项归入同一组。
  3. 不一致则建临时表聚合,必要时再排序。

代码 / 命令

-- 索引 (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 区分度越高。
  • 前缀选择性:首列先把记录分成几组,组内再靠后续列收敛。
  • 倾斜影响:某个值占绝大多数时,该前缀几乎不过滤。

原理流程

  1. 评估首列等值能收敛到多少行。
  2. 区分度低时观察后续列能否在组内继续过滤。
  3. 若整组记录都需扫描,考虑调整列顺序或更换索引策略。

代码 / 命令

-- 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 多个查询如何共享一个联合索引?

频率 ★★★★☆ | 难度 进阶

一句话结论

从多条高频查询中提取公共过滤前缀,把公共等值列放在最前,差异列依次向后;一个联合索引可服务前缀逐渐变短的一组查询,但要控制总宽度与写入成本。

概念解释

  • 公共前缀:多条查询都作为等值条件的列。
  • 逐步退化:更长索引可服务短前缀查询,反之不行。
  • 覆盖复用:展示列纳入索引后可同时服务覆盖查询。

原理流程

  1. 汇总高频 SQL 的过滤列、排序列与返回列。
  2. 找公共等值前缀,按区分度排列,差异列后置。
  3. 用各查询计划验证前缀命中,剔除无人使用的尾部列。

代码 / 命令

查询 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 个字符,后续联合列基于完整值排序时无法衔接;前缀索引不能参与覆盖,也不能保证后续列的有序衔接。

概念解释

  • 截断存储:索引项只含前缀字符,完整值需回表获取。
  • 衔接限制:前导列不完整,后续列的组合排序无法建立在前缀之上。
  • 适用边界:前缀索引主要服务前缀匹配查询,不做排序与覆盖。

原理流程

  1. 长字符串列取前 N 字符建前缀索引。
  2. LIKE 'abc%' 可按前缀定位。
  3. 需要完整值或排序时回表,联合后续列的有序性不可依赖。

代码 / 命令

-- 前缀索引:只索引 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 通过采样页估算,不是精确计数。
  • 选择性关联:组合基数越高,前缀过滤越有力。

原理流程

  1. 采样索引页,统计各前缀组合的不同值数量。
  2. 优化器按前缀基数估算条件命中行数。
  3. 数据大幅变化后需更新统计,避免估算偏差。

代码 / 命令

-- 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 这类组合无法由单一普通索引满足。

原理流程

  1. 识别 ORDER BY 各列方向组合。
  2. 全部一致时普通索引可服务;混合方向时评估降序索引。
  3. 建索引时按查询方向标注列级方向。

代码 / 命令

-- 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:查询所需列都在索引中,内层查找不回表。
  • 驱动顺序:优化器按结果集大小与索引代价选择驱动表。

原理流程

  1. 确定连接关系与各表过滤条件。
  2. 为被驱动表连接列建索引,必要时加入过滤列与返回列。
  3. 验证计划中内层访问类型是否为 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 重复建近似索引;评审时按查询前缀与宽度逐项检查。

概念解释

  • 顺序反例:范围列放在等值列之前,截断后续定位。
  • 宽度反例:宽列、大文本进索引,扇出下降。
  • 冗余反例:近似索引重复建设,写入放大。
  • 失配反例:索引列与真实查询条件对不上。

原理流程

  1. 列出索引各列的角色(等值/范围/排序/覆盖)。
  2. 检查顺序、宽度与重复度。
  3. 对照真实 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 索引统计,长期未覆盖的尾部列评估移除。

参考来源