COUNT(DISTINCT)在大表上易触发全表扫描，根本原因是缺乏覆盖索引（如未建(created_at, user_id)联合索引），导致优化器无法下推过滤与去重，被迫逐行读取并哈希去重。
为什么 COUNT(DISTINCT) 在大表上容易触发全表扫描
COUNT(DISTINCT) 本身不强制全表扫描，但数据库优化器在缺乏合适索引或统计信息不准时，会退化为逐行读取所有匹配行再去重。尤其当 WHERE 条件选择率高（比如查最近1天数据，但没在时间字段建索引），或者 DISTINCT 字段基数极高（如 user_id）、无索引、且无法利用物化视图或覆盖索引时，MySQL/PostgreSQL 都可能放弃索引下推，直接走主键或聚簇索引全扫。
关键不是函数本身慢，而是它常暴露底层索引缺失或查询设计缺陷。
用覆盖索引让 COUNT(DISTINCT) 走索引扫描而非全表扫描
覆盖索引指查询所需的所有字段（包括 WHERE 条件列和 DISTINCT 列）都包含在同一个索引中，这样引擎无需回表，更可能复用索引有序性加速去重。
例如这张用户行为表：
CREATE TABLE events ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, event_type VARCHAR(32), created_at DATETIME );
想查最近7天内不同 user_id 数量：
❌ 错误写法（即使有 created_at 索引，仍可能全表扫描）：
SELECT COUNT(DISTINCT user_id) FROM events WHERE created_at >= '2024-06-01';
✅ 正确索引（把过滤条件和去重字段一起覆盖）：
CREATE INDEX idx_created_user ON events (created_at, user_id);
此时 PostgreSQL 可能用 Index Only Scan + HashAggregate；MySQL 8.0+ 在 user_id 非空前提下也可能用索引跳扫（Index Skip Scan）优化去重路径。
注意点：
-
created_at必须是索引最左前缀，否则无法用于范围过滤 - 如果
user_id允许 NULL，部分引擎（如 MySQL）会额外处理 NULL 值，影响索引效率 - PostgreSQL 中需确保
VACUUM定期执行，否则 Index Only Scan 可能失效
用近似去重替代精确 COUNT(DISTINCT) 降低开销 当业务允许误差（比如运营看板、趋势监控），可用概率算法绕过精确去重的内存与 CPU 开销。
PostgreSQL 示例（需安装 hll 扩展）：
SELECT #hll_add_agg(hll_hash_integer(user_id)) FROM events WHERE created_at >= '2024-06-01';
MySQL 8.0+ 可用 APPROX_COUNT_DISTINCT()（基于 HyperLogLog）：
SELECT APPROX_COUNT_DISTINCT(user_id) FROM events WHERE created_at >= '2024-06-01';
性能差异明显：精确 COUNT(DISTINCT) 内存占用随唯一值线性增长，而近似算法通常固定几 KB 内存，响应快一个数量级。
但要注意：
- 误差率通常在 0.8%~1.5%，不适用于账务、审计等强一致性场景
- MySQL 的
APPROX_COUNT_DISTINCT()不支持GROUP BY，PostgreSQL 的hll支持但需手动 merge - 近似结果不能用于分页或作为子查询条件（比如
WHERE COUNT(...) > 1000）
拆分聚合：先去重再计数，避免大中间结果集
COUNT(DISTINCT) 的执行计划里，去重操作（HashAggregate / Sort + Unique）往往发生在最后一步，导致大量重复 user_id 被反复传输、排序、哈希。若能提前缩小数据集范围，效果立竿见影。
比如原查询：
SELECT COUNT(DISTINCT user_id) FROM events WHERE status = 'active' AND created_at >= '2024-06-01';
可改写为：
SELECT COUNT(*) FROM (SELECT DISTINCT user_id FROM events WHERE status = 'active' AND created_at >= '2024-06-01') t;
看起来只是语法糖，但某些版本 PostgreSQL 会将子查询识别为“semi-join 友好结构”，启用 Unique 节点提前终止；MySQL 8.0+ 也可能触发 Loose Index Scan 优化（尤其当 user_id 是索引前缀时）。
进一步优化空间：
- 给
(status, created_at, user_id)建联合索引，让子查询完全走索引 - 若
status只有少数几个值，考虑分区表按status分区，再配合时间裁剪 - 避免在
DISTINCT字段上用函数，如COUNT(DISTINCT UPPER(email))会彻底禁用索引
EXPLAIN ANALYZE 开始——看是不是真在扫全表，还是只是估算不准吓到你了。很多“慢”其实是统计信息陈旧或绑定变量导致执行计划固化，而不是 COUNT(DISTINCT) 本身不可救。 
