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