数据模型
排序键和索引前缀
数据分布
物化视图
Colocate Join
Lateral Join
Bitmap 索引
Bloomfilter 索引
使用 Bitmap 实现精确去重
使用 HyperLogLog 实现近似去重
starrocks性能优化
数据模型
明细模型
明细模型是StarRocks中最常用的数据模型，顾名思义，它会保留所有的明细数据，也就是说，在明细模型下，即便导入两条完全相同的数据，StarRocks也会将数据原封不动的保存进表，不会进行聚合的操作，也没有update的语义。
基于明细模型的特点，我们不难发现，明细模型通常用于追加式的数据写入，比较适合：
1、需要保留原始数据的业务；
2、查询维度不固定的业务；
3、数据产生后就不会发生太多变化的业务。
创建明细模型表
例如，需要分析某时间范围的某一类事件的数据，则可以将事件时间（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)DISTRIBUTED BY HASH(user_id) BUCKETS 8;
建表时必须使用
DISTRIBUTED BY HASH
子句指定分桶键。分桶键的更多说明，请参见分桶。
使用说明
排序键的相关说明：
在建表语句中，排序键必须定义在其他列之前。
排序键可以通过
DUPLICATE KEY
显式定义。本示例中排序键为event_time
和event_type
。如果未指定，则默认选择表的前三列作为排序键。
明细模型中的排序键可以为部分或全部维度列。
建表时，支持为指标列创建 BITMAP、Bloom Filter 等索引。
聚合模型
聚合模型会在数据导入时将维度列相同的数据根据指标列设定的聚合函数进行聚合，最终表格中只会保留聚合后的数据。聚合模型可以减少查询时需要处理的数据量，进而提升查询的效率，其适合的场景如下：
1、不需要原始的明细数据，只关注汇总的结果；
2、业务涉及的查询为汇总类查询，比如sum、min、max、count等类型的查询；
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")DISTRIBUTED BY HASH(site_id) BUCKETS 8;
建表时必须使用
DISTRIBUTED BY HASH
子句指定分桶键。分桶键的更多说明，请参见分桶。
使用说明
排序键的相关说明：
如果
AGGREGATE KEY
未包含全部维度列（除指标列之外的列），则建表会失败。如果不通过
AGGREGATE KEY
显示定义排序键，则默认除指标列之外的列均为排序键。在建表语句中，排序键必须定义在其他列之前。
排序键可以通过
AGGREGATE KEY
显式定义。排序键必须满足唯一性约束，必须包含全部维度列且列的值不会修改。
指标列：通过在列名后指定聚合函数，定义该列为指标列。一般为需要汇总统计的数据。
聚合函数：指标列使用的聚合函数。聚合模型支持的聚合函数，请参见 CREATE TABLE。
查询时，排序键在多版聚合之前就能进行过滤，而指标列的过滤在多版本聚合之后。因此建议将频繁使用的过滤字段作为排序键，在聚合前就能过滤数据，从而提升查询性能。
建表时，不支持为指标列创建 BITMAP、Bloom Filter 等索引
更新模型
明细模型会将所有写入的数据保留，聚合模型是对写入的数据进行聚合处理，而更新模型的特点是只保留相同主键下最新导入的数据。在更新模型中，排序键构成表的唯一性约束，成为我们常说的“主键”。
建表时，支持定义主键和指标列，查询时返回主键相同的一组数据中的最新数据。相对于明细模型，更新模型简化了数据导入流程，能够更好地支撑实时和频繁更新的场景。
明细模型和聚合模型都不适合处理数据频繁更新的场景。比如在电商场景中，订单的状态经常会发生变化，每天的订单更新量可达到上亿，若在明细模型下通过delete+insert的方式实现更新，效率是比较低的，更新模型就是针对这种场景的实时数据分析设计的。
创建明细模型表
在电商订单分析场景中，经常按照日期对订单状态进行统计分析，则可以将经常使用的过滤字段订单创建时间 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 8;
建表时必须使用
DISTRIBUTED BY HASH
子句指定分桶键。分桶键的更多说明，请参见分桶。
使用说明
主键的相关说明：
查询时，主键在聚合之前就能进行过滤，而指标列的过滤通常在多版本聚合之后，因此建议将频繁使用的过滤字段作为主键，在聚合前就能过滤数据，从而提升查询性能。
聚合过程中会比较所有主键，因此需要避免设置过多的主键，以免降低查询性能。如果某个列只是偶尔会作为查询中的过滤条件，则不建议放在主键中。
在建表语句中，主键必须定义在其他列之前。
主键通过
UNIQUE KEY
定义。主键必须满足唯一性约束，且列的值不会修改。
设置合理的主键。
建表时，不支持为指标列创建 BITMAP、Bloom Filter 等索引。
主键模型
明细模型、聚合模型和更新模型都不支持update语法，为实现update功能，StarRocks又自研了主键模型：primary key。
主键模型与更新模型的特点比较接近，主键模型的表要求有唯一的主键，支持对表中的行按主键进行更新和删除操作。但对比更新模型，主键模型通过牺牲微小的写入性能和内存占用，极大提升了查询性能。同时，主键模型可以更好地支持实时/频繁更新的功能，特别适合MySQL或其他数据库同步到StarRocks的场景，主键模型在查询时不需要执行聚合操作，并且支持谓词和索引下推。
适用场景
主键模型适用于实时和频繁更新的场景，例如：
实时对接事务型数据至 StarRocks。事务型数据库中，除了插入数据外，一般还会涉及较多更新和删除数据的操作，因此事务型数据库的数据同步至 StarRocks 时，建议使用主键模型。通过 Flink-CDC 等工具直接对接 TP 的 Binlog，实时同步增删改的数据至主键模型，可以简化数据同步流程，并且相对于读时合并 (Merge-On-Read) 策略的更新模型，查询性能能够提升 3~10 倍。
利用部分列更新轻松实现多流 JOIN。在用户画像等分析场景中，一般会采用大宽表方式来提升多维分析的性能，同时简化数据分析师的使用模型。而这种场景中的上游数据，往往可能来自于多个不同业务（比如来自购物消费业务、快递业务、银行业务等）或系统（比如计算用户不同标签属性的机器学习系统），主键模型的部分列更新功能就很好地满足这种需求，不同业务直接各自按需更新与业务相关的列即可，并且继续享受主键模型的实时同步增删改数据及高效的查询性能。
主键模型适用于主键占用空间相对可控的场景。这是由于 StarRocks 存储引擎会为主键模型的主键创建索引，并导入时将主键索引加载至内存中，所以相对于其他模型，主键模型对内存的要求比较高。目前主键模型中，主键编码后，占用内存空间上限为 127 字节。
如下两个场景中，主键占用空间相对可控：
数据有冷热特征，即最近几天的热数据才经常被修改，老的冷数据很少被修改。例如，MySQL订单表实时同步到 StarRocks 中提供分析查询。其中，数据按天分区，对订单的修改集中在最近几天新创建的订单，老的订单完成后就不再更新，因此导入时其主键索引就不会加载，也就不会占用内存，内存中仅会加载最近几天的索引。
如图所示，数据按天分区，最新两个分区的数据更新比较频繁。
如图所示，大宽表中排序键只占一小部分，且数据行数不多。
例如，需要按天实时分析订单，则可以将时间
dt
、订单编号order_id
作为主键，其余列为指标列。建表语句如下：主键相关的说明：
假设存在主键模型，排序键为
dt
、id
，数据类型为 DATE（4 个字节）、BIGINT（8 个字节）。则排序键占 12 个字节。假设该表的热数据有 1000 万行，存储为三个副本。
则内存占用的计算方式：
(12 + 9(每行固定开销) ) * 1000W * 3 * 1.5（哈希表平均额外开销) = 945 M在建表语句中，主键必须定义在其他列之前。
主键通过
PRIMARY KEY
定义。主键必须满足唯一性约束，且列的值不会修改。本示例中主键为
dt
、order_id
。支持主键的数据类型为 BOOLEAN、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、STRING、VARCHAR、DATE、DATETIME，且不允许为 NULL。
分区列和分桶列必须在主键中。
合理设置主键的列数和长度，以节约内存。建议主键为占用内存空间较少的数据类型，例如 INT、BIGINT 等，暂时不建议为 VARCHAR。
在建表前，建议根据主键的数据类型和表的行数来预估主键索引占用内存空间，以避免出现内存溢出。以下示例说明主键索引占用内存空间的计算方式：
enable_persistent_index
：是否持久化主键索引，同时使用磁盘和内存存储主键索引，避免主键索引占用过大内存空间。通常情况下，持久化主键索引后，主键索引所占内存为之前的 1/10。您可以在建表时，在PROPERTIES
中配置该参数，取值范围为true
或者false
（默认值）。建表后，如果您需要修改该参数，请参见 ALTER TABLE 修改表的属性 。
主键必须为定长的数据类型（CHAR 除外），不支持为可变长的数据类型（例如 VARCHAR）。
如果磁盘为固态硬盘 SSD，则建议设置为
true
。自 2.3.0 版本起，StarRocks 支持配置该参数。
自2.3.0 版本起，指标列新增支持 BITMAP、HLL 数据类型。
创建表时，支持为指标列创建 BITMAP、Bloom Filter 等索引。
主键模型目前不支持物化视图。
暂不支持使用 ALTER TABLE 修改列类型。ALTER TABLE 的相关语法说明和示例，请参见 ALTER TABLE。
大宽表（数百到数千列）。主键只占整个数据的很小一部分，其内存开销比较低。比如用户状态和画像表，虽然列非常多，但总的用户数不大（千万至亿级别），主键索引内存占用相对可控。
创建表
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) BUCKETS 4PROPERTIES("replication_num" = "3","enable_persistent_index" = "true");
建表时必须使用
DISTRIBUTED BY HASH
子句指定分桶键。分桶键的更多说明，请参见分桶。
使用说明
排序键和索引前缀
排序键：
在明细模型中，排序列就是通过 DUPLICATE KEY
关键字指定的列。
在聚合模型中，排序列就是通过 AGGREGATE KEY
关键字指定的列。
在更新模型中，排序列就是通过 UNIQUE KEY
关键字指定的列。
在主键模型中，排序列就是主键列，通过 PRIMARY KEY
关键字指定。
在定义排序列时，需要注意以下几点：
排序列必须从定义的第一列开始、并且是连续的。
在定义各列时，计划作为排序列的列必须定义在其他普通列之前。
排序列的顺序必须与表定义的列顺序一致。
前缀索引：
StarRocks 对前缀索引做了如下限制:
前缀索引项的内容只能由数据块中第一行的排序列的前缀组成。
前缀索引列的数量不能超过 3。
前缀索引项的长度不能超过 36 字节。
前缀索引中不能包含 FLOAT 或 DOUBLE 类型的列。
前缀索引中 VARCHAR 类型的列只能出现一次，并且处在末尾位置。
当前缀索引的末尾列是 CHAR 或 VARCHAR 类型时，前缀索引项的长度不会超过 36 字节。
数据分布
StarRocks 支持如下两种数据分布方式：
Hash 数据分布方式：一张表为一个分区，分区按照分桶键和分桶数量进一步进行数据划分。
Range+Hash 数据分布方式：一张表拆分成多个分区，每个分区按照分桶键和分桶数量进一步进行数据划分。
# 采用 Hash 分布的建表语句如下CREATE TABLE site_access(site_id INT DEFAULT '10',city_code SMALLINT,user_name VARCHAR(32) DEFAULT '',pv BIGINT SUM DEFAULT '0')AGGREGATE KEY(site_id, city_code, user_name)DISTRIBUTED BY HASH(site_id) BUCKETS 10;# 采用Range+Hash组合分布的建表语句如下CREATE TABLE site_access(event_day DATE,site_id INT DEFAULT '10',city_code VARCHAR(100),user_name VARCHAR(32) DEFAULT '',pv BIGINT SUM DEFAULT '0')AGGREGATE KEY(event_day, site_id, city_code, user_name)PARTITION BY RANGE(event_day)(PARTITION p1 VALUES LESS THAN ("2020-01-31"),PARTITION p2 VALUES LESS THAN ("2020-02-29"),PARTITION p3 VALUES LESS THAN ("2020-03-31"))DISTRIBUTED BY HASH(site_id) BUCKETS 10;
设置分区和分桶
选择分区键
选择合理的分区键可以有效的裁剪扫描的数据量。目前仅支持分区键的数据类型为日期和整数类型。在实际业务场景中，一般从数据管理的角度选择分区键，常见的分区键为时间或者区域。按照分区键划分数据后，单个分区原始数据量建议不要超过 100 GB。
选择分桶键
选择高基数的列（例如唯一 ID）来作为分桶键，可以保证数据在各个分桶中尽可能均衡。如果数据倾斜情况严重，您可以使用多个列作为数据的分桶键，但是不建议超过 3 个列。
还是以上述 Range+Hash 组合分布的建表语句为例：
CREATE TABLE site_access(
event_day DATE,
site_id INT DEFAULT '10',
city_code VARCHAR(),
user_name VARCHAR() DEFAULT '',
pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(event_day, site_id, city_code, user_name)
PARTITION BY RANGE(event_day)
(
PARTITION p1 VALUES LESS THAN ("2020-01-31"),
PARTITION p2 VALUES LESS THAN ("2020-02-29"),
PARTITION p3 VALUES LESS THAN ("2020-03-31")
)
DISTRIBUTED BY HASH(site_id) BUCKETS ;
如上示例中，site_access
表采用 site_id
作为分桶键，其原因在于，针对 site_access
表的查询请求，基本上都以站点（高基数列）作为查询过滤条件。采用 site_id
作为分桶键，可以在查询时裁剪掉大量无关分桶。
如下查询中，10 个分桶中的 9 个分桶被裁减，因而系统只需要扫描 site_access
表中 1/10 的数据：
select sum(pv)
from site_access
where site_id = ;
但是如果 site_id
分布十分不均匀，大量的访问数据是关于少数网站的（幂律分布, 二八规则），那么采用上述分桶方式会造成数据分布出现严重的倾斜，进而导致系统局部的性能瓶颈。此时，您需要适当调整分桶的字段，以将数据打散，避免性能问题。例如，可以采用 site_id
、city_code
组合作为分桶键，将数据划分得更加均匀。相关建表语句如下：
CREATE TABLE site_access
(
site_id INT DEFAULT '10',
city_code SMALLINT,
user_name VARCHAR() DEFAULT '',
pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(site_id, city_code, user_name)
DISTRIBUTED BY HASH(site_id,city_code) BUCKETS ;
在实际使用中，您可以依据自身的业务特点选择以上两种分桶方式。采用 site_id
的分桶方式对于短查询十分有利，能够减少节点之间的数据交换，提高集群整体性能；采用 site_id
、city_code
的组合分桶方式对于长查询有利，能够利用分布式集群的整体并发性能。
说明：
短查询是指扫描数据量不大、单机就能完成扫描的查询。
长查询是指扫描数据量大、多机并行扫描能显著提升性能的查询。
确定分桶数量
注意：不支持修改已创建的分区的分桶数量，支持在增加分区时为新增分区设置新的分桶数量。
在机器资源不足的情况下，如果想充分利用机器资源，可以通过如下公式来计算合适的分桶数量：
分桶数量 = BE节点数量 * CPU 核数/2
管理分区
# 增加分区ALTER TABLE site_access ADD PARTITION p4 VALUES LESS THAN ("2020-04-30")DISTRIBUTED BY HASH(site_id) BUCKETS 20;# 删除分区ALTER TABLE site_access DROP PARTITION p1;# 恢复分区RECOVER PARTITION p1 FROM site_access;# 查看分区SHOW PARTITIONS FROM site_access;
管理动态分区
创建支持动态分区的表
如下示例，创建一张支持动态分区的表，表名为 site_access
，动态分区通过 PEROPERTIES
进行配置。分区的区间为当前时间的前后 3 天，总共 6 天。
CREATE TABLE site_access(event_day DATE,site_id INT DEFAULT '10',city_code VARCHAR(100),user_name VARCHAR(32) DEFAULT '',pv BIGINT DEFAULT '0')DUPLICATE KEY(event_day, site_id, city_code, user_name)PARTITION BY RANGE(event_day)(PARTITION p20200321 VALUES LESS THAN ("2020-03-22"),PARTITION p20200322 VALUES LESS THAN ("2020-03-23"),PARTITION p20200323 VALUES LESS THAN ("2020-03-24"),PARTITION p20200324 VALUES LESS THAN ("2020-03-25"))DISTRIBUTED BY HASH(event_day, site_id) BUCKETS 32PROPERTIES("dynamic_partition.enable" = "true","dynamic_partition.time_unit" = "DAY","dynamic_partition.start" = "-3","dynamic_partition.end" = "3","dynamic_partition.prefix" = "p","dynamic_partition.buckets" = "32");
PROPERTIES
配置项如下：
dynamic_partition.enable
：是否开启动态分区特性，取值为TRUE
或FALSE
。默认值为TRUE
。dynamic_partition.time_unit
：必填，调度动态分区特性的时间粒度，取值为DAY
、WEEK
或MONTH
，表示按天、周或月调度动态分区特性。并且，时间粒度必须对应分区名后缀格式。具体对应规则如下：取值为
DAY
时，分区名后缀的格式应该为 yyyyMMdd，例如20200321
。
PARTITION BY RANGE(event_day)(
PARTITION p20200321 VALUES LESS THAN ("2020-03-22"),
PARTITION p20200322 VALUES LESS THAN ("2020-03-23"),
PARTITION p20200323 VALUES LESS THAN ("2020-03-24"),
PARTITION p20200324 VALUES LESS THAN ("2020-03-25")
)取值为
WEEK
时，分区名后缀的格式应该为 yyyy_ww，例如2020_13
代表 2020 年第 13 周。取值为
MONTH
时，分区名后缀的格式应该为 yyyyMM，例如202003
。dynamic_partition.start
：必填，动态分区的开始时间。以当天为基准，超过该时间范围的分区将会被删除。取值范围为小于 0 的负整数，最大值为 -1。默认值为 Integer.MIN_VALUE，即 -2147483648。dynamic_partition.end
：必填，动态分区的结束时间。以当天为基准，提前创建指定数量的分区。取值范围为大于 0 的正整数，最小值为 1。dynamic_partition.prefix
: 动态分区的前缀名，默认值为 p。dynamic_partition.buckets
: 动态分区的分桶数量，默认与 BUCKETS 关键词指定的分桶数量保持一致。
修改表的动态分区属性
执行 ALTER TABLE，修改动态分区的属性，例如暂停或者开启动态分区特性。
ALTER TABLE site_access SET("dynamic_partition.enable"="false");
ALTER TABLE site_access SET("dynamic_partition.enable"="true");
说明：
可以执行 SHOW CREATE TABLE 命令，查看表的动态分区属性。
ALTER TABLE 也适用于修改
PEROPERTIES
中的其他配置项。
使用说明
开启动态分区特性，相当于将创建分区的判断逻辑交由 StarRocks 完成。因此创建表时，必须保证动态分区配置项 dynamic_partition.time_unit
指定的时间粒度与分区名后缀格式对应，否则创建表会失败。具体对应规则如下：
dynamic_partition.time_unit
指定为DAY
时，分区名后缀的格式应该为 yyyyMMdd，例如20200325
。
PARTITION BY RANGE(event_day)(
PARTITION p20200321 VALUES LESS THAN ("2020-03-22"),
PARTITION p20200322 VALUES LESS THAN ("2020-03-23"),
PARTITION p20200323 VALUES LESS THAN ("2020-03-24"),
PARTITION p20200324 VALUES LESS THAN ("2020-03-25")
)
dynamic_partition.time_unit
指定为WEEK
时，分区名后缀的格式应该为 yyyy_ww，例如2020_13
，代表 2020 年第 13 周。dynamic_partition.time_unit
指定为MONTH
时，分区名后缀的格式应该为 yyyyMM，例如202003
。
批量创建分区
建表时批量创建日期分区
当分区键为日期类型时，建表时通过 START、END 指定批量分区的开始日期和结束日期，EVERY 子句指定分区增量值。并且 EVERY 子句中用 INTERVAL 关键字表示日期间隔，目前仅支持日期间隔的单位为 DAY、WEEK、MONTH、YEAR。
如下示例中，批量分区的开始日期为 2021-01-01
和结束日期为 2021-01-04
，增量值为一天：
CREATE TABLE site_access (
datekey DATE,
site_id INT,
city_code SMALLINT,
user_name VARCHAR(),
pv BIGINT DEFAULT '0'
)
ENGINE=olap
DUPLICATE KEY(datekey, site_id, city_code, user_name)
PARTITION BY RANGE (datekey) (
START ("2021-01-01") END ("2021-01-04") EVERY (INTERVAL DAY)
)
DISTRIBUTED BY HASH(site_id) BUCKETS
PROPERTIES (
"replication_num" = "1"
);
则相当于在建表语句中使用如下 PARTITION BY 子句：
PARTITION BY RANGE (datekey) (
PARTITION p20210101 VALUES [('2021-01-01'), ('2021-01-02')),
PARTITION p20210102 VALUES [('2021-01-02'), ('2021-01-03')),
PARTITION p20210103 VALUES [('2021-01-03'), ('2021-01-04'))
)
建表时批量创建不同日期间隔的日期分区
建表时批量创建日期分区时，支持针对不同的日期分区区间（日期分区区间不能相重合），使用不同的 EVERY 子句指定日期间隔。一个日期分区区间，按照对应 EVERY 子句定义的日期间隔，批量创建分区，例如：
CREATE TABLE site_access (
datekey DATE,
site_id INT,
city_code SMALLINT,
user_name VARCHAR(),
pv BIGINT DEFAULT '0'
)
ENGINE=olap
DUPLICATE KEY(datekey, site_id, city_code, user_name)
PARTITION BY RANGE (datekey) (
START ("2019-01-01") END ("2021-01-01") EVERY (INTERVAL YEAR),
START ("2021-01-01") END ("2021-05-01") EVERY (INTERVAL MONTH),
START ("2021-05-01") END ("2021-05-04") EVERY (INTERVAL DAY)
)
DISTRIBUTED BY HASH(site_id) BUCKETS
PROPERTIES (
"replication_num" = "1"
);
则相当于在建表语句中使用如下 PARTITION BY 子句：
PARTITION BY RANGE (datekey) (
PARTITION p2019 VALUES [('2019-01-01'), ('2020-01-01')),
PARTITION p2020 VALUES [('2020-01-01'), ('2021-01-01')),
PARTITION p202101 VALUES [('2021-01-01'), ('2021-02-01')),
PARTITION p202102 VALUES [('2021-02-01'), ('2021-03-01')),
PARTITION p202103 VALUES [('2021-03-01'), ('2021-04-01')),
PARTITION p202104 VALUES [('2021-04-01'), ('2021-05-01')),
PARTITION p20210501 VALUES [('2021-05-01'), ('2021-05-02')),
PARTITION p20210502 VALUES [('2021-05-02'), ('2021-05-03')),
PARTITION p20210503 VALUES [('2021-05-03'), ('2021-05-04'))
)
建表时批量创建数字分区
当分区键为整数类型时，建表时通过 START、END 指定批量分区的开始值和结束值，EVERY 子句指定分区增量值。
说明：分区键的值需要使用英文引号包裹，而 EVERY 子句中的分区增量值不用英文引号包裹。
如下示例中，批量分区的开始值为 1
和结束值为 5
，分区增量值为 1
：
CREATE TABLE site_access (
datekey INT,
site_id INT,
city_code SMALLINT,
user_name VARCHAR(),
pv BIGINT DEFAULT '0'
)
ENGINE=olap
DUPLICATE KEY(datekey, site_id, city_code, user_name)
PARTITION BY RANGE (datekey) (
START ("1") END ("5") EVERY ()
)
DISTRIBUTED BY HASH(site_id) BUCKETS
PROPERTIES (
"replication_num" = "1"
);
则相当于在建表语句中使用如下 PARTITION BY 子句：
PARTITION BY RANGE (datekey) (
PARTITION p1 VALUES [("1"), ("2")),
PARTITION p2 VALUES [("2"), ("3")),
PARTITION p3 VALUES [("3"), ("4")),
PARTITION p4 VALUES [("4"), ("5"))
)
建表后批量创建分区
建表后，支持通过ALTER TABLE 语句批量创建分区。相关语法与建表时批量创建分区类似，通过指定 ADD PARTITIONS 关键字，以及 START、END 以及 EVERY 子句来批量创建分区。示例如下：
ALTER TABLE site_access
ADD PARTITIONS START ("2021-01-04") END ("2021-01-06") EVERY (INTERVAL DAY);
物化视图
物化视图的一般定义是：一种包含一个查询结果的数据库对象，它可以是远端数据的一份本地拷贝，也可以是一个表或一个 join 结果的行/列的一个子集，还可以是使用聚合函数的一个汇总。相对于普通的逻辑视图，将数据「物化」后，能够带来查询性能的提升。
系统目前还不支持join，更多注意事项请参看 注意事项。
在本系统中，物化视图会被更多地用来当做一种预先计算的技术，同RollUp表，预先计算是为了减少查询时现场计算量，从而降低查询延迟。RollUp 表有两种使用方式：对明细表的任意维度组合进行预先聚合；采用新的维度列排序方式，以命中更多的前缀查询条件。当然也可以两种方式一起使用。物化视图的功能是 RollUp 表的超集，原有的 RollUp 功能都可通过物化视图来实现。
CREATE MATERIALIZED VIEW store_amt ASSELECT store_id, SUM(sale_amt)FROM sales_recordsGROUP BY store_id;
查看物化视图构建状态
SHOW ALTER MATERIALIZED VIEW FROM db_name;SHOW ALTER TABLE ROLLUP FROM db_name;
查看已创建的物化视图
desc sales_records all;
查看命中物化视图
EXPLAIN SELECT store_id, SUM(sale_amt) FROM sales_records GROUP BY store_id;
结果为:
| Explain String |
+-----------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:<slot 2> `store_id` | <slot 3> sum(`sale_amt`) |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 4:EXCHANGE |
| use vectorized: true |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: <slot 2> `store_id` |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:AGGREGATE (merge finalize) |
| | output: sum(<slot 3> sum(`sale_amt`)) |
| | group by: <slot 2> `store_id` |
| | use vectorized: true |
| | |
| 2:EXCHANGE |
| use vectorized: true |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: <slot 2> `store_id` |
| |
| 1:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(`sale_amt`) |
| | group by: `store_id` |
| | use vectorized: true |
| | |
| 0:OlapScanNode |
| TABLE: sales_records |
|PREAGGREGATION: ON|
| partitions=1/1 |
|rollup: store_amt|
| tabletRatio=10/10 |
| tabletList=22326,22328,22330,22332,22334,22336,22338,22340,22342,22344 |
| cardinality=0 |
| avgRowSize=0.0 |
| numNodes=1 |
| use vectorized: true |
+-----------------------------------------------------------------------------+
查询计划树中的 OlapScanNode 显示 PREAGGREGATION: ON
和 rollup: store_amt
，说明使用物化视图 store_amt 的预先聚合计算结果。也就是说查询已经命中到物化视图 store_amt，并直接从物化视图中读取数据了。
删除物化视图
下列两种情形需要删除物化视图:
用户误操作创建物化视图，需要撤销该操作。
用户创建了大量的物化视图，导致数据导入速度过慢不满足业务需求，并且部分物化视图的相互重复，查询频率极低，可容忍较高的查询延迟，此时需要删除部分物化视图。
删除已经创建完成的物化视图:
DROP MATERIALIZED VIEW IF EXISTS store_amt;
删除处于创建中的物化视图，需要先取消异步任务，然后再删除物化视图，以表 db0.table0
上的物化视图 mv 为例:
首先获得JobId，执行命令:
show alter table rollup from db0;
结果为:
+-------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+-------------+------+----------+---------+
| JobId | TableName | CreateTime | FinishedTime | BaseIndexName | RollupIndexName | RollupId | TransactionId | State | Msg | Progress | Timeout |
| 22478 | table0 | 2020-09-27 01:46:42 | NULL | table0 | mv | 22479 | 676 | WAITING_TXN | | NULL | 86400 |
+-------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+-------------+------+----------+---------+
其中JobId为22478，取消该Job，执行命令:
cancel alter table rollup from db0.table0 ();
物化视图最佳实践
精确去重
用户可以在明细表上使用表达式bitmap_union(to_bitmap(col))
创建物化视图，实现原来聚合表才支持的基于 bitmap 的预先计算的精确去重功能。
比如，用户有一张计算广告业务相关的明细表，每条记录包含的信息有点击日期、点击的是什么广告、通过什么渠道点击、以及点击的用户是谁：
CREATE TABLE advertiser_view_record(
TIME date,
advertiser varchar(),
channel varchar(),
user_id int
) distributed BY hash(TIME)
properties("replication_num" = "1");
用户查询广告 UV，使用下面查询语句：
SELECT advertiser, channel, count(distinct user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;
这种情况下，可以创建物化视图，使用 bitmap_union 预先聚合:
CREATE MATERIALIZED VIEW advertiser_uv AS
SELECT advertiser, channel, bitmap_union(to_bitmap(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;
物化视图创建完毕后，查询语句中的count(distinct user_id)
，会自动改写为bitmap_union_count (to_bitmap(user_id))
以命中物化视图。
近似去重
用户可以在明细表上使用表达式 hll_union(hll_hash(col))
创建物化视图，实现近似去重的预计算。
在同上一样的场景中，用户创建如下物化视图:
CREATE MATERIALIZED VIEW advertiser_uv AS
SELECT advertiser, channel, hll_union(hll_hash(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;
匹配更丰富的前缀索引
用户的基表 tableA 有 (k1, k2, k3) 三列。其中 k1, k2 为排序键。这时候如果用户查询条件中包含 where k1=1 and k2=2
，就能通过 shortkey 索引加速查询。但是用户查询语句中使用条件 k3=3
，则无法通过 shortkey 索引加速。此时，可创建以 k3 作为第一列的物化视图:
CREATE MATERIALIZED VIEW mv_1 AS
SELECT k3, k2, k1
FROM tableA
这时候查询就会直接从刚才创建的 mv_1 物化视图中读取数据。物化视图对 k3 是存在前缀索引的，查询效率也会提升。
注意事项
物化视图的聚合函数的参数仅支持单列，比如：
sum(a+b)
不支持。如果删除语句的条件列，在物化视图中不存在，则不能进行删除操作。如果一定要删除数据，则需要先将物化视图删除，然后方可删除数据。
单表上过多的物化视图会影响导入的效率：导入数据时，物化视图和 base 表数据是同步更新的，如果一张表的物化视图表超过10张，则有可能导致导入速度很慢。这就像单次导入需要同时导入10张表数据是一样的。
相同列，不同聚合函数，不能同时出现在一张物化视图中，比如：
select sum(a), min(a) from table
不支持。物化视图的创建语句目前不支持 JOIN 和 WHERE ，也不支持 GROUP BY 的 HAVING 子句。
目前聚合模型、更新模型和明细模型支持创建物化视图，主键模型不支持创建物化视图。
创建物化视图是一个异步的操作。在执行完 CREATE MATERIALIZED VIEW 语句后，创建物化视图的任务即提交成功。只有等待上一个物化视图创建完成后（即
State
为FINISHED
），才能提交下一个创建物化视图的任务。
Colocate Join
Colocate Join 功能是分布式系统实现 Join 数据分布的策略之一，能够减少数据多节点分布时 Join 操作引起的数据移动和网络传输，从而提高查询性能。
在 StarRocks 中使用 Colocate Join 功能，您需要在建表时为其指定一个 Colocation Group（CG），同一 CG 内的表需遵循相同的 Colocation Group Schema（CGS），即表对应的分桶副本具有一致的分桶键、副本数量和副本放置方式。如此可以保证同一 CG 内，所有表的数据分布在相同一组 BE 节点上。当 Join 列为分桶键时，计算节点只需做本地 Join，从而减少数据在节点间的传输耗时，提高查询性能。因此，Colocation Join，相对于其他 Join，例如 Shuffle Join 和 Broadcast Join，可以有效避免数据网络传输开销，提高查询性能。
创建 Colocation 表
在建表时，您需要在 PROPERTIES 中指定属性 "colocate_with" = "group_name"
以创建一个 Colocate Join 表，并且指定其归属于特定的 Colocation Group。
注意 StarRocks 仅支持对同一 Database中的表进行 Colocate Join 操作。
CREATE TABLE tbl (k1 int, v1 int sum)DISTRIBUTED BY HASH(k1)BUCKETS 8PROPERTIES("colocate_with" = "group1");
如果指定的 CG 不存在，StarRocks 会自动创建一个只包含当前表的 CG，并指定当前表为该 CG 的 Parent Table。如果 CG 已存在，StarRocks 会检查当前表是否满足 CGS。如果满足，StarRocks 会创建该表，并将该表加入 Group。同时，StarRocks 会根据已存在的 Group 中的数据分布规则为当前表创建分片和副本。
Group 归属于一个 Database，Group 名在一个 Database 内唯一。在内部存储中，Group 的全名为 dbId_groupName
，但用户只感知 groupName
。
分桶键哈希值，对分桶数取模得到桶的序号（Bucket Seq）。假设一个表的分桶数为 8，则共有 [0, 1, 2, 3, 4, 5, 6, 7] 8 个分桶（Bucket)，每个分桶内会有一个或多个子表（Tablet)，子表数量取决于表的分区（Partition）数量：为单分区表时，一个分桶内仅有一个子表。如果是多分区表，则会有多个子表。
为了使得表能够有相同的数据分布，同一 CG 内的表必须满足下列约束：
同一 CG 内的表的分桶键的类型、数量和顺序完全一致，并且桶数一致，从而保证多张表的数据分片能够一一对应地进行分布控制。分桶键，即在建表语句中
DISTRIBUTED BY HASH(col1, col2, ...)
中指定一组列。分桶键决定了一张表的数据通过哪些列的值进行 Hash 划分到不同的 Bucket Seq 下。同 CG 的表的分桶键的名字可以不相同，分桶列的定义在建表语句中的出现次序可以不一致，但是在DISTRIBUTED BY HASH(col1, col2, ...)
的对应数据类型的顺序要完全一致。同一个 CG 内所有表的所有分区的副本数必须一致。如果不一致，可能出现某一个子表的某一个副本，在同一个 BE 上没有其他的表分片的副本对应。
同一个 CG 内所有表的分区键，分区数量可以不同。
同一个 CG 中的所有表的副本放置必须满足下列约束：
CG 中所有表的 Bucket Seq 和 BE 节点的映射关系和 Parent Table 一致。
Parent Table 中所有分区的 Bucket Seq 和 BE 节点的映射关系和第一个分区一致。
Parent Table 第一个分区的 Bucket Seq 和 BE 节点的映射关系利用原生的 Round Robin 算法决定。
CG 内表的一致的数据分布定义和子表副本映射，能够保证分桶键取值相同的数据行一定在相同 BE 节点上，因此当分桶键做 Join 列时，只需本地 Join 即可。
删除 Colocation 表
当 Group 中最后一张表彻底删除后（彻底删除是指从回收站中删除。通常，一张表通过 DROP TABLE
命令被删除后，会在回收站默认停留一天的时间后，再被彻底删除），该 Group 也会被自动删除。
查看 Group 信息
您可以通过以下命令查看集群内已存在的 Group 信息。
SHOW PROC '/colocation_group';
示例：
mysql> SHOW PROC '/colocation_group';
+-------------+--------------+--------------+------------+----------------+----------+----------+
| GroupId | GroupName | TableIds | BucketsNum | ReplicationNum | DistCols | IsStable |
+-------------+--------------+--------------+------------+----------------+----------+----------+
| 10005.10008 | 10005_group1 | 10007, 10040 | 10 | 3 | int(11) | true |
+-------------+--------------+--------------+------------+----------------+----------+----------+
| 列名 | 描述 |
|---|---|
| GroupId | 一个 Group 的全集群唯一标识。前半部分为 DB ID，后半部分为 Group ID。 |
| GroupName | Group 的全名。 |
| TabletIds | 该 Group 包含的表的 ID 列表。 |
| BucketsNum | 分桶数。 |
| ReplicationNum | 副本数。 |
| DistCols | Distribution columns，即分桶列类型。 |
| IsStable | 该 Group 是否稳定。 |
您可以通过以下命令进一步查看特定 Group 的数据分布情况。
SHOW PROC '/colocation_group/GroupId';
示例：
mysql> SHOW PROC '/colocation_group/10005.10008';
+-------------+---------------------+
| BucketIndex | BackendIds |
+-------------+---------------------+
| 0 | 10004, 10002, 10001 |
| 1 | 10003, 10002, 10004 |
| 2 | 10002, 10004, 10001 |
| 3 | 10003, 10002, 10004 |
| 4 | 10002, 10004, 10003 |
| 5 | 10003, 10002, 10001 |
| 6 | 10003, 10004, 10001 |
| 7 | 10003, 10004, 10002 |
+-------------+---------------------+
| 类名 | 描述 |
|---|---|
| BucketIndex | 分桶序列的下标。 |
| BackendIds | 分桶中数据分片所在的 BE 节点 ID 列表。 |
注意 以上命令需要 ADMIN 权限。暂不支持普通用户查看。
修改表 Group 属性
您可以通过以下命令修改表的 Colocation Group 属性。
ALTER TABLE tbl SET ("colocate_with" = "group_name");
如果该表之前没有指定过 Group，则该命令会检查 Schema，并将该表加入到该 Group（如 Group 不存在则会创建）。如果该表之前有指定其他 Group，则该命令会先将该表从原有 Group 中移除，并将其加入新 Group（如 Group 不存在则会创建）。
您也可以通过以下命令，删除一个表的 Colocation 属性：
ALTER TABLE tbl SET ("colocate_with" = "");
Group 具有 Stable 属性，当 Stable 为 true 时（即 Stable 状态），表示当前 Group 内的表的所有分片没有正在进行的变动，Colocation 特性可以正常使用。当 Stable 为 false 时（即 Unstable 状态），表示当前 Group 内有部分表的分片正在做修复或迁移，此时，相关表的 Colocate Join 将退化为普通 Join。可以设置 FE 的配置项 disable_colocate_balance 来禁止自动均衡。
查询使用样例：
CREATE TABLE `tbl1` (`k1` date NOT NULL COMMENT "",`k2` int(11) NOT NULL COMMENT "",`v1` int(11) SUM NOT NULL COMMENT "") ENGINE=OLAPAGGREGATE KEY(`k1`, `k2`)PARTITION BY RANGE(`k1`)(PARTITION p1 VALUES LESS THAN ('2019-05-31'),PARTITION p2 VALUES LESS THAN ('2019-06-30'))DISTRIBUTED BY HASH(`k2`) BUCKETS 8PROPERTIES ("colocate_with" = "group1");CREATE TABLE `tbl2` (`k1` datetime NOT NULL COMMENT "",`k2` int(11) NOT NULL COMMENT "",`v1` double SUM NOT NULL COMMENT "") ENGINE=OLAPAGGREGATE KEY(`k1`, `k2`)DISTRIBUTED BY HASH(`k2`) BUCKETS 8PROPERTIES ("colocate_with" = "group1");
查看 Join 查询计划：
EXPLAIN SELECT * FROM tbl1 INNER JOIN tbl2 ON (tbl1.k2 = tbl2.k2);
+----------------------------------------------------+
| Explain String |
+----------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:`tbl1`.`k1` | |
| PARTITION: RANDOM |
| |
| RESULT SINK |
| |
| 2:HASH JOIN |
| | join op: INNER JOIN |
| | hash predicates: |
| |colocate: true|
| | `tbl1`.`k2` = `tbl2`.`k2` |
| | tuple ids: 0 1 |
| | |
| |----1:OlapScanNode |
| | TABLE: tbl2 |
| | PREAGGREGATION: OFF. Reason: null |
| | partitions=0/1 |
| | rollup: null |
| | buckets=0/0 |
| | cardinality=-1 |
| | avgRowSize=0.0 |
| | numNodes=0 |
| | tuple ids: 1 |
| | |
| 0:OlapScanNode |
| TABLE: tbl1 |
| PREAGGREGATION: OFF. Reason: No AggregateInfo |
| partitions=0/2 |
| rollup: null |
| buckets=0/0 |
| cardinality=-1 |
| avgRowSize=0.0 |
| numNodes=0 |
| tuple ids: 0 |+----------------------------------------------------+
以上示例中 Hash Join 节点显示 colocate: true，表示 Colocation Join 生效。
+----------------------------------------------------+
| Explain String |
+----------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:`tbl1`.`k1` | |
| PARTITION: RANDOM |
| |
| RESULT SINK |
| |
| 2:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | hash predicates: |
| |colocate: false, reason: group is not stable|
| |`tbl1`.`k2` = `tbl2`.`k2`|
| | tuple ids: 0 1 |
| | |
| |----3:EXCHANGE |
| | tuple ids: 1 |
| | |
| 0:OlapScanNode |
| TABLE: tbl1 |
| PREAGGREGATION: OFF. Reason: No AggregateInfo |
| partitions=0/2 |
| rollup: null |
| buckets=0/0 |
| cardinality=-1 |
| avgRowSize=0.0 |
| numNodes=0 |
| tuple ids: 0 |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 03 |
| UNPARTITIONED |
| |
| 1:OlapScanNode |
| TABLE: tbl2 |
| PREAGGREGATION: OFF. Reason: null |
| partitions=0/1 |
| rollup: null |
| buckets=0/0 |
| cardinality=-1 |
| avgRowSize=0.0 |
| numNodes=0 |
| tuple ids: 1 |
+----------------------------------------------------+
以上示例中，HASH JOIN 节点显示了 Colocation Join 没有生效以及对应原因：colocate: false, reason: group is not stable
。同时 StarRocks 生成一个 EXCHANGE 节点。
FE 配置项
disable_colocate_balance
：是否关闭自动 Colocation 副本均衡功能。默认为 false
，即不关闭。该参数只影响 Colocation 表的副本均衡，不影响普通表。
以上参数支持动态修改，您可以通过以下命令关闭。
ADMIN SET FRONTEND CONFIG ("disable_colocate_balance" = "TRUE");
Session 变量
disable_colocate_join
：是否在 session 粒度关闭 Colocate Join 功能。默认为 false
，即不关闭。
以上参数可以动态修改，您可以通过以下命令关闭。
SET disable_colocate_join = TRUE;
HTTP Restful API
查看集群的全部 Colocation 信息。
curl -u<user>:<password> http://<fe_host>:<fe_http_port>/api/colocate
将 Group 标记为 Stable 或 Unstable。
# 标记为 Stable。
curl -u<user>:<password> http://<fe_host>:<fe_http_port>/api/colocate/group_stable?db_id=<dbId>&group_id=<grpId>
# 标记为 Unstable。
curl -u<user>:<password> http://<fe_host>:<fe_http_port>/api/colocate/group_unstable?db_id=<dbId>&group_id=<grpId>
若返回为 200
， 则表示标记修改成功。
设置 Group 的数据分布。
该接口可以强制设置某一 Group 的数据分布。
注意 使用该命令，需要将 FE 的配置
disable_colocate_balance
设为true
，即关闭系统自动 Colocation 副本修复和均衡。否则在修改数据分布设置后可能会被系统自动重置。
curl -u<user>:<password> -X POST "http://<fe_host>:<fe_http_port>/api/colocate/bucketseq?db_id=10005&group_id=10008"
Body:
[[10004,10002],[10003,10002],[10002,10004],[10003,10002],[10002,10004],[10003,10002],[10003,10004],[10003,10004],[10003,10004],[10002,10004]]
返回：200
其中 Body 是以嵌套数组表示的 Bucket Seq 以及每个分桶中分片所在 BE 的 ID。
注意 使用该命令，需要将 FE 的配置
disable_colocate_balance
设为true
，即关闭系统自动 Colocation 副本修复和均衡。否则在修改数据分布设置后可能会被系统自动重置。
Lateral Join
行列转化」是 ETL 处理过程中常见的操作。Lateral Join 功能能够将每行数据和内部的子查询或者 Table Function 关联。通过 Lateral Join 与 Unnest 功能配合，您可以实现一行转多行的功能。Unnest 是一种 Table Function，可以把数组类型转化成 Table 的多行。
注意
当前版本中，Lateral Join 仅用于和 Unnest 函数配合使用，实现行转列的功能。后续会支持配合其他 Table Function 或 UDTF。
当前版本中，Lateral Join 暂不支持子查询。
使用方式：
使用 Lateral Join 功能前，您需要开启 CBO 优化器。
SET global enable_cbo = true;
语法
from table_reference join [lateral] table_reference
-- 完整 SQL 语句。
SELECT student, score, t.unnest
FROM tests
CROSS JOIN LATERAL UNNEST(scores) AS t;
-- 简化 SQL 语句。您可以使用 Unnest 关键字省略 Lateral Join 关键字。
SELECT student, score, t.unnest
FROM tests, UNNEST(scores) AS t;
支持 Bitmap、String、Array、Column 之间的转化关系如下
展开 Bitmap 类型数据
您可以使用 Lateral Join 功能配合 Unnest 功能展开 Bitmap 类型数据。
示例：
创建测试用表并插入测试数据。
CREATE TABLE lateral_test3 (
`v1` bigint() NULL COMMENT "",
`v2` Bitmap BITMAP_UNION COMMENT ""
)
Aggregate key(v1)
DISTRIBUTED BY HASH(`v1`) BUCKETS ;
insert into lateral_test3 values (, bitmap_from_string('1, 2')), (, to_bitmap());检查当前数据中
v1
以及bitmap_to_string(v2)
。mysql> select v1, bitmap_to_string(v2) from lateral_test3;
+------+------------------------+
| v1 | bitmap_to_string(`v2`) |
+------+------------------------+
| 1 | 1,2 |
| 2 | 3 |
+------+------------------------+插入一行新数据。
mysql> insert into lateral_test3 values (1, to_bitmap(3));检查新数据中
v1
以及bitmap_to_string(v2)
。mysql> select v1, bitmap_to_string(v2) from lateral_test3;
+------+------------------------+
| v1 | bitmap_to_string(`v2`) |
+------+------------------------+
| 1 | 1,2,3 |
| 2 | 3 |
+------+------------------------+展开 Bitmap 类型数据。
mysql> select v1, unnest from lateral_test3, unnest(bitmap_to_array(v2));
+------+--------+
| v1 | unnest |
+------+--------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
+------+--------+
Bitmap 索引
Bitmap 索引是一种特殊的数据库索引技术，其使用 bitmap 进行储存和计算操作，能够提高指定列的查询效率。
注意事项
Bitmap 索引适用于满足以下条件的列：
基数较低，值大量重复，例如 ENUM 类型的列。如列基数较高，推荐使用 Bloomfilter 索引。
可使用等值条件查询或者可转化为等值条件查询。
主键模型和明细模型中所有列都可以创建 bitmap 索引；聚合模型和更新模型中，只有维度列（即 Key 列）支持创建 bitmap 索引。
不支持为 FLOAT、DOUBLE、BOOLEAN 和 DECIMAL 类型的列创建 bitmap 索引。
创建索引
CREATE TABLE d0.table_hash
(
k1 TINYINT,
k2 DECIMAL(, ) DEFAULT "10.5",
v1 CHAR() REPLACE,
v2 INT SUM,
INDEX index_name (column_name) USINGBITMAPCOMMENT ''
)
ENGINE = olap
AGGREGATE KEY(k1, k2)
COMMENT "my first starrocks table"
DISTRIBUTED BY HASH(k1) BUCKETS
PROPERTIES ("storage_type" = "column");
其中有关索引部分参数说明如下：
关于建表的其他参数说明，参见 CREATE TABLE。
建表后使用 CREATE INDEX 语句创建 bitmap 索引。详细参数说明和示例，参见 CREATE INDEX。
CREATE INDEX index_name ON table_name (column_name) [USING BITMAP] [COMMENT ''];
查看索引
查看指定表的所有 bitmap 索引。详细参数和返回结果说明，参见 SHOW INDEX。
SHOW INDEX[ES] FROM [db_name.]table_name [FROM db_name];
或
SHOW KEY[S] FROM [db_name.]table_name [FROM db_name];
说明：创建 bitmap 索引为异步过程，使用如上语句只能查看到已经创建完成的索引。
删除索引
删除指定表的某个 bitmap 索引。详细参数说明和示例，参见 DROP INDEX。
DROP INDEX index_name ON [db_name.]table_name;
适用场景
单个非前缀索引列查询：如果一个查询条件命中前缀索引列，StarRocks 即可使用前缀索引提高查询效率，快速返回查询结果。但是前缀索引的长度有限，如果想要提高一个非前缀索引列的查询效率，即可以为这一列创建 bitmap 索引。
多个非前缀索引列查询：bitmap 索引使用位运算，速度较快，所以在多列查询的场景中，可以通过为每列创建 bitmap 索引来提高查询速度。需要注意的是创建 bitmap 索引会消耗额外的存储空间。
说明：如要了解一个查询是否命中了 bitmap 索引，可查看该查询的 Profile。关于如何查看 Profile，参见分析查询。
单个非前缀索引列查询
CREATE INDEX index1 ON table1 (Platform) USING BITMAP COMMENT 'index1';
过程：Platform列----->构建字典，映射成int类型编码值------>根据字典的编码值生成 bitmap。
查询：select xxx from table where Platform = Ios，那么 StarRocks 会先查找字典，得到Ios的编码值是1，然后再去查找 bitmap，得到Ios对应的 bitmap 是 0001
多个非前缀索引列查询
执行如下命令为Platform
列创建 bitmap 索引。
CREATE INDEX index1 ON table1 (Platform) USING BITMAP COMMENT 'index1';
执行如下命令为Producer
列创建 bitmap 索引。
CREATE INDEX index2 ON table1 (Producer) USING BITMAP COMMENT 'index2';
如果执行一个 SQL 查询 select xxx from table where Platform = Android and Producer = P1
，那么：
StarRocks 会同时查找Platform
和Producer
的字典，得到Android
的编码值为0
，对应的 bitmap 为1110
；P1
的编码值为0
，对应的 bitmap 为1010
。
因为Platform = Android
和Producer = P1
这两个查询条件是 and 关系，所以 StarRocks 会对两个 bitmap 进行位运算 1110 & 1010
，得到最终结果1010
。
根据最终结果，StarRocks 只读取第 1 行和第 3 行数据，不会读取所有数据。
Bloomfilter 索引
Bloom Filter（布隆过滤器）是用于判断某个元素是否在一个集合中的数据结构，优点是空间效率和时间效率都比较高，缺点是有一定的误判率。本质上就是bitmap+N hash函数 。
初始值全部为0，当插入一个元素时，n个Hash函数对元素进行计算, 得到n个slot，然后将Bit数组中n个slot的Bit置1。当我们要判断一个元素是否在集合中时，还是通过相同的n个Hash函数计算Hash值，如果所有Hash值在布隆过滤器里对应的Bit不全为1，则该元素不存在。当对应Bit全1时, 则元素的存在与否, 无法确定. 这是因为布隆过滤器的位数有限, 由该元素计算出的slot, 恰好全部和其他元素的slot冲突. 所以全1情形, 需要回源查找才能判断元素的存在性。
适用场景
满足以下几个条件时可以考虑对某列建立Bloom Filter 索引：
首先BloomFilter也适用于非前缀过滤。
查询会根据该列高频过滤，而且查询条件大多是in和=。
不同于Bitmap, BloomFilter适用于高基数列。
# 创建索引PROPERTIES ( "bloom_filter_columns"="k1,k2,k3" )# 查看索引SHOW CREATE TABLE table_name;# 删除索引ALTER TABLE example_db.my_table SET ("bloom_filter_columns" = "");# 修改索引ALTER TABLE example_db.my_table SET ("bloom_filter_columns" = "k1,k2,k3");不支持对Tinyint、Float、Double、Decimal 类型的列创建Bloom Filter索引。Bloom Filter索引只对in和=过滤查询有加速效果。如果要查看某个查询是否命中了Bloom Filter索引，可以通过查询Profile的信息来查看是否命中。
使用 Bitmap 实现精确去重
Bitmap 去重是指，当给定一个数组 A， 其取值范围为 [0, n)， 可采用 (n+7)/8 的字节长度的 bitmap 对该数组去重， 初始化为全 0；逐个处理数组 A 的元素， 以 A 中元素取值作为 bitmap 的下标， 将该下标的 bit 置 1； 最后统计 bitmap 中 1 的个数即为数组 A 的 count distinct 结果。
与传统的使用 count distinct 方式 Bitmap 的优势主要体现在以下两点 ：
空间优势：通过用 Bitmap 的一个 Bit 位表示对应下标是否存在，能节省大量存储空间；例如对 INT32 去重，使用普通 Bitmap 所需的存储空间只占传统去重的 1/32。StarRocks 采用 Roaring Bitmap 的优化实现，对于稀疏的 Bitmap，所占用的存储空间会进一步降低。
时间优势：Bitmap 的去重涉及的计算包括对给定下标的 Bit 置位，统计 Bitmap 的置位个数，分别为 O(1) 操作和 O(n) 操作， 并且后者可使用 CLZ，CTZ 等指令高效计算。 此外，Bitmap 去重在 MPP 执行引擎中还可以并行加速处理，每个计算节点各自计算本地子 Bitmap， 使用 BITOR 操作将这些子 Bitmap 合并成最终的 Bitmap。BITOR 操作比基于 sort 和基于 hash 的去重效率更高，且无条件依赖和数据依赖，可向量化执行。
Roaring Bitmap 实现，细节可以参考：具体论文和实现
使用 Bitmap 去重
Bitmap index 和 Bitmap 去重二者虽然都使用 Bitmap 技术， 但引入动机和解决的问题完全不同。前者用于低基数的枚举型列的等值条件过滤，后者则用于计算一组数据行的指标列的不重复元素的个数。
从2.3版本开始，所有数据模型的指标列均支持 BITMAP，但是所有模型的排序键还不支持 BITMAP。
创建表时指定指标列的数据类型为 ·BITMAP， 聚合函数为 BITMAP_UNION。
当在 Bitmap 类型列上使用 count distinct 时，StarRocks 会自动转化为 BITMAP_UNION_COUNT 计算。
具体操作函数参见 Bitmap函数。
示例
以统计某一个页面的 UV 为例：
创建一张含有 BITMAP 列的表，其中 visit_users 列为聚合列，列类型为 BITMAP，聚合函数为 BITMAP_UNION。
CREATE TABLE `page_uv` (
`page_id` INT NOT NULL COMMENT '页面id',
`visit_date` datetime NOT NULL COMMENT '访问时间',
`visit_users` BITMAP BITMAP_UNION NOT NULL COMMENT '访问用户id'
) ENGINE=OLAP
AGGREGATE KEY(`page_id`, `visit_date`)
DISTRIBUTED BY HASH(`page_id`) BUCKETS
PROPERTIES (
"replication_num" = "1",
"storage_format" = "DEFAULT"
);
向表中导入数据，采用 insert into 语句导入。
insert into page_uv values
(, '2020-06-23 01:30:30', to_bitmap()),
(, '2020-06-23 01:30:30', to_bitmap()),
(, '2020-06-23 01:30:30', to_bitmap()),
(, '2020-06-23 02:30:30', to_bitmap()),
(, '2020-06-23 01:30:30', to_bitmap());
在以上数据导入后，在 page_id = 1， visit_date = '2020-06-23 01:30:30' 的数据行，visit_user 字段包含着 3 个 bitmap 元素（13，23，33）；在 page_id = 1， visit_date = '2020-06-23 02:30:30' 的数据行，visit_user 字段包含着 1 个 bitmap 元素（13）；在 page_id = 2， visit_date = '2020-06-23 01:30:30' 的数据行，visit_user 字段包含着 1 个 bitmap 元素（23）。
采用本地文件导入
cat <<<'DONE' | \
curl --location-trusted -u root: -H "label:label_1600960288796" \
-H "column_separator:," \
-H "columns:page_id,visit_date,visit_users, visit_users=to_bitmap(visit_users)" -T - \
http://StarRocks_be0:8040/api/db0/page_uv/_stream_load
-06-23 01:30:30,130
-06-23 01:30:30,230
-06-23 01:30:30,120
-06-23 02:30:30,133
-06-23 01:30:30,234
DONE
统计每个页面的 UV
select page_id, count(distinct visit_users) from page_uv group by page_id;
查询结果如下所示：
mysql> select page_id, count(distinct visit_users) from page_uv group by page_id;
+-----------+------------------------------+
| page_id | count(DISTINCT `visit_user`) |
+-----------+------------------------------+
| | |
+-----------+------------------------------+
| | |
+-----------+------------------------------+
row in set ( sec)
Bitmap 全局字典
目前，基于 Bitmap 类型的去重机制有一定限制，即 Bitmap 需要使用整数型类型作为输入。如用户期望将其他数据类型作为 Bitmap 的输入，则需要构建全局字典，将其他类型数据（如字符串类型）通过全局字典映射成为整数类型。构建全局字典有以下几种方案：
基于 Hive 表的全局字典
该方案需创建一张 Hive 表作为全局字典。Hive 表有两个列，一个是原始值，一个是编码的 Int 值。以下为全局字典的生成步骤：
将事实表的字典列去重并生成临时表。
对临时表和全局字典进行 left join，以悬空的词典项作为新 value。
对新 value 进行编码并插入全局字典。
对事实表和更新后的全局字典进行 left join，将词典项替换为 ID。
采用这种构建全局字典的方式，可以通过 Spark 或者 MR 实现全局字典的更新，和对事实表中 Value 列的替换。相比基于 Trie 树的全局字典，这种方式可以分布式化，还可以实现全局字典复用。
但需要注意的是，使用这种方式构建全局字典时，事实表会被读取多次，并且过程中有两次 Join 操作，会导致计算全局字典使用大量额外资源。
基于 Trie 树构建全局字典
Trie 树又叫前缀树或字典树。Trie 树中节点的后代存在共同的前缀，系统可以利用字符串的公共前缀来减少查询时间，从而最大限度地减少字符串比较。因此，基于 Trie 树构建全局字典的方式适合用于实现字典编码。但基于 Trie 树的全局字典实现难以分布式化，在数据量比较大的时候会产生性能瓶颈。 通过构建全局字典，将其他类型的数据转换成为整型数据，就可以利用 Bitmap 对非整型数据列进行精确去重分析了。
传统Count_distinct计算
StarRocks 是基于 MPP 架构实现的，在使用 count distinct 做精准去重时，可以保留明细数据，灵活性较高。但是，由于在查询执行的过程中需要进行多次数据 shuffle（不同节点间传输数据，计算去重），会导致性能随着数据量增大而直线下降。
如以下场景所示，存在表（dt, page, user_id)，需要通过明细数据计算 UV。
按 page 进行分组统计 UV。
select page, count(distinct user_id) as uv from table group by page;
对于上图计算 UV 的 SQL，StarRocks 在计算时，会按照下图进行计算，先根据 page 列和 user_id 列 group by，最后再 count。
显然，在上面的计算方式中，由于数据需要进行多次 shuffle，当数据量越来越大时，所需的计算资源就会越来越多，查询也会越来越慢。而使用 Bitmap 技术去重，就是为了解决传统 count distinct 在大量数据场景下的性能问题。
使用 HyperLogLog 实现近似去重
HyperLogLog 是一种近似去重算法，在部分对去重精度要求不高的场景下，您可以选择使用 HyperLogLog 算法减轻数据去重分析的计算压力。根据数据集大小以及所采用的哈希函数的类型，HyperLogLog 算法的误差可控制在 1% 至 10% 左右。
创建包含 HLL 列的表
以下示例创建 test
表，其中包含 DATE 数据类型列 dt
，INT 数据类型列 id
，以及 HLL 类型列 uv
，其使用的 HLL_HASH
函数为 HLL_UNION
。
CREATE TABLE test(
dt DATE,
id INT,
uvHLL HLL_UNION
)
DISTRIBUTED BY HASH(ID) BUCKETS ;
注意
当前版本中, 仅聚合表支持 HLL 类型的指标列。
当数据量较大时，建议为高频率的 HLL 查询建立对应的物化视图。
数据导入 test.csv
2022-03-10,02022-03-11,12022-03-12,22022-03-13,32022-03-14,42022-03-15,52022-03-16,62022-03-14,42022-03-15,52022-03-16,6
Stream Load 模式:
curl --location-trusted -u root: -H "label:987654321" -H "column_separator:," -H "columns:dt,id,uv=hll_hash(id)" -T test.csv http://fe_host:http_port/api/db_name/test/_stream_load
Broker Load 模式:
LOAD LABEL test_db.label
(
DATA INFILE("hdfs://hdfs_host:hdfs_port/user/starRocks/data/input/file")
INTO TABLE `test`
COLUMNS TERMINATED BY ","
(dt, id, uv)
SET (
uv = HLL_HASH(id)
)
);
通过 HLL 聚合数据
您可以通过以下三种方式聚合数据加速查询。
基于示例表创建物化视图，使 HLL 列产生聚合。
ALTER TABLE test ADD ROLLUP test_rollup(dt, uv);
创建针对 HLL 列计算的新表，并插入原示例表中的相关数据。
create table test_uv1(
id int,
uv_set hll hll_union)
distributed by hash(id) buckets ;
insert into test_uv select id, uv from test;
创建针对 HLL 列计算的新表，并插入通过
HLL_HASH
基于原示例表中相关数据生成的 HLL 列。
create table test_uv2(
id int,
uv_set hll hll_union)
distributed by hash(id) buckets ;
insert into test_uv select id, hll_hash(id) from test;
查询数据
HLL 列不支持直接查询原始值。您可以通过函数 HLL_UNION_AGG
进行查询。
SELECT HLL_UNION_AGG(uv) FROM test;
返回如下：
+---------------------+
| hll_union_agg(`uv`) |
+---------------------+
| 7 |
+---------------------+
当在 HLL 类型列上使用 count distinct
时，StarRocks 会自动将其转化为 HLL_UNION_AGG(hll) 计算。所以以上查询等价于以下查询。
SELECT COUNT(DISTINCT uv) FROM test;
返回如下：
+----------------------+
| count(DISTINCT `uv`) |
+----------------------+
| 7 |
+----------------------+
如果您的数据集基数在百万、千万量级，并拥有几十台机器，那么您可以直接使用 count distinct
方式。如果您的数据集基数在亿级以上，并且需要精确去重，那么您需要使用 Bitmap 去重。如果您选择近似去重，那么可以使用 HLL 类型去重。
Bitmap 类型仅支持 TINYINT，SMALLINT，INT，BIGINT（注意不支持 LARGEINT）去重。对于其他类型数据集去重，您需要构建词典，将原类型映射到整数类型。HLL 去重方式则无需构建词典，仅要求对应的数据类型支持哈希函数。
对于普通列，您还可以使用 NDV
函数进行近似去重计算。NDV
函数返回值是 COUNT(DISTINCT col)
结果的近似值聚合函数，底层实现将数据存储类型转为 HyperLogLog 类型进行计算。但 NDV
函数在计算的时候消耗资源较大，不适合于并发高的场景。
如果您的应用场景为用户行为分析，建议使用 INTERSECT_COUNT 或者自定义 UDAF 去重。
相关函数
HLL_UNION_AGG(hll)：此函数为聚合函数，用于计算满足条件的所有数据的基数估算。此函数还可用于分析函数，只支持默认窗口，不支持窗口子句。
HLL_RAW_AGG(hll)：此函数为聚合函数，用于聚合 HLL 类型字段，返回 HLL 类型。
HLL_CARDINALITY(hll)：此函数用于估算单条 HLL 列的基数。
HLL_HASH(column_name)：生成 HLL 列类型，用于
insert
或导入 HLL 类型。HLL_EMPTY()：生成空 HLL 列，用于
insert
或导入数据时补充默认值。
starrocks性能优化
基础优化：
优化的主要思路：减少资源使用量。借助「执行时间」中的 profile 图，查看下图中的执行时间占比，优先优化占比高的处理节点。这里：
每个节点(算子)的进度条是执行时间占比。能直观地看到全局图中哪些节点耗 CPU 多、Scan 时间长；
行数是下面节点(算子)的总输出行数。对于性能有很直接的影响，往往“返回量大”代表着数据过滤效果不够、数据传输量大、后续处理更耗时。
一、优化基本的建表 SCHEMA 等
选择合适的数据模型
使用内存表：内存表适合数据量较小的分析业务。
使用 Colocate Table
使用星型模型
使用分区和分桶
使用稀疏索引和 Bloomfilter
使用倒排索引
使用物化视图
没有特殊要求，尽量用 int 类型，减少字符串使用，调整列的顺序...；
DATE 、DATETIME，不必要时尽量不要转换成字符串；
特别是会作为 join condition 的列，更应该使用 int、DATE 等简单类型。
二：优化导入性能
StarRocks 目前提供 Broker Load 和 Stream Load 两种导入方式，通过指定导入 label 标识一批次的导入。StarRocks 对单批次的导入会保证原子生效，即使单次导入多张表也同样保证其原子性。
Stream Load：通过 HTTP 推流方式导入数据，用于微批导入。该模式下，1MB 数据导入延迟可维持在秒级别，适合高频导入。
Broker Load：通过拉取的方式批量导入数据，适合大批量数据的导入。
三：优化 Schema Change 性能
StarRocks 目前支持三种 Schema Change 方式，即 Sorted Schema Change，Direct Schema Change，Linked Schema Change。
Sorted Schema Change：改变列的排序方式，需对数据进行重新排序。例如删除排序列中的一列，字段重排序。
ALTER TABLE site_visit DROP COLUMN city;Direct Schema Change：无需重新排序，但是需要对数据做一次转换。例如修改列的类型，在稀疏索引中加一列等。
ALTER TABLE site_visit MODIFY COLUMN username varchar();Linked Schema Change：无需转换数据，直接完成。例如加列操作。
ALTER TABLE site_visit ADD COLUMN click bigint SUM default '0';
建议您在建表时考虑好 Schema，以便加快后续 Schema Change 速度。
四：join优化
1、broadcast join 场景中调整 Join 的左右表顺序，使得“大表在左边、小表在右边”，以发挥 broadcast join 的优势。
2、两个相差不多的表（一般几百k行）join，有些情况下默认会选用broadcast join，这个时候可以尝试采用[shuffle] hint的方式强制走shuffle join。
例如：select a.x,b.y from a join [shuffle] b on a.x1 = b.x1
3、大表的 join，能用 colocate join 的尽量使用。
4、在join之前尽量添加一些where条件，能够充分发挥谓词下推，减少后续的数据shuffle和join节点处理的数据量。
BroadCast Join：将右表全量发送到左表的HashJoinNodeShuffle Join：将左右表的数据根据哈希计算分散到集群的节点之中Colocate Join：两个表的数据分布都是一样的，只需要本地join即可，没有网络传输开销。Bucket Shuffle Join：join的列是左表的数据分布列（分桶键），所以相比于shuffle join只需要将右表的数据发送到左表数据存储计算节点。Replicated Join：右表的全量数据是分布在每个节点上的（也就是副本个数和BE节点数量一致），不管左表怎么分布，都是走本地Join。没有网络传输开销。
五：优化 group by group_concat
关于 group_concat 的两阶段聚合，系统中有个设置第一阶段聚合方式的参数：
-- 第一阶段的预聚合方式：auto, force_streaming, force_preaggregation
set streaming_preaggregation_mode = 'force_streaming';
六：提升QPS的能力
修改并行度设置成CPU核数量的一半 。
set global parallel_fragment_exec_instance_num=16
参考链接：https://docs.starrocks.com/zh-cn/latest/administration/Profiling
