在仅使用文字值的 WHERE 子句中替换 ISNULL() 有哪些不同的方法？

一尘不染

在仅使用文字值的 WHERE 子句中替换 ISNULL() 有哪些不同的方法？

sql

我们的示例查询针对 SQL Server 2016 上 Stack Overflow 数据库的本地副本，并查找NULL年龄或年龄 < 18 岁的用户。

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 17) < 18;

查询计划显示了一个经过深思熟虑的非聚集索引的扫描。

扫描运算符显示（由于在 SQL Server 的较新版本中添加了实际执行计划 XML）我们读取了每一个臭名昭著的行。

总的来说，我们进行了 9157 次读取并使用了大约半秒的 CPU 时间：

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 485 ms,  elapsed time = 483 ms.

问题： 有什么方法可以重写此查询以使其更高效，甚至可能是 SARGable？

随时提供其他建议。我认为我的答案不一定是答案，并且有足够多的聪明人想出可能更好的替代方案。

如果你想在自己的电脑上玩，请到这里下载 SO 数据库。

谢谢！

阅读 115

2022-10-27

共2个答案

一尘不染

有多种方法可以使用不同的 T-SQL 结构来重写它。我们将在下面查看优缺点并进行总体比较。

首先：使用OR

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE u.Age < 18
OR u.Age IS NULL;

UsingOR为我们提供了一个更有效的 Seek 计划，它读取我们需要的确切行数，但是它将技术世界调用的内容添加a whole mess of malarkey到查询计划中。

另请注意，此处执行了两次 Seek，这从图形运算符中应该更明显：

Table 'Users'. Scan count 2, logical reads 8233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 469 ms,  elapsed time = 473 ms.

其次：使用派生表和UNION ALL 我们的查询也可以这样重写

SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records);

这产生了相同类型的计划，更少的恶意，以及更明显的关于索引被搜索（搜索？）多少次的诚实程度。

它执行与OR查询相同数量的读取 (8233)，但减少了大约 100 毫秒的 CPU 时间。

CPU time = 313 ms,  elapsed time = 315 ms.

但是，您必须非常小心，因为如果此计划尝试并行，则两个单独的COUNT操作将被序列化，因为它们都被视为全局标量聚合。如果我们使用跟踪标志 8649 强制执行并行计划，问题就会变得很明显。

SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)
OPTION(QUERYTRACEON 8649);

这可以通过稍微改变我们的查询来避免。

SELECT SUM(Records)
FROM 
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)   
OPTION(QUERYTRACEON 8649);

现在执行 Seek 的两个节点都是完全并行的，直到我们点击连接运算符。

对于它的价值，完全并行的版本有一些好处。以大约 100 次读取和大约 90 毫秒的额外 CPU 时间为代价，经过的时间缩减到 93 毫秒。

Table 'Users'. Scan count 12, logical reads 8317, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 500 ms,  elapsed time = 93 ms.

交叉申请呢？ 没有魔法的答案是不完整的CROSS APPLY！

不幸的是，我们遇到了更多的问题COUNT。

SELECT SUM(Records)
FROM dbo.Users AS u 
CROSS APPLY 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2 
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2 
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

这个计划太可怕了。当你最后一次出现在圣帕特里克节时，这就是你最终的计划。虽然很好地并行，但出于某种原因，它正在扫描 PK/CX。嗯。该计划的成本为 2198 美元。

Table 'Users'. Scan count 7, logical reads 31676233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 29532 ms,  elapsed time = 5828 ms.

这是一个奇怪的选择，因为如果我们强制它使用非聚集索引，成本会显着下降到 1798 美元。

SELECT SUM(Records)
FROM dbo.Users AS u 
CROSS APPLY 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

嘿，寻找！在那边检查你。还要注意，有了的魔力CROSS APPLY，我们不需要做任何愚蠢的事情来拥有一个几乎完全平行的计划。

Table 'Users'. Scan count 5277838, logical reads 31685303, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 27625 ms,  elapsed time = 4909 ms.

COUNT如果没有这些东西，Cross apply 最终会表现得更好。

SELECT SUM(Records)
FROM dbo.Users AS u
CROSS APPLY 
(
    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

该计划看起来不错，但读取和 CPU 并没有改进。

Table 'Users'. Scan count 20, logical reads 17564, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4844 ms,  elapsed time = 863 ms.

将交叉应用重写为派生连接会导致完全相同的所有内容。我不会重新发布查询计划和统计信息——它们真的没有改变。

SELECT COUNT(u.Id)
FROM dbo.Users AS u
JOIN 
(
    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x ON x.Id = u.Id;

关系代数：为了彻底，为了不让 Joe Celko 困扰我的梦想，我们至少需要尝试一些奇怪的关系代数。这里什么都没有！

一次尝试INTERSECT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   INTERSECT
                   SELECT u.Age WHERE u.Age IS NOT NULL );

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1094 ms,  elapsed time = 1090 ms.

这是一个尝试EXCEPT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   EXCEPT
                   SELECT u.Age WHERE u.Age IS NULL);

Table 'Users'. Scan count 7, logical reads 9247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2126 ms,  elapsed time = 376 ms.

可能还有其他方法可以编写这些，但我将把它留给那些可能比我更经常使用EXCEPT的人。INTERSECT

如果您真的只需要 我在查询中使用的计数COUNT作为速记（阅读：有时我懒得想出更多涉及的场景）。如果你只需要一个计数，你可以使用一个CASE表达式来做几乎同样的事情。

SELECT SUM(CASE WHEN u.Age < 18 THEN 1
                WHEN u.Age IS NULL THEN 1
                ELSE 0 END) 
FROM dbo.Users AS u

SELECT SUM(CASE WHEN u.Age < 18 OR u.Age IS NULL THEN 1
                ELSE 0 END) 
FROM dbo.Users AS u

它们都获得相同的计划并具有相同的 CPU 和读取特性。

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 719 ms,  elapsed time = 719 ms.

获胜者，冠军？ 在我的测试中，在派生表上使用 SUM 的强制并行计划表现最好。是的，可以通过添加几个过滤索引来解释这两个谓词来帮助这些查询中的许多查询，但我想将一些实验留给其他人。

SELECT SUM(Records)
FROM 
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)   
OPTION(QUERYTRACEON 8649);

谢谢！

2022-10-27

一尘不染

我不想为一张表恢复 110 GB 的数据库，所以我创建了自己的数据。年龄分布应该与上的匹配，但显然表格本身不匹配。我不认为这是一个太大的问题，因为无论如何查询都会命中索引。我正在使用 SQL Server 2016 SP1 的 4 CPU 计算机上进行测试。需要注意的一点是，对于快速完成的查询，重要的是不要包含实际的执行计划。这会使事情变慢很多。

我首先浏览了 Erik 出色答案中的一些解决方案。对于这个：

SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records);

我从sys.dm_exec_sessions超过 10 次试验得到了以下结果（查询自然对我来说是并行的）：

╔══════════╦════════════════════╦═══════════════╗
║ cpu_time ║ total_elapsed_time ║ logical_reads ║
╠══════════╬════════════════════╬═══════════════╣
║     3532 ║                975 ║         60830 ║
╚══════════╩════════════════════╩═══════════════╝

对 Erik 工作得更好的查询实际上在我的机器上执行得更差：

SELECT SUM(Records)
FROM 
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)   
OPTION(QUERYTRACEON 8649);

10 次试验的结果：

╔══════════╦════════════════════╦═══════════════╗
║ cpu_time ║ total_elapsed_time ║ logical_reads ║
╠══════════╬════════════════════╬═══════════════╣
║     5704 ║               1636 ║         60850 ║
╚══════════╩════════════════════╩═══════════════╝

我无法立即解释为什么它这么糟糕，但不清楚为什么我们要强制查询计划中的几乎每个运算符并行。在最初的计划中，我们有一个串行区域，可以找到所有带有AGE < 18. 只有几千行。在我的机器上，我得到 9 次逻辑读取，这部分查询和 9 毫秒报告的 CPU 时间和经过的时间。还有一个用于行的全局聚合的串行区域，AGE IS NULL但每个 DOP 只处理一行。在我的机器上，这只有四行。

NULL我的结论是优化查询中查找带有for 的行的部分是最重要的，Age因为这些行数以百万计。与列上的简单页面压缩索引相比，我无法创建覆盖数据的页面更少的索引。我假设每行有一个最小索引大小，或者我尝试过的技巧无法避免大量索引空间。因此，如果我们坚持使用大约相同数量的逻辑读取来获取数据，那么使其更快的唯一方法是使查询更加并行，但这需要以不同于 Erik 使用 TF 的查询的方式完成8649. 在上面的查询中，CPU 时间与经过时间的比率为 3.62，这非常好。理想的情况是我的机器上的比率为 4.0。

一个可能的改进领域是在线程之间更均匀地分配工作。在下面的屏幕截图中，我们可以看到我的一个 CPU 决定休息一下：

索引扫描是少数可以并行实现的运算符之一，我们无法对行如何分布到线程做任何事情。它也有机会的元素，但我一直看到一个未充分利用的线程。解决此问题的一种方法是采用困难的方式进行并行处理：在嵌套循环连接的内部。嵌套循环内部的任何内容都将以串行方式实现，但许多串行线程可以同时运行。只要我们得到一个有利的并行分布方法（例如循环），我们就可以准确地控制发送到每个线程的行数。

我正在使用 DOP 4 运行查询，因此我需要将NULL表中的行平均分成四个存储桶。一种方法是在计算列上创建一堆索引：

ALTER TABLE dbo.Users
ADD Compute_bucket_0 AS (CASE WHEN Age IS NULL AND Id % 4 = 0 THEN 1 ELSE NULL END),
Compute_bucket_1 AS (CASE WHEN Age IS NULL AND Id % 4 = 1 THEN 1 ELSE NULL END),
Compute_bucket_2 AS (CASE WHEN Age IS NULL AND Id % 4 = 2 THEN 1 ELSE NULL END),
Compute_bucket_3 AS (CASE WHEN Age IS NULL AND Id % 4 = 3 THEN 1 ELSE NULL END);

CREATE INDEX IX_Compute_bucket_0 ON dbo.Users (Compute_bucket_0) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_1 ON dbo.Users (Compute_bucket_1) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_2 ON dbo.Users (Compute_bucket_2) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_3 ON dbo.Users (Compute_bucket_3) WITH (DATA_COMPRESSION = PAGE);

我不太确定为什么四个独立的索引比一个索引快一点，但这是我在测试中发现的一个。

要获得并行嵌套循环计划，我将使用未记录的跟踪标志 8649。我还将编写代码有点奇怪，以鼓励优化器不要处理不必要的行。下面是一种似乎运行良好的实现：

SELECT SUM(t.cnt) + (SELECT COUNT(*) FROM dbo.Users AS u WHERE u.Age < 18)
FROM 
(VALUES (0), (1), (2), (3)) v(x)
CROSS APPLY 
(
    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_0 = CASE WHEN v.x = 0 THEN 1 ELSE NULL END

    UNION ALL

    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_1 = CASE WHEN v.x = 1 THEN 1 ELSE NULL END

    UNION ALL

    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_2 = CASE WHEN v.x = 2 THEN 1 ELSE NULL END

    UNION ALL

    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_3 = CASE WHEN v.x = 3 THEN 1 ELSE NULL END
) t
OPTION (QUERYTRACEON 8649);

十次试验的结果：

╔══════════╦════════════════════╦═══════════════╗
║ cpu_time ║ total_elapsed_time ║ logical_reads ║
╠══════════╬════════════════════╬═══════════════╣
║     3093 ║                803 ║         62008 ║
╚══════════╩════════════════════╩═══════════════╝

通过该查询，我们的 CPU 与经过时间的比率为 3.85！我们从运行时缩短了 17 毫秒，并且只需要 4 个计算列和索引就可以完成！每个线程处理的总行数非常接近，因为每个索引的行数非常接近，并且每个线程只扫描一个索引：

最后一点，我们还可以点击简单按钮并将非集群 CCI 添加到Age列中：

CREATE NONCLUSTERED COLUMNSTORE INDEX X_NCCI ON dbo.Users (Age);

以下查询在我的机器上在 3 毫秒内完成：

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE u.Age < 18 OR u.Age IS NULL;

这将很难被击败。

2022-10-27