树叶云OceanBase教程：OceanBase EXPLAIN

描述

该语句用于解释 SQL 语句的执行计划，可以是 SELECT、DELETE、INSERT、REPLACE 或 UPDATE 语句。

格式

获取表或列的信息：
{EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild]

获取SQL计划信息：
{EXPLAIN} 
[BASIC | OUTLINE | EXTENDED | EXTENDED_NOADDR | PARTITIONS | FORMAT = {TRADITIONAL| JSON}] 
{SELECT statement | DELETE statement | INSERT statement | UPDATE statement | MERGE statement}

参数解释

参数	描述
tbl_name	指定表名。
col_name	指定表的列名。
BASIC	指定输出计划的基础信息，如算子 ID、算子名称、所引用的表名。
OUTLINE	指定输出的计划信息包含 Outline 信息。
EXTENDED	`EXPLAIN`产生附加信息，包括：每个算子的输入列和输出列，访问表的分区信息，当前使用的filter信息，如果当前算子使用了索引，显示所使用的索引列及抽取的 Query Range。
EXTENDED_NOADDR	以简约的方式展示附加信息。
PARTITIONS	显示分区相关信息。
FORMAT = {TRADITIONAL\| JSON}	指定`EXPALIN`的输出格式： `TRADITIONAL`：表格输出格式. `JSON`：`KEY:VALUE`输出格式，`JSON`显示为`JSON`字符串，包括`EXTENDED`和`PARTITIONS`信息。

示例

省略 explain_type

obclient>CREATE TABLE t1(c1 INT,c2 INT);
Query OK, 0 rows affected (0.04 sec)

obclient>CREATE TABLE t2(c1 INT,c2 INT);
Query OK, 0 rows affected (0.07 sec)

obclient>EXPLAIN SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST   |
---------------------------------------
|0 |HASH JOIN  |    |9801000  |5933109|
|1 | TABLE SCAN|t2  |10000    |6219   |
|2 | TABLE SCAN|t1  |100000   |68478  |
=======================================

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

EXTENDED

obclient>EXPLAIN EXTENDED_NOADDR SELECT * FROM t1,t2 
        WHERE t1.c2=t2.c2 AND t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST   |
---------------------------------------
|0 |HASH JOIN  |    |9801000  |5933109|
|1 | TABLE SCAN|t2  |10000    |6219   |
|2 | TABLE SCAN|t1  |100000   |68478  |
=======================================

Outputs & filters: 
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), 
      equal_conds([t1.c2 = t2.c2]), other_conds(nil)
  1 - output([t2.c2], [t2.c1]), filter(nil), 
      access([t2.c2], [t2.c1]), partitions(p0), 
      is_index_back=false, 
      range_key([t2.c1]), range(4 ; MAX), 
      range_cond([t2.c1 > 4])
  2 - output([t1.c2], [t1.c1]), filter(nil), 
      access([t1.c2], [t1.c1]), partitions(p0), 
      is_index_back=false, 
      range_key([t1.__pk_increment], [t1.__pk_cluster_id], [t1.__pk_partition_id]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true

TRADITIONAL格式

obclient>EXPLAIN FORMAT=TRADITIONAL SELECT * FROM t1,t2 
        WHERE t1.c2=t2.c2 AND t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: =======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST   |
---------------------------------------
|0 |HASH JOIN  |    |9801000  |5933109|
|1 | TABLE SCAN|t2  |10000    |6219   |
|2 | TABLE SCAN|t1  |100000   |68478  |
=======================================

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

JSON 格式

obclient>EXPLAIN FORMAT=JSON SELECT * FROM t1,t2 
       WHERE t1.c2=t2.c2 AND t2.c1 > 4\G
*************************** 1. row ***************************
Query Plan: {
  "ID":2,
  "OPERATOR":"JOIN",
  "NAME":"JOIN",
  "EST.ROWS":9800999,
  "COST":6840524,
  "output": [
    "T1.C1",
    "T1.C2",
    "T2.C1",
    "T2.C2"
  ],
  "CHILD_1": {
    "ID":0,
    "OPERATOR":"TABLE SCAN",
    "NAME":"TABLE SCAN",
    "EST.ROWS":10000,
    "COST":80810,
    "output": [
      "T2.C2",
      "T2.C1"
    ]
  },
  "CHILD_2": {
    "ID":1,
    "OPERATOR":"TABLE SCAN",
    "NAME":"TABLE SCAN",
    "EST.ROWS":100000,
    "COST":64065,
    "output": [
      "T1.C2",
      "T1.C1"
    ]
  }
}
1 row in set (0.01 sec)

EXPLAIN 的每个输出行提供一个表的相关信息，并且每个行包括下面的列：

列名	说明
ID	计划执行序列号。
OPERATOR	执行算子。
NAME	算子所引用的表。
EST.ROWS	估计执行到当前算子输出的行数。
COST	执行到当前算子的 CPU 时间。