StartRocks基础表创建和索引选择

一：表分类

StarRocks 提供四种类型的表，包括明细表、主键表、聚合表和更新表，适用于存储多种业务数据，例如原始数据、实时频繁更新的数据和聚合数据。

• 明细表简单易用，表中数据不具有任何约束，相同的数据行可以重复存在。该表适用于存储不需要约束和预聚合的原始数据，例如日志等。
• 主键表能力强大，具有唯一性非空约束。该表能够在支撑实时更新、部分列更新等场景的同时，保证查询性能，适用于实时查询。
• 聚合表适用于存储预聚合后的数据，可以降低聚合查询时所需扫描和计算的数据量，极大提高聚合查询的效率。
• 更新表适用于实时更新的业务场景，目前已逐渐被主键表取代。

二：表数据分布

StarRocks 采用分区+分桶的两级数据分布策略，将数据均匀分布各个 BE 节点（BE 负责执行查询计划和存储数据）。查询时能够有效裁剪数据扫描量，最大限度地利用集群的并发性能，从而提升查询性能。

分区​

第一层级为分区。表中数据可以根据分区列（通常是时间和日期）分成一个个更小的数据管理单元。查询时，通过分区裁剪，可以减少扫描的数据量，显著优化查询性能

分桶​

第二层级为分桶。同一个分区中的数据通过分桶，划分成更小的数据管理单元。并且分桶以多副本形式（默认为3）均匀分布在 BE 节点上，保证数据的高可用。一个分区按分桶方式被分成了多个桶 bucket，每个桶的数据称之为一个 tablet。

StarRocks 提供两种分桶方式：

• 哈希分桶：根据数据的分桶键值，将数据划分至分桶。选择查询时经常使用的条件列组成分桶键，能有效提高查询效率。
• 随机分桶：随机划分数据至分桶。这种分桶方式更加简单易用。

三：不同表之间差异

本篇文章基于3.1.10版本。建议先看下自己数据库版本。 select current_version();

	主键表 (Primary Key table)	明细表 (Duplicate Key table)	聚合表 (Aggregate table)	更新表 (Unique Key table)
Key 列和唯一约束	主键（Primary Key）具有唯一约束和非空约束。	Duplicate Key 不具有唯一约束。	聚合键（Aggregate Key）具有唯一约束。	唯一键（Unique Key）具有唯一约束。
Key 列和数据变更的关系（逻辑关系）	如果新数据的主键值与表中原数据的主键值相同，则存在唯一约束冲突，此时新数据会替代原数据。 与更新表相比，主键表增强了其底层存储引擎，已经可以取代更新表。	Duplicate Key 不具有唯一约束，因此如果新数据的 Duplicate Key 与表中原数据相同，则新旧数据都会存在表中。	如果新数据与表中原数据存在唯一约束冲突，则会根据聚合键和 Value 列的聚合函数聚合新旧数据。	如果新数据与表中原数据存在唯一约束冲突，则新数据会替代原数据。 更新表实际可以视为聚合函数为 replace 的聚合表。
Key 列和排序键的关系	自 3.0 起，两者解耦。	两者耦合。
Key 列和排序键支持的数据类型	数值（包括整型、布尔）、字符串、时间日期。	数值（包括整型、布尔、Decimal）、字符串、时间日期。
Key 和分区/分桶列的关系	分区列、分桶列必须在主键中。	无	分区列、分桶列必须在聚合键中。	分区列、分桶列必须在唯一键中。

 明细表：

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 code",
    channel INT COMMENT ""
)
DUPLICATE KEY(event_time, event_type)
-- 分桶键无限制 可以选择任意字段
DISTRIBUTED BY HASH(user_id)
PROPERTIES (
"replication_num" = "3"
);

   聚合表:

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"
)
AGGREGATE KEY(site_id, date, city_code)
-- 分区列、分桶列必须在聚合键中。   聚合类型：SUM、MAX、MIN等
DISTRIBUTED BY HASH(site_id)
PROPERTIES (
"replication_num" = "3"
);

假设导入数据

        

site_id	data	city	pv
A	2024-09-03	C1	1
A	2024-09-03	C1	2
B	2024-09-04	C1	3
B	2024-09-04	C1	4

在聚合模型中，以上四条数据会聚合为两条数据。

site_id	data	city	pv
A	2024-09-03	C1	3
B	2024-09-04	C1	7

主键表：

create table orders (
    dt date NOT NULL,
    order_id bigint NOT NULL,
    user_id int NOT NULL,
    merchant_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
) PRIMARY KEY (dt, order_id)
PARTITION BY RANGE(`dt`) (
    PARTITION p20210820 VALUES [('2021-08-20'), ('2021-08-21')),
    PARTITION p20210821 VALUES [('2021-08-21'), ('2021-08-22')),
    PARTITION p20210929 VALUES [('2021-09-29'), ('2021-09-30')),
    PARTITION p20210930 VALUES [('2021-09-30'), ('2021-10-01'))
) DISTRIBUTED BY HASH(order_id)
PROPERTIES (
    "replication_num" = "3",
    "enable_persistent_index" = "true"
);

主键支持以下数据类型：BOOLEAN、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、DATE、DATETIME、VARCHAR/STRING。并且不允许为 NULL。分区列和分桶列必须在主键中。

适用场景：

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

四：分区设置

动态分区相关属性 PROPERTIES：

参数	是否必填	说明
dynamic_partition.enable	否	开启动态分区特性，取值为 true（默认）或 false。
dynamic_partition.time_unit	是	动态分区的时间粒度，取值为 HOUR、DAY、WEEK、MONTH 或 YEAR。时间粒度会决定动态创建的分区名后缀格式。 取值为 HOUR 时，分区列的类型仅支持 DATETIME，不支持为 DATE。动态创建的分区名后缀格式为 yyyyMMddHH，例如 2020032101。 取值为 DAY 时，动态创建的分区名后缀格式为 yyyyMMdd，例如 20200321。 取值为 WEEK 时，动态创建的分区名后缀格式为 yyyy_ww，例如 2020_13 代表 2020 年第 13 周。 取值为 MONTH 时，动态创建的分区名后缀格式为 yyyyMM，例如 202003。 取值为 YEAR 时，动态创建的分区名后缀格式为 yyyy，例如 2020。
dynamic_partition.time_zone	否	动态分区的时区。默认与系统时区一致。
dynamic_partition.start	否	保留的动态分区的起始偏移，取值范围为负整数。根据 dynamic_partition.time_unit 属性的不同，以当天（周/月）为基准，分区范围在此偏移之前的分区将会被删除。比如设置为-3，并且dynamic_partition.time_unit为day，则表示 3 天前的分区会被删掉。 如果不填写，则默认为 Integer.MIN_VALUE，即 -2147483648，表示不删除历史分区。
dynamic_partition.end	是	提前创建的分区数量，取值范围为正整数。根据 dynamic_partition.time_unit 属性的不同，以当天（周/月）为基准，提前创建对应范围的分区。
dynamic_partition.prefix	否	动态分区的前缀名，默认值为 p。
dynamic_partition.buckets	否	动态分区的分桶数量。默认与 BUCKETS 保留字指定的分桶数量、或者 StarRocks 自动设置的分桶数量保持一致。
dynamic_partition.history_partition_num	否	动态分区的创建历史分区的个数，默认值为 0。当值>0时会提前创建历史分区。自 2.5.2 版本起，StarRocks 支持配置该参数。
dynamic_partition.start_day_of_week	否	当 dynamic_partition.time_unit 为 WEEK 时，该参数指定每周的第一天。有效值为 1 到 7。1 表示星期一，7 表示星期日。默认值为 1，表示每周从星期一开始。
dynamic_partition.start_day_of_month	否	当 dynamic_partition.time_unit 为 MONTH 时，该参数指定每月的第一天。有效值为 1 到 28。1 表示每月的第一天，28 表示每月的第 28 天。默认值为 1，表示每月从第一天开始。每月的第一天不支持为 29 日、30 日或 31 日。
dynamic_partition.replication_num	否	在动态创建的分区中，每个 tablet 副本的数量。默认值与建表时配置的副本数量相同。