寻求MySQL大数据库中COUNT DISTINCT查询的提速方案

我碰到过很多类似的场景，尤其是数据量到几十万甚至百万级之后，COUNT(DISTINCT)的性能问题会特别突出。结合你的需求——实时展示3个字段的唯一值计数，这里有几个落地性很强的解决方案，你可以根据业务对实时性、精度的要求来选择：

1. 预计算统计值（最适合准实时场景） #

如果你的业务能接受几秒到几分钟的延迟，这是最简单高效的方案。核心思路是定期计算并存储统计结果，查询时直接读预存的值，完全避开全表扫描的开销。

具体步骤： #

• 先创建一个专门存储统计结果的表：

  CREATE TABLE field_unique_counts (
      id SERIAL PRIMARY KEY,
      field_a_count INT NOT NULL,
      field_b_count INT NOT NULL,
      field_c_count INT NOT NULL,
      updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  );
  -- 初始化第一条记录
  INSERT INTO field_unique_counts (field_a_count, field_b_count, field_c_count)
  VALUES (0, 0, 0);
  

• 写一个定时任务（比如数据库事件调度、crontab脚本），定期更新统计值：

  UPDATE field_unique_counts
  SET 
      field_a_count = (SELECT COUNT(DISTINCT field_a) FROM your_large_table),
      field_b_count = (SELECT COUNT(DISTINCT field_b) FROM your_large_table),
      field_c_count = (SELECT COUNT(DISTINCT field_c) FROM your_large_table),
      updated_at = CURRENT_TIMESTAMP
  WHERE id = 1;
  

• 展示数据时，直接查询这个统计表：

  SELECT field_a_count, field_b_count, field_c_count, updated_at FROM field_unique_counts WHERE id = 1;
  

优点：查询速度极快，几乎是毫秒级；对原表的查询压力为0。
缺点：结果有延迟，延迟取决于定时任务的执行频率。

2. 利用索引加速（适合绝对精确的实时场景） #

如果必须要绝对精确的实时结果，可以给每个需要统计的字段单独建索引。数据库可以利用索引的有序性来快速去重计数，避免扫描全表的原始数据。

操作示例： #

-- 给三个字段分别建B-tree索引
CREATE INDEX idx_large_table_field_a ON your_large_table(field_a);
CREATE INDEX idx_large_table_field_b ON your_large_table(field_b);
CREATE INDEX idx_large_table_field_c ON your_large_table(field_c);

注意：

• 如果字段的基数很高（比如几乎每个值都是唯一的），索引的提升可能有限，但如果基数中等（比如值的重复率较高），效果会非常明显。
• 索引会增加写入操作的开销（插入/更新/删除时需要维护索引），如果你的表写入频率很高，需要权衡写入和查询的性能。

3. 近似计数算法（适合对精度要求不严格的实时场景） #

如果业务能接受1%以内的误差，那近似计数绝对是最优解。现在主流数据库都支持基于HyperLogLog算法的近似计数函数，速度比COUNT(DISTINCT)快几个数量级，而且完全实时。

各数据库示例： #

• PostgreSQL：

  SELECT 
      approx_count_distinct(field_a) AS field_a_count,
      approx_count_distinct(field_b) AS field_b_count,
      approx_count_distinct(field_c) AS field_c_count
  FROM your_large_table;
  

• MySQL 8.0+：

  SELECT 
      APPROX_COUNT_DISTINCT(field_a) AS field_a_count,
      APPROX_COUNT_DISTINCT(field_b) AS field_b_count,
      APPROX_COUNT_DISTINCT(field_c) AS field_c_count
  FROM your_large_table;
  

优点：完全实时，查询速度超快；不占用额外的存储（除了数据库内部的算法内存）。
缺点：结果是近似值，误差在1%左右，不适合需要绝对精确的场景。

4. 分区表拆分统计（适合有分区维度的表） #

如果你的表已经按时间、地区等维度做了分区，或者可以做分区，那么可以分别统计每个分区的唯一值计数，再求和。每个分区的数据量小，统计速度会快很多，而且数据库通常能并行处理多个分区的查询。

示例（按日期分区的表）： #

-- 统计field_a的总唯一值数
SELECT SUM(partition_count) AS total_field_a_count
FROM (
    SELECT COUNT(DISTINCT field_a) AS partition_count FROM your_large_table_202401
    UNION ALL
    SELECT COUNT(DISTINCT field_a) AS partition_count FROM your_large_table_202402
    -- 依次添加所有分区
) AS partition_counts;

优点：拆分后每个分区的统计压力小，适合超大规模的表；可以结合预计算进一步优化。
缺点：需要表有合适的分区维度，否则无法使用。

内容的提问来源于stack exchange，提问作者R Wri