联合索引的查询原理--mysql

原理

之所以要分析联合索引，是因为我们平时设计系统的时候一般都是设计联合索引，很少用单个字段做索引。

现在我们假设：

• 当前有一个表是存储学生成绩的，这个表里有id，这个id是一个自增主键，默认会基于主键做一个聚簇索引。
• 然后，这个表里还包含了学生班级、学生姓名、科目名称、成绩分数这四个字段。

平时查询，可能比较多的是查找某个班的某个学生的某个科目的成绩。所以，我们可以基于学生班级、学生姓名、科目名称建立一个联合索引。

如下图展示了这三个字段组成的联合索引的部分内容：

• 下面有两个数据页，第一个数据页里有三条数据，每条数据都包含了联合索引的三个字段的值和主键值，数据页内部是按照顺序排序的。
• 首先按照班级字段的值来排序，如果一样则按照学生姓名字段来排序，如果一样则按照科目名称来排序。所以数据页内部都是按照三个字段的值来排序的，而且还组成了单向链表
• 然后数据页之间也是有顺序的，第二个数据页里的三个字段的值一定都是按照上一个数据页里的三个字段的值，比较丰富也是按照班级名称、学生姓名、科目名称依次来比较的，数据页之间组成双向链表。
• 索引页里就是两条数据，分别指向两个索引页，索引存放的是每个数据页里最小的那个数据的值。可以看到，索引页里指向两个数据页的索引项里存放了最小数据页里最小的值。
• 索引页内部的数据页是组成单向链表有序的，如果你有多个索引页，那么索引页之间也是有序的，组成了双向链表。

全值匹配规则

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

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

查询的过程也很简单：

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

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

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

上面整个过程就是联合索引的查找过程，以及全值匹配规则。

• 如果你的SQL语句的where条件里用的几个字段的名称和顺序，都跟你的索引里的字段一样，同时都是基于等号的等值匹配，那么直接就会按照上述过程来找。
• 即使where语句里写的顺序，与联合索引里的字段顺序不一致，也没有关系，MySQL会自动优化为联合索引的字段顺序去找
• 对于联合索引来说，就是依次按照各个字段来进行二分查找，先定位到第一个字段对应的值在哪个页里，然后如果一条字段里有多条数据值都一样，就根据第二个字段来找，依次类推，一定可以定位到某条或者某几条数据！

最左侧列匹配

这个意思就是假设我们联合索引是KEY(class_name，student_name，subject_name)，那么不一定必须要在where语句里根据三个字段来查，其实只要根据最左侧的部分字段来查，也是可以的。

• 比如你可以写select * from student_score where class_name=‘’ and student_name=‘’，就查某个学生所有科目的成绩，这都是没有问题的。
• 但是假设你写一个select * from student_score where subject_name=‘’，那就不行了，因为联合索引的B+树里，是必须先按照class_name查，再按照student查，不能跳过前两个字段，直接按照最后一个subject_name来查的。
• 另外，假设你写一个select * from student_score where class_name=‘’ and subject_name=‘’，那么只有class_name的值可以在索引里搜索，剩下的subject_name是没法在索引里找的，道理同上。

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

最左前缀匹配原则

• 即如果你要用like语法来查，比如select * from student_score where class_name like ‘1%’，查找所有1打头的班级的分数，那么也是可以用到索引的。
• 确定的最左前缀是1，然后后面的给一个模糊匹配符号，那也是可以基于索引来查找的，这是没问题的。
• 但是你如果写class_name like ‘%班’，在左侧用一个模糊匹配符，那他就没法用索引了，因为不知道你最左前缀是什么，怎么去索引里找啊？

范围查找规则

• 这个意思就是说，我们可以用select * from student_score where class_name > ‘1班’ and class_name < '5班’这样的语句来范围查找某几个班级的分数。
• 这个时候也是会用到索引的，因为我们的索引的最下层的数据页都是按照顺序组成的双向链表的，所以完全可以先找到‘1班’对应的数据，再找到‘5班’对应的数据页，两个数据页中间的那些数据页，就全都是在你范围内的数据了。
• 但是如果你要是写select * from student_score where class_name > ‘1班’ and class_name < ‘5班’ and student_name > ‘xx’，这里只有class_name是可以基于索引来找的，student_name的范围查询是没法用到索引的。

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

等值匹配+范围匹配的规则

如果你要是用select * from student_score where class_name=‘1班’ and student_name>‘xx’ and subject_name<‘yy’，那么此时你首先可以用class_name在索引里精准定位到一波数据，接着这波数据里的student_name都是按照顺序排列的，所以student_name > 'xx’这会基于索引来查找，但是接下来的subject_name < 'yy’是不能用索引的。

小结

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

问题

什么是联合索引？为什么需要注意联合索引中的顺序？

MySQL中可以使用多个字段同时建立一个索引，叫做联合索引。

在联合索引中，如果想要索引，需要按照建立索引的字段顺序挨个使用，否则无法命中索引。

所以，我们在建立联合索引的时候，一定要把最常用的列放在最左边。

如何创建联合索引

按照我们的想法，一个查询创建一个索引，所以我们针对这两条 SQL 创建了两个索 引，这种做法觉得正确吗？

CREATE INDEX idx_name on user_innodb(name);
CREATE INDEX idx_name_phone on user_innodb(name,phone);

• 1
• 2

当我们创建一个联合索引的时候，按照最左匹配原则，用左边的字段 name 去查询 的时候，也能用到索引，所以第一个索引完全没必要。

如果我们创建三个字段的索引 index(a,b,c)，相当于创建三个索引：

• index(a)
• index(a,b)
• index(a,b,c)

用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引的。不能不用第一个字段，不能中断。 这里就是 MySQL 联合索引的最左匹配原则。

---

---

---

---

---

• 联合索引在B+Tree中是复合的数据结构，它是按照从左到右的顺序来建立索引树的(name在左边、phone在右边)
• 从上图可以看出来，name是有序的，phone是无序的。当name相等的时候，pthone才是有序的
• 这时候我们使用 where name= '青山' and phone = '136xx '去查询数据的时候，B+Tree会优先比较name来确定下一步应该搜索的方向，往左还是向右。如果name相同的时候再比较pthone。但是如果查询条件没有name，就不知道应该查哪个节点，因为建立搜索树的时候name是第一个比较因子，所以用不到索引。