38.联合索引查询原理以及索引使用规则

联合索引查询原理以及全值匹配规则

之所以讲解联合索引，那是因为平时我们设计系统的时候一般都是设计联合索引，很少用单个字段做索引，原因之前讲过，我们还是要尽可能的让索引数量少一些，避免磁盘占用太多，增删改性能太差。

咱们有一个表是存储学生成绩的，这个表当然有id了，这个id是一个自增主键，默认就会基于他做一个聚簇索引，这个就不用多说了。

然后呢，就是包含了学生班级、学生姓名、科目名称、成绩分数四个字段，平时查询，可能比较多的就是查找某个班的某个学生的某个科目的成绩。

所以，我们可以针对学生班级、学生姓名和科目名称建立一个联合索引。

接着我们画了下面的一个图，这个图就展示了这个三个字段组成的联合索引的部分内容，大家看一下。

下面有两个数据页，第一个数据页里有三条数据，每条数据都包含了联合索引的三个字段的值和主键值，数据页内部是按照顺序排序的。

首先按照班级字段的值来排序，如果一样则按照学生姓名字段来排序，如果一样，则按照科目名称来排序，所以数据页内部都是按照三个字段的值来排序的，而且还组成了单向链表。

然后数据页之间也是有顺序的，第二个数据页里的三个字段的值一定都大于上一个数据页里三个字段的值，比较方法也是按照班级名称、学生姓名、科目名称依次来比较的，数据页之间组成双向链表。

索引页里就是两条数据，分别指向两个数据页，索引存放的是每个数据页里最小的那个数据的值，大家看到，索引页里指向两个数据页的索引项里都是存放了那个数据页里最小的值！

索引页内部的数据页是组成单向链表有序的，如果你有多个索引页，那么索引页之间也是有序的，组成了双向链表。

好了，那么现在假设我们想要搜索：1班+张小强+数学的成绩，此时你可能会写一个类似下面的SQL语句，select * from studentscore where classname='1班' and studentname='张小强' and subjectname='数学'。

此时就涉及到了一个索引使用的规则，那就是你发起的SQL语句里，where条件里的几个字段都是基于等值来查询，都是用的等于号！而且where条件里的几个字段的名称和顺序也跟你的联合索引一模一样！此时就是等值匹配规则，上面的SQL语句是百分百可以用联合索引来查询的。

那么查询的过程也很简单了，首先到索引页里去找，索引页里有多个数据页的最小值记录，此时直接在索引页里基于二分查找法来找就可以了，先是根据班级名称来找1班这个值对应的数据页，直接可以定位到他所在的数据页，如下图。

然后你就直接找到索引指向的那个数据页就可以了，在数据页内部本身也是一个单向链表，你也是直接就做二分查找就可以了，先按1班这个值来找，你会发现几条数据都是1班，此时就可以按照张小强这个姓名来二分查找，此时会发现多条数据都是张小强，接着就按照科目名称数学来二分查找。

很快就可以定位到下图中的一条数据，1班的张小强的数学科目，他对应的数据的id是127，如下图所示。

然后就根据主键id=127到聚簇索引里按照一样的思路，从索引根节点开始二分查找迅速定位下个层级的页，再不停的找，很快就可以找到id=127的那条数据，然后从里面提取所有字段，包括分数，就可以了。

上面整个过程就是联合索引的查找过程，以及全值匹配规则，假设你的SQL语句的where条件里用的几个字段的名称和顺序，都跟你的索引里的字段一样，同时你还是用等号在做等值匹配，那么直接就会按照上述过程来找。

对于联合索引而言，就是依次按照各个字段来进行二分查找，先定位到第一个字段对应的值在哪个页里，然后如果第一个字段有多条数据值都一样，就根据第二个字段来找，以此类推，一定可以定位到某条或者某几条数据！

基本的索引使用规则

上面我们讲的是等值匹配规则，就是你where语句中的几个字段名称和联合索引的字段完全一样，而且都是基于等号的等值匹配，那百分百会用上我们的索引，这个大家是没有问题的，即使你where语句里写的字段的顺序和联合索引里的字段顺序不一致，也没关系，MySQL会自动优化为按联合索引的字段顺序去找。

现在看第二个规则，就是最左侧列匹配，这个意思就是假设我们联合索引是KEY(classname, studentname, subject_name)，那么不一定必须要在where语句里根据三个字段来查，其实只要根据最左侧的部分字段来查，也是可以的。

比如你可以写select * from studentscore where classname='' and student_name=''，就查某个学生所有科目的成绩，这都是没有问题的。

但是假设你写一个select * from studentscore where subjectname=''，那就不行了，因为联合索引的B+树里，是必须先按classname查，再按studentname查，不能跳过前面两个字段，直接按最后一个subject_name查的。

另外，假设你写一个select * from studentscore where classname='' and subjectname=''，那么只有classname的值可以在索引里搜索，剩下的subject_name是没法在索引里找的，道理同上。

所以在建立索引的过程中，你必须考虑好联合索引字段的顺序，以及你平时写SQL的时候要按哪几个字段来查。

第三个规则，是最左前缀匹配原则，即如果你要用like语法来查，比如select * from studentscore where classname like '1%'，查找所有1打头的班级的分数，那么也是可以用到索引的。

因为你的联合索引的B+树里，都是按照classname排序的，所以你要是给出classname的确定的最左前缀就是1，然后后面的给一个模糊匹配符号，那也是可以基于索引来查找的，这是没问题的。

B+树是由字符串组成的。

字符串的排序方式：先按照第一个字母排序，如果第一个字母相同，就按照第二个字母排序。。。以此类推 由于B+树的索引顺序，是按照首字母的大小进行排序，前缀匹配又是匹配首字母。所以可以在B+树上进行有序的查找，查找首字母符合要求的数据。所以有些时候可以用到索引。

但是你如果写class_name like '%班'，在左侧用一个模糊匹配符，那他就没法用索引了，因为不知道你最左前缀是什么，怎么去索引里找啊？

第四个规则，就是范围查找规则，这个意思就是说，我们可以用select * from studentscore where classname>'1班' and class_name<'5班'这样的语句来范围查找某几个班级的分数。

这个时候也是会用到索引的，因为我们的索引的最下层的数据页都是按顺序组成双向链表的，所以完全可以先找到'1班'对应的数据页，再找到'5班'对应的数据页，两个数据页中间的那些数据页，就全都是在你范围内的数据了！

但是如果你要是写select * from studentscore where classname>'1班' and classname<'5班' and studentname>''，这里只有classname是可以基于索引来找的，studentname的范围查询是没法用到索引的！

这也是一条规则，就是你的where语句里如果有范围查询，那只有对联合索引里最左侧的列进行范围查询才能用到索引！

第五个规则，就是等值匹配+范围匹配的规则，如果你要是用select * from studentscore where classname='1班' and studentname>'' and subjectname<''，那么此时你首先可以用classname在索引里精准定位到一波数据，接着这波数据里的studentname都是按照顺序排列的，所以studentname>''也会基于索引来查找，但是接下来的subjectname<''是不能用索引的。

假设有索引(a,b,c)

a: 1 1 1 2 2 2 2

b: 1 2 3 1 1 2 4

c: 3 4 5 6 7 3 3

在a值确定的情况下即a相等的情况下,b值一定有序。比如a=1

在a和b确定的情况下,即a和b相等的情况下,c值一定有序,比如a=2 b=1

在这个场景下:a是确定的,所以a一定能用到索引。由于a是确定的导致b是有序的,b也能用到索引。

但是a,b是不确定的，所以用不到索引,但是可以走索引下推？

所以综上所述，一般我们如果写SQL语句，都是用联合索引的最左侧的多个字段来进行等值匹配+范围搜索，或者是基于最左侧的部分字段来进行最左前缀模糊匹配，或者基于最左侧字段来进行范围搜索，这就要写符合规则的SQL语句，才能用上我们建立好的联合索引！

参考:https://blog.csdn.net/zht245648124/article/details/129290878