【MySQL】还记不住索引失效场景的来打我！

本文链接：https://blog.csdn.net/qq_43771096/article/details/106416874

文章目录

> 全值匹配我最爱

建立索引

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

查看下列sql的Explian

 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30  
 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'

在这里插入图片描述

最后一行因为建的索引都用上了！快的一批
结论：全值匹配我最爱，指的是查询的字段按照顺序在索引中都可以匹配到,索引都派上用场了

更换条件顺序查看索引能否被应用

在这里插入图片描述

结论:SQL中查询字段的顺序，跟使用索引中字段的顺序没有关系，有优化器用AND把条件连起来会自动优化。

> 最佳左前缀法则

使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列，跳了后面的索引会没用上。

在这里插入图片描述

结论：带头大哥不能死，中间兄弟不能断，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

> 索引列上做计算

在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。

在查询列上使用了函数

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;

在这里插入图片描述

结论：等号左边无计算！等号左边不能有计算。

在查询列上做了转换

create index idx_name on emp(name);
explain select sql_no_cache * from emp where name='30000';
explain select sql_no_cache * from emp where name=30000;

在这里插入图片描述

结论：等号右边无转换！等号右边不进行类型转换

> 索引列上进行范围查询

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd';
explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<5 AND emp.name = 'abcd';

在这里插入图片描述

总结：中间用了范围查询，会使后面的兄弟索引都失效，所以要将可能做范围查询的字段的索引顺序放在最后

> 使用覆盖索引

查询列和索引列时，不要写select *，即使要查50个字段就列50个字段

explain  SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30  and deptId=4 and name='XamgXt';
explain  SELECT SQL_NO_CACHE age,deptId,name  FROM emp WHERE emp.age=30  and deptId=4 and name='XamgXt';

在这里插入图片描述

> 使用不等于(!= 或者<>)

使用不等于(!= 或者<>)时，有时会无法使用索引导致全表扫描。因为系统不知道哪个不等于，进行全表扫描比较

mysql> explain select SQL_NO_CACHE *  from emp where emp.age = 30 ;
mysql> explain select SQL_NO_CACHE *  from emp where emp.age != 30 ;

在这里插入图片描述

> is not null 和 is null

查看索引字段是否允许为空

mysql> desc emp;

当字段允许为Null的条件下

explain select * from emp where age is null
explain select * from emp where age is not null

在这里插入图片描述
结论：is not null用不到索引，is null可以用到索引。

> like的前后模糊匹配

前缀模糊查询会导致索引失效，最前面字母要确定下来

mysql> create index idx_name_emp on emp (name);
mysql> explain select * from emp where name like '%a' ;#索引失效
mysql> explain select * from emp where name like '%a%' ;#索引失效
mysql> explain select * from emp where name like 'a%' ;#索引不失效
mysql> explain select * from emp where name like '_a%' ;#索引失效

在这里插入图片描述

> 使用or

OR查询会导致索引失效

mysql> explain select * from emp where age = 30 or age = 40 ;

在这里插入图片描述

使用union all或者union来替代OR便不会失效

mysql> explain select * from emp where age = 30 union  select * from emp where age =40 ;

在这里插入图片描述

> 总结

建立索引index(a,b,c)，判断下列索引引用情况。

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N，大哥不在了
where a = 3 and c = 5	使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b断了
where a is null and b is not null	is null 支持索引但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用索引，做了计算
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c