EXPLAIN

描述

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

EXPLAIN 与 DESCRIBE、DESC 互为同义词。

语法

{EXPLAIN | DESCRIBE | DESC} [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

参数解释

示例

• 省略 explain_type，返回语句的执行计划。

  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_NOADDR 关键字输出附加信息。

  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":5933108,
    "output": [
      "t1.c1",
      "t1.c2",
      "t2.c1",
      "t2.c2"
    ],
    "TABLE SCAN": {
      "ID":0,
      "OPERATOR":"TABLE SCAN",
      "NAME":"TABLE SCAN",
      "EST.ROWS":10000,
      "COST":6218,
      "output": [
        "t2.c2",
        "t2.c1"
      ]
    },
    "TABLE SCAN": {
      "ID":1,
      "OPERATOR":"TABLE SCAN",
      "NAME":"TABLE SCAN",
      "EST.ROWS":100000,
      "COST":68477,
      "output": [
        "t1.c2",
        "t1.c1"
      ]
    }
  }