MySQL常见索引失效
索引失效的原因有很多种, 这里列举一些常见失效案例。
首先创建表和数据
create table 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 '入职时间'
) charset utf8 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('tom',23,'dev',now());
...
添加索引:
# 添加复合索引 name,age,pos
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
1.单个--多个索引匹配
伴随where后面查询精度越大,key_len越大,ref中const越多,代价也会越大。
2.未遵循最左前缀原则
建立索引顺序是name,age,pos,直接查询age,未遵循最左前缀中开头必为建立索引的字段。从而导致索引失效。
上面只引用了部分索引,从key_len可以看出,并且ref就一个常量,违背了最左前缀的中间部分不能省,导致只右部分索引起效
3.不要在索引列上做任何操作
所说的操作指的是计算,函数,自动或手动的类型转换,这样会导致索引失效,从而转换全表扫描
使用left函数,导致索引失效
4.范围检索也会让索引失效
因为第二个字段使用范围查找,导致第二个索引用途不是检索而是排序,这样导致第三个索引失效。
常用范围查找有:in/like/between/>
5.尽量使用覆盖索引
1和2执行计划对比,可以看第二个Extra使用Using where,Using index,这样表示用索引检索并查询。
而3,4对比可以看出虽然第三个执行计划的第二个索引字段使用age>25的范围排序,最起码第一个索引是生效的。
这都是得益于select 索引字段,而不是select *
6.使用不等于(!= or <>) 无法使用索引
使用不等于导致全表扫描
可以看到type=ALL为全表扫描
7.is null 和is not null也会无法使用索引
为了避免此类事情发生最好设置default值
8. like 以通配符开头"%abc" 会造成全表扫描
而通过常量开头不会导致索引失效
当然如果你执意像要通配符形式为%abc%形式让索引不失效也是可以的:
创建表和数据,示例:
create table `tbl_user`(
`id` int(10) not null auto_increment,
`name` varchar(20) default null,
`age` int(11) default null,
`email` varchar(20) default null,
primary key(`id`)
)engine=innodb auto_increment=1 default charset=utf8;
insert into tbl_user(name,age,email)values('1aa1',21,'aaa@163.com');
insert into tbl_user(name,age,email)values('2aa2',22222,'bbb@163.com');
insert into tbl_user(name,age,email)values('3aa3',33333,'ccc@163.com');
创建覆盖索引:
create index idx_user_nameAge on tbl_user(name,age);
我们可以看到给name,age创建覆盖索引
我们指定查询的字段使用覆盖索引全,单字段,都不会造成索引失效
但我们添加email(email不是我们创建索引字段),就会造成全表扫描,email就好像搅屎棍一样,导致索引失效
所以通过:select [覆盖索引or 主键索引 or 覆盖索引沾边的字段] from staffs where name like '%July%';可以不会让索引失效,通常type为index
9.字段不是整形不加单引号
字符串不加单引号索引失效 name为varchar类型 ,必须使用单引号。 否则会失效。
name字段类型为varchar,但是我们查询条件为整型,其内部SQL会自行将int类型转换字符串,但是索引失效
10.少用or
用它连接也会导致索引失效。