EXPLAIN 关键字基本语法

领航猿麦兜
252 阅读12分钟

「这是我参与11月更文挑战的第17天，活动详情查看：2021最后一次更文挑战」

EXPLAIN 使用形式

EXPLAIN 可用来分析 SQL 的执行计划，有三种使用形式。具体格式如下：

mysql> help explain;
Name: 'EXPLAIN'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]
​
{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}
​
explain_type: {
    FORMAT = format_name
}
​
format_name: {
    TRADITIONAL
  | JSON
}
​
explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}
​
The DESCRIBE and EXPLAIN statements are synonyms. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query).
​
URL: http://dev.mysql.com/doc/refman/8.0/en/explain.html
​

其中，使用最广泛的是第二种形式。

EXPLAIE 语句的基本语法

EXPLAIN [EXTENDED] SELECT select _options

使用 EXPLAIN 关键字需要附件信息。其中，select _options 是 SELECT 语句的查询选项，包括 FROM WHERE 字句等。

执行该语句，可以分析 EXPLAIN 后面的 SELECT 语句的执行情况，并且能够分析出所查询的表的一些特征，从而根据需求进行 SQL 语句的优化、表结构的优化等等。

使用 EXPLAIN 语句分析 1 个查询语句，执行如下 SQL 语句：

explain select * from salaries where from_date ='1996-12-02';

image-20210825235415315

展示出来的结果有好多字段，下面我们来对查询结构进行详解。

  • id ：是指 SELECT 识别符，是唯一标识。这是 SELECT 的查询序列号，有几个 SELECT 就有几个 id。有优先级之分，id 的值之越大，它的优先级别越高，并且越先被执行，id 的值为 NULL ，则最后执行。

    • 如果 id 的值相同，那么执行的顺序由上至下。
    • 如果 id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。
    • id 相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id 值越大，优先级越高，越先执行。

  • select_type：是指 SELECT 的类型，是普通查询还是联合查询还是子查询，它有以下几种取值：

    • SAMPLE：简单的查询，不包含子查询和连接查询。
    • PRIMARY：是指如何查询中包含任何复杂的子查询，那么最外层查询则被标记为 PRIMARY。
    • UNION：在 UNION、UNION ALL 和子查询中的第二个和随后的 SELECT 被标记为 UNION，不依赖与外部查询的结果集。如果 UNION 被 FROM子 句中的子查询包含，那么它的第—个 SELECT 会被标记为 DERIVED。
    • DEPENDENT UNION：在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的 select_type 的值就是DEPENDENT UNION。
    • UNION RESULT：UNION 查询的结果集。
    • SUBQUERY：在 SELECT 或者 WHERE 列表中包含子查询，但是不在 FROM 子句中，不依赖于外部查询的结果集。
    • DEPENDENT SUBQUERY：子查询中的第一个 SELECT，但是不在 FROM 子句中，而且取决于外面的查询。
    • DERIVED：在 FROM 子句里包含的子查询被标记为 DERIVED，也叫派生类，MySQL 数据库会递归执行这些子查询，并且把结果放在临时表里。
    • UNCACHEABLE SUBQUERY：子查询的结果不能被缓存，必须重新评估外链接的第一行对于外层的主表，子查询不可被物化，每次都需要计算。
    • UNCACHEABLE UNION：UNION 属于UNCACHEABLE SUBQUERY 的第二个或后面的查询。

  • table：当前这一行正在访问哪一张表，可以是表名，如果 SQL 定义了别名，则是表的别名，也可能是临时表或者 UNION 合并结果集。

    • 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名。
    • 表名是 derived N 的形式，表示使用了 id 为 N 的查询产生的衍生表。
    • 当有union result的时候，表名是 UNION n1，n2 等的形式，n1,n2 表示参与 UNION 的 id。

  • partitions：是指当前查询匹配记录的分区。对于未分区的表，返回 NULL。

  • type：是指访问类型，MySQL 是以何种方式去访问表中的数据行，我们最容易想的是全表扫描，这种方式直接暴力的遍历一张表去寻找需要的数据。访问的类型有很多，性能从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

    • all: 全表扫描，需要遍历整张表，性能最差。一般情况下出现这样的 SQL 语句而且数据量比较大，就需要进行 SQL 优化。
    • index：全索引扫描，与全表扫描一样，只是性能要比 ALL 好一些，只不过 MySQL 扫描表是按照索引的顺序全盘扫描而不是行。当查询仅使用索引中的一部分列是，可以使用此类型。主要有两种情况：一是如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain 的 Extra 列的结果是 Using index。index 通常比 ALL 快，因为索引的大小通常小于表数据。二是按索引的顺序来查找数据行，执行了全表扫描。此时，explain 的 Extra 列的结果不会出现 Uses index。
    • range：范围扫描，表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了 index 的全索引扫描，比较常见的范围扫描是带有 BETWEEN 子句或 WHERE 子句里有 =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 等操作符
    • index_subquery：和 unique_subquery 类似，利用索引来关联子查询，不再扫描全表，只是子查询使用的是非唯一索引。但是一般情况下使用 SELECT子 查询时，MySQL 查询优化器会自动将子查询优化为联表查询，因此 type 不会显示为 index_subquery，而是 ref。
    • unique_subquery: ∶该类型和 eq_ref 类似，但是使用了 IN 查询，且子查询是主键或者唯一索引。unique_subquery 是一个索引查找函数，可以完全替换子查询，效率更高。
    • index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引。在这种情况下，key 列包含了使用的索引的清单，key_len 包含了使用的索引的最长的关键元素。
    • ref_or_null：该类型类似于 ref，对于某个字段即需要关联条件，也需要 NULL 值的情况下，查询优化器会选择这种访问方式。
    • fulltext：全文索引。
    • ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错。ref 可以用于使用 = 或 <=>操作符的带索引的列。
    • eq_ref：使用这种索引查找，MySQL知道最多只返回一条符合条件的记录。当使用了索引的全部组成部分，并且索引是 PRIMARYKEY 或 UNIQUE NOT NULL 才会使用该类型，性能仅次于 system 及 const。
    • const：针对主键或唯一索引的等值查询扫描,最多只返回一行数据. const查询速度非常快,因为它仅仅读取一次即可。
    • system：该表只有一行(相当于系统表), system 是 const 类型的特例。

  • possible_keys：显示 MySQL 可以使用哪些索引在该表中查找。如果这一列为 NULL 的话，那么表示连个可选的索引都没有。key 表示执行这条 SQL 语句的时候实际会选择的索引。

  • key_len：表示当前使用索引的长度，一般 key_len 的值越小越好。如果键是 NULL，则长度是 NULL。

    注意通过 key_len 值可以确定MySQL将实际使用一个多列索引中的几个字段。

    key_len 的长度计算公式： varchr(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

    varchr(10)变长字段且不允许NULL = 10 *( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

    char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

    char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

  • rows：表示估计要扫描的行，数字越小越好。那么 rows 字段是 EXPLAIN 里面最重要的，这个的性能和扫描的行数息息相关。在做 SQL 调优的时候，一个原则就是要降低扫描的行数。如果扫描的行数比较多的话，那么性能相对比较差。

  • filtered：表示符合查询条件的数据的百分比，这个数值也是越少越好，最大 100。比较有意思的是，用 rows x filtered 可以获得和下一张表连接的行数。例如：rows =100，filtered =50%，则和下一张表的连接数的行数是 50。当然了，这个计算出来的值也是一个估算值。

    在 MySQL 5.7 之前，想要显示此字段需使用 explain extended 命令。

    在 MySQL.5.7 及更高版本，explain 默认就会展示 filtered。

  • Extra：展示了 MySQL 不适合其他列的额外信息。

    • Child of 'table' pushed jion@1：此值只会在 NDB Cluster 下出现。

    • const row not found：例如查询语句 SELECT ... FROM table_name，而表为空。

    • Deleting all rows：对于 DELETE 语句，某些存储引擎（例如MyISAM）支持以一种简单而快速的方式删除所有表行的处理程序方法。Extra 如果引擎使用此优化，则会显示此值。

    • Distinct：MySQL 正在寻找不同的值，当找到第一个匹配的行后，将会停止为当前行组合搜索更多行。

    • FirstMatch(tbl_name) ：当前使用了半连接 FirstMatch 策略，用于tbl_name。

      等价传播可以跨越 semi-join 边界，但是不能跨越子查询边界。因此，使用 FirstMatch 将子查询转换成 semi-join 可以提供一个更好的执行计划。

      对于 FirstMatch 策略，优化器可以选择是否应该在子查询中使用的所有表都位于 join 前缀时运行 FirstMatch 策略，或者在稍后的某个时间点运行 FirstMatch 策略。

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

    • Impossible HAVING：该 HAVING 子句始终为 false，并且不能命中任何行。

    • Impossible WHERE：该WHERE子句始终为 false，并且不能命中任何行。

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

    • LooseScan(m..n)：LooseScan 策略是 semi join 子查询的一种执行策略，当前使用了半连接 LooseScan 策略。

    • No matching min/max row：没有任何行满足查询条件，例如 ，SELECT MIN(...) FROM ... WHERE condition 中的 condition 行。

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

    • No matching rows after partition pruning：对于 DELETE 或者 UPDATE，优化器在 partition pruning（分区修剪）之后，没有发现要 DELETE 或者 UPDATE 的内容。

    • No tables used：当此查询没有 FROM 子句或者拥有 FROM DUAL 子句时出现。对于 INSERT 或者 REPLACE语句， EXPLAIN 当没有 SELECT 部件时显示该值。例如，它出现是 EXPLAIN INSERT INTO t VALUES(10) 因为它等价于 EXPLAIN INSERT INTO t SELECT 10 FROM DUAL

    • Not exists：MySQL 能够对LEFT JOIN 查询进行优化，并且在找到符合 LEFT JOIN 条件的行之后，不会检查该表中前一行组合的更多行。

    SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

    假设 t2.id 定义成了NOT NULL，此时，MySQL 会扫描 t1，并使用 t1.id 的值查找 t2 中的行。如果 MySQL 在 t2 中找到一个匹配的行，它会知道 t2.id 永远不会为 NULL，并且不会扫描 t2 中具有相同 id 值的其余行。也就是说，对于 t1 中的每一行，MySQL 只需要在 t2 中只执行一次查找，而不考虑在 t2 中实际匹配的行数。

    在 MySQL8.0.17 及更高版本中，如果出现此提示，还可表示形如 NOT IN （subquery）或 NOT EXISTS （subquery）的 WHERE 条件已经在内部转换为反连接。这将删除子查询并将其表放入最顶层的查询计划中，从而改进查询的开销。通过合并半连接和反联接，优化器可以更加自由地对执行计划中的表重新排序，在某些情况下，可让查询提速。你可以通过在EXPLAIN语句后紧跟一个 SHOW WARNING 语句，并分析结果中的 Message 列，从而查看何时对该查询执行了反联接转换。

    两表关联只返回主表的数据，并且只返回主表与子表没关联上的数据，这种连接就叫反连接。

    • Plan isn't ready yet：使用了 EXPLAIN FOR CONNECTION，当优化器尚未完成为在命名连接中执行的语句创建执行计划时， 会出现此值。如果执行计划输出包含多行，则其中任何一行或所有行都可以具有此 Extra 值，具体取决于优化程序确定完整执行计划的进度。
    • Range checked for each record (index map: N)：MySQL 没有找到合适的索引去使用，但是去检查是否可以使用 range 或 index_merge 来检索行时，会出现此提示。index map N 索引的编号从 1 开始，按照与表的 SHOW INDEX 所示相同的顺序。索引映射值 N 是指示哪些索引是候选的位掩码值。例如 0x19 （二进制 11001）的值意味着将考虑索引 1、4 和 5。
    • Recursive：出现了递归查询。
    • Rematerialize：用得很少，使用类似如下SQL时，会展示 Rematerialize。
    • Scanned N databases：表示在处理 INFORMATION_SCHEMA 表的宣询时，扫描了几个目录，N的取值可以是 0，1 或者 all。
    • Select tables optimized away：优化器确定∶①最多返回1行；②要产生该行的数据，要读取一组确定的行，时会出现此提示。一般在用某些聚合函数访问存在索引的某个字段时，优化器会通过索引查接一次定位到所需要的数据行完成整个查询时展示。
    • Skip_open_table， Open_frm_only， Open_full_table：这些值表示透用于 INFORMATION_SCHEMA 表查询的文件打开优化：Skip_open_table：无需打开表文件，信息已经通过扫描数据字典获得；Open_frm_only：仅需要读取数据字典以获取表信息；Open_fuil_table：未优化的信息查找。表信息必须从数据字典以及表文件中读取。
    • Start temporary，End temporary：表示临时表使用 Dupllcate Weedout 策略。
    • unique row not found：对于形如 SELECT ... FROM tbl_name 的查询，但没有行能够满足唯一索引或主键查询的条件。
    • Using filesort：当 Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Explain 不会显示的告诉客户端用哪种排序。官方解释：“MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配 WHERE 子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行”
    • Using index：仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。从 MySQL 8.0.27 开始，它也显示为 EXPLAIN FORMAT=TREE。
avatar
文章
阅读
粉丝