联合索引设计：最左前缀、选择性、覆盖索引与落地方法

目标：你能把“联合索引怎么建”讲成一套可执行的方法，而不是背一句“where 用得多的放前面”。

1. 联合索引的本质：一棵按字典序排序的 B+ 树

联合索引(a, b, c)不是三棵树，而是一棵树，key 的排序是：

• 先按a排
• a相同时按b
• a,b相同时按c

因此它能高效支持的访问模式必须遵循这种有序性。

2. 最左前缀：不是“必须写 a”，而是“必须先确定 a 的范围”

对(a,b,c)：

• a = ?✅
• a = ? and b = ?✅
• a = ? and b > ?✅（b成为范围后，c通常无法继续用于定位）
• b = ?❌（缺少对a的约束）

2.1 一个关键面试点：范围条件会“截断”后续列的定位能力

例子：

• where a = 1 and b > 10 and c = 3

通常：

• 能用到a和b做范围定位
• c=3只能在扫描结果里再过滤（不再用于继续缩小范围）

3. 选择性（selectivity）：决定索引能否真正减少扫描

选择性直觉：

• 选择性高：条件能过滤掉大量行，索引价值大
• 选择性低：比如性别/状态枚举，走索引可能仍扫很多行

经验：

• 不要把“低选择性列”盲目放在联合索引第一位
• 但如果它能配合order by或覆盖索引，仍可能有价值

4. 设计方法论：从“查询集合”反推索引

你不应该为表建索引，而应该为查询建索引。

4.1 先收集 Top 查询

• 慢日志/监控：Top N SQL
• 业务读路径：列表、详情、搜索

4.2 对每条查询拆成 3 段

• 过滤（where）：决定扫描范围
• 排序/分组（order/group）：决定是否 filesort/temporary
• 返回列（select）：决定是否回表

4.3 索引列顺序的一般原则（可解释版本）

按优先级排列：

1. 等值过滤列（=/in）
2. 范围列（>/between/like 'xxx%'）
3. 排序列（为了避免 filesort）
4. 返回列（为了覆盖索引，减少回表）

但要结合选择性：等值列里优先放选择性更高、能显著缩小范围的列。

4.4 一个可复现的最小例子：用“列表页”推导联合索引顺序

先准备一张常见业务表（订单/流水类），你可以把它当成所有索引推导的“练习模板”。

createtablet_order(idbigintprimarykey,user_idbigintnotnull,statustinyintnotnull,create_timedatetimenotnull,amountintnotnull,titlevarchar(64)notnull);

需求 1：某用户的订单列表，按时间倒序分页（典型读路径）。

selectid,title,create_timefromt_orderwhereuser_id=?orderbycreate_timedesc,iddesclimit20;

索引推导：

• where 的等值过滤是user_id
• order by 的顺序是(create_time, id)
• 返回列是id/title/create_time，希望覆盖索引减少回表

因此索引可设计为：

createindexidx_user_timeont_order(user_id,create_time,id,title);

验证目标（用 EXPLAIN 观察）：

• key命中idx_user_time
• Extra尽量出现Using index（覆盖）
• 尽量不要出现Using filesort

需求 2：同样是列表，但只筛选状态（选择性低）

selectid,create_timefromt_orderwherestatus=1orderbycreate_timedesclimit20;

如果status取值很少（比如 0/1/2），它的选择性往往低。

你可能会面临两种策略：

• 更偏“过滤”：把更高选择性的列放前面
• 更偏“排序”：为了避免 filesort 把排序列放进联合索引

这也是索引设计为什么必须回到“真实数据分布 + 真实 SQL”验证，而不是背口诀。

4.5 对照组：同一条查询，索引列顺序错了会发生什么

仍以需求 1 为例。

对照 1：把排序列放前面（通常不如把等值列放前面）

-- 不推荐：create_time 在前，user_id 在后createindexidx_time_useront_order(create_time,user_id);

现象直觉：

• 你的 where 是user_id = ?，但索引首先按 create_time 排
• 对单个 user 的定位能力弱，可能扫描更多行

对照 2：把低选择性列放第一位

-- status 很可能低选择性createindexidx_status_user_timeont_order(status,user_id,create_time);

现象直觉：

• status=1 可能命中大量行，rows 仍然很大
• 即使走索引，也可能变成“扫很多再过滤”

8. 线上验证：一套闭环

5. 覆盖索引：让二级索引“直接产出结果”

覆盖索引概念：

• 查询需要的列都在索引叶子里，Extra出现Using index

收益：

• 减少回表（随机 IO）
• 对范围查询、分页查询尤为关键

5.1 覆盖索引的典型用法：列表页

列表页通常只需要：

• id
• 标题
• 时间

就可以设计：

• (user_id, create_time, id, title)

从而：

• where 用user_id
• order by 用create_time
• select 列全部覆盖

6. 典型场景拆解

6.1 查询：用户维度列表 + 按时间倒序分页

selectid,title,create_timefromtwhereuser_id=?orderbycreate_timedesclimit20;

索引建议：

• (user_id, create_time, id, title)

理由：

• user_id等值定位
• create_time用于有序扫描避免 filesort
• id/title覆盖避免回表

6.2 查询：状态筛选 + 时间范围 + 排序

状态列选择性低，但如果查询频繁：

• (status, create_time, id)

关键看：

• rows是否显著下降
• filesort/temporary是否消失

7. 常见坑

• 过度索引：
  • 索引多会拖慢写入（每次 insert/update 都要维护多棵 B+ 树）
• “为了覆盖索引把列全塞进去”：
  • 索引太宽导致页能放的 key 变少，树变高，适得其反
• in列表过长：
  • 优化器可能改计划，且可能产生大量随机回表

8. 线上验证：一套闭环

• EXPLAIN对比：
  • rows是否下降
  • Extra是否从 filesort/temporary 变为 Using index
• 用真实参数采样：
  • 避免“测试参数很干净，线上参数很脏”

8.1 一个更流程化的 checklist（建议照这个顺序执行）

1. 明确目标 SQL（只讨论 1 条查询 + 1 组参数）
2. 确认查询分解
   • where：等值/范围
   • order/group：是否需要有序输出
   • select：是否必须回表
3. 设计候选联合索引顺序
   • 等值列优先（结合选择性）
   • 范围列其次（范围会截断后续定位）
   • 排序列为了消 filesort
   • 返回列为覆盖索引（控制索引宽度）
4. EXPLAIN 验证
   • rows是否显著下降
   • Extra是否消除Using filesort/temporary
   • 是否出现Using index
5. 用线上真实参数再验证一次（避免数据分布差异导致误判）

9. 面试背诵稿（60 秒）

联合索引本质是一棵按(a,b,c)字典序排序的 B+ 树，所以必须遵循最左前缀：先约束 a，再才能利用 b、c；一旦遇到范围条件会截断后续列的定位能力。
索引设计我会从查询出发，把 SQL 拆成 where 过滤、order/group 排序聚合、select 返回列三段，然后按“等值列优先、范围列其次、再考虑排序、最后考虑覆盖索引减少回表”的思路确定列顺序，并结合选择性验证是否真的减少扫描行数。最终用 EXPLAIN 看 rows、Extra（filesort/temporary/Using index）闭环验证。