業務需求：統計廣告數據，生成流量漏斗，查看廣告曝光、點擊效果。

StarRocks原表結構：

CREATE TABLE `ad_events` (
`event_time` DATETIME NOT NULL COMMENT '時間',
`event_time_hour` DATETIME NOT NULL COMMENT '時間(分、秒為0)',
`event_type` TINYINT(4) NOT NULL COMMENT '事件類型（1:請求,2:展示,3:點擊）',
`user_id` INT(11) NOT NULL COMMENT '用戶ID（映射為整數）',
`jh_more` TINYINT(4) NOT NULL COMMENT '是否聚合',
`ad_site_id` VARCHAR(128) NOT NULL COMMENT '代碼位id',
`client_type` TINYINT(4) NOT NULL COMMENT '客戶端類型',
`my_app_id` VARCHAR(64) NOT NULL COMMENT '應用id',
`ad_type` VARCHAR(32) NOT NULL COMMENT '廣告類型',
`platform` TINYINT(4) NOT NULL COMMENT '廣告源',
`device_id` VARCHAR(64) NOT NULL COMMENT '設備號'
)
COLLATE='utf8_general_ci'
ENGINE=OLAP
PARTITION BY date_trunc('month', event_time) # 按月分區
DISTRIBUTED BY HASH(`user_id`) BUCKETS 100
PROPERTIES (
"compression" = "LZ4", # 存儲算法
"fast_schema_evolution" = "true",
"partition_live_number" = "12", # 保留12個月數據，就是1年的數據
"replicated_storage" = "true",
"replication_num" = "3"  # 3個副本
);

統計分析sql：

SELECT event_type, COUNT(event_type) AS event_type_nums, COUNT(DISTINCT device_id) AS dau_nums 
FROM ssp_ad_events
WHERE user_id = 10077 -- 動態參數AND event_time >= '2025-07-01 00:00:00' -- 動態參數AND event_time <= '2025-07-01 23:59:59' -- 動態參數
GROUP BY event_type;

存在問題：

1、分區字段未有效利用

使用了 event_time精確過濾，但表是按 date_trunc('month', event_time)??按月分區??，??無法利用分區裁剪，會掃描多個分區。

PARTITION BY date_trunc('month', event_time)  -- 按月分區

2、COUNT(DISTINCT)函數

??COUNT(DISTINCT) 是計算密集型操作，尤其當 device_id 數量很大時，比如上百萬個不同設備訪問，性能開銷非常高。

優化方式：

1、將分區字段改為按天分區

PARTITION BY date_trunc('day', event_time)  -- 按天分區

2、COUNT(DISTINCT device_id) —— 性能瓶頸優化

????????當 device_id是字符串類型 VARCHAR(64)，并且有大量不同設備訪問時，StarRocks 會對所有值進行去重計算??。
????????這是一個??高CPU、高內存、低效的操作??，尤其在大規模數據下，可能消耗數秒甚至更長時間。

???????使用 Bitmap 類型 + BITMAP_UNION_COUNT 實現高性能去重統計??。

? ? ? ?優化后的表結構：

CREATE TABLE `ad_events_optimized` (event_time DATETIME NOT NULL COMMENT '事件時間',event_time_hour DATETIME NOT NULL COMMENT '小時粒度時間（分秒歸零）',event_type TINYINT NOT NULL COMMENT '事件類型',user_id INT NOT NULL COMMENT '用戶ID',jhn_more TINYINT NOT NULL,ad_site_id VARCHAR(128) NOT NULL,client_type TINYINT NOT NULL,my_app_id VARCHAR(64) NOT NULL,ad_type VARCHAR(32) NOT NULL,platform TINYINT NOT NULL,device_id VARCHAR(64) NOT NULL,         -- 使用 BIGINT 存儲哈希值（避免沖突）device_id_hash BIGINT,                   -- BITMAP 列（用于聚合去重）device_bitmap BITMAP BITMAP_UNION        
)
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(user_id) BUCKETS 100
PROPERTIES ("replication_num" = "3","compression" = "LZ4"
);

????????優化后的sql：

SELECT event_type, COUNT(event_type) AS event_type_nums, bitmap_union_count(device_bitmap) AS dau_nums  -- 優化的地方
FROM ssp_ad_events
WHERE user_id = 10077AND event_time >= '2025-07-01 00:00:00'AND event_time <= '2025-07-01 23:59:59' 
GROUP BY event_type;

????????插入數據時生成 Bitmap：

-- INSERT 時直接生成
INSERT INTO ad_events_optimized (event_time, event_time_hour, event_type, user_id, jhn_more, ad_site_id, client_type, my_app_id, ad_type, platform, device_id, device_id_hash, device_bitmap
)
VALUES 
('2025-07-01 10:30:00', '2025-07-01 10:00:00', 1, 10077, 1, 'site_001', 2, 'app_123', 'banner', 1, 'device_abc123',bitmap_hash('device_abc123'),        -- 自動生成哈希to_bitmap(bitmap_hash('device_abc123'))  -- 轉為 Bitmap
);

????????bitmap_hash輸出的是64位整數，十億級數據沖突概率極低。

????????優化后性能提升10倍以上，在十億級數據量下能接近秒級響應（BE節點的資源要給夠）~

擴展：

? ? ? ? 1、使用物化視圖：物化視圖適合固定單參數，由于業務存在動態參數，暫時無法使用。

? ? ? ? 2、使用HLL：近似去重，誤差在1%~10%。

參考文檔：

使用 Bitmap 實現精確去重 | StarRocks

BITMAP | StarRocks

bitmap_hash | StarRocks

bitmap_union_count | StarRocks

使用 HyperLogLog 實現近似去重 | StarRocks

同步物化視圖 | StarRocks

告別 Count Distinct 慢查詢：StarRocks 高效去重全攻略