OceanBase Cloud快速入门第77期：如何查看执行计划?

执行计划是对一条 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 [pretty_name]
  | OUTLINE [pretty_name]
  | EXTENDED [pretty_name]
  | EXTENDED_NOADDR [pretty_name]
  | PARTITIONS [pretty_name]
  | FORMAT = { TRADITIONAL | JSON }

pretty_name:
  PRETTY
  | PRETTY_COLOR

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 PARTITITIONS 也可用于检查涉及分区表的查询。如果检查针对非分区表的查询，则不会产生错误，但 PARTIONS 列的值始终为 NULL。
对于复杂的执行计划，可以使用 PRETTY 或者 PRETTY_COLOR 选项将计划树中的父节点和子节点使用树线或彩色树线连接起来，使得执行计划展示更方便阅读。

查看执行计划示例

创建示例表。

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

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

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
  */