主页 / dba / 问题 / 224998
Accepted
Salman A
Asked: 2018-12-15 04:13:03 +0800 CST

查找恰好具有一个值的组：COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x)

  • 772

鉴于此数据：

gid | val
1   | a
1   | a
1   | a
2   | b
3   | x
3   | y
3   | z

以下查询返回仅包含一个不同值 (val) 的组 (gid)：

SELECT gid FROM t GROUP BY gid HAVING MIN(val) = MAX(val)
SELECT gid FROM t GROUP BY gid HAVING COUNT(DISTINCT val) = 1

人们似乎建议第一个变体会更快（如果假设存在适当的索引，那么查找 MIN 和 MAX 会比计算所有值更快）。这是事实还是神话。

sql-server performance

1 个回答

  1. Best Answer

    简短的版本是，您应该期望MIN(val) = MAX(val)在所有情况下COUNT(DISTINCT val) = 1都更适合行存储查询，并且当val是字符串列时更适合列存储查询。

    我将 640 万行放入表中进行测试。数据与您的样本数据具有大致相似的数据分布：

    DROP TABLE IF EXISTS dbo.t224998_2;

CREATE TABLE dbo.t224998_2 (
gid INT,
val VARCHAR(20)
);

INSERT INTO dbo.t224998_2 WITH (TABLOCK)
SELECT 
CASE WHEN q.RN % 7 <= 2 THEN q.RN - q.RN % 7
WHEN q.RN % 7 >= 4 THEN q.RN - q.RN % 7 + 4
ELSE q.RN END
, CHAR(65 + q.RN % 7)
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 RN 
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

    这是表中的前 14 行：

    第一行

    以下是在没有任何索引的情况下运行一对查询时的查询计划：

    在此处输入图像描述

    具有MIN且MAX仅具有单个哈希聚合运算符的查询。COUNT(DISTINCT)查询有两个。对于第二个查询，最右边的运算符只保留不同的行，最左边的运算符执行计数。毫不奇怪，DISTINCT query速度大约是原来的两倍。

    创建以下索引使两个查询更具竞争力：

    CREATE INDEX IX1 ON dbo.t224998_2 (gid, val);

    现在的计划是这样的：

    在此处输入图像描述

    现在 distinct 查询大约慢了 25%。这里应该强调的是，因为这些计划都不是“查找”的MIN和值。MAX您正在查询没有过滤器的表。SQL Server 将扫描索引或表的所有行。该索引很有用，因为它可以按键顺序扫描，并且可以更有效地计算聚合。对于MIN(val) = MAX(val)查询，流聚合读取有序行并跟踪每个唯一值的最小值和最大值gid。当找到 的新值时，它将行传递给下一个运算符gid。在任何时候都不会执行索引查找来获取最小值或最大值。您可以编写一个查询来执行此操作，但它有些复杂。

    查询再次将COUNT(DISTINCT)工作分成两个聚合。两个聚合都利用了索引的排序。最右边的一个删除重复的行，最左边的一个执行计数。

    如果我将表更改为没有任何非聚集索引的列存储表，则第二个查询将成为赢家。以下是计划：

    在此处输入图像描述

    以MIN(val) = MAX(val)行模式执行所有聚合工作。批处理模式不支持返回字符串列的聚合。Microsoft记录了此限制。COUNT(DISTINCT val)由批处理模式支持，因此所有聚合工作都以批处理模式执行。MIN(val) = MAX(val)该查询的速度是选项的两倍多。

    • 7

相关问题