环境

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'