sql-server – 在仅行使笔墨值的WHERE子句中替代ISNULL()的差异

这不是什么：

这不是关于接管用户输入或行使变量的catch-all queries的题目.

这严酷来说,在WHERE子句中行使ISNULL()将NULL值替代为canary值以与谓词举办较量,以及在SQL Server中将这些查询重写为SARGable的差异要领.

你为什么不在何处坐？

我们的示例查询针对SQL Server 2016上的Stack Overflow数据库的当地副本,并查找具有NULL年数或年数

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

查询打算表现扫描很是殷勤的非聚簇索引.

扫描运算符表现(因为在更新版本的SQL Server中添加了现实执行打算XML),我们读取了每个stinkin’行.

总的来说,我们执行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？

随意提供其他提议.我以为我的谜底不必然是谜底,而且有足够的智慧人在哪里提出也许更好的更换方案.

假如你想在本身的电脑上玩,请到这里download the SO database.

感谢！

办理要领

有多种要领可以行使差异的T-SQL布局重写它.我们将看看利弊,并在下面举办整体较量.

起首：行使OR

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

行使OR为我们提供了一个更有用的Seek打算,该打算读取了我们必要简直切行数,可是它增进了技能界称为查询打算的一整套malarkey.

另请留意,Seek在这里执行了两次,从图形操纵符开始真的应该更明明：

Table 'Users'. Scan count 2,logical reads 8233,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);

这发生了沟通范例的打算,更少的malarkey,以及关于索引被寻求(寻求？)的次数的更明明的厚道水平.

它与OR查询执行沟通数目的读取(8233),但减少约莫100ms的CPU时刻.

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

可是,你必需很是警惕,由于假如这个打算试图并行,那么两个单独的COUNT操纵将被序列化,由于它们都被以为是全局标量聚合.假如我们行使Trace Flag 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多次读取和约莫90ms的特殊CPU时刻为价钱,颠末的时刻收缩到93ms.

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

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

CROSS APPLY怎么样？
没有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. EW.该打算的本钱为2198美元.

Table 'Users'. Scan count 7,logical reads 31676233,lob read-ahead reads 0.
Table 'Worktable'. Scan count 0,logical 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,lob read-ahead reads 0.

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

假如没有COUNT的对象,交错申请最终会更好.

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并不是一种改造.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!