OceanBase SQL執(zhí)行計(jì)劃簡介

用戶可以通過 EXPLAIN 命令查看優(yōu)化器針對(duì)給定 SQL 生成的邏輯執(zhí)行計(jì)劃。如果要分析某條 SQL 的性能問題，通常需要先查看 SQL 的執(zhí)行計(jì)劃，排查每一步 SQL 執(zhí)行是否存在問題。所以讀懂執(zhí)行計(jì)劃是 SQL 優(yōu)化的先決條件，而了解執(zhí)行計(jì)劃的算子是理解 EXPLAIN 命令的關(guān)鍵。

EXPLAIN 命令格式

OceanBase 數(shù)據(jù)庫的執(zhí)行計(jì)劃命令有三種模式：EXPLAIN BASIC、EXPLAIN 和 EXPLAIN EXTENDED。這三種模式對(duì)執(zhí)行計(jì)劃展現(xiàn)不同粒度的細(xì)節(jié)信息:

• EXPLAIN BASIC 命令用于最基本的計(jì)劃展示。

• EXPLAIN EXTENDED 命令用于最詳細(xì)的計(jì)劃展示（通常在排查問題時(shí)使用這種展示模式）。

• EXPLAIN 命令所展示的信息可以幫助普通用戶了解整個(gè)計(jì)劃的執(zhí)行方式。

命令格式如下：??

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 }

執(zhí)行計(jì)劃形狀與算子信息

在數(shù)據(jù)庫系統(tǒng)中，執(zhí)行計(jì)劃在內(nèi)部通常是以樹的形式來表示的，但是不同的數(shù)據(jù)庫會(huì)選擇不同的方式展示給用戶。

如下示例分別為 PostgreSQL 數(shù)據(jù)庫、Oracle 數(shù)據(jù)庫和 OceanBase 數(shù)據(jù)庫對(duì)于 TPCDS Q3 的計(jì)劃展示。

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; 
 

• Oracle 數(shù)據(jù)庫執(zhí)行計(jì)劃展示如下：

  Plan hash value: 2331821367
  --------------------------------------------------------------------------------------------------
  | Id  | Operation                         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                  |              |   100 |  9100 |  3688   (1)| 00:00:01 |
  |*  1 |  COUNT STOPKEY                    |              |       |       |            |          |
  |   2 |   VIEW                            |              |  2736 |   243K|  3688   (1)| 00:00:01 |
  |*  3 |    SORT ORDER BY STOPKEY          |              |  2736 |   256K|  3688   (1)| 00:00:01 |
  |   4 |     HASH GROUP BY                 |              |  2736 |   256K|  3688   (1)| 00:00:01 |
  |*  5 |      HASH JOIN                    |              |  2736 |   256K|  3686   (1)| 00:00:01 |
  |*  6 |       TABLE ACCESS FULL           | DATE_DIM     |  6087 | 79131 |   376   (1)| 00:00:01 |
  |   7 |       NESTED LOOPS                |              |  2865 |   232K|  3310   (1)| 00:00:01 |
  |   8 |        NESTED LOOPS               |              |  2865 |   232K|  3310   (1)| 00:00:01 |
  |*  9 |         TABLE ACCESS FULL         | ITEM         |    18 |  1188 |   375   (0)| 00:00:01 |
  |* 10 |         INDEX RANGE SCAN          | SYS_C0010069 |   159 |       |     2   (0)| 00:00:01 |
  |  11 |        TABLE ACCESS BY INDEX ROWID| STORE_SALES  |   159 |  2703 |   163   (0)| 00:00:01 |
  --------------------------------------------------------------------------------------------------

• OceanBase 數(shù)據(jù)庫執(zhí)行計(jì)劃展示如下：

  |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 數(shù)據(jù)庫的計(jì)劃展示與 Oracle 數(shù)據(jù)庫類似。OceanBase 數(shù)據(jù)庫執(zhí)行計(jì)劃中的各列的含義如下：

OceanBase 數(shù)據(jù)庫 EXPLAIN 命令輸出的第一部分是執(zhí)行計(jì)劃的樹形結(jié)構(gòu)展示。其中每一個(gè)操作在樹中的層次通過其在 operator 中的縮進(jìn)予以展示。樹的層次關(guān)系用縮進(jìn)來表示，層次最深的優(yōu)先執(zhí)行，層次相同的以特定算子的執(zhí)行順序?yàn)闃?biāo)準(zhǔn)來執(zhí)行。

上述 TPCDS Q3 示例的計(jì)劃展示樹如下:

OceanBase 數(shù)據(jù)庫 EXPLAIN 命令輸出的第二部分是各操作算子的詳細(xì)信息，包括輸出表達(dá)式、過濾條件、分區(qū)信息以及各算子的獨(dú)有信息（包括排序鍵、連接鍵、下壓條件等）。示例如下：

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)