mysql中explain命令详解

前言

我们可以使用 explain 命令来查看 SQL 语句的执行计划，从而帮助我们优化慢查询。

使用

注意：使用的 mysql 版本为 8.0.28

数据准备

CREATE TABLE `tb_product2` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `name` varchar(20) DEFAULT NULL COMMENT '商品名称',
  `en_name` varchar(20) DEFAULT NULL COMMENT '商品英文名称',
  `stock` int DEFAULT NULL COMMENT '库存量',
  PRIMARY KEY (`id`),
  index `index_name`(`name`)
) ENGINE=InnoDB;

CREATE TABLE `tb_order2` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `product_id` bigint DEFAULT NULL COMMENT '商品ID',
  `quantity` int DEFAULT NULL COMMENT '购买数量',
  `price` decimal(10,2) DEFAULT NULL COMMENT '订单总金额',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `tb_product2`( `name`, `en_name` , `stock` ) VALUES('苹果11', 'iphone11', 10); 
INSERT INTO `tb_product2`( `name`, `en_name` , `stock` ) VALUES('小米6', 'xiaomi6', 20); 
INSERT INTO `tb_order2`( `product_id`, `quantity` , `price`, `create_time`) VALUES(1, 5, 100.00, now()); 
INSERT INTO `tb_order2`( `product_id`, `quantity` , `price` , `create_time`) VALUES(2, 3, 60.00, now()); 

查询执行计划

EXPLAIN SELECT * FROM `tb_product2` WHERE id = 1;

结果为

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tb_product2	null	const	PRIMARY	PRIMARY	8	const	1	100.00	null

EXPLAIN SELECT * FROM `tb_product2` WHERE en_name = 'xiaomi6';

结果为

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tb_product2	null	ALL	null	null	null	null	2	50.00	Using where

字段详解

id

每次select查询都会对应一个id，它代表着SQL执行的顺序，如果id值越大，说明对应的SQL语句执行的优先级越高。

select_type

表示执行计划对应的查询类型，常见的查询类型主要包括普通查询、联合查询以及子查询等。

• simple： 简单的select查询，没有union或者子查询
• primary: 有嵌套查询时的最外层的select查询
• derived: 用来表示包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为是Derived table(派生表)，因为该临时表是从子查询派生出来的
• union: union中的第二个或随后的select查询，不依赖于外部查询的结果集
• dependent union: union中的第二个或随后的select查询，依赖于外部查询的结果集
• subquery: 子查询中的第一个select查询，不依赖与外部查询的结果集
• dependent subquery: 子查询中的第一个select查询，依赖于外部查询的结果集

table

表示要查询哪张表，当然不一定是真实的表的名称，也可能是表的别名或者临时表。

partitions

表示在进行查询时,如果对应的表存在分区表，那么这里就会显示具体的分区信息。

type

type是非常核心的属性，需要重点掌握。它表示的是当前通过什么样的方式对数据库表进行访问。

• system: 该表只有一行（相当于系统表），数据量很小，查询速度很快，system是const类型的特例。
  
• const: 在进行数据查询的时候，命中了primary key或唯一索引，此类数据查询速度非常快。
• eq_ref: 对于每个来自于前面的表的行组合，从该表中读取一行，常用在一个索引是unique key或者primary key。
• ref: 数据查询的时候如果命中的索引是二级索引不是唯一索引，测试查询速度也会很快，但是type是ref。另外如果是多字段的联合索引，那么根据最左匹配原则，从联合索引的最左侧开始连续多个列的字段进行等值比较也是ref的类型。
• ref_or_null: 类似于 ref，区别在于 MySQL会额外搜索包含NULL值的行。
• unique_subquery: 在where条件中的关于in的子查询条件集合。
• index_subquery: 区别于unique_subquery，用于非唯一索引，可以返回重复值。
• range: 使用索引进行行数据检索，只对指定范围内的行数据进行检索。换句话说就是针对一个有索引的字段，在指定范围中检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。
  
• all: 遍历全表进行数据匹配，此时的数据查询性能最差。
  
• index: index 与 all 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。

possible_keys

表示哪些索引可以被 MySQL 的优化器进行选择，也就是索引候选者有哪些。

key

最终选择使用的索引。

key_len

表示索引的长度，和实际的字段属性以及是否为null都有关系。

ref

当使用字段进行常量等值查询时ref此处为const，当查询条件中使用了表达式或者函数则ref显示为func，其他的显示为null。

rows

表示 MySQL 认为它执行查询时必须检查的行数。行数越少，效率越高。

filtered

这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下的满足条件的记录数量的比例。

extra

在其他列不显示额外信息在此列进行展示。

• Using index: 在进行数据查询的时候，数据库使用了覆盖索引，就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快。
  
• Using where: 查询时未找到可用的索引，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。
• Using temporary: 表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。
• Using filesort: 此类型表示无法利用索引完成指定的排序操作，也就是ORDER BY的字段实际没有索引，因此此类SQL是需要进行优化的。

参考

Mysql的explain，你真的会用吗？
MySQL优化之EXPLAIN命令解析
全网最全 | MySQL EXPLAIN 完全解读
Mysql中key 、primary key 、unique key 与index区别