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

往期推荐

第 46 期：EXPLAIN：解说一条简单语句的执行计划

第 47 期：EXPLAIN TYPE 列的 JOIN 常见场景详解（上）

第 48 期：EXPLAIN TYPE 列的 JOIN 常见场景详解（下）

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

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

本期我们继续对 MySQL 的执行计划中 EXTRA 的内容进行解读。

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

1关于 SEMI JOIN 的几个 Extra 提示

FirstMatch： 一般是外层查询块对内层查询块检索时，扫描的匹配项个数。这里指的是只扫描第一个匹配项即可。

比如经典的 EXISTS 子句，EXISTS 子句只需要内层查询结果为真即可，不需要过多的判断，所以遇到 FirstMatch 即代表 MySQL 对内层的嵌套表使用了这个优化策略。

举个例子：以下 SQL 执行计划结果显示这条 SQL 使用了索引，并且具备 FristMatch 条件（ 内层查询 只需要有记录返回即可）。

localhost:ytt>desc select count(*) from t1 where exists (select  * from y1 limit 10)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: y1
   partitions: NULL
         type: index
possible_keys: NULL
          key: id
      key_len: 8
          ref: NULL
         rows: 461756
     filtered: 100.00
        Extra: Using index; FirstMatch
*************************** 2. row ***************************
...
2 rows in set, 1 warning (0.00 sec)

复制

Start temporary, End temporary： 把 SEMI JOIN 当做 INNER JOIN，然后对其结果建立一张预置主键的临时表，用临时表来达到去重效果后再返回。

举个例子：以下执行计划结果显示，先处理内层查询，并且同时生成临时表，针对外层表做 JOIN ，完后结果放入临时表进行去重。

localhost:ytt>desc select * from t1 a where (a.f0,a.f1) in (select b.r1,b.r2 from t1 b)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: idx_r1,idx_r2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 101700
     filtered: 100.00
        Extra: Using where; Start temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: ytt.b.r1,ytt.b.r2
         rows: 1
     filtered: 100.00
        Extra: End temporary
2 rows in set, 1 warning (0.00 sec)

复制

LooseScan：类似 GROUP BY 优化策略里的松散索引扫描。

也就是说对于内层表来讲，用于和外层表来匹配的字段如果有索引，可以走一遍 covering index ，基于索引的有序性进行分组，完后每组只取出第一行和外层查询块来匹配检索，这样一来，可以有效减少外层查询的次数。 不过这里需要注意的一点是：我描述的外层查询块和内层查询块仅限于查询语句本身的编写形式，与优化器实际执行的表顺序无关。

举个例子： 表 b
为驱动表，使用 LooseScan 策略按照字段 r1
分组后和表 a
做 JOIN。

localhost:ytt>desc select * from t1 a where r1  in (select r1 from t2 b)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: index
possible_keys: idx_r1
          key: idx_r1
      key_len: 5
          ref: NULL
         rows: 1000
     filtered: 97.50
        Extra: Using where; Using index; LooseScan
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ref
possible_keys: idx_r1
          key: idx_r1
      key_len: 5
          ref: ytt.b.r1
         rows: 5
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.01 sec)

复制

我们来看下表 t2
按照字段 r1
的分组数据： 一共 1000 条记录，对 r1
进行分组后，有 100 个组。

localhost:ytt>select count(r1),count(distinct r1) from t2;
+-----------+--------------------+
| count(r1) | count(distinct r1) |
+-----------+--------------------+
|      1000 |                100 |
+-----------+--------------------+
1 row in set (0.00 sec)

复制

不过从以上简单的执行计划看不到实际的效果，只能是一个预估场景。可以对查询语句执行 EXPLAIN ANALYZE 来看下实际优化效果:

MySQL 对表 b
进行了去重后与表 a
做 JOIN， 记录数和我们以上的分析一致。(actual time=0.067..0.792 rows=100 loops=1)

localhost:ytt>desc analyze select * from t1 a where r1  in (select r1 from t2 b)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=5772.48 rows=5041) (actual time=3.247..10.303 rows=525 loops=1)
    -> Remove duplicates from input sorted on idx_r1  (cost=98.15 rows=975) (actual time=0.067..0.792 rows=100 loops=1)
        -> Filter: (b.r1 is not null)  (cost=98.15 rows=975) (actual time=0.065..0.690 rows=1000 loops=1)
            -> Index scan on b using idx_r1  (cost=98.15 rows=1000) (actual time=0.064..0.569 rows=1000 loops=1)
    -> Index lookup on a using idx_r1 (r1=b.r1)  (cost=5170.75 rows=5) (actual time=0.090..0.094 rows=5 loops=100)

1 row in set (0.02 sec)

复制

2Range checked for each record

Range checked for each record 是在使用索引和全表扫描之间的一个折中优化项。

一般来讲，在 TYPE 栏结果为 RANGE 或者 index_merge 时可能会使用。往细了讲就是这条 SQL 语句对于 MySQL 优化器本身来讲，是可以使用索引扫描的，不过由于某些特殊原因，没法使用索引（比如数据类型转换、编码转换等内部操作）时，优化器做的一个这种优化措施，会比全表扫快，但是比索引扫慢！

举个例子：下面 SQL 两表 JOIN 的字段编码不一致，导致 MySQL 无法使用现有索引，转而使用 Range checked for each record。

localhost:ytt>desc select a.* from y1 a left join y1_sub b on binary a.r1 = b.r1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_r1
      key_len: 43
          ref: NULL
         rows: 461740
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: idx_r1
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100316
     filtered: 100.00
        Extra: Range checked for each record (index map: 0x8)
2 rows in set, 2 warnings (0.00 sec)

复制

其中表 y1
的字段 r1
定义如下：

r1 varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci DEFAULT NULL,

复制

表 y1_sub
的字段 r1
定义如下：

r1 varchar(30) CHARACTER SET gb18030 COLLATE gb18030_chinese_ci DEFAULT NULL,

复制

两个字段的编码、数据类型严格意义来讲，都不一致，所以没法使用索引。

这里 (index map: 0x8) 表示可能使用的索引在表上的顺序，以位映射的方式展示，比如 0x8 表示表 y1_sub
第四个索引，对应“show index from y1_sub” 的第四行。

 localhost:ytt>select conv(0x8,8,2) as 'index map';
+-----------+
| index map |
+-----------+
| 1000      |
+-----------+
1 row in set (0.01 sec)

复制

我们对于 EXTRA 栏的结果分析还未完，请继续关注下一期！

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle

扫描下方的微信扫描小程序码，进行在线咨询预约：

此外，您也可以直接联系我们的商业支持团队获取更多信息，联系方式如下：

400-820-6580 / 13916131869 / 18930110869