EXPLAIN

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

语法结构

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

语法描述

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

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

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

参数释义

  • ANALYZE:

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

  • VERBOSE:

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

  • FORMAT:

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

  • BOOLEAN:

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

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

  • STETEMENT

MatrixOne 支持任何 SELECTUPDATEDELETE 语句执行计划。在 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. +-------------------------------------------------------------------------------------+