如何利用非分区键进行分区裁剪

OceanBase 支持利用和分区键有一定关系（一般是等值关系或者子串关系）的非分区键进行分区裁剪，但是需要通过生成列（generated column）或者 check 约束（check constraint），让数据库明确知道这两个列之间确实满足这种关系。

问题描述

创建一张表 t1，表中的 id 和 user_id 满足 id / 1000000 = user_id（向下取整），例如 23999999 / 1000000 = 23。

1. 创建表 t1。

   obclient [mysql]> create table t1(
           id int,
           user_id int)
   partition by hash(user_id) partitions 128;
   Query OK, 0 rows affected (0.186 sec)
   

2. 向表 t1 插入数据。

   obclient [mysql]> insert into t1 values(1000000, 1);
   Query OK, 1 row affected (0.051 sec)
   

   obclient [mysql]> insert into t1 values(1000001, 1);
   Query OK, 1 row affected (0.042 sec)
   

   obclient [mysql]> insert into t1 values(2000000, 2);
   Query OK, 1 row affected (0.045 sec)
   

   obclient [mysql]> insert into t1 values(2000001, 2);
   Query OK, 1 row affected (0.042 sec)
   

   obclient [mysql]> insert into t1 values(23000000, 23);
   Query OK, 1 row affected (0.045 sec)
   

   obclient [mysql]> insert into t1 values(23999999, 23);
   Query OK, 1 row affected (0.042 sec)
   

3. 查询表 t1 内容。

   obclient [mysql]> select * from t1;
   

   输出结果如下：

   +----------+---------+
   | id       | user_id |
   +----------+---------+
   |  1000000 |       1 |
   |  1000001 |       1 |
   |  2000000 |       2 |
   |  2000001 |       2 |
   | 23000000 |      23 |
   | 23999999 |      23 |
   +----------+---------+
   6 rows in set (0.212 sec)
   

4. 非分区键 id 做过滤条件查询表 t1 内容，并显示执行计划。

   obclient [mysql]> explain select * from t1 where id = 23000000;
   

   输出结果如下：

   +------------------------------------------------------------------------------------+
   | Query Plan                                                                         |
   +------------------------------------------------------------------------------------+
   | =============================================================                      |
   | |ID|OPERATOR                 |NAME    |EST.ROWS|EST.TIME(us)|                      |
   | -------------------------------------------------------------                      |
   | |0 |PX COORDINATOR           |        |1       |485         |                      |
   | |1 |└─EXCHANGE OUT DISTR     |:EX10000|1       |485         |                      |
   | |2 |  └─PX PARTITION ITERATOR|        |1       |484         |                      |
   | |3 |    └─TABLE FULL SCAN    |t1      |1       |484         |                      |
   | =============================================================                      |
   | Outputs & filters:                                                                 |
   | -------------------------------------                                              |
   |   0 - output([INTERNAL_FUNCTION(t1.id, t1.user_id)]), filter(nil), rowset=16       |
   |   1 - output([INTERNAL_FUNCTION(t1.id, t1.user_id)]), filter(nil), rowset=16       |
   |       dop=1                                                                        |
   |   2 - output([t1.user_id], [t1.id]), filter(nil), rowset=16                        |
   |       force partition granule                                                      |
   |   3 - output([t1.user_id], [t1.id]), filter([t1.id = 23000000]), rowset=16         |
   |       access([t1.user_id], [t1.id]), partitions(p[0-127])                          |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
   |       range_key([t1.__pk_increment]), range(MIN ; MAX)always true                  |
   +------------------------------------------------------------------------------------+
   19 rows in set (0.055 sec)
   

   综上可知 SQL 语句中查询过滤条件是 where id = 23000000，不包含分区键 user_id，默认会查询所有分区。可以从显示的执行计划中看到 3 号算子 table scan 扫描了 partitions(p[0-127]) 这所有 128 个分区。

5. 非分区键 id 和 分区键 user_id 做过滤条件查询表 t1 内容，并显示执行计划。

   obclient [mysql]> explain select * from t1 where id = 23000000 and user_id = 23;
   

   输出结果如下：

   +-----------------------------------------------------------------------------------------------+
   | Query Plan                                                                                    |
   +-----------------------------------------------------------------------------------------------+
   | ===============================================                                               |
   | |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                               |
   | -----------------------------------------------                                               |
   | |0 |TABLE FULL SCAN|t1  |1       |4           |                                               |
   | ===============================================                                               |
   | Outputs & filters:                                                                            |
   | -------------------------------------                                                         |
   |   0 - output([t1.id], [t1.user_id]), filter([t1.id = 23000000], [t1.user_id = 23]), rowset=16 |
   |       access([t1.user_id], [t1.id]), partitions(p23)                                          |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false,false],       |
   |       range_key([t1.__pk_increment]), range(MIN ; MAX)always true                             |
   +-----------------------------------------------------------------------------------------------+
   11 rows in set (0.044 sec)
   

   综上可知如果想要让计划做分区裁剪，需要人为的在 SQL 语句中增加 user_id = 23 做过滤条件。由显示的执行计划可知，虽然新增的这个过滤条件并不能过滤掉更多数据，但是在执行前可以利用分区键 user_id = 23，裁剪出需要进行 table scan 扫描的只有一个分区 partitions(p23)。

   说明

   有时过滤还不只是等值查询, 可能是 IN 查询，比如 where id IN (23000000, 2000001…)，如果不想让查询遍历所有分区，还需要人为的在过滤条件中增加 where user_id IN (23, 2…) 这样对应的过滤条件，十分麻烦。

利用生成列

如果希望能够利用和分区键有一定关系（一般是等值关系或者子串关系）的非分区键进行分区裁剪，显然就需要让 OceanBase 数据库先明确知道这两个列之间确实满足这种关系。 其中一种方法就是把其中的分区键写成基于另一个非分区键的生成列（substr 或者等值），这样另一个非分区键就也可以用来进行分区裁剪了，下面就简单举一个例子。

1. 创建表 t2。

   obclient [mysql]> create table t2(
           id int,
           user_id int generated always as (substr(id, 1, length(id) - 6)) virtual)
      partition by hash(user_id) partitions 128;
   Query OK, 0 rows affected (0.174 sec)
   

2. 向表 t2 插入数据。

   obclient [mysql]> insert into t2(id) values(1000000);
   Query OK, 1 row affected (0.056 sec)
   

   obclient [mysql]> insert into t2(id) values(1000001);
   Query OK, 1 row affected (0.042 sec)
   

   obclient [mysql]> insert into t2(id) values(2000000);
   Query OK, 1 row affected (0.045 sec)
   

   obclient [mysql]> insert into t2(id) values(2000001);
   Query OK, 1 row affected (0.042 sec)
   

   obclient [mysql]> insert into t2(id) values(23000000);
   Query OK, 1 row affected (0.045 sec)
   

   obclient [mysql]> insert into t2(id) values(23999999);
   Query OK, 1 row affected (0.042 sec)
   

3. 查询表 t2 内容。

   obclient [mysql]> select * from t2;
   

   输出结果如下：

   +----------+---------+
   | id       | user_id |
   +----------+---------+
   |  1000000 |       1 |
   |  1000001 |       1 |
   |  2000000 |       2 |
   |  2000001 |       2 |
   | 23000000 |      23 |
   | 23999999 |      23 |
   +----------+---------+
   6 rows in set (0.202 sec)
   

4. 分区键 user_id 做过滤条件查询表 t2 内容，并显示执行计划。

   obclient [mysql]> explain select * from t2 where user_id = 23;
   

   输出结果如下：

   +----------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                     |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ===============================================                                                                                                                |
   | |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                                                                                                |
   | -----------------------------------------------                                                                                                                |
   | |0 |TABLE FULL SCAN|t2  |1       |4           |                                                                                                                |
   | ===============================================                                                                                                                |
   | Outputs & filters:                                                                                                                                            |
   | -------------------------------------                                                                                                                          |
   |   0 - output([t2.id], [column_conv(INT,PS:(11,0),NULL,cast(substr(cast(t2.id, VARCHAR(1048576)), 1, length(cast(t2.id, VARCHAR(1048576))) - 6), INT(-1,        |
   |       0)))]), filter([column_conv(INT,PS:(11,0),NULL,cast(substr(cast(t2.id, VARCHAR(1048576)), 1, length(cast(t2.id, VARCHAR(1048576))) - 6), INT(-1, 0))) =  |
   |       23]), rowset=16                                                                                                                                          |
   |       access([t2.id]), partitions(p23)                                                                                                                         |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                              |
   |       range_key([t2.__pk_increment]), range(MIN ; MAX)always true                                                                                              |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------+
   13 rows in set (0.045 sec)
   

   综上可知分区键 user_id 做过滤条件，会做分区裁剪，table scan 只需要扫描一个分区：partitions(p23)。

5. 非分区键 id 做过滤条件查询表 t2 内容，并显示执行计划。

   obclient [mysql]> explain select * from t2 where id = 23000000;
   

   输出结果如下：

   +----------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                               |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ===============================================                                                                                                          |
   | |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                                                                                          |
   | -----------------------------------------------                                                                                                          |
   | |0 |TABLE FULL SCAN|t2  |1       |4           |                                                                                                          |
   | ===============================================                                                                                                          |
   | Outputs & filters:                                                                                                                                       |
   | -------------------------------------                                                                                                                    |
   |   0 - output([t2.id], [column_conv(INT,PS:(11,0),NULL,cast(substr(cast(t2.id, VARCHAR(1048576)), 1, length(cast(t2.id, VARCHAR(1048576))) - 6), INT(-1,  |
   |       0)))]), filter([t2.id = 23000000]), rowset=16                                                                                                      |
   |       access([t2.id]), partitions(p23)                                                                                                                   |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                        |
   |       range_key([t2.__pk_increment]), range(MIN ; MAX)always true                                                                                        |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------+
   12 rows in set (0.044 sec)
   

   综上可知非分区键 id 做过滤条件，也做了分区裁剪，table scan 也只扫描了一个分区：partitions(p23)。

利用 check 约束

除了生成列，check 约束也能约束两个列之间满足某种特定的关系，和生成列的约束十分类似。所以我们还可以利用 check 约束让非分区键也可以被利用来进行分区裁剪。不过利用 check 约束的条件会比生成列更严格些，例如需要让 substr 里的列类型是字符类型。

1. 创建表 t3。

   obclient [mysql]> create table t3(
       id varchar(10), user_id varchar(10),
       constraint cst check(user_id = substr(id, 1, 2)))
       partition by list columns(user_id) (
       partition p0 values in ('23'),
       partition p1 values in ('45'),
       partition p2 values in ('67'));
   Query OK, 0 rows affected (0.174 sec)
   

2. 向表 t3 插入数据。

   obclient [mysql]> insert into t4 values(230000, 23);
   Query OK, 1 row affected (0.064 sec)
   

   obclient [mysql]> insert into t4 values(450000, 45);
   Query OK, 1 row affected (0.044 sec)
   

   obclient [mysql]> insert into t4 values(670000, 67);
   Query OK, 1 row affected (0.044 sec)
   

3. 分区键 user_id 做过滤条件查询表 t3 内容，并显示执行计划。

   obclient [mysql]> explain select * from t3 where user_id = '45';
   

   输出结果如下：

   +------------------------------------------------------------------------------------+
   | Query Plan                                                                         |
   +------------------------------------------------------------------------------------+
   | ===============================================                                    |
   | |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                    |
   | -----------------------------------------------                                    |
   | |0 |TABLE FULL SCAN|t3  |1       |4           |                                    |
   | ===============================================                                    |
   | Outputs & filters:                                                                 |
   | -------------------------------------                                              |
   |   0 - output([t3.id], [t3.user_id]), filter([t3.user_id = '45']), rowset=16        |
   |       access([t3.user_id], [t3.id]), partitions(p1)                                |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
   |       range_key([t3.__pk_increment]), range(MIN ; MAX)always true                  |
   +------------------------------------------------------------------------------------+
   11 rows in set (0.046 sec)
   

   综上可知分区键 user_id 做过滤条件，会做分区裁剪，table scan 只需要扫描一个分区：partitions(p1)。

4. 非分区键 id 做过滤条件查询表 t3 内容，并显示执行计划。

   obclient [mysql]> explain select * from t4 where id = '450000';
   

   输出结果如下：

   +------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                 |
   +------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ===============================================                                                                                                            |
   | |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                                                                                            |
   | -----------------------------------------------                                                                                                            |
   | |0 |TABLE FULL SCAN|t4  |1       |4           |                                                                                                            |
   | ===============================================                                                                                                            |
   | Outputs & filters:                                                                                                                                         |
   | -------------------------------------                                                                                                                      |
   |   0 - output([t4.id], [t4.user_id]), filter([t4.id = '450000'], [t4.user_id IS NULL OR t4.user_id = substr(cast('450000', VARCHAR(10)), 1, 2)]), rowset=16 |
   |       access([t4.user_id], [t4.id]), partitions(p1)                                                                                                        |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false,false],                                                                    |
   |       range_key([t4.__pk_increment]), range(MIN ; MAX)always true                                                                                          |
   +------------------------------------------------------------------------------------------------------------------------------------------------------------+
   11 rows in set (0.045 sec)
   

   综上可知非分区键 id 做过滤条件，也做了分区裁剪，table scan 也只扫描了一个分区：partitions(p1)。

适用版本

OceanBase 数据库 V4.x 版本。