MySQL索引的最左前缀原则

MySQL索引的最左前缀原则

引言

创建索引有利于提高数据库的查询效率，当我们需要对多个列进行查询时，可以考虑建立一个联合索引。

但是有些时候，当我们对三个列a、b、c建立一个联合索引（a,b,c）后，按理说，列b和c的索引也包含在联合索引(a,b,c)中了，

但是当我们执行sql语句select * from table where b='1' and c='2'时，发现却没有用到索引，查询效率并没有得到提升，

执行sql语句select * from table where a='1' and c='2'时，却用到了索引，查询效率得到了提升。

这是为什么呢？

数据结构

联合索引是通过B+树构建的，对于只在单列上构建的非联合索引，在查询时只需要根据B+树顺序查询即可，

但是对于一个有多个列的联合索引，如果想将多个列的索引都构建在一颗B+树内，也就是通过一个排序方法对多个列进行排序，那么就必须有一个优先级：

表明优先按照哪个列进行排序，当该列值相等时再更具次优先级的列进行排序，以此类推。

联合索引便是这样的，当我们在创建联合索引时，就通过列的顺序指定了列在构建B+树时的优先级：联合索引（a,b,c）越靠左侧，优先级越高，a>b>c。

执行sql语句select * from table where b='1' and c='2'时，因为并没有用到列a，对于以列a为第一优先级构建的B+树来说，没有a，单看b和c都是无序的，自然就无法使用该B+树，所以联合索引(a,b,c)没有生效。

对于：

select * from table where a='1' and b='3' and c='2';
select * from table where a='1' and b='3';
select * from table where a='1' and c='2';

由于都用到了列a，所以可以部分或全部用到联合索引（a,b,c），因为列a在B+树中的顺序不依赖其它列。

特殊情况

优化器

此外像select * from table where c='1' and a='3';的，先用其它列，后用列a的sql语句，按理说也无法用到联合索引（a,b,c），

但是，MySQL在执行最终sql语句之前，会通过优化器进行sql优化，

将select * from table where c='1' and a='3';优化为可以使用联合索引的sql语句！

范围值

对于范围查询>、<、between，

例如select * from table where a='1' and b>100 and c='2'，a='1’可以用到索引，b>100也可以用到索引，但是b>100查出的是一个范围值，由于只有在b相等时，c才有序，所以一个范围值b，对于c的有序性是没有意义的，c就无法用到索引了。

当遇到>、<、between时，该条件列还可以使用索引，右边的列就无法使用索引了！

模糊查询

对于like模糊匹配，

例如select * from table where a='1' and b like '李%' and c='2'，对于like ‘李%’ 这样的前缀匹配，与上面的范围值是同样的道理，该条件列还可以使用索引，右边的列就无法使用索引了！

但是对于like ‘%李’ 这样的后缀匹配，因为 ‘李’ 前面可以有任意值，就没有顺序的意义了，该条件列及右边的列都无法使用索引了！

总结

由于联合索引的B+树是根据列顺序来先后在原基础上构建的，只有前列相等时后列才有序，所以在使用时，也必须要保证前列所查出的结果为单一值，后列才能使用索引。