大数据 - Doris系列《三》- 数据表设计之表的基本概念

🐶3.1 字段类型

🐶3.2 表的基本概念

3.2.1 Row & Column

3.2.2 分区与分桶

🥙3.2.2.1 Partition

1. Range 分区

2. List 分区

进阶：复合分区与单分区的选择

3.2.3 PROPERTIES

🥙3.2.3.1 分片副本数

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

3.2.4小练习：建表指定分区和分桶数

🐶3.1 字段类型

TINYINT	1 字节	范围：-2^7 + 1 ~ 2^7 - 1
SMALLINT	2 字节	范围：-2^15 + 1 ~ 2^15 - 1
INT	4 字节	范围：-2^31 + 1 ~ 2^31 - 1
BIGINT	8 字节	范围：-2^63 + 1 ~ 2^63 - 1
LARGEINT	16 字节	范围：-2^127 + 1 ~ 2^127 - 1
FLOAT	4 字节	支持科学计数法
DOUBLE	12 字节	支持科学计数法
DECIMAL[(precision, scale)]	16 字节	保证精度的小数类型。默认是DECIMAL(10, 0) ，precision: 1 ~ 27 ，scale: 0 ~ 9，其中整数部分为 1 ~ 18，不支持科学计数法
DATE	3 字节	范围：0000-01-01 ~ 9999-12-31
DATETIME	8 字节	范围：0000-01-01 00:00:00 ~ 9999-12-31 23:59:59
CHAR[(length)]		定长字符串。长度范围：1 ~ 255。默认为 1
VARCHAR[(length)]		变长字符串。长度范围：1 ~ 65533
BOOLEAN		与 TINYINT 一样，0 代表 false，1 代表 true
HLL	1~16385 个字节	hll 列类型，不需要指定长度和默认值,长度根据数据的聚合程度系统内控制，并且 HLL 列只能通过配套的hll_union_agg、Hll_cardinality、hll_hash 进行查询或使用
BITMAP		bitmap 列类型，不需要指定长度和默认值。表示整型的集合，元素最大支持到 2^64 - 1
STRING		变长字符串，0.15 版本支持，最大支持 2147483643 字节（2GB-4），长度还受 be 配置`string_type_soft_limit`, 实际能存储的最大长度取两者最小值。只能用在 value 列，不能用在 key列和分区、分桶列

🐶3.2 表的基本概念

3.2.1 Row & Column

一张表包括行（Row）和列（Column）；

Row 即用户的一行数据。Column 用于描述一行数据中不同的字段。

doris中的列分为两类：key列和value列

key列在doris中有两种作用：

聚合表模型中，key是聚合和排序的依据

其他表模型中，key是排序依据

表中所有数据的存储都是有序的，指定的key列的字典顺序去存

3.2.2 分区与分桶

partition（分区）：是在 逻辑上 将一张表按行(横向)划分

分区的逻辑存储在元数据里面的

tablet（又叫bucket，分桶）：在 物理上 对一个分区再按行(横向)划分

分区的基础上进一步划分

存储路径：table/分桶的文件夹

比如按照province分两个分区，按照id分两个桶，此时会在底层生成四个文件夹

🥙3.2.2.1 Partition

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

1. Range 分区

range分区创建语法

1）单列分区

-- Range Partition
drop table if exists test.expamle_range_tb;
CREATE TABLE IF NOT EXISTS test.expamle_range_tb
(
    `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 "用户性别"
)
ENGINE=OLAP
DUPLICATE KEY(`user_id`, `date`) -- 表模型
-- 分区的语法
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 2;

注意点
1. 分区名称要么用``号围起来，要么就单加一个名称

举例：

--id为int类型
PARTITION BY RANGE(`id`) -- 指定分区类型和分区列
(
    -- 指定分区名称，分区的上界   前闭后开
    PARTITION `p100` VALUES LESS THAN (100),   //范围： [int的最小值,100)
    PARTITION `p200` VALUES LESS THAN (200),   //范围： [100,200)
    PARTITION `p300` VALUES LESS THAN (300)    //范围： [200,300)
)

注意：

如果此时插入一条id=300的数据，分区字段里面并没有这个范围，会直接丢掉这条数据，并不会报错。

insert into table values(300)  丢掉
ok, 0行收到影响

insert into table values(200) -->p300
ok.1行收到影响

但在公司中往往需要同时插入多行数据，可能就会漏掉一条而不自知

分区列通常为时间列，以方便的管理新旧数据。
Partition 支持通过 VALUES LESS THAN (...) 仅指定上界，系统会将前一个分区的上界作为该分区的下界，生成一个左闭右开的区间。同时，也支持通过 VALUES [...) 指定上下界，生成一个左闭右开的区间。
通过 VALUES [...) 同时指定上下界比较容易理解。这里举例说明，当使用 VALUES LESS THAN (...) 语句进行分区的增删操作时，分区范围的变化情况：

如上 expamle_range_tbl 得建表语句中可以看到，当建表完成后，会自动生成如下3个分区：

-- 查看表中分区得情况
SHOW PARTITIONS FROM test.expamle_range_tbl \G;

mysql> SHOW PARTITIONS FROM test.expamle_range_tbl \G;
*************************** 1. row ***************************
             PartitionId: 12020
           PartitionName: p201701
          VisibleVersion: 1
      VisibleVersionTime: 2022-08-30 21:57:36
                   State: NORMAL
            PartitionKey: date
                   Range: [types: [DATE]; keys: [0000-01-01]; ..types: [DATE]; keys: [2017-02-01]; )
         DistributionKey: user_id
                 Buckets: 1
          ReplicationNum: 3
           StorageMedium: HDD
            CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
                DataSize: 0.000 
              IsInMemory: false
       ReplicaAllocation: tag.location.default: 3
*************************** 2. row ***************************
             PartitionId: 12021
           PartitionName: p201702
          VisibleVersion: 1
      VisibleVersionTime: 2022-08-30 21:57:36
                   State: NORMAL
            PartitionKey: date
                   Range: [types: [DATE]; keys: [2017-02-01]; ..types: [DATE]; keys: [2017-03-01]; )
         DistributionKey: user_id
                 Buckets: 1
          ReplicationNum: 3
           StorageMedium: HDD
            CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
                DataSize: 0.000 
              IsInMemory: false
       ReplicaAllocation: tag.location.default: 3
*************************** 3. row ***************************
             PartitionId: 12022
           PartitionName: p201703
          VisibleVersion: 1
      VisibleVersionTime: 2022-08-30 21:57:35
                   State: NORMAL
            PartitionKey: date
                   Range: [types: [DATE]; keys: [2017-03-01]; ..types: [DATE]; keys: [2017-04-01]; )
         DistributionKey: user_id
                 Buckets: 1
          ReplicationNum: 3
           StorageMedium: HDD
            CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
                DataSize: 0.000 
              IsInMemory: false
       ReplicaAllocation: tag.location.default: 3
3 rows in set (0.00 sec)

这是他生成得三个分区：

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

当我们增加一个分区 p201705 VALUES LESS THAN ("2017-06-01")，分区结果如下：

ALTER TABLE test.expamle_range_tbl ADD PARTITION p201705 VALUES LESS THAN ("2017-06-01");
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)

此时我们删除分区 p201703，则分区结果如下：

ALTER TABLE test.expamle_range_tbl DROP PARTITION p201703;
p201701: [MIN_VALUE,  2017-02-01)
p201702: [2017-02-01, 2017-03-01)
p201705: [2017-04-01, 2017-06-01)

注意到 p201702 和 p201705 的分区范围并没有发生变化，而这两个分区之间，出现了一个空洞：[2017-03-01, 2017-04-01)。即如果导入的数据范围在这个空洞范围内，是无法导入的。

继续删除分区 p201702，分区结果如下：

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

空洞范围变为：[2017-02-01, 2017-04-01)

现在增加一个分区 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)

综上，分区的删除不会改变已存在分区的范围。删除分区可能出现空洞。通过 VALUES LESS THAN 语句增加分区时，分区的下界紧接上一个分区的上界。

2）多列分区

Range分区除了上述我们看到的单列分区，也支持 多列分区，示例如下：

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")-- 默认采用id类型的最小值
)

在以上示例中，我们指定 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。当用户插入数据时，分区列值会按照顺序依次比较，最终得到对应的分区。举例如下：

2. List 分区

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

List分区创建语法

-- List Partition

CREATE TABLE IF NOT EXISTS test.expamle_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 "用户最小停留时间"
)
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 1
PROPERTIES
(
    "replication_num" = "3"
);

如上 example_list_tbl 示例，当建表完成后，会自动生成如下3个分区：

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

当我们增加一个分区 p_uk VALUES IN ("London")，分区结果如下：

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

当我们删除分区 p_jp，分区结果如下：

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

List分区也支持 多列分区，示例如下

PARTITION BY LIST(`id`, `city`)
(
    PARTITION `p1_city` VALUES IN (("1", "Beijing"), ("2", "Shanghai")),
    PARTITION `p2_city` VALUES IN (("2", "Beijing"), ("1", "Shanghai")),
    PARTITION `p3_city` VALUES IN (("3", "Beijing"), ("4", "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     ---> 无法导入

🥙 3.2.2.2 Bucket

如果使用了 Partition，则 DISTRIBUTED ... 语句描述的是数据在 各个分区内的划分规则。如果不使用 Partition，则描述的是对整个表的数据的划分规则。
分桶列可以是多列，但必须为 Key 列。分桶列可以和 Partition 列相同或不同。
分桶列的选择，是在 查询吞吐 和 查询并发 之间的一种权衡：

多个分桶列，适合高吞吐低并发的场景

单个分桶列，使用高并发点查询场景。
- 如果选择多个分桶列，则数据分布更均匀。如果一个查询条件不包含所有分桶列的等值条件，那么该查询会触发所有分桶同时扫描，这样 查询的吞吐会增加，单个查询的延迟随之降低。这个方式适合高吞吐低并发的查询场景。
- 如果仅选择一个或少数分桶列，则对应的点查询可以仅触发一个分桶扫描。此时，当多个点查询并发时，这些查询有 较大的概率分别触发不同的分桶扫描，各个查询之间的IO影响较小（尤其当不同桶分布在不同磁盘上时），所以这种方式适合高并发的点查询场景。
分桶的数量理论上没有上限

举例来说：

我们拿 省份和城市作为分桶列

1）当查询where province ='江苏‘ and city="南通“时，此时只需要根据江苏和南通的hashcode找到对应的分桶编号。（查询很快）

2）当查询where province ='江苏‘时，此时需要全表扫描。（查询会慢一些,但当你一个人查询时，所有机器都为你服务，读写数据的吞吐量会增加，因此适合于高吞吐低并发的场景）

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

一个表的 Tablet 总数量等于 (Partition num * Bucket num)。
分桶字段尽量选择基数大的字段。如只选择性别作为分桶列，只会落入两个桶中。
一个表的 Tablet 数量，在不考虑扩容的情况下，推荐略多于整个集群的磁盘数量。
单个 Tablet 的数据量理论上没有上下界，但建议在 1G - 10G 的范围内。如果单个 Tablet 数据量过小，则数据的聚合效果不佳，且元数据管理压力大。如果数据量过大，则不利于副本的迁移、补齐，且会增加 Schema Change 或者 Rollup 操作失败重试的代价（这些操作失败重试的粒度是 Tablet）。分桶应该控制桶内数据量，不易过大或者过小
当 Tablet 的数据量原则和数量原则冲突时，建议 优先考虑数据量原则 。
在建表时，每个分区的 Bucket 数量统一指定。但是在动态增加分区时（ADD PARTITION），可以单独指定新分区的 Bucket 数量。可以利用这个功能方便的应对数据缩小或膨胀。
一个 Partition 的 Bucket 数量一旦指定，不可更改。所以在确定 Bucket 数量时，需要预先考虑集群扩容的情况。比如当前只有 3 台 host，每台 host 有 1 块盘。如果 Bucket 的数量只设置为 3 或更小，那么后期即使再增加机器，也不能提高并发度。

小例子：

假设在有10台BE，每台BE一块磁盘的情况下。

如果一个表总大小为 500MB，则可以考虑4-8个分片。 5个

5GB：8-16个分片。

50GB：32个分片。

500GB：建议分区，每个分区大小在 50GB 左右，每个分区16-32个分片。

5TB：建议分区，每个分区大小在 500GB 左右，每个分区16-32个分片。

注：表的数据量可以通过 SHOW DATA命令查看，结果除以副本数，即表的数据量。

进阶：复合分区与单分区的选择

复合分区

第一级称为 Partition，即分区。用户可以指定某一维度列作为分区列（当前只支持整型和时间类型的列），并指定每个分区的取值范围。

第二级称为 Distribution，即分桶。用户可以指定一个或多个维度列以及桶数对数据进行 HASH 分布。

以下场景推荐使用复合分区

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

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

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

用户也可以不使用复合分区，即使用单分区。则数据只做 HASH 分布。

3.2.3 PROPERTIES

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

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

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

🥙3.2.3.1 分片副本数

replication_num

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

副本数量可以在运行时修改。强烈建议保持奇数。

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

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

storage_medium
storage_cooldown_time datetime

建表时，可以统一指定所有 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 时间后，数据才会迁移。

注意:

当指定 storage_medium 时，如果 FE 参数 enable_strict_storage_medium_check 为False 该参数只是一个“尽力而为”的设置。即使集群内没有设置 SSD 存储介质，也不会报错，而是自动存储在可用的数据目录中。

同样，如果 SSD 介质不可访问、空间不足，都可能导致数据初始直接存储在其他可用介质上。而数据到期迁移到 HDD 时，如果 HDD 介质不可访问、空间不足，也可能迁移失败（但是会不断尝试）。

如果 FE 参数enable_strict_storage_medium_check 为 True 则当集群内没有设置 SSD 存储介质时，会报错Failed to find enough host in all backends with storage medium is SSD。

3.2.4小练习：建表指定分区和分桶数

create table student(
    uid int,
    name varchar(10),
    birthday date,
    age int,
    province varchar(10)
)engine =olap
duplicate key(uid,name,birthday)
partition by range(birthday)(
    partition  `p202209` values less than ("2022-10-01"),
    partition `p202210` values less than ("2022-11-01"),
    partition `p202211` values less than ("2022-12-01"),
    partition `p202212` values less than ("2023-01-01")
)
distributed by hash(uid) buckets 4
properties(
    "replication_num"="2",
    "storage_medium"="SSD",
    "storage_cooldown_time"="2024-01-25 05:00:00"
);