MySQL的EXPLAIN执行计划深入分析

已于 2022-10-07 10:51:05 修改
阅读量978 收藏 11
点赞数 5
分类专栏： mysql 文章标签： 数据库 mysql sql
于 2022-09-12 22:45:38 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010132847/article/details/126823521
版权

目录

一、EXPLAIN介绍

二、准备数据

三、重要的属性介绍

3.1、select_type——查询类型

SIMPLE：表示查询语句不包含子查询或union。

PRIMARY： 查询中若包含任何复杂的子查询，最外层查询则被标记为Primary。

UNION：表示此查询是UNION的第二个或后续的查询。在union, union all的第二个以及随后的select被标记为union。

DEPENDEN UNION：UNION中的第二个或后续的查询语句，使用到了外面查询结果。

UNION RESULT：从UNION表中获取的结果。

SUBQUERY：SELECT子查询语句。

DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

3.2、Type——表示存储引擎查询数据时采用的方式

        ALL：表示全表扫描，性能最差。

        Index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。

        range：表示使用索引范围查询， 使用>、 >=、 <=、between...and、 in等等。

        ref: 表示使用非唯一索引进行单值查询。

        eq_ref：一般情况下出现在多表join查询，表示前面表的记录，存在与后面表的数据有关联，存在关联关系。

        const：表示使用主键或唯一索引做等值查询，也叫常量查询。

        null：表示不用访问表，直接出结果，速度最快。

3.3、possible_keys——表示查询时能够使用到的索引

3.4、key——表示查询时真正使用到的索引

3.5、rows——估算SQL要查询到结果需要扫描多少行记录

3.6、key_len——表示查询使用了索引的字节数量

3.7、Extra——表示额外的信息

四、参考资料

一、EXPLAIN介绍

MySQL提供了一个EXPLAIN命令，它可以对SELECT语句进行分析，并输出SELECT执行的详细信息，供开发人员有针对性的优化。例如：

EXPLAIN select * from user where id < 3;

说明：

  1.  5.6版本如果想显示filtered,则需要使用explain extended，本次演示的是5.7版本。
  2. 查看版本使用 select version()。
  3. 语句右面的“\G”是未来在cmd界面竖列展示。

二、准备数据

-- ----------------------------

-- Table structure for persion

-- ----------------------------

DROP TABLE IF EXISTS `persion`;

CREATE TABLE `persion`  (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

  `age` int(11) NULL DEFAULT NULL,

  `is_delete` tinyint(4) NULL DEFAULT 0 COMMENT '0：正常 1:已废弃',

  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,

  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`) USING BTREE,

  INDEX `idx_age`(`age`) USING BTREE,

  INDEX `idx_name`(`name`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of persion

-- ----------------------------

INSERT INTO `persion` VALUES (1, '张三', 20, 0, '2022-09-12 14:22:12', '2022-09-12 14:24:36');

INSERT INTO `persion` VALUES (2, '李四', 19, 0, '2022-09-12 14:23:36', '2022-09-12 14:23:36');

INSERT INTO `persion` VALUES (3, '王五', 20, 0, '2022-09-12 14:23:43', '2022-09-12 14:23:43');

INSERT INTO `persion` VALUES (4, '赵六', 21, 0, '2022-09-12 14:23:51', '2022-09-12 14:23:51');

INSERT INTO `persion` VALUES (5, '孙七', 20, 0, '2022-09-12 14:24:13', '2022-09-12 14:24:31');

INSERT INTO `persion` VALUES (6, '周八', 22, 0, '2022-09-12 14:24:23', '2022-09-12 16:00:16');

三、重要的属性介绍

3.1、select_type——查询类型

常用的值如下：

  • SIMPLE：表示查询语句不包含子查询或union。

示例语句如：explain select * from persion

  • PRIMARY： 查询中若包含任何复杂的子查询，最外层查询则被标记为Primary。

示例语句如：explain select * from persion where id = 1

                  Union

                  Select * from persion where id = 2;

  • UNION：表示此查询是UNION的第二个或后续的查询。在union, union all的第二个以及随后的select被标记为union。

示例语句如：explain select * from persion where id = 1

                  Union

                  Select * from persion where id = 2;

  • DEPENDEN UNION：UNION中的第二个或后续的查询语句，使用到了外面查询结果。

在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。

示例语句如：

explain select * from persion where id in(

select id from persion where age =19  

union

select id from persion where  age =20

union

select id from persion where age=21);

  • UNION RESULT：从UNION表中获取的结果。

示例语句如：

explain select * from persion where age= 18

Union

Select * from persion where age=19;

  • SUBQUERY：SELECT子查询语句。

示例语句如：

explain select * from persion where age = (select max(age) from persion);

  • DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

示例语句如：

 explain select * from persion p1 where id =(select max(p2.id) from persion p2 where p1.age=20 );

最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没有用到UNION查询。

3.2、Type——表示存储引擎查询数据时采用的方式
 

type是比较重要的一个属性，通过它可以判断出是全表扫描还是基于索引的部分扫描。

常用属性值如下，从上至下效率依次增强。

        ALL：表示全表扫描，性能最差。

语句如：explain select * from persion;

        Index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。

语句1如：explain select * from persion oder by id;

 语句2如：explain select * from persion where age = 18 order by id desc;
注意：如果使用的条件没有建立索引，则type是all。

        range：表示使用索引范围查询， 使用>、 >=、 <=、between...and、 in等等。

语句1如：explain select * from persion where id > 1;

语句2如：explain select * from persion where id between 2 and 5;

        ref: 表示使用非唯一索引进行单值查询。

前提，我们为age字段建立一个普通索引。
语句1如：explain select * from persion where age = 22;

语句2如：此时如果查询age不能22，有多个的话，则type是ALL。

explain select * from persion where age != 22;

explain select * from persion where age = 20;

        eq_ref：一般情况下出现在多表join查询，表示前面表的记录，存在与后面表的数据有关联，存在关联关系。

前提：准备另一张表car及数据:

CREATE TABLE `car`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `persion_id` int(11) NULL DEFAULT NULL,
  `car_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `car_price` decimal(10, 2) NULL DEFAULT NULL,
  `is_delete` tinyint(4) NULL DEFAULT 0,
  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of car
-- ----------------------------
INSERT INTO `car` VALUES (1, 1, '五菱神车', 5.00, 0, '2022-09-12 15:58:10', '2022-09-12 15:58:10');
INSERT INTO `car` VALUES (2, 2, '哈弗h6', 8.00, 0, '2022-09-12 15:58:26', '2022-09-12 15:58:26');
INSERT INTO `car` VALUES (3, 3, '吉利CS75', 10.00, 0, '2022-09-12 15:58:48', '2022-09-12 15:58:48');
INSERT INTO `car` VALUES (4, 4, '欧尚Z6', 8.00, 0, '2022-09-12 15:59:13', '2022-09-12 15:59:13');
INSERT INTO `car` VALUES (5, 5, '宋PLUS-DMI', 16.00, 0, '2022-09-12 15:59:46', '2022-09-12 15:59:46');
INSERT INTO `car` VALUES (6, 6, '汉', 23.00, 0, '2022-09-12 16:00:07', '2022-09-12 16:00:07');

 语句如：explain select * from persion p, car c where p.id=c.persion_id;

        const：表示使用主键或唯一索引做等值查询，也叫常量查询。

语句如：explain select * from persion where id = 2;

 

        null：表示不用访问表，直接出结果，速度最快。

语句如：explain select now();

3.3、possible_keys——表示查询时能够使用到的索引

注意：并不一定真正全部使用，显示的是索引名称。

3.4、key——表示查询时真正使用到的索引

注意：key显示的才是查询时真正使用到的索引，显示的是索引名称。

3.5、rows——估算SQL要查询到结果需要扫描多少行记录

说明：MYSQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
 

3.6、key_len——表示查询使用了索引的字节数量

说明：通过key_len可以判断是否全部使用了组合索引，或只用到索引的最左部分的部分字段值。

Key_len的计算规则如下：

  • 字符串类型

字符串长度都跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4

char(n): n * 字符集长度

Varchar(n): n * 字符集长度 + 2字节(因为是可变类型，所以加2个字节)

  • 数值类型

TINYINT：1个字节

SMALLINT:  2个字节

MEDIUMINT:  3个字节

INT、FLOAT:  4个字节

BIGINT、DOUBLE：8个字节

  • 时间类型：

DATE: 3个字节

TIMESTAMP: 8个字节

DATETIME: 8个字节

  • 字段属性：

NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

示例剖析：

示例1: Explain select * from persion where id = 1;

 剖析：使用到字段id，id是int类型，所以key_len是4。

示例2：Explain select * from persion where age =18(前面给age添加过滤普通索引)。

 剖析：使用到字段age，age是int类型，且DEFAULT NULL，所以key_len=4+1，是5。
示例3：explain select * from persion where name='张三';

执行语句前给表persion的字段name添加普通索引：

alter table persion add index idx_name(name);


执行语句后的结果：

剖析：使用字段name，name是varchar类型，长度都255个，且DEFAULT NULL, 表使用字符集utf8mb4, 所以key_len= 255 *4+ 2+1, 是1023。

3.7、Extra——表示额外的信息

常见的集中如下：

  • Using where——查询的列未被索引覆盖，where筛选条件非索引列。在查找使用索引的情况下，需要回表去查询所需的数据。

示例：explain select * from persion where age >18;


剖析1：执行计划结果possible_keys的值是idx_age(age的索引名)，但key的实际值却是NULL, 且查询出来的字段是*号表示，所以extra的值是using where，可以分析出还是需要回表将表的所有字段查询出来。

  • Using index——表示查询需要通过索引，索引就可以满足所需数据。表示索引能够覆盖所有的查询字段，无需进行回表查询。

示例1：explain select id from persion where id = 1;

剖析：查询条件使用字段id, 且查询字段也是id, id字段是主键索引。所以Extra的值出现了Using index。

示例2：explain select id, age from persion where age >20;


剖析：查询的列被索引覆盖，并且 where筛选条件是索引列之一。但无法直接通过索引查找来查询到符合条件的数据。

  •  NULL——既没有Using index，也没有Using where Using index，也没有using where.

查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)。

示例：explain select id,age from persion where age =20 and id = 5;

  •  Using filesort——表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘。因此有Using filesort建议优化。

示例：

1、explain select * from persion order by age;

2、explain select * from persion order by age desc;

  • Using temproary——查询使用到了临时表，一般出现于去重等操作。

执行计划前，我们先删掉除主键索引的全部索引。

示例1：explain select distinct(age) from persion ;

 剖析：操作的字段没有索引，执行distinct(该字段)时会生成临时表。

示例2：explain select age from persion group by age;

 剖析：操作的字段没有索引，执行group by (该字段)时会生成临时表，再内存中排序。

四、参考资料

  1. 8_EXPLAIN查询分析之select_type详解_哔哩哔哩_bilibili
  2. mysql explain解析一 extra中的using index，using where，using index condition - zhp_king - 博客园
  3. MySQL进阶系列： 一文详解explain各字段含义，分层展示的架构图_普通网友的博客
令狐前生
关注
  • 5
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
MYSQL explain 执行计划
09-11
MySQL的`EXPLAIN`命令是数据库管理员和开发者用于分析SQL查询执行计划的重要工具。通过`EXPLAIN`，我们可以深入理解MySQL如何处理`SELECT`语句，优化查询性能，特别是涉及多表连接的情况。以下是`EXPLAIN`输出的各列...
Mysql深入探索之Explain执行计划详析
12-14
如何写出效率高的SQL语句，提到这必然离不开Explain执行计划的分析，至于什么是执行计划，如何写出高效率的SQL，本篇文章将会一一介绍。 执行计划 执行计划是数据库根据 SQL 语句和相关表的统计信息作出的一个查询...
MySQL EXPLAIN执行计划分析
qq_28227405的博客
05-08 506
通常都会使用Explain来查看sql的执行计划，快速的分析sql性能并找出sql的问题所在。 explain执行计划的结果有一下列： id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra 说明：id: select查询的序列号，包含一组数字，表示执行select字句或操作表的顺序。 1.如果id相同，则执行顺序由上至下 2.如果id不同，且有子查询，则id越大的执行优先级越高。因为子
MySQL 执行计划分析：EXPLAIN
Tony_stark_L的博客
12-31 443
MySQL EXPLAIN 执行计划分析
mysql的explain执行计划_MySQL之Explain(执行计划)
weixin_30386973的博客
01-28 129
今天，数据库的操作越来越成为整个应用的性能瓶颈了，这点对于Web应用尤其明显。关于数据库的性能，这并不只是DBA才需要担心的事，而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构，对操作数据库时(尤其是查表时的SQL语句)，我们都需要注意数据操作的性能。这里，我们不会讲过多的SQL语句的优化，而只是针对MySQL这一Web应用最多的数据库1.explain(查看执行计划)：使用Expla...
mysql sql explain_SQL优化 MySQL版 ，explain SQL执行计划详解
weixin_29898529的博客
02-17 272
目录一、执行计划分析二、SQL执行计划中的参数讲解——explain中的id1、id值相同，从上往下顺序执行。2、ID值不同，id值越大越优先查询3、id值有相同，又有不同。id值越大越优先；id值相同，从上往下顺序执行二、SQL执行计划中的参数讲解——explain中的select_type三、SQL执行计划中的参数讲解——explain中的table四、SQL执行计划中的参数讲解——expla...
mysql的explain执行计划_mysql explain执行计划详解
weixin_35929851的博客
01-28 100
1)id列SELECT识别符。这是SELECT查询序列号。这个不重要,查询序号即为sql语句执行的顺序css2)select_type列常见的有：mysqlA：simple：表示不须要union操做或者不包含子查询的简单select查询。有链接查询时，外层的查询为simple，且只有一个sqlB：primary：一个须要union操做或者含有子查询的select，位于最外层的单位查询的selec...
MySql中如何使用 explain 查询 SQL 的执行计划
09-09
在MySQL中，`EXPLAIN`命令是一个强大的工具，用于分析和理解SQL查询的执行计划。通过使用`EXPLAIN`，我们可以深入了解查询优化器如何处理SQL语句，从而优化查询性能。下面我们将详细介绍如何使用`EXPLAIN`以及如何...
MySQL中执行计划explain命令示例详解
09-09
MySQL中的`EXPLAIN`命令是数据库管理员和开发者用于分析SQL查询执行计划的重要工具。通过`EXPLAIN`，我们可以了解MySQL如何处理查询，包括它选择的索引、表的连接顺序以及数据检索方式，从而帮助优化查询性能。 ...
mysql执行计划介绍
09-10
MySQL执行计划是数据库管理系统在处理SQL查询时的预估策略，它是分析和优化SQL...通过对执行计划的深入分析，我们可以发现潜在的性能问题，针对性地调整索引、查询结构和数据库设计，从而提升数据库系统的响应速度。
MySQL数据库下的Explain命令深度解析
主要分享测试的学习资源，帮助快速了解测试行业，帮助想转行、进阶、小白成长为高级测试工程师。
04-27 1257
Explain是一个非常有的命令，可以用来获取关于查询执行计划的信息，以及如何解释输出。Explain命令是查看查询优化器如何决定执行查询的主要方法。这个功能有一定的局限性，并不总是会说出真相，但是它的输出是可以获取的最好信息，值得花时间了解，可以学习到查询是如何执行的。
MySQL中explain执行计划的各属性含义
一个不爱编程的程序猿
09-09 695
各属性含义： id：查询的序列号 select_type：查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询 SIMPLE：查询中不包含子查询或者UNION 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY table：输出的行所引用的表 type：访问类型 从左至右，性能由差到...
MySQL日记——Explain详细介绍
饥渴计科极客杰铿的博客
05-02 361
Explain是什么使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分徐你的查询语句或是表结构的性能瓶颈Explain能干嘛 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引可以被实际使用 表之间的引用 每张表有多少航被优化器查询 Explain如何使用Explain + SQL语句执行计划包含的信息 id select
mysql学习记录 - explain命令
V_zxw的博客
11-03 232
author:zxw email:502513206@qq.com @ Jishou University 1.前言 最近买了mysql的一个课程，基本已经看完第一轮了，第一轮只是留个印象在脑海中还不算深入的理解，想想最近很久没写过博客了，刚好来记录下学习的理解以及加深印象。 2.Content 有关explain官方是这样解释的,explain提供mysql如何执行语句的信息，可以和select,delete,insert,replace,update命令一起使用。 The EXPLAIN st..
MySQL性能分析及explain的使用
weixin_33714884的博客
08-21 403
MySQL性能分析及explain用法的知识是本文我们主要要介绍的内容，接下来就让我们通过一些实际的例子来介绍这一过程，希望能够对您有所帮助。 1.使用explain语句去查看分析结果 如explain select * from test1 where id=1;会出现：id selecttype table type possible_keys key key_len...
MySQL的EXPLAIN参数的简单解析
One_Punch_Devil的博客
03-24 1319
mysql的explain参数整理
MySQL中ENGINE = InnoDB AUTO_INCREMENT = 200 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci的作用
qq_47768542的博客
12-21 9390
CREATE TABLE `permission` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_id` int(11) NULL DEFAULT NULL COMMENT '角色ID', `permission` varchar(63) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '权限', `add_time` datetime(0)
MySQL 运行SQL文件 Unknown character set: 'utf8mb4'
HunterYoung0718的博客
12-03 3454
今天在初始化项目的时候，导入sql文件中报了如下错误： 下面是部分创建sql DROP TABLE IF EXISTS `alipay_config`; CREATE TABLE `alipay_config` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `app_id` varchar(255) CHAR...
mysql导入数据时提示 USING BTREE 错误解决办法
Ben@极地飞鸿的专栏
04-26 8927
在导入MAGENTO SAMPLE数据时出现USING BTREE错误，主要原因是MYSQL不兼容问题导致。在tag与tag_relation两个表中使用了USING BTREE，但其格式不兼容导致导入失败。修改后即可正常导入了。 修改如下： PRIMARY KEY (`tag_id`) USING BTREE, 修改为: PRIMARY KEY `tab_id` USIN
MySQL Explain
最新发布
05-23
它可以帮助我们分析SQL查询语句的执行计划，从而找出查询语句中存在的性能问题，进而优化查询语句，提升数据库查询效率。 通过使用Explain，我们可以了解MySQL是如何处理SQL查询语句的，包括使用哪些索引、如何连接...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值