EXISTS、IN与JOIN性能分析
EXISTS、IN与JOIN,都可以用来实现形如“查询A表中在(或不在)B表中的记录”的查询逻辑。
在论坛上看到很多人对此有所误解(如关于in的疑惑、用 外连接 和 Is Null 代替 not in两帖),特做一简单测试。
测试结果:
测试代码较长,附于本帖最后。
图表中百分数表示同一组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容易出现的两个问题
参看如下代码:
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);
其中:
问题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;
- 大小: 17.3 KB
分享到:
相关推荐
SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt欢迎下载!
NOT IN、JOIN、IS NULL、NOT EXISTS效率对比 语句一:select count(*) from A where A.a not in (select a from B) 语句二:select count(*) from A left join B on A.a = B.a where B.a is null 语句三:select ...
SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句2.txt,欢迎下载!
PostgreSQL IN vs EXISTS vs ANYALL vs JOIN性能分析
且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询。 如果两张表大小差不多,那么exists和in的效率差不多。 例如: 一张大表为A,一张小表B...
in用的是hash join,所以内表如果小,整个查询的范围都会很小,如果内表很大,外表如果也很大就很慢了,这时候exists才真正的会快过in的方式。 not in和not exists的区别 not in内外表都进行全表扫描,没有用到索引...
NULL 博文链接:https://xinyangwjb.iteye.com/blog/1388909
Having useful indexes speeds up finding individual rows in a table, as well as finding the matching rows needed to join two tables. What You Will Learn After completing this lesson, you will be able ...
LEFT JOIN的主表 ...CREATE TABLE IF NOT EXISTS `categories` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(15) NOT NULL, `created` datetime NOT NULL, PRIMARY KEY (`id`)
if not os.path.exists(dest_folder): os.makedirs(dest_folder) for file_name in os.listdir(src_folder): src_file = os.path.join(src_folder, file_name) dest_file = os.path.join(dest_folder, file_...
使用EXISTS替代IN子句提高效率。 数据类型与查询优化 适当的数据类型选择: 根据实际数据范围和精度选择合适的数值类型。 避免过度使用TEXT/BLOB类型,可能影响索引和查询速度。 数据过滤: 使用谓词推导和SARGable...
DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_name` VARCHAR(50) DEFAULT NULL COMMENT '角色名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=...
有这样的一个问题mysql查询使用mysql中left(right)join筛选条件在on与where查询出的数据是否有差异。可能只看着两个关键字看不出任何的问题。那我们使用实际的例子来说到底有没有差异。例如存在两张表结构表结构1...
Returns the value of the named attribute as an Object, or null if no attribute of the given name exists. getAttribute(String) - Method in interface javax.servlet.http.HttpSession Returns the object ...
- FIX In some cases TFlexCurve.Paint leaves selected black pen in the canvas which then affects on next flex-control output (in particular when the next flex-control have gradient fill). FlexEdit...
hive练习数据和hive练习题包含了hive练习数据,hive数据的建表ddl和hive练习题,非常适合做hive练习,新手培训,快速...LEFT SEMI JOIN Hive当前没有实现 IN/EXISTS 子查询,可以用 LEFT SEMI JOIN 重写子查询语句。
for pg in range(total): page = doc[pg] zoom = int(zoom) # 值越大,分辨率越高,文件越清晰 rotate = int(0) trans = fitz.Matrix(zoom / 100.0, zoom / 100.0).prerotate(rotate) pm = page.get_pixmap...
with open(os.path.join(json_root_path, 'root_draft_meta_info.json'), 'r', encoding='utf-8') as f: json_path = (json.load(f)['all_draft_store'][0]['draft_fold_path']) # 打开json文件并将其转换为srt...
def delete_folder(path): if os.path.exists(path): for file in os.listdir(path): file_path = os.path.join(path,file) if os.path.isdir(file_path): delete_folder(file_path) else: os.remove(file_path) ...