揭秘 OceanBase SQL 执行计划（一）

数据库技术闲谈 2021-03-08

160

本文分享 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 ，这样性能有可能会更好。

更多阅读

OceanBase 索引列的NULL属性测试分析
阿里数据库性能诊断的利器——SQL执行干预
阿里数据库性能诊断的利器——SQL全量日志

未完待续，如果觉得有帮助，就点个赞吧 😄️

oceanbase

文章转载自数据库技术闲谈，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

揭秘 OceanBase SQL 执行计划（一）

执行计划的概念

OceanBase 客户端

EXPLAIN 命令

常用 SQL 执行计划解读

表访问

TABLE GET

TABLE SCAN

filter_before_indexback

排序

SORT

TOP-N SORT

总结

评论

`TABLE GET`

`TABLE SCAN`

`filter_before_indexback`

`SORT`

`TOP-N SORT`