分区裁剪简介

当用户访问分区表时，往往只需要访问其中部分的分区。优化器根据 SQL 中所带的条件，避免访问无关分区的优化过程我们称之为“分区裁剪”(Partition Pruning)。分区裁剪是分区表提供的重要优化手段，通过分区的裁剪，SQL 的执行效率可以得到大幅度的提升。用户可以利用分区裁剪的特性，在访问中加入定位分区的条件，避免访问无关数据，优化查询性能。

分区裁剪本身是一个比较复杂的过程。优化器需要根据用户表的分区信息和 SQL 中给定的条件，抽取出相关的分区信息，由于 SQL 中的条件往往比较复杂，整个抽取逻辑的复杂性也随之增加，这一过程由 OceanBase 中的 Query Range 子模块完成。

如下为用户使用 SQL 访问分区表的示例：

  1. create table t1 (c1 int, c2 int) partition by hash(c1) partitions 5;
  2. select * from t1 where c1 = 1;

由于 c1 为1的数据全部处于第1号分区(p1)，实际上我们只需要访问该分区即可(避免访问第0、2、3、4号分区）。

通过 explain 可以看到分区裁剪的结果如下例所示：

  1. create table t1 (c1 int, c2 int) partition by hash(c1) partitions 5;
  3. explain select * from t1 where c1 = 1 \G
  4. *************************** 1. row ***************************
  5. Query Plan: ===================================
  6. |ID|OPERATOR  |NAME|EST. ROWS|COST|
  7. -----------------------------------
  8. |0 |TABLE SCAN|t1  |1        |1303|
  9. ===================================
  11. Outputs & filters:
  12. -------------------------------------
  13.   0 - output([t1.c1], [t1.c2]), filter([t1.c1 = 1]),
  14.       access([t1.c1], [t1.c2]), partitions(p1)

裁剪的结果（p1)显示在了 Table Scan 操作符的 partitions 属性中。

分区裁剪基本原理

一级分区的裁剪

  • hash/list分区

分区裁剪就是根据 where 子句里面的条件并且计算得到分区列的值，通过结果判断需要访问哪些分区。
如果分区函数为表达式，且该表达式作为一个整体出现在等值条件里，也可以做分区裁剪。

  1. create table t1 (c1 int, c2 int) partition by hash(c1 + c2) partitions 5;
  3. explain select * from t1 where c1 + c2 = 1 \G
  4. *************************** 1. row ***************************
  5. Query Plan: ===================================
  6. |ID|OPERATOR  |NAME|EST. ROWS|COST|
  7. -----------------------------------
  8. |0 |TABLE SCAN|t1  |5        |1303|
  9. ===================================
  11. Outputs & filters:
  12. -------------------------------------
  13.   0 - output([t1.c1], [t1.c2]), filter([t1.c1 + t1.c2 = 1]),
  14.       access([t1.c1], [t1.c2]), partitions(p1)
  • range分区
    通过 where 子句的分区键的范围跟表定义的分区范围的交集来确定需要访问的分区。

注意：

对于range分区，因为考虑到函数的单调性，如果分区表达式是一个函数并且查询条件是一个范围，则不支持分区裁剪。

如下例所示，分区条件为表达式，而查询条件为非等值条件（c1 < 150 and c1 > 100），则无法进行分区裁剪。

  1. create table t1 (c1 int, c2 int) partition by range(c1 + 1)
  2. (partition p0 values less than (100), partition p1 values less than(200));
  4. explain select * from t1 where c1 < 150 and c1 > 110 \G
  5. *************************** 1. row ***************************
  6. Query Plan: ============================================
  7. |ID|OPERATOR           |NAME|EST. ROWS|COST|
  8. --------------------------------------------
  9. |0 |EXCHANGE IN DISTR  |    |19       |1410|
  10. |1 | EXCHANGE OUT DISTR|    |19       |1303|
  11. |2 |  TABLE SCAN       |t1  |19       |1303|
  12. ============================================
  14. Outputs & filters:
  15. -------------------------------------
  16.   0 - output([t1.c1], [t1.c2]), filter(nil)
  17.   1 - output([t1.c1], [t1.c2]), filter(nil)
  18.   2 - output([t1.c1], [t1.c2]), filter([t1.c1 < 150], [t1.c1 > 110]),
  19.       access([t1.c1], [t1.c2]), partitions(p[0-1])

如果查询条件是等值条件，则可以进行分区裁剪。

  1. explain select * from t1 where c1 = 150  \G
  2. *************************** 1. row ***************************
  3. Query Plan: ===================================
  4. |ID|OPERATOR  |NAME|EST. ROWS|COST|
  5. -----------------------------------
  6. |0 |TABLE SCAN|t1  |1        |1303|
  7. ===================================
  9. Outputs & filters:
  10. -------------------------------------
  11.   0 - output([t1.c1], [t1.c2]), filter([t1.c1 = 150]),
  12.       access([t1.c1], [t1.c2]), partitions(p1)

二级分区的裁剪

对于二级分区，先按照一级分区键确定一级需要访问的分区，然后在通过二级分区键确定二级分区需要访问的分区。然后做一个乘积确定二级分区访问的所有物理分区。

  1. create table t1 (c1 int , c2 int) partition by hash(c1) subpartition by range(c2) subpartition template (subpartition sp0 values less than(100), subpartition sp1 values less than(200)) partitions 5
  2. select * from t1 where (c1 = 1 or c1 = 2) and (c2 > 101 and c2 < 150)

经过计算得到一级分区裁剪结果是 p1、p2，而二级分区裁剪的结果是 sp1，所以访问的物理分区为 p1sp1、p2sp1。

  1. explain select * from t1 where (c1 = 1 or c1 = 2) and (c2 > 101 and c2 < 150) \G
  2. *************************** 1. row ***************************
  3. Query Plan: ============================================
  4. |ID|OPERATOR           |NAME|EST. ROWS|COST|
  5. --------------------------------------------
  6. |0 |EXCHANGE IN DISTR  |    |1        |1403|
  7. |1 | EXCHANGE OUT DISTR|    |1        |1303|
  8. |2 |  TABLE SCAN       |t1  |1        |1303|
  9. ============================================
  11. Outputs & filters:
  12. -------------------------------------
  13.   0 - output([t1.c1], [t1.c2]), filter(nil)
  14.   1 - output([t1.c1], [t1.c2]), filter(nil)
  15.   2 - output([t1.c1], [t1.c2]), filter([t1.c1 = 1 OR t1.c1 = 2], [t1.c2 > 101], [t1.c2 < 150]),
  16.       access([t1.c1], [t1.c2]), partitions(p1sp1, p2sp1)

需要注意的是，上述规则在某些情况下并不适用，不能得到精确的分区。如下例所示：

  1. select * from t1 where (c1,c2) in ((1,10), (2,150));

根据上述规则，一级分区要访问 p1, p2，二级分区要访问 sp0, sp1，那么最后访问的物理分区为 p1sp0, p1sp1, p2sp0, p2sp1。实际上只需要访问 p1sp0, p2sp1 即可。因此，我们对向量条件做了特殊处理，能精确的做分区裁剪。

  1. explain select * from t1 where (c1,c2) in ((1,10), (2,150)) \G
  2. *************************** 1. row ***************************
  3. Query Plan: ============================================
  4. |ID|OPERATOR           |NAME|EST. ROWS|COST|
  5. --------------------------------------------
  6. |0 |EXCHANGE IN DISTR  |    |1        |1403|
  7. |1 | EXCHANGE OUT DISTR|    |1        |1303|
  8. |2 |  TABLE SCAN       |t1  |1        |1303|
  9. ============================================
  11. Outputs & filters:
  12. -------------------------------------
  13.   0 - output([t1.c1], [t1.c2]), filter(nil)
  14.   1 - output([t1.c1], [t1.c2]), filter(nil)
  15.   2 - output([t1.c1], [t1.c2]), filter([(t1.c1, t1.c2) IN ((1, 10), (2, 150))]),
  16.       access([t1.c1], [t1.c2]), partitions(p1sp0, p2sp1)

注意：

某些场景下，分区裁剪可能会存在一定程度的放大，但优化器可以确保裁剪的结果是所需访问数据的超集，不会存在丢失数据的情况。

指定分区的查询

除了根据 SQL 的查询条件进行分区裁剪以外，OceanBase 也支持用户通过 SQL 语法指定需要访问的分区。

如下例所示，partition(p0) 指定了只访问 p0分区。

  1. select * from t1 partition (p0);

注意：

如果SQL中指定了分区，系统会将查询的范围限定在所指定的分区集合内，同时根据SQL的查询条件进行分区裁剪。最终访问的分区为指定分区和分区裁剪二者的交集。

分区名字规则

由于需要指定分区查询，所以用户需要知道分区的命名规则。
对于 list 和 range 分区，因为在创建表的过程中就指定了分区的名字。所以名字就是用户给定的名字。对于 hash/key 分区，用户没有给定分区的名字，分区的命名由系统根据命名规则完成，具体是：如果 hash/key 出现在一级分区里面，那么每个分区分别命名为 p0, p1, …, pn。如果出现在二级分区里面，那么就是 sp0, sp1, …, spn。

二级分区的名字由“一级分区+二级分区”方式构成。例如 p0sp0，其中 p0 是一级分区的名字，sp0 是二级分区的名字。

获取二级分区的各级分区 id

对于二级分区，在日志里面的分区id很大。如下例所示：

  1. create table t1 (c1 int , c2 int) partition by hash(c1) 
  2. subpartition by range(c2) 
  3. subpartition template 
  4. (subpartition sp0 values less than(100), 
  5. subpartition sp1 values less than(200)) partitions 5

对于 t1，实际的分区 id 如下例所示：

  1. select partition_id from __all_meta_table join __all_table using(table_id) 
  2. where table_name = 't1';
  3. +---------------------+
  4. | partition_id        |
  5. +---------------------+
  6. | 1152921504875282432 |
  7. | 1152921504875282433 |
  8. | 1152921509170249728 |
  9. | 1152921509170249729 |
  10. | 1152921513465217024 |
  11. | 1152921513465217025 |
  12. | 1152921517760184320 |
  13. | 1152921517760184321 |
  14. | 1152921522055151616 |
  15. | 1152921522055151617 |
  16. +---------------------+

Partition-Wise Join 简介

Partition-Wise Joins 是指当需要连接的表是按照连接条件进行分区的时候，连接只需对连接表对应分区进行连接操作，能极大提高连接的性能。

  1. create table t1 (c1 int, c2 int) partition by hash(c1) partitions 5;
  2. create table t2 (c1 int, c2 int) partition by hash(c1) partitions 5;
  4. explain select * from t1 join t2 using (c1) \G
  5. *************************** 1. row ***************************
  6. Query Plan: =============================================
  7. |ID|OPERATOR           |NAME|EST. ROWS|COST |
  8. ---------------------------------------------
  9. |0 |EXCHANGE IN DISTR  |    |49500    |52169|
  10. |1 | EXCHANGE OUT DISTR|    |49500    |42798|
  11. |2 |  HASH JOIN        |    |49500    |42798|
  12. |3 |   TABLE SCAN      |t1  |5000     |499  |
  13. |4 |   TABLE SCAN      |t2  |5000     |499  |
  14. =============================================
  16. Outputs & filters:
  17. -------------------------------------
  18.   0 - output([t1.c1], [t1.c2], [t2.c2]), filter(nil)
  19.   1 - output([t1.c1], [t1.c2], [t2.c2]), filter(nil)
  20.   2 - output([t1.c1], [t1.c2], [t2.c2]), filter(nil),
  21.       equal_conds([t1.c1 = t2.c1]), other_conds(nil)
  22.   3 - output([t1.c1], [t1.c2]), filter(nil),
  23.       access([t1.c1], [t1.c2]), partitions(p[0-4])
  24.   4 - output([t2.c1], [t2.c2]), filter(nil),
  25.       access([t2.c1], [t2.c2]), partitions(p[0-4])

使用Partition-Wise Join需要满足以下条件：

  • 连接条件包含所有的分区键

  • 分区方式需要完全一致

  • 对应分区必须分布在同一台机器上