背景:(不需要知道)
首先,我正在编写一个快速查询并粘贴一个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.
