定义
最左匹配原则是指,在使用联合索引时,如果查询条件是联合索引最左边的,那么可以使用联合索引来进行查询。下面举一些例子来说明:
现有联合索引abc
select * from demo where a=1 and b=2 and c=3; 符合最左前缀匹配原则,可以使用联合索引进行查询
select * from demo where a=1 and c=2 and b=3; 符合;可以使用联合索引(注意,最左匹配并不是指where后的查询条件顺序符合联合索引的顺序。)
select * from demo where a=1 and b=2; 符合;可以使用联合索引
select * from demo where b=1 and c=2; 不符合;不能使用联合索引
select * from demo where a=1 and c=2; 不完全符合;只能用到abc索引中的a,而bc用不到
当条件语句中遇到范围查询时(>、<、between、like)会停止匹配
为什么?
复合索引最左匹配原则是指在使用复合索引进行查询时,索引的第一个列会被用于查找,如果查询条件中包含了范围查询(例如大于、小于、区间等),那么就不会继续向后匹配其他列。
这是因为复合索引的最左匹配原则是基于索引的有序性。索引是按照索引列的顺序进行排序的,因此在进行范围查询时,无法保证后面的列的有序性。如果继续向后匹配其他列,会破坏索引的有序性,导致查询效率下降。
举个例子来说明:
假设有一个复合索引 (A, B, C),查询条件为 A = 1 AND B > 5 AND C < 10。
根据最左匹配原则,首先会使用索引的第一个列 A 进行查找,找到 A = 1 的记录。然后,由于查询条件中有范围查询 B > 5 和 C < 10,无法保证 B 和 C 的有序性,所以就不会继续向后匹配其他列,而是直接返回符合条件的记录。
这样做可以减少不必要的索引扫描和数据访问,提高查询效率。但也需要注意,如果查询条件中没有范围查询,那么可以继续向后匹配其他列,以进一步缩小查询范围,提高查询效率。
例如
select * from demo where a=1 and b>2 and c=3;这种情况,b出现范围查询,停止匹配,只有ab用到了索引而c无法使用索引查询
但是如果将(abc)索引改为(acb)那么上述的sql语句就可以使用(acb)联合索引,因为mysql优化器会自动改为最优的sql语句
改为:
select * from demo where a=1 and c=3 and b>2;
原理
我们知道索引的底层时B+树,当将几条属性联合起来形成索引时,叶子节点的数据部分会根据联合索引的顺序进行排序,比如有(abc)联合索引,数据部分会先按照a的大小进行排序,在a相等时,就按照b排序,b相等时,按照c。B+树的非叶子节点会按照顺序记录下联合索引的值,如下图。
联合索引B+树的结构如下图
当我们最左的索引不知道时,例如(abc)->(*11),按照下图结构去查,因为第一位是*,所以在第一层就无法查询。
联合索引在叶子节点匹配成功之后,通过与数据相关联的主键(图中浅黄色部分)部分去找整条完整的数据。
总结
联合索引的最左匹配原则其实也是因为其B+树的特性而生成的,以上就是联合索引为什么会有最左匹配原则的原因了,上图使用的图灵学院的结构图,有问题的地方欢迎各位大佬指出。