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) 性能分析
- 随行付微服务测试之性能测试 原 荐
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
The Book of CSS3
Peter Gasston / No Starch Press / 2011-5-13 / USD 34.95
CSS3 is the technology behind most of the eye-catching visuals on the Web today, but the official documentation can be dry and hard to follow. Luckily, The Book of CSS3 distills the heady technical la......一起来看看 《The Book of CSS3》 这本书的介绍吧!