第 50 期：根据 EXPLAIN EXTRA 栏提示进行优化（二）

爱可生开源社区

发布于 2025-01-22 12:50:35

6900

代码可运行

文章被收录于专栏：爱可生开源社区爱可生开源社区

运行总次数：0

代码可运行

作者：杨涛涛，爱可生技术专家。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

接着上一篇继续，依然是举例说明 EXPLAIN EXTRA 提示栏里的相关释义，这些提示将有助于我们更好的理解 SQL 语句在 MySQL 里的具体执行计划。

1Impossible 系列

表示这条查询永远不可能获取到结果。类似的有：Impossible WHERE、Impossible HAVING 等等。

Impossible WHERE

WHERE 永远不可能为真。

localhost:ytt>desc select * from t1 where null=0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE
1 row in set, 1 warning (0.00 sec)

Impossible HAVING

GROUP BY 过滤后筛选条件不可能为真。

localhost:ytt>desc select r1 from t1 group by r1 having false\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
...
        Extra: Impossible HAVING
1 row in set, 1 warning (0.01 sec)

Impossible WHERE noticed after reading const tables

读取主键或者常量记录集，再次筛选后不可能获取到结果。

localhost:ytt>desc select * from t1 where f1=6 and f0=110 and r4=900\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
...
        Extra: Impossible WHERE noticed after reading const tables
1 row in set, 1 warning (0.00 sec)

2No matching/No table 系列

表示这条 SQL 语句没有匹配到想要的结果集。究其原因要么是表本身无记录，要么是 SQL 语句的过滤条件（也即 WHERE 子句适配的字段比对）不可能筛选到想要的结果等等。

No matching min/max row

没有匹配的最小最大记录。

localhost:ytt>desc select min(r1) from t2 where 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
...
        Extra: No matching min/max row
1 row in set, 1 warning (0.01 sec)

no matching row in const table

主键或者常量记录集没有匹配的记录。

localhost:ytt>desc select * from t1 where f1=6 and f0=112 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
...
        Extra: no matching row in const table
1 row in set, 1 warning (0.00 sec)

No matching rows after partition pruning

和 Impossible WHERE 类似，不同的是在优化器在分区裁剪优化过程中发现不可能匹配的记录。

localhost:ytt>desc delete from t1_p where false\G
*************************** 1. row ***************************
           id: 1
  select_type: DELETE
        table: NULL
...
        Extra: No matching rows after partition pruning
1 row in set, 1 warning (0.00 sec)

No tables used

表示使用常量，没有检索表。

localhost:ytt>desc select now()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
...
        Extra: No tables used
1 row in set, 1 warning (0.00 sec)

3Recursive

表示 SQL 语句包含有递归的通用表达式。这种提示也不是说有就是不够优化，重点在于层级深或者浅的问题。当然递归层级越浅资源占用越少，就越优化。

localhost:ytt>desc with recursive tmp(a) as (select 1 union all select a+1 from tmp where a < 10) table tmp\G
*************************** 1. row ***************************
...
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: tmp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Recursive; Using where
3 rows in set, 1 warning (0.01 sec)

4Rematerialize

重新物化！常见于横向派省表（lateral derived table）向上扫描外层表的相关记录，每读取一条新记录就重新物化一次。举个例子：

localhost:ytt>desc select a.* from t1 a, lateral (select * from t1 where r4 = a.r4) b\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 101700
     filtered: 100.00
        Extra: Rematerialize (<derived2>)
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 51
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT DERIVED
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_r4
          key: idx_r4
      key_len: 5
          ref: ytt.a.r4
         rows: 51
     filtered: 100.00
        Extra: NULL
3 rows in set, 2 warnings (0.01 sec)

一般来讲，出现这个提示，也即表明有可能派生表的合并开关没有开（derived_merge=off），效率低下。可以打开这个开关，再次执行同样的 SQL。


localhost:ytt>set optimizer_switch='derived_merge=on';
Query OK, 0 rows affected (0.00 sec)

localhost:ytt>desc  select a.* from t1 a, lateral (select * from t1 where r4 = a.r4) b\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: idx_r4
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 101700
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_r4
          key: idx_r4
      key_len: 5
          ref: ytt.a.r4
         rows: 51
     filtered: 100.00
        Extra: Using index
2 rows in set, 2 warnings (0.00 sec)

可以看下两种常见下的执行时间：

提示有 Rematerialize 的执行时间为 21 秒，时间的确有点长。


localhost:ytt>set optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

localhost:ytt>select a.* from t1 a, lateral (select * from t1 where r4 = a.r4) b;
...
5283004 rows in set (21.70 sec)

优化掉 Rematerialize 的执行时间为 5 秒，快了很多。


localhost:ytt>set optimizer_switch='derived_merge=on';
Query OK, 0 rows affected (0.00 sec)

localhost:ytt>select a.* from t1 a, lateral (select * from t1 where r4 = a.r4) b;
...
5283004 rows in set (5.01 sec)

5canned N databases

这个表示扫描数据字典库（information_schema）时，扫描的磁盘目录数量，N 有三个值，分别是 0、1、ALL。以下是 MySQL 5.7 版本下的执行计划：

localhost:information_schema>desc select * from tables\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tables
   partitions: NULL
         type: ALL
...
        Extra: Open_full_table; Scanned all databases
1 row in set, 1 warning (0.00 sec)

MySQL 8.0 之后的版本由于对数据字典进行重新设计，而且系统表都默认加了索引，所以执行计划也有少许变化。比如表 tables 由临时表直接变为一个视图。

localhost:information_schema>desc select * from tables\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cat
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: name
      key_len: 194
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index
   ...

那本篇到此为止，欢迎继续订阅。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-01-20，如有侵权请联系 cloudcommunity@tencent.com 删除

优化