为何同时使用APPROX_COUNT_DISTINCT与COUNT(DISTINCT)可提升查询性能？

背景说明 #

• PTransaction表数据量超1亿行
• Users表数据量超百万行

查询示例与耗时 #

未使用APPROX_COUNT_DISTINCT的查询（耗时1分43秒） #

SELECT TransactionTypeId
        , count(1) as [Count]
        , sum(BetAmount) as [TotalBetAmount]
        , sum(PayoutAmount) as [TotalPayoutAmount]
        , sum(ResultAmount) as [TotalResultAmount]
        , count(distinct PlayerId) as [PlayerCount]
FROM PTransaction 
WHERE 
    ResultTime >= @local_StartResultTime 
    and ResultTime < @local_EndResultTime   
    and TransactionStatusId in (SELECT value FROM STRING_SPLIT(@local_TransactionStatusIdsCommaString, ','))
    and TransactionTypeId in (SELECT value FROM STRING_SPLIT(@local_TransactionTypeIdsCommaString, ','))
    and PlayerId in(SELECT Id from Users where TenantId = @local_TenantId)
group by TransactionTypeId

同时使用COUNT(DISTINCT)与APPROX_COUNT_DISTINCT的查询（耗时27秒） #

SELECT TransactionTypeId
        , count(1) as [Count]
        , sum(BetAmount) as [TotalBetAmount]
        , sum(PayoutAmount) as [TotalPayoutAmount]
        , sum(ResultAmount) as [TotalResultAmount]
        , count(distinct PlayerId) as [PlayerCount]
        , Approx_Count_distinct (PlayerId) as [PlayerCount_Approx]
FROM PTransaction 
WHERE 
    ResultTime >= @local_StartResultTime 
    and ResultTime < @local_EndResultTime   
    and TransactionStatusId in (SELECT value FROM STRING_SPLIT(@local_TransactionStatusIdsCommaString, ','))
    and TransactionTypeId in (SELECT value FROM STRING_SPLIT(@local_TransactionTypeIdsCommaString, ','))
    and PlayerId in(SELECT Id from Users where TenantId = @local_TenantId)
group by TransactionTypeId

性能提升原因分析 #

1. 执行计划策略变更：
   单独使用COUNT(DISTINCT)时，SQL Server优化器通常会选择排序去重或哈希聚合实现精确计数，这两种操作处理超大规模数据时，会消耗大量内存、CPU和IO资源——排序需要将数据加载到内存或磁盘临时空间排序，哈希聚合则要构建庞大的哈希表存储所有唯一值。

2. APPROX_COUNT_DISTINCT的低开销特性：
   APPROX_COUNT_DISTINCT基于HyperLogLog算法实现，不需要存储所有唯一值，仅通过统计哈希值前缀分布估算唯一值数量，内存占用和计算开销远低于精确去重。当查询中同时包含该函数时，优化器会调整执行计划，改用更高效的流聚合或轻量哈希聚合处理整个查询的分组和聚合逻辑，连带降低了COUNT(DISTINCT)的执行代价。

3. 规避高代价排序操作：
   部分场景下，单独的COUNT(DISTINCT)会强制查询计划引入排序步骤保证去重精度，但加入APPROX_COUNT_DISTINCT后，优化器可能跳过这种高代价排序，改用哈希聚合同时处理两个计数需求，大幅减少数据处理时间。

内容的提问来源于stack exchange，提问作者Kenneth Wong