奇数SQL Server(TSQL)查询在“WHERE”子句中使用NEWID()

背景:(不需要知道)

首先,我正在编写一个快速查询并粘贴一个UNIQUERIDENTIFIER列表,并希望它们在WHERE X IN(…)子句中是统一的.在过去,我在列表顶部使用了一个空的UNIQUERIDENTIFIER(全零),这样我就可以粘贴一组UNIQUERIDENTIFIER,它们看起来像：’XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX’.这一次,为了避免点击零,我插入了一个NEWID(),认为碰撞的几率几乎是不可能的,令我惊讶的是,这导致了数千个额外的结果,比如表的50％.

开始提问:(你需要知道的部分)

这个查询：

-- SETUP: (i boiled this down to the bare minimum)
-- just creating a table with 500 PK UNIQUERIDENTIFIERs
IF (OBJECT_ID('tempdb..#wtfTable') IS NOT NULL) DROP TABLE #wtfTable; 

CREATE TABLE #wtfTable (WtfId UNIQUEIDENTIFIER PRIMARY KEY);

INSERT INTO #wtfTable 
SELECT TOP(500) NEWID()
FROM master.sys.all_objects o1 (NOLOCK)
CROSS JOIN master.sys.all_objects o2 (NOLOCK);

-- ACTUAL QUERY:
SELECT *
FROM #wtfTable
WHERE [WtfId] IN ('00000000-0000-0000-0000-000000000000',NEWID());

……应该统计产生bupkis.但如果你运行十次左右,你有时会得到大量的选择.例如,在最后一次运行中,我收到了465/500行,这意味着超过93％的行被返回.

虽然我理解NEWID()将按行进行重新计算,但是在地狱中没有一个统计机会可以达到那么多.我在这里写的所有东西都需要产生细致入微的SELECT,删除任何东西都会阻止它发生.顺便提一下,你可以用WHERE WtfId =’…’或WtfId = NEWID()替换IN,但仍然会收到相同的结果.我正在使用SQL SERVER 2014 Standard补丁到目前为止,没有激活奇怪的设置,我知道.

所以那里的任何人都知道这是怎么回事？提前致谢.

编辑：

‘00000000-0000-0000-0000-000000000000’是一个红色的鲱鱼,这是一个与整数一起工作的版本:(有趣的是,我需要用整数将表大小提高到1000以产生有问题的查询计划……)

IF (OBJECT_ID('tempdb..#wtfTable') IS NOT NULL) DROP TABLE #wtfTable; 

CREATE TABLE #wtfTable (WtfId INT PRIMARY KEY);

INSERT INTO #wtfTable 
SELECT DISTINCT TOP(1000) CAST(CAST('0x' + LEFT(NEWID(),8) AS VARBINARY) AS INT)
FROM sys.tables o1 (NOLOCK)
CROSS JOIN sys.tables o2 (NOLOCK);

SELECT *
FROM #wtfTable
WHERE [WtfId] IN (0,CAST(CAST('0x' + LEFT(NEWID(),8) AS VARBINARY) AS INT));

或者您可以只替换文字UNIQUEIDENTIFIER并执行此操作：

DECLARE @someId UNIQUEIDENTIFIER = NEWID();

SELECT *
FROM #wtfTable
WHERE [WtfId] IN (@someId,NEWID());

两者产生相同的结果……问题是为什么会发生这种情况？

解决方法

在查询的这个特定运行中,Seek返回51行而不是估计1行.

以下实际查询生成具有相同形状的计划,但更容易分析它,因为我们有两个变量@ ID1和@ID2,您可以在计划中跟踪它们.

CREATE TABLE #wtfTable (WtfId UNIQUEIDENTIFIER PRIMARY KEY);

INSERT INTO #wtfTable 
SELECT TOP(500) NEWID()
FROM master.sys.all_objects o1 (NOLOCK)
CROSS JOIN master.sys.all_objects o2 (NOLOCK);

DECLARE @ID1 UNIQUEIDENTIFIER;
DECLARE @ID2 UNIQUEIDENTIFIER;

SELECT TOP(1) @ID1 = WtfId
FROM #wtfTable
ORDER BY WtfId;

SELECT TOP(1) @ID2 = WtfId
FROM #wtfTable
ORDER BY WtfId DESC;

-- ACTUAL QUERY:
SELECT *
FROM #wtfTable
WHERE WtfId IN (@ID1,@ID2);

DROP TABLE #wtfTable;

如果仔细检查此计划中的运算符,您将看到IN部分查询转换为包含两行和三列的表. Concatenation运算符返回此表.此帮助程序表中的每一行都定义了索引中的搜索范围.

ExpFrom    ExpTo    ExpFlags
@ID1       @ID1     62
@ID2       @ID2     62

内部ExpFlags指定需要哪种范围搜索(<,< =,>,> =).如果向IN子句添加更多变量,您将在连接到此帮助程序表的计划中看到它们.

排序和合并间隔运算符可确保合并任何可能的重叠范围.查看Fabiano Amorim的详细文章Merge Interval operator,其中检查了具有此形状的计划. Here is another good post关于Paul White的这个计划形状.

最后,带有两行的辅助表与主表连接,对于辅助表中的每一行,在从ExpFrom到ExpTo的聚簇索引中都有一个范围搜索,它在Index Seek运算符中显示. Seek运算符显示<和>,但它有误导性.实际比较由Flags值在内部定义.

如果您有一些不同的范围,例如：

WHERE 
    ([WtfId] >= @ID1 AND [WtfId] < @ID2)
    OR [WtfId] = @ID3

,您仍会看到具有相同搜索谓词的相同形状的计划,但不同的标志值.

所以,有两个寻求：

from @ID1 to @ID1,which returns one row
from @ID2 to @ID2,which returns one row

在带有变量的查询中,内部表达式会导致在需要时从变量中获取值.在查询执行期间,变量的值不会更改,并且所有内容都按预期正常运行.

NEWID()如何影响它

当我们在您的示例中使用NEWID时：

SELECT *
FROM #wtfTable
WHERE WtfId IN ('00000000-0000-0000-0000-000000000000',NEWID());

计划和所有内部处理与变量相同.

不同之处在于此内部表有效地变为：

ExpFrom    ExpTo    ExpFlags
0...0      0...0    62
NEWID()    NEWID()  62

NEWID()被调用两次.当然,每个调用产生一个不同的值,这偶然会导致覆盖表中某些现有值的范围.

聚集索引有两个范围扫描范围

from `0...0`     to `0...0`
from `some_id_1` to `some_id_2`

现在很容易看出这样的查询如何返回一些行,即使NEWID冲突的可能性非常小.

显然,优化器认为它可以调用两次NEWID,而不是记住第一个生成的随机值并在查询中进一步使用它.还有其他一些情况,优化者称NEWID比预期更多次,产生类似看似不可能的结果.

例如：

Is it legal for SQL Server to fill PERSISTED columns with data that does not match the definition?

Inconsistent results with NEWID() and PERSISTED computed column

优化器应该知道NEWID()是非确定性的.总的来说,感觉就像一个bug.

我对SQL Server内部结构一无所知,但我的猜测看起来像这样：有一些运行时常量函数,如RAND(). NEWID()被错误地归入了这个类别.然后有人注意到人们不希望它以相同的方式返回相同的ID,因为RAND()为每次调用返回相同的随机数.并且每次NEWID()出现在表达式中时,他们通过实际重新生成新ID来修补它.但是优化器的整体规则与RAND()保持一致,因此更高级别的优化器认为所有NEWID()的调用都返回相同的值并使用NEWID()自由重新排列表达式,这会导致意外结果.

关于NEWID()的类似奇怪行为还有另一个问题：

NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior

答案是说有一个Connect bug report,它被关闭为“无法修复”.微软的评论基本上说这种行为是设计的.

The optimizer does not guarantee timing or number of executions of scalar functions. This is a long-estabilished tenet. It’s the fundamental ‘leeway’ tha allows the optimizer enough freedom to gain significant improvements in query-plan execution.

（编辑：李大同）

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