【业务影响】查询感觉较慢
【是否存算分离】否
【StarRocks版本】3.1.6
【集群规模】1FE+2BE
【机器信息】96C/128G/千兆网
【详述】同样是1亿3千万数据,在MySQL中点查只需要0.06s,在SR中将表物化为分区视图,并且创建了索引,点查时间不太稳定快的时候0.3s,慢的时候大概要1s,下面是对应的profile
0.3秒profile.txt (47.9 KB)
0.8秒profile.txt (47.3 KB)
求助:Deploy耗时较长,无法定位发生原因
sp_traffic_mv_target是物化视图吗?
是的。这是它的建表语句
CREATE MATERIALIZED VIEW sp_traffic_mv_target
(created_at
, marketplace_id
, advertiser_id
, time_window_start
, campaign_id
, ad_group_id
, ad_id
, keyword_id
, placement
, currency
, cost
, impressions
, clicks
,
INDEX index1 (marketplace_id
) USING BITMAP,
INDEX index2 (campaign_id
) USING BITMAP,
INDEX index3 (time_window_start
) USING BITMAP,
INDEX index4 (keyword_id
) USING BITMAP)
PARTITION BY (created_at
)
DISTRIBUTED BY HASH(created_at
, marketplace_id
, advertiser_id
, time_window_start
, campaign_id
, ad_group_id
, ad_id
, keyword_id
, placement
, currency
)
REFRESH ASYNC START(“2024-01-08 16:30:00”) EVERY(INTERVAL 30 MINUTE)
PROPERTIES (
“replicated_storage” = “true”,
“replication_num” = “2”,
“storage_medium” = “HDD”
)
AS SELECT sp_traffic
.created_at
, sp_traffic
.marketplace_id
, sp_traffic
.advertiser_id
, sp_traffic
.time_window_start
, sp_traffic
.campaign_id
, sp_traffic
.ad_group_id
, sp_traffic
.ad_id
, sp_traffic
.keyword_id
, sp_traffic
.placement
, sp_traffic
.currency
, sum(sp_traffic
.cost
) AS cost
, sum(sp_traffic
.impressions
) AS impressions
, sum(sp_traffic
.clicks
) AS clicks
FROM amazon_marketing_stream
.sp_traffic
WHERE (sp_traffic
.time_window_start
>= (now() - INTERVAL 8 DAY)) AND (sp_traffic
.match_type
IN (‘TARGETING_EXPRESSION’, ‘TARGETING_EXPRESSION_PREDEFINED’))
GROUP BY sp_traffic
.marketplace_id
, sp_traffic
.advertiser_id
, sp_traffic
.time_window_start
, sp_traffic
.campaign_id
, sp_traffic
.ad_group_id
, sp_traffic
.ad_id
, sp_traffic
.keyword_id
, sp_traffic
.placement
, sp_traffic
.currency
, sp_traffic
.created_at
;
麻烦试下建mv的时候考虑以下几点再测试下:
- 将keyword_id置于第一列
- DISTRIBUTED BY HASH里不用太多,1-3列能保证数据均匀的列即可
比如:
CREATE MATERIALIZED VIEWsp_traffic_mv_target
(keyword_id
,created_at
,marketplace_id
,advertiser_id
,time_window_start
,campaign_id
,ad_group_id
,ad_id
,placement
,currency
,cost
,impressions
,clicks
,
INDEX index1 (marketplace_id
) USING BITMAP,
INDEX index2 (campaign_id
) USING BITMAP,
INDEX index3 (time_window_start
) USING BITMAP,
INDEX index4 (keyword_id
) USING BITMAP)
PARTITION BY (created_at
)
DISTRIBUTED BY HASH(keyword_id
)
收到,正在尝试,感谢
有作用,维持在150ms左右,之前可以达到30~70ms这样
之前30-60ms,现在变慢了吗