深入了解 MySQL 的 EXPLAIN 命令


一、什么是 EXPLAIN 命令?

EXPLAIN 命令用于显示 MySQL 如何执行某个 SQL 语句,尤其是 SELECT 语句。通过 EXPLAIN 命令,可以看到查询在实际执行前的执行计划,这对于优化查询性能至关重要。

二、EXPLAIN 的基本用法

要使用 EXPLAIN 命令,只需在你的 SELECT 语句前加上 EXPLAIN 关键字即可。例如:

EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

执行上述命令后,MySQL 会返回一个结果集,包含关于查询执行计划的详细信息。下面我们逐一解释这些信息。

三、EXPLAIN 结果各列的含义

EXPLAIN 命令的结果集通常包含以下几列:

  • id
  • select_type
  • table
  • partitions
  • type
  • possible_keys
  • key
  • key_len
  • ref
  • rows
  • filtered
  • Extra

id

id 列表示查询中每个 SELECT 子句的标识符。单个查询的 id 值通常是 1,子查询和联合查询的 id 值可能不同。

select_type

select_type 列表示 SELECT 的类型,常见的值有:

  • SIMPLE:简单的 SELECT 查询,不包含子查询或联合查询。
  • PRIMARY:最外层的 SELECT 查询。
  • UNION:UNION 中的第二个或后续的 SELECT 查询。
  • DEPENDENT UNION:UNION 中的第二个或后续的 SELECT 查询,依赖于外部查询。
  • UNION RESULT:UNION 的结果。
  • SUBQUERY:子查询中的第一个 SELECT。
  • DEPENDENT SUBQUERY:子查询中的第一个 SELECT,依赖于外部查询。
  • DERIVED:派生表(子查询的 FROM 子句)。

table

table 列表示正在访问的表的名称。

partitions

partitions 列表示查询涉及到的分区。如果表是分区表,此列将显示实际访问的分区。如果没有使用分区,该列显示 NULL

type(重点)

type 列表示连接类型(join type),反映了 MySQL 在执行查询时使用的访问方法。连接类型从最优到最差依次如下:

  • system:表仅有一行(等于系统表),这是 const 类型的特例。
  • const:表最多有一个匹配行,这是非常快速的,因为匹配行在优化阶段就读取出来了。使用索引一般是一般是 唯一索引 或 主键索引。
  • eq_ref:对于每个来自前一张表的行组合,读一行,这是最理想的连接类型。连接字段,使用索引一般是 唯一索引 或 主键索引。
  • ref:对于每个来自前一张表的行组合,读出所有匹配某个单独值的行。使用索引一般是普通索引。
  • range:检索给定范围内的行,使用一个索引来选择行。
  • index:全索引扫描(与全表扫描类似,但遍历索引树)。
  • ALL:全表扫描。

possible_keys

possible_keys 列表示查询可能使用的索引。

key(重点)

key 列表示实际使用的索引。如果没有选择索引,显示 NULL

key_len

key_len 列表示使用的索引键的长度。这个值是 MySQL 决定使用哪个索引时考虑的。

ref

ref 列表示使用哪个列或常量与 key 一起从表中选择行。

rows

rows 列表示 MySQL 估计为了找到所需的行,需要读取的行数。

filtered

filtered 列表示经过表条件过滤后返回的行数百分比。这个值表示剩余行数相对于读取的行数的百分比。计算公式为:filtered = (满足表条件的记录数 / 该表的总记录数) * 100%。

注意如果使用索引查询,那么 MySQL 可能不会扫全表,直接查出索引中返回的数据,filtered 会是 100。

Extra

Extra 列包含关于查询的详细信息,可能的值有:

  • Using index:只使用索引信息而不读取实际的行(覆盖索引)。
  • Using where:使用 WHERE 子句来限制哪些行将与下一张表匹配或返回给用户。
  • Using temporary:需要使用临时表来存储结果。
  • Using filesort:需要额外的传递来排序结果。

四、EXPLAIN 命令 type 字段 SQL 测试

4.1、const 类型测试

-- const 类型测试
drop table if exists user;
create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

explain
select *
from user
where id = 1;

image-20240713155353388

4.2、eq_ref 类型测试

-- eq_ref 类型测试
drop table if exists user_balance;
drop table if exists user;

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

create table user_balance (
  uid int primary key,
  balance int
)engine=innodb;

insert into user_balance values(1,100);
insert into user_balance values(2,200);
insert into user_balance values(3,300);
insert into user_balance values(4,400);

explain
select *
from user
         left join user_balance on user.id = user_balance.uid
where user.id = user_balance.uid;

image-20240713155429390

4.3、ref 类型测试

-- ref 类型测试
drop table if exists user_balance;
drop table if exists user;

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

create table user_balance (
  uid int,
  balance int,
  index(uid)
)engine=innodb;

insert into user_balance values(1,100);
insert into user_balance values(2,200);
insert into user_balance values(3,300);
insert into user_balance values(4,400);
insert into user_balance values(5,500);

explain
select *
from user
         left join user_balance on user.id = user_balance.uid
where user.id = 1;

explain select * from user_balance where uid = 1;

image-20240713155508580

4.4、range 类型测试

-- range 类型测试
drop table if exists user;

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

explain
select *
from user
where id between 1 and 2;

explain
select *
from user
where id in (1, 2, 3);

explain
select *
from user
where id > 1;

image-20240713155627248

4.5、index 类型测试

-- index 类型测试
drop table if exists user;

create table user (
  id int primary key,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');

explain
select count(1)
from user;

image-20240713155701062

4.6、ALL 类型测试

-- ALL 类型测试
drop table if exists user;

create table user (
  id int,
  name varchar(20)
)engine=innodb;

insert into user values(1,'ar414');
insert into user values(2,'zhangsan');
insert into user values(3,'lisi');
insert into user values(4,'wangwu');
insert into user values(5,'zhaoliu');

explain
select *
from user
where id = 1;

image-20240713155750163


文章作者: xiucai
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 xiucai !
  目录