15 数据存储上如何选用合适的表存储类型？

你好，我是彭旭。

上节课我们探索了StarRocks的架构和数据分布，也介绍了CDP的几个表，了解了如何在StarRocks里面设计分区分桶键。

这节课我们再来看看，CDP的数据模型，在StarRocks里面，该如何选用合适的存储引擎。

从数仓的建模分层说起

在数仓建模中，有一个比较通用的建模方法论，就是将数据分为4层：包括：ODS（Operational Data Store）、DWD（Data Warehouse Detail）、DWS（Data Warehouse Summary）和ADS（Application Data Store）。

这里我们简单解释一下这4层数据模型的定义与作用。

ODS：直接存放从各个渠道收集的原始数据，比如业务系统收集的、前后端埋点收集的等等。有时候会考虑做一定程度的数据清洗，比如处理异常字段、规范字段命名、统一时间字段等。

DWD：以业务过程作为建模驱动，基于每个具体的业务过程特点，构建最细粒度的明细事实表，比如浏览明细表、交易明细表。DWD也会清洗ODS层的数据（去除空值、脏数据、异常数据）、降维处理、脱敏等，并基于维度建模，将某些字段做适当冗余，做宽表化。

DWS：在DWD层的基础上进行数据聚合和汇总，比如用户交易汇总表、用户指标汇总宽表。一般会依据特定业务需求或报表要求进行数据汇总和预计算，提高数据查询效率和性能。

ADS：在DWS之上，面向特定应用场景设计的数据层，比如我们CDP场景里面，用户标签、人群画像，是通过计算后直接给到前端应用系统使用的表。

这样分层的主要目的有两个。

第一个是将数据按照不同的层次、不同的用途进行组织，比如用在数据管理、分析统计等多样化的数据分析需求中。

第二个是可以将一个大的任务，分解成多个步骤，每个步骤的中间结果都可以复用，出错了也只需要部分重试，提升了灵活性和扩展性。

StarRocks作为数据仓库，很可能就是借鉴了这种分层方法，它支持的表模型正好符合这个分层思路。

DWD与明细模型

在CDP中，用户行为表其实就是一个明细表，这里我们就不按行为类型进一步细分多个明细表了，所有用户的操作行为、业务行为，都以不同的事件类型数据存储在这个表。

你会发现，明细模型其实就类似DWD中的明细表，适合需要保存明细数据的场景，相同key的数据可以重复插入，保存多行数据。注意，明细表的数据不会更新，只会新增插入。

明细模型是StarRocks的默认建表模型，可以通过 “DUPLICATE KEY (C1，C2)” 来声明，其中C1、C2会同时作为排序键排序。注意，在建表DDL中，排序键需要排在其他字段前面，比如我们的“用户行为表cdp_user_event”，定义为明细模型的DDL语句如下所示。

CREATE TABLE cdp_user_event (
    event_time DATETIME COMMENT '事件发生时间',
    event_type VARCHAR(50) COMMENT '事件类型，pay,add_shop_cat,browse,recharge等',
    unique_user_id BIGINT NOT NULL COMMENT '用户全局唯一ID，ONE-ID',
    page_id INT COMMENT '浏览事件页面ID',
    item_id ARRAY<INT> COMMENT '浏览、加购、下单事件中商品ID',
    total_amount DECIMAL(18, 2) COMMENT '订单金额',
    device_type VARCHAR(50) COMMENT '设备类型',
    event_param JSON COMMENT '事件相关参数，比如购买事件商品ID、支付金额等',
    location VARCHAR(100) COMMENT '发生地点，如城市、门店等'
)
DUPLICATE KEY(event_time,event_type)
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(event_type,unique_user_id)
PROPERTIES (
"replication_num" = "1"
);

DWD层明细数据有了对应的存储模型，那接下来就是数据汇总后的DWS层数据存储了。

DWS与聚合模型

在DWS层，我们需要基于DWD明细数据，在某些维度做一些数据的汇总，比如基于用户维度做浏览次数汇总，基于商品维度做销售额汇总。

在CDP中，我们用户指标表的字段，其实都是基于用户行为数据，通过SQL统计计算出来的。在StarRocks中，提供了另外一种方法，也就是使用聚合模型。

使用聚合模型可以在建表时，指定聚合列（StarRocks叫做排序键）与指标列，指标列一般被声明为SUM、MIN、MAX之类的聚合函数。

指标列会根据排序键进行聚合，所以在分析统计和汇总数据时，能够减少查询时所需要处理的数据，提升查询效率。

比如在我们CDP “用户指标表cdp_user_metrics” 场景中，排序键就是用户唯一ID，其他都是指标列，表定义DDL语句如下。

CREATE TABLE cdp_user_metrics (
    unique_user_id BIGINT COMMENT '用户全局唯一ID，ONE-ID',
    sum_pay DECIMAL(20,10) SUM COMMENT '总计消费金额',
    sum_pay_num INT SUM COMMENT '总计消费次数',
    sum_30d_login INT SUM COMMENT '30天登录次数'
) AGGREGATE KEY(unique_user_id)
  DISTRIBUTED BY HASH(unique_user_id)
  PROPERTIES (
  "replication_num" = "1"
  );

最后，到了ADS层，ADS层的每行数据一般都需要一个唯一键来标识，同样，作为一个数据库，必须要支持根据主键或者唯一键来实时更新或者读取数据。StarRocks号称能够支持高并发的实时频繁更新，具体是怎么实现的呢？

更新模型

先来看一下更新模型。

你应该还记得MOR（Merge-On-Read），也就是在读取的时候来聚合数据。更新模型采用的就是MOR的技术。

在更新模型中，可以将一列或者多列定义为主键，其他列就被称为指标列。数据写入或者导入的时候，一批数据会分配一个版本号，所以同一主键的数据可能有多个版本，查询时会返回版本最新（即版本号最大）的数据。

看到这里是不是会想起HBase里面的LSM？它们其实是一个逻辑。所以更新模型适合写入以及实时更新较多的场景，但是读取性能相对较差。

StarRocks为了提升读取性能，同样采用Compact机制。在存储文件过多的时候，就能压缩合并，减少需要扫描的文件数，提升读取性能。

更新模型采用的是MOR策略，查询时需要在线聚合多版本。这里由于Merge算子的存在，导致谓词和索引无法下推，对查询性能影响较大。

为了提升性能，StarRocks1.19版本推出了主键模型 (Primary Key Model) 。

主键模型

事实上，我觉得主键模型完全可以替代更新模型。主键模型采取Delete-and-Insert策略，前面的课程也有提到，Delete-and-Insert的思路是牺牲部分写性能，极大地提升读性能。

Delete-and-Insert会引入一个主键索引，在数据更新的时候，通过主键索引找到记录，给这条记录打上一个删除标记（Delete Bitmap），表示这条记录已经被删除了。接着，将其更新的记录当作新数据插入到新的数据块中。这样做的好处是在读取数据时，可以并行加载所有数据块Block，然后根据删除标记过滤已经删除的记录。

为什么说主键模型能够替代更新模型，性能上会有一个巨大的提升呢？

这里就得说一下谓词下推了。谓词下推的核心逻辑，就是尽可能多地将判断放在更贴近数据源的那一侧进行，在查询时跳过无关的数据。用SQL优化的话来说，就是先过滤再做聚合等操作。

在MOR模式下，数据需要先Merge完了以后才能过滤，因为同一行数据可能有多个版本分布在不同数据存储文件，那就有可能发生问题呢？

比如在查询的时候发现，某行低版本的数据满足过滤条件，那这行数据应该被返回吗？这个时候我们还不知道，因为有可能这行数据不是最新版本，而最新版本又不满足过滤条件。所以只能是先Merge完成之后，才能过滤。

而在Delete-and-Insert，数据只有删除的版本或者最新生效版本，这样过滤就可以下推到存储引擎最下面一层。极大地减小了IO，提升了效率。

回到CDP的数据模型里面，显然，“用户属性表cdp_user”的每行记录存在一个唯一的用户ID，并且会通过这个ID更新用户数据，所以适合用主键模型，建表DDL如下。

CREATE TABLE cdp_user (
    unique_user_id BIGINT NOT NULL COMMENT '用户全局唯一ID，ONE-ID',
    name VARCHAR(255) COMMENT '用户姓名',
    nickname VARCHAR(255) COMMENT '用户昵称',
    gender INT COMMENT '性别：1-男；2-女；3-未知',
    birthday VARCHAR(8) COMMENT '用户生日：yyyyMMdd',
    user_level INT COMMENT '用户等级：1-5',
    register_date DATETIME COMMENT '注册日期',
    last_login_time DATETIME COMMENT '最后一次登录时间'
) PRIMARY KEY (unique_user_id)
DISTRIBUTED BY HASH(unique_user_id)
ORDER BY(`register_date`);

注意，因为有可能需要按用户注册时间进行统计分析，所以建表的时候，我们用register_date作为排序键。

好了，总结一下，StarRocks支持4种表模型，每种表模型都可以应对不同的场景，这里我再整理一下。

明细表（Duplicate Key table）

由关键字“DUPLICATE KEY”定义，相同KEY的数据可以存在多行，也就是没有唯一约束。
数据同时会根据DUPLICATE KEY定义的列排序。
分区列、分桶列无限制。
适合需要保留原始数据，数据只需要追加的场景，比如日志数据分析。

聚合表（Aggregate table）

由关键字“AGGREGATE KEY”定义，相同KEY的数据会根据定义的聚合函数聚合统计。
数据同时会根据AGGREGATE KEY定义的列排序。
分区列、分桶列必须从聚合列中选取。
适合不需要保留原始数据，数据只追加不更新，只需要聚合查询的场景，比如网站访问分析。

更新表（Unique Key table）

由关键字“UNIQUE KEY”定义，类似关系型数据库UK，具备唯一约束。
数据同时会根据UNIQUE KEY定义的列排序。
分区列、分桶列必须从UK列中选取。

特别注意，更新表其实跟主键表作用类似，可以被主键表取代。

主键表（Primary Key table）

由关键字“PRIMARY KEY”定义，3.0版本前主键就是排序键，3.0起，可以额外用关键字“ORDER BY”定义一个排序键。
类似关系型数据库主键，主键相同数据具备唯一与非空约束，新增主键相同的数据就是会更新旧数据。
分区列、分桶列必须从主键的列中选取。
适合于有更新和实时分析的场景。

小结

在数仓建模中，数据通常被分为四个层次：ODS、DWD、DWS和ADS。ODS用于存放原始数据，DWD是最细粒度的明细表，DWS用于汇总数据，而ADS则是面向特定应用场景设计的数据层。StarRocks的表模型也符合这种分层思路。

在我们CDP的数据模型里面，用户行为表是一个原始的明细数据，所以用明细表存储。用户指标表定义的都是一些聚合函数字段，所以可以使用聚合表，不过注意的是聚合表不会存储原始数据，只存储聚合后的数据。用户属性表一般根据用户ID查询或者更新，所以使用主键表。

主键模型采用Delete-and-Insert策略，提升了读性能并支持谓词下推，相比更新模型有更好的性能表现。已经可以替代掉更新模型。其实主键表的目标就是跟关系型数据库的表一样，能够实现快速的实时更新。事实上，在StarRocks内部也有提到后续的版本迭代、新功能的支持，会聚焦在主键模型上，所以大部分情况下，我们建议都使用主键模型。

思考题

谓词下推是指将查询条件尽可能地下推到数据源的最底层，以减少需要处理的数据量，提升查询效率。，你知道在关系型数据库中，表连接的情况下，在什么时候使用谓词下推可以提升查询效率吗？

欢迎你在留言区和我交流。如果觉得有所收获，也可以把课程分享给更多的朋友一起学习。欢迎你加入我们的读者交流群，我们下节课见！

精选留言（1）

一米阳光 👍（1） 💬（1）
在关系型数据库中，表连接的情况下，在什么时候使用谓词下推可以提升查询效率吗？当连接的时候有筛选条件，存在索引和Subquery的情况下
2024-07-15