count(distinct) 在大表上慢因需全量去重，依赖排序或哈希聚合，易内存溢出至tempdb；高重复、长字段、null值均加剧开销；优化需预计算哈希、过滤后分步处理或近似统计。
为什么 COUNT(DISTINCT) 在大表上特别慢
因为 SQL Server 默认用排序（Sort）或哈希（Hash Aggregate）去重，而这两者都要求把所有待去重的值暂存到内存或 tempdb。当 COUNT(DISTINCT ShipCity) 这类字段重复率高、基数低但数据量极大时，排序阶段极易触发磁盘 spill，导致 I/O 爆增；若字段本身长度大（如 nvarchar(200)），哈希桶开销也成倍上升。
常见错误现象包括：执行计划里出现红色警告“Warning: Operator used tempdb to spill data”，Hash Match (Aggregate) 或 Sort 节点耗时占比超 70%，且 tempdb 日志增长异常快。
- 不是所有索引都能加速
COUNT(DISTINCT)—— 普通非聚集索引对去重无直接帮助 -
DISTINCT作用于整行组合，哪怕只选一列，SQL Server 仍需保障该列值在结果集中唯一，底层仍是全集扫描 + 去重 - 如果字段有大量 NULL，
COUNT(DISTINCT)会自动忽略它们，但 NULL 仍参与排序/哈希过程，徒增负担
用统计信息 + 近似计数替代精确 COUNT(DISTINCT)
当业务能接受误差 APPROX_COUNT_DISTINCT()（SQL Server 2019+）。它基于 HyperLogLog 算法，内存占用恒定，不 spill，速度通常快 3–10 倍。
示例：
SELECT APPROX_COUNT_DISTINCT(ShipCity) FROM Orders;
- 注意：
APPROX_COUNT_DISTINCT()不支持GROUP BY后嵌套，也不能和窗口函数混用 - 旧版本（SQL Server 2017 及更早）可退而求其次：用
sys.dm_db_stats_properties查看列基数估计值，再结合采样估算，但需自行校准误差 - 不要在 WHERE 条件里对
APPROX_COUNT_DISTINCT()做比较（如HAVING APPROX_COUNT_DISTINCT(...) > 1000），它不能下推谓词
给高频 DISTINCT 字段建计算列 + 索引
如果必须精确结果，且该字段（如 ShipCity）更新极少、查询极多，可固化去重逻辑：加计算列并持久化，再建唯一索引模拟“预聚合”效果。
步骤：
ALTER TABLE Orders ADD ShipCityHash AS HASHBYTES('SHA2_256', ISNULL(ShipCity, '')) PERSISTED;<br>CREATE INDEX IX_Orders_ShipCityHash ON Orders(ShipCityHash);
- 后续可用
COUNT(DISTINCT ShipCityHash)替代原查询 —— 因为哈希值是定长 binary(32)，排序/哈希开销大幅下降 - 必须加
PERSISTED，否则每次计算都重新调用HASHBYTES，反而更慢 - 注意哈希碰撞概率（SHA2_256 实际可忽略），但若业务对 100% 精确有强要求，此法不适用
拆分查询 + 利用物化中间结果
当以上都不行（比如要精确、不能改表、又必须支持 GROUP BY），就放弃单条语句幻想，改用临时表分步处理：
SELECT DISTINCT ShipCity INTO #uniq_cities FROM Orders WHERE OrderDate >= '2025-01-01';<br>SELECT COUNT(*) FROM #uniq_cities;
- 关键点在于 WHERE 先过滤——避免在全量数据上做 DISTINCT
- 临时表自带统计信息，后续 COUNT(*) 走
INDEX SCAN或CLUSTERED INDEX SEEK，比原COUNT(DISTINCT)快得多 - 别用表变量（
@t），它没有统计信息，优化器常误判行数，导致计划劣化 - 如果中间结果仍太大，可进一步加索引：
CREATE UNIQUE INDEX IX_tmp ON #uniq_cities(ShipCity);
真正难优化的从来不是语法本身，而是没意识到 COUNT(DISTINCT) 的本质是“先全集去重、再计数”——它天然抗拒懒加载。任何绕过这个本质的尝试（比如试图靠一个索引直接跳过排序），最终都会撞上 tempdb 或内存墙。
