1.全值匹配
explain select * from employees where name = 'Lucy';
ref为const
2.最左前缀法则
当索引是组合索引时,需遵守最左前缀法则,即查询组合索引中的列时,从最左侧开始不能跳过列,否则索引会失效
explain select * from employees where name = 'Lucy' and age=23 and position='dev';
explain select * from employees where name = 'Lucy' and age=23;
explain select * from employees where name = 'Lucy';
explain select * from employees where age=23;
explain select * from employees where position='dev';
explain select * from employees where age=23 and position='dev';
只有前三种是符合法则的,走了索引
3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
explain select * from employees where left(name,2) = 'Lu';
4.存储引擎不能使用索引中范围条件右边的列
组合索引时,比如有三个字段组合
explain select * from employees where name = 'Lucy' and age>23 and position='dev';
从key_len可以看出,全部字段使用时是140,目前只有78,得知postion没有走索引。
mysql5.6版本之前没有加入index condition pushdown,所以索引逻辑还是这样的:
即便对于复合索引,从第一列开始先确定第一列索引范围,如果范围带=号,则对于=号情况,确定第二列索引范围加入索引结果集里,每列的处理方式都是一样的。
确定完索引范围后,则回表查询数据,再用剩下的where条件进行过滤判断。
mysql5.6后加入了ICP,对于确定完了索引范围后,会用剩下的where条件对索引范围再进行一次过滤,然后再回表,再用剩下的where条件进行过滤判断。(减少回表记录数量)。
(只访问索引的查询(索引列包含查询列)),减少 select * 语句。select的字段是索引所相关的字段,不然无法是覆盖索引,只是const级别的查询而已。
< 小于、 > 大于、 <=、>= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
explain select * from employees where name != 'Lucy' ;
7.is null 、 is not null 一般也会使索引失效
explain select * from employees where age is not null ;
8.字符串不添加单引号也会使索引失效
explain select * from employees where name = 20;
9.模糊查询-like 用通配符开头('%xxx...')会导致索引失效
explain select * from employees where name like '%Lu';
explain select * from employees where name like '%Lu%';
前后都是通配符时,索引还是失效,需要使用覆盖索引,即select字段是索引相关字段。
explain select name,age from employees where name like '%Lu%';
10.查询使用or或者in时
不一定会走索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
11.查询使用范围查询时会失效
explain select * from employees where age >1 and age <=1000;
这里可能是数据量过大或者过小的情况下,直接不走索引,如果数据量太大可以优化范围查询(即分页)
如:
explain select * from employees where age >1 and age <=500;
12.组合索引常见示例
组合索引index_a_b_c(a,b,c)
where子句 |
索引是否有效 |
a=3 |
是,使用了最左字段a |
a=3 and b=4 |
是,使用了a、b |
a=3 and b=4 and c=5 |
是,都使用了 |
b=4 and c=5或者b=4或者c=5 |
否,没使用最左字段a |
a=3 and c=5 |
a有用,c没用,b被中断了 |
a=3 and b>4 and c=5 |
a、b有用,c不在范围内,b是范围查找,中断了 |
a=3 and b like 'AA%' and c=5 |
是,都使用了 |
a=3 and b like '%AA' and c=5 |
是,使用了a |
a=3 and b like '%AA%' and c=5 |
是,使用了a |
a=3 and b like 'A%AA%' and c=5 |
是,都使用了 |
like AA%相当于=常量,%AA和%AA% 相当于范围 |