EXPLAIN

EXPLAIN — 展示一个语句的执行计划。

语法结构

  1. EXPLAIN [ ( option [, ...] ) ] statement
  3. where option can be one of:
  4.     ANALYZE [ boolean ]
  5.     VERBOSE [ boolean ]
  6.     FORMAT { TEXT | JSON }

语法描述

此命令主要作用是显示出 MatrixOne 计划程序为提供的语句生成的执行计划。执行计划显示了如何通过普通顺序扫描、索引扫描等方式扫描语句引用的表，如果引用了多个表，将使用什么连接算法将每个输入表中所需的行聚集在一起。

显示的最关键部分是估计语句执行成本，即计划程序将估计运行语句所需时间 (以任意一种成本单位衡量，但通常是听过磁盘页获取)。实际上这里显示了两个数字：返回第一行之前的启动成本，以及返回所有行的总成本。对于大多数查询来说，总成本是最重要的，但在 EXISTS 中的子查询中，计划程序会选择最小的启动成本，而不是最小的总成本 (因为执行者在获得一行之后就会停止)。此外，如果您使用 LIMIT 从句限制返回的行数，计划程序将在端点成本之间进行适当的插值，以便估计哪个计划真正是最便宜的。

ANALYZE 子句语法选项为语句实际执行，而不仅仅是计划执行，然后将实际运行时统计信息添加到显示中，包括每个计划节点中花费的总运行时间 (以毫秒为单位) 和实际返回的行总数。这有助于了解规划者的期望是否接近实际。

参数释义

  • ANALYZE:

执行该命令并显示实际运行时和其他统计数据。该参数默认为 FALSE。

  • VERBOSE:

VERBOSE 用作显示有关计划的其他信息。具体来说，包括计划树中每个节点的输出列列表、模式限定表和函数名称，始终使用范围表别名标记表达式中的变量，并且始终打印显示统计信息的每个触发器的名称。该参数默认为 FALSE。

  • FORMAT:

FORMAT 用作指定输出格式，可以是 TEXT、JSON。非文本输出包含与文本输出格式相同的信息，且容易被程序解析。该参数默认为 TEXT。

  • BOOLEAN:

BOOLEAN 指定所选选项是打开还是关闭。你可以写 TRUE 来启用该选项，或者写 FALSE 来禁用它。

boolean 值也可以省略，省略 boolean 值的情况下默认为 TRUE。

  • STETEMENT

MatrixOne 支持任何 SELECT，UPDATE，DELETE 语句执行计划。在 MatrixOne 0.5.1 版本中仅支持 INSERT 语句类型中的 INSERT INTO..SELECT 语句，暂不支持 INSERT INTO...VALUES 语句。

示例

Node_TABLE_SCAN

  1. mysql> explain verbose SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_NATIONKEY > 0 OR N_NATIONKEY < 10;
  2. +------------------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                         |
  4. +------------------------------------------------------------------------------------+
  5. | Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                             |
  6. |   Output: #[0,0], #[0,1]                                                           |
  7. |   ->  Table Scan on db1.nation(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)      |
  8. |         Output: #[0,1], #[0,2]                                                     |
  9. |         Table: 'nation' (0:'n_nationkey', 1:'n_name', 2:'n_regionkey')             |
  10. |         Filter Cond: ((CAST(#[0,0] AS INT64) > 0) or (CAST(#[0,0] AS INT64) < 10)) |
  11. +------------------------------------------------------------------------------------+

Node_VALUE_SCAN

  1. mysql> explain verbose select abs(-1);
  2. +-----------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                  |
  4. +-----------------------------------------------------------------------------+
  5. | Project(cost=0.00..0.00 card=1.00 ndv=0.00 rowsize=0)                       |
  6. |   Output: 1                                                                 |
  7. |   ->  Values Scan "*VALUES*" (cost=0.00..0.00 card=1.00 ndv=0.00 rowsize=0) |
  8. |         Output: 0                                                           |
  9. +-----------------------------------------------------------------------------+

Node_SORT

  1. mysql> explain verbose SELECT N_NAME, N_REGIONKEY a FROM NATION WHERE N_NATIONKEY > 0 AND N_NATIONKEY < 10 ORDER BY N_NAME, N_REGIONKEY DESC;
  2. +--------------------------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                                 |
  4. +--------------------------------------------------------------------------------------------+
  5. | Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                                     |
  6. |   Output: #[0,0], #[0,1]                                                                   |
  7. |   ->  Sort(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                                  |
  8. |         Output: #[0,0], #[0,1]                                                             |
  9. |         Sort Key: #[0,0] INTERNAL,  #[0,1] DESC                                            |
  10. |         ->  Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                         |
  11. |               Output: #[0,0], #[0,1]                                                       |
  12. |               ->  Table Scan on db1.nation(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)  |
  13. |                     Output: #[0,1], #[0,2]                                                 |
  14. |                     Table: 'nation' (0:'n_nationkey', 1:'n_name', 2:'n_regionkey')         |
  15. |                     Filter Cond: (CAST(#[0,0] AS INT64) > 0), (CAST(#[0,0] AS INT64) < 10) |
  16. +--------------------------------------------------------------------------------------------+

带有限制和偏移量：

  1. mysql> explain SELECT N_NAME, N_REGIONKEY FROM NATION WHERE abs(N_REGIONKEY) > 0 AND N_NAME LIKE '%AA' ORDER BY N_NAME DESC, N_REGIONKEY limit 10;
  2. +-------------------------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                                |
  4. +-------------------------------------------------------------------------------------------+
  5. | Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                                    |
  6. |   ->  Sort(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                                 |
  7. |         Sort Key: #[0,0] DESC,  #[0,1] INTERNAL                                           |
  8. |         Limit: 10                                                                         |
  9. |         ->  Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                        |
  10. |               ->  Table Scan on db1.nation(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0) |
  11. |                     Filter Cond: (abs(CAST(#[0,1] AS INT64)) > 0), (#[0,0] like '%AA')    |
  12. +-------------------------------------------------------------------------------------------+
  1. mysql> explain SELECT N_NAME, N_REGIONKEY FROM NATION WHERE abs(N_REGIONKEY) > 0 AND N_NAME LIKE '%AA' ORDER BY N_NAME DESC, N_REGIONKEY LIMIT 10 offset 20;
  2. +-------------------------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                                |
  4. +-------------------------------------------------------------------------------------------+
  5. | Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                                    |
  6. |   ->  Sort(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                                 |
  7. |         Sort Key: #[0,0] DESC,  #[0,1] INTERNAL                                           |
  8. |         Limit: 10, Offset: 20                                                             |
  9. |         ->  Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                        |
  10. |               ->  Table Scan on db1.nation(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0) |
  11. |                     Filter Cond: (abs(CAST(#[0,1] AS INT64)) > 0), (#[0,0] like '%AA')    |
  12. +-------------------------------------------------------------------------------------------+

Node_AGG

  1. mysql> explain verbose SELECT count(*) FROM NATION group by N_NAME;
  2. +-------------------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                          |
  4. +-------------------------------------------------------------------------------------+
  5. | Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                              |
  6. |   Output: #[0,0]                                                                    |
  7. |   ->  Aggregate(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                      |
  8. |         Output: #[-2,0]                                                             |
  9. |         Group Key:#[0,1]                                                            |
  10. |         Aggregate Functions: starcount(#[0,0])                                      |
  11. |         ->  Table Scan on db1.nation(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0) |
  12. |               Output: #[0,0], #[0,1]                                                |
  13. |               Table: 'nation' (0:'n_nationkey', 1:'n_name')                         |
  14. +-------------------------------------------------------------------------------------+

Node_JOIN

  1. mysql> explain verbose SELECT NATION.N_NAME, REGION.R_NAME FROM NATION join REGION on NATION.N_REGIONKEY = REGION.R_REGIONKEY WHERE NATION.N_REGIONKEY > 10 AND LENGTH(NATION.N_NAME) > LENGTH(REGION.R_NAME);
  2. +--------------------------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                                 |
  4. +--------------------------------------------------------------------------------------------+
  5. | Project(cost=0.00..0.00 card=125.00 ndv=0.00 rowsize=0)                                    |
  6. |   Output: #[0,1], #[0,0]                                                                   |
  7. |   ->  Filter(cost=0.00..0.00 card=125.00 ndv=0.00 rowsize=0)                               |
  8. |         Output: #[0,0], #[0,1]                                                             |
  9. |         Filter Cond: (length(CAST(#[0,1] AS CHAR)) > length(CAST(#[0,0] AS CHAR)))         |
  10. |         ->  Join(cost=0.00..0.00 card=125.00 ndv=0.00 rowsize=0)                           |
  11. |               Output: #[0,1], #[1,0]                                                       |
  12. |               Join Type: INNER                                                             |
  13. |               Join Cond: (#[1,1] = #[0,0])                                                 |
  14. |               ->  Table Scan on tpch.region(cost=0.00..0.00 card=5.00 ndv=0.00 rowsize=0)  |
  15. |                     Output: #[0,0], #[0,1]                                                 |
  16. |                     Table: 'region' (0:'r_regionkey', 1:'r_name')                          |
  17. |               ->  Table Scan on tpch.nation(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0) |
  18. |                     Output: #[0,0], #[0,1]                                                 |
  19. |                     Table: 'nation' (0:'n_name', 1:'n_regionkey')                          |
  20. |                     Filter Cond: (CAST(#[0,1] AS INT64) > 10)                              |
  21. +--------------------------------------------------------------------------------------------+

Node_INSERT

  1. mysql> explain verbose INSERT NATION select * from nation;
  2. +---------------------------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                                  |
  4. +---------------------------------------------------------------------------------------------+
  5. | Insert on db1.nation (cost=0.0..0.0 rows=0 ndv=0 rowsize=0)                                 |
  6. |   Output: #[0,0], #[0,1], #[0,2], #[0,3]                                                    |
  7. |   ->  Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                                |
  8. |         Output: #[0,0], #[0,1], #[0,2], #[0,3]                                              |
  9. |         ->  Table Scan on db1.nation(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)         |
  10. |               Output: #[0,0], #[0,1], #[0,2], #[0,3]                                        |
  11. |               Table: 'nation' (0:'n_nationkey', 1:'n_name', 2:'n_regionkey', 3:'n_comment') |
  12. +---------------------------------------------------------------------------------------------+
  13. 7 rows in set (0.00 sec)

Node_Update

  1. mysql> explain verbose UPDATE NATION SET N_NAME ='U1', N_REGIONKEY=2 WHERE N_NATIONKEY > 10 LIMIT 20;
  2. +-------------------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                          |
  4. +-------------------------------------------------------------------------------------+
  5. | Update on db1.nation (cost=0.0..0.0 rows=0 ndv=0 rowsize=0)                         |
  6. |   ->  Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                        |
  7. |         Output: #[0,0], 'U1', CAST(2 AS INT32)                                      |
  8. |         Limit: 20                                                                   |
  9. |         ->  Table Scan on db1.nation(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0) |
  10. |               Output: #[0,1]                                                        |
  11. |               Table: 'nation' (0:'n_nationkey', 1:'PADDR')                          |
  12. |               Filter Cond: (CAST(#[0,0] AS INT64) > 10)                             |
  13. +-------------------------------------------------------------------------------------+

Node_Delete

  1. mysql> explain verbose DELETE FROM NATION WHERE N_NATIONKEY > 10;
  2. +-------------------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                          |
  4. +-------------------------------------------------------------------------------------+
  5. | Delete on db1.nation (cost=0.0..0.0 rows=0 ndv=0 rowsize=0)                         |
  6. |   ->  Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                        |
  7. |         Output: #[0,0]                                                              |
  8. |         ->  Table Scan on db1.nation(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0) |
  9. |               Output: #[0,1]                                                        |
  10. |               Table: 'nation' (0:'n_nationkey', 1:'PADDR')                          |
  11. |               Filter Cond: (CAST(#[0,0] AS INT64) > 10)                             |
  12. +-------------------------------------------------------------------------------------+

带有限制：

  1. mysql>  explain verbose DELETE FROM NATION WHERE N_NATIONKEY > 10 LIMIT 20;
  2. +-------------------------------------------------------------------------------------+
  3. | QUERY PLAN                                                                          |
  4. +-------------------------------------------------------------------------------------+
  5. | Delete on db1.nation (cost=0.0..0.0 rows=0 ndv=0 rowsize=0)                         |
  6. |   ->  Project(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0)                        |
  7. |         Output: #[0,0]                                                              |
  8. |         Limit: 20                                                                   |
  9. |         ->  Table Scan on db1.nation(cost=0.00..0.00 card=25.00 ndv=0.00 rowsize=0) |
  10. |               Output: #[0,1]                                                        |
  11. |               Table: 'nation' (0:'n_nationkey', 1:'PADDR')                          |
  12. |               Filter Cond: (CAST(#[0,0] AS INT64) > 10)                             |
  13. +-------------------------------------------------------------------------------------+