11.OB4.0执行计划查看

最新推荐文章于 2024-12-10 14:51:48 发布

原创最新推荐文章于 2024-12-10 14:51:48 发布 · 734 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

国产数据库-OceanBase 专栏收录该内容

39 篇文章

订阅专栏

本文展示了使用EXPLAIN命令解析SQL查询的不同格式，包括基础的表格统计信息、EXTENDED_NOADDR的附加信息、TRADITIONAL格式以及JSON格式的执行计划。这些信息有助于理解查询的优化过程和成本估算。

1.查看表统计信息

EXPLAIN SELECT * FROM t1  WHERE t1=4\G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |1        |2   |
===================================

Outputs & filters: 
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c1 = 4]), rowset=256, 
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p0)

2.EXTENDED_NOADDR 关键字输出附加信息

EXPLAIN EXTENDED_NOADDR SELECT * FROM t1  WHERE t1=4\G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |1        |2   |
===================================

Outputs & filters: 
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c1 = 4]), rowset=256, 
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p0), 
      is_index_back=false, filter_before_indexback[false], 
      range_key([t1.__pk_increment]), range(MIN ; MAX)always true

3.展示 TRADITIONAL 格式的执行计划

EXPLAIN FORMAT=TRADITIONAL SELECT * FROM t1 where c1=4\G
*************************** 1. row ***************************
Query Plan: ===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |1        |2   |
===================================

Outputs & filters: 
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c1 = 4]), rowset=256, 
      access([t1.c1], [t1.c2], [t1.c3]), partitions(p0)

4.展示 JSON 格式的执行计划。

EXPLAIN FORMAT=JSON SELECT * FROM t1 where c1=4\G
*************************** 1. row ***************************
Query Plan: {
  "ID":0,
  "OPERATOR":"TABLE SCAN",
  "NAME":"TABLE SCAN",
  "EST.ROWS":1,
  "COST":1,
  "output": [
    "t1.c1",
    "t1.c2",
    "t1.c3"
  ]
}