COUNT(DISTINCT) 与 GROUP BY 去重统计：5 亿数据量下的性能实测与选型指南

在数据分析和处理领域，去重统计是最基础也是最频繁使用的操作之一。当数据量达到亿级规模时，不同的去重统计方法在性能上可能产生天壤之别。本文将基于 5 亿行数据的实测环境，深入对比COUNT(DISTINCT)与GROUP BY两种去重统计方案的性能差异，并提供生产环境下的选型建议。

1. 去重统计的基本原理与语法差异

去重统计的核心目标是计算某列中不同值的数量。SQL 提供了两种主流实现方式：

-- 方法1：COUNT(DISTINCT) SELECT COUNT(DISTINCT user_id) FROM user_behavior; -- 方法2：GROUP BY 子查询 SELECT COUNT(*) FROM ( SELECT user_id FROM user_behavior GROUP BY user_id ) AS temp;

这两种语法在功能上等价，但在底层执行机制上存在本质区别：

• COUNT(DISTINCT)：数据库引擎会在内存中维护一个哈希表，用于快速判断值是否已存在
• GROUP BY：先对数据进行分组聚合，再统计分组后的行数

关键差异点对比：

2. 5 亿数据量下的基准测试设计

为准确评估两种方法的性能差异，我们设计了以下测试环境：

测试数据集：

• 数据量：5 亿行用户行为记录
• 字段结构：

  CREATE TABLE user_behavior ( id BIGINT PRIMARY KEY, user_id VARCHAR(32) NOT NULL, item_id VARCHAR(32) NOT NULL, behavior_type TINYINT, timestamp DATETIME, INDEX idx_user (user_id), INDEX idx_item (item_id) );

• 去重基数：约 5000 万独立 user_id

测试环境配置：

• 服务器：AWS EC2 r5.4xlarge (16 vCPU, 128GB RAM)
• 数据库：MySQL 8.0.28 (InnoDB)
• 缓冲池：96GB
• 连接池：HikariCP (20 connections)

测试指标：

• 执行时间（3次取平均）
• CPU 使用率（通过SHOW PROFILE获取）
• 内存消耗（通过 Performance Schema 监控）
• 磁盘 I/O（通过iostat监控）

3. 实测性能数据对比

经过多次测试，我们得到以下关键指标：

执行时间对比：

资源消耗对比：

执行计划分析：

-- COUNT(DISTINCT) 执行计划 -> Aggregate: count(distinct user_behavior.user_id) -> Index scan on user_behavior using idx_user -- GROUP BY 子查询执行计划 -> Aggregate: count(0) -> Table scan on <temporary> -> Temporary table -> Group (no aggregates) -> Index scan on user_behavior using idx_user

从执行计划可以看出，GROUP BY方案需要创建临时表来处理分组结果，这是其内存消耗较高的主要原因。

4. 深度优化技巧与实践建议

基于实测结果，我们针对不同场景给出以下优化建议：

4.1 COUNT(DISTINCT) 优化方案

适用场景：

• 去重列基数较低（<1000万）
• 内存资源充足
• 需要简单直观的语法

优化手段：

1. 确保使用合适的索引：

   ALTER TABLE user_behavior ADD INDEX idx_user_covering (user_id, id);

2. 调整内存参数：

   # MySQL 配置 tmp_table_size = 256M max_heap_table_size = 256M

3. 使用近似计数（适用于可接受误差的场景）：

   SELECT COUNT_APPROX_DISTINCT(user_id) FROM user_behavior;

4.2 GROUP BY 优化方案

适用场景：

• 超大数据量（>10亿行）
• 需要并行处理
• 去重后还需其他聚合操作

优化手段：

1. 强制使用索引：

   SELECT COUNT(*) FROM ( SELECT user_id FROM user_behavior FORCE INDEX(idx_user) GROUP BY user_id ) AS temp;

2. 分批次处理：

   -- 按ID范围分批处理 SELECT SUM(cnt) FROM ( SELECT COUNT(*) AS cnt FROM ( SELECT user_id FROM user_behavior WHERE id BETWEEN 1 AND 100000000 GROUP BY user_id ) t1 UNION ALL SELECT COUNT(*) FROM ( SELECT user_id FROM user_behavior WHERE id BETWEEN 100000001 AND 200000000 GROUP BY user_id ) t2 -- 更多批次... ) final;

3. 使用物化视图（MySQL 8.0+）：

   CREATE MATERIALIZED VIEW user_distinct_mv AS SELECT user_id FROM user_behavior GROUP BY user_id; SELECT COUNT(*) FROM user_distinct_mv;

4.3 新型数据库的替代方案

对于超大规模数据集，传统关系型数据库可能不是最佳选择。以下替代方案值得考虑：

1. ClickHouse：

   -- 使用专门优化的uniqExact函数 SELECT uniqExact(user_id) FROM user_behavior;

2. Apache Spark：

   # PySpark示例 df = spark.read.parquet("hdfs://user_behavior.parquet") df.select("user_id").distinct().count()

3. 预计算方案：

   • 使用调度系统定期计算去重结果
   • 将结果存储在Redis等高速缓存中

5. 生产环境选型决策树

基于以上分析，我们总结出以下决策流程：

1. 数据量评估：

   • <1亿行：优先考虑COUNT(DISTINCT)
   • 1-10亿行：根据资源情况选择

   • 10亿行：考虑GROUP BY或替代方案

2. 系统资源评估：

   • 内存充足：COUNT(DISTINCT)
   • 内存受限：GROUP BY分批次处理

3. 实时性要求：

   • 实时查询：优化COUNT(DISTINCT)
   • 准实时：预计算+缓存

4. 数据库类型：

   • OLTP数据库：COUNT(DISTINCT)
   • OLAP数据库：使用原生高效函数

最终建议：在5亿行数据规模下，GROUP BY方案整体表现更优，特别是在合理优化后，执行时间可进一步缩短30%以上。对于需要频繁执行的去重统计，建议建立定期更新的物化视图。