设计网站轮廓模板,招人在哪个网站比较好找,百度seo培训公司,万网怎么做网站前言 本节我们来综合比较NOT IN VS NOT EXISTS VS LEFT JOIN...IS NULL的性能#xff0c;简短的内容#xff0c;深入的理解#xff0c;Always to review the basics。 NOT IN、NOT EXISTS、LEFT JOIN...IS NULL性能分析 我们首先创建测试表 USE TSQL2012
GOCREATE SCHEMA [c… 前言 本节我们来综合比较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
GOCREATE 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
GOCREATE 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
GOBEGIN TRANSACTION
DECLARE cnt INT
SET cnt 1
WHILE cnt 100000
BEGININSERTINTO [compare].t_leftVALUES (cnt,cnt % 10000,LEFT(Left CAST(cnt AS VARCHAR) REPLICATE(*, 200), 200))SET cnt cnt 1
END;
WITH rows AS(SELECT 1 AS rowUNION ALLSELECT row 1FROM rowsWHERE 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 JOINrows
COMMIT 我们稍微解释下上述插入的测试数据 1t_left表中插入10万条数据其中包含1万条重复数据。 2t_right表中插入100万条数据其中包含1万条重复数据。 3t_left表中插入10条t_right表中没有的数据。 接下来我们一个个来看看其查询执行计划。 NOT IN性能分析 USE TSQL2012
GOSET STATISTICS IO ON
SET STATISTICS TIME ONSELECT l.id, l.value
FROM [compare].t_left l
WHERE l.value NOT IN(SELECT valueFROM [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
GOSET STATISTICS IO ON
SET STATISTICS TIME ONSELECT l.id, l.value
FROM [compare].t_left l
WHERE NOT EXISTS(SELECT NULLFROM [compare].t_right rWHERE r.value l.value) 关于其查询耗费时间就不再给出了其实NOT EXISTS和NOT查询计划和查询时间都是一样的并没有任何区别我们之前在单独讨论NOT EXISTS和NOT IN时就已经明确说过二者在查询列不为NULL的前提下二者的查询开销是一样的而将查询列设置为可NULL时NOT EXISTS的性能远高于NOT IN这里我们就不过多的讨论了不明白的童鞋可以看看前面关于二者比较的文章。 LEFT JOIN....IS NULL性能分析 USE TSQL2012
GOSET STATISTICS IO ON
SET STATISTICS TIME ONSELECT 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的性能简短的内容深入的理解我们下节再会。
