MySQL的explain使用详解

最新推荐文章于 2024-06-11 19:20:38 发布

Prajna Sea

最新推荐文章于 2024-06-11 19:20:38 发布

阅读量2.2k

点赞数 27

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_42133326/article/details/136274683

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL中的explain命令用法，涉及查询执行顺序、查询类型、衍生表的作用以及如何通过索引优化查询性能。通过实例演示了如何解读explain结果并提供优化建议。

摘要由CSDN通过智能技术生成

1、explain的用法

在MySQL中，explain语句是一个极其重要的工具，它用于分析SQL查询的执行计划，帮助我们理解MySQL是如何处理我们的SQL语句，以及如何优化查询性能。下面是对MySQL explain 命令的基本用法：

explain 关键字通常放在select语句前，用于查看该查询的具体执行过程。

  # 数据表的设计结构和每张表的数据如下：
CREATE TABLE `goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `total_num` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

CREATE TABLE `goods_spec` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `goods_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `goods_id` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `goods_spec_price` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `price` decimal(10,2) NOT NULL,
  `num` int(11) NOT NULL,
  `goods_id` int(11) NOT NULL,
  `spec_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `goods_id` (`goods_id`),
  KEY `spec_id` (`spec_id`),
  CONSTRAINT `goods_id` FOREIGN KEY (`goods_id`) REFERENCES `goods` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `spec_id` FOREIGN KEY (`spec_id`) REFERENCES `goods_spec` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

#goods 表数据
INSERT INTO goods ( `title`, `total_num` ) VALUES ( '商品1', 100 ),( '商品2', 200 ),( '商品3', 300 );
#goods_spec 表数据
INSERT INTO goods_spec ( `name`, `goods_id` ) VALUES ( '商品1的规格', 1 ),( '商品2的规格', 2 ),( '商品3的规格', 3 );
#goods_spec_price 表数据
INSERT INTO goods_spec_price ( `price`, `num`, `goods_id`, `spec_id` ) VALUES ( 1, 100, 1, 1 ),( 2, 200, 2, 2  ),( 3, 300, 3, 3 );

# 执行语句
explain select * from product

执行结果如下：

2、结果解读

id
执行顺序编号，有以下几种情况：
- 顺序编号相同，从上往下执行。
- 顺序编号不同，序号大的先执行。
- 如果两种都存在，编号大的先执行，同级编号从上到下执行。
- 如果为NULL，则最后执行。用来表示结果集，并且不使用它来进行查询。
select_type
查询类型，有如下类型：
- SIMPLE（简单查询，执行的语句不涉及到子查询、UNION、DERIVED）
- PRIMARY（主查询，复杂查询中的最外层查询通常id为1）
- SUBQUERY（子查询，select语句中出现子查询，并且子查询不依赖外部结果）
```
EXPLAIN SELECT *,(SELECT name FROM goods_spec limit 1) FROM goods
```
- DEPENDENT SUBQUERY（select 语句中出现子查询，并且子查询依赖外部查询结果）
```
EXPLAIN SELECT *,(SELECT name FROM goods_spec WHERE goods_id = goods.id) FROM goods WHERE id = 1
```
- DERIVED
  
  在MySQL中，衍生表（Derived Table）是指在查询语句内部定义的临时结果集，它由一个子查询生成并用作外部查询的FROM子句中的数据源。衍生表是虚拟存在的，仅在查询执行过程中存在，并且随着查询结束而消失。
  衍生表的主要作用包括：
  简化复杂查询：当查询逻辑包含多层嵌套或需要对中间结果进行进一步处理时，可以通过创建衍生表来简化表达复杂的SQL操作，使查询结构更清晰、易于理解和维护。
  分阶段计算结果：将一部分复杂的查询或者计算先放在子查询中完成，然后将结果作为新的数据源继续参与其他JOIN、WHERE等操作。
  优化性能：虽然早期版本的MySQL可能因为衍生表没有索引而导致性能问题，但现代版本（如5.6及以后）的优化器能够根据需要为派生表创建临时索引，从而提高查询效率。
  避免重复计算：如果多个地方需要用到同一部分计算结果，可以将这部分结果封装成一个衍生表，防止多次计算浪费资源。
- UNION（语句分为两种：union、union all，两者的作用都是将结果集进行拼接，区别在于union会进行数据去重复，而union all不会）
- UNION RESULT（语句中使用了union的时候）
- DEPENDENT UNION（将union用在子查询中）
```
EXPLAIN SELECT * FROM goods_spec WHERE id IN (SELECT id FROM goods WHERE id < 2 UNION SELECT goods_id FROM goods_spec_price WHERE goods_id < 2)
```
tabel
查询的表名称
partitions
表分区情况
type
查询访问类型，如system，const，ref，eq_ref，range，index，ALL，NULL
查询效率从高到底分别为：system > const > rq_ref > ref > range > index > ALL
- system（表只有一行记录时，并且该表使用的存储引擎统计数据是精确的时候）
  
  InnoDB（其统计数据（包括TABLE_ROWS）是基于采样和估算的，并非总是精确的）
```
 CREATE TABLE `test2` (`id` INT) ENGINE = INNODB;
 INSERT INTO test2 VALUES (1);
 EXPLAIN SELECT * FROM test2
```
  MyISAM（TABLE_ROWS字段的统计是相对准确的，因为它会在每次插入、删除或更新行时动态更新这个计数器）
```
  CREATE TABLE `test1` (`id` INT) ENGINE = MyISAM;
  INSERT INTO test1 VALUES (1);
  EXPLAIN SELECT * FROM test1
```
- cons（常量查询，当主键作为查询条件时出现）
- eq_ref（唯一性索引扫描，常用于主键关联或唯一索引）
- ref（非唯一性索引扫描，常用于二级索引关联或二级索引作为查询条件）
- range（范围扫描）
- index（索引全扫描，常用于查询的字段建立了索引或覆盖索引，须优化缩小数据范围）
- ALL（全表扫描，须优化建立索引并缩小数据范围）
- NULL（不访问任何表或索引）
possible_keys
可能使用到的索引，如果为NULL，则没有选择使用索引
key
实际使用到的索引，如果为NULL，则最终没有使用索引
key_len
使用的索引长度，计算规则如下：
- 字符串类型：
  
  char(n): n个字节
  
  varchar(n)：如果是utf-8格式（3n+2）字节，加的两个存储字符串的长度。如果是utf8mb4格式（4n+2）字节
- 数值类型：
  
  tinyint: 1个字节
  
  smaillint: 2个字节
  
  int: 4个字节
  
  bigint: 8个字节
- 时间类型:
  
  date: 3个字节
  
  timestamp: 4个字节
  
  datetime: 8个字节
- 字段如果允许为NULL，还需要1个字节记录是否为NULL
```
CREATE TABLE `goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `total_num` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `title_total_num` (`title`,`total_num`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

EXPLAIN SELECT * FROM goods WHERE title = '商品1' and total_num = 100
```
上面的示例中key_len的计算方式：`title` varchar(255) + `total_num` int(11) 按照计算规则最终结果为：(255 * 4 + 2) + 4 = 1026
ref
列与索引的关联，显示哪个字段或常量被用于查找索引列
rows
预计扫描的行数（索引行数或表记录行数）值越小越好，注意这个行数不是返回的结果集行数。
filtered
按表条件过滤后，存储引擎返回的数据行占比
extra
额外信息
- Using index（覆盖索引）
- Using where（使用了WHERE条件过滤）
- Using index conditon（使用了二级索引，但是需要进行回表查询）
```
EXPLAIN SELECT * FROM goods_spec_price WHERE goods_id > 1
```
- Using filesort（需要排序操作，可以优化为走索引也就是Using index）
- Using temporary（使用了临时表，可以优化为走索引也就是Using index）
```
EXPLAIN SELECT DISTINCT NAME FROM goods_spec
```
```
#添加索引后
CREATE INDEX index_name ON goods_spec(name);
EXPLAIN SELECT DISTINCT NAME FROM goods_spec
```
- Select tables optimized away（MySQL优化器在解析查询后发现部分查询或者子查询实际上不需要从表中读取任何数据即可得出结果）