深入了解 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;

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;

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;

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;

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;

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;