使用DISTINCT COUNT进行性能优化

Question

我正在运行SQL Server 2012。

我有一个查询，当它被简化到最基本的形式时，看起来像这样：

SELECT COUNT(DISTINCT fullAddress) as quickCount 
FROM leads
WHERE yearID >=12 AND yearID <=21

潜在客户表中有大约1.49亿条记录。其中，leadID具有聚集索引，YearID具有非聚集索引，并且包含fullAddress。

当前查询需要大约40秒的时间来运行。我认识到这已经不错了，但在这种情况下，速度还不够快。

我查看了执行计划，据我所知，大约60%的成本是由于DISTINCT COUNT造成的。

如果我按以下方式运行同样的查询但不使用DISTINCT COUNT：

SELECT COUNT(*) as quickCount 
FROM leads
WHERE yearID >=12 AND yearID <=21

运行时间只需要1秒。

不幸的是，我需要得到不同全地址的数量。所以我在尝试想出如何使第一个查询运行更快。

这里是两个查询的执行计划截图：

这是一个链接，可以看到更大的截图 - http://www.sequenzia.com/execPlan.jpg

从我所能看到的，我的主要问题是独特排序（52%）。

任何关于此的帮助或反馈都将是极好的。

谢谢！

更新

我采纳了Thilo的建议并应用了这个索引：

 CREATE INDEX IDX_X ON LEADS(FULLADDRESS, YEARID);

我实际上创建了2个新的测试表，每个表中都有完全相同的100万条记录。我在两个表上都应用了原始索引，然后只在其中一个表上应用了上述索引。现在，当我在相同的执行计划下比较这两个表时，具有上述索引的表略好一些，为48％到52％。这是新的执行计划 - http://www.sequenzia.com/execPlan2.jpg

这对我有所帮助，但我确实需要更高的性能。还有其他想法吗？

- Sequenzia

Answer 1

建议尝试取消排序，通过对fullAddress进行索引排序（还要包括yearID列以满足where子句）。

CREATE INDEX IDX_X ON LEADS(FULLADDRESS, YEARID);

这样做，您应该得到一个快速的全索引扫描（可能仍然比非唯一计数的索引范围扫描慢，但希望比您的40秒排序更快）。

但为什么需要这么快呢？这不是每次都需要做的事情，对吧？如果这是用于公共网站，我认为可以使用稍微过时的缓存结果。

- Thilo

Answer 2

这个答案并不能帮助解决原问题(SQL Server 2012)，它只适用于Azure SQL DB和SQL Server 2019+。

##近似计数公开预览在Azure SQL Database中

近似查询处理是一系列新功能，旨在提供对非常大的数据集的聚合，其中响应速度比绝对精度更为关键。...

新的APPROX_COUNT_DISTINCT聚合函数返回组中唯一非空值的近似数量。

##优势

APPROX_COUNT_DISTINCT使用的内存明显少于详尽准确的COUNT DISTINCT。因此，即使数据集中有数十亿行，在不溢出到磁盘的情况下，也更有可能在内存中完成计算。在COUNT DISTINCT运行时会耗尽内存并将数据溢出到TempDB，从而导致极大的性能损失，而APPROX_COUNT_DISTINCT通常不会发生这种情况。

部分原因是由于不将大型数据集溢出到TempDB，部分原因是由于算法内部，对于非常大的数据集，APPROX_COUNT_DISTINCT的执行速度比COUNT DISTINCT快得多。

更多信息请参见APPROX_COUNT_DISTINCT文档

SELECT APPROX_COUNT_DISTINCT(fullAddress) as quickCount 
FROM leads
WHERE yearID >=12 AND yearID <=21

- Lukasz Szozda

---

谢谢，这非常有帮助，因为我的情况是获得数量级，而不是精确数字。 - Pavel Gomon

Answer 3

在执行distinct计数之前，我会尝试使用子查询进行筛选

WITH cte as (SELECT ID
FROM leads
WHERE yearID >=12 AND yearID <=21)
SELECT COUNT(DISTINCT fullAddress) as quickCount 
FROM leads
JOIN cte on cte.ID = leads.ID

这并不在每种情况下都表现得更快，但据我所知，在您对一个不进行去重操作的列设置了where子句时，它会更快。

- Patrick Mize

Answer 4

如果您不经常更新此表，可以考虑使用正在运行的查询创建索引视图 http://msdn.microsoft.com/en-us/library/aa933148(v=sql.80).aspx

- Renats Stozkovs