泉州网站设计招聘网,宁波企业网站制作哪家好,企业商标图案大全,公司网站一年多少钱在大数据分析中#xff0c;去重计算#xff08;如 Count Distinct#xff09;是一个常见但计算开销极高的操作#xff0c;尤其在高基数和高并发场景下#xff0c;常常成为查询性能的瓶颈。以用户访问行为为例#xff0c;同一用户一天内多次访问页面时#xff0c;PV 会累…在大数据分析中去重计算如 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) 查询的结果完全一致。
基于 BitmapMV 加速精确去重 去重列为数值类型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;
基于 HLLMV 加速精确去重 对输入列类型没有限制对输入类型基于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可能在特定场景下导致性能回退后续版本将进一步优化此问题。
通过合理选型与参数配置既可以大幅提升查询性能又能在精度与资源之间找到最佳平衡点。希望本文的内容能为你在复杂去重计算场景中提供清晰的技术参考与实操思路。
