欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL索引优化(二)索引失效

程序员文章站 2022-03-08 20:11:27
...

本文主要说的是索引失效问题

创建表

create table if not exists staffs(
    id int primary key auto_increment,
    name varchar(24) not null default '' comment '姓名',
    age int not null default 0 comment '年龄',
    pos varchar(20) not null default '' comment '职位',
    add_time timestamp not null default current_timestamp comment '入职时间'
)comment '员工记录表';

insert into staffs(name, age, pos, add_time) values ('z3', 22, 'manager', NOW());
insert into staffs(name, age, pos, add_time) values ('July', 23, 'dev', NOW());
insert into staffs(name, age, pos, add_time) values ('2000', 23, 'dev', NOW());



添加索引

alter table staffs add index idx_staffs_nameAgePos(name, age, pos);

1、左前缀法则

先看一个正常的

explain select * from staffs where name = 'July' and age = 23 and pos = 'dev';

结果如下
MySQL索引优化(二)索引失效
由ref下的三个const可知,三个索引都被使用到了,key_len的长度也可以体现。说明这个是正常的sql查询。

1)带头索引不能少

name字段的索引是带头索引,这里不使用name字段的索引。

explain select * from staffs where age = 23 and pos = 'dev';

结果如下
MySQL索引优化(二)索引失效
可知type类型变成了最糟糕的ALL,且key_len的长度为null,说明三个索引都没有用到。

2)中间索引不能丢

age字段的索引为中间索引,所以使用age字段的索引。

explain select * from staffs where name = 'July' and pos = 'dev';

结果如下
MySQL索引优化(二)索引失效
由ref下的一个const可知,索引只使用到了一个,也就是带头索引。

3)结论

综上,如果可以全值匹配就全值匹配(条件字段和索引一样),要遵守左前缀法则,查询从索引的最左前列开始并且不跳过索引中的列。

2、 不在索引列上做任何操作

这里的操作包括计算、函数、类型转换,会导致索引失效而转向全表扫描。

select * from staffs where name = 'July';
select * from staffs where left(name, 4) = 'July';

执行这两条sql都可以得到想要的结果。
MySQL索引优化(二)索引失效
执行

explain select * from staffs where left(name, 4) = 'July';

结果如下
MySQL索引优化(二)索引失效
会发现索引失效了。

3、 少用范围查询

执行

explain select * from staffs where name = 'July' and age = 23;
explain select * from staffs where name = 'July' and age > 23;

结果如下
MySQL索引优化(二)索引失效
type类型由ref降低为range。

4、减少使用select *

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *

explain select * from staffs where name = 'July' and age = 23;

MySQL索引优化(二)索引失效
只取想要的数据。

explain select name, age, pos from staffs where name = 'July' and age = 23;

结果如下
MySQL索引优化(二)索引失效
Extra出现了Using index,表明性能更优了。

5、不等于

使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描。

explain select * from staffs where name = 'July' and age != 23;

MySQL索引优化(二)索引失效
type变为ALL,索引失效了。

6、is null,is not null

is null,is not null也无法使用索引。

explain select * from staffs where name = 'July' and age is null;

MySQL索引优化(二)索引失效
创建字段的时候,尽量避免空值null。

6、like和通配符

like以通配符开头(%abc)索引也会失效,变成全表扫描。
分别执行

explain select * from staffs where name = 'July';
explain select * from staffs where name like 'July';
explain select * from staffs where name like '%July';

结果如下
MySQL索引优化(二)索引失效
可以看见第三条sql的索引失效。所以like的时候,百分号加右边。

如何解决like ‘%字符串%’ 时索引不会被使用?
使用覆盖索引即可解决(只要查询的字段是索引字段即可)。
MySQL索引优化(二)索引失效
可以看到type变为了index,性能比ALL好了不少。

7、 字符串不加单引号

字符串不加单引号索引失效。

explain select * from staffs where name = 2000;

结果如下
MySQL索引优化(二)索引失效
type变成了ALL,这里也说明了MySQL底层会自动进行类型转换,隐性自动类型转换,也是我们需要避免的。
字符串不加单引号还会导致一个很严重的问题,就是行锁会升级为表锁。

8、 少用or

or也会导致索引失效。

 explain select * from staffs where name = 'July' or name = 'z3';

MySQL索引优化(二)索引失效
可见索引失效了。

9、

MySQL索引优化(二)索引失效