explain extra字段

环境

drop table if exists user;
create table user(
	id int primary key not null,
	roleId int not null,
	name varchar(20) not null,
	age int not null,
	sex char(10) not null,
	hobby varchar(30) 
);
insert into user values (1,1001,'xz1',1,'man','read1'),
(2,1002,'xz2',2,'man','read2'),(3,1003,'xz3',3,'man','read3'),
(4,1004,'xz4',4,'man','read4'),(5,1005,'xz5',5,'man','read5');

-- 联合索引
create index inx_roleId_name_age_sex on user(roleId,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_roleId_name_age_sex,key_len=4(roleId索引生效)
explain select * from user where roleId=1001;

-- type=ref,extra=null,key=inx_roleId_name_age_sex,key_len=70(roleId、name和age索引生效)
explain select * from user where roleId=1 and name='xz1' and age=1;

-- type=ref,extra=null,key=inx_roleId_name_age_sex,key_len=100(索引全部生效)
explain select * from user where roleId=1001 and name='xz1' and age=1 and sex='man';

延伸：违反最左前缀法则

违反最左前缀法则

-- type=all,extra=Using where,key=null,key_len=null
explain select * from user where name='xz1';
-- type=all,extra=Using where,key=null,key_len=null
explain select * from user where age=1;
-- type=all,extra=Using where,key=null,key_len=null
explain select * from user where sex='man';
-- type=all,extra=Using where,key=null,key_len=null
explain select * from user where age=1 and sex='man';
-- type=all,extra=Using where,key=null,key_len=null
explain select * from user where name='xz1' and age=1 and sex='man';
-- ......
-- ......
-- ......

Using index

1、查询列索引完全覆盖
2、where索引的前导列

-- type=ref,extra=Using index,key=inx_roleId_name_age_sex,key_len=66(roleId和name生效)
explain select name,age,sex from user where roleId=1 and name='xz1';

-- type=ref,extra=Using index,key=inx_roleId_name_age_sex,key_len=70(roleId、name和age索引生效)
explain select name,age,sex from user where roleId=1 and name='xz1' and age=1;

-- type=ref,extra=Using index,key=inx_roleId_name_age_sex,key_len=100(索引全部生效)
explain select name,age,sex from user where roleId=1 and name='xz1' and age=1 and sex='man';

Using where; Using index

where索引列之一但不是前导列

1、查询列索引完全覆盖
2、where索引列之一但不是前导列

-- type=ref,extra=Using where; Using index,key=inx_name_age_sex,key_len=96
explain select name,age,sex from user where age=1;

-- type=ref,extra=Using where; Using index,key=inx_name_age_sex,key_len=96
explain select name,age,sex from user where sex='man';

-- type=ref,extra=Using where; Using index,key=inx_name_age_sex,key_len=96
explain select name,age,sex from user where age=1 and sex='man' ;

where索引列前导列的一个范围

1、查询列未被索引覆盖，需要回表
2、where索引列前导列的一个范围

-- type=index,extra=Using where; Using index,key=inx_roleId_name_age_sex,key_len=100
explain select roleId,name,age,sex from user where roleId>=1001 androleId<=1005;

-- type=index,extra=Using where; Using index,key=inx_roleId_name_age_sex,key_len=100
explain select roleId,name,age,sex from user where roleId between 1001 and 1005;

Using where

where筛选条件不是索引列,和查询列是否完全索引覆盖无关

1、和查询列是否完全索引覆盖无关
2、where筛选条件不是索引列

-- type=all,extra=Using where,key=null,key_len=null
explain select * from user where hobby='read1';

-- type=all,extra=Using where,key=null,key_len=null
explain select roleId,name,age,sex from user where hobby='read1';

where筛选条件不是索引前导，查询的列未被索引覆盖

1、查询的列未被索引覆盖
2、where筛选条件不是索引前导

-- type=all,extra=Using where,key=null,key_len=null
explain select * from user where sex='man';

where筛选条件是索引列前导列的一个范围，查询的列未被索引覆盖

1、查询的列未被索引覆盖
2、索引列前导列的一个范围

-- type=all,extra=Using where,key=null,key_len=null
explain select * from user where roleId>=1001 and roleId<=1009;

-- type=all,extra=Using where,key=null,key_len=null
explain select roleId,name,age,sex from user where roleId>=1001 and roleId<=1009;

Using index condition

where索引列前导列的一个范围（<、between） 查询列未被索引覆盖

1、查询列未被索引覆盖
2、where索引列前导列的一个范围（<、between）

-- type=range,extra=Using index condition,key=inx_roleId_name_age_sex,key_len=4
explain select * from user where roleId>1001 and roleId<1004;

断裂式前导列

1、查询列未被索引覆盖，需要回表
2、where索引的前导列（断裂式前导列）

-- type=ref,extra=Using index condition,key=inx_roleId_name_age_sex,key_len=4(roleId索引生效)
explain select * from user where roleId=1 and sex='man';

-- type=ref,extra=Using index condition,key=inx_roleId_name_age_sex,key_len=4(roleId索引生效)
explain select * from user where roleId=1 and age=1;

-- 警告：hobby不是索引列
-- type=ref,extra=Using where,key=inx_roleId_name_age_sex,key_len=4(roleId索引生效)
explain select * from user where roleId=1 and hobby='read1';

MySQL-explain-extra字段详解