第2.1章：StarRocks表设计--概述

关注

原创 2023-03-31 15:42:42 657 阅读 AI 写同款· GEO 优化 ›

第2.2章 StarRocks表设计——排序键和数据模型 2.2 StarRocks表设计——排序键和数据模型阅读详情

建表是使用StarRocks非常重要的一环，规范的表设计在某些场景下甚至能使查询性能有数倍的提升。StarRocks的表设计涉及到的知识点主要包括数据模型选择、分区分桶、排序键和前缀索引、列级别索引以及字段类型等等。

StarRocks的建表语句与MySQL比较相似，但也有自己的特点。我们先在StarRocks中创建一个演示用的数据库starrocks（后续所有的建表咱们都在里面进行），然后用比较规范的建表语句创建表table01。这里的建表语句我们只是先把涉及的点覆盖到，后面会逐项展开说明：

mysql> create database starrocks;

mysql> use starrocks;

mysql> CREATE TABLE table01 (

user_id INT COMMENT "id of user",

device_code INT COMMENT "code of device",

device_price DECIMAL(10,2) COMMENT "",

event_time DATETIME NOT NULL COMMENT "datetime of event",

total DECIMAL(18,2) SUM DEFAULT "0" COMMENT "total amount of equipment",

index index01 (user_id) USING BITMAP COMMENT "bitmap index"

)

AGGREGATE KEY(user_id, device_code,device_price,event_time)

PARTITION BY RANGE(event_time)

(

PARTITION p1 VALUES LESS THAN ('2021-01-31'),

PARTITION p2 VALUES LESS THAN ('2021-02-28'),

PARTITION p3 VALUES LESS THAN ('2021-03-31')

)

DISTRIBUTED BY HASH(user_id,device_code) BUCKETS 20

PROPERTIES (

"replication_num" = "1",

"bloom_filter_columns"="event_time"

);

在建表完成后，我们可以用show create table语句查看建表语句：

show create table table01;

也可以使用desc命令查看表结构：

desc table01;

或者：

SHOW FULL COLUMNS FROM table01;

table01是一个简单的聚合模型分区表，语句乍一看比较长，我们将其拆分为五部分来研究：

第一部分：

CREATE TABLE table01 (

user_id INT COMMENT "id of user",

device_code INT COMMENT "code of device",

device_price DECIMAL(10,2) COMMENT "",

event_time DATETIME NOT NULL COMMENT "datetime of event",

total DECIMAL(18,2) SUM DEFAULT "0" COMMENT " total amount of equipment",

…………

这里是列创建部分，与MySQL语法较为相似。我们创建了五列user_id、device_code、device_price、event_time和total。每列的语法为：

col_name col_type [agg_type] [NULL | NOT NULL] [DEFAULT "default_value"] COMMENT " xxxxxx"

这里的前四列我们可以称之为维度列，第五列total列为指标列，我们为其设置了聚合类型sum。所以在table01中，前四列相同的数据在导入StarRocks时，指标列total会根据聚合类型sum自动聚合。

第二部分：

…………

index index01 (user_id) USING BITMAP COMMENT 'bitmap index'

)

…………

这部分是创建索引，我们为user_id列创建了名为index01的bitmap索引。当前这种写法仅支持BITMAP索引，创建语法为：

INDEX index_name (col_name1[, col_name2, ...]) [USING BITMAP] COMMENT 'xxxxxx'

bitmap索引我们也可以在建表完成后再创建，这里暂不展开。

第三部分：

…………

AGGREGATE KEY(user_id, device_code,device_price,event_time)

…………

这里是为聚合模型显式的定义排序键，在为聚合模型定义排序键时，需要把所有的维度列按照建表的顺序都写上。在StarRocks聚合模型建表时，只要给指标列定义指明聚合函数，就会自动启用聚合模型，所以这部分大多数情况下其实也可以省略不写。

排序键中的列需要与建表语句中列的顺序一致，我们可以从头选取几个列作为排序键。在省略这部分时，StarRocks也会自动选择表结构前面的几列作为排序列，所以通常我们需要把经常作为查询条件的列作为排序键写在建表语句靠前部分以加速查询。

第四部分：

…………

PARTITION BY RANGE(event_time)

(

PARTITION p1 VALUES LESS THAN ('2021-01-31'),

PARTITION p2 VALUES LESS THAN ('2021-02-28'),

PARTITION p3 VALUES LESS THAN ('2021-03-31')

)

DISTRIBUTED BY HASH(user_id,device_code) BUCKETS 20

…………

这里是为table01指定分区和分桶，我们按照event_time根据日期分为三个区，然后按照user_id和device_code将每个分区数据根据hash分散到20个桶中，最终会将数据切分为子表tablet分布在桶中。

分区的主要目的是裁剪数据，来最大限度地减少扫描数据量，从而提高查询性能。通常我们会使用日期进行分区。这里如果不进行分区，StarRocks会将整个表的数据视为在一个大分区内。

分桶是将分区后的数据打散为一个个tablet，使数据多副本分散在集群的多个节点上，充分发挥集群多机多核的查询优势。为了保证数据不倾斜，我们会根据业务选择一列或者多列进行分桶。如果这里不指定分桶数，比如只写为“DISTRIBUTED BY HASH(user_id,device_code)”，StarRocks会默认将数据分为10个桶。

第五部分：

…………

PROPERTIES (

"replication_num" = "1",

"bloom_filter_columns"="event_time"

);

这部分用来指定表的一些其他属性，比如副本数、存储介质、动态分区、bloom_filter索引等等。例如这里的bloom_filter_columns为BloomFilter索引，replication_num为副本数。我们将table01的数据副本指定为了1，而在不指定时，StarRocks默认为三副本数据（这里的副本数与ElasticSearch等的含义不同，StarRocks的1副本意思是仅有一份数据，三副本为共有三份数据）。提前说明一下，在默认情况下，这里的属性是针对全表的，但是，StarRocks也支持针对分区级别单独设置一些属性，这里咱们也暂不展开。

为方便展示聚合模型的特性，我们导入三条数据：

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

mysql> insert into table01 values(1001,27,9.99,'2021-03-11',10.99);

mysql> insert into table01 values(1002,28,15.49,'2021-03-12',15.49);

mysql> select * from table01;

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

| user_id | device_code | device_price | event_time | total |

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

| 1001 | 27 | 9.99 | 2021-03-11 00:00:00 | 20.98 |

| 1002 | 28 | 15.49 | 2021-03-12 00:00:00 | 15.49 |

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

查询发现表中只有两条数据，这是因为导入的前两条数据由于前四列（维度列）的值相同，在导入时自动聚合，对第五列（指标列）进行了sum求和运算。

以上就是StarRocks聚合模型的简单例子，后续，我们会针对StarRocks的建表进行展开介绍。

收录于