項目背景

指標管理平臺按指標查詢類型可以劃為落表指標和即席查詢指標。

• 落表指標：可選擇不同的維度生成多個結果表(每天提交任務寫入結果表)，對指標進行取數的時候會根據查詢條件自動匹配最合適的結果表進行查詢。

• 即席查詢指標：不產生結果表，每次取數根據指標計算規則以及查詢條件動態生成SQL去指標來源表中查詢。

舉例說明：現有一張訂單明細表 order_info，表結構如下

CREATE TABLE order info`(
order_id`varchar(64)NOT NULL COMMENT"訂單id"pt`varchar(12)NOT NULL COMMENT“用戶id"user id` varchar(64) NOT NULL COMMENT "用戶id",price`double NULL COMMENT ""project_id`int(11)NOT NULL COMMENT"產品id"channel`varchar(64)NULL COMMENT“渠道'ENGINE=OLAP
PRIMARY KEY( order_id,“pt~)
PARTITION BY(`pt~)
DISTRIBUTED BY HASH( order_id)
PROPERTIES(
"replication num""in_memory"= "false"enable persistent index"= "true""replicated storage"= "true"compression"="LZ4"

構建指標

（1）創建模型：示例只有單表不需要增加關聯，選擇price作為度量列，user_id、project_id、channel作為維度列。

（2）創建原子指標：銷售額、計算邏輯 sum(price) , 維度為模型的全部維度。

（3）構建落表派生指標：當日銷售金額、指標計算邏輯 sum(price) , 落表維度分別選擇 channel （渠道當日銷售金額）， project_id (產品當日銷售金額)

-- 渠道當日銷售金額create?table?sum_price_day_channel?as?? ??select?sum(price)?as?sum_price_day , channel ,?'{pt}'? ??from?order_info?where?pt?=?'{pt}'? ??group?by?channel;?
-- 產品當日銷售金額create?table?sum_price_day_project?as?? ??select?sum(price)?as?sum_price_day , project_id ,?'{pt}'? ??from?order_info?where?pt?=?'{pt}'? ??group?by?project_id;

（4）構建即席查詢派生指標：當日銷售金額、指標計算邏輯 sum(price), 支持維度選擇 channel、project_id。

查詢指標

（1）根據維度channel ，20250101<= pt <= 20250105 查詢

a.即席查詢：實時生成sql

select?sum(price)?as?sum_price_day,channel,ptfrom?order_info?where?pt >=?'20250101'?and?pt <=?'20250105'group?by?channel,pt

b.落表查詢：當?sum_price_channel 表包含所有需要查詢的日期，否則根據即席查詢生成sql獲取數據。

-- 當sum_price_channel包含所有查詢日期select?sum_price_day,channel,ptfrom?sum_price_day_channel?where?pt?>=?'20250101'?and?pt?<=?'20250105'

（2）根據維度channel、project ，20250101<= pt <= 20250105 查詢

因為落表指標沒有同時包含channel、project_id的結果表則走即席查詢邏輯

select?sum(price)?as?sum_price_day,channel,project_id,ptfrom?order_info?where?pt >=?'20250101'?and?pt <=?'20250105'group?by?channel,project_id,pt

StarRocks物化視圖

同步物化視圖

限制

• 只支持單表

• 本質上是基表的索引而不是物理表

語法

CREATE MATERIALIZED VIEW?[IF NOT EXISTS]?[database.]<mv_name>[COMMENT?""][PROPERTIES ("key"="value", ...)]AS?<query_statement>

異步物化視圖

基于default_catalog為基表創建的異步物化視圖，StarRocks 通過排除數據與基表不一致的物化視圖，來保證改寫之后的查詢與原始查詢結果的強一致性。External Catalog 創建的物化視圖由于異步刷新機制，查詢結果可能與基表上查詢的結果不一致。

限制

• 異步物化視圖不支持使用 List 分區策略，不支持基于使用 List 分區的基表創建。

• 查詢改寫只支持Cardinality Preservation Join（結果集行數不會超過輸入表中的任意一方）

• 不支持grouping set、grouping set with rollup 以及 grouping set with cube 的查詢改寫

• 分區物化視圖只支持 Range 分區

語法???????

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [database.]<mv_name>[COMMENT?""]-- 必須至少指定?distribution_desc?和?refresh_scheme?其中之一。-- distribution_desc[DISTRIBUTED BY HASH(<bucket_key>[,<bucket_key2> ...]) [BUCKETS <bucket_number>]]-- refresh_desc[REFRESH?-- refresh_moment? ? [IMMEDIATE | DEFERRED]-- refresh_scheme? ? [ASYNC | ASYNC [START (<start_time>)] EVERY (INTERVAL <refresh_interval>) | MANUAL]]-- partition_expression[PARTITION BY? ? {<date_column> | date_trunc(fmt, <date_column>)}]-- order_by_expression[ORDER BY (<sort_key>)][PROPERTIES ("key"="value", ...)]AS?<query_statement>

手動刷新視圖???????

-- 異步調用刷新任務。REFRESH MATERIALIZED?VIEW?<mv_name>;-- 同步調用刷新任務。REFRESH MATERIALIZED?VIEW?<mv_name>?WITH?SYNC MODE;

查詢加速

方案一：于StarRocks物化視圖加速即席指標

StarRocks 查詢改會校驗是否可以復用已有物化視圖中的預計算結果處理查詢，如果不能復用會去原表查詢，保證數據一致性。

（1）基于原子指標創建異步物化視圖???????

CREATE?MATERIALIZED?VIEW?sum_price_viewREFRESH ASYNC?START('2025-05-01 09:00:00')?EVERY?(interval?1?day)ASSELECT? ??sum(price),user_id,project_id,channel,ptFROM?order_info?group?by?user_id,project_id,channel,pt;

（2）根據維度channel ，20250101<= pt <= 20250105 查詢

即席查詢生成sql???????

select?sum(price)?as?sum_price_day,channel,pt? ??from?order_info?where?pt >=?'20250101'?and?pt <=?'20250105'group?by?channel,pt

因為有sum_price物化視圖，StarRocks會改寫查詢???????

select?sum(price)?as?sum_price_day,channel,pt? ??from?sum_price_view?where?pt >=?'20250101'?and?pt <=?'20250105'group?by?channel,pt

從而達到查詢加速的目的。

方案二：基于StarRocks物化視圖加速落表指標

落表指標只生成最多維度結果表，其他結果表基于最全結果表使用同步物化視圖代替。

（1）與方案一一樣也基于原子指標創建物化視圖

（2）創建所有已選維度的結果表，結果表使用range分區???????

-- 結果表分區字段設置為date類型，分區方式使用時間表達式分區-- 主鍵修改為bigint類型自增
CREATE?TABLE?IF?NOT?EXISTS?sum_price_day_channel_project_id (? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?pk ? ?bigint?AUTO_INCREMENT,? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?pt ? ? ? ? ? ? ?datetime,? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?sum_price_day ? ? ??DOUBLE,? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?channel ? ? ? ?string,? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?project_id ? ? ? ?int(11))? ??PRIMARY?KEY (pk,pt)PARTITION?BY?date_trunc('day',pt)? ? DISTRIBUTED?BY?HASH(pk)PROPERTIES (? ? "enable_persistent_index"?=?"true");
-- 基于所有維度結果表創建異步分區物化視圖CREATE?MATERIALIZED?VIEW?sum_price_day_channel_viewREFRESH ASYNCPARTITION?BY?ptASSELECT? ??sum(sum_price_day),channelFROM?sum_price_day_channel_project_idwhere?pt?=?'{pt}'group?by?channel;
CREATE?MATERIALIZED?VIEW?sum_price_day_project_viewREFRESH ASYNCPARTITION?BY?ptASSELECT? ??sum(sum_price_day),project_idFROM?sum_price_day_channel_project_idwhere?pt?=?'{pt}'group?by?project_id;

（3）落表指標任務 sql 利用物化視圖自動刷新機制，查詢sum_price_day_channel_view、sum_price_day_project_view 數據會與sum_price_day_channel_project_id結果一致，并支持查詢改寫。???????

insert OVERWRITE ?sum_price_day_channel_project_id?PARTITION(pt='20250501') (pt,sum_price_day,channel,project_id)select?str2date('20250501',?'%Y%m%d'),idx.sum_price_day,idx.channel,idx.project_id?from?（?select?sum(price)?as?sum_price_day ,channel, project_id? ??from?order_info?where?pt =?'{pt}'? ??group?by?project_id,channel,project_id;）idx

基于以上操作可以減少導入結果表次數加速任務運行，簡化取數sql結合StarRocks查詢改寫提升查詢性能。

方案三：其他優化

• 通過字典轉換string類型為integer類型提升效率。

• 有序的排序聚合 (Sorted streaming aggregate)，利用排序鍵提高group性能。

• Colocate Join 通過指定?"colocate_with" = "group_name" 參數，使相同維度數據保持在同一組 BE 節點上，從而減少數據在節點間的傳輸耗時，提升join性能。

（1）創建字典表并導入數據。???????

CREATE?TABLE?channel_dict (? ? channel STRING,? ? channel_int?BIGINT?AUTO_INCREMENT ?)PRIMARY?KEY (channel)DISTRIBUTED?BY?HASH (channel)PROPERTIES("replicated_storage"?=?"true");
CREATE?TABLE?order_id_dict (? ? order_id STRING,? ? order_id_int?BIGINT?AUTO_INCREMENT?)PRIMARY?KEY (order_id)DISTRIBUTED?BY?HASH (order_id)PROPERTIES("replicated_storage"?=?"true");
CREATE?TABLE?user_id_dict (? ? user_id STRING,? ? user_id_int?BIGINT?AUTO_INCREMENT?)PRIMARY?KEY (user_id)DISTRIBUTED?BY?HASH (user_id)PROPERTIES("replicated_storage"?=?"true");
-- 導入數據insert?into?channel_dict(channel)?select?distinct?channel?from?order_info;insert?into?order_id_dict(order_id)?select?distinct?order_id?from?order_info;insert?into?user_id_dict(user_id)?select?distinct?user_id?from?order_info;

（2）創建包含channel_integer的結果表并導入數據。???????

CREATE?TABLE?order_info_integer (? ?`order_id`?varchar(64)?NOT?NULL?COMMENT "訂單id",? ?`pt`?varchar(12)?NOT?NULL?COMMENT "用戶id",? ?`user_id`?varchar(64)?NOT?NULL?COMMENT "用戶id",? ?`price`?double?NULL?COMMENT "",? ?`project_id`?int(11)?NOT?NULL?COMMENT "產品id",? ?`channel`?varchar(64)?NULL?COMMENT "渠道"? ??-- 該列是配置 dict_mapping 的生成列，在導入數據時其列值自動從示例一中的字典表 dict 中獲取。? ??-- 后續可以直接基于該列進行去重和 JOIN 查詢。? ? channel_int?BIGINT?AS?dict_mapping('channel_dict', channel),? ? order_id_int?BIGINT?AS?dict_mapping('order_id_dict', order_id),? ? user_id?BIGINT?AS?dict_mapping('user_id_dict', user_id)) ENGINE=OLAP?PRIMARY?KEY(`order_id`, `pt`)PARTITION?BY?(`pt`)DISTRIBUTED?BY?HASH(`order_id`)PROPERTIES (? "replication_num"?=?"3",? "in_memory"?=?"false",? "enable_persistent_index"?=?"true",? "replicated_storage"?=?"true",? "compression"?=?"LZ4");
insert?into?order_info_integer (order_id,pt,user_id,price,project_id)select?order_id,pt,user_id,price,project_id?from?order_info;

（3）結果表存儲以及后續關聯都是用integer字段，會加速查詢關聯查詢。

這個方案會產生字典數據，查詢時需要查字典表進行id轉換，會帶來一定開銷，適合關聯比較頻繁的場景使用。

???????