大数据StarRocks(三) StarRocks数据表设计_starrocks单表存储数据多大(2)

2401_84181731

于 2024-05-11 15:29:46 发布

阅读量772

点赞数 12

CC 4.0 BY-SA版权

分类专栏：程序员文章标签：大数据面试学习

本文链接：https://blog.youkuaiyun.com/2401_84181731/article/details/138717721

程序员专栏收录该内容

151 篇文章

订阅专栏

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

广告厂商为广告主提供的广告点击总量、展示总量、消费统计等。
通过分析电商的全年交易数据，获得指定季度或者月份中，各类消费人群的爆款商品。

在这些场景中，数据查询和导入，具有以下特点：
多为汇总类查询，比如 SUM、MAX、MIN等类型的查询。
不需要查询原始的明细数据。
旧数据更新不频繁，只会追加新的数据。

原理
从数据导入至数据查询阶段，聚合模型内部同一排序键的数据会多次聚合，聚合的具体时机和机制如下：
数据导入阶段：数据按批次导入至聚合模型时，每一个批次的数据形成一个版本。在一个版本中，同一排序键的数据会进行一次聚合。
后台文件合并阶段 (Compaction) ：数据分批次多次导入至聚合模型中，会生成多个版本的文件，多个版本的文件定期合并成一个大版本文件时，同一排序键的数据会进行一次聚合。
查询阶段：所有版本中同一排序键的数据进行聚合，然后返回查询结果。

注意
建表时必须使用 DISTRIBUTED BY HASH 子句指定分桶键。分桶键的更多说明，请参见分桶。
自 2.5.7 版本起，StarRocks 支持在建表和新增分区时自动设置分桶数量 (BUCKETS)，您无需手动设置分桶数量。

使用说明
排序键的相关说明：
在建表语句中，排序键必须定义在其他列之前。
排序键可以通过 AGGREGATE KEY 显式定义。
如果 AGGREGATE KEY 未包含全部维度列（除指标列之外的列），则建表会失败。
如果不通过 AGGREGATE KEY 显示定义排序键，则默认除指标列之外的列均为排序键。
排序键必须满足唯一性约束，必须包含全部维度列，并且列的值不会更新。

指标列：
通过在列名后指定聚合函数，定义该列为指标列。一般为需要汇总统计的数据。

聚合函数：
指标列使用的聚合函数。聚合模型支持的聚合函数，请参见 CREATE TABLE。
查询时，排序键在多版聚合之前就能进行过滤，而指标列的过滤在多版本聚合之后。因此建议将频繁使用的过滤字段作为排序键，在聚合前就能过滤数据，从而提升查询性能。
建表时，不支持为指标列创建 BITMAP、Bloom Filter 等索引。

4.3 更新模型（Unique key）

建表时，支持定义主键和指标列，查询时返回主键相同的一组数据中的最新数据。相对于明细模型，更新模型简化了数据导入流程，能够更好地支撑实时和频繁更新的场景。

适用场景
实时和频繁更新的业务场景，例如分析电商订单。在电商场景中，订单的状态经常会发生变化，每天的订单更新量可突破上亿。

原理
更新模型可以视为聚合模型的特殊情况，指标列指定的聚合函数为 REPLACE，返回具有相同主键的一组数据中的最新数据。
数据分批次多次导入至更新模型，每一批次数据分配一个版本号，因此同一主键的数据可能有多个版本，查询时返回版本最新（即版本号最大）的数据。相对于明细模型，更新模型通过简化导入流程，能够更好地支持实时和频繁更新。

使用说明
主键的相关说明：
在建表语句中，主键必须定义在其他列之前。主键通过 UNIQUE KEY 定义。
主键必须满足唯一性约束，且列的值不会修改。设置合理的主键。
查询时，主键在聚合之前就能进行过滤，而指标列的过滤通常在多版本聚合之后，因此建议将频繁使用的过滤字段作为主键，在聚合前就能过滤数据，从而提升查询性能。
聚合过程中会比较所有主键，因此需要避免设置过多的主键，以免降低查询性能。如果某个列只是偶尔会作为查询中的过滤条件，则不建议放在主键中。
建表时，不支持为指标列创建 BITMAP、Bloom Filter 等索引。

4.4 主键模型（Primary key）

相比较更新模型，主键模型可以更好地支持实时/频繁更新的功能。虽然更新模型也可以实现实时对数据的更新，但是更新模型采用Merge on Read读时合并策略会大大限制查询功能，在主键模型更好地解决了行级的更新操作。配合Flink-connector-starrocks可以完成Mysql CDC实时同步的方案。

适用场景
主键模型适用于实时和频繁更新的场景，例如：
实时对接事务型数据至 StarRocks。事务型数据库中，除了插入数据外，一般还会涉及较多更新和删除数据的操作，因此事务型数据库的数据同步至 StarRocks 时，建议使用主键模型。通过 Flink-CDC 等工具直接对接 TP 的 Binlog，实时同步增删改的数据至主键模型，可以简化数据同步流程，并且相对于 Merge-On-Read 策略的更新模型，查询性能能够提升 3~10 倍。
利用部分列更新轻松实现多流 JOIN。在用户画像等分析场景中，一般会采用大宽表方式来提升多维分析的性能，同时简化数据分析师的使用模型。而这种场景中的上游数据，往往可能来自于多个不同业务（比如来自购物消费业务、快递业务、银行业务等）或系统（比如计算用户不同标签属性的机器学习系统），主键模型的部分列更新功能就很好地满足这种需求，不同业务直接各自按需更新与业务相关的列即可，并且继续享受主键模型的实时同步增删改数据及高效的查询性能。

需要注意的是：
由于存储引擎会为主键建立索引，导入数据时会把索引加载到内存中，所以主键模型对内存的要求更高，所以不适合主键模型的场景还是比较多的。

目前比较适合使用主键模型的场景有这两种：
（1）数据冷热特征，比如最近几天的数据才需要修改，老的冷数据很少需要修改，比如订单数据，老的订单完成后就不在更新，并且分区是按天进行分区的，那么在导入数据时历史分区的数据的主键就不会被加载，也就不会占用内存了，内存中仅会加载近几天的索引。
（2）大宽表（数百列数千列），主键只占整个数据的很小一部分，内存开销比较低。比如用户状态/画像表，虽然列非常多，但总的用户数量不大（千万-亿级别），主键索引内存占用相对可控。

原理：由于更新模型采用Merge策略，使得谓词无法下推和索引无法使用，严重影响
查询性能。所以主键模型通过主键约束，保证同一个主键仅存一条数据的记录，这样就规避了Merge操作。
StarRocks收到对某记录的更新操作时，会通过主键索引找到该条数据的位置，并对其标记为删除，再插入一条数据，相当于把update改写为delete+insert

4.5 排序键

StarRocks中为加速查询，在内部组织并存储数据时，会把表中数据按照指定的列进行排序，这部分用于排序的列（可以是一个或多个列），可以称之为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,
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;

三种表对应的sort key都为site_id,city_code。创建排序列需要注意以下两点：
（1）排序列的定义必须出现在建表语句中其他列的定义之前。以上建表语句为例，三个表的排序列可以是site_id、city_code，或者site_id、city_code、user_name，但不能是city_code、user_name，或者site_id、city_code、pv。
（2）排序列的顺序是由create table语句中的列顺序决定的。DUPLICATE/UNIQUE/AGGREGATE KEY中顺序需要和create table语句保持一致。以site_access_duplicate表为例，也就是说下面的建表语句会报错。

-- 错误的建表语句
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;

使用时注意事项：
（1）用户查询时如果条件包含上述两列，则可以大幅地降低扫描数据行，如：
select sum(pv) from site_access_duplicate where site_id = 123 and city_code = 2;
（2）如果查询只包含site_id一列，也能定位到只包含site_id的数据行，如：
select sum(pv) from site_access_duplicate where site_id = 123;
（3）如果查询只包含city_code一列，那么需要扫描所有的数据行，排序的效果相当于大打折扣，
如：
select sum(pv) from site_access_duplicate where city_code = 2; //使用时和mysql索引规则一样，缺少最佳左前缀原则，索引会失效
使用排序键本质就是在进行二分查找，所以排序列指定的越多，那么消耗的内存也会越大，StarRocks为了避免这种情况发生也对排序键做了限制：
shortkey 的列只能是排序键的前缀;
shortkey 列数不超过3;
字节数不超过36字节;
不包含FLOAT/DOUBLE类型的列;
VARCHAR类型列只能出现一次, 并且是末尾位置;
当shortkey index的末尾列为CHAR或者VARCHAR类型时, shortkey的长度会超过36字节;
当用户在建表语句中指定PROPERTIES {short_key = “integer”}时, 可突破上述限制;

Bitmap索引

StarRocks支持基于BitMap索引，对于Filter的查询有明显的加速效果。
原理：Bitmap是元素为bit的，取值为0、1两种情形的, 可对某一位bit进行置位(set)和清零(clear)操作的数组。
Bitmap的使用场景有：
用一个long型表示32位学生的性别，0表示女生，1表示男生。
用Bitmap表示一组数据中是否存在null值，0表示元素不为null，1表示为null。
一组数据的取值为(Q1, Q2, Q3, Q4)，表示季度，用Bitmap表示这组数据中取值为Q4的元素，1表示取值为Q4的元素, 0表示其他取值的元素。
在这里插入图片描述

什么是Bitmap索引：
Bitmap只能表示取值为两种情形的列数组, 当列的取值为多种取值情形枚举类型时, 例如季度(Q1, Q2, Q3, Q4), 系统平台(Linux, Windows, FreeBSD, MacOS), 则无法用一个Bitmap编码; 此时可以为每个取值各自建立一个Bitmap的来表示这组数据; 同时为实际枚举取值建立词典.
如上图所示，Platform列有4行数据，可能的取值有Android、Ios。StarRocks中会首先针对Platform列构建一个字典，将Android和Ios映射为int，然后就可以对Android和Ios分别构建Bitmap。具体来说，我们分别将Android、Ios 编码为0和1，因为Android出现在第1，2，3行，所以Bitmap是0111，因为Ios出现在第4行，所以Bitmap是1000。
假如有一个针对包含该Platform列的表的SQL查询，select xxx from table where Platform = iOS，StarRocks会首先查找字典，找出iOS对于的编码值是1，然后再去查找 Bitmap Index，知道1对应的Bitmap是1000，我们就知道只有第4行数据符合查询条件，StarRocks就会只读取第4行数据，不会读取所有数据。

适用场景：
使用Bitmap可以大大减少判断过滤时间，提高查询效率
（1）当需要对表数据进行非前置列（排序键）进行过滤时，可以创建bitmap索引加速效率。
（2）对表数据进行多列过滤，也可以考虑对多列分别创建bitmap索引加速效率

使用：
（1）创建测试表

CREATE TABLE IF NOT EXISTS user_dup (
    user_id INT,
    sex INT ,
    age INT 
   )DUPLICATE KEY(user_id)DISTRIBUTED BY HASH(user_id) BUCKETS 8

（2）插入测试数据

   INSERT INTO user_dup VALUES(1001,0,18);
   INSERT INTO user_dup VALUES(1002,1,18);
   INSERT INTO user_dup VALUES(1003,0,18);
   INSERT INTO user_dup VALUES(1004,1,18);
   INSERT INTO user_dup VALUES(1005,0,18);
   INSERT INTO user_dup VALUES(1006,1,18);
   INSERT INTO user_dup VALUES(1007,0,18);
   INSERT INTO user_dup VALUES(1008,1,18);

（3）创建位图索引

  CREATE INDEX user_sex_index ON user_dup(sex) USING bitmap;

（4）创建完后查看表中索引

SHOW INDEX FROM user_dup;

注意事项：
（1）对于明细模型，所有列都可以建Bitmap 索引；对于聚合模型，只有Key列可以建Bitmap 索引。
（2）Bitmap索引, 应该在取值为枚举型, 取值大量重复, 较低基数, 并且用作等值条件查询或者可转化为等值条件查询的列上创建.
（3）不支持对Float、Double、Decimal 类型的列建Bitmap 索引。
（4）如果要查看某个查询是否命中了Bitmap索引，可以通过查询的Profile信息查看。

Bloom Filter 索引

什么是Bloom Filter ？
Bloom Filter（布隆过滤器）是用于判断某个元素是否在一个集合中的数据结构，优点是空间效率和时间效率都比较高，缺点是有一定的误判率。
在这里插入图片描述

布隆过滤器是由一个Bit数组和n个哈希函数构成。Bit数组初始全部为0，当插入一个元素时，n个Hash函数对元素进行计算, 得到n个slot，然后将Bit数组中n个slot的Bit置1。当我们要判断一个元素是否在集合中时，还是通过相同的n个Hash函数计算Hash值，如果所有Hash值在布隆过滤器里对应的Bit不全为1，则该元素不存在。当对应Bit全1时, 则元素的存在与否, 无法确定. 这是因为布隆过滤器的位数有限, 由该元素计算出的slot, 恰好全部和其他元素的slot冲突. 所以全1情形, 需要回源查找才能判断元素的存在性。

什么是Bloom Filter 索引:
StarRocks的建表时, 可通过PROPERTIES{“bloom_filter_columns”=“c1,c2,c3”}指定需要建BloomFilter索引的列，查询时, BloomFilter可快速判断某个列中是否存在某个值。如果Bloom Filter判定该列中不存在指定的值，就不需要读取数据文件；如果是全1情形，此时需要读取数据块确认目标值是否存在。另外，Bloom Filter索引无法确定具体是哪一行数据具有该指定的值。
使用：
（1）建表时指定需要加Bloom Filter索引的列，创建一张测试表



![img](https://img-blog.csdnimg.cn/img_convert/6ced18af4662d8f47a07c57508e418b0.png)
![img](https://img-blog.csdnimg.cn/img_convert/68b7831149f8609a132a5108310678bf.png)
![img](https://img-blog.csdnimg.cn/img_convert/f93708f3842d69ae269e850ab132c8c7.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.youkuaiyun.com/forums/4f45ff00ff254613a03fab5e56a57acb)**

jbEx-1715412554341)]
[外链图片转存中...(img-AMbGd3RU-1715412554341)]

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.youkuaiyun.com/forums/4f45ff00ff254613a03fab5e56a57acb)**