create table staffs(
id int primary key auto_increment,
name varchar(20) not null default "aaa" comment "姓名",
age int not null default 0 comment "年龄",
pos varchar(20) not null default " ass" comment "职位",
add_time timestamp not null default current_timestamp comment "入职时间"
);
insert into staffs(name,age,pos,add_time) values("Joke",29,"manager",now());
insert into staffs(name,age,pos,add_time) values("Bob",28,"dev",now());
insert into staffs(name,age,pos,add_time) values("Rose",29,"dev",now());
alter table staffs add index index_name_age_pos(name,age,pos);
--索引失效,跳过了复合索引第一个属性name,全表扫描
explain select *from staffs
where age=28;
--索引部分失效,跳过了复合索引第二个属性age,索引只对name生效
explain select *from staffs
where name="Bob" and pos="dev";
--生效组合name,age,pos
-- name,age
--总之最左原则
--1.复合索引的第一个属性必须存在
--2.复合索引中间属性不能断
select *from staffs
where left(name,4)="Rose" ;
explain select *from staffs
where left(name,4)="Rose";
select *from staffs
where name="Rose" and age>20 and pos="dev";
explain select *from staffs
where name="Rose" and age>20 and pos="dev";
--pos列不能使用索引
select age,name,pos
where name="Rose" and age=29 and pos="dev";
explain select age,name,pos
where name="Rose" and age=29 and pos="dev";
explain select * from staffs
where name!="Rose";
--全表扫描
explain select * from staffs
where name is not null;
--null
explain select * from staffs
where name is null;
--可以使用覆盖索引解决%%
--下面是全表扫描
explain select * from staffs
where name like "%R_s%";
explain select * from staffs
where name like "%R_s";
explain select *from *from staffs
where age="28";
--假设插入了一个name为“39”的记录
explain select *from *from staffs
where name=39;
explain select *from staffs
where name="Rose" or name="Joke";
本文地址:https://blog.csdn.net/qq_42706464/article/details/107327565
如对本文有疑问, 点击进行留言回复!!
网友评论