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子查詢