mysql组合索引失效的情况 mysql 组合索引最左匹配原因

转载

lingyuli 2023-08-07 10:30:00

文章标签 mysql组合索引失效的情况 mysql 联合索引字段左匹配 文章分类 MySQL 数据库

什么是最左匹配原则

就是已最左边为起点，任何连续的索引都能匹配上，但是当遇到范围查询的时候（ >、<、between、like）之后的索引就会失效。

什么是联合索引

对多个字段同时建立的索引(有顺序，ABC，ACB是完全不同的两种联合索引。)

为什么要使用联合索引

以联合索引(a,b,c)为例
建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。

最左匹配原理

最左匹配原则都是针对联合索引来说的，所以我们有必要了解一下联合索引的原理。了解了联合索引，那么为什么会有最左匹配原则这种说法也就理解了。
我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的

mysql组合索引失效的情况 mysql 组合索引最左匹配原因_mysql组合索引失效的情况

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。
同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。