一.索引规则
| ALL | 全表扫描
| index | 索引全扫描
| range | 索引范围扫描,常用语<,<=,>=,between等操作
| ref | 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中
| eq_ref | 类似ref,区别在于使用的是唯一索引,使用主键的关联查询
| const/system | 单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询
| null | MySQL不访问任何表或索引,直接返回结果
由上至下,效率越来越高
当前表索引:
show index from company;
二.联合索引失效情况(遵守最左前缀法则)
一.联合索引生效与失效规则
如果索引了多列(联合索引),要遵守最左前缀法则。
最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。
如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。
show index from company;
对于最左前缀法则指的是,查询时,最左边的列,也就是company_name必须存在,否则索引全部失效。而且中间不能跳过某一列,否则该列后面的字段索引将失效
(例如:第五组示例 当前company_age没有,则company_status的索引也不会生效)
接下来,我们来演示几组案例,看一下具体的执行计划:
第一组:(使用最左侧company_name)
explain select * from `company` where company_name = 'dyyt'
第二组:(使用最左侧company_name + company_age)
explain select * from `company` where company_name = 'dyyt' AND company_age = 1
第三组:(使用最左侧company_name + company_age + company_status)
explain select * from `company` where company_name = 'dyyt' AND company_age = 1 AND company_status='1'
结论:以上的这三组测试中,我们发现只要联合索引最左边的字段 company_name存在,索引就会生效,只不过索引的长度不同。 而且由以上三组测试,我们也可以推测出company_name字段索引长度为768、company_age字段索引长度为5、company_status字段索引长度为33。
第四组:(不遵守最左前缀法则)
explain select * from `company` where company_age = 1 AND company_status='1'
结论:当前情况下,联合索引中的第一个company_name并为在条件中,索引字段长度为null,所以联合索引失效。
第五组:(跳过某一列,否则该列后面的字段索引将失效)
explain select * from `company` where company_name = 'dyyt' AND company_status='1'
结论:当前情况下,sql语句在条件中丢失了company_age的条件,结果的索引长度为768,由上一、二、三组的对比可以看出company_name 的索引长度为768,所以可以判定,company_status索引并未生效,所以,从跳过的那一列后面的所有字段的索引将会失效。
第六组:(三个索引都使用,但是使用顺序颠倒)
explain select * from `company` where company_status='1' AND company_name = 'dyyt' AND company_age = 1
结论:当前情况下,只要是最左侧的索引company_name使用到了,则索引是生效的。最左前缀法则中指的最左边的列,是指在查询时,联合索引的最左边的字段(即是第一个字段)必须存在,与我们编写SQL时,条件编写的先后顺序无关。
大总结:
其实最左前缀法则概括只有一句话,即为那一个索引跳过(不存在),则其后面的所有索引均不生效!
二.联合索引失效特例
联合索引中,范围查询为(>,<),范围查询右侧的列索引失效(当前索引生效)。
参照:
explain select * from `company` where company_name = 'dyyt' AND company_age > 1 AND company_status='1'
由上三组的对照可以得出:company_name字段索引长度为768、company_age字段索引长度为5、company_status字段索引长度为33。所以当前生效的索引为:company_name,company_age,而company_status索引失效
联合索引中,范围查询为(>=,<=),则索引全部生效。
参照:
explain select * from `company` where company_name = 'dyyt' AND company_age >= 1 AND company_status='1'
explain select * from `company` where company_name = 'dyyt' AND company_age <= 1 AND company_status='1'
由当前索引的长度可以看出,三个索引都用到了。
大结论:因为存在等于,有了等于,查询到等于的记录,大于只要在等于的基础上继续查询就可以,相对于大于,有确定的记录。
三.单索引失效情况
1.有or必索引失效
explain select * from `company` where company_name = 'dyyt' or company_age <= 1 AND company_status='1'
2.复合索引未用左列字段(最左前缀法则)
参考目录三
3.like以%开头
1.不使用%开头,则索引正常使用
explain select * from `company` where company_name like 'dyyt' AND company_age <= 1 AND company_status like '1'
2.使用%开头,索引失效
explain select * from `company` where company_name like '%dyyt' AND company_age <= 1 AND company_status like '1'
4.需要类型转换
很明显,company_status的索引未生效
explain select * from `company` where company_name = 'dyyt' AND company_age = 1 AND company_status = 1
5.where中索引列有运算
explain select * from `company` where company_name = 'dyyt' AND company_age + 1 = 1
explain select * from `company` where company_name + 'x' = 'dyyt' AND company_age = 1
6.where中索引列使用了函数
7.如果mysql觉得全表扫描更快时(数据少)
四:总结
单索引目前为以上几种规则失效,联合索引遵循最左前缀规则,但是如果联合索引中的左侧的索引失效了,则当前索引与后续索引全部失效。
即联合索引遵循之前先遵循单索引规则。