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

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

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

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

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

1Impossible 系列

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

Impossible WHERE

WHERE 永远不可能为真。

代码语言：javascript代码运行次数：0运行复制

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

Impossible HAVIG

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

代码语言：javascript代码运行次数：0运行复制

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

Impossible WHERE noticed after reading ct tables

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

代码语言：javascript代码运行次数：0运行复制

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

2o matching/o table 系列

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

o matching min/max row

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

代码语言：javascript代码运行次数：0运行复制

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

no matching row in ct table

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

代码语言：javascript代码运行次数：0运行复制

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

o matching rows after partition pruning

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

代码语言：javascript代码运行次数：0运行复制

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

o tables used

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

代码语言：javascript代码运行次数：0运行复制

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

Recursive

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

代码语言：javascript代码运行次数：0运行复制

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 ***************************
...
*************************** . row ***************************
           id: 
  select_type: UIO
        table: tmp
   partiti: ULL
         type: ALL
possible_keys: ULL
          key: ULL
      key_len: ULL
          ref: ULL
         rows: 2
     filtered: 50.00
        Extra: Recursive; Using where
 rows in set, 1 warning (0.01 sec)

4Rematerialize

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

代码语言：javascript代码运行次数：0运行复制

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
   partiti: ULL
         type: ALL
possible_keys: ULL
          key: ULL
      key_len: ULL
          ref: ULL
         rows: 101700
     filtered: 100.00
        Extra: Rematerialize (<derived2>)
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partiti: ULL
         type: ALL
possible_keys: ULL
          key: ULL
      key_len: ULL
          ref: ULL
         rows: 51
     filtered: 100.00
        Extra: ULL
*************************** . row ***************************
           id: 2
  select_type: DEPEDET DERIVED
        table: t1
   partiti: ULL
         type: ref
possible_keys: idx_r4
          key: idx_r4
      key_len: 5
          ref: ytt.a.r4
         rows: 51
     filtered: 100.00
        Extra: ULL
 rows in set, 2 warnings (0.01 sec)

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

代码语言：javascript代码运行次数：0运行复制

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
   partiti: ULL
         type: ALL
possible_keys: idx_r4
          key: ULL
      key_len: ULL
          ref: ULL
         rows: 101700
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partiti: ULL
         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 秒，时间的确有点长。

代码语言：javascript代码运行次数：0运行复制

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;
...
528004 rows in set (21.70 sec)

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

代码语言：javascript代码运行次数：0运行复制

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;
...
528004 rows in set (5.01 sec)

5canned databases

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

代码语言：javascript代码运行次数：0运行复制

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

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

代码语言：javascript代码运行次数：0运行复制

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

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

本文参与 腾讯云自媒体同步曝光计划，分享自。原始发表：2025-01-20，如有侵权请联系 cloudcommunity@tencent 删除优化nullrowselecttable