第2.2章：StarRocks表设计--数据模型_starrocks 自增主键

为了全面的满足企业业务上的分析需求，StarRocks当前设计了四种数据模型：明细模型、聚合模型、更新模型、主键模型。

1明细模型

明细模型是StarRocks中最常用的数据模型，顾名思义，它会保留所有的明细数据，也就是说，在明细模型下，即便导入两条完全相同的数据，StarRocks也会将数据原封不动的保存进表，不会进行聚合的操作，也没有update的语义。

基于明细模型的特点，我们不难发现，明细模型通常用于追加式的数据写入，比较适合：

1、需要保留原始数据的业务；

2、查询维度不固定的业务；

3、数据产生后就不会发生太多变化的业务。

还是举一个简单的例子，在starrocks库中创建明细模型表table02：

CREATE TABLE IF NOT EXISTS starrocks.table02 (

    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",

    channel INT COMMENT ""

)

DUPLICATE KEY(event_time, event_type,user_id)

DISTRIBUTED BY HASH(user_id) BUCKETS 10;

这里我们使用DUPLICATE KEY(event_time, event_type, user_id)显式的说明采用明细模型，并且指定排序键为event_time、event_type和user_id。排序键是指该表中数据存储时多重排序的次序，具体概念我们后面再讨论，这里先注意：

1、若在数据列中我们未定义聚合函数（例如2.1章table01的total列），则默认采用明细模型；

2、明细模型可以使用DUPLICATE KEY(列1,列2……)显式的说明使用明细模型，也可以整个省略；

3、若显式指定使用明细模型，则DUPLICATE KEY(列1,列2……)中，排序键“列1、列2……”需要是建表语句中的前几列，且顺序需要和建表语句中的保持一致（例如table02中的event_time, event_type和table01中的user_id, device_code,device_price,event_time），否则建表会报错；

4、在省略DUPLICATE KEY(列1,列2……)时，StarRocks通常也会默认为表选择前三列作为排序键。

所以，table02的建表语句也可以简写为：

CREATE TABLE IF NOT EXISTS starrocks.table02 (

    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",

    channel INT COMMENT ""

)

DISTRIBUTED BY HASH(user_id);

我们对table02导入几条数据：

mysql> insert into table02 values('2021-03-11',27,1001,01);

mysql> insert into table02 values('2021-03-11',27,1001,01);

mysql> insert into table02 values('2021-03-12',31,1002,02);

mysql> select * from table02;

+------------------------------+-------------------+-------------+--------------+

| event_time                   | event_type     | user_id     | channel   |

+------------------------------+-------------------+-------------+--------------+

| 2021-03-12 00:00:00   |         31           |    1002     |       2         |

| 2021-03-11 00:00:00   |         27           |    1001     |       1         |

| 2021-03-11 00:00:00   |         27           |    1001     |       1         |

+------------------------------+-------------------+-------------+--------------+

可以发现，虽然导入的前两条数据完全相同，明细模型仍将其视为两条单独数据完整的保存进表。

2聚合模型

在上一篇概述章节我们已经举了一个聚合模型的例子，准确来说，聚合模型会在数据导入时将维度列相同的数据根据指标列设定的聚合函数进行聚合，最终表格中只会保留聚合后的数据。聚合模型可以减少查询时需要处理的数据量，进而提升查询的效率，其适合的场景如下：

1、不需要原始的明细数据，只关注汇总的结果；

2、业务涉及的查询为汇总类查询，比如sum、min、max、count等类型的查询；

3、查询维度固定。

再举一个聚合模型的例子，在starrocks库中建表table03：

CREATE TABLE IF NOT EXISTS starrocks.table03 (

    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",

    state INT COMMENT "web state",

    pv BIGINT SUM DEFAULT "0" COMMENT "total page views"

)

DISTRIBUTED BY HASH(site_id) BUCKETS 8;

前面提到过，在建表时只要给指标列的定义指明聚合函数，就会启用聚合模型，所以这里我们省略了AGGREGATE KEY(site_id, date, city_code, state)。因为是聚合模型，这里的AGGREGATE KEY()要么整个省略，要么在小括号中给排序键按建表顺序指定所有的维度列，也即table03中没有聚合函数的前四列。在创建完成后，我们可以使用show create table语句查看表的创建语句，会发现是一样的。

在聚合模型中，StarRocks还支持聚合函数REPLACE_IF_NOT_NULL。这个聚合类型的含义是当且仅当新导入数据是非NULL值时会发生替换行为，如果新导入的数据是NULL，那么StarRocks仍然会保留原值。我们可以借助这种聚合类型实现部分列导入的业务。

3更新模型

明细模型会将所有写入的数据保留，聚合模型是对写入的数据进行聚合处理，而更新模型的特点是只保留相同主键下最新导入的数据。在更新模型中，排序键构成表的唯一性约束，成为我们常说的“主键”。

明细模型和聚合模型都不适合处理数据频繁更新的场景。比如在电商场景中，订单的状态经常会发生变化，每天的订单更新量可达到上亿，若在明细模型下通过delete+insert的方式实现更新，效率是比较低的，更新模型就是针对这种场景的实时数据分析设计的。

这里我们创建一个订单类的表table04：

CREATE TABLE IF NOT EXISTS starrocks.table04 (

    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;

不同于明细模型和聚合模型，这里的UNIQUE KEY(create_time, order_id)不能省略，且其中的排序键“create_time+order_id”构成了唯一主键，主键相同的数据，StarRocks只会保留最后导入的那一条。

逻辑上，更新模型也可以理解为一种特殊的聚合模型。以table04为例，create_time和order_id为维度列，order_state和total_price为指标列，指标列聚合类型为REPLACE。

为方便理解，我们为table04导入三条数据：

mysql> insert into table04 values('2021-03-11',20210311001,01,100);

mysql> insert into table04 values('2021-03-11',20210311001,02,200);

mysql> insert into table04 values('2021-03-12',20210312001,01,100);

前两条数据的主键相同，指标列不同，根据更新模型特点，我们推测出数据库中最终的数据应该是只保留第二条和第三条，导入完成后，我们查看数据：

mysql> select * from table04;

+---------------------+-------------------+--------------------+---------------------+

| create_time       | order_id          | order_state      |  total_price       |

+---------------------+-------------------+--------------------+---------------------+

| 2021-03-11        | 20210311001 |           2            |         200           |

| 2021-03-12        | 20210312001 |           1           |         100            |

+---------------------+-------------------+--------------------+---------------------+

数据情况符合预期。

这里还需要注意，更新模型的“更新”，并不是说其支持update，而是通过追加式的数据写入，用新的数据去覆盖主键相同的历史数据，从而实现更新。同时，更新模型在导入数据时需要将所有字段补全才能够完成更新操作。

更新模型建表时，我们通常将需要经常被过滤且不会被修改的字段放在“主键”上来提升查询性能。同时，我们也需要避免放置过多的“主键”字段，因为数据导入时的合并过程需要对所有“主键”字段进行比较，当“主键”字段较多时，整体的查询性能就会下降。假如某个字段只是偶尔会作为查询中的过滤条件存在，不需要放在“主键”中。

对于更新模型的数据读取，因为需要在查询时完成多版本合并，当版本过多时会导致查询性能降低。所以在向更新模型导入数据时，应该适当降低导入频率，从而提升查询性能。建议在设计导入频率时以满足业务对实时性的要求为准。如果业务对实时性的要求是分钟级别，那么每分钟导入一次更新数据即可，不需要秒级导入。

4主键模型

明细模型、聚合模型和更新模型都不支持update语法，为实现update功能，StarRocks又自研了主键模型：primary key。

主键模型与更新模型的特点比较接近，主键模型的表要求有唯一的主键，支持对表中的行按主键进行更新和删除操作。但对比更新模型，主键模型通过牺牲微小的写入性能和内存占用，极大提升了查询性能。同时，主键模型可以更好地支持实时/频繁更新的功能，特别适合MySQL或其他数据库同步到StarRocks的场景。以table05为例：

create table starrocks.table05 (

    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 p20210929 VALUES [('2021-09-29'), ('2021-09-30')),

    PARTITION p20210930 VALUES [('2021-09-30'), ('2021-10-01'))

) DISTRIBUTED BY HASH(order_id) BUCKETS 4

PROPERTIES("replication_num" = "3");

主键模型当前也还不直接支持update语法（后续会逐步支持），目前使用主键模型的限制也相对较多：

1、主键列仅支持类型: boolean、tinyint、smallint、int、bigint、largeint、string/varchar、date和datetime，不允许NULL；

2、分区列（partition）、分桶列（bucket）必须在主键列中；

3、和更新模型不同，主键模型允许为非主键列创建bitmap等索引，但需要建表时指定；

4、由于其列值可能会更新，主键模型目前还不支持rollup index和物化视图；

5、Alter table目前仅支持添加/删除列，还不支持更改列类型和添加删除索引等操作；

6、在设计表时应尽量减少主键的列数和大小以节约内存，建议使用int/bigint等占用空间少的类型。暂时不建议使用varchar。建议提前根据表的行数和主键列类型来预估内存使用量，避免出现OOM。内存估算举例：

a. 假设表的主键为: dt date (4byte), id bigint(8byte) = 12byte

b. 假设热数据有1000W行, 存储3副本

c. 内存占用计算：(12 + 9(每行固定开销) ) * 1000W * 3 * 1.5(hash表平均额外开销) = 945M

7、目前主键模型仍只支持整行更新，还不支持部分列更新。

在1.19版本中，主键模型是作为“实验功能”发布的，由于存储引擎会为主键建立索引，而在导入数据时会把主键索引加载在内存中，所以主键模型对内存的要求比较高，后续版本也会不断的优化内存占用。目前推荐使用的场景有：

1、数据有冷热特征，比如最近几天的热数据才经常被修改，老的冷数据很少被修改（老的业务数据导入完成后不再更新，新数据导入时老数据的主键索引就不会加载，也就不会占用内存，内存中仅会加载最近几天的索引）；

2、大宽表（数百到数千列）。主键只占整个数据的很小一部分，其内存开销比较低。

主键模型的“插入/更新/删除”目前只支持使用“导入”的方式完成，通过SQL语句(insert/update/delete)来操作数据的功能会在未来版本中支持。目前支持的导入方式有stream load、broker load、routine load、Json数据导入，Spark load还未支持。

关于主键模型的数据导入，官网文档中介绍的非常详细，这里不再赘述，文档地址：

数据模型介绍 @ Data_model @ StarRocks Docshttps://docs.starrocks.com/zh-cn/main/table_design/Data_model#%E6%8F%92%E5%85%A5%E6%9B%B4%E6%96%B0%E5%88%A0%E9%99%A4%E6%93%8D%E4%BD%9C

特别注意：不要在1.19之前的版本中使用主键模型表，虽然可以创建成功，但功能无法正常使用。