SQL Server-聚焦INNER JOIN AND IN性能分析(十四)

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 原文:SQL Server-聚焦INNER JOIN AND IN性能分析(十四)前言 本节我们来讲讲联接综合知识,我们在大多教程或理论书上都在讲用哪好,哪个性能不如哪个的性能,但是真正讲到问题的实质却不是太多,所以才有了本系列每一篇的篇幅不是太多,但是肯定是我用心去查找许多资料而写出,简短的内容,深入的理解,Always to review the basics。
原文: SQL Server-聚焦INNER JOIN AND IN性能分析(十四)

前言

本节我们来讲讲联接综合知识,我们在大多教程或理论书上都在讲用哪好,哪个性能不如哪个的性能,但是真正讲到问题的实质却不是太多,所以才有了本系列每一篇的篇幅不是太多,但是肯定是我用心去查找许多资料而写出,简短的内容,深入的理解,Always to review the basics。

初次探讨INNER JOIN和IN性能分析

接下来我们看第一篇联接综合知识讲解INNER JOIN和IN的比较分析,我们通过创建表来看INNER JOIN。

创建测试表1

CREATE TABLE Table1  (
 id INT IDENTITY PRIMARY KEY,
 SomeColumn CHAR(4),
 Filler CHAR(100)
)

插入测试数据

Insert into Table1(SomeColumn) Values (1),(2),(3),(4),(5)

创建测试表2并插入数据

USE TSQL2012
GO

CREATE TABLE Table2  (IntCol int)
Insert into Table2 (IntCol) Values (1),(2),(2),(3),(4),(5),(5)

接下来我们对测试表1和测试表2中的SomeColumn和IntCol进行JOIN

USE TSQL2012
GO

SELECT *
FROM Table1 b 
 INNER JOIN Table2 s ON b.SomeColumn = s.IntCol

此时我们看到两个测试表中都返回7行数据,因为在测试表2中有重复的数据都匹配上所有测试表1返回所有数据。此时我们再来看看IN的查询

USE TSQL2012
GO

SELECT *
 FROM Table1
 WHERE SomeColumn IN (Select IntCol FROM Table2)

此时则返回5条数据,从这里我们知道INNER JOIN和IN还是有很大的区别,但是若在测试表2中没有重复的数据,同时在测试表2中没有需要的列,此时则查询出的数据和测试表1是一样的,此时二者在性能上有什么区别呢?接下来我们在创建大量数据的前提下来进行测试看看。

创建两个测试表

CREATE TABLE BigTable (
id INT IDENTITY PRIMARY KEY,
SomeColumn UNIQUEIDENTIFIER NOT NULL,
Filler CHAR(100)
)
 
CREATE TABLE SmallerTable (
id INT IDENTITY PRIMARY KEY,
LookupColumn UNIQUEIDENTIFIER NOT NULL,
SomeArbDate DATETIME DEFAULT GETDATE()
)

在BigTable表SomeColumn列中插入100万条数据

INSERT INTO BigTable (SomeColumn)
SELECT NEWID()
FROM dbo.Nums
WHERE n<1000001

取出BigTable中的25%数据插入到SmallerTable表LookupColumn列中

USE TSQL2012
GO

INSERT INTO SmallerTable (LookupColumn)
SELECT DISTINCT SomeColumn
FROM BigTable TABLESAMPLE (25 PERCENT)

这里我们分三种情况来测试。

(1)未建立索引比较INNER和JOIN

SELECT BigTable.ID, SomeColumn
FROM BigTable
WHERE SomeColumn IN (SELECT LookupColumn FROM dbo.SmallerTable)
 
SELECT BigTable.ID, SomeColumn
FROM BigTable
INNER JOIN SmallerTable ON dbo.BigTable.SomeColumn = dbo.SmallerTable.LookupColumn

 

从上看出此时在无论是查询开销还是IO上均没有什么差异,下面我们再来看看建立索引的情况

(2)建立非唯一非聚集索引比较INNER JOIN和IN

CREATE INDEX idx_BigTable_SomeColumn ON BigTable (SomeColumn)
CREATE INDEX idx_SmallerTable_LookupColumn ON SmallerTable (LookupColumn)

此时我们发现在建立非唯一非聚集索引的情况二者在查询开销上开始有了比较大的差异,INNER JOIN的开销是IN的两倍而IO几乎是等同的。

(3)建立唯一非聚集索引比较INNER JOIN和IN

CREATE UNIQUE INDEX idx_BigTable_SomeColumn ON BigTable (SomeColumn)
CREATE UNIQUE INDEX idx_SmallerTable_LookupColumn ON SmallerTable (LookupColumn)

此时为何索引变为唯一聚集索引二者性能开销却一致了呢?有点纳闷,同时到这里为止是不是说明IN的查询性能比JOIN的性能更好呢,完全颠覆我们的想法,在本文前言我们讨论过在教程中都会给出大部分JOIN比EXISTS性能好,而EXISTS比IN性能好,凡是还是动手实践,亲自验证才是王道,我们只能得出一般性结论:一般来说,JOIN比EXISTS性能好,而EXISTS比IN性能好仅此而已。这都是一般性情况,本系列需要讲述的是什么时候应该用EXISTS,什么时候应该用JOIN,还有什么时候应该用IN,后续内容会陆续讨论这些内容。好了,有点跑题了,上述我们通过100万条数据得出IN的性能接近是INNER JOIN性能的两倍,完全出乎你我的意料,带着这个疑问,接下来我们进一步进行探讨。

进一步探讨INNER JOIN和IN性能分析 

上述在SmallerTable表从BigTable表中取出的25%的数据都是唯一的,接下来我们将这25%数据的一部分设置为重复的。我们随便从BigTable表中取出SomeColumn这列的数据,然后将SmallerTable表中的LookupColumn这列的数据设置重复的10000条,如下

USE TSQL2012
GO

UPDATE dbo.SmallerTable SET LookupColumn = '0067cb6c-64e1-46cc-b7f2-334a7dd812ff'
WHERE id>=1 AND id<=10000

此时我们查询包括重复的这10000条

USE TSQL2012
GO

SELECT BigTable.ID, SomeColumn
FROM BigTable
WHERE SomeColumn IN (SELECT LookupColumn FROM dbo.SmallerTable)
 
SELECT BigTable.ID, SomeColumn
FROM BigTable
INNER JOIN SmallerTable ON dbo.BigTable.SomeColumn = dbo.SmallerTable.LookupColumn

此时结果还是IN性能比INNER JOIN性能要接近一半,接下来我们在查询SmallerTable表时将重复的LookupColumn列数据去除,此时我们查询变为如下:

USE TSQL2012
GO

SELECT BigTable.ID, SomeColumn
FROM BigTable
WHERE SomeColumn IN (SELECT LookupColumn FROM dbo.SmallerTable)
 
SELECT BigTable.ID, SomeColumn
FROM BigTable
INNER JOIN (SELECT DISTINCT LookupColumn FROM dbo.SmallerTable) AS s
ON s.LookupColumn = dbo.BigTable.SomeColumn

终于查询开销和上述不一样了,此时二者查询性能开销是一致的,相信到了这里我们应该很清楚了。通过上述大量篇幅的贴图和比较我们可以得出INNER JOIN和IN的性能开销使用场景,当我们在初步探讨INNER JOIN和IN的性能分析时,当建立非唯一聚集索引时IN性能接近是INNER JOIN的两倍,而当建立唯一聚集索引时,此时性能开销一致,不免有点纳闷,当我们继续向下探讨时终于明白了这个原因,至此我们最终得出INNER JOIN和IN的性能开销结论。

INNER JOIN和IN性能开销结论:当INNER JOIN表中列数据是唯一的,此时INNER JOIN和IN的性能开销是相同的,当INNER JOIN表中列数据是重复的,此时IN性能要INNER JOIN要好。

总结

本节我们详细叙述了INNER JOIN和IN的性能分析,最终得出一致性结论,下节我们开始讨论NOT EXISTS和NOT IN性能分析,简短的内容,深入的理解,我们下节再会,good night。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
1月前
|
SQL
SQL JOIN
【11月更文挑战第06天】
44 4
|
2月前
|
SQL 关系型数据库 MySQL
图解 SQL 里的各种 JOIN
用文氏图表示 SQL 里的各种 JOIN,一下子就理解了。
43 2
|
2月前
|
SQL 分布式计算 Java
Hadoop-11-MapReduce JOIN 操作的Java实现 Driver Mapper Reducer具体实现逻辑 模拟SQL进行联表操作
Hadoop-11-MapReduce JOIN 操作的Java实现 Driver Mapper Reducer具体实现逻辑 模拟SQL进行联表操作
38 3
|
2月前
|
SQL 监控 数据库
SQL语句性能分析技巧与方法
在数据库管理中,分析SQL语句的性能是优化数据库查询、提升系统响应速度的重要步骤
|
2月前
|
SQL 监控 关系型数据库
SQL语句性能分析:实战技巧与详细方法
在数据库管理中,分析SQL语句的性能是优化数据库查询、提升系统响应速度的重要步骤
|
3月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
5月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
129 13
|
4月前
|
SQL 存储 监控
|
4月前
|
Java 网络架构 数据格式
Struts 2 携手 RESTful:颠覆传统,重塑Web服务新纪元的史诗级组合!
【8月更文挑战第31天】《Struts 2 与 RESTful 设计:构建现代 Web 服务》介绍如何结合 Struts 2 框架与 RESTful 设计理念,构建高效、可扩展的 Web 服务。Struts 2 的 REST 插件提供简洁的 API 和约定,使开发者能快速创建符合 REST 规范的服务接口。通过在 `struts.xml` 中配置 `&lt;rest&gt;` 命名空间并使用注解如 `@Action`、`@GET` 等,可轻松定义服务路径及 HTTP 方法。
65 0
|
4月前
|
SQL 存储 数据挖掘
"SQL JOIN大揭秘:解锁多表联合查询的终极奥义,从内到外,左至右,全连接让你数据世界畅通无阻!"
【8月更文挑战第31天】在数据库领域,数据常分散在多个表中,而SQL JOIN操作如同桥梁,连接这些孤岛,使数据自由流动,编织成复杂的信息网络。本文通过对比内连接、左连接、右连接和全连接的不同类型,并结合示例代码,展示SQL JOIN的强大功能。掌握JOIN技术不仅能高效查询数据,更是数据分析和数据库管理的关键技能。
121 0