Doris：数据库建表最佳实践

一、表模型推荐归约

二、字段推荐归约

三、建表推荐归约

四、建表强制归约

五、最佳实践

Doris 数据表模型上目前分为三类：DUPLICATE KEY, UNIQUE KEY, AGGREGATE KEY。因为数据模型在建表时就已经确定，且无法修改。所以，选择一个合适的数据模型非常重要。

建表时除了要注意数据表模型、索引和字段类型的选择还需要注意分区分桶的设置。

一、表模型推荐归约

1. Duplicate： 适合任意维度的 Ad-hoc 查询。虽然同样无法利用预聚合的特性，但是不受聚合模型的约束，可以发挥列存模型的优势（只读取相关列，而不需要读取所有 Key 列）。

2. Aggregate： 模型可以通过预聚合，极大地降低聚合查询时所需扫描的数据量和查询的计算量，非常适合有固定模式的报表类查询场景。但是该模型对 count(*) 查询很不友好。同时因为固定了 Value 列上的聚合方式，在进行其他类型的聚合查询时，需要考虑语意正确性。

3. Unique： 模型针对需要唯一主键约束的场景，可以保证主键唯一性约束。但是无法利用物化等预聚合带来的查询优势。对于聚合查询有较高性能需求的用户，推荐使用自 1.2 版本加入的写时合并实现。

4.如果有部分列更新的需求，可以选择：
a. Unique 模型的 Merge-on-Write 模式
b. Aggregate 模型的 REPLACE_IF_NOT_NULL 聚合方式

二、字段推荐归约

1. VARCHAR

a. 变长字符串，长度范围为：1-65533 字节长度，以 UTF-8 编码存储的，因此通常英文字符占 1 个字节，中文字符占 3 个字节。
b. 这里存在一个误区，即 varchar(255) 和 varchar(65533) 的性能问题，这二者如果存的数据是一样的，性能也是一样的，建表时如果不确定这个字段最大有多长，建议直接使用 65533 即可，防止由于字符串过长导致的导入问题。

2. STRING

        a. 变长字符串，默认支持 1048576 字节（1MB），可调大到 2147483643 字节（2G），以 UTF-8 编码存储的，因此通常英文字符占 1 个字节，中文字符占 3 个字节。
        b. 只能用在 Value 列，不能用在 Key 列和分区分桶列。
        c. 适用于一些比较大的文本存储，一般如果没有这种需求的话，建议使用 VARCHAR，STRING 列无法用在 Key 列和分桶列，局限性比较大。

3. 数值型字段：按照精度选择对应的数据类型即可，没有过于特殊的注意。

4. 时间字段：这里需要注意的是，如果有高精度（毫秒值时间戳）需求，需要指明使用 datetime(6)，否则默认是不支持毫秒值时间戳的。

5. 建议使用 JSON 数据类型代替字符串类型存放 JSON 数据的使用方式。

三、建表推荐归约

1. 库名统一使用小写方式，中间用下划线（_）分割，长度 62 字节内。

2. 表名称大小写敏感，统一使用小写方式，中间用下划线（_）分割，长度 64 字节内。

3. 能手动分桶，尽量不要使用 Auto Bucket，按照自己的数据量来进行分区分桶，这样你的导入及查询性能都会得到很好的效果，Auto Bucket 会造成 tablet 数量过多，造成大量小文件的问题。

4. 1000W-2 亿以内数据为了方便可以不设置分区，直接用分桶策略（不设置其实 Doris 内部会有个默认分区）。

5. 如果是时序场景，建议在建表时 "compaction_policy" = "time_series" 加上这个表属性配置，在时序场景持续导入的情况下有效降低 compact 的写入放大率，注意需要配合倒排一起用。

四、建表强制归约

1. 数据库字符集指定 UTF-8，并且只支持 UTF-8。

2. 表的副本数必须为 3（未指定副本数时，默认为 3）。

3. 单个 Tablet（Tablet 数 = 分区数 * 桶数 * 副本数）的数据量理论上没有上下界，除小表（百兆维表）外需确保在 1G - 10G 的范围内：

a. 如果单个 Tablet 数据量过小，则数据的聚合效果不佳，且元数据管理压力大。
b. 如果数据量过大，则不利于副本的迁移、补齐，且会增加 Schema Change 或者物化操作失败重试的代价（这些操作失败重试的粒度是 Tablet）。

4. 5 亿以上的数据必须设置分区分桶策略：

a. bucket 设置建议：

i. 大表的单个 Tablet 存储数据大小在 1G-10G 区间，可防止过多的小文件产生。

ii. 百兆左右的维表 Tablet 数量控制在 3-5 个，保证一定的并发数也不会产生过多的小文件。

b. 没有办法分区的，数据又较快增长的，没办法按照时间动态分区，可以适当放大一下你的 Bucket 数量，按照你的数据保存周期（180 天）数据总量，来估算你的 Bucket 数量应该是多少，建议还是单个 Bucket 大小在 1-10G。

c. 对分桶字段进行加盐处理，业务上查询的时候也是要同样的加盐策略，这样能利用到分桶数据剪裁能力。

d. 数据随机分桶：

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

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

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

e. 维度表：缓慢增长的，可以使用单分区，在分桶策略上使用常用查询条件（这个字段数据分布相对均衡）分桶。

f. 事实表

5. 如果分桶字段存在 30% 以上的数据倾斜，则禁止使用 Hash 分桶策略，改使用 RANDOM 分桶策略。

6. 2KW 以内数据禁止使用动态分区（动态分区会自动创建分区，而小表用户客户关注不到，会创建出大量不使用分区分桶）。

7. 对于有大量历史分区数据，但是历史数据比较少，或者不均衡，或者查询概率的情况，使用如下方式将数据放在特殊分区。

对于历史数据，如果数据量比较小我们可以创建历史分区（比如年分区，月分区），将所有历史数据放到对应分区里创建历史分区方式例如：FROM ("2000-01-01") TO ("2022-01-01") INTERVAL 1 YEAR，具体参考：

(
    PARTITION p00010101_1899 VALUES [('0001-01-01'), ('1900-01-01')),
    PARTITION p19000101 VALUES [('1900-01-01'), ('1900-01-02')),
    ...
    PARTITION p19000104_1999 VALUES [('1900-01-04'), ('2000-01-01')),
    FROM ("2000-01-01") TO ("2022-01-01") INTERVAL 1 YEAR,
    PARTITION p30001231 VALUES [('3000-12-31'), ('3001-01-01')),
    PARTITION p99991231 VALUES [('9999-12-31'), (MAXVALUE))
)

8.单表物化视图不能超过 6 个

a. 单表物化视图是实时构建

b. 在 Unqiue 模型上物化视图只能起到 Key 重新排序的作用，不能做数据的聚合，因为 Unqiue 模型的聚合模型是 Replace

五、最佳实践

-- 以 Unique 模型的 Merge-on-Write 表为例
-- Unique 模型的写时合并实现，与聚合模型就是完全不同的两种模型了，查询性能更接近于 duplicate 模型，
-- 在有主键约束需求的场景上相比聚合模型有较大的查询性能优势，尤其是在聚合查询以及需要用索引过滤大量数据的查询中。-- 非分区表
CREATE TABLE IF NOT EXISTS tbl_unique_merge_on_write
(`user_id` LARGEINT NOT NULL COMMENT "用户id",`username` VARCHAR(50) NOT NULL COMMENT "用户昵称",`register_time` DATE COMMENT "用户注册时间",`city` VARCHAR(20) COMMENT "用户所在城市",`age` SMALLINT COMMENT "用户年龄",`sex` TINYINT COMMENT "用户性别",`phone` LARGEINT COMMENT "用户电话",`address` VARCHAR(500) COMMENT "用户地址"
)
UNIQUE KEY(`user_id`, `username`)
-- 3-5G 的数据量
DISTRIBUTED BY HASH(`user_id`) BUCKETS 10 
PROPERTIES (
-- 在 1.2.0 版本中，作为一个新的 feature，写时合并默认关闭，用户可以通过添加下面的 property 来开启
"enable_unique_key_merge_on_write" = "true" 
);-- 分区表
CREATE TABLE IF NOT EXISTS tbl_unique_merge_on_write_p
(`user_id` LARGEINT NOT NULL COMMENT "用户id",`username` VARCHAR(50) NOT NULL COMMENT "用户昵称",`register_time` DATE COMMENT "用户注册时间",`city` VARCHAR(20) COMMENT "用户所在城市",`age` SMALLINT COMMENT "用户年龄",`sex` TINYINT COMMENT "用户性别",`phone` LARGEINT COMMENT "用户电话",`address` VARCHAR(500) COMMENT "用户地址"
)
UNIQUE KEY(`user_id`, `username`, `register_time`)
PARTITION BY RANGE(`register_time`) (PARTITION p00010101_1899 VALUES [('0001-01-01'), ('1900-01-01')), PARTITION p19000101 VALUES [('1900-01-01'), ('1900-01-02')), PARTITION p19000102 VALUES [('1900-01-02'), ('1900-01-03')),PARTITION p19000103 VALUES [('1900-01-03'), ('1900-01-04')),PARTITION p19000104_1999 VALUES [('1900-01-04'), ('2000-01-01')),FROM ("2000-01-01") TO ("2022-01-01") INTERVAL 1 YEAR, PARTITION p30001231 VALUES [('3000-12-31'), ('3001-01-01')), PARTITION p99991231 VALUES [('9999-12-31'), (MAXVALUE)) 
) 
-- 默认 3-5G 的数据量
DISTRIBUTED BY HASH(`user_id`) BUCKETS 10 
PROPERTIES ( 
-- 在 1.2.0 版本中，作为一个新的 feature，写时合并默认关闭，用户可以通过添加下面的 property 来开启
"enable_unique_key_merge_on_write" = "true", 
-- 动态分区调度的单位。可指定为 HOUR、DAY、WEEK、MONTH、YEAR。分别表示按小时、按天、按星期、按月、按年进行分区创建或删除。
"dynamic_partition.time_unit" = "MONTH",
-- 动态分区的起始偏移，为负数。根据 time_unit 属性的不同，以当天（星期/月）为基准，分区范围在此偏移之前的分区将会被删除（TTL）。如果不填写，则默认为 -2147483648，即不删除历史分区。
"dynamic_partition.start" = "-3000",
-- 动态分区的结束偏移，为正数。根据 time_unit 属性的不同，以当天（星期/月）为基准，提前创建对应范围的分区。
"dynamic_partition.end" = "10",
-- 动态创建的分区名前缀（必选）。
"dynamic_partition.prefix" = "p",
-- 动态创建的分区所对应的分桶数量。
"dynamic_partition.buckets" = "10", 
"dynamic_partition.enable" = "true", 
-- 动态创建的分区所对应的副本数量，如果不填写，则默认为该表创建时指定的副本数量 3。
"dynamic_partition.replication_num" = "3",
"replication_num" = "3"
);  -- 分区创建查看
-- 实际创建的分区数需要结合 dynamic_partition.start、end 以及 PARTITION BY RANGE 的设置共同决定
show partitions from tbl_unique_merge_on_write_p;