关于索引假脱机的问题

Question

有人说是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的执行计划

---

给我写信： QQ我：

Answer 1

有人给出的解释是：

对于相同的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（）也没有什么关系，不知道大家的看法？

---

给我写信： QQ我：

Answer 2

You want to compare performance of 2 queries? Should run them in same batch then., seperate plan doesn't tell much because each costs 100%. 

Answer 3

没看出什么逻辑“错误”或“假像”呢

NewID()随意取值，就是说每次的子查询结果不同。。。导致结果每次不一样，这是正确的解析逻辑

---

Try SQL Server 2008 QQ:315054403 [e-mail]

Answer 4

我的问题：多次运行查询语句，运行后看到=与in的区别，用in的时候得出的结果行数会变化，可能是一行，可能是多行，但是用=的时候始终产生得到三行结果

为什麽？

---

给我写信： QQ我：

Answer 5

这个应该是查询优化器对于表达式和子查询判定上的一个差异

对于 = 运算符， 其语法定义是： 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()
		)
     )

Answer 6

果然跟SQL2是一样的，但是这个地方还不是很明白

对于相同的aa， WHERE aa=a.aa 这个条件是一样的

那么两个查询都是一样的啊，跟索引假脱机没有什么关系吧

 SELECT TOP 1 bb FROM #tb 

---

给我写信： QQ我：