中山seo网站优化公司,深圳全网推广小程序制作,重庆旅游,外贸网站建设网站优化总结StarRocks更新表的全部内容的集合#xff08;V3.2版本#xff09;
一、基本功能
聚合函数replace的聚合表主键被主键表替代采用Merge-On-Read的策略#xff0c;读取时需要在线Merge多个版本的数据文件#xff0c;谓词和索引无法下推至底层数据#xff0c;会严重影响…总结StarRocks更新表的全部内容的集合V3.2版本
一、基本功能
聚合函数replace的聚合表主键被主键表替代采用Merge-On-Read的策略读取时需要在线Merge多个版本的数据文件谓词和索引无法下推至底层数据会严重影响查询性能
创建更新表ddl
CREATE TABLE IF NOT EXISTS unq_orders (create_time DATETIME NOT NULL COMMENT create time of an order,shop_id BIGINT NOT NULL COMMENT id of shop,order_id BIGINT NOT NULL COMMENT id of an order,order_state INT COMMENT state of an order,price BIGINT COMMENT price of an order
)
UNIQUE KEY(create_time, shop_id)
PARTITION BY date_trunc(day, create_time)
DISTRIBUTED BY HASH(shop_id)
PROPERTIES (
replication_num 1
); INSERT INTO unq_orders
VALUES(2024-07-01 12:00:00, 2001, 3001, 1, 30),(2024-07-01 13:00:00, 2001, 3002, 1, 40),(2024-07-02 11:00:00, 2002, 3003, 1, 60),(2024-07-03 10:00:00, 2002, 3004, 1, 10),(2024-07-04 17:00:00, 2006, 3005, 1, 70);
注意事项
UNIQUE KEY是更新表的标识同时也是排序键必须使用 DISTRIBUTED BY HASH 子句指定分桶键也就是必须使用hash分桶
二、分区分区键必须为key键
starrocks支持分区分桶的数据分布分区可以是单个分区也可以是多个分区分区是左闭右开的范围。 分区的方式
表达式分区自动创建分区适用于时间范围或者枚举值分区数据导入自动创建分区很强大Range分区对于简单有序的数据分区连续1天连续6天1-3等动态、批量或者手动创建List分区适用于枚举值分区比如按照国家城市分区手动创建 List分区不支持动态和批量创建 异步物化视图暂不支持基于使用List分区的基表创建 Range分区只支持时间类型/整数类型的字段 List分区支持字符串/时间/整数/布尔值类型字段 各个分区的创建的标识
range分区PARTITION BY RANGE(dt) 支持日期/整型list分区PARTITION BY LIST (city) 支持多种类型时间表达式分区PARTITION BY date_trunc/time_slice(‘day’, create_time) 支持时间(特殊的Range分区功能列表达式分区PARTITION BY (dt, merchant_id) 支持多种类型特殊的List分区功能
2.1 手动创建Range分区仅支持分区键的数据类型为日期和整数类型
-- 日期
CREATE TABLE IF NOT EXISTS unq_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)
PARTITION BY RANGE(create_time)(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(order_id)
PROPERTIES (
replication_num 3
); --整数
CREATE TABLE IF NOT EXISTS unq_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)
PARTITION BY RANGE(order_id)(PARTITION p1 VALUES LESS THAN (4),PARTITION p2 VALUES LESS THAN (9),PARTITION p3 VALUES LESS THAN (20)
)
DISTRIBUTED BY HASH(order_id)
PROPERTIES (
replication_num 1
); --联合分区
CREATE TABLE IF NOT EXISTS unq_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)
PARTITION BY RANGE(create_time, order_id)(PARTITION p1 VALUES LESS THAN (2020-01-31, 1),PARTITION p2 VALUES LESS THAN (2020-02-29, 2),PARTITION p3 VALUES LESS THAN (2020-03-31, 3)
)
DISTRIBUTED BY HASH(order_id)
PROPERTIES (
replication_num 1
); 2.2 批量创建Range分区仅支持分区键的数据类型为日期和整数类型
方法START、END 指定批量分区的开始和结束EVERY子句指定分区增量值时间指定单位HOUR3.0V、DAY、WEEK、MONTH、YEAR
--日期
CREATE TABLE IF NOT EXISTS unq_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)
PARTITION BY RANGE(create_time)(START (2021-01-01) END (2021-01-04) EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(order_id)
PROPERTIES (
replication_num 1
); --整数
CREATE TABLE IF NOT EXISTS unq_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)
PARTITION BY RANGE(order_id)(START (1) END (5) EVERY (1)
)
DISTRIBUTED BY HASH(order_id)
PROPERTIES (
replication_num 1
);
2.3 动态创建Range分区仅支持分区键的数据类型为日期类型
动态分区通过PROPERTIES进行配置 表达式分区与动态分区创建分区的区别表达式分区会根据导入数据自动创建分区而动态分区是根据动态分区属性定期提前创建一些分区如果导入数据不属于任何存在的分区则会报错。 动态分区⾃动提前创建新的分区删除过期分区可以设置TTL --日期
CREATE TABLE IF NOT EXISTS unq_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)
PARTITION BY RANGE(create_time)(PARTITION p20200321 VALUES LESS THAN (2020-03-22),PARTITION p20200322 VALUES LESS THAN (2020-03-23)
)
DISTRIBUTED BY HASH(order_id)
PROPERTIES (replication_num 1,dynamic_partition.enable true,dynamic_partition.time_unit DAY,dynamic_partition.start -3,dynamic_partition.end 3,dynamic_partition.prefix p,dynamic_partition.history_partition_num 0
); 表示依照当天时间为基准删除3天前的分区同时创建3天后的分区 dynamic_partition_check_interval_secondsFE 配置项动态分区检查的时间周期默认为 600, 周期检查是否删除分区与创建分区 其他配置项见官网动态分区 动态分区只能创建时间类型的分区 2.4 手动创建List分区V3.1支持
分区键支持字符串/时间/整数/布尔值类型字段 分区键不可以为null
--列多个值放在一分区
CREATE TABLE test.unq_orders1000 (id bigint,city varchar(20) not null,user_id bigint,recharge_money decimal(32,2), dt varchar(20) not null
)
UNIQUE KEY(id,city)
PARTITION BY LIST (city) (PARTITION pCalifornia VALUES IN (Los Angeles,San Francisco,San Diego), -- 这些城市同属一个州PARTITION pTexas VALUES IN (Houston,Dallas,Austin)
)
DISTRIBUTED BY HASH(id);--联合分区列多个值放在一分区
CREATE TABLE IF NOT EXISTS unq_orders (create_time DATE NOT NULL COMMENT create time of an order,city varchar(20) NOT NULL COMMENT ,order_state INT COMMENT state of an order,total_price BIGINT COMMENT price of an order
)
UNIQUE KEY(create_time, city)
PARTITION BY LIST (create_time, city) (PARTITION p202204_California VALUES IN ((2022-04-01, Los Angeles),(2022-04-01, San Francisco),(2022-04-02, Los Angeles),(2022-04-02, San Francisco)),PARTITION p202204_Texas VALUES IN ((2022-04-01, Houston),(2022-04-01, Dallas),(2022-04-02, Houston),(2022-04-02, Dallas))
)
DISTRIBUTED BY HASH(city)
PROPERTIES (
replication_num 1
); 2.5 表达式分区
根据表达式的值可以自动创建对应分区不需要提前创建分区基本适用于多数场景覆盖大部分range分区与list分区功能但是部分场景可能需要独立使用Range或者List分区自动创建分区数量上限默认为 4096
2.5.1 时间函数表达式分区
分区列只能有一个而且数据类型必须是时间类型分区粒度hour、day、month 或 year仅支持date_trunc和time_slice函数应用到分区列上分区列值可以为null
-- 天级别分区
CREATE TABLE IF NOT EXISTS unq_orders (create_time DATETIME 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)
PARTITION BY date_trunc(day, create_time)
DISTRIBUTED BY HASH(order_id)
PROPERTIES (
replication_num 1
); --时间分片7天内的放在一个分区
CREATE TABLE IF NOT EXISTS unq_orders (create_time DATETIME 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)
PARTITION BY time_slice(create_time, INTERVAL 7 day)
DISTRIBUTED BY HASH(order_id)
PROPERTIES (
replication_num 1
); 2.5.2 列表达式分区V3.1
分区列可以多个数据类型包括字符串、日期、整数和布尔值分区值不支持null每个列值都会创建一个分区
CREATE TABLE IF NOT EXISTS unq_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)
PARTITION BY (create_time, order_id)
DISTRIBUTED BY HASH(order_id)
PROPERTIES (
replication_num 1
); 2.6 建表后创建分区
--Range分区批量日期
ALTER TABLE unq_orders ADD PARTITIONS START (2021-01-10) END (2021-01-20) EVERY (INTERVAL 1 DAY);--Range分区批量整数
ALTER TABLE unq_orders ADD PARTITIONS START (100) END (200) EVERY (50);--List分区不支持批量创建只能手动单个分区
ALTER TABLE unq_orders ADD PARTITION p202205_California VALUES IN ((2022-05-03, Los Angeles),(2022-05-03, San Francisco),(2022-05-04, Los Angeles),(2022-05-05, San Francisco)
);--Range分区创建单个日期分区
ALTER TABLE unq_orders
ADD PARTITION p20200130 VALUES LESS THAN (2021-01-22)--Range分区创建单个整数分区
ALTER TABLE unq_orders ADD PARTITION p300 VALUES LESS THAN (300);2.7 管理分区
2.7.1 删除分区(默认会保留一天误删可以使用恢复分区恢复
测试发现LIST分区无法删除
Range分区可以删除
ALTER TABLE unq_orders DROP PARTITION p1;2.7.2 恢复分区
RECOVER PARTITION p1 FROM unq_orders;2.7.3 查看分区
SHOW PARTITIONS FROM unq_orders;三、分桶
3.1 哈希分桶
分区内部的数据按照分桶键存放数据支持多个分桶键更新表分桶键必须为key键分桶键值尽量多基数避免数据倾斜分桶键支持的数据类型整型、字符串、日期时间建表后可以修改分桶键V3.2
CREATE TABLE IF NOT EXISTS unq_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)
PARTITION BY RANGE(create_time)(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(create_time, order_id)
PROPERTIES (
replication_num 1
); 3.2.1 哈希分桶数
每个分桶的数据规模最好不要超过10G单个分区数据超过100G建议手动设置分区其他建议系统自动分桶数
--显示指定分桶数
--一个分区的原始数据量为 300 GB则按照每 10 GB 原始数据一个 Tablet则分区中分桶数量可以设置为 30
CREATE TABLE IF NOT EXISTS unq_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)
PARTITION BY RANGE(create_time)(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(create_time, order_id) BUCKETS 30
PROPERTIES (
replication_num 1
); 建表后设置分桶数
-- 手动指定所有分区中分桶数量
ALTER TABLE unq_orders DISTRIBUTED BY HASH(create_time, order_id) BUCKETS 20;-- 手动指定部分分区中分桶数量
ALTER TABLE unq_orders partitions p20210102 DISTRIBUTED BY HASH(create_time, order_id) BUCKETS 30;查看分桶数
SHOW PARTITIONS from unq_orders三、索引
starrocks内部自动创建的索引内部索引前缀索引、Ordinal索引、ZoneMap 索引 需要用户手动创建的索引Bitmap 索引和 Bloom filter 索引
3.1 Ordinal索引列级索引
starrocks采用列式存储每一列数据以Date Page64*1024 个字节64KB为单位分块存储每个Date Page的起始行号作为Ordinal索引项。所以其他所有的索引都要通过Ordinal索引找到数据的物理地址。
3.2 ZoneMap索引
ZoneMap索引存储了每块数据统计信息统计信息包括 Min 最大值、Max 最小值、HasNull 空值、HasNotNull 不全为空的信息。 在查询时StarRocks 可以根据这些统计信息快速判断这些数据块是否可以过滤掉从而减少扫描数据量提升查询速度。 ZoneMap索引有两种一种是存每个 Segment 的统计信息另一种是存每个 Data Page 的统计信息
3.3 前缀索引
按照UNIQUE KEY顺序建立前缀索引也就是排序键 前缀索引每1024行数据创建一个索引项所以前缀索引一般都可以全部加载到内存加速查询速度。 一个表只能有一个前缀索引 更新表在建表后不能修改排序键 注意事项
排序列不宜过多一般为3个建议不超过4个前缀索引项的最大长度为36字节超过部分会被截断前缀字段中CHAR、VARCHAR、STRING类型的列只能出现一次并且处在末尾位置而且超过部分会被截断
3.4 Bitmap索引
原理bitmap即为一个bit数组一个bit的取值有两种0或1。每一个bit对应数据表中的一行并根据该行的取值情况来决定bit的取值是0还是1。 适用场景
于较高基数列的查询多个低基数列的组合查询
劣势
查询时加载Bitmap索引的开销写入数据可能会影响损耗性能
查询有Bitmap索引的自适应选择机制 原因由于查询bitmap可能有性能损耗所以建立bitmap索引是否使用由starrocks自动控制也可以强制使用 原则查询条件涉及的列值数量/基数 是否小于 1/1000
SELECT * FROM employees WHERE gender male
由于 gender 基数 只用两个查询只查询其中一个 1/2 1/1000 所以不使用对应的bitmap索引SELECT * FROM employees WHERE gender male AND city IN (北京, 上海);
假设city列的基数为10000查询条件涉及其中2个值结合gender则(1*2)/(2*10000) 1/1000 所以使用对应的bitmap索引3.4.1 创建bitmap索引
更新表只有Key列支持创建Bitmap索引每个索引只能一个列常见的数据类型都支持
CREATE TABLE IF NOT EXISTS unq_orders (create_time DATETIME 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,INDEX i_order_id (order_id) USING BITMAP
)
UNIQUE KEY(create_time, order_id)
PARTITION BY date_trunc(day, create_time)
DISTRIBUTED BY HASH(order_id)
PROPERTIES (
replication_num 1
); 3.4.2 管理bitmap索引
--创建索引
CREATE INDEX i_order_id ON unq_orders(order_id) USING BITMAP;--查看创建进度
SHOW ALTER TABLE COLUMN FROM db;--查看索引
SHOW INDEX FROM unq_orders;--删除索引
DROP INDEX i_user_id ON unq_orders;3.5 Bloom filter索引
原理快速判断表的数据文件中是否可能包含要查询的数据如果不包含就跳过。
3.5.1 创建Bloom filter索引
更新表只有Key可以创建Bloom filter索引每个索引只能一个列可以同时创建多个索引用,分隔常见的数据类型都支持
CREATE TABLE IF NOT EXISTS test.unq_orders (create_time DATETIME 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)
PARTITION BY date_trunc(day, create_time)
DISTRIBUTED BY HASH(order_id)
PROPERTIES (
replication_num 1,
bloom_filter_columns order_id
); 3.5.2 管理Bloom filter索引
--创建索引
ALTER TABLE aggregate_tbl SET (bloom_filter_columns order_id,create_time);--查看索引
SHOW CREATE TABLE aggregate_tbl;--删除索引
ALTER TABLE aggregate_tbl SET (bloom_filter_columns );四、同步物化视图Rollup
官网说明可以使用同步物化视图视图但是操作发现不可以创建同步物化视图
CREATE MATERIALIZED VIEW shop_id_amount AS
SELECT shop_id, SUM(price)
FROM test.unq_orders
GROUP BY shop_id;--由于更新表非key都是replace函数索引不可以使用sum等其他聚合函数
The aggregation type of column[mv_sum_price] must be same as the aggregate type of base column in aggregate table五、异步物化视图
异步物化视图优势
支持外部表多表关联查询支持部分分区刷新定时刷新与手动刷新 异步机制支持查询改写直接查询基表可以自动从对应物化视图获取数据支持直接查询异步物化视图异步物化视图可以与其基表设置不同的分区和分桶策略并且可以为分区指定TTL异步物化视图支持分区上卷支持单批次最大刷新分区数与只刷新最近N个分区
异步物化视图局限性
基表发生变化物化视图可能还没有发生变化只能对有分区的基表创建异步物化视图不支持List分区的基表创建物化视图同时不能创建List分区的异步物化视图
5.1 创建异步物化视图
CREATE MATERIALIZED VIEW merchant_mv_6
PARTITION BY create_date
DISTRIBUTED BY HASH(shop_id)
REFRESH IMMEDIATE ASYNC START(2024-07-01 00:00:00) EVERY (interval 1 minute)
PROPERTIES(partition_ttl_number 5,auto_refresh_partitions_limit 2,partition_refresh_number 2
)
AS
SELECTdate_trunc(day, create_time) as create_date,shop_id,sum(price) as total_price
FROM unq_orders
GROUP BY create_time, shop_id;基表的查询尽量不要使用order by 可能导致查询改写失败 5.2 设置分区
由于starrocks的range分区与表达式分区基本都是针对时间类型的字段而且基本都是单字段分区所以异步物化视图的分区基本只能是时间类型的字段单分区。 物化视图分区键必须是基表的分区键 物化视图的分区字段可以是基表的String类型的时间格式字段创建分区的时候转换成时间类型即可str2date
PARTITION BY str2date(datekey, %Y-%m-%d)增量更新如果物化视图分区内的基表数据没有发生变化则对应的物化视图分区数据不会刷新 partition_ttl_number: 需要保留的最近的物化视图分区数量也就是ttl比如上例子中partition_ttl_number 5表示保留最近5个分区其他分区会定时删除。 partition_refresh_number表示单批次最大刷新分区数当多个分区数据都发生变化则会分批次刷新数据每批次都刷新对应的分区数避免资源过多消耗。 auto_refresh_partitions_limit: 表示需要刷新的最近的物化视图分区数量比如上例子中auto_refresh_partitions_limit 2表示只刷新最近2个分区其他的分区数据当发生变化也不再刷新但是在数据查询改写中对于不刷新的分区查询会直接查询基表的数据有数据变化
5.3 设置刷新模式
5.3.1 自动刷新
REFRESH ASYNC : 每当基表数据发生变化时物化视图会自动刷新
5.3.2 定时刷新
ASYNC [START (start_time)] EVERY(INTERVAL interval)
day/hour/minute/second
--从当前时间开始
REFRESH ASYNC EVERY (interval 1 minute)--指定开始时间
REFRESH ASYNC START(2024-07-01 00:00:00) EVERY (interval 1 minute)5.3.3 设置成手动刷新
REFRESH MANUAL: 设置成手动刷新
-- 异步调用刷新任务
REFRESH MATERIALIZED VIEW order_mv;-- 同步调用刷新任务
REFRESH MATERIALIZED VIEW order_mv WITH SYNC MODE;5.3.4 创建异步物化视图是否立即执行刷新
IMMEDIATE异步物化视图创建成功后立即刷新 DEFERRED异步物化视图创建成功后不进行刷新您可以通过手动调用或创建定时任务触发刷新
5.3.4 查询异步物化视图
可以直接查询异步物化视图同时也支持查询基表的查询改写
5.4 管理异步物化视图
5.4.1 修改异步物化视图
-- 启用被禁用的异步物化视图
ALTER MATERIALIZED VIEW order_mv ACTIVE;-- 重命名
ALTER MATERIALIZED VIEW order_mv RENAME order_total;-- 修改刷新机制
ALTER MATERIALIZED VIEW order_mv REFRESH ASYNC EVERY(INTERVAL 2 DAY);5.4.2 查看异步物化视图
-- 查看所有的物化视图
SHOW MATERIALIZED VIEWS;-- 按条件查询
SHOW MATERIALIZED VIEWS WHERE NAME LIKE order%;-- 查看物化视图的创建语句
SHOW CREATE MATERIALIZED VIEW merchant_mv_3;5.4.3 查询物化视图执行状态与异步刷新情况
-- 找到 物化视图的任务 task_name
select * from information_schema.tasks order by CREATE_TIME desc-- 通过task_name 获取 物化视图执行与刷新情况
select * from information_schema.task_runs where task_namemv-11576 order by CREATE_TIME5.4.4 删除物化视图
DROP MATERIALIZED VIEW order_mv;六、数据去重
starrocks是mpp架构的引擎所以在数据去重方面就需要把各个BE节点上的数据进行shuffle所以如果大规模的数据会导致计算资源紧张所以对于数据去重starrcoks提供两种特殊的额外的方式但是都是有局限性的。
6.1 常规去重
对于数据集基数在百万、千万量级并拥有几十台机器那么您可以直接使用count distinct方式
select date_trunc(day, create_time) as create_date, count(distinct shop_id) as cnt
from unq_orders
group by create_date6.2 Bitmap精确去重
更新表不支持UNIQUE_KEYS table should not specify aggregate type for non-key column[order_id].
6.3 HyperLogLog近似去重
更新表不支持
七、Colocate Join
功能Colocate Join 功能是分布式系统实现 Join 数据分布的策略之一能够减少数据多节点分布时 Join 操作引起的数据移动和网络传输从而提高查询性能。
原理同一CG内所有表的数据分布在相同一组 BE 节点上。当 Join 列为分桶键时计算节点只需做本地 Join从而减少数据在节点间的传输耗时提高查询性能把相同数据放在同一个BE上数据本地join 所以当数据迁移都是一起均衡操作在数据迁移过程中查询退回普通的joinshfflue
条件
表具有相同数据类型的分桶键名字可以不一样而且顺序要保持一致桶数一致表具有相同的副本数查询只支持等值查询其他比较都失效退化成普通的join
使用方式在表PROPERTIES中指定属性colocate_with “group_name” 如果指定的 CG 不存在StarRocks 会自动创建一个只包含当前表的 CG并指定当前表为该 CG 的 Parent Table。如果 CG 已存在StarRocks 会检查当前表是否满足条件。如果满足StarRocks 会创建该表并将该表加入 Group。同时StarRocks 会根据已存在的 Group 中的数据分布规则为当前表创建分片和副本。 7.1 创建Colocate Join表
CREATE TABLE pri_orders_colocate (order_id bigint NOT NULL,create_time datetime NOT NULL,merchant_id int NOT NULL,user_id int NOT NULL,good_name string NOT NULL,cnt int NOT NULL,revenue int NOT NULL,state tinyint NOT NULL
)
PRIMARY KEY (order_id, create_time, merchant_id)
PARTITION BY date_trunc(day, create_time)
DISTRIBUTED BY HASH (merchant_id) BUCKETS 8
ORDER BY (create_time, merchant_id)
PROPERTIES (colocate_with order_merchant_group,enable_persistent_index true
);CREATE TABLE IF NOT EXISTS test.unq_orders_colocate (create_time DATETIME NOT NULL COMMENT create time of an order,shop_id INT NOT NULL COMMENT id of shop,order_id BIGINT NOT NULL COMMENT id of an order,order_state INT COMMENT state of an order,price BIGINT COMMENT price of an order
)
UNIQUE KEY(create_time, shop_id)
PARTITION BY date_trunc(day, create_time)
DISTRIBUTED BY HASH(shop_id) BUCKETS 8
PROPERTIES (colocate_with order_merchant_group,replication_num 1
);
);7.2 修改表Colocate Join组
ALTER TABLE tbl SET (colocate_with group_name);7.2 删除Colocate Join
-- 删除表的Colocate Join属性
ALTER TABLE tbl SET (colocate_with );删除特定的Colocate Join组 当所有的设置colocate_with的表都删除并且真正后台清理掉后默认保留一天colocate_with设置的组才会真实的删掉
7.3 是否真实使用Colocate Join
-- 查看 colocation_group
SHOW PROC /colocation_group;-- 查看特定group bucket分布的BE
SHOW PROC /colocation_group/11912.11916;使用Colocate Join和使用普通的join是一样的通过expain命令查看是否真实使用Colocate Join如果Colocate Join失效或者不稳定则会退化成普通的join查询。
explain
select *
from test.pri_orders_colocate as pri
INNER JOIN
test.unq_orders_colocate as unq
on (pri.merchant_id unq.shop_id)
and pri.create_time 2024-07-01 12:00:00八、Query Cache
8.1、Query Cache使用场景
query cache默认是关闭的。
查询多为宽表模型下的单表聚合查询或星型模型下简单多表 JOIN 的聚合查询。聚合查询以非 GROUP BY 聚合和低基数 GROUP BY 聚合为主。查询的数据以按时间分区追加的形式导入并且在不同时间分区上的访问表现出冷热性。
8.2、Query Cache命中高效性
会对query判断是否语义等价
聚合语句的第一次聚合是语义等价就判定语义等价
q1:
SELECTsum(murmur_hash3_32(hour)), 0)) sumvi AS fingerprint
FROM
(SELECTdate_trunc(hour, ts) AS hour,sum(v1) AS sumviFROMt0WHEREts BETWEEN 2022-01-03 00:00:00AND 2022-01-03 23:59:59GROUP BYdate_trunc(hour, ts)
) AS t;q2:SELECTdate_trunc(hour, ts) AS hour,sum(v1) AS sumvi
FROMt0
WHEREts BETWEEN 2022-01-03 00:00:00AND 2022-01-03 23:59:59
GROUP BYdate_trunc(hour, ts)是否含有 ORDER BY 子句和 LIMIT 子句不影响两个查询的语义等价where 条件的先后顺序 group by 先后顺序不影响语义等价
九、CBO 统计信息
CBO优化器cost-based Optimizer)是查询优化的关键CBO会统计starrcoks中表的信息当一条Sql查询到达Starrocks后会基于统计信息选择一个最优的执行路径作为最终的物理查询计划。
9.1、CBO统计信息的类别列的粒度
CBO统计表的信息主要分为两种 第一种是统计表的列的基础统计信息包括
row_count: 表的总行数data_size: 列的数据大小ndv: 列基数即 distinct value 的个数null_count: 列中 NULL 值的个数min: 列的最小值max: 列的最大值
上面的信息存储在_statistics_.column_statistics表中是按照表的分区内各个列进行统计的。
第二种是把表的列信息统计到直方图中用直方图表达数据的情况 直方图能方便的展示数据列的倾斜分布不均的情况。支持列的数据类型是数值类型、DATE、DATETIME 或字符串类型。 直方图目前只支持手动采样采集采集的数据存储zai _statistics_.histogram_statistics表中其中mcv表示列的值出现的次数
9.2、自动全量/抽样采集基础信息
自动全量采集基础信息可能消耗大量的系统资源默认5分钟一次默认开启自动全量采集基础信息。可以配置参数statistic_auto_analyze_start_time, statistic_auto_analyze_end_time设置每天的开始结束时间。
在自动全量采集基础信息中如果表过大或者设置的健康度达到阈值将把自动全量采集基础信息变更为自动抽样采集基础信息。具体信息见官网。
9.3、自定义自动全量/抽样采集基础信息
创建自动意自动采集任务需要先关闭自动全量采集基础信息enable_collect_full_statisticfalse采集相关参数见官网。
语法
CREATE ANALYZE [FULL|SAMPLE] TABLE tbl_name (col_name [,col_name]) [PROPERTIES (property [,property])]例子
-- 定期全量采集所有数据库的统计信息。
CREATE ANALYZE ALL;-- 定期全量采集指定数据库下所有表的统计信息。
CREATE ANALYZE FULL DATABASE db_name;-- 定期全量采集指定表、列的统计信息。
CREATE ANALYZE TABLE tbl_name(c1, c2, c3); -- 定期抽样采集指定数据库下所有表的统计信息。
CREATE ANALYZE SAMPLE DATABASE db_name;-- 自动采集所有数据库的统计信息不收集db_name.tbl_name表。
CREATE ANALYZE SAMPLE DATABASE db_name PROPERTIES (statistic_exclude_pattern db_name.tbl_name
); 9.4、手动全量/抽样采集基础信息
手动任务创建后仅会执行一次无需手动删除。
语法
ANALYZE [FULL|SAMPLE] TABLE tbl_name (col_name [,col_name])
[WITH SYNC | ASYNC MODE]
[PROPERTIES (property [,property])]例子
-- 手动全量采集指定表的统计信息使用默认配置。
ANALYZE FULL TABLE tbl_name;-- 手动全量采集指定表指定列的统计信息使用默认配置。
ANALYZE TABLE tbl_name(c1, c2, c3);-- 手动抽样采集指定表的统计信息使用默认配置。
ANALYZE SAMPLE TABLE tbl_name;-- 手动抽样采集指定表指定列的统计信息设置抽样行数。
ANALYZE SAMPLE TABLE tbl_name (v1, v2, v3) PROPERTIES(statistic_sample_collect_rows 1000000
);9.5、手动采集直方图信息
手动任务创建后仅会执行一次无需手动删除。
-- 语法
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name]
[WITH SYNC | ASYNC MODE]
[WITH N BUCKETS]
[PROPERTIES (property [,property])]例子
-- 手动采集v1列的直方图信息使用默认配置。
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON v1;-- 手动采集v1列的直方图信息指定32个分桶mcv指定为32个采样比例为50%。
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON v1,v2 WITH 32 BUCKETS
PROPERTIES(histogram_mcv_size 32,histogram_sample_ratio 0.5
);
