告别 Count Distinct 慢查询：StarRocks 高效去重全攻略

✨ 精选文章 用户案例
#1

1
11280×544 167 KB

在大数据分析中，去重计算（如 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?
SELECT
    lo_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
FROM
    lineorder
GROUP BY 1, 2, 3 ORDER BY 1, 2, 3 LIMIT 10;

-- Q2: How to speed up this query?
SELECT
    lo_orderdate,
     count(distinct lo_shipmode) as ndv4
FROM
    lineorder
GROUP BY 1 ORDER BY 1 LIMIT 10;;

-- Q3: How to speed up this query?
SELECT
    count(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
FROM
    lineorder;

-- Q4: How to speed up this query?
SELECT
    count(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
FROM
    lineorder
WHERE lo_orderdate >= 19930101 and lo_orderdate <= 19950101;

组合方案

优化 Count Distinct 操作有多种方法可选，但如何选择合适的方案呢？

  • 优先使用函数——函数通常是最简单、直接的优化方式。
  • 如果函数无法满足性能要求，可尝试不同的数据类型优化：使用 Bitmap 或 HLL 数据类型。将 String 转换为 Int，提高计算效率。
  • 如果 SQL 逻辑较固定，且场景对实时性要求不高，可以考虑使用物化视图（MV）进行预计算。

862×624 40.1 KB

详细技术方案：

1. 使用函数

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

862×195 12.3 KB

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

862×195 9.36 KB

根据预估的基数选择合适的类型，以避免哈希冲突。这可以与生成列结合使用。举例来说：

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，从而优化查询效率。(https://docs.starrocks.io/docs/table_design/indexes/#bitmap-indexes)。

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 合并的方式进行改写，提升查询性能。

（https://docs.starrocks.io/zh/docs/using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views/#union-改写）；

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
SELECT
    lo_orderdate,
    lo_custkey,
    lo_orderkey,
    -- ndv: multi count-distinct metrics/columns
    bitmap_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 conditions
    bitmap_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
FROM
    lineorder
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
SELECT
    lo_orderdate,
    lo_custkey,
    lo_orderkey,
    -- ndv: multi count-distinct metrics/columns with numeric types
    array_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 type
    array_distinct(array_agg(lo_shipmode)) as ndv4,
    -- ndv: the same count distinct column with different conditions
    array_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
FROM
    lineorder
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
SELECT
    lo_orderdate,
    lo_custkey,
    lo_orderkey,
    -- ndv: multi count-distinct metrics/columns
    bitmap_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 bitmap
    bitmap_union(bitmap_hash(lo_shipmode)) as ndv3,
    -- ndv: the same count distinct column with different conditions
    bitmap_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
FROM
    lineorder
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
SELECT
    lo_orderdate,
    lo_custkey,
    lo_orderkey,
    -- ndv: multi count-distinct metrics/columns
    hll_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 type
    hll_union(hll_hash(lo_shipmode)) as ndv4,
    -- ndv: the same count distinct column with different conditions
    hll_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
FROM
    lineorder
GROUP BY 1, 2, 3;

4.4 总结

在创建完上述任一物化视图后，即可对相关查询实现透明改写与加速。但在实际使用中，需关注以下要点：

  1. 去重列为 bigint 或可转换为 bigint 的 string 类型：推荐使用 bitmap_union(to_bitmap(column)) 保存去重的中间状态，便于不同维度间的聚合上卷。
  2. 去重列为普通 string 或其他非数值类型时：
  • 如需精确去重，可使用 array_distinct(array_agg(column)) 保存中间状态；
  • 如可接受近似去重，则可根据数据特点与精度要求，选择 bitmap_union(bitmap_hash(column)) 或 hll_union(hll_hash(column))。
  1. 当查询中存在多个 Count Distinct 时，若不启用改写，将默认转换为 CTE + Join 的形式，无法命中物化视图：
  • 可以通过设置参数 set materialized_view_rewrite_mode = ‘force’，使物化视图改写过程启用多阶段改写策略，从而在多 MultiCountDistinct 查询被转换为 CTE + Join 之前完成改写，实现透明加速。
  • 当前版本中，物化视图改写后的上卷算子尚未支持转为 CTE + Join，可能在特定场景下导致性能回退，后续版本将进一步优化此问题。

通过合理选型与参数配置，既可以大幅提升查询性能，又能在精度与资源之间找到最佳平衡点。希望本文的内容能为你在复杂去重计算场景中提供清晰的技术参考与实操思路。

1赞

京ICP备2022026421号-1