MySQL EXPLAIN详解

最新推荐文章于 2023-08-20 16:26:56 发布
最新推荐文章于 2023-08-20 16:26:56 发布
阅读量289 收藏 1
点赞数 1
分类专栏： 数据库 文章标签： mysql mysql EXPLAIN mysql优化 EXPLAIN 详解
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/pizicai007/article/details/112195416
版权

MySQL EXPLAIN详解

TIPS：
   本文基于 MySQL 8.0.21，理论支持MySQL 5.0及更高版本。

1. EXPLAIN详解

1.1 EXPLAIN使用

  explain 可用来分析 SQL 的执行计划。格式如下：

EXPLAIN | DESCRIBE | DESC SQL 语句;

EXPLAIN select * from employees;

DESCRIBE select * from employees;

DESC select * from employees;

  结果输出展示：

字段含义
id该语句的唯一标识
select_type查询类型
table表名
partitions匹配的分区
type联接类型
possible_keys可能的索引选择
key实际选择的索引
key_len索引的长度
ref索引的哪一列被引用了
rows估计要扫描的行
filtered表示符合查询条件的数据百分比
Extra附加信息

  

1.2 参数解读
1.2.1 id 唯一标识

  该语句的唯一标识。如果 explain 的结果包括多个 id 值，则数字越大越先执行；而对于相同 id 的行，则表示从上往下依次执行。

  

1.2.2 select_type 查询类型

  查询类型，有如下几种取值：

查询类型作用
SIMPLE简单查询（未使用UNION或子查询）
PRIMARY最外层的查询
UNION在UNION中的第二个和随后的SELECT被标记为UNION。如果UNION被FROM子句中的子查询包含，那么它的第一个SELECT会被标记为DERIVED。
DEPENDENT UNIONUNION中的第二个或后面的查询，依赖了外面的查询
UNION RESULTUNION的结果
SUBQUERY子查询中的第一个 SELECT
DEPENDENT SUBQUERY子查询中的第一个 SELECT，依赖了外面的查询
DERIVED用来表示包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为是Derived table（派生表），因为该临时表是从子查询派生出来的
DEPENDENT DERIVED派生表，依赖了其他的表
MATERIALIZED物化子查询
UNCACHEABLE SUBQUERY子查询，结果无法缓存，必须针对外部查询的每一行重新评估
UNCACHEABLE UNIONUNION属于UNCACHEABLE SUBQUERY的第二个或后面的查询

  

1.2.3 table 表名

  表示当前这一行正在访问哪张表，如果 SQL 定义了别名，则展示表的别名。

  

1.2.4 partitions 分区

  当前查询匹配记录的分区。对于未分区的表，返回 null。

  

1.2.5 type 连接类型

  连接类型，有如下几种取值，性能从好到坏排序 如下：

  1. system：该表只有一行（相当于系统表），system 是 const 类型的特例；
  2. const：针对主键或唯一索引的等值查询扫描，最多只返回一行数据。 const 查询速度非常快，因为它仅仅读取一次即可；
  3. eq_ref：当使用了索引的全部组成部分，并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 才会使用该类型，性能仅次于system及const；
-- 多表关联查询，单行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

-- 多表关联查询，联合索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;
  1. ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的；
-- 根据索引（非主键，非唯一索引），匹配到多行
SELECT * FROM ref_table WHERE key_column=expr;

-- 多表关联查询，单个索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

-- 多表关联查询，联合索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;
  1. fulltext：全文索引；
  2. ref_or_null：该类型类似于 ref，但是 MySQL 会额外搜索哪些行包含了 NULL。这种类型常见于解析子查询；
SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;
  1. index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引；
  2. unique_subquery：该类型和 eq_ref 类似，但是使用了 IN 查询，且子查询是主键或者唯一索引；
value IN (SELECT primary_key FROM single_table WHERE some_expr)
  1. index_subquery：和 unique_subquery 类似，只是子查询使用的是非唯一索引；
value IN (SELECT key_column FROM single_table WHERE some_expr)
  1. range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有 BETWEEN 子句或 WHERE 子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN() 等操作符。
SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);
  1. index：全索引扫描，和 ALL 类似，只不过 index 是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：
    1. 如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain 的 Extra 列的结果是 Using index。index 通常比 ALL 快，因为索引的大小通常小于表数据；
    2. 按索引的顺序来查找数据行，执行了全表扫描。此时，explain 的Extra 列的结果不会出现 Uses index。
  2. ALL：全表扫描，性能最差。
      
1.2.6 possible_keys 可能使用的索引

  展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。

  

1.2.7 key 实际选择的索引

  表示MySQL实际选择的索引。

  

1.2.8 key_len 索引的长度

  索引使用的字节数。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节。

  key_len 计算公式： https://www.cnblogs.com/gomysql/p/4004244.html。

  

1.2.9 ref 索引引用列

  表示将哪个字段或常量和key列所使用的字段进行比较，如果 ref 是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。

  

1.2.10 rows 扫描的行数

  MySQL估算会扫描的行数，数值越小越好。

  

1.2.11 filtered 符合查询条件的数据百分比

  表示符合查询条件的数据百分比，最大100。用 rows × filtered 可获得和下一张表连接的行数。例如 rows = 1000，filtered = 50%，则和下一张表连接的行数是 500。

  

1.2.12 Extra 扩展信息

  展示有关本次查询的附加信息，取值如下：

  1. Child of ‘table’ pushed join@1：此值只会在 NDB Cluster 下出现；

  2. const row not found：例如查询语句 SELECT … FROM tbl_name，而表是空的；

  3. Deleting all rows：对于 DELETE 语句，某些引擎（例如 MyISAM）支持以一种简单而快速的方式删除所有的数据，如果使用了这种优化，则显示此值；

  4. Distinct：查找 distinct 值，当找到第一个匹配的行后，将停止为当前行组合搜索更多行；

  5. FirstMatch(tbl_name)：当前使用了半连接FirstMatch策略，详见https://www.cnblogs.com/abclife/p/10895624.html

  6. Full scan on NULL key：子查询中的一种优化方式，在无法通过索引访问 null 值的时候使用；

  7. Impossible HAVING：HAVING 子句始终为 false，不会命中任何行；

  8. Impossible WHERE：WHERE子句始终为 false，不会命中任何行；

  9. Impossible WHERE noticed after reading const tables：MySQL 已经读取了所有 const（或system）表，并发现 WHERE 子句始终为 false；

  10. LooseScan(m…n)：当前使用了半连接LooseScan策略，详见： http://www.javacoder.cn/?p=39

  11. No matching min/max row：没有任何能满足例如 SELECT MIN(…) FROM … WHERE condition 中的 condition 的行；

  12. no matching row in const table：对于关联查询，存在一个空表，或者没有行能够满足唯一索引条件；

  13. No matching rows after partition pruning：对于 DELETE 或 UPDATE 语句，优化器在partition pruning（分区修剪）之后，找不到要 delete 或 update 的内容；

  14. No tables used：当此查询没有 FROM 子句或拥有 FROM DUAL 子句时出现。例如：explain select 1；

  15. Not exists：MySQL 能对 LEFT JOIN 优化，在找到符合 LEFT JOIN 的行后，不会为上一行组合中检查此表中的更多行。例如：

    1. 假设t2.id定义成了NOT NULL ，此时，MySQL会扫描t1，并使用t1.id的值查找t2中的行。 如果MySQL在t2中找到一个匹配的行，它会知道t2.id永远不会为NULL，并且不会扫描t2中具有相同id值的其余行。也就是说，对于t1中的每一行，MySQL只需要在t2中只执行一次查找，而不考虑在t2中实际匹配的行数。
        
    2. 在MySQL 8.0.17及更高版本中，如果出现此提示，还可表示形如 NOT IN (subquery) 或 NOT EXISTS (subquery) 的WHERE条件已经在内部转换为反连接。这将删除子查询并将其表放入最顶层的查询计划中，从而改进查询的开销。通过合并半连接和反联接，优化器可以更加自由地对执行计划中的表重新排序，在某些情况下，可让查询提速。你可以通过在EXPLAIN语句后紧跟一个SHOW WARNING语句，并分析结果中的Message列，从而查看何时对该查询执行了反联接转换。
SELECT * 
FROM t1 LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;
  1. Plan isn’t ready yet：使用了 EXPLAIN FOR CONNECTION，当优化器尚未完成为在指定连接中为执行的语句创建执行计划时， 就会出现此值；
  2. Range checked for each record (index map: N)：MySQL 没有找到合适的索引去使用，但是去检查是否可以使用 range或 index_merge 来检索行时，会出现此提示。index map N 索引的编号从1开始，按照与表的 SHOW INDEX 所示相同的顺序。 索引映射值N是指示哪些索引是候选的位掩码值。 例如0x19（二进制11001）的值意味着将考虑索引1、4和5；
  3. Recursive：出现了递归查询；
  4. Select tables optimized away：优化器确定：①最多返回1行；②要产生该行的数据，要读取一组确定的行，时会出现此提示。一般在用某些聚合函数访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询时展示；
  5. Skip_open_table， Open_frm_only， Open_full_table：这些值表示适用于 INFORMATION_SCHEMA 表查询的文件打开优化，依次为：无需打开表文件，信息已经通过扫描数据字典获得、仅需要读取数据字典以获取表信息、未优化的信息查找。表信息必须从数据字典以及表文件中读取；
  6. unique row not found：对于形如 SELECT … FROM tbl_name 的查询，但没有行能够满足唯一索引或主键查询的条件；
  7. Using filesort：当 Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Explain 不会显示的告诉客户端用哪种排序。官方解释：“MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行”；
  8. Using index：仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略；
  9. Using index condition：表示先按条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。通过这种方式，除非有必要，否则索引信息将可以延迟“下推”读取整个行的数据；
  10. Using index for group-by：数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用 GROUP BY或 DISTINCT 子句时，如果分组字段也在索引中，Extra 中的信息就会是 Using index for group-by；
  11. Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)：使用Block Nested Loop或Batched Key Access算法提高join的性能，详见 https://www.cnblogs.com/chenpingzhao/p/6720531.html
  12. Using MRR：使用了Multi-Range Read优化策略；
  13. Using sort_union(…), Using union(…), Using intersect(…)：这些指示索引扫描如何合并为 index_merge 连接类型；
  14. Using temporary：为了解决该查询，MySQL 需要创建一个临时表来保存结果。如果查询包含不同列的 GROUP BY和 ORDER BY子句，通常会发生这种情况；
-- name无索引
explain SELECT name FROM t1 group by name
  1. Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现 using where 信息。
坯子蔡
关注
专栏目录
MySQL优化相关知识点总结
追梦的疯子
11-18 407
MYSQL优化一、慢查询二、将慢查询的日志写入到文件中 一、慢查询 MySQL默认10秒内执行的SQL语句没有响应及为慢查询 查看当前数据库设置的时间 show variables like 'long_query_time'; 修改慢查询判定的时间，用于查找慢查询 SET long_query_time=1; 注：重启mysql之后，long_query_time依然是my.ini中设置的默认值。 二、将慢查询的日志写入到文件中 ...
MySQL中的EXPLAIN的详解
最新发布
SHI940637335的博客
08-17 2514
是一种优化技术，允许 MySQL 在存储引擎层面应用部分。
Mysql Explain 详解 [原创]
cixin1802的博客
08-06 786
一.语法 explain < table_name > 例如: explain select * from t3 where id=3952602; 二.explain输出解释 +----+---------...
MySQL explain详解
weixin_30609287的博客
01-15 255
Explain简介 本文主要讲述如何通过 explain 命令获取 select 语句的执行计划，通过 explain 我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。 下面是使用 explain 的例子： 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记...
MySQL查看SQL性能分析（explain关键字）
fox_bert的博客
10-09 2305
Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。 Explain语法：explain select … from … [where ...] 例如：explain select * from news; 首先需要注意：MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELE...
mysql explain详解
03-29
MySQL中的`EXPLAIN`命令是用于分析SQL查询执行计划的重要工具，它可以帮助数据库管理员和开发者了解MySQL如何处理SQL语句，从而进行性能优化。在深入理解`EXPLAIN`之前，我们先来了解一下数据库优化的基本概念。 ...
mysql explain详解1
08-04
MySQL的EXPLAIN命令是数据库管理员和开发者用来分析SQL查询执行计划的重要工具，它能帮助我们理解MySQL如何使用索引来处理SELECT语句，并优化查询性能。本文将深入解析EXPLAIN输出的各项参数，以便更好地理解和优化...
MySQL explain详解.md
06-06
MySQL explain详解
MYSQL EXPLAIN详解
12-01
### MySQL EXPLAIN详解 #### 一、EXPLAIN简介 **EXPLAIN** 是 MySQL 提供的一个非常有用的工具，它能够帮助用户了解 MySQL 如何执行查询语句，并展示出 MySQL 预估的查询计划。这对于理解 SQL 查询的工作原理、...
MySQL Explain详解
johnhuster的专栏
11-10 4146
转载自：https://www.toutiao.com/a6621756882769936904/?tt_from=weixin&amp;utm_campaign=client_share&amp;wxshare_count=1&amp;timestamp=1541753671&amp;app=news_article&amp;utm_source=weixin&amp;iid=496349922...
explain 分析sql语句
jiliang272的博客
03-06 490
+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref...
教你如何定位及优化SQL语句的性能问题
qq_41246635的博客
09-05 558
在现如今的软件开发中，关系型数据库是做数据存储最重要的工具。无论是Oracale还是Mysql，都是需要通过SQL语句来和数据库进行交互的，这种交互我们通常称之为CRUD。在CRUD操作中，最最常用的也就是Read操作了。而对于不同的表结构，采用不同的SQL语句，性能上可能千差万别。本文，就基于MySql数据库，来介绍一下如何定位SQL语句的性能问题。   对于低性能的SQL语句的定位，最重要...
Mysql explain详解
BodyCoding
07-09 240
前言 explain关键字在MySQL中可以查看一条查询语句的具体执行策略。通过explain关键字，我们可以查看到哪些字段使用了索引，发生了哪些查询，查询是什么类型的等等。explain关键字分别有以下10列： id:选择标识符 select_type:表示查询的类型。 table:输出结果集的表 partitions:匹配的分区 type:表示表的连接类型 possible_keys:表示查...
MySQL之Explain详解
2301_78140533的博客
08-20 2534
优化器优化后的执行SQL由子查询(两个select)变成了表关联查询(一个select)，所以执行计划的结果是两个id都为1。( '张三', 'zhangsan@example.com', 'password123' ),( '赵六', 'zhaoliu@example.com', 'password123' ),( '王五', 'wangwu@example.com', 'password123' ),( '李四', 'lisi@example.com', 'password123' ),
mysql Explain 详解
05-19
MySQL的EXPLAIN是一个非常有用的工具，可以帮助开发人员优化查询语句并提高查询性能。通过执行EXPLAIN，可以了解查询的执行计划、表之间的连接方式以及使用的索引等信息。下面是一份比较详细的MySQL Explain解释： ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值