Mysql的联合索引最左匹配原则小记

mysql的联合索引，也称为组合索引，指的是建立一个索引的时候选择多个字段。

可能我们都听说过联合索引的最左匹配原则，今天我们就这个主题实战讨论一下。

使用工具：navicat。

创建表：

我不喜欢上传建表语句，那样不直观。

创建联合索引，

ok，准备工作完成，让我们以各种查询姿势来观察下对应的查询计划。

1select * 系列

        select * 系列走普通索引需要回表操作，无法实现索引覆盖。这里顺便讲一下回表和索引覆盖的概念，知道的请忽略下一段。

        在mysql的innodb引擎中，数据是挂在主键的叶子节点下的，而普通索引的叶子节点下面挂的是主键。所以当我们根据普通主键索引查询出主键后，还需要再去主键索引树上查找一遍才能获取到数据，这就是回表。所以我们的select *查询走普通索引的时候，都是需要回表的。那么什么是索引覆盖呢，简单来说就是select语句在一颗索引树上就能获取到所需的数据，不需要再去遍历一遍别的索引树，也就是说我们的select *改成了select xx(xx指的是索引)，或者select * where走的不是普通索引，而是主键。总结来说，索引覆盖就是不需要回表的情况。

ok，接下来上第一条sql语句：

因为我们的索引最左边是name列，所以毫无疑问的使用到了索引。

第二条sql,我们把查询条件改为索引的第二个字段试一试。

这样会不会走索引呢？不会的，因为按照最左匹配原则，mysql在查找联合索引的时候会先比较第一个字段name，但我们的查询条件没有name，mysql就不知道咋办了，只能全文搜索。如下图。

 

第三条语句，

这条语句应该是我们最正常走联合索引的情况，所以毫无疑问的，使用到了索引。

第四条，也是最容易迷惑的一条。

没错，我们把age 和 name 调换一下，看起来不符合最左前缀匹配，还能不能用到索引呢？

答案是肯定的，mysql这点逻辑还是处理的了的。

接下来，

2 select 索引上的字段

第一条，

  正常使用索引的情况，我们看下和select*有什么区别。

        和上面的第三条比起来，我们发现多了个Using index，也就是说只需要在这棵索引树上就可以取到所需数据，不需要回表，也就是实现了索引覆盖。

第二条，

直接上explain，我们看下有什么区别。

        可以看到，除了不需要回表，我们的索引检索长度也由33减小到了28。这个好理解，因为我们只需要查满足name的条件，不需要再去检索age这个条件，自然就变小了。

最后一条，也是非常迷惑的一条，这条要谨记，面试官可能会在这给你挖坑。

我们把where条件改成联合索引的非最左字段。按道理讲这样就不会走索引了。但是请看结果。

看到没，仍然使用到了索引。

为什么呢？很简单，这个索引的使用方式就不是简单的B+树分叉搜索了，而是挨个查找，所以我们的rows检索行数也就从正常走索引的1变成了7（我这里只有七条数据），因为mysql只需要遍历一遍索引树就可以找到数据了。所以这个地方我们一定要注意别被带到坑里去。

总结一下

1.联合索引要使用最左前缀匹配，即最左边的字段会建立单独的索引，我们的sql语句where一定要包含这个字段才能用到索引。

2.走不走索引不知看where子句，还要看select的字段，有可能走索引，而且是非常慢的挨个遍历（这种查询效率依然很慢，大家不要这么做，建议跟第一条一样命中索引）。

3.联合索引的检索过程是先走最左边的字段，然后再去找后面的字段。如索引（a,b,c），先去找a，找到后再去找b或者c.这时我们的查询条件没有b也没关系，因为只要找到了a,剩下的检索量就很小了（除非你的数据中a有大量的重复值，这时候就需要把b也带上了）。