MySQL：联合索引的具体执行过程、索引失效的原因分析

1.前言

        在学习了普通索引的基础上，学习到了联合索引的相关知识，但其具体的执行过程仍然模糊，仅仅是了解了最左匹配原则是不够的，以下是一些深究的问题。
        该文具体介绍了：联合索引的最左匹配原则、联合索引的具体查询流程，联合索引的索引失效原理。

2.联合索引与最左匹配

        联合索引的本质其实是非聚簇索引，即叶子节点中存放的不是聚簇索引那样的数据页，而是主键索引，仍然需要回表。
        联合索引构建的过程，具体如下图所示，联合索引不管联合多少列，都只有一棵B+树，这个B+树是按照创建索引的第一个列为来构建的。

同时以多个列作为排序规则，也就是同时为多个列建立索引。比如，我们想让B+树按照c2列和c3列的大小进行排序，这两包含两层含义：

• 先把各个记录和页按照c2列进行排序；//此处是将c2以b+树进行排列
• 在记录的c2列相同的情况下，再采用c3列进行排序；//注意，是c2相同的情况下，再对c3排序

         

1.1 最左匹配

         耳熟能详的最左匹配原则，其实就概括而言，就是：

         按照哪一个索引构建的B+树，那么在查询的时候就必须要使用到它，而不能单独使用别的索引。

         例如，上图中，可以查询where c2 = 1，或者 where c2 =1 and c3 = 'y'，这两种查询都能使用c2的B+树索引。而如果单独的进行where c3 = 'y'，就没办法使用以c2构建的B+树，索引会失效。

1.2 索引的查询过程

        例如，在查询：具体是如何用到两个索引的呢？

select * from table where c2 = 4 and c3 = 'u';

1. 首先，对于联合索引（c2，c3）而言，首先会根据c2的B+树定位具体的数据页元素：例如先在页33中，判断c2=4，比2大比9小，因此对磁盘的页30进行io，继续查找，发现4的页号为28，再io出28页进行查找。
2. 此时找到满足第一个索引条件的全部数据，即4 o 10和4 u 1，然后根据第二个索引c3的有序性，进行2分查找，最终定位到数据4 u 1。
3. 拿到主键索引1进行回表，最终查到所有数据。

3. 联合索引失效

        从联合索引中，我们可以发现索引能够奏效的一个关键特点：有序性

        c2索引能按照B+树的顺序排列，因此可以奏效。c3索引在c2相同的情况下顺序排列，因此可以使用。而反之，则索引失效！例如：

         “范围条件右边的列索引失效”

         之所以范围条件右边的索引失效，是因为在该索引范围条件筛选后，其下一级的索引变得无序了！

        例如图中以下这部分，如果先取出c2 >=4 and c2 <= 7 ，我们发现其对应的二级索引为：o、u、b、m、i、a，是无序的，无序就无法二分查找，索引就失效了！
        其根本原因是上文中开头的这句话：
        在记录的c2列相同的情况下，再采用c3列进行排序；

 

4. 结尾 

        注意：我们知道数据库的优化器会自动优化语句，例如where c2 =1 and c3 = 'y'，就算反过来写：where c3 =‘y’ and c2 = 1，仍然会使用c2和c3联合索引。
        但与之相反的，对于查询顺序上，却不能够将“范围查询”这种造成索引失效的条件自动地放到最后，因此这需要我们在编写sql语句时就要提前注意。