DorisDB系列文档(五) 表设计详解

1 数据存储基本原理


由此可见, 查找维度列的前缀的查找过程为: 先查找shortkey index, 获得逻辑块的起始行号, 查找维度列的行号索引, 获得目标列的数据块, 读取数据块, 然后解压解码, 从数据块中找到维度列前缀对应的数据项.

加速数据处理


列式存储

DorisDB的表和关系型数据相同, 由行和列构成. 每行数据对应用户一条记录, 每列数据有相同数据类型. 所有数据行的列数相同, 可以动态增删列. DorisDB中, 一张表的列可以分为维度列(也成为key列)和指标列(value列), 维度列用于分组和排序, 指标列可通过聚合函数SUM, COUNT, MIN, MAX, REPLACE, HLL_UNION, BITMAP_UNION等累加起来. 因此, DorisDB的表也可以认为是多维的key到多维指标的映射.


在DorisDB中, 表中数据按列存储, 物理上, 一列数据会经过分块编码压缩等操作, 然后持久化于非易失设备, 但在逻辑上, 一列数据可以看成由相同类型的元素构成的数组. 一行数据的所有列在各自的列数组中保持对齐, 即拥有相同的数组下标, 该下标称之为序号或者行号. 该序号是隐式, 不需要存储的, 表中的所有行按照维度列, 做多重排序, 排序后的位置就是该行的行号.


查询时, 如果指定了维度列的等值条件或者范围条件, 并且这些条件中维度列可构成表维度列的前缀, 则可以利用数据的有序性, 使用range-scan快速锁定目标行. 例如: 对于表table1: (event_day, siteid, citycode, username)➜(pv); 当查询条件为event_day > 2020-09-18 and siteid = 2, 则可以使用范围查找; 如果指定条件为citycode = 4 and username in ["Andy", "Boby", "Christian", "DorisDB"], 则无法使用范围查找.


稀疏索引
当范围查找时, 如何快速地找到起始的目标行呢? 答案是shortkey index. 如下图所示: shortkey索引为稀疏索引,

表中数据组织有主要由三部分构成:

shortkey index表: 表中数据每1024行, 构成一个逻辑block. 每个逻辑block在shortkey index表中存储一项索引, 内容为表的维度列的前缀, 并且不超过36字节. shortkey index为稀疏索引, 用数据行的维度列的前缀查找索引表, 可以确定该行数据所在逻辑块的起始行号.
Per-column data block: 表中每一列数据按64KB分块存储, 数据块作为一个单位单独编码压缩, 也作为IO单位, 整体写回设备或者读出.
Per-column cardinal index: 表中的每列数据有各自的行号索引表, 列的数据块和行号索引项一一对应, 索引项由数据块的起始行号和数据块的位置和长度信息构成, 用数据行的行号查找行号索引表, 可以获取包含该行号的数据块所在位置, 读取目标数据块后, 可以进一步查找数据.
预先聚合: DorisDB支持聚合模型, 维度列取值相同数据行可合并一行, 合并后数据行的维度列取值不变, 指标列的取值为这些数据行的聚合结果, 用户需要给指标列指定聚合函数. 通过预先聚合, 可以加速聚合操作.
分区分桶: 事实上DorisDB的表被划分成tablet, 每个tablet多副本冗余存储在BE上, BE和tablet的数量可以根据计算资源和数据规模而弹性伸缩. 查询时, 多台BE可并行地查找tablet快速获取数据. 此外, tablet的副本可复制和迁移, 增强了数据的可靠性, 避免了数据倾斜. 总之, 分区分桶保证了数据访问的高效性和稳定性.
RollUp表索引: shortkey index可加速数据查找, 然后shortkey index依赖维度列排列次序. 如果使用非前缀的维度列构造查找谓词, 则无法使用shortkey index. 用户可以为数据表创建若干RollUp表索引, RollUp表索引的数据组织和存储和数据表相同, 但RollUp表拥有自身的shortkey index. 用户创建RollUp表索引时, 可选择聚合的粒度, 列的数量, 维度列的次序; 使频繁使用的查询条件能够命中相应的RollUp表索引.
列级别的索引技术: Bloomfilter可快速判断数据块中不含所查找值, ZoneMap通过数据范围快速过滤待查找值, Bitmap索引可快速计算出枚举类型的列满足一定条件的行.
2 表模型介绍
目前，DorisDB根据摄入数据和实际存储数据之间的映射关系, 将数据表的明细表, 聚合表和更新表, 分别对应有明细模型, 聚合模型和更新模型。为了描述方便, 我们借鉴关系模式中的主键概念, 称DorisDB表的维度列的取值构成数据表的排序键, DorisDB的排序键对比传统的主键具有:

数据表所有维度列构成排序键, 所以后文中提及的排序列, key列本质上都是维度列.
排序键可重复, 不必满足性约束.
数据表的每一列, 以排序键的顺序, 聚簇存储.
排序键使用稀疏索引.
对于摄入(ingest)的主键重复的多行数据, 填充于(populate)数据表中时, 按照三种处理方式划分:

明细模型: 表中存在主键重复的数据行, 和摄入数据行一一对应, 用户可以召回所摄入的全部历史数据.
聚合模型: 表中不存在主键重复的数据行, 摄入的主键重复的数据行合并为一行, 这些数据行的指标列通过聚合函数合并, 用户可以召回所摄入的全部历史数据的累积结果, 但无法召回全部历史数据.
更新模型: 聚合模型的特殊情形, 主键满足性约束, 近摄入的数据行, 替换掉其他主键重复的数据行. 相当于在聚合模型中, 为数据表的指标列指定的聚合函数为REPLACE, REPLACE函数返回一组数据中的新数据.
需要注意:

建表语句, 排序列的定义必须出现在指标列定义之前.
排序列在建表语句中的出现次序为数据行的多重排序的次序.
排序键的稀疏索引(shortkey index)会选择排序键的若干前缀列.
2.1 明细模型
1. 适用场景

DorisDB建表的默认模型是明细模型。



一般用明细模型来处理的场景有如下特点：

需要保留原始的数据（例如原始日志，原始操作记录等）来进行分析；
查询方式灵活, 不局限于预先定义的分析方式, 传统的预聚合方式难以命中;
数据更新不频繁。导入数据的来源一般为日志数据或者是时序数据, 以追加写为主要特点, 数据产生后就不会发生太多变化。
2. 模型原理

用户可以指定数据表的排序列, 没有明确指定的情况下, 那么DorisDB会为表选择默认的几个列作为排序列。这样，在查询中，有相关排序列的过滤条件时，DorisDB能够快速地过滤数据，降低整个查询的时延。

注意：在向DorisDB明细模型表中导入完全相同的两行数据时，DorisDB会认为是两行数据。

3. 如何使用

数据表默认采用明细模型. 排序列使用shortkey index, 可快速过滤数据. 用户可以考虑将过滤条件中频繁使用的维度列的定义放置其他列的定义之前. 例如用户经常查看某时间范围的某一类事件的数据，可以将事件时间和事件类型作为排序键。



以下是一个使用明细模型创建数据表的例子

其中DUPLICATE KEY(event_time, event_type)说明采用明细模型, 并且指定了排序键, 并且排序列的定义在其他列定义之前.
CREATE TABLE IF NOT EXISTS detail (
event_time DATETIME NOT NULL COMMENT "datetime of event",
event_type INT NOT NULL COMMENT "type of event",
user_id INT COMMENT "id of user"
device_code INT COMMENT "device of ",
channel INT COMMENT ""
)
DUPLICATE KEY(event_time, event_type)
DISTRIBUTED BY HASH(user_id) BUCKETS 8
4. 注意事项

充分利用排序列，在建表时将经常在查询中用于过滤的列放在表的前面，这样能够提升查询速度。
明细模型中, 可以指定部分的维度列为排序键; 而聚合模型和更新模型中, 排序键只能是全体维度列.
2.2 聚合模型
1. 适用场景

在数据分析领域，有很多需要对数据进行统计和汇总操作的场景。比如:

分析网站或APP访问流量，统计用户的访问总时长、访问总次数;
广告厂商为广告主提供的广告点击总量、展示总量、消费统计等;
分析电商的全年的交易数据, 获得某指定季度或者月份的, 各人口分类(geographic)的爆款商品.
适合采用聚合模型来分析的场景具有如下特点：

业务方进行的查询为汇总类查询，比如sum、count、 max等类型的查询；
不需要召回原始的明细数据；
老数据不会被频繁更新，只会追加新数据。
2. 模型原理

DorisDB会将指标列按照相同维度列进行聚合。当多条数据具有相同的维度时，DorisDB会把指标进行聚合。从而能够减少查询时所需要的处理的数据量，进而提升查询的效率。



以下面的原始数据为例：

Date Country PV
2020.05.01 CHN 1
2020.05.01 CHN 2
2020.05.01 USA 3
2020.05.01 USA 4
在DorisDB聚合模型的表中，存储内容会从四条数据变为两条数据。这样在后续查询处理的时候，处理的数据量就会显著降低：

Date Country PV
2020.05.01 CHN 3
2020.05.01 USA 7
3. 如何使用

在建表时, 只要给指标列的定义指明聚合函数, 就会启用聚合模型; 用户可以使用AGGREGATE KEY显示地定义排序建.

以下是一个使用聚合模型创建数据表的例子：

site_id, date, city_code为排序键;
pv为指标列, 使用聚合函数SUM.
CREATE TABLE IF NOT EXISTS example_db.aggregate_tbl (
site_id LARGEINT NOT NULL COMMENT "id of site",
date DATE NOT NULL COMMENT "time of event",
city_code VARCHAR(20) COMMENT "city_code of user"
pv BIGINT SUM DEFAULT "0" COMMENT "total page views"
)
DISTRIBUTED BY HASH(site_id) BUCKETS 8;
4. 注意事项

聚合表中数据会分批次多次导入, 每次导入会形成一个版本. 相同排序键的数据行聚合有三种触发方式: 1. 数据导入时, 数据落盘前的聚合; 2. 数据落盘后, 后台的多版本异步聚合; 3. 数据查询时, 多版本多路归并聚合.
数据查询时, 指标列采用先聚合后过滤的方式, 把没必有做指标的列存储为维度列.
聚合模型所支持的聚合函数列表请参考《Create Table语句说明》。
2.3 更新模型
1. 适用场景

有些分析场景之下，数据会更新, DorisDB采用更新模型来满足这种需求。比如在电商场景中，定单的状态经常会发生变化，每天的订单更新量可突破上亿。在这种量级的更新场景下进行实时数据分析，如果在明细模型下通过delete+insert的方式，是无法满足频繁更新需求的; 因此, 用户需要使用更新模型来满足数据分析需求。



以下是一些适合更新模型的场景特点：

已经写入的数据有大量的更新需求；
需要进行实时数据分析。
2. 模型原理

更新模型中, 排序键满足性约束, 成为主键.

DorisDB存储内部会给每一个批次导入数据分配一个版本号, 同一主键的数据可能有多个版本, 查询是, 大(新)版本的数据胜出.

ID value _version
1 100 1
1 101 2
2 100 3
2 101 4
2 102 5
具体的示例如上表所示，ID是表的主键，value是表的内容，而__version是DorisDB内部的版本号。其中ID为1的数据有两个导入批次，版本分别为1，2；ID为2的数据有三个批次导入，版本分别为3，4，5。在查询的时候对于ID为1只会返回新版本2的数据，而对于ID为2只会返回新版本5的数据，那么对于用户能能够看到的数据如下表所示：

ID value
1 101
2 102
通过这种机制，DorisDB可以支持对于频繁更新数据的分析。

3. 如何使用

在电商订单分析场景中，经常根据订单状态进行的统计分析。因为订单状态经常改变，而create_time和order_id不会改变，并且经常会在查询中作为过滤条件。所以可以将 create_time和order_id 两个列作为这个表的主键（即，在建表时用UNIQUE KEY关键字定义），这样既能够满足订单状态的更新需求，又能够在查询中进行快速过滤。



以下是一个使用更新模型创建数据表的例子：

用UNIQUE KEY(create_time, order_id)做主键, 其中create_time, order_id为排序列, 其定义在其他列定义之前出现;
order_state和total_price为指标列, 其聚合类型为REPLACE.
CREATE TABLE IF NOT EXISTS detail (
create_time DATE NOT NULL COMMENT "create time of an order",
order_id BIGINT NOT NULL COMMENT "id of an order",
order_state INT COMMENT "state of an order",
total_price BIGINT COMMENT "price of an order"
)
UNIQUE KEY(create_time, order_id)
DISTRIBUTED BY HASH(order_id) BUCKETS 8
4. 注意事项

导入数据时需要将所有字段补全才能够完成更新操作，即，上述例子中的create_time、order_id、order_state和total_price四个字段都需必须存在。
对于更新模型的数据读取，需要在查询时完成多版本合并，当版本过多时会导致查询性能降低。所以在向更新模型导入数据时，应该适当降低导入频率，从而提升查询性能。建议在设计导入频率时以满足业务对实时性的要求为准。如果业务对实时性的要求是分钟级别，那么每分钟导入一次更新数据即可，不需要秒级导入。
在查询时，对于value字段的过滤通常在多版本合并之后。将经常过滤字段且不会被修改的字段放在主键上, 能够在合并之前就将数据过滤掉，从而提升查询性能。
因为合并过程需要将所有主键字段进行比较，所以应该避免放置过多的主键字段，以免降低查询性能。如果某个字段只是偶尔会作为查询中的过滤条件存在，不需要放在主键中。
3 数据分布
3.1 简介


为了更灵活地划分数据，现代分布式数据库除了单独采用上述四种数据分布方式之外，也会视情况采用组合数据分布。常见的组合方式有Hash-Hash、Range-Hash、Hash-List。

3.2 数据分布方式


数据分布：数据分布是将数据划分为子集, 按一定规则, 均衡地分布在不同节点上，以期大限度地利用集群的并发性能。
短查询：short-scan query，指扫描数据量不大，单机就能完成扫描的查询。
长查询：long-scan query，指扫描数据量大，多机并行扫描能显著提升性能的查询。
常见的四种数据分布方式有：(a) Round-Robin、(b) Range、(c) List和(d) Hash (DeWitt and Gray, 1992)。如下图所示:

Round-Robin : 以轮转的方式把数据逐个放置在相邻节点上。
Range : 按区间进行数据分布，图中区间[1-3]，[4-6]分别对应不同Range。
List : 直接基于离散的各个取值做数据分布，性别、省份等数据就满足这种离散的特性。每个离散值会映射到一个节点上，不同的多个取值可能也会映射到相同节点上。
Hash : 按哈希函数把数据映射到不同节点上。
1 数据分布方式

DorisDB使用先分区后分桶的方式, 可灵活地支持支持二种分布方式:

Hash分布: 不采用分区方式, 整个table作为一个分区, 指定分桶的数量. Range-Hash的组合数据分布: 即指定分区数量, 指定每个分区的分桶数量.

-- 采用Hash分布的建表语句
CREATE TABLE site_access(
site_id INT DEFAULT '10',
city_code SMALLINT,
user_name VARCHAR(32) DEFAULT '',
pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(site_id, city_code, user_name)
DISTRIBUTED BY HASH(site_id) BUCKETS 10;
图3.2：采用Hash分布的建表语句

-- 采用Range-Hash组合分布的建表语句
CREATE TABLE site_access(
event_day DATE,
site_id INT DEFAULT '10',
city_code VARCHAR(100),
user_name VARCHAR(32) DEFAULT '',
pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(event_day, site_id, city_code, user_name)
PARTITION BY RANGE(event_day)
(
PARTITION p1 VALUES LESS THAN ('2020-01-31'),
PARTITION p2 VALUES LESS THAN ('2020-02-29'),
PARTITION p3 VALUES LESS THAN ('2020-03-31')
)
DISTRIBUTED BY HASH(site_id) BUCKETS 10;

图3.3：采用Range-Hash组合分布的建表语句



ALTER TABLE site_access
ADD PARTITION p4 VALUES LESS THAN ("2020-04-31")
DISTRIBUTED BY HASH(site_id) BUCKETS 20;
图3.4：在site_access表中添加新的分区，并使用新的分桶数

2 分区列如何选择

分区的主要作用是将整个分区作为管理单位, 选择存储策略, 比如副本数, 冷热策略和存储介质等等。大多数情况下，近期的数据被查询的可能性更大。将近的数据放在一个分区之内，这样可以通过DorisDB的分区裁剪功能，大限度地减少扫描数据量，从而提高查询性能。同时，DorisDB支持在一个集群内使用多种存储介质(SATA/SSD)。用户可以将新数据所在的分区放在SSD上，利用SSD的随机读写性能来提高查询性能。而老的数据可以放在SATA盘上，以节省数据存储的成本。

3 分桶列如何选择

DorisDB采用Hash算法作为分桶算法，同一分区内, 分桶键的哈希值相同的数据形成(Tablet)子表, 子表多副本冗余存储, 子表副本在物理上由一个单独的本地存储引擎管理, 数据导入和查询终都下沉到所涉及的子表副本上, 同时子表也是数据均衡和恢复的基本单位.

select
city_code, sum(pv)
from site_access
where site_id = 54321;
图3.5：site_access表场景查询

CREATE TABLE site_access
(
site_id INT DEFAULT '10',
city_code SMALLINT,
user_name VARCHAR(32) DEFAULT '',
pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(site_id, city_code, user_name)
DISTRIBUTED BY HASH(site_id,city_code) BUCKETS 10;
图3.6：采用site_id、city_code作为分桶键

4 分桶数如何确定

在DorisDB系统中，分桶是实际物理文件组织的单元。数据在写入磁盘后，就会涉及磁盘文件的管理。一般而言，我们不建议分桶数据过大或过小，尽量适中会比较妥当。

5 佳实践

对于DorisDB而言，分区和分桶的选择是非常关键的。在建表时选择好的分区分桶列，可以有效提高集群整体性能。当然，在使用过程中，也需考虑业务情况，根据业务情况进行调整。



3.3.4 动态分区管理
在很多实际应用场景中，数据的时效性很重要，需要为新达到数据创建新分区, 删除过期. DorisDB的动态分区机制可以实现分区rollover: 对分区实现进行生命周期管理(TTL)，自动增删分区，减少用户的使用心智负担。

1 创建支持动态分区的表

下面以一个实际的例子来介绍动态分区功能。

CREATE TABLE site_access(
event_day DATE,
site_id INT DEFAULT '10',
city_code VARCHAR(100),
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(event_day, site_id, city_code, user_name)
PARTITION BY RANGE(event_day)(
PARTITION p20200321 VALUES LESS THAN ("2020-03-22"),
PARTITION p20200322 VALUES LESS THAN ("2020-03-23"),
PARTITION p20200323 VALUES LESS THAN ("2020-03-24"),
PARTITION p20200324 VALUES LESS THAN ("2020-03-25")
)
DISTRIBUTED BY HASH(event_day, site_id) BUCKETS 32
PROPERTIES(
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-3",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "32"
);

图4.1：动态分区的表



SHOW PARTITIONS FROM site_access;
图4.3 : site_access当前分区

3 修改表的分区属性

动态分区的属性可以修改，例如需要起/停动态分区的功能，可以通过ALTER TABLE来完成。

ALTER TABLE site_access SET("dynamic_partition.enable"="false");
ALTER TABLE site_access SET("dynamic_partition.enable"="true");
注意：依照相同语句，也可以相应的修改其他属性。

4 注意事项

动态分区的方式相当于把建分区的判断逻辑交由DorisDB来完成，在配置的过程中一定要保证分区名称满足规范，否则会创建失败。具体规范可以描述如下：



以图1中创建了一张表，并同步开启动态分区特性。图中分区的区间为当前时间的前后3天，总共6天。假设当前时间为2020-03-25为例，在每次调度时，会删除分区上界小于 2020-03-22 的分区，同时在调度时会创建今后3天的分区。调度完成之后，新的分区会是下列列表。

[*: [DATE]; keys: [2020-03-22]; ‥*: [DATE]; keys: [2020-03-23]; )
[*: [DATE]; keys: [2020-03-23]; ‥*: [DATE]; keys: [2020-03-24]; )
[*: [DATE]; keys: [2020-03-24]; ‥*: [DATE]; keys: [2020-03-25]; )
[*: [DATE]; keys: [2020-03-25]; ‥*: [DATE]; keys: [2020-03-26]; )
[*: [DATE]; keys: [2020-03-26]; ‥*: [DATE]; keys: [2020-03-27]; )
[*: [DATE]; keys: [2020-03-27]; ‥*: [DATE]; keys: [2020-03-28]; )
[*: [DATE]; keys: [2020-03-28]; ‥*: [DATE]; keys: [2020-03-29]; )
图4.2：自动增删分区之后表的分区

2 查看表当前的分区

动态分区表运行过程中，会不断地自动增减分区，可以通过下列命令查看当前的分区情况，

4 如何选择排序键
4.1 排序键基本原理


数据倾斜：业务方如果确定数据有很大程度的倾斜，那么建议采用多列组合的方式进行数据分桶，而不是只单独采用倾斜度大的列做分桶。
高并发：分区和分桶应该尽量覆盖查询语句所带的条件，这样可以有效减少扫描数据，提高并发。
高吞吐：尽量把数据打散，让集群以更高的并发扫描数据，完成相应计算。
dynamic_partition.enable : 是否开启动态分区特性，可指定为 TRUE 或 FALSE。如果不填写，默认为 TRUE。

dynamic_partition.time_unit : 动态分区调度的粒度，可指定为 DAY/WEEK/MONTH。

指定为 DAY 时，分区名后缀需为yyyyMMdd，例如20200325。图1 就是一个按天分区的例子，分区名的后缀满足yyyyMMdd。
PARTITION p20200321 VALUES LESS THAN ("2020-03-22"),
PARTITION p20200322 VALUES LESS THAN ("2020-03-23"),
PARTITION p20200323 VALUES LESS THAN ("2020-03-24"),
PARTITION p20200324 VALUES LESS THAN ("2020-03-25")
指定为 WEEK 时，分区名后缀需为yyyy_ww，例如2020_13代表2020年第13周。
指定为 MONTH 时，动态创建的分区名后缀格式为 yyyyMM，例如 202003。
dynamic_partition.start: 动态分区的开始时间。以当天为基准，超过该时间范围的分区将会被删除。如果不填写，则默认为Integer.MIN_VALUE 即 -2147483648。

dynamic_partition.end: 动态分区的结束时间。 以当天为基准，会提前创建N个单位的分区范围。

dynamic_partition.prefix : 动态创建的分区名前缀。

dynamic_partition.buckets : 动态创建的分区所对应的分桶数量。

指定为 DAY 时，分区名后缀需为yyyyMMdd，例如20200325。
指定为 WEEK 时，分区名后缀需为yyyy_ww，例如 2020_13, 代表2020年第13周。
指定为 MONTH 时，动态创建的分区名后缀格式为 yyyyMM，例如 202003。
DorisDB中为加速查询，在内部组织并存储数据时，会把表中数据按照指定的列进行排序，这部分用于排序的列（可以是一个或多个列），可以称之为Sort Key。明细模型中Sort Key就是指定的用于排序的列（即 DUPLICATE KEY 指定的列），聚合模型中Sort Key列就是用于聚合的列（即 AGGREGATE KEY 指定的列），更新模型中Sort Key就是指定的满足性约束的列（即 UNIQUE KEY 指定的列）。下图中的建表语句中Sort Key都为 (site_id、city_code)。

CREATE TABLE site_access_duplicate
(
site_id INT DEFAULT '10',
city_code SMALLINT,
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(site_id, city_code)
DISTRIBUTED BY HASH(site_id) BUCKETS 10;

CREATE TABLE site_access_aggregate
(
site_id INT DEFAULT '10',
city_code SMALLINT,
user_name VARCHAR(32) DEFAULT '',
pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(site_id, city_code)
DISTRIBUTED BY HASH(site_id) BUCKETS 10;

CREATE TABLE site_access_unique
(
site_id INT DEFAULT '10',
city_code SMALLINT,
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT '0'
)
UNIQUE KEY(site_id, city_code)
DISTRIBUTED BY HASH(site_id) BUCKETS 10;

图 5.1 ：三种建表模型分别对应的Sort Key



图5.1中，各表数据都依照site_id、city_code这两列排序。这里有两点需要注意：

排序列的定义必须出现在建表语句中其他列的定义之前。以图1中的建表语句为例，三个表的排序列可以是site_id、city_code，或者site_id、city_code、user_name，但不能是city_code、user_name，或者site_id、city_code、pv。 排序列的顺序是由create table语句中的列顺序决定的。DUPLICATE/UNIQUE/AGGREGATE KEY中顺序需要和create table语句保持一致。以site_access_duplicate表为例，也就是说下面的建表语句会报错。



在个case中，为了定位到数据行的位置，需进行二分查找，以找到指定区间。假设数据行非常多，直接对site_id, city_code进行二分查找，需要把两列数据都加载到内存中，这会消耗大量内存空间。为优化这个细节，DorisDB在Sort Key的基础上引入稀疏的shortkey index，Sort Index的内容会比数据量少1024倍，因此会全量缓存在内存中，实际查找的过程中可以有效加速查询。当Sort Key列数非常多时，会占用大量内存, 为了避免这种情况, 对shortkey index索引项做了限制:

shortkey 的列只能是排序键的前缀; shortkey 列数不超过3; 字节数不超过36字节; 不包含FLOAT/DOUBLE类型的列; VARCHAR类型列只能出现一次, 并且是末尾位置; 当shortkey index的末尾列为CHAR或者VARCHAR类型时, shortkey的长度会超过36字节; 当用户在建表语句中指定PROPERTIES {short_key = "integer"}时, 可突破上述限制;

如果查询只包含site_id一列，也能定位到只包含site_id的数据行，如：
select sum(pv) from site_access_duplicate where site_id = 123;
如果查询只包含city_code一列，那么需要扫描所有的数据行，排序的效果相当于大打折扣，如：
select sum(pv) from site_access_duplicate where city_code = 2;
-- 错误的建表语句
CREATE TABLE site_access_duplicate
(
site_id INT DEFAULT '10',
city_code SMALLINT,
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(city_code, site_id)
DISTRIBUTED BY HASH(site_id) BUCKETS 10;

-- 正确的建表语句
CREATE TABLE site_access_duplicate
(
site_id INT DEFAULT '10',
city_code SMALLINT,
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(site_id, city_code)
DISTRIBUTED BY HASH(site_id) BUCKETS 10;
图 5.2 ：DUPLICATE KEY列顺序与CREATE TABLE中不一致



再来看一下排序列在查询中的效果，图1中排序列的效果可分三种情况进行描述：

用户查询时如果条件包含上述两列，则可以大幅地降低扫描数据行，如：
select sum(pv) from site_access_duplicate where site_id = 123 and city_code = 2;
4.2 如何选择

从上面的介绍可以看出，如果用户在查询site_access_duplicate表时只选择city_code做查询条件，排序列相当于失去了功效。因此排序列的选择是和查询模式息息相关的，经常作为查询条件的列建议放在Sort Key中。



当Sort Key涉及多个列的时候，谁先谁后也有讲究，区分度高、经常查询的列建议放在前面。在site_access_duplicate表中，city_code的取值个数是固定的（城市数目是固定的），而site_id的取值个数要比city_code大得多，而且还在不断变多，因此site_id区分度就比city_code要高不少。



还是以site_access_duplicate表为例：

如果用户需要经常按site_id+city_code的组合进行查询，那么把site_id放在Sort Key列就是更加有效的一种方式。
如果用户需要经常用city_code进行查询，偶尔按照site_id+city_code组合查询，那么把city_code放在Sort Key的列就更为合适。
当然有一种极端情况，就是按site_id+city_code组合查询、以及city_code单独查询的比例不相上下。那么这个时候，可以创建一个city_code为列的RollUp表，RollUp表会为city_code再建一个Sort Index。
4.3 注意事项
由于DorisDB的shortkey索引大小固定（只有36字节），所以不会存在内存膨胀的问题。需要注意的是：

排序列中包含的列必须是从列开始，并且连续的。
排序列的顺序是由create table语句中的列顺序决定的。
Sort Key不应该包含过多的列。如果选择了大量的列用于Sort Key，那么排序的开销会导致数据导入的开销增加。
在大多数时候，Sort Key的前面几列也能很准确的定位到数据行所在的区间，更多列的排序也不会带来查询的提升
部分资料内容来源于网络 , 如侵权请联系博主删除!
————————————————
版权声明：本文为CSDN博主「白眼黑刺猬」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_37933018/article/details/116118171