如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始, 并且不跳过索引中的列。如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。
以 tb_user 表为例,我们先来查看一下之前 tb_user 表所创建的索引。
在 tb_user 表中,有一个联合索引,这个联合索引涉及到三个字段,顺序分别为:profession, age,status。
对于最左前缀法则指的是,查询时,最左变的列,也就是profession必须存在,否则索引全部失效。 而且中间不能跳过某一列,否则该列后面的字段索引将失效。 接下来,我们来演示几组案例,看一下 具体的执行计划:
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
接下来根据profession和age两个字段查询,索引也会生效。
并且由 key_len
可以推断出 status
的索引长度为 5
。
explain select * from tb_user where profession = '软件工程' and age = 31;
explain select * from tb_user where profession = '软件工程';
以上的这三组测试中,我们发现只要联合索引最左边的字段 profession存在,索引就会生效,只不 过索引的长度不同。 而且由以上三组测试,我们也可以推测出profession字段索引长度为47、age 字段索引长度为2、status字段索引长度为5。
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';
而通过上面的这两组测试,我们也可以看到索引并未生效,原因是因为不满足最左前缀法则,联合索引最左边的列profession不存在。
explain select * from tb_user where profession = '软件工程' and status = '0';
上述的SQL查询时,存在profession字段,最左边的列是存在的,索引满足最左前缀法则的基本条件。但是查询时,跳过了age这个列,所以后面的列索引是不会使用的,也就是索引部分生效,所以索引的长度就是47。
思考题:
当执行SQL语句: explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';
时,是否满足最左前缀法则,走不走 上述的联合索引,索引长度?
可以看到,是完全满足最左前缀法则的,索引长度54,联合索引是生效的。
注意 : 最左前缀法则中指的最左边的列,是指在查询时,联合索引的最左边的字段(即是第一个字段)必须存在,与我们编写SQL时,条件编写的先后顺序无关。