最左前缀原则:联合索引为什么挑查询写法
一、联合索引存的是组合顺序
以包含甲、乙、丙三列的联合索引为例,记录首先按甲列排序;甲列相同时再按乙列排序;甲、乙都相同时再按丙列排序。它并不是三棵互不相干的单列索引,而是一棵按组合键组织的 B+ 树。只有先确定前面列的范围,后面列的有序性才会在相应分组内体现。
这与电话簿先按姓氏、再按名字排列相似。已知姓氏时,可以继续利用名字缩小范围;只知道名字而不知道姓氏时,名字在整本电话簿中并不全局有序,通常无法直接定位到一小段连续目录。
二、最左匹配不等于条件书写位置
最左前缀中的“左”指索引定义时的列顺序,不是查询条件在语句中的书写先后。对于全部由等值条件组成的查询,即使条件排列与索引列顺序不同,优化器通常仍能识别出相同的键值组合,并使用联合索引进行定位。调整条件文本顺序本身不会创造或破坏索引结构。
真正关键的是条件是否提供连续的前缀。只有甲列等值,可以利用甲列;同时提供甲、乙等值,可以继续利用乙列;只有乙列而没有甲列时,通常不能把这棵组合树当作乙列独立索引使用。能够利用多少列,应按索引键的有序性判断,而不是数查询中出现了几个字段。
三、范围条件为何改变后续列的作用
当甲列使用等值、乙列使用范围条件时,索引可以先定位甲列分组,再找到乙列区间起点并连续扫描。此时丙列在乙列取值不同的记录之间不再保持全局有序,因此通常不能继续用于缩小扫描边界。不过,丙列条件仍可能在扫描过程中参与过滤,索引下推也可能减少不必要的回表。
“范围之后完全无用”因而不够准确。更严谨的说法是,范围条件之后的列通常不能继续按照联合键精确定位,但仍可能用于条件判断。至于多值等值、排序要求和具体访问方式如何影响利用程度,需要结合执行计划分析,不能只用一句口诀覆盖所有情况。
四、排序与分组也依赖键的顺序
联合索引不仅用于筛选,还可能为排序和分组提供现成顺序。查询若按甲列等值限定,再按乙、丙排序,结果可能已经符合索引顺序,从而避免额外排序。若缺少甲列限定,却直接要求按乙列排序,索引整体顺序通常无法满足要求,因为不同甲列分组的乙列值会交错出现。
排序方向、前后排序列是否连续,以及是否混入未在索引中的列,也会影响能否复用该顺序。执行计划中的 filesort 表示需要额外排序步骤,并不必然意味着磁盘排序。联合索引的列顺序因此需要同时服务过滤与结果组织,而不能只比较单列区分度。
五、用典型查询反推列顺序
面向“某个用户的某段时间记录”,用户列等值在前、时间列范围在后,通常能形成清晰的有效区间。面向“某种状态下按时间排序的任务”,状态列在前也可能同时支持过滤和排序。若查询还经常按另一字段筛选,则应检查该字段位于等值前缀还是范围之后,避免仅凭一般经验决定排列。
设计时应把真实查询按频率和重要性归类,分别写出等值条件、范围条件、排序键及返回列,再观察同一索引能否覆盖主要模式。最左前缀不是要求所有查询迁就一条固定语句,而是提醒设计者:联合索引的收益来自组合键的有序结构,列顺序必须与访问方式共同决定
- 点赞
- 收藏
- 关注作者
评论(0)