SQL Server-聚焦NOT IN VS NOT EXISTS VS LEFT JOIN...IS NULL性能分析(十八)

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

前言

本节我们来综合比较NOT IN VS NOT EXISTS VS LEFT JOIN...IS NULL的性能,简短的内容,深入的理解,Always to review the basics。

NOT IN、NOT EXISTS、LEFT JOIN...IS NULL性能分析

我们首先创建测试表

复制代码
USE TSQL2012
GO

CREATE SCHEMA [compare]
CREATE TABLE [compare].t_left (
        id INT NOT NULL PRIMARY KEY,
        value INT NOT NULL,
        stuffing VARCHAR(200) NOT NULL
)
CREATE TABLE [compare].t_right (
        id INT NOT NULL PRIMARY KEY,
        value INT NOT NULL,
        stuffing VARCHAR(200) NOT NULL
)
GO
复制代码

接着我们在两个表中的列value上创建索引

USE TSQL2012
GO

CREATE INDEX idx_left_value ON [compare].t_left (value)
CREATE INDEX idx_right_value ON [compare].t_right (value)

我们在t_left和t_right表中插入如下测试数据

复制代码
USE TSQL2012
GO

BEGIN TRANSACTION
DECLARE @cnt INT
SET @cnt = 1
WHILE @cnt <= 100000
BEGIN
        INSERT
        INTO    [compare].t_left
        VALUES  (
                @cnt,
                @cnt % 10000,
                LEFT('Left ' + CAST(@cnt AS VARCHAR) + ' ' + REPLICATE('*', 200), 200)
                )
        SET @cnt = @cnt + 1
END;
WITH    rows AS
        (
        SELECT  1 AS row
        UNION ALL
        SELECT  row + 1
        FROM    rows
        WHERE   row < 10
        )
INSERT
INTO    [compare].t_right
SELECT  (id - 1) * 10 + row + 1,
        value + 1,
        LEFT('Right ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 200), 200)
FROM    [compare].t_left
CROSS JOIN
        rows
COMMIT
复制代码

我们稍微解释下上述插入的测试数据:

(1)t_left表中插入10万条数据,其中包含1万条重复数据。

(2)t_right表中插入100万条数据,其中包含1万条重复数据。

(3)t_left表中插入10条t_right表中没有的数据。

接下来我们一个个来看看其查询执行计划。

NOT IN性能分析

复制代码
USE TSQL2012
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT  l.id, l.value
FROM    [compare].t_left l
WHERE   l.value NOT IN
        (
        SELECT  value
        FROM    [compare].t_right r
        )
复制代码

我们重点看看上述图做了标记的两个重要的地方,最后返回结果集时使用了Merge Anti Semi Join也就是说是上述Merge Join和Right Anti Semi Join的结合,可以说这是一种非常高效的方式,事先通过索引来排序然会获取两个表的结果集。数据库通过Merge Join来迭代两个表的结果集从小值到大值,当然也是通过指针指向二者结果集的当前值然后接着指向下一个值。而Anti Semi Join主要是干什么的呢?前面我们讲过它是半联接,此时数据库引擎只要匹配到t_right表中的值就跳过所有t_left和t_right表其他也同样匹配的同一个值,为什么会跳过呢? 因为此时Stream Aggregate起到了决定性作用(【关于Stream Aggregate前面简单了解了下,感觉理解的还是不够透,写这篇文章时才算是灰常了解了,后续会专门写写Stream Aggregate和Hash Aggregate】)我们知道Stream Aggregate首先需要排序,然后进行分组接着就是聚合,因为我们建立了索引所以就有了排序,接着执行Stream Aggregate进行分组,通过查看Stream Aggregate如下具体信息知道。因为对t_right表中的值进行了分组,所以当进行合并右半联接时,只取组中第一个,其余的自然而然就进行跳过,所以这种方式非常高效,通过索引来进行排序,再通过Stream Aggregate进行分组,最后执行Merge Join(Right Anti Semi Join)。最后我们看到查询仅仅只耗费了0.315秒。

NOT EXISTS性能分析

我们运行如下查询

复制代码
USE TSQL2012
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT  l.id, l.value
FROM    [compare].t_left l
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    [compare].t_right r
        WHERE   r.value = l.value
        )
复制代码

关于其查询耗费时间就不再给出了,其实NOT EXISTS和NOT查询计划和查询时间都是一样的,并没有任何区别,我们之前在单独讨论NOT EXISTS和NOT IN时就已经明确说过,二者在查询列不为NULL的前提下,二者的查询开销是一样的,而将查询列设置为可NULL时,NOT EXISTS的性能远高于NOT IN,这里我们就不过多的讨论了,不明白的童鞋可以看看前面关于二者比较的文章。

LEFT JOIN....IS NULL性能分析

复制代码
USE TSQL2012
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT  l.id, l.value
FROM    [compare].t_left l
LEFT JOIN
        [compare].t_right r
ON      r.value = l.value
WHERE   r.value IS NULL
复制代码

到这里我们知道很显然结果集肯定是一样的,但是查询计划和上述NOT EXISTS、NOT IN有很大的差异,LEFT JOIN...IS NULL首先是使用LEFT JOIN返回所有数据,其中包括重复的,然后再进行过滤,为什么会先进行LEFT JOIN然后再进行Filter呢?因为SQL Server根本无法很智能的识别LEFT JOIN上紧跟着的IS NULL,所以需要两步操作来完成。此时我们需要过滤100万条数据,这是一个非常耗时的工作,所以此时利用非常高效的Hash Match并且是并行的,但是过滤这些值还是要花费很长时间。整个时间花费了0.989秒,其查询耗费时间是NOT EXISTS或者NOT IN的3倍。所以到这里,关于此三者我们可以定下如下这样一个结论。

NOT IN VS NOT EXISTS VS LEFT JOIN..IS NULL结论:当查询缺省值时利用NOT EXISTS和NOT IN是最佳方式,但是前提是二者查询列都不能为NULL,否则使用NOT EXISTS。而LEFT JOIN...IS NULL因其总是不会跳过已经匹配过的值而是利用先返回所有结果集然后过滤的方式,其低效性可想而知。

总结

本节我们比较了NOT EXISTS和NOT IN和LEFT JOIN..IS NULL的性能,最终得出了三者性能分析结论,下一节我们已经确定是最后一篇终极篇比较EXISTS VS IN VS JOIN的性能,简短的内容,深入的理解,我们下节再会。




本文转自Jeffcky博客园博客,原文链接:http://www.cnblogs.com/CreateMyself/p/6165543.html,如需转载请自行联系原作者

相关实践学习
使用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
目录
打赏
0
0
0
0
60
分享
相关文章
管理系统VS SQL:高效集成的关键技巧与方法
在现代企业信息化建设中,管理系统(如ERP、CRM等)与SQL数据库之间的紧密集成是确保数据流动顺畅、业务逻辑高效执行的关键
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)")
开发效率与灵活性:SQL vs NoSQL
【8月更文第24天】随着大数据和实时应用的兴起,数据库技术也在不断发展以适应新的需求。传统的SQL(结构化查询语言)数据库因其成熟的数据管理机制而被广泛使用,而NoSQL(Not Only SQL)数据库则以其灵活性和扩展性赢得了众多开发者的青睐。本文将从开发者的视角出发,探讨这两种数据库类型的优缺点,并通过具体的代码示例来说明它们在实际开发中的应用。
175 1
|
8月前
|
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
204 13
|
8月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
121 9
HQL vs SQL:谁将统治数据库查询的未来?揭秘Hibernate的神秘力量!
【8月更文挑战第31天】Hibernate查询语言(HQL)是一种面向对象的查询语言,它模仿了SQL的语法,但操作对象为持久化类及其属性,而非数据库表和列。HQL具有类型安全、易于维护等优点,支持面向对象的高级特性,内置大量函数,可灵活处理查询结果。下面通过示例对比HQL与SQL,展示HQL在实际应用中的优势。例如,HQL查询“从员工表中筛选年龄大于30岁的员工”只需简单地表示为 `FROM Employee e WHERE e.age &gt; 30`,而在SQL中则需明确指定表名和列名。此外,HQL在处理关联查询时也更为直观易懂。然而,对于某些复杂的数据库操作,SQL仍有其独特优势。
104 0
性能与扩展性的考量:SQL vs NoSQL
【8月更文第24天】在选择数据库系统时,开发者和架构师面临着一个关键决策:是选择传统的SQL(结构化查询语言)数据库还是现代的NoSQL(非关系型)数据库。这两种类型各有优劣,尤其是在性能和扩展性方面。本文将深入探讨SQL和NoSQL数据库在这两个方面的差异,并通过具体的代码示例来展示它们各自的优势。
234 0
|
7月前
|
数据模型与应用场景对比:SQL vs NoSQL
【8月更文第24天】随着大数据时代的到来,数据存储技术也在不断演进和发展。传统的SQL(Structured Query Language)数据库和新兴的NoSQL(Not Only SQL)数据库各有优势,在不同的应用场景中发挥着重要作用。本文将从数据模型的角度出发,对比分析SQL和NoSQL数据库的特点,并通过具体的代码示例来说明它们各自适用的场景。
166 0

热门文章

最新文章

AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等