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

目标你能把“联合索引怎么建”讲成一套可执行的方法而不是背一句“where 用得多的放前面”。1. 联合索引的本质一棵按字典序排序的 B 树联合索引(a, b, c)不是三棵树而是一棵树key 的排序是先按a排a相同时按ba,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做范围定位c3只能在扫描结果里再过滤不再用于继续缩小范围3. 选择性selectivity决定索引能否真正减少扫描选择性直觉选择性高条件能过滤掉大量行索引价值大选择性低比如性别/状态枚举走索引可能仍扫很多行经验不要把“低选择性列”盲目放在联合索引第一位但如果它能配合order by或覆盖索引仍可能有价值4. 设计方法论从“查询集合”反推索引你不应该为表建索引而应该为查询建索引。4.1 先收集 Top 查询慢日志/监控Top N SQL业务读路径列表、详情、搜索4.2 对每条查询拆成 3 段过滤where决定扫描范围排序/分组order/group决定是否 filesort/temporary返回列select决定是否回表4.3 索引列顺序的一般原则可解释版本按优先级排列等值过滤列/in范围列/between/like xxx%排序列为了避免 filesort返回列为了覆盖索引减少回表但要结合选择性等值列里优先放选择性更高、能显著缩小范围的列。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_idorder by 的顺序是(create_time, id)返回列是id/title/create_time希望覆盖索引减少回表因此索引可设计为createindexidx_user_timeont_order(user_id,create_time,id,title);验证目标用 EXPLAIN 观察key命中idx_user_timeExtra尽量出现Using index覆盖尽量不要出现Using filesort需求 2同样是列表但只筛选状态选择性低selectid,create_timefromt_orderwherestatus1orderbycreate_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);现象直觉status1 可能命中大量行rows 仍然很大即使走索引也可能变成“扫很多再过滤”8. 线上验证一套闭环5. 覆盖索引让二级索引“直接产出结果”覆盖索引概念查询需要的列都在索引叶子里Extra出现Using index收益减少回表随机 IO对范围查询、分页查询尤为关键5.1 覆盖索引的典型用法列表页列表页通常只需要id标题时间就可以设计(user_id, create_time, id, title)从而where 用user_idorder by 用create_timeselect 列全部覆盖6. 典型场景拆解6.1 查询用户维度列表 按时间倒序分页selectid,title,create_timefromtwhereuser_id?orderbycreate_timedesclimit20;索引建议(user_id, create_time, id, title)理由user_id等值定位create_time用于有序扫描避免 filesortid/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建议照这个顺序执行明确目标 SQL只讨论 1 条查询 1 组参数确认查询分解where等值/范围order/group是否需要有序输出select是否必须回表设计候选联合索引顺序等值列优先结合选择性范围列其次范围会截断后续定位排序列为了消 filesort返回列为覆盖索引控制索引宽度EXPLAIN 验证rows是否显著下降Extra是否消除Using filesort/temporary是否出现Using index用线上真实参数再验证一次避免数据分布差异导致误判9. 面试背诵稿60 秒联合索引本质是一棵按(a,b,c)字典序排序的 B 树所以必须遵循最左前缀先约束 a再才能利用 b、c一旦遇到范围条件会截断后续列的定位能力。索引设计我会从查询出发把 SQL 拆成 where 过滤、order/group 排序聚合、select 返回列三段然后按“等值列优先、范围列其次、再考虑排序、最后考虑覆盖索引减少回表”的思路确定列顺序并结合选择性验证是否真的减少扫描行数。最终用 EXPLAIN 看 rows、Extrafilesort/temporary/Using index闭环验证。