【OceanBase】SQL 执行计划 Part2

原创 __V__ All China Database Union 2022-07-20

545

常用 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 是在回表之后参与过滤的。在索引回表前过滤，能减少回表的行数，相比全表扫描，有时候性能还是要好一些(取决于有多少行要回表）。

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

oceanbase 执行计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

【OceanBase】SQL 执行计划 Part2

常用 SQL 执行计划解读

表访问

TABLE GET

TABLE SCAN

filter_before_indexback

评论

`TABLE GET`

`TABLE SCAN`

`filter_before_indexback`