积极答复者
关于索引假脱机的问题

问题
-
有人说是newid()函数的问题 有人说是索引假脱机的问题
请看下面测试SQL
USE [tempdb] GO create table #tb(aa int,bb char(1)) GO insert #tb values(1,'A') insert #tb values(1,'B') insert #tb values(1,'C') insert #tb values(1,'D') insert #tb values(2,'E') insert #tb values(2,'F') insert #tb values(2,'G') insert #tb values(2,'H') insert #tb values(3,'I') insert #tb values(3,'J') insert #tb values(3,'K') insert #tb values(3,'L')
--SQL1 SELECT * FROM #tb a WHERE bb IN ( SELECT TOP 1 bb FROM #tb WHERE aa=a.aa ORDER BY NEWID() ) --SQL2 SELECT * FROM #tb a WHERE bb = ( SELECT TOP 1 bb FROM #tb WHERE aa=a.aa ORDER BY NEWID() ) --drop table tb
SQL1的执行计划
SQL2的执行计划
答案
-
这个应该是查询优化器对于表达式和子查询判定上的一个差异
对于 = 运算符, 其语法定义是: expression= expression
对于 IN, 其语法定义是:
test_expression [ NOT ] IN
( subquery | expression [ ,...n ]
)根据楼主的语句, 第一个使用的是IN, 所以IN 里面的, 被查询优化器判定为 subquery (子查询)
而第二个使用的是 =, 所以查询优化器判定 = 后面的是一个表达式
因此在判定查询方法的时候, 产生了差异, 对于查询, 查询优化器认为具有不确定性, 所以每条记录都要去执行一次子查询; 而对于表达式, 查询优化器认为它具有确定性, 所以对于每个 aa, 计算一次就行了
如果把楼主的第一个查询中的 IN, 也改成表达式, 则可以看到会使用与查询2一样的执行计划, 结果也也查询2一样, 有固定的记录数
SELECT * FROM #tb a WHERE bb IN ( SELECT ( SELECT TOP 1 bb FROM #tb WHERE aa=a.aa ORDER BY NEWID() ) )
- 已标记为答案 Amy PengMicrosoft employee, Moderator 2013年4月22日 8:28
全部回复
-
有人给出的解释是:
对于相同的aa, WHERE aa=a.aa 这个条件是一样的,所以对于相同的aa,不会再重新进行输入,也就是说 对于 1, a 1, b 1, c 只会拿第一次的 top 1 bb作为输入,所以对于4个外表里的bb,肯定也只会找到一个匹配的。 所以用 = 时,永远只会有三行产生。 当用 in 时,因为没有采用 lazy spool,所以每次都是重新随机输入,所以数目是不固定的。
但我觉得
对于相同的aa, WHERE aa=a.aa 这个条件是一样的 那么两个查询都是一样的啊,跟索引假脱机没有什么关系
有人说跟newid()函数有关,我觉得跟newid()也没有什么关系,不知道大家的看法? -
这个应该是查询优化器对于表达式和子查询判定上的一个差异
对于 = 运算符, 其语法定义是: expression= expression
对于 IN, 其语法定义是:
test_expression [ NOT ] IN
( subquery | expression [ ,...n ]
)根据楼主的语句, 第一个使用的是IN, 所以IN 里面的, 被查询优化器判定为 subquery (子查询)
而第二个使用的是 =, 所以查询优化器判定 = 后面的是一个表达式
因此在判定查询方法的时候, 产生了差异, 对于查询, 查询优化器认为具有不确定性, 所以每条记录都要去执行一次子查询; 而对于表达式, 查询优化器认为它具有确定性, 所以对于每个 aa, 计算一次就行了
如果把楼主的第一个查询中的 IN, 也改成表达式, 则可以看到会使用与查询2一样的执行计划, 结果也也查询2一样, 有固定的记录数
SELECT * FROM #tb a WHERE bb IN ( SELECT ( SELECT TOP 1 bb FROM #tb WHERE aa=a.aa ORDER BY NEWID() ) )
- 已标记为答案 Amy PengMicrosoft employee, Moderator 2013年4月22日 8:28