OceanBase SQL 执行计划解读(一)──基础概念

2024年 5月 7日 71.8k 0

本文分享 OceanBase SQL 执行计划解读经验。

对于熟悉 ORACLE 或 MySQL 的朋友,初次看 OceanBase 的执行计划,会觉得难以理解和接受。相反,只懂一些数据库理论但没有接触过其他数据库的人看 OceanBase 的执行计划反而更容易接受并掌握。这种感觉可能就像武侠小说里说的,得先忘记以前的功夫才能学会新的绝世武功 😄️。等看习惯了OceanBase的SQL执行计划后,自然就适应并体会到自研SQL引擎为何能兼具 OLTP 和 OLAP 两种场景的能力。

执行计划的概念

SQL执行计划简单来说就是SQL执行的路径或者算法,影响SQL的执行性能。差之毫厘,谬以千里。

OceanBase SQL 引擎支持SQL解析,解析后的执行计划会缓存,下次就可以复用。跟传统数据库一样,SQL 文本不一样,执行计划不会复用。此外,如果SQL文本一样,但是执行环境不一样,执行计划也不会复用。目前所知的执行环境包括运行节点IP、会话级别的并行参数,应该还有其他的。所有数据库的SQL执行引擎的成熟度可以从这个小点入手判断。

OceanBase缓存执行计划的两个视图是:gv$plan_cache_plan_stat 和 gv$plan_cache_plan_explain 。这是实际运行使用的执行计划,是更准确的。不过初学的时候看这个比较麻烦,不建议看。所以本文后面只探讨用 EXPLAIN 命令解析的执行计划。

先介绍一些执行计划的大的特点。防止大家跑后面案例时跟我的结果不一样进而不理解。

  • 没有绝对完美正确的执行计划,尤其是 SQL 比较复杂的时候。公说公有理,婆说婆有理。习惯每个问题都有个唯一答案的朋友,可能会不习惯。
  • 同样的SQL,不同 OB 版本的执行计划可能会变化。如果变的更好更合理,那就是产品改进了;如果变的更差更不合理,那可能就是产品缺陷(BUG)。
  • 同样的 SQL 同样的 OB 版本,执行计划也不一定一尘不变。影响执行计划变化的因素可能有数据量(或者统计信息)、会话参数(或变量)、数据分布特点、SQL被路由到的节点等。后两者是 OB 特有的,属于分布式数据库引入的挑战,以后再详细介绍。
  • EXPLAIN 解析的执行计划可能跟实际运行的执行计划不一样,理由同上。
  • SQL 执行计划可以通过 SQL 注释干预,这个需要改SQL。如果SQL不可以改,可以通过大纲(OUTLINE)在线干预。干预的结果可能成功也可能失败。这点以后也详细介绍。

OceanBase 客户端

查看执行计划需要一个好的客户端,这样能将注意力集中在执行计划上。

一是命令行 obclient 命令。obclient 的好处是显示会格式化,当一行结果太长的时候格式会有点不好看,这个时候可以让 SQL 以 \G 替换 结尾时,结果集会列转行展示,可读性更好一些。

二是 OB 官方客户端 ODC。直接选中 SQL,然后点击右上角的 “执行计划”。然后会以表格形式展示执行计划。

三是 开源的 DBeaver 客户端工具。由于 DBeaver 跟 OB还没有紧密适配,所以只能下 EXPLAIN [SQL] 的方式查看执行计划。但是它的 UI 体验非常好。

注意,不管是哪种客户端,建议设置一下事务自动提交(autocommit=on) 或者 每次查看执行计划时自动重连一下会话。

EXPLAIN 命令

EXPLAIN 命令完整的语法是:

{EXPLAIN | DESCRIBE | DESC} 
[BASIC | OUTLINE | EXTENDED | EXTENDED_NOADDR | PARTITIONS | FORMAT = {TRADITIONAL| JSON}] 
{SELECT statement | DELETE statement | INSERT statement | REPLACE statement| UPDATE statement}

学习遵从由简入繁原则,先从默认的 BASIC格式入手。不用指定。FORMAT 有 TRADITIONAL 和 JSON ,默认是 TRADITIONAL ,人可读性好一些。JSON 格式对程序解析比较友好。

先看一个简单的 SQL 执行计划格式。 

EXPLAIN 
SELECT count(*) FROM BMSQL_ITEM 
;

为减少文章图片,我选择用 obclient 展示。

obclient> EXPLAIN
    -> SELECT count(*) FROM BMSQL_ITEM \G
*************************** 1. row ***************************
Query Plan: ===============================================
|ID|OPERATOR       |NAME      |EST. ROWS|COST |
-----------------------------------------------
|0 |SCALAR GROUP BY|          |1        |78754|
|1 | TABLE SCAN    |BMSQL_ITEM|99995    |59653|
===============================================

Outputs & filters:
-------------------------------------
  0 - output([T_FUN_COUNT(*)]), filter(nil),
      group(nil), agg_func([T_FUN_COUNT(*)])
  1 - output([1]), filter(nil),
      access([BMSQL_ITEM.I_ID]), partitions(p0)

1 row in set (0.01 sec)

整个 EXPLAIN 结果是一行。在 OB 内部,这个结果估计是以 JSON 格式储。如下:

{
  "ID": 1,
  "OPERATOR": "GROUP BY",
  "NAME": "GROUP BY",
  "EST.ROWS": 1,
  "COST": 4352454,
  "output": [
    "T_FUN_COUNT(*)"
  ],
  "CHILD_1": {
    "ID": 0,
    "OPERATOR": "TABLE SCAN",
    "NAME": "TABLE SCAN",
    "EST.ROWS": 10000000,
    "COST": 2442330,
    "output": [
      "1"
    ]
  }
}

这个 JSON 内容描述的是一个树,对普通用户可读性不好。所以传统格式的展示分为两部分。第一部分是用表格形式展示执行计划这棵树。每行是一个独立的操作,操作符是 OPERATOR, 操作有 ID。操作展示可能会缩进。缩进表示是内部操作,可以嵌套。执行顺序遵循由内到外,由上到下。操作符支持的内容也是 SQL 引擎成熟度的一个体现。

第一部分说明:

  • OPERATOR : 表示操作算子的名称,TABLE SCAN, 是常用操作算子,表示扫描。
  • NAME: 表示算子操作的对象。可以是表名或者索引名,或者内部临时视图名。需要注意的是如果是扫描主键,展示的依然是表名。因为 OB 里的表和索引的本质都是索引组织表。表数据跟主键索引是一个概念。
  • EST. ROWS:执行当前算子输出的行数,跟统计信息有关。OB里表的统计信息目前只有在集群合并的时候才更新。
  • COST: 执行当前算子预估的成本。COST计算比较复杂,暂时先不深入。

第二部分说明:

第二部分的内容跟第一部分有关,主要是描述第一部分算子的具体信息。有一些公共的信息如下:

  • output : 表示当前算子输出的表达式(也包含列)。
  • filter : 表示当前算子的过滤表达式,nil表示无。如果当前算子是访问存储层,这个过滤表达式可以下推(push)。

常用 SQL 执行计划解读

表访问

表结构:

CREATE TABLE "BMSQL_ITEM" (
  "I_ID" NUMBER(38) NOT NULL,
  "I_NAME" VARCHAR2(24),
  "I_PRICE" NUMBER(5,2),
  "I_DATA" VARCHAR2(50),
  "I_IM_ID" NUMBER(38),
  CONSTRAINT "BMSQL_ITEM_OBPK_1615115887968047" PRIMARY KEY ("I_ID")
);

TABLE GET

EXPLAIN 
SELECT i_id, i_name, I_PRICE FROM BMSQL_ITEM WHERE i_id=10
\G

*************************** 1. row ***************************
Query Plan: ========================================
|ID|OPERATOR |NAME      |EST. ROWS|COST|
----------------------------------------
|0 |TABLE GET|BMSQL_ITEM|1        |53  |
========================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_PRICE]), filter(nil),
      access([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_PRICE]), partitions(p0)

1 row in set (0.00 sec)

说明:

  • TABLE GET : 指主键访问,后面接表名。OB里主键就是表数据。
  • access :表示访问存储接口用到的表达式,通常是列名。这里要读取 ID 和 Name 两列。
  • partitions(p0) :表示访问的是表 BMSQL_ITEM的 0 号分区。普通表是单分区,分区表是多分区。想要确定分区表的一笔记录在哪个分区(进而知道在哪个节点),通常就看这里。

按照ORACLE数据库的设计,根据索引访问表数据时,会有一个“回表取数”的过程。在 OB 的SQL执行计划里通常看不到这一步。其原因是 OB 里普通表数据分区和索引分区是存储在一起的。如果是全局索引的话会看到至少有两步。一步访问全局索引,一步访问表(即回表)。

我们多看一些执行计划细节信息即可以看到回表信息。

EXPLAIN extended_noaddr
SELECT i_id, i_name, I_PRICE FROM BMSQL_ITEM WHERE i_id=10
\G

*************************** 1. row ***************************
Query Plan: ========================================
|ID|OPERATOR |NAME      |EST. ROWS|COST|
----------------------------------------
|0 |TABLE GET|BMSQL_ITEM|1        |53  |
========================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_PRICE]), filter(nil),
      access([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_PRICE]), partitions(p0),
      is_index_back=false,
      range_key([BMSQL_ITEM.I_ID]), range[10 ; 10],
      range_cond([BMSQL_ITEM.I_ID = 10])

1 row in set (0.00 sec)

说明:

  • is_index_back : 表示是否回表访问。由于这里访问的主键,主键就是数据,所以不用回表。 
  • range_key:表示扫描的键值。主键索引就只有主键,普通索引会有普通索引列加上主键列。这是索引组织表的特点。
  • range:表示扫描的键值范围,跟前面键值对应。范围是最小值和最大值,支持向量。
  • range_cond:表示扫描时的传入条件。

TABLE SCAN

无条件的全表查询示例

EXPLAIN
SELECT * FROM BMSQL_ITEM
\G

*************************** 1. row ***************************
Query Plan: ==========================================
|ID|OPERATOR  |NAME      |EST. ROWS|COST |
------------------------------------------
|0 |TABLE SCAN|BMSQL_ITEM|100000   |68478|
==========================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_PRICE], [BMSQL_ITEM.I_DATA], [BMSQL_ITEM.I_IM_ID]), filter(nil),
      access([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_PRICE], [BMSQL_ITEM.I_DATA], [BMSQL_ITEM.I_IM_ID]), partitions(p0)

1 row in set (0.00 sec)

说明:

  • TABLE SCAN :表示全表扫描或者主键扫描、索引扫描。具体看后面的操作对象名是表还是索引。注意,扫描主键也是表名。

再看一个缺索引导致的全表扫描示例

EXPLAIN extended_noaddr
SELECT i_id, i_name, I_PRICE FROM BMSQL_ITEM WHERE i_name = 'w2uw7BJj5tG5BTlSdfT'
\G

*************************** 1. row ***************************
Query Plan: ==========================================
|ID|OPERATOR  |NAME      |EST. ROWS|COST |
------------------------------------------
|0 |TABLE SCAN|BMSQL_ITEM|1        |81091|
==========================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_PRICE]), filter([BMSQL_ITEM.I_NAME = 'w2uw7BJj5tG5BTlSdfT']),
      access([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_PRICE]), partitions(p0),
      is_index_back=false, filter_before_indexback[false],
      range_key([BMSQL_ITEM.I_ID]), range(MIN ; MAX)always true

1 row in set (0.01 sec)

说明:

  • is_index_back : 由于列 i_name 上没有索引,所以还是扫描全表。所以不需要回表
  • filter: 算子此时会根据i_name具体的值做过滤。
  • filter_before_indexback : 表示是否在回表之前做了过滤操作。由于没有回表,所以这个也是false
  • 最后一行表示扫描的是主键列,扫描范围从最小到最大,即全表扫描。

再加上索引看看。

CREATE UNIQUE INDEX idx_item_uk ON bmsql_item(i_name);

EXPLAIN extended_noaddr
SELECT i_id, i_name, I_PRICE FROM BMSQL_ITEM WHERE i_name = 'w2uw7BJj5tG5BTlSdfT'
\G

*************************** 1. row ***************************
Query Plan: ======================================================
|ID|OPERATOR  |NAME                   |EST. ROWS|COST|
------------------------------------------------------
|0 |TABLE SCAN|BMSQL_ITEM(IDX_ITEM_UK)|1        |88  |
======================================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_PRICE]), filter(nil),
      access([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_PRICE]), partitions(p0),
      is_index_back=true,
      range_key([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.shadow_pk_0]), range(w2uw7BJj5tG5BTlSdfT,MIN ; w2uw7BJj5tG5BTlSdfT,MAX),
      range_cond([BMSQL_ITEM.I_NAME = 'w2uw7BJj5tG5BTlSdfT'])

1 row in set (0.00 sec)

说明:

  • TABLE SCAN : 对象变为唯一索引了,存储接口访问列是投影列。此时不需要过滤条件。
  • is_index_back: 由于索引列不包含i_price数据,所以需要回表。
  • range_key:扫描键值是索引列和主键列,印证了前面说法。
  • range:这里是向量的范围。
  • range_cond:扫描唯一索引传入的条件。

为了加深对回表和过滤的理解,再改改 SQL 。

EXPLAIN extended_noaddr
SELECT i_id, i_name FROM BMSQL_ITEM WHERE i_name = 'w2uw7BJj5tG5BTlSdfT' 
\G

*************************** 1. row ***************************
Query Plan: ======================================================
|ID|OPERATOR  |NAME                   |EST. ROWS|COST|
------------------------------------------------------
|0 |TABLE SCAN|BMSQL_ITEM(IDX_ITEM_UK)|1        |36  |
======================================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), filter(nil),
      access([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_ID]), partitions(p0),
      is_index_back=false,
      range_key([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.shadow_pk_0]), range(w2uw7BJj5tG5BTlSdfT,MIN ; w2uw7BJj5tG5BTlSdfT,MAX),
      range_cond([BMSQL_ITEM.I_NAME = 'w2uw7BJj5tG5BTlSdfT'])

1 row in set (0.00 sec)

说明:

  • 这个 SQL 跟之前的区别就是投影列都在索引内(包含主键),所以这里扫描索引就够了,不需要回表。

再给 SQL 加上一个过滤条件。

EXPLAIN extended_noaddr
SELECT i_id, i_name FROM BMSQL_ITEM
WHERE i_name = 'w2uw7BJj5tG5BTlSdfT' AND I_PRICE > 0
\G

*************************** 1. row ***************************
Query Plan: ======================================================
|ID|OPERATOR  |NAME                   |EST. ROWS|COST|
------------------------------------------------------
|0 |TABLE SCAN|BMSQL_ITEM(IDX_ITEM_UK)|1        |89  |
======================================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), filter([BMSQL_ITEM.I_PRICE > 0]),
      access([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_PRICE], [BMSQL_ITEM.I_ID]), partitions(p0),
      is_index_back=true, filter_before_indexback[false],
      range_key([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.shadow_pk_0]), range(w2uw7BJj5tG5BTlSdfT,MIN ; w2uw7BJj5tG5BTlSdfT,MAX),
      range_cond([BMSQL_ITEM.I_NAME = 'w2uw7BJj5tG5BTlSdfT'])

1 row in set (0.00 sec)

说明:

  • is_index_back :由于多了一个过滤条件不在索引里,即使投影列在索引里,这个还是需要回表。
  • filter : 这时候能看到多出的过滤列。
  • filter_before_indexback :表示过滤列是否在索引回表之前过滤。
  • range_cond : 扫描索引的时候传入的条件只有索引列。

为加深对 RANGE 查询的键值范围理解,再改改 SQL 。

EXPLAIN extended_noaddr
SELECT i_id, i_name FROM BMSQL_ITEM WHERE i_name LIKE  'w2u%'
\G

*************************** 1. row ***************************
Query Plan: ======================================================
|ID|OPERATOR  |NAME                   |EST. ROWS|COST|
------------------------------------------------------
|0 |TABLE SCAN|BMSQL_ITEM(IDX_ITEM_UK)|3        |37  |
======================================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), filter(nil),
      access([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_ID]), partitions(p0),
      is_index_back=false,
      range_key([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.shadow_pk_0]), range(w2u,MIN ; w2u�������������������������������,MAX),
      range_cond([(T_OP_LIKE, BMSQL_ITEM.I_NAME, ?, '\')])

1 row in set (0.00 sec)

说明:

  • range_key 可以看到最小值是传入的条件,最大值是个乱码(乱码是展示问题忽略)。
  • T_OP_LIKE: 这是 模糊匹配的操作符。

filter_before_indexback

filter_before_indexback 不是算子,表示扫描的时候,哪些过滤条件可以在索引回表之前计算,哪些在索引回表之后计算。通常单列索引很好判断。多列索引的查询会稍微复杂一些。

新增一个多列索引。

CREATE INDEX idx_item_name_data ON bmsql_item(i_name, i_data);

EXPLAIN extended_noaddr
SELECT i_id, i_name FROM BMSQL_ITEM WHERE i_data LIKE 'i3%'  AND I_PRICE > 5
\G

*************************** 1. row ***************************
Query Plan: ==============================================================
|ID|OPERATOR  |NAME                          |EST. ROWS|COST |
--------------------------------------------------------------
|0 |TABLE SCAN|BMSQL_ITEM(IDX_ITEM_NAME_DATA)|10       |71240|
==============================================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), filter([(T_OP_LIKE, BMSQL_ITEM.I_DATA, ?, '\')], [BMSQL_ITEM.I_PRICE > 5]),
      access([BMSQL_ITEM.I_DATA], [BMSQL_ITEM.I_PRICE], [BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), partitions(p0),
      is_index_back=true, filter_before_indexback[true,false],
      range_key([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_DATA], [BMSQL_ITEM.I_ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true

1 row in set (0.00 sec)

说明:

  • range : 虽然i_data列在索引里,但不是前导列,通常会认为用不上索引。但是 OB 应该是对这个有优化,允许使用这个索引(OB敢用索引代替表还有个原因是索引里会存储键值 NULL 数据)。只是会对这个索引做全表扫描。所以这个扫描范围是向量MIN到向量MAX,而用不上算子 T_OP_LIKE.
  • filter : 这个信息说明了参与过滤的列是 i_data 和 i_price
  • filter_before_indexback :表示 2 个过滤列里 i_data 是可以在回表之前参与过滤的,i_price 是在回表之后参与过滤的。在索引回表前过滤,能减少回表的行数,相比全表扫描,有时候性能还是要好一些(取决于有多少行要回表)。

这个例子解释了复合索引里列的顺序、索引回表前过滤等重要性。

排序

排序算子(SORT算子)的作用是将下层算子计算的结果进行排序。因为必须要拿到完整的数据后才能进行排序,所以该算子为阻塞性算子。SORT逻辑算子主要有三种表示: 普通排序,前缀排序和TOP-N排序。

SORT

继续上面 SQL 加一个排序条件。

EXPLAIN extended_noaddr
SELECT i_id, i_name FROM BMSQL_ITEM WHERE i_name LIKE  'w2u%'
ORDER BY i_name  DESC 
\G

*************************** 1. row ***************************
Query Plan: ==============================================================
|ID|OPERATOR  |NAME                           |EST. ROWS|COST|
--------------------------------------------------------------
|0 |TABLE SCAN|BMSQL_ITEM(IDX_ITEM_UK,Reverse)|3        |37  |
==============================================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), filter(nil),
      access([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_ID]), partitions(p0),
      is_index_back=false,
      range_key([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.shadow_pk_0]), range(w2u,MIN ; w2u�������������������������������,MAX),
      range_cond([(T_OP_LIKE, BMSQL_ITEM.I_NAME, ?, '\')])

1 row in set (0.00 sec)

说明:

  • 这个 SQL的排序条件恰好是索引列一部分,倒排序。这个可以利用索引扫描实现,不需要回表 。
  • 由于下层索引返回的数据自身是有序的,所以也不需要排序。相当于 SORT 算子被消除了。

换一个排序条件看看

EXPLAIN extended_noaddr
SELECT i_id, i_name FROM BMSQL_ITEM WHERE i_name LIKE  'w2u%'
ORDER BY I_PRICE  DESC 
\G

*************************** 1. row ***************************
Query Plan: =======================================================
|ID|OPERATOR   |NAME                   |EST. ROWS|COST|
-------------------------------------------------------
|0 |SORT       |                       |3        |106 |
|1 | TABLE SCAN|BMSQL_ITEM(IDX_ITEM_UK)|3        |99  |
=======================================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), filter(nil), sort_keys([BMSQL_ITEM.I_PRICE, DESC])
  1 - output([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_PRICE]), filter(nil),
      access([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_PRICE]), partitions(p0),
      is_index_back=true,
      range_key([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.shadow_pk_0]), range(w2u,MIN ; w2u�������������������������������,MAX),
      range_cond([(T_OP_LIKE, BMSQL_ITEM.I_NAME, ?, '\')])

1 row in set (0.00 sec)

说明:

  • is_index_back : 由于排序的列不在索引里,所以需要回表访问。且由于下层索引返回的数据顺序跟排序列顺序不一致,所以需要排序。
  • SORT : 就是排序操作。sort_keys 表示排序列和顺序。

再看一个利用索引消除 SORT算子例子。

EXPLAIN extended_noaddr
SELECT i_id, i_name FROM BMSQL_ITEM  WHERE I_PRICE  > 5
ORDER BY i_name 
\G

*************************** 1. row ***************************
Query Plan: ========================================================
|ID|OPERATOR  |NAME                   |EST. ROWS|COST  |
--------------------------------------------------------
|0 |TABLE SCAN|BMSQL_ITEM(IDX_ITEM_UK)|95970    |564941|
========================================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), filter([BMSQL_ITEM.I_PRICE > 5]),
      access([BMSQL_ITEM.I_PRICE], [BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), partitions(p0),
      is_index_back=true, filter_before_indexback[false],
      range_key([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.shadow_pk_0]), range(MIN,MIN ; MAX,MAX)always true

1 row in set (0.00 sec)

说明:

  • 由于排序条件在索引列,OB选择了扫描索引。由于索引里存储了键值的NULL值(如果有的话),所以 OB 可以使用扫描索引代替扫描全表而不遗漏数据。
  • is_index_back : 过滤条件的列不在索引里,需要回表。

而在 ORACLE 里,索引是不存储键值的NULL记录。这个SQL就走全表扫描了。

SQL> set autot on exp
SQL> SELECT i_id, i_name FROM BMSQL_ITEM  WHERE I_PRICE  > 5
ORDER BY i_name
;  2    3

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1706420712

---------------------------------------------------------------------------------
| Id  | Operation      | Name   | Rows  | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    40 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY     |        |     1 |    40 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| BMSQL_ITEM |     1 |    40 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("I_PRICE">5)

Note
-----
   - dynamic sampling used for this statement (level=2)

这里只是说两种执行计划不一样,具体性能如何还跟实际 SQL 和数据量有关。

TOP-N SORT

常用的场景排序后可能只用返回最大或最小的前 N 条记录。

EXPLAIN extended_noaddr
SELECT * FROM (
SELECT  i_id, i_name FROM BMSQL_ITEM WHERE i_name LIKE  'w2u%'
ORDER BY I_PRICE  DESC 
) WHERE rownum < 5
\G

*************************** 1. row ***************************
Query Plan: ========================================================
|ID|OPERATOR    |NAME                   |EST. ROWS|COST|
--------------------------------------------------------
|0 |LIMIT       |                       |3        |106 |
|1 | TOP-N SORT |                       |3        |106 |
|2 |  TABLE SCAN|BMSQL_ITEM(IDX_ITEM_UK)|3        |99  |
========================================================

Outputs & filters:
-------------------------------------
  0 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), filter(nil), limit(?), offset(nil)
  1 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), filter(nil), sort_keys([BMSQL_ITEM.I_PRICE, DESC]), topn(?)
  2 - output([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_PRICE]), filter(nil),
      access([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_PRICE]), partitions(p0),
      is_index_back=true,
      range_key([BMSQL_ITEM.I_NAME], [BMSQL_ITEM.shadow_pk_0]), range(w2u,MIN ; w2u�������������������������������,MAX),
      range_cond([(T_OP_LIKE, BMSQL_ITEM.I_NAME, ?, '\')])

1 row in set (0.00 sec)

说明:

  • TOP-N SORT : 跟上面不一样是排序后,只需要返回前 N 笔记录,所以这里用的是 TOP-N SORT 算子,不需要返回所有记录。topn 指定返回的行数。
  • LIMIT :这个算子限制返回的行数。

再看一个常用的分页查询场景。在 ORACLE 租户里分页查询需要两层嵌套查询(对应ORACLE 11g版本功能)。为了简化情况,我去掉了过滤条件,以排除 使用唯一索引时 OB的执行计划优化,方便看到简单直接的分页排序执行计划。

EXPLAIN extended_noaddr
SELECT i_id, i_name, rn
FROM (
    SELECT rownum rn, i_id, i_name FROM (
        SELECT  i_id, i_name FROM BMSQL_ITEM 
        ORDER BY I_PRICE  DESC 
    ) t WHERE rownum <= 15
) WHERE rn > 10
\G

*************************** 1. row ***************************
Query Plan: ===============================================
|ID|OPERATOR      |NAME      |EST. ROWS|COST  |
-----------------------------------------------
|0 |COUNT         |          |5        |189290|
|1 | SUBPLAN SCAN |T         |5        |189289|
|2 |  LIMIT       |          |5        |189289|
|3 |   TOP-N SORT |          |15       |189287|
|4 |    TABLE SCAN|BMSQL_ITEM|100000   |34254 |
===============================================

Outputs & filters:
-------------------------------------
  0 - output([T.I_ID], [T.I_NAME], [rownum() + ?]), filter(nil)
  1 - output([T.I_ID], [T.I_NAME]), filter(nil),
      access([T.I_ID], [T.I_NAME])
  2 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), filter(nil), limit(?), offset(?)
  3 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME]), filter(nil), sort_keys([BMSQL_ITEM.I_PRICE, DESC]), topn(? + ?)
  4 - output([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_PRICE]), filter(nil),
      access([BMSQL_ITEM.I_ID], [BMSQL_ITEM.I_NAME], [BMSQL_ITEM.I_PRICE]), partitions(p0),
      is_index_back=false,
      range_key([BMSQL_ITEM.I_ID]), range(MIN ; MAX)always true

1 row in set (0.01 sec)

说明:

  • 跟上面区别有多了一个 COUNT 算子 和 SUBPLAN 算子。

总结

总结几个要点:

  • OB 的表、索引本质上都是索引组织表。主键就是数据,普通索引都包含主键列。
  • OB 的执行计划能看到是访问表还是索引,以及参与扫描的范围、过滤条件、排序条件等。
  • OB 的索引包含了键值的 NULL 记录(如果索引列属性为NULL的话)。这使得在某些排序场景下,OB 可能会选择扫描索引代替扫描全表,来减少IO ,这样性能有可能会更好。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论