【Oceanbase系列】——OceanBase SQL执行计划

本文主要是介绍【Oceanbase系列】——OceanBase SQL执行计划，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、前言

     能点开我这篇文章的，相信应该都是有点数据库背景的，SQL语句在不同的人手中会写出不同的语句形式，比如经常遇到的SQL慢查询，这时候往往需要针对SQL进行优化。而像orancle和mysql中为保证SQL语句能够高效的运行，提供了一个Explain的命令，用来对SQL语句进行语义分析，供开发者来针对SQL进行优化。当然了 oceanbase是蚂蚁自研的分布式数据库，支持mysql和orancle两种模式，既然敢吹分布式这个牛，那这里边的执行计划就不得不提一下，毕竟你的sql是要跑在分布式机器上的，执行计划的好坏直接影响数据库的性能。

二、Oceanbase执行计划知识点脑图

     执行计划是对一条 SQL 查询语句在数据库中执行过程的描述。用户可以通过 EXPLAIN 命令查看优化器针对给定 SQL 生成的逻辑执行计划。如果要分析某条 SQL 的性能问题，通常需要先查看 SQL 的执行计划，排查每一步 SQL 执行是否存在问题。在oceanbase中学习执行计划，可以看一下下边我梳理的图：

 三、EXPLAIN 命令格式

OceanBase 数据库的执行计划命令有三种模式：EXPLAIN BASIC、EXPLAIN 和 EXPLAIN EXTENDED。这三种模式对执行计划展现不同粒度的细节信息:

• EXPLAIN BASIC 命令用于最基本的计划展示。

• EXPLAIN EXTENDED 命令用于最详细的计划展示（通常在排查问题时使用这种展示模式）。

• EXPLAIN 命令所展示的信息可以帮助普通用户了解整个计划的执行方式。

EXPLAIN [BASIC | EXTENDED | PARTITIONS | FORMAT = format_name] explainable_stmt
format_name: { TRADITIONAL | JSON }
explainable_stmt: { SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement }

执行计划形状与算子信息

obclient>SELECT /*TPC-DS Q3*/ * 
     FROM   (SELECT dt.d_year, 
               item.i_brand_id    brand_id, 
               item.i_brand       brand, 
               Sum(ss_net_profit) sum_agg 
          FROM   date_dim dt, 
               store_sales, 
               item 
          WHERE  dt.d_date_sk = store_sales.ss_sold_date_sk 
               AND store_sales.ss_item_sk = item.i_item_sk 
               AND item.i_manufact_id = 914 
               AND dt.d_moy = 11 
         GROUP  BY dt.d_year, 
                  item.i_brand, 
                  item.i_brand_id 
         ORDER  BY dt.d_year, 
                  sum_agg DESC, 
                  brand_id) 
     WHERE  rownum <= 100; 
 

OceanBase 数据库执行计划展示如下：

|ID|OPERATOR              |NAME       |EST. ROWS|COST |
-------------------------------------------------------
|0 |LIMIT                 |           |100      |81141|
|1 | TOP-N SORT           |           |100      |81127|
|2 |  HASH GROUP BY       |           |2924     |68551|
|3 |   HASH JOIN          |           |2924     |65004|
|4 |    SUBPLAN SCAN      |VIEW1      |2953     |19070|
|5 |     HASH GROUP BY    |           |2953     |18662|
|6 |      NESTED-LOOP JOIN|           |2953     |15080|
|7 |       TABLE SCAN     |ITEM       |19       |11841|
|8 |       TABLE SCAN     |STORE_SALES|161      |73   |
|9 |    TABLE SCAN        |DT         |6088     |29401|
=======================================================

由示例可见，OceanBase 数据库的计划展示与 Oracle 数据库类似。OceanBase 数据库执行计划中的各列的含义如下：

说明 
在表操作中，NAME 字段会显示该操作涉及的表的名称（别名），如果是使用索引访问，还会在名称后的括号中展示该索引的名称， 例如 t1(t1_c2) 表示使用了 t1_c2 这个索引。如果扫描的顺序是逆序，还会在后面使用 RESERVE 关键字标识，例如  t1(t1_c2,RESERVE)。

OceanBase 数据库 EXPLAIN 命令输出的第一部分是执行计划的树形结构展示。其中每一个操作在树中的层次通过其在 operator 中的缩进予以展示。树的层次关系用缩进来表示，层次最深的优先执行，层次相同的以特定算子的执行顺序为标准来执行。

上述 TPCDS Q3 示例的计划展示树如下:

OceanBase 数据库 EXPLAIN 命令输出的第二部分是各操作算子的详细信息，包括输出表达式、过滤条件、分区信息以及各算子的独有信息（包括排序键、连接键、下压条件等）。示例如下：

Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC]), prefix_pos(1)
  1 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil),
      equal_conds([t1.c1 = t2.c2]), other_conds(nil)
  2 - output([t2.c1], [t2.c2]), filter(nil), sort_keys([t2.c2, ASC])
  3 - output([t2.c2], [t2.c1]), filter(nil),
      access([t2.c2], [t2.c1]), partitions(p0)
  4 - output([t1.c1], [t1.c2]), filter(nil),
      access([t1.c1], [t1.c2]), partitions(p0)

四、OceanBase 执行计划展示

       使用 EXPLAIN 命令可以展示出当前优化器所生成的执行计划，但由于统计信息变化、用户 session 变量设置变化等，会造成该 SQL 在计划缓存中实际对应的计划可能与 EXPLAIN 的结果并不相同。为了确定该 SQL 在系统中实际使用的执行计划，需要进一步分析计划缓存中的物理执行计划。用户可以通过查询 (g)v$plan_cache_plan_explain 视图来展示某条 SQL 在计划缓存中的执行计划。

第一步：查询 SQL 在计划缓存中的 plan_id

OceanBase 数据库每个服务器的计划缓存都是独立的。用户可以直接访问 v$plan_cache_plan_stat 视图查询本服务器上的计划缓存并提供 tenant_id 和需要查询的 SQL 字符串（可以使用模糊匹配），查询该条 SQL 在计划缓存中对应的 plan_id。

第二步：使用 plan_id 展示对应执行计划

获得 plan_id 后，用户可以使用 tenant_id 和 plan_id 访问 v$plan_cache_plan_explain 来展示该执行计划。

注意
这里展示的计划为物理执行计划，在算子命名上会与 EXPLAIN 所展示的逻辑执行计划有所不同。
如果访问 gv$plan_cache_plan_explain，必须给定 IP、port、tenant_id 和 plan_id 这四列的值。
如果访问 v$plan_cache_plan_explain，必须给定 tenant_id 和 plan_id 的值，否则系统将返回空集。

五、并行执行

       对于 Shared-Nothing 的分布式系统，由于一个关系数据表的数据会以分区的方式存放在系统里面的各个节点上，所以对于跨分区的数据查询请求，必然会要求执行计划能够对多个节点的数据进行操作，因而 OceanBase 数据库具有分布式执行计划生成和执行能力。OceanBase 数据库的优化器会自动根据查询和数据的物理分布生成分布式执行计划。

5.1 OceanBase 分布式计划的生成

OceanBase 数据库的优化器会分为以下两大阶段来生成分布式的执行计划。

1. 第一阶段：不考虑数据的物理分布，生成所有基于本地关系优化的最优执行计划。在本地计划生成后，优化器会检查数据是否访问了多个分区，或者是否访问的是本地单分区表但是用户使用 HINT 强制采用了并行查询执行。

2. 第二阶段：生成分布式计划。根据执行计划树，在需要进行数据重分布的地方，插入 EXCHANGE 节点，从而将原先的本地计划树变成分布式执行计划。

生成分布式计划的过程就是在原始计划树上寻找恰当位置插入 EXCHANGE 算子的过程，在自顶向下遍历计划树的时候，需要根据相应算子的数据处理情况以及输入算子的数据分区情况，来决定是否需要插入 EXCHANGE 算子。如下示例为最简单的单表扫描：

obclient>CREATE TABLE t1 (v1 INT, v2 INT) PARTITION BY HASH(v1) PARTITIONS 5;
Query OK, 0 rows affected (0.12 sec)

obclient>EXPLAIN SELECT * FROM t1\G;
*************************** 1. row ***************************
Query Plan:
==============================================
|ID|OPERATOR               |NAME    |EST. ROWS|COST  |
------------------------------------------------------
|0 |PX COORDINATOR         |        |500000   |545109|
|1 | EXCHANGE OUT DISTR    |:EX10000|500000   |320292|
|2 |  PX PARTITION ITERATOR|        |500000   |320292|
|3 |   TABLE SCAN          |T1      |500000   |320292|
======================================================

Outputs & filters:
-------------------------------------
  0 - output([T1.V1], [T1.V2]), filter(nil)
  1 - output([T1.V1], [T1.V2]), filter(nil), dop=1
  2 - output([T1.V1], [T1.V2]), filter(nil)
  3 - output([T1.V1], [T1.V2]), filter(nil),
      access([T1.V1], [T1.V2]), partitions(p[0-4])

当 t1 是一个分区表，可以在 TABLE SCAN 上插入配对的 EXCHANGE 算子，从而将 TABLE SCAN 和 EXCHANGE OUT 封装成一个 job，可以用于并行的执行。

5.2 SQL语句并行执行流程

当用户给定的 SQL 语句需要访问的数据位于 2 台或 2 台以上 observer 时，就会启用并行执行，会执行如下步骤：

1. 用户所连接的这个 observer 将承担查询协调者（Query Coordinator，QC）的角色
2. QC 预约足够的线程资源
3. QC 将需要并行的计划拆成多个子计划（下称 DFO， Data Flow Operation），每个 DFO 包含若干个串行执行的算子。例如，一个 DFO 里包含了扫描分区，聚集，发送算子，另外一个 DFO 里包含了收集、聚集算子等。
4. QC 按照一定的逻辑顺序将 DFO 调度到合适的 observer 上执行，observer 上会临时启动一个辅助协调者（Sub Query Coordinator，SQC），SQC 负责在所在 observer 上为各个 DFO 申请执行资源、构造执行上下文环境等，然后启动 DFO 在各个 observer 上并行执行
5. 当各个 DFO 都执行完毕，QC 会串行执行剩余部分的计算。如，一个并行的 COUNT 算法，最终需要 QC 将各个机器上的计算结果做一个 SUM 运算。
6. QC 所在线程将结果返回给客户端

       优化器负责决策生成一个怎样的并行计划，QC 负责具体执行该计划。例如，两分区表 JOIN，优化器根据规则和代价信息，可能生成一个分布式的 PARTITION WISE JOIN 计划，也可能生成一个 HASH-HASH 打散的分布式 JOIN 计划。计划一旦确定，QC 就会将计划拆分成多个 DFO，有序调度执行。

六、参考文章

1.官网：OceanBase 企业级分布式关系数据库

这篇关于【Oceanbase系列】——OceanBase SQL执行计划的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！