none
关于索引假脱机的问题 RRS feed

  • 问题

  • 有人说是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我:点击这里给我发消息

    2013年4月14日 15:22

答案

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

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

    2013年4月15日 7:35

全部回复

  • 有人给出的解释是:

    对于相同的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我:点击这里给我发消息

    2013年4月14日 15:24
  • 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%. 
    2013年4月14日 16:04
  • 没看出什么逻辑“错误”或“假像”呢

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


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2013年4月15日 1:13
  • 我的问题:多次运行查询语句,运行后看到=与in的区别,用in的时候得出的结果行数会变化,可能是一行,可能是多行,但是用=的时候始终产生得到三行结果

    为什麽?


    给我写信: QQ我:点击这里给我发消息


    2013年4月15日 4:43
  • 这个应该是查询优化器对于表达式和子查询判定上的一个差异

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

    2013年4月15日 7:35
  • 果然跟SQL2是一样的,但是这个地方还不是很明白

    对于相同的aa WHERE aa=a.aa 这个条件是一样的
    
    那么两个查询都是一样的啊,跟索引假脱机没有什么关系吧

     SELECT TOP 1 bb FROM #tb 


    给我写信: QQ我:点击这里给我发消息

    2013年4月15日 16:40