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)

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行數據），兩者在邏輯上實際上是不一致的。因為重覆數據的緣故。撇開這個不談，光從性能上來考察兩種，它們幾乎是一模一樣。沒有優劣之分。

如果有人對上面的重覆數據不明白的話，下麵做個簡單的例子演示給大家看看。如下所示，截圖中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'

其實下麵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;

那麼我們再來看另外一個例子，測試一下兩者的性能差別。如下所示

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要好。

那麼我們再來看一個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<