环境
drop table if exists user;
create table user(id int primary key not null,name varchar(20) not null,age int not null,sex char(10) not null,hobby varchar(30)
);
insert into user values (1,'xz1',1,'man','read1'),
(2,'xz2',2,'man','read2'),(3,'xz3',3,'man','read3'),
(4,'xz4',4,'man','read4'),(5,'xz5',5,'man','read5');
-- 联合索引
create index inx_name_age_sex on user(name,age,sex);
null
where 主键索引
- 主键查询不回表，因为需要字段就是从聚簇索引上查找
-- type=const,extra=null,key=PRIMARY,key_len=4
explain select name from user where id=1;
where 索引的前导列
1、查询列未被索引覆盖，需要回表
2、where索引的前导列
-- type=ref,extra=null,key=inx_name_age_sex,key_len=62(name索引生效)
explain select * from user where name='xz1';-- type=all,extra=Using where,key=inx_name_age_sex,key_len=96(索引全部生效)
explain select * from user where name='xz1' and age=1 and sex='man';
延伸：违反最左前缀法则
-- type=ref,extra=null,key=null,key_len=null
explain select * from user where age=1;
-- type=ref,extra=null,key=null,key_len=null
explain select * from user where sex='man';
-- type=ref,extra=null,key=null,key_len=null
explain select * from user where age=1 and sex='man';
-- type=ref,extra=null,key=inx_name_age_sex,key_len=66(name和age索引生效)
explain select * from user where age=1 and name='xz1'
