全部产品
云市场
云游戏

EXPLAIN 和执行计划

更新时间:2020-08-03 17:44:13

与多数数据库系统类似,PolarDB-X在处理SQL时,会通过优化器生成执行计划,该执行计划由关系操作符构成一个树形结构,反映PolarDB-X如何执行SQL语句;不同的是,PolarDB-X本身不存储数据,更侧重考虑分布式环境中的网络IO开销,将运算下推到各个分库(如RDS MySQL)执行,从而提升SQL执行效率。用户可通过EXPLAIN命令查看SQL的执行计划。

本文着重介绍PolarDB-X执行计划中各个操作符的含义,以便用户通过查询计划了解SQL执行流程,从而有针对性的调优SQL。文中示例均基于如下表结构:

  1. CREATE TABLE `sbtest1` (
  2. `id` INT(10) UNSIGNED NOT NULL,
  3. `k` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  4. `c` CHAR(120) NOT NULL DEFAULT '',
  5. `pad` CHAR(60) NOT NULL DEFAULT '',
  6. KEY `xid` (`id`),
  7. KEY `k_1` (`k`)
  8. ) dbpartition BY HASH (`id`) tbpartition BY HASH (`id`) tbpartitions 4

先通过一个例子整体了解PolarDB-X执行计划的树形结构。

  1. mysql> explain select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 order by cnt limit 5, 10;
  2. +---------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +---------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | TmpSort(sort="cnt ASC", offset=?2, fetch=?3) |
  6. | Filter(condition="cnt > ?1") |
  7. | Aggregate(group="k", cnt="COUNT()") |
  8. | BKAJoin(id="id", k="k", c="c", pad="pad", id0="id0", k0="k0", c0="c0", pad0="pad0", condition="id = k", type="inner") |
  9. | MergeSort(sort="k ASC") |
  10. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?) ORDER BY `k`") |
  11. | UnionAll(concurrent=true) |
  12. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE ((`k` > ?) AND (`k` IN ('?')))") |
  13. | HitCache:false |
  14. +---------------------------------------------------------------------------------------------------------------------------------------------------+
  15. 9 rows in set (0.01 sec)

如上,PolarDB-X EXPLAIN的结果总体分为两部分:执行计划和其他信息。

  • 执行计划执行计划以缩进形式表示操作符之间的 “父-子” 关系。示例中,Filter是TmpSort的子操作符,同时是Aggregate的父操作符。从真正执行的角度看,每个操作符均从其子操作符中获取数据,经当前操作符处理,输出给其父操作符。为方便理解,将以上执行计划转换为更加直观的树形结构:
    execution-plan

  • 其他信息除执行计划外,EXPLAIN结果中还会有一些额外信息,目前仅有一项HitCache。需要说明的是PolarDB-X默认开启PlanCache功能,HitCache表示当前SQL是否命中PlanCache。开启PlanCache后,PolarDB-X会对SQL做参数化处理,参数化会将SQL中的大部分常量用?替换,并构建一个参数列表。在执行计划中的体现就是,LogicalView的sql中会有?,在部分操作符中会有类似?2的字样,这里的2表示其在参数列表中的下标,后续会结合具体的例子进一步阐述。

EXPLAIN语法

EXPLAIN用于查看SQL语句的执行计划,语法如下:

  1. EXPLAIN explainable_stmt
  2. explainable_stmt: {
  3. SELECT statement
  4. | DELETE statement
  5. | INSERT statement
  6. | REPLACE statement
  7. | UPDATE statement
  8. }

操作符介绍

本小节详细介绍PolarDB-X执行计划中各个操作符的含义。

LogicalView

LogicalView是从底层数据源获取数据的操作符。从数据库的角度来看,使用TableScan命名更符合常规,但考虑到PolarDB-X本身不存储数据,而是通过SQL从底层数据源获取,因此,该操作符中会记录下推的SQL语句和数据源信息,这更像一个“视图”。该“视图”中的SQL,通过优化器的下推,可能包含多种操作,如投影、过滤、聚合、排序、连接和子查询等。

以下通过示例说明EXPLAIN中LogicalView的输出信息及其含义:

  1. mysql> explain select * From sbtest1 where id > 1000;
  2. +-----------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +-----------------------------------------------------------------------------------------------------------------------+
  5. | UnionAll(concurrent=true) |
  6. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?)") |
  7. | HitCache:false |
  8. +-----------------------------------------------------------------------------------------------------------------------+
  9. 3 rows in set (0.00 sec)

LogicalView的信息由三部分构成:

  • tables:底层数据源对应的表名,以.分割,其前是分库对应的编号,其后是表名及其编号,对于连续的编号,会做简写,如[000-127],表示表名编号从000127的所有表。
  • shardCount:需要访问的分表总数,该示例中会访问从000127共128张分表。
  • sql:下发至底层数据源的SQL模版。这里显示的并非真正下发的SQL语句,PolarDB-X在执行时会将表名替换为物理表名;另外,SQL中的常量10?替换,这是因为PolarDB-X默认开启了PlanCache功能,对SQL做了参数化处理。

UnionAll

UnionAll是UNION ALL对应的操作符,该操作符通常有多个输入,表示将多个输入的数据UNION在一起。以上示例中,LogicalView之上的UnionAll表示将所有分表中的数据进行UNION。

UnionAll中的concurrent表示是否并行执行其子操作符,默认为true。

UnionDistinct

与UnionAll类似,UnionDistinct是UNION DISTINCT对应的操作符。如下:

  1. mysql> explain select * From sbtest1 where id > 1000 union distinct select * From sbtest1 where id < 200;
  2. +-------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +-------------------------------------------------------------------------------------------------------------------------+
  5. | UnionDistinct(concurrent=true) |
  6. | UnionAll(concurrent=true) |
  7. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?)") |
  8. | UnionAll(concurrent=true) |
  9. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` < ?)") |
  10. | HitCache:false |
  11. +-------------------------------------------------------------------------------------------------------------------------+
  12. 6 rows in set (0.02 sec)

MergeSort

MergeSort,归并排序操作符,通常有多个子操作符。PolarDB-X中实现了两种排序:基于有序数据的归并排序和对无序数据的内存排序。如下:

  1. mysql> explain select *from sbtest1 where id > 1000 order by id limit 5,10;
  2. +---------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +---------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | MergeSort(sort="id ASC", offset=?1, fetch=?2) |
  6. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?) ORDER BY `id` LIMIT (? + ?)") |
  7. | HitCache:false |
  8. +---------------------------------------------------------------------------------------------------------------------------------------------------+
  9. 3 rows in set (0.00 sec)

MergeSort 操作符包含三部分内容:

  • sort:表示排序字段以及排列顺序,id ASC表示按照id字段递增排序,DESC表示递减排序。
  • offset:表示获取结果集时的偏移量,同样由于对SQL做了参数化,示例中的offst表示为?1,其中?表示这是一个动态参数,其后的数字对应参数列表的下标。示例中SQL对应的参数为[1000, 5, 10],因此,?1实际对应的值为5
  • fetch:表示最多返回的数据行数。与offset类似,同样是参数化的表示,实际对应的值为10

Aggregate

Aggregate是聚合操作符,通常包含两部分内容:Group By字段和聚合函数。如下:

  1. mysql> explain select k, count(*) from sbtest1 where id > 1000 group by k;
  2. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | Aggregate(group="k", count(*)="SUM(count(*))") |
  6. | MergeSort(sort="k ASC") |
  7. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT `k`, COUNT(*) AS `count(*)` FROM `sbtest1` WHERE (`id` > ?) GROUP BY `k` ORDER BY `k`") |
  8. | HitCache:true |
  9. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  10. 4 rows in set (0.00 sec)

Aggregate 包含两部分内容:

  • group:表示GROUP BY字段,示例中为k
  • 聚合函数:=前为聚合函数对应的输出列名,其后为对应的计算方法。示例中count(*)="SUM(count(*))",第一个count(*)对应输出的列名,随后的SUM(count(*))表示对其输入数据中的count(*)列进行SUM运算得到最终的count(*)

由此可见,PolarDB-X将聚合操作分为两部分,首先将聚合操作下推至底层数据源做局部聚合,最终在PolarDB-X层面对局部聚合的结果做全局聚合。另外,PolarDB-X的最终聚合是基于排序做的,因此,会在优化器阶段为其添加一个Sort子操作符,而Sort操作符又进一步通过下推Sort转换为MergeSort

再来看一个AVG聚合函数的例子,如下:

  1. mysql> explain select k, avg(id) avg_id from sbtest1 where id > 1000 group by k;
  2. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN|
  4. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | Project(k="k", avg_id="sum_pushed_sum / sum_pushed_count")|
  6. | Aggregate(group="k", sum_pushed_sum="SUM(pushed_sum)", sum_pushed_count="SUM(pushed_count)")|
  7. | MergeSort(sort="k ASC")|
  8. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT `k`, SUM(`id`) AS `pushed_sum`, COUNT(`id`) AS `pushed_count` FROM `sbtest1` WHERE (`id` > ?) GROUP BY `k` ORDER BY `k`")|
  9. | HitCache:false|
  10. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 5 rows in set (0.01 sec)

PolarDB-X会将AVG聚合函数转换为SUM / COUNT,再分别根据SUMCOUNT的下推规则,将其转换为局部聚合和全局聚合。用户可自行尝试了解其他聚合函数的执行计划。

注意:PolarDB-X会将DISTINCT操作转换为GROUP操作,如下:

  1. mysql> explain select distinct k from sbtest1 where id > 1000;
  2. +-----------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +-----------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | Aggregate(group="k") |
  6. | MergeSort(sort="k ASC") |
  7. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT `k` FROM `sbtest1` WHERE (`id` > ?) GROUP BY `k` ORDER BY `k`") |
  8. | HitCache:false |
  9. +-----------------------------------------------------------------------------------------------------------------------------------------------------+
  10. 4 rows in set (0.02 sec)

TmpSort

TmpSort,表示在内存中对数据进行排序。与MergeSort的区别在于,MergeSort可以有多个子操作符,且每个子操作符返回的数据都已经排序。TmpSort仅有一个子操作符。

TmpSort对应的查询计划信息与MergeSort一致,请参考MergeSort。

Project

Project表示投影操作,即从输入数据中选择部分列输出,或者对某些列进行转换(通过函数或者表达式计算)后输出,当然,也可以包含常量。以上AVG的示例中,最顶层就是一个Project,其输出ksum_pushed_sum / sum_pushed_count,后者对应的列名为avg_id

  1. mysql> explain select '你好, DRDS', 1 / 2, CURTIME();
  2. +-------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +-------------------------------------------------------------------------------------+
  5. | Project(你好, DRDS="_UTF-16'你好, DRDS'", 1 / 2="1 / 2", CURTIME()="CURTIME()") |
  6. | |
  7. | HitCache:false |
  8. +-------------------------------------------------------------------------------------+
  9. 3 rows in set (0.00 sec)

可见,Project的计划中包括每列的列名及其对应的列、值、函数或者表达式。

Filter

Filter表示过滤操作,其中包含一些过滤条件。该操作符对输入数据进行过滤,若满足条件,则输出,否则丢弃。如下是一个较复杂的例子,包含了以上介绍的大部分操作符。

  1. mysql> explain select k, avg(id) avg_id from sbtest1 where id > 1000 group by k having avg_id > 1300;
  2. +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | Filter(condition="avg_id > ?1") |
  6. | Project(k="k", avg_id="sum_pushed_sum / sum_pushed_count") |
  7. | Aggregate(group="k", sum_pushed_sum="SUM(pushed_sum)", sum_pushed_count="SUM(pushed_count)") |
  8. | MergeSort(sort="k ASC") |
  9. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT `k`, SUM(`id`) AS `pushed_sum`, COUNT(`id`) AS `pushed_count` FROM `sbtest1` WHERE (`id` > ?) GROUP BY `k` ORDER BY `k`") |
  10. | HitCache:false |
  11. +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  12. 6 rows in set (0.01 sec)

在以上AVG示例的 SQL 基础上添加了having avg_id > 1300,执行计划最上层添加了一个 Filter 操作符,用于过滤所有满足avg_id > 1300的数据。

有读者可能会问,WHERE中的条件为什么没有对应的Filter操作符呢?在PolarDB-X优化器的某个阶段,WHERE条件的Filter操作符的确是存在的,只是最终将其下推到了LogiacalView中,因此可以在LogicalView的sql中看到id > 1000

NlJoin

NlJoin,表示NestLoop Join操作符,即使用NestLoop方法进行两表Join。PolarDB-X中实现了两种JOIN策略:NlJoin和BKAJoin,后者表示Batched Key Access Join,批量键值查询,会从左表取一批数据,构建一个IN条件拼接在访问右表的SQL中,从右表一次获取一批数据。

  1. mysql> explain select a.* from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000;
  2. +----------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +----------------------------------------------------------------------------------------------------------------------------+
  5. | Project(id="id", k="k", c="c", pad="pad") |
  6. | NlJoin(id="id", k="k", c="c", pad="pad", k0="k0", condition="id = k", type="inner") |
  7. | UnionAll(concurrent=true) |
  8. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?)") |
  9. | UnionAll(concurrent=true) |
  10. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT `k` FROM `sbtest1` WHERE (`k` > ?)") |
  11. | HitCache:false |
  12. +----------------------------------------------------------------------------------------------------------------------------+
  13. 7 rows in set (0.03 sec)

NlJOIN的计划包括三部分内容:

  • 输出列信息:输出的列名,示例中的JOIN会输出5列id="id", k="k", c="c", pad="pad", k0="k0"
  • contition:连接条件,示例中连接条件为id = k
  • type:连接类型,示例中是INNER JOIN,因此其连接类型为inner

BKAJoin

BKAJoin,Batched Key Access Join,表示通过批量键值查询的方式进行JOIN,即从左表取一批数据,构建一个IN条件拼接在访问右表的SQL中,从右表一次获取一批数据进行JOIN。

  1. mysql> explain select a.* from sbtest1 a, sbtest1 b where a.id = b.k order by a.id;
  2. +-------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +-------------------------------------------------------------------------------------------------------------------------------+
  5. | Project(id="id", k="k", c="c", pad="pad") |
  6. | BKAJoin(id="id", k="k", c="c", pad="pad", id0="id0", k0="k0", c0="c0", pad0="pad0", condition="id = k", type="inner") |
  7. | MergeSort(sort="id ASC") |
  8. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` ORDER BY `id`") |
  9. | UnionAll(concurrent=true) |
  10. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`k` IN ('?'))") |
  11. | HitCache:false |
  12. +-------------------------------------------------------------------------------------------------------------------------------+
  13. 7 rows in set (0.01 sec)

BKAJoin的计划内容与NlJoin相同,这两个操作符命名不同,旨在告知执行器以何种方法执行JOIN操作。另外,以上执行计划中右表的LogicalView中'k' IN ('?') 是优化器构建出来的对右表的IN查询模板。

LogicalModifyView

如上文介绍,LogicalView表示从底层数据源获取数据的操作符,与之对应的LogicalModifyView表示对底层数据源的修改操作符,其中也会记录一个SQL语句,该SQL可能是INSERT、UPDATE或者DELETE。

  1. mysql> explain update sbtest1 set c='Hello, DRDS' where id > 1000;
  2. +--------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +--------------------------------------------------------------------------------------------------------------------------------+
  5. | LogicalModifyView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="UPDATE `sbtest1` SET `c` = ? WHERE (`id` > ?)") |
  6. | HitCache:false |
  7. +--------------------------------------------------------------------------------------------------------------------------------+
  8. 2 rows in set (0.03 sec)
  9. mysql> explain delete from sbtest1 where id > 1000;
  10. +-------------------------------------------------------------------------------------------------------------------------+
  11. | LOGICAL PLAN |
  12. +-------------------------------------------------------------------------------------------------------------------------+
  13. | LogicalModifyView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="DELETE FROM `sbtest1` WHERE (`id` > ?)") |
  14. | HitCache:false |
  15. +-------------------------------------------------------------------------------------------------------------------------+
  16. 2 rows in set (0.03 sec)

LogicalModifyView查询计划的内容与LogicalView类似,包括下发的物理分表,分表数以及SQL模版。同样,由于开启了PlanCache,对SQL做了参数化处理,SQL模版中的常量会用?替换。

PhyTableOperation

PhyTableOperation表示对某个物理分表执行一个操作。该操作符目前仅用于INSERT INTO … VALUES …。

  1. mysql> explain insert into sbtest1 values(1, 1, '1', '1'),(2, 2, '2', '2');
  2. +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | PhyTableOperation(tables="SYSBENCH_CORONADB_1526954857179TGMMSYSBENCH_CORONADB_VGOC_0000_RDS.[sbtest1_001]", sql="INSERT INTO ? (`id`, `k`, `c`, `pad`) VALUES(?, ?, ?, ?)", params="`sbtest1_001`,1,1,1,1") |
  6. | PhyTableOperation(tables="SYSBENCH_CORONADB_1526954857179TGMMSYSBENCH_CORONADB_VGOC_0000_RDS.[sbtest1_002]", sql="INSERT INTO ? (`id`, `k`, `c`, `pad`) VALUES(?, ?, ?, ?)", params="`sbtest1_002`,2,2,2,2") |
  7. | |
  8. | HitCache:false |
  9. +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  10. 4 rows in set (0.00 sec)

示例中,INSERT插入两行数据,每行数据对应一个PhyTableOperation操作符,PhyTableOperation操作符的内容包括三部分:

  • tables:物理表名,仅有唯一一个物理表名。
  • sql:SQL 模版,该SQL模版中表名和常量均被参数化,用?替换,对应的参数在随后的params中给出。
  • params:SQL模版对应的参数,包括表名和常量。

其他信息

HitCache

PolarDB-X会默认开启PlanCache 功能,HitCache用于告知用户当前查询是否命中PlanCache。如下,第一次运行HitCache为 false,第二次运行为true。

  1. mysql> explain select * From sbtest1 where id > 1000;
  2. +-----------------------------------------------------------------------------------------------------------------------+
  3. | LOGICAL PLAN |
  4. +-----------------------------------------------------------------------------------------------------------------------+
  5. | UnionAll(concurrent=true) |
  6. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?)") |
  7. | HitCache:false |
  8. +-----------------------------------------------------------------------------------------------------------------------+
  9. 3 rows in set (0.01 sec)
  10. mysql> explain select * From sbtest1 where id > 1000;
  11. +-----------------------------------------------------------------------------------------------------------------------+
  12. | LOGICAL PLAN |
  13. +-----------------------------------------------------------------------------------------------------------------------+
  14. | UnionAll(concurrent=true) |
  15. | LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?)") |
  16. | HitCache:true |
  17. +-----------------------------------------------------------------------------------------------------------------------+
  18. 3 rows in set (0.00 sec)