云数据库OceanBase入门教程 OceanBase EXPLAIN

2024-02-26 开发教程云数据库OceanBase入门教程匿名 2℃

描述

该语句用于解释 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 时间。

上一节： OceanBase AUDIT

下一节： OceanBase FLASHBACK TABLE BEFORE DROP

"云数据库OceanBase入门教程"的目录

云数据库OceanBase入门教程 OceanBase EXPLAIN

描述

格式

参数解释

示例