前言
我们可以使用 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区别