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)
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 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)
增加对应的索引后,这个性能差距更更明显。 如下截图所示
USE [AdventureWorks2014] GO CREATE NONCLUSTERED INDEX [IX_C_N1] ON [dbo].[C] ([P_ID]) INCLUDE ([C_ID],[COLN]) GO
在生产环境遇到一个案例, 两个视图使用INNER JOIN 与 IN 两种写法,在性能上差距很大。 使用子查询IN的性能比使用INNER JOIN的性能要好很多。如下截图所示。因为视图里面涉及多表。这样肯定导致执行计划非常复杂,导致SQL用INNER JOIN 在性能上没有用子查询IN要快
其实一部分情况下,INNER JOIN 与 子查询IN都是等价的。因为SQL Server优化器已经足够聪明,能够进行一些内部转换,生成等价的计划。但是在某一些特殊场景下,各有优劣。不能武断的就说INNER JOIN在性能上要比子查询IN要好。一定要结合上下文环境具体来谈性能优劣。否则没有多大意义。另外,子查询可以分为相关子查询和无关子查询,对于无关子查询来说,Not In子句比较常见,但Not In潜在会带来下面两种问题,结果不正确和性能问题,具体可以参考 在SQL Server中为什么不建议使用Not In子查询
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:- 微服务测试之性能测试
- Go 单元测试和性能测试
- 性能测试vs压力测试vs负载测试
- SpringBoot | 第十三章:测试相关(单元测试、性能测试)
- Golang 性能测试 (2) 性能分析
- 随行付微服务测试之性能测试 原 荐
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
深入浅出SQL(中文版)
贝里 编 / O‘Reilly Taiwan公司 / 东南大学 / 2009-6 / 98.00元
你将从《深入浅出SQL(中文版)》学到什么?在如今的世界,数据就是力量,但是成功的真正秘诀却是管理你的数据的力量。《深入浅出SQL(中文版)》带你进入SQL语言的心脏地带,从使用INSERT和SELECT这些基本的查询语法到使用子查询(subquery)、连接(join)和事务(transaction)这样的核心技术来操作数据库。到读完《深入浅出SQL(中文版)》之时,你将不仅能够理解高效数据库设......一起来看看 《深入浅出SQL(中文版)》 这本书的介绍吧!