最常见和最基本的索引使用规则
规则1:等值匹配规则:
where语句中的几个字段名称和联合索引的字段完全一样,且都是基于等号的等值匹配,即使字段的顺序不一样,Mysql也会自动优化为按联合索引的字段顺序去找。
规则2:最左侧列匹配(最左侧的连续多个字段):
假设联合索引是KEY(class_name, student_name, subject_name),只要根据最左侧的部分字段来查,也是可以走索引的
// class_name、student_name都走索引
select * from student_score where class_name=’’ and student_name=’’
// subject_name不走索引,因为联合索引的B+树里,必须先按class_name查,再按student_name查,不能跳过前面两个字段直接按最后一个subject_name查
select * from student_score where subject_name=’’
// 同理class_name走索引,subject_name不走索引
select * from student_score where class_name=’’ and subject_name=’’
规则3:最左前缀匹配原则(基于最左侧的连续多个字段来进行最左前缀模糊匹配):
// 因为你的联合索引的B+树里,都是按照class_name排序的,所以你要是给出class_name的确定的最左前缀就是1,后面给一个模糊匹配符号,也可以基于索引来查
select * from student_score where class_name like ‘1%’
// 但是如果写class_name like ‘%班’,在左侧用一个模糊匹配符,就没法用索引了,因为不知道你最左侧前缀是什么
select * from student_score where class_name like ‘%班’
// 两个字段都走索引
EXPLAIN select * from student_score tt where tt.class_name like '1%' and tt.student_name like 'liang%';
规则4:范围查找规则(基于最左侧字段进行范围搜索):
where语句里如果有范围查询,那只有对联合索引里最左侧的1列进行范围查询才能用到索引
// 因为索引的最下层的数据页都是按顺序组成双向链表的,所以完全可以先找到'7年级'对应的数据页,再找到'9年级'对应的数据页,两个数据页中间的数据页就都是你范围内的数据了
// class_name走索引
EXPLAIN select * from student_score where class_name > '7年级' and class_name < '9年级';
// class_name走索引,但是student_name不走索引
EXPLAIN select * from student_score where class_name > '7年级' and class_name < '9年级' and student_name > 'pengliang02';
规则5:等值匹配+范围匹配的规则:
如果你where语句里有等值匹配,还有范围匹配,此时必须是先让联合索引最左侧开始的多个字段使用等值匹配,接着最后一个字段是范围匹配
// class_name、student_name走索引,subject_name不走索引
EXPLAIN select * from student_score tt where tt.class_name ='9年级' and tt.student_name > ''pengliang02'' and tt.subject_name > '化学02';