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

栏目: 数据库 · SQL Server · 发布时间: 6年前

内容简介: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)

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

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

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行数据),两者在逻辑上实际上是不一致的。因为重复数据的缘故。撇开这个不谈,光从性能上来考察两种,它们几乎是一模一样。没有优劣之分。

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

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

如果有人对上面的重复数据不明白的话,下面做个简单的例子演示给大家看看。如下所示,截图中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 Server中INNER JOIN与子查询IN的性能测试

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

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

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

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

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

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

那么我们再来看一个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)

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

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

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

 
USE [AdventureWorks2014]
GO
CREATE NONCLUSTERED INDEX [IX_C_N1]
ON [dbo].[C] ([P_ID])
INCLUDE ([C_ID],[COLN])
GO

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

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

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

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


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

深入浅出SQL(中文版)

深入浅出SQL(中文版)

贝里 编 / O‘Reilly Taiwan公司 / 东南大学 / 2009-6 / 98.00元

你将从《深入浅出SQL(中文版)》学到什么?在如今的世界,数据就是力量,但是成功的真正秘诀却是管理你的数据的力量。《深入浅出SQL(中文版)》带你进入SQL语言的心脏地带,从使用INSERT和SELECT这些基本的查询语法到使用子查询(subquery)、连接(join)和事务(transaction)这样的核心技术来操作数据库。到读完《深入浅出SQL(中文版)》之时,你将不仅能够理解高效数据库设......一起来看看 《深入浅出SQL(中文版)》 这本书的介绍吧!

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

MD5 加密
MD5 加密

MD5 加密工具

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具