分区分桶

概述

为了能高效处理大数据量的存储和计算，云数据库 SelectDB 版按分治思想对数据进行分割处理，将数据分散到分布式系统中进行处理。

SelectDB中所有的表引擎都支持如下两种的数据划分。

• 一层：仅使用一层分区时。

  • 建表时不写分区语句即可，此时SelectDB会生成一个默认分区，默认分区是透明的。使用一层分区时，只支持Bucket划分。

• 二层：使用两层分区时。

  • 第一层是分区（Partition），支持Range和List的划分方式。

  • 第二层是分桶（Bucket，即Tablet），仅支持Hash的划分方式。

分区

分区用于将数据划分成不同区间，可以理解为将原始表划分成了多个子表，这样可以对数据进行分区管理。分区具有如下特性。

• 分区（Partition）列可以指定一列或多列，分区列必须为Key列。

• 不论分区列是什么类型，在写分区值时，都需要加双引号。

• 分区数量理论上没有上限。

• 当不使用分区（Partition）建表时，系统会自动生成一个和表名同名的，全值范围的分区（Partition）。该分区（Partition）对用户不可见，并且不可删改。

• 创建分区时不可添加范围重叠的分区。

Range分区

Range分区的分区列通常为时间列，以方便管理新数据和旧数据。Range分区（Partition）支持通过VALUES LESS THAN (...)仅指定上界，系统会将前一个分区的上界作为该分区的下界，生成一个左闭右开的区间。同时，也可通过VALUES [...）指定上下界，生成一个左闭右开的区间。

单列分区

当使用VALUES LESS THAN (...)语句进行分区的增删操作时，分区范围的变化情况。示例如下。

1. 创建示例表test_table。

   CREATE TABLE IF NOT EXISTS test_db.test_table
   (
     `user_id` LARGEINT NOT NULL COMMENT "用户id", 
     `date` DATE NOT NULL COMMENT "数据灌入日期时间", 
     `timestamp` DATETIME NOT NULL COMMENT "数据灌入的时间戳", 
     `city` VARCHAR(20) COMMENT "用户所在城市", 
     `age` SMALLINT COMMENT "用户年龄", 
     `sex` TINYINT COMMENT "用户性别", 
     `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
     `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
     `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
     `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
   )ENGINE=OLAP
   AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
   PARTITION BY RANGE(`date`)
   ( 
     PARTITION `p201701` VALUES LESS THAN ("2017-02-01"),
     PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
     PARTITION `p201703` VALUES LESS THAN ("2017-04-01")
   )
   DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

   创建示例表test_table完成后，会自动生成如下3个分区。

   p201701: [MIN_VALUE, 2017-02-01)
   p201702: [2017-02-01, 2017-03-01)
   p201703: [2017-03-01, 2017-04-01)

2. 通过ALTER TABLE test_db.test_table ADD PARTITION p201705 VALUES LESS THAN ("2017-06-01");命令增加一个分区p201705，则分区结果如下。

   p201701: [MIN_VALUE, 2017-02-01)
   p201702: [2017-02-01, 2017-03-01)
   p201703: [2017-03-01, 2017-04-01)
   p201705: [2017-04-01, 2017-06-01)

3. 通过ALTER TABLE test_db.test_table DROP PARTITION p201703;命令删除分区p201703，则分区结果如下。

   p201701: [MIN_VALUE, 2017-02-01)
   p201702: [2017-02-01, 2017-03-01)
   p201705: [2017-04-01, 2017-06-01)

   重要

   上述示例中，删除分区p201703后，p201702和p201705的分区范围没有发生变化，而这两个分区之间，出现了一个空缺：[2017-03-01，2017-04-01)。即如果导入的数据范围在这个空缺范围内，是无法导入的且已经存在在空缺范围内的数据也会被删除。

4. 继续删除分区p201702，则分区结果如下。

   p201701: [MIN_VALUE, 2017-02-01)
   p201705: [2017-04-01, 2017-06-01)

   空缺范围变为：[2017-02-01，2017-04-01)。

5. 继续增加一个分区 `p201702new` VALUES LESS THAN ("2017-03-01")，则分区结果如下。

   p201701: [MIN_VALUE, 2017-02-01)
   p201702new: [2017-02-01, 2017-03-01)
   p201705: [2017-04-01, 2017-06-01)

   空缺范围变为：[2017-03-01，2017-04-01)。

6. 继续删除分区p201701，并添加分区`p201612` VALUES LESS THAN ("2017-01-01")，则分区结果如下。

   p201612: [MIN_VALUE, 2017-01-01)
   p201702new: [2017-02-01, 2017-03-01)
   p201705: [2017-04-01, 2017-06-01) 

   空缺范围变为：[2017-01-01，2017-02-01)和[2017-03-01，2017-04-01)。

通过上述示例表明，分区的删除不会改变已存在分区的范围。删除分区可能出现空缺。通过VALUES LESS THAN语句增加分区时，分区的下界需要紧接上一个分区的上界。

多列分区

在创建表分区时，添加如下多列分区设置。示例如下。

PARTITION BY RANGE(`date`, `id`)
(
  PARTITION `p201701_1000` VALUES LESS THAN ("2017-02-01", "1000"),
  PARTITION `p201702_2000` VALUES LESS THAN ("2017-03-01", "2000"),
  PARTITION `p201703_all` VALUES LESS THAN ("2017-04-01")
)

指定date（DATE类型）和id（INT类型）作为分区列。则分区结果如下。

* p201701_1000: [(MIN_VALUE, MIN_VALUE), ("2017-02-01", "1000") )
* p201702_2000: [("2017-02-01", "1000"), ("2017-03-01", "2000") )
* p201703_all: [("2017-03-01", "2000"), ("2017-04-01", MIN_VALUE)) 

最后一个分区只指定了date列的分区值，所以id列的分区值会默认填充MIN_VALUE。当用户插入数据时，分区列值会按照顺序依次比较，最终得到对应的分区，示例如下。

* 数据 --> 分区
* 2017-01-01, 200 --> p201701_1000
* 2017-01-01, 2000 --> p201701_1000
* 2017-02-01, 100 --> p201701_1000
* 2017-02-01, 2000 --> p201702_2000
* 2017-02-15, 5000 --> p201702_2000
* 2017-03-01, 2000 --> p201703_all
* 2017-03-10, 1 --> p201703_all
* 2017-04-01, 1000 --> 无法导入
* 2017-05-01, 1000 --> 无法导入

List分区

List分区的分区列支持BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR的数据类型，分区值为枚举值。当数据为目标分区枚举值之一时，才可以命中分区。

分区（Partition）支持通过来指定每个分区包含的枚举值。

单列分区

当使用VALUES IN (...)语句进行分区的增删操作时，分区的变化，示例如下。

1. 创建示例表test_table1。

   CREATE TABLE IF NOT EXISTS test_db.example_list_tbl1
   (
       `user_id` LARGEINT NOT NULL COMMENT "用户id",
       `date` DATE NOT NULL COMMENT "数据灌入日期时间",
       `timestamp` DATETIME NOT NULL COMMENT "数据灌入的时间戳",
       `city` VARCHAR(20) NOT NULL COMMENT "用户所在城市",
       `age` SMALLINT COMMENT "用户年龄",
       `sex` TINYINT COMMENT "用户性别",
       `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
       `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
       `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
       `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
   )
   ENGINE=olap
   AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
   PARTITION BY LIST(`city`)
   (
       PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"),
       PARTITION `p_usa` VALUES IN ("New York", "San Francisco"),
       PARTITION `p_jp` VALUES IN ("Tokyo")
   )
   DISTRIBUTED BY HASH(`user_id`) BUCKETS 16;

   创建示例表test_table1完成后，会自动生成如下3个分区。

   p_cn: ("Beijing", "Shanghai", "Hong Kong")
   p_usa: ("New York", "San Francisco")
   p_jp: ("Tokyo")

2. 增加一个分区`p_uk` VALUES IN ("London")，则分区结果如下。

   p_cn: ("Beijing", "Shanghai", "Hong Kong")
   p_usa: ("New York", "San Francisco")
   p_jp: ("Tokyo")
   p_uk: ("London")

3. 删除分区p_jp，则分区结果如下。

   p_cn: ("Beijing", "Shanghai", "Hong Kong")
   p_usa: ("New York", "San Francisco")
   p_uk: ("London")

多列分区

在创建表分区时，添加如下多列分区设置。示例如下。

PARTITION BY LIST(`id`, `city`)
(
	PARTITION `p1_city` VALUES IN (("1", "Beijing"), ("1", "Shanghai")),
	PARTITION `p2_city` VALUES IN (("2", "Beijing"), ("2", "Shanghai")),
	PARTITION `p3_city` VALUES IN (("3", "Beijing"), ("3", "Shanghai"))
)

我们指定id（INT类型）和city（VARCHAR类型）作为分区列。则分区结果如下。

* p1_city: [("1", "Beijing"), ("1", "Shanghai")]
* p2_city: [("2", "Beijing"), ("2", "Shanghai")]
* p3_city: [("3", "Beijing"), ("3", "Shanghai")]

当插入数据时，分区列值会按照顺序依次比较，最终得到对应的分区。示例如下。

* 数据 ---> 分区
* 1, Beijing ---> p1_city
* 1, Shanghai ---> p1_city
* 2, Shanghai ---> p2_city
* 3, Beijing ---> p3_city
* 1, Tianjin ---> 无法导入
* 4, Beijing ---> 无法导入

分桶

根据分桶列的Hash值，数据将被划分进不同的桶（Bucket）进行存储。

• 如果使用了分区（Partition），则DISTRIBUTED...语句描述的是数据在各个分区内的划分规则，如果不使用分区（Partition），则描述的是对整个表的数据的划分规则。

• 分桶列可以是多列，Aggregate和Unique模型分桶必须为Key列，Duplicate模型可以是Key列和Value列。分桶列可以和Partition列相同或不同。

• 分桶列的选择，是在查询吞吐和查询并发之间的一种权衡：

  • 如果选择多个分桶列，则数据分布更均匀。如果一个查询条件不包含所有分桶列的等值条件，那么该查询会触发所有分桶同时扫描，这样查询的吞吐会增加，单个查询的延迟随之降低。这个方式适合大吞吐低并发的查询场景。

  • 如果选择一个或少数分桶列，则对应的点查询可以仅触发一个分桶扫描。此时，当多个点查询并发时，这些查询有较大的概率分别触发不同的分桶扫描，各个查询之间的IO影响较小（尤其当不同桶分布在不同磁盘上时），所以这种方式适合高并发的点查询场景。

• 分桶的数量理论上没有上限。

最佳实践

分区（Partition）和桶（Bucket）的配置建议

• 一个表的Tablet总数量等于（Partition num * Bucket num）。

• 一个表一个分区的Tablet数量，在不考虑扩容的情况下，推荐略多于整个集群的磁盘数量。

• 单个Tablet的数据量理论上没有上下界，但建议在1~10 GB的范围内。如果单个Tablet数据量过小，则数据的聚合效果不佳，且元数据管理压力大。如果数据量过大，则不利于副本的迁移、补齐，会增加Schema变更或者ROLLUP操作失败重试的代价（这些操作失败重试的粒度是Tablet）。

• 当Tablet的数据量原则和数量原则冲突时，建议优先考虑数据量原则。

• 在建表时，每个分区的Bucket数量统一指定。但是在动态增加分区时（ADD PARTITION），可以单独指定新分区的Bucket数量。可以利用这个功能方便的应对数据缩小或膨胀。

• 一个分区（Partition）的桶（Bucket）数量一旦指定，不可更改。所以在确定桶数量时，需要预先考虑集群扩容的情况。例如当前只有3台HOST，每台HOST有1块盘。如果桶的数量只设置为3或更小，那么后期即使再增加机器，也不能提高并发度。

例如：有10台BE，每台BE一块磁盘的情况下。

Random Distribution的配置及使用

对于不需要聚合更新的明细类数据，可以采用Duplicate数据模型并采用Random Distribution方式，示例如下。

CREATE TABLE IF NOT EXISTS test.example_tbl
(
    `timestamp` DATETIME NOT NULL COMMENT "日志时间",
    `type` INT NOT NULL COMMENT "日志类型",
    `error_code` INT COMMENT "错误码",
    `error_msg` VARCHAR(1024) COMMENT "错误详细信息",
    `op_id` BIGINT COMMENT "负责人id",
    `op_time` DATETIME COMMENT "处理时间"
)
DUPLICATE KEY(`timestamp`, `type`, `error_code`)
DISTRIBUTED BY RANDOM BUCKETS 16;

• 如果Duplicate表没有更新类型的字段，将表的数据分桶模式设置为RANDOM，则可以避免严重的数据倾斜（数据在导入表对应的分区时，单次导入作业的数据将随机选择一个Tablet进行写入）。

• 当表的分桶模式被设置为RANDOM时，因为没有分桶列，无法根据分桶列的值仅对几个分桶查询，对表进行查询的时将对命中分区的全部分桶同时扫描，该设置适合对表数据整体的聚合查询分析而不适合高并发的点查询。

• 如果Duplicate表是Random Distribution的数据分布，那么在数据导入的时候可以设置单分片导入模式（将load_to_single_tablet设置为true，默认为false）。此时在大数据量的导入的时，一个任务在将数据写入对应的分区时只需要写入一个分片。因此可以提高数据导入的并发度和吞吐量，减少数据导入和 Compaction导致的写放大问题，保障集群的稳定性。

分区分桶同时使用的场景

• 有时间维度或带有类似有序值维度的场合，可以将这类维度列作为分区列。分区粒度可以根据导入频次、每个分区的数据量等进行评估。

• 如果有删除历史数据的需求（比如仅保留最近N天的数据），可以使用复合分区，通过删除历史分区来达到目的，也可以通过在指定分区内发送DELETE语句进行数据删除。

• 解决数据倾斜问题，每个分区可以单独指定分桶数量。例如在按天分区的场景下，当每天的数据量差异较大时，可以通过指定分区的分桶数，合理划分不同分区的数据，分桶列建议选择区分度大、数据可以被均匀划分的列。