MySQL索引匹配原则

用好索引能够大幅度提升SQL的性能,那么怎样使用索引才能更好的利用索引的特性呢?我们先来看一下索引的匹配原则,主要有以下几种

  • 等值匹配
  • 最左侧列匹配
  • 最左前缀匹配
  • 范围查找
  • 等值匹配+范围查找

假设现在有一张表student_score,表中有5个字段,id,class, name,course,score,其中id为主键,有四条数据

  • id:1 class:1班 name:张三 course:数学 score 90
  • id:2 class:1班 name:张三 course:语文 score 50
  • id:3 class:3班 name:李四 course:数学 score 50
  • id:4 class:3班 name:李四 course:语文 score 20

并且我们以class、name、course建立了联合索引

等值匹配

假设现在有一条sql如下

select * from student_score where class = '1班' and name = '张三' and course = '数学'

这条语句中就会用到所有的索引字段,首先找class,然后查找name,最后查找course

最左侧列匹配

假设有一条sql如下

select * from student_score where course = '数学'

该SQL语句就不会用到索引,因为B+树中,索引的使用是从左到右的,不能跳过左边的,直接查找右边的,我们可以用explain来看一下

image.png

确定这里是没有用到索引的,这条SQL可以改成

select * from student_score where class = '1班' and name = '张三'

这样就可以用到索引了

image.png

因此在建立索引的时候,我们需要考虑表中的字段,到底哪些字段是最常被用于查找的

最左前缀匹配

这条原则适用于模糊匹配的时候,也就是需要用到like的时候,假设有一条SQL如下

select * from student_score where class like '1%'

这里是可以用到索引的,印在B+索引树中,数据都是按照字段来排序的,比如这里有联合索引 key(class、name、course) ,那么数据会按照class排序

image.png

但是如果你把SQL写成了下面这样,就用不到索引了,因为匹配不到最左前缀

select * from student_score where class like '%班'

image.png

范围查找

我们可以通过><这种范围比较来查找数据

select * from student_score where class > '1班' and class < '3班'

通过explain,可以看到,这条sql是用到了索引的
image.png
但是如果你把sql改成下面这样的,就不会用到索引了

select * from student_score where name > '张三'

image.png
因为该联合索引的B+树中只能根据class字段来进行范围查找,也就是联合索引中最左侧的字段

等值匹配+范围查找

假设有一条SQL如下

select * from student_score where class = '1班' and name > '张三' and course > '数学'

image.png
通过explain分析后,可以看到,这里也会用到索引