在SQL Server中为什么不建议使用Not In子查询

原文:在SQL Server中为什么不建议使用Not In子查询

    在SQL Server中,子查询可以分为相关子查询和无关子查询,对于无关子查询来说,Not In子句比较常见,但Not In潜在会带来下面两种问题:

  • 结果不准确
  • 查询性能低下

 

    下面我们来看一下为什么尽量不使用Not In子句。

 

结果不准确问题

    在SQL Server中,Null值并不是一个值,而是表示特定含义,其所表示的含义是“Unknow”,可以理解为未定义或者未知,因此任何与Null值进行比对的二元操作符结果一定为Null,包括Null值本身。而在SQL Server中,Null值的含义转换为Bool类型的结果为False。让我们来看一个简单的例子,如图1所示。

图1.Null值与任何值进行对比结果都为Null

    SQL Server提供了“IS”操作符与Null值做对比,用于衡量某个值是否为Null。

 

    那么Not In 的问题在哪呢,如图2所示。

   

图2.Not In产生不准确的值

 

     在图2中,条件3不属于Not In后面列表的任意一个,该查询却不返回任何值,与预期的结果不同,那么具体原因就是Not In子句对于Null值的处理,在SQL Server中,图2中所示的Not In子句其实可以等价转换为如图3所示的查询。

图3.对于Not In子句来说,可以进行等价转换

 

    在图3中可以看到Not In可以转换为条件对于每个值进行不等比对,并用逻辑与连接起来,而前面提到过Null值与任意其他值做比较时,结果永远为Null,在Where条件中也就是False,因此3<>null就会导致不返回任何行,导致Not In子句产生的结果在意料之外。

    因此,Not In子句如果来自于某个表或者列表很长,其中大量值中即使存在一个Null值,也会导致最终结果不会返回任何数据。

解决办法?

    解决办法就是不使用Not In,而使用Not Exists作为替代。Exists的操作符不会返回Null,只会根据子查询中的每一行决定返回True或者False,当遇到Null值时,只会返回False,而不会由某个Null值导致整个子查询表达式为Null。对于图2中所示的查询,我们可以改写为子查询,如图4所示。

图4.Not Exists可以正确返回结果

 

Not In导致的查询性能低下

    前面我们可以看出,Not In的主要问题是由于对Null值的处理问题所导致,那么对Null值的处理究竟为什么会导致性能问题?让我们来看图5的示例。图5中,我们使用了Adventurework示例数据库,并为了演示目的将SalesOrderDetail表的ProductId的定义由Not Null改为Null,此时我们进行一个简单的Not In查询。如图5所示。

图5.Not In的执行计划

 

    在图5中,我们看到一个Row Count Spool操作符,该操作符用于确认ProductId列中是否有Null值(过程是对比总行数和非Null行数,不想等则为有Null值,虽然我们知道该列中没有Null值,但由于列定义是允许Null的,因此SQL Server必须进行额外的确认),而该操作符占用了接近一半的查询成本。因此我们对比Not Exists,如图6所示。

图6.Not In Vs Not Exists

 

    由图6可以看出,Not In的执行成本几乎是Not Exists的3倍,仅仅是由于SQL Server需要确认允许Null列中是否存在Null。根据图3中Not In的等价形式,我们完全可以将Not In转换为等价的Not Exist形式,如图7所示。

图7.Not In转换为Not Exists

    我们来对比图7和其等价Not In查询的成本,如图8所示。

图8.成本上完全等价

 

    因此我们可以看到Not In需要额外的步骤处理Null值,上述情况是仅仅在SalesOrderDetail表中的ProductId列定义为允许Null,如果我们将SalesOrderHeader的SalesOrderID列也定义为允许Null时,会发现SQL Server还需要额外的成本确认该列上是否有Null值。如图9所示。

图9.SQL Server通过加入Left Anti Semi Join操作符解决列允许Null的问题

 

此时Not In对应的等价Not Exist形式变为如代码清单1所示。

SELECT  *
FROM    Sales.SalesOrderHeader a
WHERE   NOT EXISTS ( SELECT *
                     FROM   Sales.SalesOrderDetail b
                     WHERE  a.SalesOrderID = b.ProductID )
        AND NOT EXISTS ( ( SELECT   *
                           FROM     Sales.SalesOrderDetail b
                           WHERE    b.ProductID IS NULL
                         ) )
        AND NOT EXISTS ( SELECT 1
                         FROM   ( SELECT    *
                                  FROM      Sales.SalesOrderHeader
                                ) AS c
                         WHERE  c.SalesOrderID IS NULL )

代码清单1.当连接列两列定义都允许Null时,Not In等价的Not Exists形式

 

    此时我们简单对比Not In和Not Exists的IO情况,如图10所示。

图10.Not In吃掉很高的IO

 

小结

    本文阐述了Not In 的实现原理以及所带来的数据不一致和性能问题,在写查询时,尽量避免使用Not In,而转换为本文提供的Not Exists等价形式,将会减少很多麻烦。

时间: 2016-03-18
Tags: sql, 性能, server

在SQL Server中为什么不建议使用Not In子查询的相关文章

SQL Server中INNER JOIN与子查询IN的性能测试

这个月碰到几个人问我关于"SQL SERVER中INNER JOIN 与 IN两种写法的性能孰优孰劣?"这个问题.其实这个概括起来就是SQL Server中INNER JOIN与子查询孰优孰劣(IN是子查询的实现方式之一,本篇还是只对比INNER JOIN与子查询IN的性能,如果展开INNER JOIN与子查询性能对比,范围太大了,没法一一详述).下面这篇文章,我们就INNER JOIN与子查询IN这两种写法孰优孰劣,在不同场景下进行一下测试对比一下,希望能解答你心中的疑惑.   下面

sql server-SQL SERVER NOT IN的用法的问题,子查询

问题描述 SQL SERVER NOT IN的用法的问题,子查询 SQL SERVER怎么取得stu_info中在teacher中不属于任何老师的学生信息的表记录呢? 解决方案 select * from stu_info where 老师 not in (select id from teacher) 大概就是这样,你根据你的字段修改下 解决方案二: 两个表的具体列都有哪些? 解决方案三: select * from stu_info s where not exist s.teacherid

SQL Server中的“最大并行度”的配置建议

SQL Server中的最大并行度(max degree of parallelism)如何设置呢? 设置max degree of parallelism有什么好的建议和指导方针呢?在微软官方文档Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server有这方面的建议和指导方针 Use the following guidelines whe

在SQL Server中保存和输出图片

server 介绍 有时候我们需要保存一些binary data进数据库.SQL Server提供一个叫做image的特殊数据类型供我们保存binary data.Binary data可以是图片.文档等.在这篇文章中我们将看到如何在SQL Server中保存和输出图片. 建表 为了试验这个例子你需要一个含有数据的table(你可以在现在的库中创建它,也可以创建一个新的数据库),下面是它的结构: Column Name Datatype Purpose ID Integer identity c

浅谈SQL Server中的高可用性(2) 文件与文件组

在谈到SQL Server的高可用性之前,我们首先要谈一谈单实例的高可用性.在单实例的高可用性中,不可忽略的就是文件和文件组的高可用性.SQL Server允许在某些文件损坏或离线的情况下,允许数据库依然保持部分在线,从而保证了高可用性. 文件和文件组 有关文件和文件组的基本概念,有很多文章已经阐述过了.这里我只是提一下,文件组作为SQL Server访问文件的一个抽象层而存在.因此SQL Server上所做的操作不是直接针对文件,而是针对文件组. 使用多个文件组和文件不仅仅是为了分散IO和提高

sql server中Select count(*)和Count(1)的区别和执行方式

    在SQL Server中Count(*)或者Count(1)或者Count([列])或许是最常用的聚合函数.很多人其实对这三者之间是区分不清的.本文会阐述这三者的作用,关系以及背后的原理.     往常我经常会看到一些所谓的优化建议不使用Count(* )而是使用Count(1),从而可以提升性能,给出的理由是Count( *)会带来全表扫描.而实际上如何写Count并没有区别.     Count(1)和Count(*)实际上的意思是,评估Count()中的表达式是否为NULL,如果为

SQL Server 中的事务(含义,属性,管理)

含义: 事务要有非常明确的开始和结束点,SQL Server 中的每一条数据操作语句,例如SELECT.INSERT.UPDATE和DELETE都是隐式事务的一部分.即使只有一条语句,系统也会把这条语句当做一个事务,要么执行所有的语句,要么什么都不执行. 事务开始之后,事务所有的操作都会写到事务日志中,写到日志中的事务,一般有两种:一是针对数据的操作,例如插入.修改和删除,这些操作的对象是大量的数据;另一种是针对任务的操作,例如创建索引.当取消这些事务操作时,系统自动执行这些操作的反操作,保证系

SQL Server 中的事务

事务的含义: 事务要有非常明确的开始和结束点,SQL Server 中的每一条数据操作语句,例如SELECT.INSERT.UPDATE和DELETE都是隐式事务的一部分.即使只有一条语句,系统也会把这条语句当做一个事务,要么执行所有的语句,要么什么都不执行. 事务开始之后,事务所有的操作都会写到事务日志中,写到日志中的事务,一般有两种:一是针对数据的操作,例如插入.修改和删除,这些操作的对象是大量的数据;另一种是针对任务的操作,例如创建索引.当取消这些事务操作时,系统自动执行这些操作的反操作,

Sql Server中的表访问方式Table Scan, Index Scan, Index Seek

  Sql Server中的表访问方式Table Scan, Index Scan, Index Seek 0.参考文献 oracle表访问方式 Index Seek和Index Scan的区别以及适用情况 1.oracle中的表访问方式 在oracle中有表访问方式的说法,访问表中的数据主要通过三种方式进行访问: 全表扫描(full table scan),直接访问数据页,查找满足条件的数据 通过rowid扫描(table access by rowid),如果知道数据的rowid,那么直接通