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

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

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

下面例子以AdventureWorks2014为测试场景，测试表为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)      

http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223324900-1544149230.png http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223326463-607321125.png

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      

如下所示，两种写法的SQL的实际执行计划是几乎一致。而且对比IO开销也是一致。cpu time 与elapsed time 有所差别，这个是因为两者返回的数据有所差别的缘故（SQL 1 返回 31465行数据， SQL 2返回 121317行数据），两者在逻辑上实际上是不一致的。因为重复数据的缘故。撇开这个不谈，光从性能上来考察两种，它们几乎是一模一样。没有优劣之分。

http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223329588-1494361206.png http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223331494-645480197.png

如果有人对上面的重复数据不明白的话，下面做个简单的例子演示给大家看看。如下所示，截图中INNER 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'      

http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223332494-1646320101.png

其实下面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;      

http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223333557-878140149.png

那么我们再来看另外一个例子，测试一下两者的性能差别。如下所示

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 );      

INNER JOIN与子查询IN的实际执行计划对比的百分比为66% VS 34% ， 子查询IN的性能还比 INNER JOIN的性能要好一些. IO几乎无差别，cpu time 与elapsed time的对比情况来看，子查询IN的性能确实要好一些。

这个是因为子查询IN在这个上下文环境中，它使用右半连接（Right Semi Join）方式的Hash Match，即一个表中返回的行与另一个表中数据行进行不完全联接查询（查找到匹配的数据行就返回，不再继续查找）。那么可以肯定的是，在这个场景（上下文）中，子查询IN这种方式的SQL的性能比INNER JOIN 这种写法的SQL要好。

http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223337713-832128294.png http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223339697-1232001912.png

那么我们再来看一个INNER JOIN性能比子查询（IN)要好的案例。如下所示，我们先构造测试数据。

CREATE TABLE P      

(      

    P_ID    INT IDENTITY(1,1),      

    OTHERCOL        CHAR(500),      

    CONSTRAINT PK_P PRIMARY KEY(P_ID)      

)      

GO      

BEGIN TRAN      

DECLARE @I INT = 1      

WHILE @I<=10000      

BEGIN      

    INSERT INTO P VALUES (NEWID())      

    SET @I = @I+1      

    IF (@I%500)=0      

    BEGIN      

        IF @@TRANCOUNT>0      

        BEGIN      

            COMMIT      

            BEGIN TRAN      

        END      

    END      

END      

IF @@TRANCOUNT>0      

BEGIN      

    COMMIT      

END      

GO      

CREATE TABLE C       

(      

    C_ID  INT IDENTITY(1,1) ,      

    P_ID   INT  FOREIGN KEY REFERENCES P(P_ID),      

    COLN  CHAR(500),      

    CONSTRAINT PK_C  PRIMARY KEY (C_ID)       

)      

SET NOCOUNT ON;      

DECLARE @I INT = 1      

WHILE @I<=1000000      

BEGIN      

    INSERT INTO C VALUES ( CAST(RAND()*10 AS INT)+1,  NEWID())      

    SET @I = @I+1      

END      

GO      

构造完测试数据后，我们对比下两者的性能差异

SET STATISTICS IO ON;      

SET STATISTICS TIME ON;      

SELECT C.* FROM dbo.C C      

INNER JOIN dbo.P  P ON C.P_ID = P.P_ID      

WHERE P.P_ID=8      

SELECT * FROM dbo.C      

WHERE P_ID IN (SELECT P_ID FROM dbo.P WHERE P_ID=8)      

http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223340853-287492109.png http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223342666-372089993.png

增加对应的索引后，这个性能差距更更明显。 如下截图所示

USE [AdventureWorks2014]      

GO      

CREATE NONCLUSTERED INDEX [IX_C_N1]      

ON [dbo].[C] ([P_ID])      

INCLUDE ([C_ID],[COLN])      

GO      

http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223343666-445180051.png

在生产环境遇到一个案例， 两个视图使用INNER JOIN 与 IN 两种写法，在性能上差距很大。 使用子查询IN的性能比使用INNER JOIN的性能要好很多。如下截图所示。因为视图里面涉及多表。这样肯定导致执行计划非常复杂，导致SQL用INNER JOIN 的写法在性能上没有用子查询IN的写法要快

http://images2015.cnblogs.com/blog/73542/201705/73542-20170517223345135-496248190.png

其实一部分情况下，INNER JOIN 与 子查询IN都是等价的。因为SQL Server优化器已经足够聪明，能够进行一些内部转换，生成等价的计划。但是在某一些特殊场景下，各有优劣。不能武断的就说INNER JOIN在性能上要比子查询IN要好。一定要结合上下文环境具体来谈性能优劣。否则没有多大意义。另外，子查询可以分为相关子查询和无关子查询，对于无关子查询来说，Not In子句比较常见，但Not In潜在会带来两种问题，结果不正确和性能问题，具体可以参考

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