查看执行计划

执行计划是对一条 SQL 查询语句在数据库中执行过程的描述，通常用于分析某条 SQL 的性能问题，读懂执行计划是 SQL 优化的先决条件。用户可以通过 EXPLAIN 命令查看优化器针对指定 SQL 生成的逻辑执行计划。

EXPLAIN 命令格式

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

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

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

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

语法如下：​​

EXPLAIN [explain_type] dml_statement;

explain_type：
    BASIC 
  | OUTLINE
  | EXTENDED
  | EXTENDED_NOADDR
  | PARTITIONS 
  | FORMAT = { TRADITIONAL | JSON }

dml_statement:
    SELECT statement 
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement

EXPLAIN 命令适用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句，显示优化器所提供的有关语句执行计划的信息，包括如何处理该语句，如何联接表以及以何种顺序联接表等信息。

一般来说，可以使用 EXPLAIN EXTENDED 命令，将表扫描的范围段展示出来。使用 EXPLAIN OUTLINE 命令可以显示 Outline 信息。

FORMAT 选项可用于选择输出格式。TRADITIONAL 表示以表格格式显示输出，这也是默认设置。JSON 表示以 JSON 格式显示信息。

使用 EXPLAIN PARTITIONS 也可用于检查涉及分区表的查询。如果检查针对非分区表的查询，则不会产生错误，但 PARTITIONS 列的值始终为 NULL。

使用黑屏环境查看执行计划示例

• 使用 EXPLAIN BASIC 命令展示执行计划。

  obclient> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, KEY k1(b));
  Query OK, 0 rows affected 
  obclient> CREATE TABLE t2(a INT PRIMARY KEY, b INT, c INT, KEY k1(b));
  Query OK, 0 rows affected 
  
  obclient> EXPLAIN BASIC SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
  *************************** 1. row ***************************
  Query Plan: 
  =====================
  |ID|OPERATOR   |NAME|
  ---------------------
  |0 |MERGE JOIN |    |
  |1 | TABLE SCAN|t1  |
  |2 | TABLE SCAN|t2  |
  =====================
  
  Outputs & filters: 
  -------------------------------------
    0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), 
        equal_conds([t1.a = t2.a]), other_conds(nil)
    1 - output([t1.a], [t1.b], [t1.c]), filter(nil), 
        access([t1.a], [t1.b], [t1.c]), partitions(p0)
    2 - output([t2.a], [t2.b], [t2.c]), filter(nil), 
        access([t2.a], [t2.b], [t2.c]), partitions(p0)
  

• 使用 EXPLAIN 命令展示执行计划。

  obclient> EXPLAIN SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
  *************************** 1. row ***************************
  Query Plan: 
  ======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  --------------------------------------
  |0 |MERGE JOIN |    |100001   |119783|
  |1 | TABLE SCAN|t1  |100000   |38681 |
  |2 | TABLE SCAN|t2  |100000   |38681 |
  ======================================
  
  Outputs & filters: 
  -------------------------------------
    0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), 
        equal_conds([t1.a = t2.a]), other_conds(nil)
    1 - output([t1.a], [t1.b], [t1.c]), filter(nil), 
        access([t1.a], [t1.b], [t1.c]), partitions(p0)
    2 - output([t2.a], [t2.b], [t2.c]), filter(nil), 
        access([t2.a], [t2.b], [t2.c]), partitions(p0)
  

• 使用 EXPLAIN EXTENDED 命令展示执行计划。

  obclient> EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
  *************************** 1. row ***************************
  Query Plan: 
  ======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  --------------------------------------
  |0 |MERGE JOIN |    |100001   |119783|
  |1 | TABLE SCAN|t1  |100000   |38681 |
  |2 | TABLE SCAN|t2  |100000   |38681 |
  ======================================
  
  Outputs & filters: 
  -------------------------------------
    0 - output([t1.a(0x7f1c68e79310)], [t1.b(0x7f1c68e79950)], [t1.c(0x7f1c68e79c10)], [t2.a(0x7f1c68e795d0)], [t2.b(0x7f1c68e79ed0)], [t2.c(0x7f1c68e7a190)]), filter(nil), 
        equal_conds([t1.a(0x7f1c68e79310) = t2.a(0x7f1c68e795d0)(0x7f1c68e78c20)]), other_conds(nil), 
        merge_directions([ASC])
    1 - output([t1.a(0x7f1c68e79310)], [t1.b(0x7f1c68e79950)], [t1.c(0x7f1c68e79c10)]), filter(nil), 
        access([t1.a(0x7f1c68e79310)], [t1.b(0x7f1c68e79950)], [t1.c(0x7f1c68e79c10)]), partitions(p0), 
        is_index_back=false, 
        range_key([t1.a(0x7f1c68e79310)]), range(MIN ; MAX)always true
    2 - output([t2.a(0x7f1c68e795d0)], [t2.b(0x7f1c68e79ed0)], [t2.c(0x7f1c68e7a190)]), filter(nil), 
        access([t2.a(0x7f1c68e795d0)], [t2.b(0x7f1c68e79ed0)], [t2.c(0x7f1c68e7a190)]), partitions(p0), 
        is_index_back=false, 
        range_key([t2.a(0x7f1c68e795d0)]), range(MIN ; MAX)always true
  
  Used Hint:
  -------------------------------------
    /*+
    */
  
  Outline Data:
  -------------------------------------
    /*+
        BEGIN_OUTLINE_DATA
        LEADING(@"SEL$1" ("test.t1"@"SEL$1" "test.t2"@"SEL$1" ))
        USE_MERGE(@"SEL$1" ("test.t2"@"SEL$1" ))
        PQ_DISTRIBUTE(@"SEL$1" ("test.t2"@"SEL$1" ) LOCAL LOCAL)
        FULL(@"SEL$1" "test.t1"@"SEL$1")
        FULL(@"SEL$1" "test.t2"@"SEL$1")
        END_OUTLINE_DATA
    */
  
  Plan Type:
  -------------------------------------
  LOCAL
  
  Optimization Info:
  -------------------------------------
  
  
  t1:table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:default_stat, optimization_method=cost_based, avaiable_index_name[t1], pruned_index_name[k1]
  
  t2:table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:default_stat, optimization_method=cost_based, avaiable_index_name[t2], pruned_index_name[k1]
  Parameters
  -------------------------------------
  

• 使用 EXPLAIN EXTENDED_NOADDR 命令展示执行计划。

  obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
  *************************** 1. row ***************************
  Query Plan: 
  ======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  --------------------------------------
  |0 |MERGE JOIN |    |100001   |119783|
  |1 | TABLE SCAN|t1  |100000   |38681 |
  |2 | TABLE SCAN|t2  |100000   |38681 |
  ======================================
  
  Outputs & filters: 
  -------------------------------------
    0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), 
        equal_conds([t1.a = t2.a]), other_conds(nil)
    1 - output([t1.a], [t1.b], [t1.c]), filter(nil), 
        access([t1.a], [t1.b], [t1.c]), partitions(p0), 
        is_index_back=false, 
        range_key([t1.a]), range(MIN ; MAX)always true
    2 - output([t2.a], [t2.b], [t2.c]), filter(nil), 
        access([t2.a], [t2.b], [t2.c]), partitions(p0), 
        is_index_back=false, 
        range_key([t2.a]), range(MIN ; MAX)always true
  

• 使用 EXPLAIN OUTLINE 命令可以显示 Outline 信息。

  obclient> EXPLAIN OUTLINE SELECT * FROM t1,t2 WHERE t1.a = t2.a\G
  *************************** 1. row ***************************
  Query Plan: 
  ======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST  |
  --------------------------------------
  |0 |MERGE JOIN |    |100001   |119783|
  |1 | TABLE SCAN|t1  |100000   |38681 |
  |2 | TABLE SCAN|t2  |100000   |38681 |
  ======================================
  
  Outputs & filters: 
  -------------------------------------
    0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), 
        equal_conds([t1.a = t2.a]), other_conds(nil)
    1 - output([t1.a], [t1.b], [t1.c]), filter(nil), 
        access([t1.a], [t1.b], [t1.c]), partitions(p0)
    2 - output([t2.a], [t2.b], [t2.c]), filter(nil), 
        access([t2.a], [t2.b], [t2.c]), partitions(p0)
  
  Outline Data:
  -------------------------------------
    /*+
        BEGIN_OUTLINE_DATA
        LEADING(@"SEL$1" ("test.t1"@"SEL$1" "test.t2"@"SEL$1" ))
        USE_MERGE(@"SEL$1" ("test.t2"@"SEL$1" ))
        PQ_DISTRIBUTE(@"SEL$1" ("test.t2"@"SEL$1" ) LOCAL LOCAL)
        FULL(@"SEL$1" "test.t1"@"SEL$1")
        FULL(@"SEL$1" "test.t2"@"SEL$1")
        END_OUTLINE_DATA
    */
  

使用 ODC 查看执行计划示例

• 使用 EXPLAIN BASIC 命令展示执行计划。

  

• 使用 EXPLAIN 命令展示执行计划。

  

• 使用 EXPLAIN EXTENDED 命令展示执行计划。

  

• 使用 EXPLAIN EXTENDED_NOADDR 命令展示执行计划。

  

• 使用 EXPLAIN OUTLINE 命令可以显示 Outline 信息。