研究索引失效的问题

一、准备工作

1、建表

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 '员工记录表';

2、插入数据

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());

3、创建索引

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

二、索引不失效的口诀

1、全职匹配我最爱
2、最佳左侧前缀法则
3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
4、存储引起不能使用索引中范围条件右边的列
5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
6、mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
7、is null,is not null也无法使用索引
8、like以通配符开头('%abc...')，mysql索引失效会变成全表扫描的操作
9、字符串不加单引号索引失效
10、少用or，用它来连接时会索引失效

1、全职匹配我最爱

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

上面查询语句使用到了复合索引的三个字段，属于全职匹配。
2、最佳做前缀法则

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

image.png

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

image.png

带头大哥不能死！！！

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

image.png

中间兄弟不能断！！！

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

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

image.png

索引列上少计算！！！

4、存储引起不能使用索引中范围条件右边的列

EXPLAIN select * from staffs where  name='z3' and age > 20 and pos = 'manager';

image.png

5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

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

image.png

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

image.png

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

EXPLAIN select * from staffs where  name!='July';

image.png

具体要看生产环境，不要因为这个束手束脚

7、is null,is not null也无法使用索引

EXPLAIN select * from staffs where  name is null;

image.png

8、like以通配符开头('%abc...')，mysql索引失效会变成全表扫描的操作

EXPLAIN select * from staffs where  name like '%July';

image.png

like%加右边！！！

问题：如何解决like '%字符串%'时索引不被使用的方法？
mybatis中like sql如何编写：要拼接，不要写死。使用CONCAT()函数连接参数形式

我就是要两边写%,怎么办？？？

1）新建表

create table tbl_user(
id INT(11) 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;

2）插入数据

insert into tbl_user(name,age,email) values('1aa1',21,'b@163.com');
insert into tbl_user(name,age,email) values('2aa2',222,'a@163.com');
insert into tbl_user(name,age,email) values('3aa3',265,'c@163.com');
insert into tbl_user(name,age,email) values('4aa4',21,'d@163.com');

3）创建索引

create index idx_user_nameAge on tbl_user(name,age);

4)开始分析

explain select name,age from tbl_user where name like '%aa%';

image.png

9、字符串不加单引号索引失效

select * from staffs where name = 2000

image.png

10、少用or，用它来连接时会索引失效

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

image.png