网站导航网学 原创论文 原创专题 网站设计 最新系统 原创论文 论文降重 发表论文 论文发表 UI设计定制 论文答辩PPT格式排版 期刊发表 论文专题
返回网学首页
网学原创论文
最新论文 推荐专题 热门论文 论文专题
当前位置: 网学 > 设计资源 > 数据库 > 正文

EXISTS、IN与JOIN性能分析

论文降重修改服务、格式排版等 获取论文 论文降重及排版 论文发表 相关服务

EXISTS、IN与JOIN,都可以用来实现形如“查询A表中在(或不在)B表中的记录”的查询逻辑

测试代码较长,附于本帖最后。
图表中百分数表示同一组3个查询的执行时间比例。红色表示3个语句中最慢,绿色表示3个语句中最快的,并列则没加颜色。
其中索引只测试了聚集索引,当表中字段较多且查询字段是非聚集索引时,选择执行计划的条件比较复杂,没有测试。并且当表中数量变化后,执行计划可能也有差异。图表反映了3种查询方式的解析机制的不同,基本结论是类似的,但具体情况还要视执行计划而定。

分析结论:
通常情况下,3种查询方式的执行时间:
EXISTS <= IN <= JOIN
NOT EXISTS <= NOT IN <= LEFT JOIN
只有当表中字段允许NULL时,NOT IN的方式最慢:
NOT EXISTS <= LEFT JOIN <= NOT IN

综上:
IN的好处是逻辑直观简单(通常是独立子查询);缺点是只能判断单字段,并且当NOT IN时效率较低,而且NULL会导致不想要的结果。
EXISTS的好处是效率高,可以判断单字段和组合字段,并不受NULL的影响;缺点是逻辑稍微复杂(通常是相关子查询)。
JOIN用在这种场合,往往是吃力不讨好。JOIN的用途是联接两个表,而不是判断一个表的记录是否在另一个表。

编程建议:
(以下三条建议中EXISTS和IN同时代指肯定式逻辑和加NOT后的否定式逻辑)
如果查询条件是单字段主键(有索引且不允许NULL),则EXISTS和IN的性能基本一样,IN的查询通常写法简单、逻辑直观。
如果查询条件涉及多个字段,则最好选择EXISTS,千万不要用字段拼接再IN的方式(索引会失效)。
如果条件不确定,选用EXISTS是最保险的办法,性能最好,不受三值逻辑影响(EXISTS只会返回True/False不会返回Unknown),但代码逻辑稍稍复杂,思路要理清楚,而且相关字段最好采用“表(别)名.字段名”的形式。

附一:IN/NOT IN容易出现的两个问题
参看如下代码:
[code=sql] 
SELECT 
EmployeeID = n, 
EmployeeName = 'E' + RIGHT('000' + CAST(n AS varchar(10)),3) 
INTO #Employees 
FROM dbo.Nums WHERE n <= 10; 

SELECT EmployeeID 
INTO #Badboys 
FROM (SELECT TOP(4) EmployeeID = n FROM dbo.Nums WHERE n <= 10 ORDER BY NEWID()) tmp 
UNION 
SELECT NULL; 

--问题1: 
SELECT * FROM #Employees WHERE EmployeeID IN (SELECT EmployeeID FROM #Badboys); 
SELECT * FROM #Employees WHERE EmployeeID NOT IN (SELECT EmployeeID FROM #Badboys); 
--问题2: 
SELECT * FROM #Employees WHERE EmployeeName IN (SELECT EmployeeName FROM #Badboys); 
SELECT * FROM #Employees WHERE EmployeeName NOT IN (SELECT EmployeeName FROM #Badboys); 
[/code]
其中:
问题1是三值逻辑的问题,说明了在NOT IN遇到NULL时要特别小心(参看关于 not in的疑问一帖)。这也是为什么建议“如果可能,尽量让所有字段都声明为NOT NULL”的原因之一。
问题2是SQL Server子查询处理时命名空间解析的漏洞,说明了在多表查询中采用“表(别)名.字段名”的形式的好处,否则就要对字段名的拼写非常小心。

附二:EXISTS、IN与JOIN性能分析测试代码:
[code=sql] 
--表中字段不允许NULL 

--TestCase1: 无重复数据,无索引 
CREATE TABLE T1(n int NOT NULL); 
CREATE TABLE T2(n int NOT NULL); 
INSERT INTO T1 
SELECT n FROM dbo.Nums WHERE n <= 100; 
INSERT INTO T2 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0; 

--TestCase2: 无重复数据,有索引 
CREATE UNIQUE CLUSTERED INDEX IX_T1 ON T1(n); 
CREATE UNIQUE CLUSTERED INDEX IX_T2 ON T2(n); 

--TestCase3: 有重复数据,无索引 
DROP TABLE T1; 
DROP TABLE T2; 
CREATE TABLE T1(n int NOT NULL); 
CREATE TABLE T2(n int NOT NULL); 
INSERT INTO T1 
SELECT n FROM dbo.Nums WHERE n <= 100; 
INSERT INTO T2 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0 
UNION ALL 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 3 = 0; 

--TestCase4: 有重复数据,有索引 
CREATE CLUSTERED INDEX IX_T1 ON T1(n); 
CREATE CLUSTERED INDEX IX_T2 ON T2(n); 


--表中字段允许NULL 

--TestCase5: 无重复数据,无索引 
DROP TABLE T1; 
DROP TABLE T2; 
CREATE TABLE T1(n int NULL); 
CREATE TABLE T2(n int NULL); 
INSERT INTO T1 
SELECT n FROM dbo.Nums WHERE n <= 100; 
INSERT INTO T2 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0; 

--TestCase6: 无重复数据,有索引 
CREATE UNIQUE CLUSTERED INDEX IX_T1 ON T1(n); 
CREATE UNIQUE CLUSTERED INDEX IX_T2 ON T2(n); 

--TestCase7: 有重复数据,无索引 
DROP TABLE T1; 
DROP TABLE T2; 
CREATE TABLE T1(n int NULL); 
CREATE TABLE T2(n int NULL); 
INSERT INTO T1 
SELECT n FROM dbo.Nums WHERE n <= 100; 
INSERT INTO T2 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 2 = 0 
UNION ALL 
SELECT n FROM dbo.Nums WHERE n <= 100 AND n % 3 = 0; 

--TestCase8: 有重复数据,有索引 
CREATE CLUSTERED INDEX IX_T1 ON T1(n); 
CREATE CLUSTERED INDEX IX_T2 ON T2(n); 


--Foreach TestCase above,分别执行以下两组语句并观察执行计划: 

--肯定式逻辑 

SELECT T1.* 
FROM T1 
WHERE EXISTS (SELECT * FROM T2 WHERE T2.n = T1.n); 

SELECT T1.* 
FROM T1 
WHERE T1.n IN (SELECT T2.n FROM T2); 

SELECT DISTINCT T1.*  --不加DISTINCT可能会引起重复 
FROM T1 
INNER JOIN T2 
ON T1.n = T2.n; 

--否定式逻辑 

SELECT T1.* 
FROM T1 
WHERE NOT EXISTS (SELECT * FROM T2 WHERE T2.n = T1.n); 

SELECT T1.* 
FROM T1 
WHERE T1.n NOT IN (SELECT T2.n FROM T2); 

SELECT T1.* 
FROM T1 
LEFT JOIN T2 
ON T1.n = T2.n 
WHERE T2.n IS NULL; 

--End Foreach 


--清场 
DROP TABLE T1; 
DROP TABLE T2; 

设为首页 | 加入收藏 | 网学首页 | 原创论文 | 计算机原创
版权所有 网学网 [Myeducs.cn] 您电脑的分辨率是 像素
Copyright 2008-2020 myeducs.Cn www.myeducs.Cn All Rights Reserved 湘ICP备09003080号 常年法律顾问:王律师