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

EXPLAIN 与 DESCRIBE、DESC 互为同义词。

语法

/*获取表或列的信息*/
{EXPLAIN | DESCRIBE | DESC} table_name [column_name];

/*获取 SQL 计划信息*/
{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 
  | UPDATE statement 
  | MERGE statement

参数解释

示例

创建示例表 tbl1 和 tbl2。

obclient>CREATE TABLE tbl1(col1 INT,col2 INT);
Query OK, 0 rows affected

obclient>CREATE TABLE tbl2(col1 INT,col2 INT);
Query OK, 0 rows affected

• 获取表 tbl1 的信息。

  obclient> EXPLAIN tbl1;
  +-------+------------+------+-----+---------+-------+
  | FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
  +-------+------------+------+-----+---------+-------+
  | COL1  | NUMBER(38) | YES  | NULL | NULL    | NULL  |
  | COL2  | NUMBER(38) | YES  | NULL | NULL    | NULL  |
  +-------+------------+------+-----+---------+-------+
  2 rows in set
  

• 获取表 tbl2 的列 col2 的信息。

  obclient> EXPLAIN tbl2 col2;
  +-------+------------+------+-----+---------+-------+
  | FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
  +-------+------------+------+-----+---------+-------+
  | COL2  | NUMBER(38) | YES  | NULL | NULL    | NULL  |
  +-------+------------+------+-----+---------+-------+
  1 row in set
  

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

  obclient> EXPLAIN SELECT * FROM tbl1,tbl2 WHERE tbl1.col2=tbl2.col2 AND tbl2.col1 > 4\G
  *************************** 1. row ***************************
  Query Plan: =======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST   |
  ---------------------------------------
  |0 |HASH JOIN  |    |9801000  |2442404|
  |1 | TABLE SCAN|TBL2|10000    |40790  |
  |2 | TABLE SCAN|TBL1|100000   |38681  |
  =======================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([TBL1.COL1], [TBL1.COL2], [TBL2.COL1], [TBL2.COL2]), filter(nil),
        equal_conds([TBL1.COL2 = TBL2.COL2]), other_conds(nil)
    1 - output([TBL2.COL2], [TBL2.COL1]), filter([TBL2.COL1 > 4]),
        access([TBL2.COL2], [TBL2.COL1]), partitions(p0)
    2 - output([TBL1.COL2], [TBL1.COL1]), filter(nil),
        access([TBL1.COL2], [TBL1.COL1]), partitions(p0)
  
  1 row in set
  

• 通过 EXTENDED_NOADDR 关键字输出附加信息。

  obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM tbl1,tbl2 WHERE tbl1.col2=tbl2.col2 AND tbl2.col1 > 4\G
  *************************** 1. row ***************************
  Query Plan: =======================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST   |
  ---------------------------------------
  |0 |HASH JOIN  |    |9801000  |2442404|
  |1 | TABLE SCAN|TBL2|10000    |40790  |
  |2 | TABLE SCAN|TBL1|100000   |38681  |
  =======================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([TBL1.COL1], [TBL1.COL2], [TBL2.COL1], [TBL2.COL2]), filter(nil),
        equal_conds([TBL1.COL2 = TBL2.COL2]), other_conds(nil)
    1 - output([TBL2.COL2], [TBL2.COL1]), filter([TBL2.COL1 > 4]),
        access([TBL2.COL2], [TBL2.COL1]), partitions(p0),
        is_index_back=false, filter_before_indexback[false],
        range_key([TBL2.__pk_increment]), range(MIN ; MAX)always true
    2 - output([TBL1.COL2], [TBL1.COL1]), filter(nil),
        access([TBL1.COL2], [TBL1.COL1]), partitions(p0),
        is_index_back=false,
        range_key([TBL1.__pk_increment]), range(MIN ; MAX)always true
  
  1 row in set
  

• 展示 TRADITIONAL 格式的 INSERT 语句执行计划。

  obclient> EXPLAIN FORMAT=TRADITIONAL INSERT INTO TBL1 VALUES(1,1)\G
  *************************** 1. row ***************************
  Query Plan: ====================================
  |ID|OPERATOR   |NAME|EST. ROWS|COST|
  ------------------------------------
  |0 |INSERT     |    |1        |1   |
  |1 | EXPRESSION|    |1        |1   |
  ====================================
  
  Outputs & filters:
  -------------------------------------
    0 - output([column_conv(BIGINT UNSIGNED,PS:(-1,-1),NOT NULL,nextval(1))], [column_conv(NUMBER,PS:(38,0),NULL,__values.COL1)], [column_conv(NUMBER,PS:(38,0),NULL,__values.COL2)]), filter(nil),
        columns([{TBL1: ({TBL1: (TBL1.__pk_increment, TBL1.COL1, TBL1.COL2)})}]), partitions(p0)
    1 - output([__values.COL1], [__values.COL2]), filter(nil)
        values({1, 1})
  
  1 row in set
  

• 展示 JSON 格式的 SELECT 语句执行计划。

  obclient> EXPLAIN FORMAT=JSON SELECT * FROM tbl1,tbl2 WHERE tbl1.col2=tbl2.col2 AND tbl2.col1 > 4\G
  *************************** 1. row ***************************
  Query Plan: {
    "ID":2,
    "OPERATOR":"JOIN",
    "NAME":"JOIN",
    "EST.ROWS":9800999,
    "COST":2442403,
    "output": [
      "TBL1.COL1",
      "TBL1.COL2",
      "TBL2.COL1",
      "TBL2.COL2"
    ],
    "CHILD_1": {
      "ID":0,
      "OPERATOR":"TABLE SCAN",
      "NAME":"TABLE SCAN",
      "EST.ROWS":10000,
      "COST":40789,
      "output": [
        "TBL2.COL2",
        "TBL2.COL1"
      ]
    },
    "CHILD_2": {
      "ID":1,
      "OPERATOR":"TABLE SCAN",
      "NAME":"TABLE SCAN",
      "EST.ROWS":100000,
      "COST":38680,
      "output": [
        "TBL1.COL2",
        "TBL1.COL1"
      ]
    }
  }
  1 row in set