【MySQL】 Explain详解

Metadata

title: 【MySQL】 Explain详解
date: 2023-06-24 12:50
tags:
  - 行动阶段/完成
  - 主题场景/数据存储
  - 笔记空间/KnowladgeSpace/ProgramSpace/BasicsSpace
  - 细化主题/数据存储
categories:
  - 数据存储
keywords:
  - 数据存储
description: 【MySQL】 Explain详解

概述

EXPLAIN 语句帮助理解 MySQL 如何执行 SQL 查询。每个输出列都有特定的含义。
主要列：

  • table: 显示访问的表名。
  • id: 对于每个 SELECT 关键字，都会分配一个唯一的 id 值。
  • select_type: 描述了查询中各部分的类型（例如，SIMPLE, PRIMARY, UNION等）。
  • partitions: 显示涉及到的分区信息。
  • type：显示了 MySQL 使用哪种内部机制来查找行。可能是全索引扫描、范围扫描、全表扫描等方式之一。
  • possible_keys 和 key：显示 MySQL 可能和实际使用什么索引进行查询优化。
  • key_len：显示所选择的键长度，在某些条件下可能不使用全部长度来获取数据结果集
  • ref：表示参考什么信息寻找索引值 (如果可能) 在给定表上匹配一个行操作，这可以是常量或者函数等值对比结果
  • rows: 预计需要读取多少行以获得最终结果集.
  • filtered : 表示返回结果集百分比(通过where过滤后), 是估算值.
  • Extra: 提供MySQL如何处理查询以及检索表中行额外信息.
    Json格式：
    Json 格式提供了更详细且直观地查看执行计划花费成本与评价性能。
  • read_cost: IO 成本 + 检测 rows × (1 - filter) 条记录 CPU 成本
  • eval_cost: 检测 rows × filter 条记录成本
  • prefix_cost: 单独查询 s1 表成本 = read_cost + eval_cost
  • data_read_per_join :在此次查询中需要读取数据量

总体而言，使用 EXPLAIN 可以帮助开发人员诊断低效率或潜在问题的 SQL 查询，并为优化提供依据。

【MySQL】 Explain详解

EXPLAIN 语句输出的各个列的作用:

EXPLAIN SELECT 1;

执行计划输出中各列详解

table

EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名

id

每出现一个SELECT 关键字，会为它分配一个唯一的id 值。

在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表。

查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。

select_type

为每一个SELECT 关键字代表的小查询都定义了一个称之为select_type 的属性，意思是我们只要知道了某个小查询的select_type 属性，就知道了这个小查询在整个大查询中扮演了一个什么角色

select_type 取值

  • SIMPLE
    查询语句中不包含UNION 或者子查询的查询都算作是SIMPLE 类型
  • PRIMARY
    对于包含UNION 、UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type 值就是PRIMARY
  • UNION
    对于包含UNION 或者UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type 值就是UNION
  • UNION RESULT
    MySQL 选择使用临时表来完成UNION 查询的去重工作，针对该临时表的查询的select_type 就是UNIONRESULT
  • SUBQUERY
    如果包含子查询的查询语句不能够转为对应的semi-join 的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT 关键字代表的那个查询的select_type 就是SUBQUERY
    由于select_type为SUBQUERY的子查询由于会被物化，所以只需要执行一遍
  • DEPENDENT SUBQUERY
    如果包含子查询的查询语句不能够转为对应的semi-join 的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT 关键字代表的那个查询的select_type 就是DEPENDENT SUBQUERY
    select_type为DEPENDENT SUBQUERY的查询可能会被执行多次
  • DEPENDENT UNION
    在包含UNION 或者UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type 的值就是DEPENDENT UNION 。
  • DERIVED
    对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type 就是DERIVED ，比方说下边这个查询
  • MATERIALIZED
    当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type 属性就是MATERIALIZED
  • UNCACHEABLE SUBQUERY
  • UNCACHEABLE UNION

partitions

type

MySQL 对某个表的执行查询时的访问方法

其中的type 列就表明了这个访问方法是个啥

完整的访问方法如下： system ， const ，eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ，range ， index ， ALL 。

  • system
    该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system 。

  • const

根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const

  • eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref

  • ref

当通过普通的二级索引列与常量进行等值匹配时来查询某个表

  • fulltext

全文索引

  • ref_or_null

当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL 值时，那么对该表的访问方法就可能是ref_or_null

  • index_merge

一般情况下对于某个表的查询只能使用到一个索引，但我们唠叨单表访问方法时特意强调了在某些场景下可以使用Intersection 、Union 、Sort-Union 这三种索引合并的方式来执行查询

  • unique_subquery

类似于两表连接中被驱动表的eq_ref 访问方法， unique_subquery 是针对在一些包含IN 子查询的查询语句中，如果查询优化器决定将IN 子查询转换为EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type 列的值就是unique_subquery

  • index_subquery

index_subquery 与unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引

  • range

如果使用索引获取某些范围区间的记录，那么就可能使用到range 访问方法

  • index

当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index

  • ALL

全表扫描

possible_keys和key

possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些， key 列表示实际用到的索引有哪些

possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

key_len

key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度, 由这三个部分构成的

  • 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是VARCHAR(100) ，使用的字符集是utf8 ，那么该列实际占用的最大存储空间就是100 × 3 = 300 个字节。
  • 如果该索引列可以存储NULL 值，则key_len 比不可以存储NULL 值时多1个字节。
  • 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

由于id 列的类型是INT ，并且不可以存储NULL 值，所以在使用该列的索引时key_len 大小就是4 。

ref

在访问方法是const 、eq_ref 、ref 、ref_or_null 、unique_subquery 、index_subquery 其中之一时， ref 列展示的就是与索引列作等值匹配的东东是个啥，比如只是一个常数或者是某个列。

rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows 列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows 列就代表预计扫描的索引记录行数。

filtered

condition filtering 的概念, MySQL 在计算驱动表扇出时采用的一个策略

  • 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。
  • 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

Extra

Extra 列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL 到底将如何执行给定的查询语句。

  • No tables used

当查询语句的没有FROM 子句时将会提示该额外信息

  • Impossible WHERE

查询语句的WHERE 子句永远为FALSE 时将会提示该额外信息

  • No matching min/max row

当查询列表处有MIN 或者MAX 聚集函数，但是并没有符合WHERE 子句中的搜索条件的记录时，将会提示该额外信息

  • Using index

当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra 列将会提示该额外信息

  • Using index condition

有些搜索条件中虽然出现了索引列，但却不能使用到索引

  • Using where

当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE 子句中有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息。

  • Using join buffer (Block Nested Loop)

当被驱动表不能有效的利用索引加快访问速度， MySQL 一般会为其分配一块名叫join buffer 的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法

  • Not exists

当我们使用左（外）连接时，如果WHERE 子句中包含要求被驱动表的某个列等于NULL 值的搜索条件，而且那个列又是不允许存储NULL 值的，那么在该表的执行计划的Extra 列就会提示Not exists 额外信息

没有必要到被驱动表中找到全部符合ON子句条件的记录

  • Using intersect(…) 、Using union(…) 和Using sort_union(…)

如果执行计划的Extra 列出现了Using intersect(…) 提示，说明准备使用Intersect 索引合并的方式执行查询，括号中的… 表示需要进行索引合并的索引名称；如果出现了Using union(…) 提示，说明准备使用Union 索引合并的方式执行查询；出现了Using sort_union(…) 提示，说明准备使用Sort-Union 索引合并的方式执行查询。

  • Zero limit

当我们的LIMIT 子句的参数为0 时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息

  • Using filesort

很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序

把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort ）。

  • Using temporary

如果查询中使用到了内部的临时表，在执行计划的Extra 列将会显示Using temporary 提示

  • Start temporary, End temporary

查询优化器会优先尝试将IN 子查询转换成semi-join ，而semi-join 又有好多种执行策略

当执行策略为DuplicateWeedout 时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，
驱动表查询执行计划的Extra 列将显示Start temporary 提示
被驱动表查询执行计划的 Extra 列将显示End temporary 提示

  • LooseScan

在将In 子查询转为semi-join 时，如果采用的是LooseScan 执行策略，则在驱动表执行计划的Extra 列就是显示LooseScan 提示

  • FirstMatch(tbl_name)

在将In 子查询转为semi-join 时，如果采用的是FirstMatch 执行策略，则在被驱动表执行计划的Extra列就是显示FirstMatch(tbl_name)

Json格式的执行计划

提供了一种查看某个执行计划花费的成本的方式

  • read_cost 是由下边这两部分组成的
    • IO 成本
    • 检测rows × (1 - filter) 条记录的CPU 成本
  • eval_cost 是这样计算的：
    • 检测 rows × filter 条记录的成本。
  • prefix_cost 就是单独查询s1 表的成本
    • read_cost + eval_cost
  • data_read_per_join 表示在此次查询中需要读取的数据量