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

在大數據分析中，去重計算（如 Count Distinct）是一個常見但計算開銷極高的操作，尤其在高基數和高并發場景下，常常成為查詢性能的瓶頸。以用戶訪問行為為例，同一用戶一天內多次訪問頁面時，PV 會累加，而 UV 應僅記一次，這種典型的去重統計在實際業務中非常常見。然而，直接執行去重操作在數據量大、查詢頻繁的條件下，往往效率低下。

針對這一挑戰，StarRocks 提供了多種優化策略，常見做法包括：以精度換取性能、將高成本的數據類型（如 String）轉為低成本類型（如 Int/BigInt）、利用 Bitmap 或 HLL 等高效的數據結構，以及通過物化視圖實現預計算。接下來，本文將圍繞這些方案展開詳細分析，并結合實際應用場景，評估它們在性能、精度與易用性之間的權衡。

比如基于 SSB 中的lineorder表作為示例，如何加速計算基于lineorder的一些去重計算呢？

CREATE TABLE IF NOT EXISTS `lineorder` (`lo_orderkey` int(11) NOT NULL COMMENT "",`lo_linenumber` int(11) NOT NULL COMMENT "",`lo_custkey` int(11) NOT NULL COMMENT "",`lo_partkey` int(11) NOT NULL COMMENT "",`lo_suppkey` int(11) NOT NULL COMMENT "",`lo_orderdate` datetime NOT NULL COMMENT "",`lo_orderpriority` varchar(16) NOT NULL COMMENT "",`lo_shippriority` int(11) NOT NULL COMMENT "",`lo_quantity` int(11) NOT NULL COMMENT "",`lo_extendedprice` int(11) NOT NULL COMMENT "",`lo_ordtotalprice` int(11) NOT NULL COMMENT "",`lo_discount` int(11) NOT NULL COMMENT "",`lo_revenue` int(11) NOT NULL COMMENT "",`lo_supplycost` int(11) NOT NULL COMMENT "",`lo_tax` int(11) NOT NULL COMMENT "",`lo_commitdate` int(11) NOT NULL COMMENT "",`lo_shipmode` varchar(11) NOT NULL COMMENT ""
) DUPLICATE KEY(`lo_orderkey`)
PARTITION BY date_trunc('day', `lo_orderdate`);-- Q1: How to speed up this query?
SELECTlo_orderdate,lo_custkey,lo_orderkey,count (distinct lo_orderkey) as ndv10,count(distinct case when lo_revenue > 1 then lo_orderkey else 0 end) as ndv12
FROMlineorder
GROUP BY 1, 2, 3 ORDER BY 1, 2, 3 LIMIT 10;-- Q2: How to speed up this query?
SELECTlo_orderdate,count(distinct lo_shipmode) as ndv4
FROMlineorder
GROUP BY 1 ORDER BY 1 LIMIT 10;;-- Q3: How to speed up this query?
SELECTcount(distinct case when lo_discount > 1 then lo_orderkey else 0 end) as ndv11,count(distinct case when lo_revenue > 1 then lo_orderkey else 0 end) as ndv12
FROMlineorder;-- Q4: How to speed up this query?
SELECTcount(distinct case when lo_discount > 1 then lo_orderkey else 0 end) as ndv11,count(distinct case when lo_revenue > 1 then lo_orderkey else 0 end) as ndv12
FROMlineorder
WHERE lo_orderdate >= 19930101 and lo_orderdate <= 19950101;

組合方案

優化 Count Distinct 操作有多種方法可選，但如何選擇合適的方案呢？

優先使用函數——函數通常是最簡單、直接的優化方式。
如果函數無法滿足性能要求，可嘗試不同的數據類型優化：

使用 Bitmap 或 HLL 數據類型。

將 String 轉換為 Int，提高計算效率。
如果 SQL 邏輯較固定，且場景對實時性要求不高，可以考慮使用物化視圖（MV）進行預計算。

特性	精確計算	提示	近似計算	提示
函數	count (distinct)	易于使用性能不佳	ds_hll_count_distinct approx_count_distinct	易于使用結果為近似值，精度較低
Bitmap/HLL 列	Bitmap 聚合表 / 主鍵表	在高基數情況下，性能會下降	HLL 聚合鍵表	在寬表和大數據量場景下，性能（插入、合并、查詢）可能下降
數據轉換（String → Int）	全局字典	流式加載（Routine Load）可能較復雜需要創建字典	哈希函數作為生成列	需要小心處理位數問題，避免哈希沖突
物化視圖	可以根據精確度要求在物化視圖中使用不同的函數問題：異步物化視圖（Async MV）無法很好地支持實時計算，且在涉及多個不同篩選條件或維度時，靈活性較差，成本也不劃算。

詳細技術方案：

使用函數

精度越高，性能越差。精度排序（從高到低）如下：

函數名稱	精度	適用版本
count (distinct)		始終支持
ds_hll_count_distinct		3.4 及以上
approx_count_distinct	Same as hll agg table	始終支持

詳細技術方案：

1. 使用函數

精度越高，性能越差。精度排序（從高到低）如下：

2. 使用 Bitmap/HLL

2.1 HLL

優點：

相比精確去重，性能更好。
無需構建字典，因為對數據類型沒有要求。

缺點：

結果為近似去重，僅適用于聚合鍵表。
在聚合鍵表中，如果表格非常寬，選擇鍵可能會變得具有挑戰性，尤其是在復雜的篩選條件下（包括半結構化數據）。如果選擇的鍵太多，可能會影響導入/合并性能。
對于大規模聚合鍵表，讀取操作時底層存儲的合并成本可能會超過直接掃描詳細表的成本。查詢性能可能會比直接在去重鍵表上執行 COUNT DISTINCT 更差。

https://docs.starrocks.io/docs/using_starrocks/distinct_values/Using_HLL/

2.2 Bitmap

聚合鍵表
主鍵表（理論上也適用于重復鍵表，但目前尚未啟用）

優點：

提供精確的去重。

缺點：

去重列必須是 INT 類型。如果不是，則需要額外的轉換過程。
對于小型數據集，可以使用聚合表。但對于大數據集或寬表，可能會出現與 HLL 聚合表相同的問題：選擇多個鍵可能會影響導入/合并性能，并增加大數據集的查詢性能開銷。
如果表的基數極高，使用 Bitmap 可能反而會降低性能。

https://docs.starrocks.io/zh/docs/using_starrocks/distinct_values/Using_bitmap/

3. 數據類型轉換

3.1 Hash functions

函數名	位數	返回類型
murmur_hash3_32	32	int
xx_hash3_128	64	bigint
xx_hash3_64	128	largeint

根據預估的基數選擇合適的類型，以避免哈希沖突。這可以與生成列結合使用。舉例來說：

CREATE TABLE dest_hash_ge (imsi STRING,imsi_hash BIGINT AS xx_hash3_64(imsi)
);select count(distinct imsi_hash) from dest_hash_ge;

優點：

直觀且易于使用，無需外部依賴。

缺點：

精度可能會受到基數的影響而降低。

https://docs.starrocks.io/docs/category/hash/

3.2全局字典

整體方法：使用字典表將字符串轉換為整數，然后將整數插入目標表。這樣，統計不同整數的速度比統計不同字符串快得多。

優點：

不會丟失精度。

缺點：

操作繁瑣，用戶需要自己創建字典表。
不支持 Routine Load。因為它需要兩個 Routine Load，無法保證在持續導入過程中先將數據寫入字典表，再寫入目標表。

https://docs.starrocks.io/docs/using_starrocks/query_acceleration_with_auto_increment/

4. 物化視圖

4.1 構建物化視圖

在物化視圖構建的過程中，我們需要考慮以下幾個關鍵點：

4.1.1 數據分布

物化視圖的數據分布是構建時首要考慮的因素，對查詢性能和刷新穩定性影響顯著。

分區：如果基表按日期分區且數據量較大，為保證刷新效率與查詢性能，需要為物化視圖設置合理的分區與分桶。分區后，刷新可按分區粒度調度，降低整體資源消耗并確保刷新過程穩定。
分桶：查詢中若經常包含分區及分桶字段過濾條件，可借助分區與分桶裁剪顯著提升查詢性能；若存在多個分桶鍵，應將查詢中最常用的分桶列置于首位，以便更好地利用分桶裁剪。
Colocate 屬性：除了分區與分桶，還應關注 Colocate 屬性，它能優化聚合計算的資源消耗，從而進一步提升查詢性能。

4.1.2 索引

可在物化視圖之上添加必要的索引，以提升帶謂詞的查詢性能。類似于 StarRocks 內表，可以針對過濾列添加適當的 Bitmap 或 BloomFilter 索引，以減少磁盤 I/O，從而優化查詢效率。(Indexes | StarRocks)。

4.1.3 通用性

用戶通常期望構建的物化視圖具備一定的通用性，以便在性能與維護成本之間取得平衡，減少物化視圖數量并降低維護代價。為了實現這一目標，可采用以下策略：

合并多個具有相似聚合維度的 MV：若維度相同，可直接合并不同的聚合指標；若維度不同，可通過添加到GroupBy Keys 的方式合并（需注意維度基數）；
將謂詞轉換成維度列（需要考慮維度基數）；
在聚合物化視圖的 Projection 列中避免復雜表達式計算，有助于提升上層查詢的復用率。
在聚合維度中增加分區列和分桶列，可優化數據分布，降低聚合計算資源消耗，并進一步加速查詢性能。

4.1.4 數據刷新

當前所有物化視圖均采用分區設計，并統一設置為 Manual（手動）刷新方式，用于同步物化視圖與基表數據。之所以選擇手動刷新，是因為基表經常發生刪表重建操作，如果采用自動刷新，將可能導致物化視圖數據過期并觸發全量重刷，造成大量資源與時間浪費。

考慮到基表數據量較大，且查詢通常只涉及最近 6 個月的數據，在刷新過程中會通過手動指定刷新范圍，僅同步最近分區。例如：

REFRESH MATERIALIZED VIEW mv1 PARTITION START ("2023-04-01") END ("2023-09-30");

后續可通過設置 partition_ttl 或 partition_ttl_number 參數，自動控制物化視圖僅保留最近時間范圍或指定數量的分區。

4.1.5 時效性問題

為保障Query在物化視圖改寫的過程中的嚴格一致，目前物化視圖改寫默認只對已經刷新的數據改寫。但如果基表有更新，目前可以通過如下方式：

為保障在物化視圖改寫過程中的查詢嚴格一致性，StarRocks 默認僅對已刷新完成的物化視圖進行改寫。但在基表發生更新時，可以通過以下方式調整：

query_rewrite_consistency : 調整為loose ，不校驗物化視圖的時效性，計算結果以物化視圖為準，而不再以查詢本身為準；
mv_rewrite_staleness_second : 調整物化視圖改寫可以接受的最大延遲時間（單位：秒)；
Union Rewrite :對于必須保證嚴格一致性的查詢，可在改寫過程中將基表數據與物化視圖結果通過 Union 合并的方式進行改寫，提升查詢性能。

（物化視圖查詢改寫 | StarRocks）；

4.2 基于 MV 加速去重計算

針對前文中的幾個 Query，可根據上述物化視圖構建策略，在不同場景下（精確去重或非精確去重）構建合適的物化視圖，以加速計算。

4.2.1 精確去重

精確去重旨在確保基于物化視圖計算的結果與直接執行 COUNT(DISTINCT) 查詢的結果完全一致。

基于 Bitmap+MV 加速精確去重

去重列為數值類型（INT / BIGINT / BOOL / SMALLINT / TINYINT），將輸入值映射至 Bitmap 中，可以獲取相應列的聚合狀態；
去重列為（LARGEINT / VARCHAR）時，若輸入列可隱式轉換為 UINT64，則仍可利用 Bitmap 實現精確去重；

若無法轉換，則這些值會被視為 NULL。

CREATE MATERIALIZED VIEW `test_mv1` 
PARTITION BY (`lo_orderdate`)
DISTRIBUTED BY HASH(lo_orderdate, lo_custkey, lo_orderkey)
REFRESH DEFERRED MANUAL
PROPERTIES ("replication_num" = "1"
)
AS
SELECTlo_orderdate,lo_custkey,lo_orderkey,-- ndv: multi count-distinct metrics/columnsbitmap_union(to_bitmap(lo_orderkey)) as ndv10,bitmap_union(to_bitmap(lo_linenumber)) as ndv2,bitmap_union(to_bitmap(lo_orderpriority)) as ndv3,-- DANGER: lo_shipmode is string type only works if lo_shipmode can cast to bigint implicitly, -- otherwise null for non-casted rows.bitmap_union(to_bitmap(lo_shipmode)) as ndv4,-- ndv: the same count distinct column with different conditionsbitmap_union(to_bitmap(case when lo_discount > 1 then lo_orderkey else 0 end)) as ndv11,bitmap_union(to_bitmap(case when lo_revenue > 1 then lo_orderkey else 0 end)) as ndv12
FROMlineorder
GROUP BY 1, 2, 3;

基于 ArrayAgg + MV 加速精確去重

支持任意輸入類型（NUMERIC / STRING / ARRAY / STRUCT / MAP 等）。
由于會將所有值保存在內存中，如果該列的ndv很大，會影響查詢加速性能；

CREATE MATERIALIZED VIEW `test_mv2` 
PARTITION BY (`lo_orderdate`)
DISTRIBUTED BY HASH(lo_orderdate, lo_custkey, lo_orderkey)
REFRESH DEFERRED MANUAL
PROPERTIES ("replication_num" = "1"
)
AS
SELECTlo_orderdate,lo_custkey,lo_orderkey,-- ndv: multi count-distinct metrics/columns with numeric typesarray_distinct(array_agg(lo_orderkey)) as ndv10,array_distinct(array_agg(lo_linenumber)) as ndv2,array_distinct(array_agg(lo_orderpriority)) as ndv3,-- ndv: input is string typearray_distinct(array_agg(lo_shipmode)) as ndv4,-- ndv: the same count distinct column with different conditionsarray_distinct(array_agg(case when lo_discount > 1 then lo_orderkey else 0 end)) as ndv11,array_distinct(array_agg(case when lo_revenue > 1 then lo_orderkey else 0 end)) as ndv12
FROMlineorder
GROUP BY 1, 2, 3;

4.2.2 非精確去重

精確去重無法保證基于物化視圖計算的結果依然是精確的，與直接使用 count distinct 相比，結果可能存在誤差。因此，在實際使用中需要根據具體業務場景權衡選擇。

基于 Bitmap + MV 加速近似去重

使用bitmap_hash 代替to_bitmap，但由于其實現原理不同，存在因 hash 沖突導致去重結果不精確問題；
其原理是先通過計算輸入的hash 值（取值范圍為 0 ~ 2^32 區間的 unsigned int），再將該 hash 值記錄到Bitmap 中；

CREATE MATERIALIZED VIEW `test_mv3` 
PARTITION BY (`lo_orderdate`)
DISTRIBUTED BY HASH(lo_orderdate, lo_custkey, lo_orderkey)
REFRESH DEFERRED MANUAL
PROPERTIES ("replication_num" = "1"
)
AS
SELECTlo_orderdate,lo_custkey,lo_orderkey,-- ndv: multi count-distinct metrics/columnsbitmap_union(bitmap_hash(lo_orderkey)) as ndv10,bitmap_union(bitmap_hash(lo_linenumber)) as ndv2,bitmap_union(bitmap_hash(lo_orderpriority)) as ndv3,-- For string type input, use bitmap_hash to compute its hash(0~max(uint64) which-- may be hash-conflict and store the hash value into bitmapbitmap_union(bitmap_hash(lo_shipmode)) as ndv3,-- ndv: the same count distinct column with different conditionsbitmap_union(bitmap_hash(case when lo_discount > 1 then lo_orderkey else 0 end)) as ndv11,bitmap_union(bitmap_hash(case when lo_revenue > 1 then lo_orderkey else 0 end)) as ndv12
FROMlineorder
GROUP BY 1, 2, 3;

基于 HLL+MV 加速精確去重

對輸入列類型沒有限制，對輸入類型基于hyperloglog 格式進行存儲；

CREATE MATERIALIZED VIEW `test_mv4` 
PARTITION BY (`lo_orderdate`)
DISTRIBUTED BY HASH(lo_orderdate, lo_custkey, lo_orderkey)
REFRESH DEFERRED MANUAL
PROPERTIES ("replication_num" = "1"
)
AS
SELECTlo_orderdate,lo_custkey,lo_orderkey,-- ndv: multi count-distinct metrics/columnshll_union(hll_hash(lo_orderkey)) as ndv10,hll_union(hll_hash(lo_linenumber)) as ndv2,hll_union(hll_hash(lo_orderpriority)) as ndv3,-- ndv: column with string typehll_union(hll_hash(lo_shipmode)) as ndv4,-- ndv: the same count distinct column with different conditionshll_union(hll_hash(case when lo_discount > 1 then lo_orderkey else 0 end)) as ndv11,hll_union(hll_hash(case when lo_revenue > 1 then lo_orderkey else 0 end)) as ndv12
FROMlineorder
GROUP BY 1, 2, 3;

4.4 總結

在創建完上述任一物化視圖后，即可對相關查詢實現透明改寫與加速。但在實際使用中，需關注以下要點：

去重列為 bigint 或可轉換為 bigint 的 string 類型：推薦使用 bitmap_union(to_bitmap(column)) 保存去重的中間狀態，便于不同維度間的聚合上卷。
去重列為普通 string 或其他非數值類型時：

如需精確去重，可使用 array_distinct(array_agg(column)) 保存中間狀態；
如可接受近似去重，則可根據數據特點與精度要求，選擇 bitmap_union(bitmap_hash(column)) 或 hll_union(hll_hash(column))。

當查詢中存在多個 Count Distinct 時，若不啟用改寫，將默認轉換為 CTE + Join 的形式，無法命中物化視圖：

可以通過設置參數 set materialized_view_rewrite_mode = ‘force’，使物化視圖改寫過程啟用多階段改寫策略，從而在多 MultiCountDistinct 查詢被轉換為 CTE + Join 之前完成改寫，實現透明加速。
當前版本中，物化視圖改寫后的上卷算子尚未支持轉為 CTE + Join，可能在特定場景下導致性能回退，后續版本將進一步優化此問題。

通過合理選型與參數配置，既可以大幅提升查詢性能，又能在精度與資源之間找到最佳平衡點。希望本文的內容能為你在復雜去重計算場景中提供清晰的技術參考與實操思路。