深入了解 StarRocks 表类型：解锁高效数据分析的密码

在当今数字化浪潮下，大数据分析成为企业决策、优化业务流程的关键利器。StarRocks 作为一款备受瞩目的高性能分析型数据库，其多样化的表类型为复杂的数据处理需求提供了精准解决方案。今天，就让我们一同深入探索 StarRocks 中的主键表、明细表、聚合表和更新表，从工作原理、读写流程、适用场景、标准建表语句等多个维度全面剖析，助您开启高效的大数据分析之旅。

表类型

StarRocks 支持四种表类型，分别是明细表 (Duplicate key table)、聚合表 (Aggregate table)、更新表 (Unique Key table) 和主键表 ( Primary Key table)。这四种表类型能够支持多种数据分析场景，例如日志分析、数据汇总分析、实时分析等。

排序键

数据导入至使用某个类型的表，会按照建表时指定的一列或多列排序后存储，这部分用于排序的列就称为排序键。排序键通常为查询时过滤条件频繁使用的一个或者多个列，用以加速查询。 明细表中，数据按照排序键 DUPLICATE KEY 排序，并且排序键不需要满足唯一性约束。 聚合表中，数据按照排序键 AGGREGATE KEY 聚合后排序，并且排序键需要满足唯一性约束。 更新表中，数据按照排序键 UNIQUE KEY REPLACE 后排序，并且排序键需要满足唯一性约束。 主键表支持分别定义主键和排序键，主键 PRIMARY KEY 需要满足唯一性和非空约束，主键相同的数据进行 REPLACE。排序键是用于排序，由 ORDER BY 指定 。

TIPS：

• 建表后，表类型不支持修改（比如不能将已有的明细表修改为主键表）。如果需要修改表类型，请重新建表。

• 在建表语句中，排序键必须定义在其他列之前。

• 在创建表时，您可以将一个或多个列定义为排序键。排序键在建表语句中的出现次序，为数据存储时多重排序的次序。

• 不支持排序键的数据类型为 BITMAP、HLL。

• 前缀索引的长度限制为 36 字节。如果排序键中全部列的值的长度加起来超过 36 字节，则前缀索引仅会保存限制范围内排序键的若干前缀列。

• 如果导入的数据存在重复的主键，则数据导入至不同类型的表时，存储在 StarRocks 时，则会按照如下方式进行处理：

  1. 明细表：表中会存在主键重复的数据行，并且与导入的数据是完全对应的。您可以召回所导入的全部历史数据。
  2. 聚合表：表中不存在主键重复的数据行，主键满足唯一性约束。导入的数据中主键重复的数据行聚合为一行，即具有相同主键的指标列，会通过聚合函数进行聚合。您只能召回导入的全部历史数据的聚合结果，但是无法召回历史明细数据。
  3. 主键表和更新表：表中不存在主键重复的数据行，主键满足唯一性约束。最新导入的数据行，替换掉其他主键重复的数据行。这两种类型的表可以视为聚合表的特殊情况，相当于在聚合表中，为表的指标列指定聚合函数为 REPLACE，REPLACE 函数返回主键相同的一组数据中的最新数据。

主键表

主键表使用 StarRocks 全新设计开发的存储引擎。其主要优势在于支撑实时数据更新的同时，也能保证高效的复杂即席查询性能。在实时分析业务中采用主键表，用最新的数据实时分析出结果来指导决策，使得数据分析不再受限于 T+1 数据延迟。

主键表中的主键具有唯一非空约束，用于唯一标识数据行。如果新数据的主键值与表中原数据的主键值相同，则存在唯一约束冲突，此时新数据会替代原数据。

原理

更新表和聚合表整体上采用了 Merge-On-Read 的策略。虽然写入时处理简单高效，但是读取时需要在线 Merge 多个版本的数据文件。并且由于 Merge 算子的存在，谓词和索引无法下推至底层数据，会严重影响查询性能。

然而为了兼顾实时更新和查询性能，主键表的元数据组织、读取、写入方式完全不同。主键表采用了 Delete+Insert 策略，借助主键索引配合 DelVector 的方式实现，保证在查询时只需要读取具有相同主键值的数据中的最新数据。如此可以避免 Merge 多个版本的数据文件，并且谓词和索引可以下推到底层数据，所以可以极大提升查询性能。

主键表中写入和读取数据的整体流程如下：

• 数据写入是通过 StarRocks 内部的 Loadjob 实现，包含一批数据变更操作（包括 Insert、Update、Delete）。StarRocks 会加载导入数据对应 Tablet 的主键索引至内存中。对于 Delete 操作，StarRocks 先通过主键索引找到数据行原来所在的数据文件以及行号，在 DelVector（用于存储和管理数据导入时生成数据行对应的删除标记）中把该条数据标记为删除。对于 Update 操作，StarRocks 除了在 DelVector 中将原先数据行标记为删除，还会把最新数据写入新的数据文件，相当于把 Update 改写为 Delete+Insert（如下图所示）。并且会更新主键索引中变更的数据行现在所在的数据文件和行号。
• 读取数据时，由于写入数据时各个数据文件中历史重复数据已经标记为删除，同一个主键值下仅需要读取最新的一条数据，无需在线 Merge 多个版本的数据文件来去重以找到最新的数据。扫描底层数据文件时借助过滤算子和各类索引，可以减少扫描开销（如下图所示），所以查询性能的提升空间更大。并且相对于 Merge-On-Read 策略的更新表，主键表的查询性能能够提升 3~10 倍。

使用说明

使用CREATE TABLE 语句通过 PRIMARY KEY 定义主键，即可创建一个主键表。

-- 定义一个主键表--订单
CREATE TABLE orders2 (order_id bigint NOT NULL,dt date NOT NULL,merchant_id int NOT NULL,user_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 (order_id,dt,merchant_id) -- 主键/组合主键，每个订单的组合必须是唯一的
PARTITION BY RANGE(dt) (START ('1970-01-01') END ('2099-12-31') EVERY (INTERVAL 1 DAY)
) -- 根据订单日期的天进行分区，以便于管理和查询。
DISTRIBUTED BY HASH (merchant_id)  -- 由于主键表仅支持分桶策略为哈希分桶，因此您还必须通过 DISTRIBUTED BY HASH () 定义哈希分桶键。提高查询性能和负载均衡。
ORDER BY (dt,merchant_id) -- 经常根据订单日期和商户组合维度查询，可指定排序键/组合键，助于优化查询性能
PROPERTIES ("enable_persistent_index" = "true"
); -- 启用持久化索引 默认：true

主键用于唯一标识表中的每一行数据，组成主键的一个或多个列在 PRIMARY KEY 中定义，具有非空唯一性约束。其注意事项如下：

• 在建表语句中，主键列必须定义在其他列之前。
• 主键必须包含分区列和分桶列。
• 主键列支持以下数据类型：数值（包括整型和布尔）、日期和字符串。
• 默认设置下，单条主键值编码后的最大长度为 128 字节。
• 建表后不支持修改主键。
• 主键列的值不能更新，避免破坏数据一致性。
• 如果指定了排序键，就根据排序键构建前缀索引；如果没指定排序键，就根据主键构建前缀索引。
• 建表后支持通过 ALTER TABLE ... ORDER BY ... 修改排序键。不支持删除排序键，不支持修改排序列的数据类型。

适用场景

键表能够在支撑实时数据更新的同时，也能保证高效的查询性能。可适用于如下场景：

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

明细表

明细表是默认创建的表类型。如果在建表时未指定任何 key，默认创建的是明细表。

创建表时，支持定义排序键。如果查询的过滤条件包含排序键，则 StarRocks 能够快速地过滤数据，提高查询效率。明细表适用于日志数据分析等场景，支持追加新数据，不支持修改历史数据。

使用说明

需要分析某时间范围的某一类事件的数据，则可以将事件时间（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 code",channel INT COMMENT ""
)
DUPLICATE KEY(event_time, event_type) -- 重复键（Duplicate Key），这意味着这些列的组合可以重复，但数据会被存储在不同的副本中以提高查询性能
DISTRIBUTED BY HASH(user_id) BUCKETS 16 -- 必须使用 DISTRIBUTED BY HASH 子句指定分桶键，否则建表失败，自 2.5.7 版本起，StarRocks 支持在建表和新增分区时自动设置分桶数量 (BUCKETS)，您无需手动设置分桶数量
PROPERTIES (
"replication_num" = "3"
); -- 数据副本的数量为 3，这有助于提高数据的可靠性和可用性.

排序键的相关说明：

• 在建表语句中，排序键必须定义在其他列之前。

• 排序键可以通过 DUPLICATE KEY 显式定义。本示例中排序键为 event_time 和 event_type，如果未指定，则默认选择表的前三列作为排序键。

• 建表时，支持为指标列创建 BITMAP、Bloom Filter 等索引。

• -- Bitmap 索引适用于等值查询、IN 查询、范围查询等，特别适合于基数较低的列
  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 "channel",INDEX event_type_index (event_type) USING BITMAP COMMENT "Bitmap index on event_type" -- 创建 Bitmap 索引
  )
  DUPLICATE KEY(event_time, event_type)
  DISTRIBUTED BY HASH(user_id) BUCKETS 16
  PROPERTIES ("replication_num" = "3"
  );-- Bloom Filter 索引适用于基数较高的列，例如 ID 列，但存在一定的误判率
  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 "channel",INDEX user_id_index (user_id) USING BLOOMFILTER COMMENT "Bloom Filter index on user_id" -- 创建 Bloom Filter 索引
  )
  DUPLICATE KEY(event_time, event_type)
  DISTRIBUTED BY HASH(user_id) BUCKETS 16
  PROPERTIES ("replication_num" = "3"
  );

  适用场景​

• 分析原始数据，例如原始日志、原始操作记录等。
• 查询方式灵活，不需要局限于预聚合的分析方式。
• 导入日志数据或者时序数据，主要特点是旧数据不会更新，只会追加新的数据。

聚合表

建表时，支持定义排序键和指标列，并为指标列指定聚合函数。当多条数据具有相同的排序键时，指标列会进行聚合。在分析统计和汇总数据时，聚合表能够减少查询时所需要处理的数据，提升查询效率。

原理

从数据导入至数据查询阶段，聚合表内部同一排序键的数据会多次聚合，聚合的具体时机和机制如下：

1. 数据导入阶段：数据按批次导入至聚合表时，每一个批次的数据形成一个版本。在一个版本中，同一排序键的数据会进行一次聚合。

2. 后台文件合并阶段 (Compaction) ：数据分批次多次导入至聚合表中，会生成多个版本的文件，多个版本的文件定期合并成一个大版本文件时，同一排序键的数据会进行一次聚合。

3. 查询阶段：所有版本中同一排序键的数据进行聚合，然后返回查询结果。

因此，聚合表中数据多次聚合，能够减少查询时所需要的处理的数据量，进而提升查询的效率。

使用说明

需要分析某一段时间内，来自不同城市的用户，访问不同网页的总次数。

-- 将网页地址 site_id、日期 date 和城市代码 city_code 作为排序键，将访问次数 pv 作为指标列，并为指标列 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" -- 指标列，列名后指定聚合函数：SUM
)
AGGREGATE KEY(site_id, date, city_code) -- 定义site_id、date city_code 作为排序键，亦是聚合维度
DISTRIBUTED BY HASH(site_id) -- 必须使用 DISTRIBUTED BY HASH 子句指定分桶键
PROPERTIES (
"replication_num" = "3"
); -- 三副本

排序键的相关说明：

• 在建表语句中，排序键必须定义在其他列之前。

• 排序键可以通过 AGGREGATE KEY 显式定义。如果 AGGREGATE KEY 未包含全部维度列（除指标列之外的列），则建表会失败，如果不通过 AGGREGATE KEY 显示定义排序键，则默认除指标列之外的列均为排序键

• 排序键/组合键必须满足唯一性约束，必须包含全部维度列，并且列的值不会更新。

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

• 聚合函数：指标列使用的聚合函数。聚合表支持的聚合函数，请参见 CREATE TABLE。

• 查询时，排序键在多版聚合之前就能进行过滤，而指标列的过滤在多版本聚合之后。因此建议将频繁使用的过滤字段作为排序键，在聚合前就能过滤数据，从而提升查询性能。

• 建表时，不支持为指标列创建 BITMAP、Bloom Filter 等索引。

适用场景

适用于分析统计和汇总数据。比如:

• 通过分析网站或 APP 的访问流量，统计用户的访问总时长、访问总次数。

• 广告厂商为广告主提供的广告点击总量、展示总量、消费统计等。

• 通过分析电商的全年交易数据，获得指定季度或者月份中，各类消费人群的爆款商品。

在这些场景中，数据查询和导入，具有以下特点：

• 多为汇总类查询，比如 SUM、MAX、MIN等类型的查询。

• 不需要查询原始的明细数据。

• 旧数据更新不频繁，只会追加新的数据。

更新表

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

原理

更新表可以视为聚合表的特殊情况，指标列指定的聚合函数为 REPLACE，返回具有相同主键的一组数据中的最新数据。

数据分批次多次导入至更新表，每一批次数据分配一个版本号，因此同一主键的数据可能有多个版本，查询时返回版本最新（即版本号最大）的数据。相对于明细表，更新表通过简化导入流程，能够更好地支持实时和频繁更新。

如下数据在更新表中的表现：

ID	value	_version
1	100	1
1	101	2
2	100	3
2	101	4
2	102	5

最终查询结果如下：

ID	value
1	101
2	102

使用说明

在电商订单分析场景中，经常按照日期对订单状态进行统计分析，则可以将经常使用的过滤字段订单创建时间 create_time、订单编号 order_id 作为主键，其余列订单状态 order_state 和订单总价 total_price 作为指标列。这样既能够满足实时更新订单状态的需求，又能够在查询中进行快速过滤。

-- 该业务场景下，建表语句如下
CREATE TABLE IF NOT EXISTS orders (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 16 -- 必须使用 DISTRIBUTED BY HASH 子句指定分桶键，2.5.7 版本起，StarRocks 支持在建表和新增分区时自动设置分桶数量 (BUCKETS)
PROPERTIES (
"replication_num" = "3"
); 

主键的相关说明：

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

适用场景

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

优劣对比

对比项	主键表 (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 和分区/分桶列的关系	分区列、分桶列必须在主键中。	无	分区列、分桶列必须在聚合键中。	分区列、分桶列必须在唯一键中。

补充

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

第一层级为分区。表中数据可以根据分区列（通常是时间和日期）分成一个个更小的数据管理单元。查询时，通过分区裁剪，可以减少扫描的数据量，显著优化查询性能。StarRocks 提供简单易用的分区方式，即表达式分区。此外还提供较灵活的分区方式，即 Range 分区和 List 分区。

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

StarRocks 提供两种分桶方式：

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

为保证确保数据的完整性、一致性和准确性。主键表的 Primary Key 列具有唯一非空约束，聚合表的 Aggregate Key 列和更新表的 Unique Key 列具有唯一约束。

结束语

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