研究索引失效的问题
一、准备工作
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
网友评论