mysql explain详解

最新推荐文章于 2023-05-29 10:13:19 发布

Brain_L

最新推荐文章于 2023-05-29 10:13:19 发布

阅读量602

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/weixin_39120845/article/details/105365116

版权

Mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

定义

在查询语句前加上explain，Mysql就不会去执行查询语句，而是返回sql语句的执行计划，帮助我们查看索引是否真正使用等重要信息。

测试表

CREATE TABLE `primary_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `c1` bigint(20) unsigned NOT NULL COMMENT 'c1',
  `c2` varchar(20) DEFAULT NULL COMMENT 'c2',
  `c3` varchar(20) DEFAULT NULL COMMENT 'c3',
  `c4` varchar(20) DEFAULT NULL COMMENT 'c4',
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_C1` (`c1`) USING BTREE,
  KEY `IDX_C2` (`c2`) USING BTREE,
  KEY `IDX_C3` (`c3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `second_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

字段

id：select序列号

EXPLAIN SELECT * FROM primary_table WHERE id = (SELECT id FROM second_table WHERE name = 'mysql');

在这里插入图片描述
有几个select就有几个id，代表了执行顺序，相同id，从上到下顺序执行，不同id，id大的先执行。

select_type：查询类型

simple：最简单的查询，不包含子查询和union

EXPLAIN SELECT * FROM primary_table WHERE id = 1;

在这里插入图片描述

primary：最外层对应的select
derived：from子句中子查询
subquery：除了上面两种的子查询，即select、where中的子查询

EXPLAIN SELECT id FROM (SELECT id FROM primary_table LIMIT 5 ) AS T1 WHERE T1.id = (SELECT id FROM second_table WHERE name = 'mysql');

在这里插入图片描述
where中的子查询id为3，最先被执行。FROM后的子查询id为2，生成的表即为最外层查询所使用的表，所以id为1的的那行table为derived2，即id为2生成的表。

union：union后的select查询
union result：union联合后的结果

EXPLAIN SELECT id FROM primary_table UNION SELECT id FROM second_table WHERE name = 'mysql';

在这里插入图片描述

table：使用的表

简单的情况是明确的某张表，如primary_table。如果是derivedN，则表示使用id为N所生成的表。如果是unionN,M，表示使用id为N和M生成的联合表。

partitions：使用的分区

由于本次测试表没有建立分区，所以partitions都为null

type：连接类型

这个字段表明了查询的连接类型，可以看出是否使用索引、是否全表扫描等信息，需要重点关注。
type有多种类型，性能从优到差依次为：
null > system/const > eq_ref > ref > ref_or_null >index_merge > range > index > all

null：优化过程即可得到结果，不需要扫表，性能最好

EXPLAIN SELECT max(id) FROM primary_table

在这里插入图片描述

const/system：最多只有一行数据

EXPLAIN SELECT * FROM primary_table WHERE id = 3;

在这里插入图片描述
使用主键或者唯一索引进行查找的时候会出现这种情况。MySQL可以将这种查询优化成常量。

eq_ref：非简单查询的时候，使用了主键或唯一索引

EXPLAIN SELECT * FROM primary_table T0, second_table T1 WHERE T0.id = T1.age;

在这里插入图片描述

ref：使用了普通索引（非唯一索引），结果可能有多行

EXPLAIN SELECT * FROM primary_table WHERE c2 = '';

在这里插入图片描述

ref_or_null：使用了普通索引（非唯一索引），结果可能含有空行

EXPLAIN SELECT c4 FROM primary_table WHERE c2 = 'zgyq' or c2 is null;

在这里插入图片描述

index_merge：使用了多个索引，发生了索引合并

EXPLAIN SELECT * FROM primary_table WHERE c2 = 'zgyq' or c3 = 'teyi'

在这里插入图片描述
使用了idx_c2和idx_c3两个索引分别进行扫描，然后对结果进行合并。

range：在索引上进行范围扫描，如in、<>、<=、>=、between、in等操作

EXPLAIN SELECT * FROM primary_table WHERE c1 > 5;

在这里插入图片描述
注意，不是所有的范围扫描都是range，当查询条件扫描出的行数和全表扫描的行数相差不大时，mysql会使用全表扫描而不再走索引。比如

EXPLAIN SELECT * FROM primary_table WHERE c1 < 5;

在这里插入图片描述
此时就没有走索引，而是直接全表扫描。

index：查询的字段，通过扫描索引树即可获得

EXPLAIN SELECT c1 FROM primary_table;

在这里插入图片描述

all：光搜索索引树无法获得全部数据，需要进行全表扫描

EXPLAIN SELECT c1, c2 FROM primary_table;

在这里插入图片描述
c1和c2虽然单独都建了索引，但是走任何一个索引都无法获得全部数据，只能进行全表扫描。这种情况就需要考虑建立c1、c2联合索引。

possible_keys：可能使用到的索引

mysql推断本次查询可能使用到的索引有哪些。
如果全表扫描，则possible_key为null，表明不会使用索引。
如果使用了主键，则possible_key中会有primary。
此处为null或者有过多索引，就要考虑优化索引了。

key：实际使用的索引

possible_keys只是列出了可能的索引，优化器会根据统计信息中的字段值的分布，使用区分度更好的索引。也可能发生索引合并的情况，key中会出现不止一个索引，具体见上面type为index_merge的情况。

key_len：索引使用的字节数

表明索引真正使用的字节数，注意此处指的是表定义中的字节数，并不是字段真正占用的字节数。

类型	字节数
tinyint	1
smallint	2
int	4
bigint	8
char(n)	n
varchar(n)	2字节存储字符串长度，如果是utf-8，则长度 3n + 2
date	3
timestamp	4
datetime	8

如果字段允许为NULL，额外需要1字节记录是否为NULL。

EXPLAIN SELECT c1 FROM primary_table;

在这里插入图片描述
因为c1为bigint，所以这里key_len为8.

EXPLAIN SELECT c2 FROM primary_table;

在这里插入图片描述
c2为varchar(20)，所以key_len为3*20+2+1(可以为NULL)=63.

ref：key列所选择的索引的查找方式

常见的值有 const, func, NULL, 具体字段名。
如果key为NULL，则ref为NULL。

EXPLAIN SELECT * FROM primary_table WHERE c1 = 2;

在这里插入图片描述
此时表示索引IDX_C1是和常量2一起进行数据查询的。

EXPLAIN SELECT * FROM primary_table T0, second_table T1 WHERE T0.c1 = T1.id;

在这里插入图片描述
此时表示索引IDX_C1是和T1.id一起进行数据查询。

rows：可能读取的行数

mysql为了得到最终结果可能需要读取的行数，是个估计值，并不是最终结果的行数。

filtered：过滤百分比

按条件过滤的估计百分比，最大为100，表示没有过滤。rows × filtered为预估过滤的行数。

extra：额外信息

这里的信息有很多种，在此只列出常见的几种。

Using index：使用覆盖索引
查询的字段通过索引树即可获得，无需再回表查询

EXPLAIN SELECT id, c2 FROM primary_table WHERE c2 = '2222'

在这里插入图片描述

Using where：数据过滤

EXPLAIN SELECT * FROM primary_table WHERE c4 = '444'

在这里插入图片描述
c4没有建立索引，type为ALL，进行全表扫描，取出行数据后，过滤满足where条件的数据。

Using where & Using index：使用索引，并且需要过滤

EXPLAIN SELECT c2 FROM primary_table WHERE c2 >'2222' and c2 < '3333'

在这里插入图片描述
使用覆盖索引IDX_C2，所以有Using index。同时需要进行数据过滤，所以有Using where

Using index condition：索引条件下推
索引条件下推(Index Condition Pushdown，ICP)：筛选字段在索引中的where条件从server层下推到storage engine层，这样可以在存储引擎层过滤数据。

EXPLAIN SELECT * FROM primary_table WHERE c2 >'2222' and c2 < '3333'

在这里插入图片描述
这里的sql和上面的sql不同的地方是查询的*而不是c2，需要回表查询。满足ICP条件，在引擎层去过滤c2>‘2222’ and c2<'3333’的数据。

Using filesort：需要额外的排序操作
如对非索引字段进行排序

EXPLAIN SELECT c4 FROM primary_table ORDER BY c4

在这里插入图片描述

Using temporary：使用临时表

EXPLAIN SELECT DISTINCT c4 FROM primary_table

在这里插入图片描述
c4没有建立索引，所以使用了临时表。如果是

EXPLAIN SELECT DISTINCT c2 FROM primary_table

则是Using index。

参考

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
https://www.cnblogs.com/butterfly100/archive/2018/01/15/8287569.html
https://www.cnblogs.com/wangfengming/articles/8275448.html
https://www.cnblogs.com/thrillerz/p/4166720.html

Brain_L

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql explain详解

定义在查询语句前加上explain，Mysql就不会去执行查询语句，而是返回sql语句的执行计划，帮助我们查看索引是否真正使用等重要信息。测试表CREATE TABLE `primary_table` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `c1` bigint(20) unsigned NO...
复制链接

扫一扫