Post Jobs

【澳门新葡亰平台游戏】JOIN与子查询IN的性能测试,Server中为什么不建议使用Not

澳门新葡亰平台游戏 16

前段时间境遇多少人问作者有关“SQL SE中华VVE汉兰达中INNER JOIN 与
IN二种写法的品质孰优孰劣?”那一个主题素材。其实这么些包涵起来便是SQL
Server中INNE福特ExplorerJOIN与子查询孰优孰劣(IN是子查询的达成情势之壹,本篇仍然只比较INNE途乐JOIN与子查询IN的属性,若是进展INNE奥迪Q5JOIN与子查询质量相比,范围太大了,无法一1详述)。上面那篇文章,大家就INNE奥迪Q三JOIN与子查询IN那三种写法孰优孰劣,在差别处境下开始展览一下测试对照一下,希望能解答你内心的思疑。

转自新浪宋沄剑  英文名:CareySon :

 

    在SQL
Server中,子查询能够分为相关子查询和非亲非故子查询,对于非亲非故子查询来讲,Not
In子句相比较宽泛,但Not In潜在会带来上边三种难点:

澳门新葡亰平台游戏,上面例子以AdventureWorks201四为测试场景,测试表为Sales.SalesOrderHeader与Sales.SalesOrderDetail。
如下所示:

  • 结果不正确
  • 询问质量低下
 

DBCC FREEPROCCACHE;

GO

DBCC DROPCLEANBUFFERS;

GO

 

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

 

SELECT  h.* FROM 

Sales.SalesOrderHeader h

WHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail)

 

澳门新葡亰平台游戏 1

    上面大家来看一下为啥尽量不行使Not In子句。

 

 

澳门新葡亰平台游戏 2

结果不标准难点

    在SQL
Server中,Null值并不是2个值,而是表示一定含义,其所代表的意思是“Unknow”,可以清楚为未定义可能隔靴搔痒,因而任何与Null值进行比对的二元操作符结果必然为Null,包蕴Null值本人。而在SQL
Server中,Null值的含义调换为Bool类型的结果为False。让大家来看叁个简短的事例,如图一所示。

澳门新葡亰平台游戏 3

图一.Null值与其余值实行自己检查自纠结果都为Null

    SQL Server提供了“IS”操作符与Null值做比较,用于衡量有个别值是还是不是为Null。

 

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

澳门新葡亰平台游戏 4   

图贰.Not In发生不确切的值

 

     在图第22中学,条件三不属于Not
In前面列表的自由三个,该查询却不回来任何值,与预期的结果差别,那么具体原因正是Not
In子句对于Null值的管理,在SQL Server中,图第22中学所示的Not
In子句实在能够等价调换为如图叁所示的询问。

澳门新葡亰平台游戏 5

图三.对于Not In子句来讲,能够张开等价转变

 

    在图三中得以见见Not
In能够转变为规范对于各个值实行差异期相比较对,并用逻辑与连接起来,而近期提到过Null值与自由其余值做相比时,结果恒久为Null,在Where条件中也正是False,因而3<>null就能够导致不回来任何行,导致Not
In子句发生的结果在预期之外。

    由此,Not
In子句假诺来自于某些表或然列表不长,个中大批量值中即便存在1个Null值,也会招致最后结出不会回去任何数据。

 

消除办法?

    化解办法便是不应用Not In,而利用Not
Exists作为代表。Exists的操作符不会回去Null,只会依据子查询中的每一行决定回到True或许False,当境遇Null值时,只会回来False,而不会由某些Null值导致整个子查询表明式为Null。对于图2中所示的询问,大家得以改写为子查询,如图四所示。

澳门新葡亰平台游戏 6

图肆.Not Exists能够正确再次回到结果

 

DBCC FREEPROCCACHE;

GO

DBCC DROPCLEANBUFFERS;

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

 

SELECT h.* FROM Sales.SalesOrderHeader h

INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID

Not In导致的查询品质低下

    前边大家得以看来,Not
In的首要难点是由于对Null值的拍卖难题所变成,那么对Null值的管理终究为何会促成品质难点?让大家来看图伍的演示。图5中,我们接纳了Adventurework示例数据库,并为了演示指标将SalesOrderDetail表的ProductId的定义由Not
Null改为Null,此时大家开始展览三个简短的Not In查询。如图5所示。

澳门新葡亰平台游戏 7

图伍.Not In的进行布署

 

    在图5中,大家看看3个Row Count
Spool操作符,该操作符用于确认ProductId列中是还是不是有Null值(进程是看待总行数和非Null行数,不想等则为有Null值,纵然大家明白该列中从未Null值,但出于列定义是同意Null的,因而SQL
Server必须开始展览额外的确认),而该操作符占用了类似一半的询问资金。由此我们相比较Not
Exists,如图陆所示。

澳门新葡亰平台游戏 8

图6.Not In Vs Not Exists

 

    由图陆能够看来,Not In的执行费用大概是Not Exists的三倍,仅仅是出于SQL
Server需求分明允许Null列中是还是不是存在Null。依照图三中Not
In的相当于方式,大家完全能够将Not In转换为等价的Not Exist方式,如图柒所示。

澳门新葡亰平台游戏 9

图7.Not In转换为Not Exists

    我们来相比图柒和其等价Not In查询的老本,如图八所示。

澳门新葡亰平台游戏 10

图8.花费上完全等价

 

    因而我们得以见见Not
In须要万分的手续管理Null值,上述意况是只有在SalesOrderDetail表中的ProductId列定义为允许Null,即使大家将SalesOrderHeader的SalesOrderID列也定义为允许Null时,会发觉SQL
Server还供给非凡的老本确认该列上是或不是有Null值。如图九所示。

澳门新葡亰平台游戏 11

图九.SQL Server通过投入Left Anti Semi Join操作符化解列允许Null的主题素材

 

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

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所示。

澳门新葡亰平台游戏 12

图十.Not In吃掉非常高的IO

本文解说了Not In
的完毕原理以及所带来的多少不一样等和本性难题,在写查询时,尽量幸免使用Not
In,而转变为本文提供的Not Exists等价情势,将会减弱过多烦劳。

原文:

 

正如所示,两种写法的SQL的实际上实践布署是差不多同样。而且相比较IO成本也是一律。cpu
time 与elapsed time
有所出入,那几个是因为两者重回的数额具备差别的由来(SQL 壹 返回31四6五行数据, SQL 二重返121317行数据),两者在逻辑上实际是不等同的。因为重新数据的原因。撇开那一个不谈,光从性质上来考查二种,它们差不离是一模二样。未有好坏之分。

 

澳门新葡亰平台游戏 13

 

澳门新葡亰平台游戏 14

 

假使有人对地方的双重数据不亮堂的话,上面做个轻巧的事例演示给大家看看。如下所示,截图中INNE奥德赛JOIN就能够有重新数据。

 

CREATE TABLE P

(

    PID    INT ,

    Pname  VARCHAR(24)

)

 

INSERT INTO dbo.P

SELECT 1, 'P1' UNION ALL

SELECT 2, 'P2' UNION ALL

SELECT 3, 'P3'

 

 

CREATE TABLE dbo.C

(

    CID       INT ,

    PID       INT ,

    Cname  VARCHAR(24)

)

 

INSERT INTO dbo.c

SELECT 1, 1, 'C1' UNION ALL

SELECT 2, 1, 'C2' UNION ALL

SELECT 3, 2, 'C3' UNION ALL

SELECT 3, 3, 'C4'

澳门新葡亰平台游戏 15

 

实际下边SQL在逻辑上才是相等的,它们的其实奉行布置与IO是同样的。未有好坏之分。

 

SELECT  h.* FROM 

Sales.SalesOrderHeader h

WHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail);

 

 

SELECT DISTINCT h.* FROM Sales.SalesOrderHeader h

INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID;

 

澳门新葡亰平台游戏 16

 

那么我们再来看其它3个例子,测试一下两岸的习性差距。如下所示

 

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

 

SELECT  C.*

FROM    Sales.Customer C

        INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID;

 

 

SELECT  C.*

FROM    Sales.Customer C

WHERE  C.PersonID IN ( SELECT Person.Person.BusinessEntityID

                                     FROM   Person.Person );

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图