Doris 分区与桶（学习笔记）

• Doris 分区与桶（学习笔记）
  • OLTP和OLAP
    • OLTP
    • OLAP
    • OLAP和OLTP比较
  • Doris的默认端口
  • 数据表设计
    • 字段类型
    • 表的基本概念
      • Row & Column
      • 分区与分桶
      • Partition
      • Range 分区
      • List 分区
      • 添加分区
      • 删除分区
    • Bucket桶
      • 关于 Partition 和 Bucket的数量和数据量的建议。
    • 复合分区与单分区的选择
      • 复合分区
      • 复合分区推荐场景
    • PROPERTIES
      • 分片副本数
      • 存储介质 和 热数据冷却时间

OLTP和OLAP

OLTP

联机事务处理OLTP(On-Line Transaction Processing) 公司针对自己公司的业务构建出来的系统化
○ 公司业务系统使用数据库的场景，针对业务系统数据库有大量随机的增删改查
○ 高并发
○ 速度要快
○ 支持事务

OLAP

联机分析处理OLAP(On-Line Analytical Processing)
○ 公司的数据分析使用数据库的场景，对已经生成好的数据进行统计分析
○ 一次操作都是针对的整个数据集
○ 只有查这个动作，不会去增删改
○ 查询的响应速度相对慢点也能接受
○ 并发量要求不是太高

OLAP和OLTP比较

Doris的默认端口

数据表设计

字段类型

表的基本概念

Row & Column

一张表包括行（Row）和列（Column）；
Row 即用户的一行数据。Column 用于描述一行数据中不同的字段。
doris中的列分为两类：key列和value列
key列在doris中有两种作用：
聚合表模型中，key是聚合和排序的依据
其他表模型中，key是排序依据

分区与分桶

partition（分区）：是在逻辑上将一张表按行(横向)划分
tablet（又叫bucket，分桶）：在物理上对一个分区再按行(横向)划分

Partition

Partition 列可以指定一列或多列，在聚合模型中，分区列必须为 KEY 列。
不论分区列是什么类型，在写分区值时，都需要加双引号。
分区数量理论上没有上限。
当不使用 Partition 建表时，系统会自动生成一个和表名同名的，全值范围的 Partition。该 Partition 对用户不可见，并且不可删改。
创建分区时不可添加范围重叠的分区。

Range 分区

CREATE TABLE IF NOT EXISTS test.example_range_tbl_CC -- 创建表，如果表不存在则创建
(
    `user_id` LARGEINT NOT NULL COMMENT "用户id", -- 定义用户ID列，数据类型为大整数，不能为空，并添加注释说明
    `date` DATE NOT NULL COMMENT "数据灌入日期时间", -- 定义日期列，数据类型为日期，不能为空，并添加注释说明
    `timestamp` DATETIME NOT NULL COMMENT "数据灌入的时间戳", -- 定义时间戳列，数据类型为日期时间，不能为空，并添加注释说明
    `city` VARCHAR(20) COMMENT "用户所在城市", -- 定义城市列，数据类型为可变长度字符串，最大长度为20，并添加注释说明
    `age` SMALLINT COMMENT "用户年龄", -- 定义年龄列，数据类型为小整数，并添加注释说明
    `sex` TINYINT COMMENT "用户性别" -- 定义性别列，数据类型为极小整数，并添加注释说明
)
ENGINE=OLAP -- 指定存储引擎为OLAP
DUPLICATE KEY(`user_id`, `date`) -- 表模型，定义主键为user_id和date的组合
-- 分区的语法，以下为分区定义部分
PARTITION BY RANGE(`date`) -- 指定分区类型为范围分区，分区列是date
(
    PARTITION `p20170101` VALUES LESS THAN ("2017-01-02"), -- 创建分区p20170101，包含date小于2017-01-02的数据
    PARTITION `p20170102` VALUES LESS THAN ("2017-01-03"), -- 创建分区p20170102，包含date小于2017-01-03的数据
    PARTITION `p20170103` VALUES LESS THAN ("2017-01-04"), -- 创建分区p20170103，包含date小于2017-01-04的数据
    PARTITION `p20170104` VALUES LESS THAN ("2017-01-05") -- 创建分区p20170104，包含date小于2017-01-05的数据
)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 2 -- 指定数据分布方式为哈希分布，根据user_id进行哈希分片，分片数为2
PROPERTIES -- 表属性
(
    "replication_num" = "1" -- 设置表的副本数量为1
);

执行后我们发现我们的表创建好了。

可通过下面的sql语句查看表结构。

-- 查看表结构
desc example_range_tbl_CC;

这里是按照时间范围做的分区。

通过下面的命令可以查看表中分区得情况

SHOW PARTITIONS FROM `example_range_tbl_CC`

这是他生成得四个分区

我们也可以通过下面的命令进行添加一个分区

ALTER TABLE `example_range_tbl_CC` ADD PARTITION `p20170105` VALUES LESS THAN ("2017-01-06");

当然我们也可以删除这个分区：

ALTER TABLE `example_range_tbl_CC` drop PARTITION `p20170105`;

List 分区

● 分区列支持 BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME, CHAR, VARCHAR 数据类型，分区值为枚举值。只有当数据为目标分区枚举值其中之一时，才可以命中分区。
● Partition 支持通过 VALUES IN (…) 来指定每个分区包含的枚举值。
● 下面通过示例说明，进行分区的增删操作时，分区的变化。
List分区创建语法:

-- List Partition
CREATE TABLE IF NOT EXISTS test.example_list_tbl
(
    `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 NOT NULL COMMENT "用户年龄",
    `sex` TINYINT NOT NULL 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 "用户最小停留时间"
)
-- 设置表的引擎为 OLAP（联机分析处理引擎），这是一种适合分析型业务的存储引擎
ENGINE=olap
-- 指定聚合主键（AGGREGATE KEY），其中 user_id、date、timestamp、city、age 和 sex 是聚合主键字段
-- 聚合主键用于定义主键字段，其他字段可以参与聚合计算
AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
-- 使用 LIST 分区，按照 city 字段进行分区
-- 列表分区可以为不同的城市值创建不同的分区
PARTITION BY LIST(`city`)
(
    -- 创建名为 p_cn 的分区，包含 北京、上海 和 香港 城市数据
    PARTITION `p_cn` VALUES IN ("Beijing", "Shanghai", "Hong Kong"),
    -- 创建名为 p_usa 的分区，包含 纽约 和 San Francisco 城市数据
    PARTITION `p_usa` VALUES IN ("New York", "San Francisco"),
    -- 创建名为 p_jp 的分区，包含 Tokyo 城市数据
    PARTITION `p_jp` VALUES IN ("Tokyo")
)
-- 指定分桶的语法，使用 HASH 分桶策略，按 user_id 字段进行哈希分桶，并创建 1 个桶
-- 分桶用于数据分布，提高查询性能
-- 指定分桶的语法
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
PROPERTIES
(
    "replication_num" = "1"
);

last_visit_date这里记录用户最后一次访问时间，使用 DATETIME 类型，并设置为 REPLACE 类型以自动更新为最新值。
cost 用户总消费，使用 BIGINT 类型，并设置为 SUM 类型以便累加值，默认值是 0，并添加注释说明用户总消费的含义。
max_dwell_time用户最大停留时间，使用 INT 类型，并设置为 MAX 类型以便存储最大值，默认值是 0，并添加注释说明用户最大停留时间的含义。
min_dwell_time用户最小停留时间，使用 INT 类型，并设置为 MIN 类型以便存储最小值,默认值是 99999，表示初始值，并添加注释说明用户最小停留时间的含义.
如上 example_list_tbl 示例，当建表完成后，会自动生成如下3个分区：

SHOW PARTITIONS FROM `example_list_tbl`

我们添加一下数据试试看。

INSERT INTO test.example_list_tbl
VALUES
(
    1001, '2024-10-10', '2024-10-10 12:00:00', 'Beijing', 25, 1, '2024-10-09 15:30:00', 500, 300, 10
),
(
    1002, '2024-10-11', '2024-10-11 13:30:00', 'Shanghai', 30, 2, '2024-10-10 10:45:00', 1200, 450, 20
);

SELECT * from test.example_list_tbl

然后我们添加一下没有分区的重庆的数据，查看是否能添加成功。

INSERT INTO test.example_list_tbl
VALUES
(
    2001,'2024-10-15','2024-10-15 14:00:00','Chongqing',32,2,'2024-10-14 18:00:00',800,400,15
);

发现执行时报错，报错提供了一个链接我们点开看看。

发现它找不到相关的分区，所以无法进行添加。

添加分区

添加一个新分区，支持 Chongqing 城市

ALTER TABLE test.example_list_tbl ADD PARTITION `p_cq` VALUES IN ("Chongqing");

添加好后我们再次进行添加重庆的数据就可以进行添加了。

我们再添加同样的北京的数据，只修改聚合字段，会发现它并没有进行新增加一行，只是简单做了一下修改以及聚合。

INSERT INTO test.example_list_tbl
VALUES
(
    1001, '2024-10-10', '2024-10-10 12:00:00', 'Beijing', 25, 1, '2024-10-09 15:30:00', 500, 300, 10
);

删除分区

下面是删除聚合的语句，当我们删除之后关于重庆的数据也同样被删除了。

ALTER TABLE test.example_list_tbl DROP PARTITION p_cq;

Bucket桶

如果使用了 Partition，则 DISTRIBUTED … 语句描述的是数据在各个分区内的划分规则。如果不使用 Partition，则描述的是对整个表的数据的划分规则。
分桶列可以是多列，但必须为Key 列。分桶列可以和 Partition 列相同或不同。
● 分桶列的选择，是在查询吞吐 和 查询并发 之间的一种权衡：
○ 如果选择多个分桶列，则数据分布更均匀。如果一个查询条件不包含所有分桶列的等值条件，那么该查询会触发所有分桶同时扫描，这样查询的吞吐会增加，单个查询的延迟随之降低。这个方式适合大吞吐低并发的查询场景。
○ 如果仅选择一个或少数分桶列，则对应的点查询可以仅触发一个分桶扫描。此时，当多个点查询并发时，这些查询有较大的概率分别触发不同的分桶扫描，各个查询之间的IO影响较小（尤其当不同桶分布在不同磁盘上时），所以这种方式适合高并发的点查询场景。
分桶的数量理论上没有上限

关于 Partition 和 Bucket的数量和数据量的建议。

1.一个表的 Tablet 总数量等于 (Partition num * Bucket num)也就是分区乘以分桶。
2.一个表的 Tablet 数量，在不考虑扩容的情况下，推荐略多于整个集群的磁盘数量。
3.单个 Tablet 的数据量理论上没有上下界，但建议在 1G - 10G 的范围内。如果单个 Tablet 数据量过小，则数据的聚合效果不佳，且元数据管理压力大。如果数据量过大，则不利于副本的迁移、补齐，且会增加 Schema Change 或者 Rollup 操作失败重试的代价（这些操作失败重试的粒度是 Tablet）。分桶应该控制桶内数据量 ，不易过大或者过小
4.当 Tablet 的数据量原则和数量原则冲突时，建议优先考虑数据量原则。

5.在建表时，每个分区的 Bucket 数量统一指定。
但是在动态增加分区时（ADD PARTITION），可以单独指定新分区的 Bucket 数量。可以利用这个功能方便的应对数据缩小或膨胀。
6.一个 Partition 的 Bucket 数量一旦指定，不可更改。
所以在确定 Bucket 数量时，需要预先考虑集群扩容的情况。
比如当前只有 3 台 host，每台 host 有 1 块盘。如果 Bucket 的数量只设置为 3 或更小，那么后期即使再增加机器，也不能提高并发度。

复合分区与单分区的选择

复合分区

● 第一级称为 Partition，即分区。用户可以指定某一维度列作为分区列（当前只支持整型和时间类型的列），并指定每个分区的取值范围。
● 第二级称为 Distribution，即分桶。用户可以指定一个或多个维度列以及桶数对数据进行 HASH 分布。

复合分区推荐场景

1.有时间维度或类似带有有序值的维度，可以以这类维度列作为分区列。分区粒度可以根据导入频次、分区数据量等进行评估。地域、时间

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

3.改善数据倾斜问题：每个分区可以单独指定分桶数量。如按天分区，当每天的数据量差异很大时，可以通过指定分区的分桶数，合理划分不同分区的数据,分桶列建议选择区分度大的列。

PROPERTIES

在建表语句的最后，可以用 PROPERTIES 关键字来设置一些表的属性参数（参数有很多）

PROPERTIES(
  "参数名" = "参数值"
)

下文挑选了3个比较重要的参数进行示例:

分片副本数

● replication_num

每个 Tablet 的副本数量。默认为 3，建议保持默认即可。在建表语句中，所有 Partition中的 Tablet 副本数量统一指定。而在增加新分区时，可以单独指定新分区中 Tablet 的副本数量。

副本数量可以在运行时修改。强烈建议保持奇数。
最大副本数量取决于集群中独立 IP 的数量（注意不是 BE 数量）

Doris 中副本分布的原则是，不允许同一个 Tablet 的副本分布在同一台物理机上，而识别物理机即通过 IP。所以，即使在同一台物理机上部署了 3 个或更多 BE 实例，如果这些 BE 的 IP 相同，则依然只能设置副本数为 1。对于一些小，并且更新不频繁的维度表，可以考虑设置更多的副本数。这样在 Join 查询时，可以有更大的概率进行本地数据 Join。

存储介质 和 热数据冷却时间

● storage_medium
● storage_cooldown_time

建表时，可以统一指定所有 Partition 初始存储的介质及热数据的冷却时间，如：

"storage_medium" = "SSD" 
"storage_cooldown_time" = "2023-04-20 00:00:00" 要在当前时间之后，并且是一个datetime类型

默认初始存储介质可通过 fe 的配置文件 fe.conf 中指定 default_storage_medium=xxx,如果没有指定，则默认为 HDD。如果指定为 SSD，则数据初始存放在 SSD 上。没设storage_cooldown_time，则默认 30 天后，数据会从 SSD自动迁移到 HDD上。如果指定了 storage_cooldown_time，则在到达 storage_cooldown_time 时间后，数据才会迁移。