MySQL索引-索引匹配规则

·  阅读 1084

「这是我参与11月更文挑战的第18天,活动详情查看:2021最后一次更文挑战

平时设计的时候很多时候都会用到联合索引,一般很少用单个字段作为索引,这样可以让索引尽量少一些,避免磁盘占用太多,增删改性能太差。

加入有叫学生分数表,包含:班级,姓名,科目名称,成绩分数,一般我们都会根据学生的班级+姓名+科目来查询,这时候就需要建立二级索引KEY(class_name,student_name ,subject_nane )。这时候索引的数据结构图如下图所示:

image.png

等值匹配

执行下面的语句:SELECT * FROM student_scope where class_name= '1班' AND student_name ='小明' AND subject_nane ='英语';这样的语句,因为WHERE条件里的几个字段的名称和顺序跟建立的联合索引一模一样,此时就会等值匹配,从索引页依次往下查找,会定位到:1+小明+英语(id=100)这条数据,然后根据id 从聚簇索引中回表查询提取所有的字段信息。

最左侧列匹配

例如联合索引:KEY(class_name,student_name ,subject_nane ),不一定在where条件中都要写3个字段来才会走索引查询,只要根据最左侧的部分字段来查询也要走索引,下面的3条SQL都会走索引:

SELECT * FROM student_scope where class_name= '1班' AND student_name ='小明' AND subject_nane  ='英语';
SELECT * FROM student_scope where class_name= '1班' AND student_name ='小明';
SELECT * FROM student_scope where class_name= '1班' ;
复制代码

会根据建立的索引采取最左侧来匹配,当执行下面的SQL,class_name字段会走索引,subject_name 就不会走索引。

SELECT * FROM student_scope where class_name= '1班' AND subject_nane  ='英语';
复制代码

又如下面的语句,完全就不会走索引

SELECT * FROM student_scope where student_name ='小明' AND subject_nane  ='英语';
SELECT * FROM student_scope where subject_nane  ='英语';
复制代码

最左前缀匹配原则

在SQL查询的时候,我们经常会根据LIKE语法来查询,比如查询班级为1开头的数据,也可以用到索引,因为你的联合索引的B+树里,都是按照class_name排序的,所以你要是给出class_name的确定的最左前缀就是1,然后在后面给一个模糊匹配符号,那也是可以基于索引来查找。

SELECT * FROM student_scope where class_name like '1%';
复制代码

但是执行以班结尾的数据就不会走到索引,因为不知道左侧前缀是多少,无法基于排序来查找。

SELECT * FROM student_scope where class_name like '%班';
复制代码

范围查找规则

如果SQL查询的字段基于某个索引列采取范围查询,例如下面的SQL,也会走索引。

SELECT * FROM student_scope where class_name >= '1班' AND class_name <'10班';
复制代码
但是当第一个字段是范围查询的时候,后面的第二个字段是没法走索引,例如下面的SQL,class_name 会走索引,后面的student_name 不会走索引。
复制代码
SELECT * FROM student_scope where class_name >= '1班' AND class_name <'10班' AND student_name ='小明';
SELECT * FROM student_scope where class_name >= '1班' AND class_name <'10班' AND student_name >= '小明';
复制代码

等值匹配+范围匹配规则

如果要使用下面的SQL进行查询的时候,此时class_name 会走索引,精准定位到一波数据,接下来这波被命中的数据,是按照student_name排序而来,这时候student_name >= '小明'也会基于索引来查找,但是后面的student_name <'王五'就不能用索引。

SELECT * FROM student_scope where class_name = '1班' AND student_name >= '小明' AND student_name <'王五';
复制代码

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

排序如何使用索引

当我们的SQL 语句需要使用ORDER BY语句进行排序的时候,似乎应该是通过索引快速筛选出来一波数据,接着把数据放入内存,或者放在一个临时磁盘文件里,然后通过排序算法按照ORDER BY后面的字段进行排序,然后把排序好的数据返回,当排序的数据量比较大的时候,就不能够用内存进行排序,就会基于磁盘文件来排序(filesort),这样的话就比较慢。 因为建立的联合索引是按照一定的顺序已经排序好的,如果order by 后面的排序字段能够命中联合索引的时候,就会走索引,例如下面的排序SQL就会走索引

SELECT * FROM student_scope ORDER BY class_name ASC,student_name ASC,subject_nane  ASC LIMIT 10;
SELECT * FROM student_scope ORDER BY class_name DESC,student_name DESC,subject_nane DESC LIMIT 10;
复制代码

在排序的时候尽量按照查询的联合索引去进行order by排序,这样就可以直接使用联合索引树里的数据有序性到索引数里直接按照字段的顺序获取所需要的数据。但是也有一定的限制,因为联合索引里的字段值在索引树里都是从小到大依次排列的 ,所以在ORDER BY里要不然就是每个字段后面什么都不加,直接就是ORDER BY xx1,xx2,xx3,要不然就都加DESC降序排列,就是ORDER BY xx1 DESC,xx2 DESC,xx3 DESC。 当ORDER BY 排序字段既有升序,又有降序,那么是没法走索引进行排序,例如下面的SQL语句没法走索引

SELECT * FROM student_scope ORDER BY class_name DESC,student_name ASC,subject_nane DESC LIMIT 10;
复制代码

分组如何使用索引

GROUP BY 语句使用索引的时候跟ORDER BY排序使用索引的规则一样,对于group by后的字段,最好也是按照联合索引里的最左侧的字段开始,按顺序排列开来,这样的话,就可以完美的运用上索引来直接提取一组一组的数据,然后针对每一组的数据执行聚合函数就可以。

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改