Mysql优化二：Explain工具列介绍

最新推荐文章于 2023-06-20 11:56:56 发布
最新推荐文章于 2023-06-20 11:56:56 发布
阅读量122 收藏 1
点赞数 2
分类专栏： mysql 文章标签： sql mysql 索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/pang5356/article/details/108777051
版权

目录

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返 回执行计划的信息，而不是执行这条SQL 。
注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中。
在查询中的每个表会输出一行，如果有两个表通过 join 连接查询，那么会输出两行 。
如下示例：

EXPLAIN SELECT * FROM actor;

在这里插入图片描述
当然EXPLAIN还有两个变种：

  1. explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有filtered 列，是 一个半分比的值，rows * filtered /100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当 前表id值小的表）。

    EXPLAIN EXTENDED SELECT * FROM film WHERE id = 1;

    在这里插入图片描述

    SHOW WARNINGS;

    在这里插入图片描述

  2. explain partitions：相比 explain 多了个 partitions字段，如果查询是基于分区表的话，会显示查询将访问的分区。

基于EXPLAIN的查询结果我们来了解EXPLAIN结果的每个字段的含义。 在介绍之前先把建表的语句给出来：
actor表的建表语句：

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` VALUES ('1', 'a', '2017-12-22 15:27:18');
INSERT INTO `actor` VALUES ('2', 'b', '2017-12-22 15:27:18');
INSERT INTO `actor` VALUES ('3', 'c', '2017-12-22 15:27:18');

film表的建表语句：

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `film` VALUES ('3', 'film0');
INSERT INTO `film` VALUES ('1', 'film1');
INSERT INTO `film` VALUES ('2', 'film2');

film_actor的建表语句：

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` VALUES ('1', '1', '1', null);
INSERT INTO `film_actor` VALUES ('2', '1', '2', null);
INSERT INTO `film_actor` VALUES ('3', '2', '1', null);

id列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的 顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

在这里插入图片描述
可以看到这里有三个select查询，所以这里有三列并且id大小不同，说明三个select有优先级。这里的3是指最后的一个from后面的子查询，说明先执行这个子查询，其次2是在第一个select后面的子查询，最后才是走的第一个select的查询。

select_type列

select_type 表示对应行是简单还是复杂的查询。
其值有以下几种：

  1. simple：简单查询。查询不包含子查询和union。

    EXPLAIN SELECT * FROM film WHERE id = 2;

    在这里插入图片描述

  2. primary：复杂查询中最外层的 select。

    explain select (select 1 from actor where id = 1) from (select * from film where 	id = 1) der;

    在这里插入图片描述

  3. subquery：包含在 select 中的子查询（不在 from 子句中）。

    explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

    在这里插入图片描述

  4. derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表。

    explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

    在这里插入图片描述
    可以看到子查询 select * from film where id = 1的select_type列是DERIVED,他是查询后将结果放到临时表了。
    需要注意的是对于mysql的更高的版本(5.7)会将派生结果合并，需要关闭其功能，其对应的语句如下:
    #关闭mysql5.7新特性对衍生表的合并优化

    set session optimizer_switch='derived_merge=off';

    #还原默认配置

    set session optimizer_switch='derived_merge=on';

  5. union：在 union 中的第二个和随后的 select。

    EXPLAIN SELECT 1 UNION ALL SELECT 1;

    如上图所示，第二个select的select_type就是UNION，这里我们也可以看到有个id为空的数据，就是在两个select执行完后将结果合并，其执行优先级最低。

table列

这一列表示 explain 的一行正在访问哪个表。当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
还是看那个sql举例：

explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

在这里插入图片描述
可以看到最外层的select的table列是derived3,就是基于id为3的查询结果进行查询的。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。如下：

EXPLAIN SELECT 1 UNION ALL SELECT 1;

在这里插入图片描述

type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。 依次从最优到最差分别为：
system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref 。
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表 。

EXPLAIN SELECT MIN(id) FROM film;

在这里插入图片描述
接下来来逐一了解每个字段值的含义。

  1. const，system。mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于primary key 或 unique key的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system 。

    EXPLAIN SELECT * FROM (SELECT * FROM film WHERE id = 1) film;

    在这里插入图片描述
    这里可以看到id为2的是sql里的子查询，这里使用主键id查询，其type值为const，而外层查询时基于子查询结果再查询，这里通过唯一主键查询只能有一条或者0条，数量是确定，只是拿出即可，其值为system。
    这里我们可以使用EXPLAIN的变种来查看：

    EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM film WHERE id = 1) film;

    在这里插入图片描述

    SHOW WARNINGS;

    在这里插入图片描述

  2. eq_ref。primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

    EXPLAIN SELECT * FROM film_actor LEFT JOIN film ON film_actor.film_id = film.id;

    在这里插入图片描述
    从上面我们看到这里以film_actor为主表，所以扫描film_actor中的所有数据，然后使用film_actor中扫描出来的每条数据的film_id去film表中查询比较，符合则返回film的行数据，不符合则返回空。这里使用film_actor表的film_id的去film中查询数据时走的就是film的主键所以，所以此处的type为eq_ref。当然如果这里关联的film的一个unique的字段也是这个值。

  3. ref。相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
    简单查询：

    EXPLAIN SELECT * FROM film WHERE name = 'film1';

    在这里插入图片描述
    关联表查询：

    EXPLAIN SELECT * FROM film LEFT JOIN film_actor ON film_actor.film_id = film.id;

    在这里插入图片描述
    如上图所示，在查询film_actor时其type列的值是ref，这里以film表为主表逐行扫描表的数据，每次扫表的行数据拿出film的id去film_actor表中关联film_id查询，我们知道在film_actor表中film_id位于索引中，所以会通过索引查询，但是一个film的id在film_actor表中可能对应多条数据，所以其效率一般会低于eq_ref。

  4. range。范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

    EXPLAIN SELECT * FROM actor  WHERE id > 1;

    在这里插入图片描述

  5. index。扫描全表索引，这通常比ALL快一些。

    EXPLAIN SELECT * FROM film  WHERE id > 1;

    在这里插入图片描述

  6. ALL。即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了。

    EXPLAIN SELECT * FROM actor;

    在这里插入图片描述

possible_keys列

这一列显示查询可能使用哪些索引来查找explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

EXPLAIN SELECT * FROM film_actor WHERE film_actor.film_id = 2;

在这里插入图片描述
在表的定义已经知道film_actor的定义，如下：

在这里插入图片描述
key_len计算规则如下：

  1. 字符串

    char(n)：n字节长度。
    varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2。

  2. 数值类型

    tinyint：1字节 。
    smallint：2字节。
    int：4字节。
    bigint：8字节。

  3. 时间类型

    date：3字节。
    timestamp：4字节。
    datetime：8字节。

如果字段允许为 NULL，需要1字节记录是否为 NULL ，索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）。

  1. const。

    EXPLAIN SELECT * FROM film WHERE name = 'film0';

    在这里插入图片描述
    此处查询使用了film表的idx_name索引，使用索引的字段条件是name=’film0’，这是指定的是常量，所以ref该字段的值是const。

  2. 字段名。

    EXPLAIN SELECT * FROM film_actor LEFT JOIN film ON film_actor.film_id = film.id;

    在这里插入图片描述
    这里的查询以film_actor表为主表进行全表扫描，在扫描每行数据时从每行的数据中拿出film_id这个字段的值去film表根据id查询，那么就是使用film的主键索引，而这里film的id使用的值是film_actor表的film_id的值，所以这里的ref值就是tulingtest.film_actor.film_id，即tulingtest库的film_actor表的film_id字段。

rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

Extra列

这一列展示的是额外信息。常见的重要值如下：

  1. Using index：使用覆盖索引。
    接下来对比两个sql如下：

    EXPLAIN SELECT * FROM film_actor WHERE film_actor.film_id = 1;

    在这里插入图片描述

    EXPLAIN SELECT film_id,actor_id FROM film_actor WHERE film_actor.film_id = 1;

    在这里插入图片描述
    这里相同的sql查询的字段不同，其Extra的结果就不同，第一个查询的是*，而第二查询的是film_id和actor_id，第一个查询的*包括id、film_id、actor_id和remark,我们可以查询使用的索引是idx_film_actor_id，之前我们已经了解过联合索引的数据结构，这里通过所以是取不到remark的值，所以需要再回表查询，而第二次查询的值film_id和actor_id均可在索引中取到，索引使用了覆盖索引。

  2. Using Where：使用where语句来处理结果，查询的列未被索引覆盖。

    EXPLAIN SELECT * FROM actor WHERE name = 'a';

    在这里插入图片描述
    这里查询actor表中数据，条件是name=’a’，这里name没有相关的索引，其Extra输出的结果就是Using where。

  3. Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

    EXPLAIN SELECT DISTINCT name FROM actor;

    在这里插入图片描述
    这里的大致流程就是先扫描全表将每行的name字段数据取出放置到临时表中，然后从表中将重复的数据去重，明显会影响性能，可以通过对去重的字段建立索引来解决。

    CREATE INDEX idx_actor_name ON actor(name);

    再次运行去重查询语句如下：
    在这里插入图片描述

    可以看到在去重的字段上创建索引后再次查询Extra字段值为Using index，这是因为数据可以从索引中按顺序拿出，处理重复数据效率也高(基于索引结构)。
    完毕后将索引删除：

    DROP INDEX  idx_actor_name ON actor

  4. Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

    EXPLAIN SELECT * FROM actor ORDER BY name;

    在这里插入图片描述
    actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录 。可以通过对排序字段创建索引来使排序走索引来解决。

  5. Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段。

    EXPLAIN SELECT MIN(id)FROM film;

    在这里插入图片描述

bingaPang
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql优化工具explain
12-14
explain的使用很简单，只要在select语句的前面加上explain的关键字就好了，来看个例子 先创建两个表，注意两个表的索引 CREATE TABLE `user` ( `id` varchar(32) NOT NULL, `name` varchar(32) DEFAULT NULL, `...
Mysql中explain优化分析详解
白鸽
08-03 1783
mysql体系结构 性能分析前提知识 MySQL Query Optimizer Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的,这部分最耗费时间） 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Qu
MYSQL _数据库_EXPLAIN(查询性能优化)
一个后端菜鸟的博客
03-10 4120
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录前言一、EXPLAIN是什么？二、有什么用？三、Explain 的每个字段3.1、id(必记住)3.2、select_type3.3、table3.4、type（必记住）3.5、possible_keys3.6、key3.7、key_len3.8、ref3.9、rows（记）3.10、extra（记）四、MySQL执行计划（explain）的局限性参考文章 前言 提示：这里可以添加本文要记录的大概内容： EXPAIN 记录。 .
MySql中Explain详解与索引最佳实践
王林冲的博客
10-10 2427
.使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈   下面是使用 explain 的例子：  在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）...
一文读懂＜explain＞索引优化
热门推荐
慕白Lee的博客
12-31 5万+
对于互联网公司来说，随着用户量和数据量的不断增加，慢查询是无法避免的问题。一般情况下如果出现慢查询，意味着接口响应慢、接口超时等问题。如果是高并发的场景，可能会出现数据库连接被占满的情况，直接导致服务不可用。慢查询的确会导致很多问题，我们要如何优化慢查询呢？主要解决办法有：监控sql执行情况，发邮件、短信报警，便于快速识别慢查询sql打开数据库慢查询日志功能简化业务逻辑代码重构、优化异步处理sql优化索引优化其他的办法先不说，后面有机会再单独介绍。...
（Mysql优化学习02）Explain详解与索引最佳实践
a250029634的博客
02-06 370
架构师课程学习笔记-Mysql Explain详解与索引最佳实践
MySQL优化 – EXPLAIN
12-14
EXPLAIN是我们最常用的SQL分析工具，在使用工具之前我们需要先了解下工具中每一项代表的含义，如下是EXPLAIN中的所有列： mysql> explain select * from user u1 left join user u2 on u1.id=u2.id where u1.name='...
MySQL查询优化之explain 执行计划 深入解析(精品)
12-14
前言二. `Explain` 用途三. `Explain`语法四. 执行效果4.1. 命令行4.2. 可视化工具–`HeidiSQL Portable 9.4`五. Explain 字段详解5.1. 一览全局字段5.2. Id字段5.2.1. id相同5.2.2. id不相同5.2.3. id相同,又不...
SQL优化和SQL执行分析工具Explain的使用详解和示例
03-25
SQL优化 Explain的使用详解 mysql
【最全最详细explain讲解】explain | 索引优化的这把绝世好剑，你真的会用吗？
wufaqidong1的博客
09-15 3766
对于互联网公司来说，随着用户量和数据量的不断增加，慢查询是无法避免的问题。一般情况下如果出现慢查询，意味着接口响应慢、接口超时等问题。如果是高并发的场景，可能会出现数据库连接被占满的情况，直接导致服务不可用。慢查询的确会导致很多问题，我们要如何优化慢查询呢？主要解决办法有：监控sql执行情况，发邮件、短信报警，便于快速识别慢查询sql打开数据库慢查询日志功能简化业务逻辑代码重构、优化异步处理sql优化索引优化其他的办法先不说，后面有机会再单独介绍。
Explain详解与索引最佳实践
weixin_39585051的博客
07-19 387
Explain工具介绍 使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是 执行这条SQL 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 Explain分析示例 参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html 1 示例表： 2 D
explain调优
西魏陶渊明的博客
01-04 610
作者: 西魏陶渊明 博客: https://blog.springlearn.cn/ (opens new window) 西魏陶渊明 莫笑少年江湖梦，谁不少年梦江湖 这篇文章主要讲 explain 如何使用，还有 explain 各种参数概念，之后会讲优化 # 一、Explain 用法 explain模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的...
explain的sql优化详解
森林之王的博客
09-13 1584
explain的sql优化详解
mysql filtered_Mysql Explain 查看 filtered 优化sql
weixin_29903043的博客
01-19 1950
sqlSELECT mt.* ,ds.ps_count FROM(selectdo.id,do.logistics_company_id,do.order_no,do.lable_info,do.ext_order_no,do.ext_order_no2,do.ext_po_no,do.owner_id,do.owner_name,do.shop_id,do.warehouse,do.wareho...
sql优化之explain详解
最新发布
m0_37486454的博客
06-20 398
explain可以查看sql的性能瓶颈信息，并根据结果进行sql的相关优化。在select 语句前加上explain关键字，执行的时候并不会真正执行sql语句，而是返回sql查询语句对应的执行计划信息。根据返回的信息可以分析sql的性能瓶颈从而进行优化。
MySQL索引优化
rzpy_qifengxiaoyue的博客
09-13 112
1. 回表查询 InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记 录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记 录，这就叫做回表查询，它的性能比扫一遍索引树低。 2. 覆盖索引 只需要在一棵索引树上就能获取SQL所需的所 有列数据，无需回表，速度更快，这就叫做索引覆盖。 实现索引覆盖最常见的方法就是:将被查询的字段，建立到组合索引。 3.
数据库 详解explain 性能优化
言午戌的博客
02-16 396
一、从哪几个维度优化数据库性能的问题 1、数据库配置，客户端连接服务端，并发的情况下，数据库的连接不够 服务端设置最大的连接数 show variables like 'max_connections' ，修改连接数的命令：set GLOBAL max_connections=100; 或者直接修改配置文件 客户端使用连接池，应用池化技术不需要新建一个连接，MyBatis自带一个连接池，或...
SQL优化-explain的用法（实例解析）
qq_16268979的博客
06-21 4936
前面写了两篇博客，关于单表和多表的优化，这两篇博客的基础其实就是explain，explain是必须掌握的知识点，我曾尝试过死记硬背，但效果甚微，还是实践来的实在。本文就一些实例来解析explain的用法。 ...
性能优化--explain的使用
guo2312668的博客
11-11 667
一、业务场景 当通过查看Mysql慢日志，我们知晓慢sql后，怎么分析一条sql慢在哪里？以及怎么来优化这条sql呢？ 同时，假如一张表建立了索引，你想看看你写的sql有没有按预期的用上索引。 二、解决办法 MySQL 提供了一个 explain 命令，它可以对select语句进行分析, 并输出select执行的详细信息，以供开发人员进行针对性优化。 三、explain详解 不多说，先上大家随时能够保存下来阅读的思维导图： ...
mysql索引优化 explain
05-26
EXPLAIN是用来分析查询语句的工具，可以通过分析EXPLAIN输出结果来进行索引优化。下面是一些常用的优化技巧： 1. 索引覆盖：通过使用覆盖索引，可以避免MySQL访问表格数据，从而提高查询速度。如下所示： ``` ...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值