在自己测试索引成功场景时,可能出现符合索引规则,但是却不走索引的情况,这是因为mysql有自己的优化规则,比如数据量很少的时候,不走索引反而更快,具体可自行百度,
全值匹配(索引最佳)
explain select * from user where name = 'zhangsan' and age = 20 and pos = 'cxy' and phone = '18730658760';
和索引顺序无关,MySQL底层的优化器会进行优化,调整索引的顺序
explain select * from user where name = 'zhangsan' and age = 20 and pos = 'cxy' and phone = '18730658760';
1、违反最左前缀法则
如果索引有多列,要遵守最左前缀法则
即查询从索引的最左前列开始并且不跳过索引中的列
explain select * from user where age = 20 and phone = '18730658760' and pos = 'cxy';
但是如果,你的查询条件是联合索引的其中一个,也会走索引
如果你的这张表,刚好就只有name,age,pos,phone那也会走索引(覆盖索引)
1、违反最左前缀法则索引失效的原因:
先看mysql索引B+树的存储结构 依次插入1,2 2,1 3,3 1,4 4,1 这5个(a,b)
如上图,
他的a顺序:1,1,2,3,4
他的b顺序:2,4,1,3,1 明显b是乱序的,只有a才是顺序的。在a有序的前提下,b也是有序的(1,2 1,4)
乱序无法定位,只能进行全表扫描,所以用不了索引。
2、在索引列上做任何操作
如计算、函数、(自动or手动)类型转换等操作,会导致索引失效从而全表扫描
explain select * from user where left(name,5) = 'zhangsan' and age = 20 and phone = '18730658760';
2、在索引列上做任何操作,索引失效的原因:
数据库的索引是有一个具体的后缀为. MYI的索引文件,所以当涉及到索引列的计算时,mysql自己的优化器会认为,重新修改索引文件代价太高,从而不走索引。
3.范围之后全失效——存储引擎不能使用索引中范围条件右边的列
key_len = 1028
key_len 还是等于 1028,说明pos的索引没用上
3.范围之后全失效——存储引擎不能使用索引中范围条件右边的列 失效原因:
因为范围条件后(age)的pos是无序的。
4、尽量使用覆盖索引
只访问索引查询(索引列和查询列一致),减少select*。
该表除了name,ahe,pos,phone之外, 该表还有一个aa字段。
原因:指定的具体值如果是索引,那就不用回表去查询
5、使用不等于(!=、<>)
mysql在使用不等于(!=、<>)的时候无法使用索引会导致全表扫描(除覆盖索引外)
explain select * from user where age != 20;
explain select * from user where age <> 20;
原因:范围查询后,sql优化器会执行全表扫描
6、like以通配符开头('%abc')
索引失效
explain select * from user where name like '%zhangsan';
索引生效
explain select * from user where name like 'zhangsan%';
原因:以通配符开头,是无序的,无法使用索引。
7、字符串不加单引号索引失效
explain select * from user where name = 2000;
原因:发生隐式转换 和例子2同理。
8.用 or 分割开的条件,前后都要满足索引最左前缀法则才不会失效。
9、order by
正常(索引参与了排序)
explain select * from user where name = 'zhangsan' and age = 20 order by age,pos;
备注:索引有两个作用:排序和查找
导致额外的文件排序(会降低性能)
explain select name,age from user where name = 'zhangsan' order by pos;//违反最左前缀法则
explain select name,age from user where name = 'zhangsan' order by pos,age;//违反最左前缀法则
explain select * from user where name = 'zhangsan' and age = 20 order by created_time,age;//含非索引字段
10、group by
正常(索引参与了排序)
explain select name,age from user where name = 'zhangsan' group by age;
备注:分组之前必排序(排序同order by)
导致产生临时表(会降低性能)
explain select name,pos from user where name = 'zhangsan' group by pos;//违反最左前缀法则
explain select name,age from user where name = 'zhangsan' group by pos,age;//违反最左前缀法则
explain select name,age from user where name = 'zhangsan' group by age,created_time;//含非索引字段
9.10的失败原因:违反最左前缀法则