网站建设 图标,德阳公司做网站,企业邮箱域名查询,素材网站定制文章目录1. 文章引言2. 查询对比2.1 in和exists2.2 not in 和not exists2.3 in 与 的区别3. 性能分析3.1 in和exists3.2 NOT IN 与NOT EXISTS4. 重要总结1. 文章引言
我们在工作的过程中#xff0c;经常使用in#xff0c;not in#xff0c;exists#xff0c;not exists来…
文章目录1. 文章引言2. 查询对比2.1 in和exists2.2 not in 和not exists2.3 in 与 的区别3. 性能分析3.1 in和exists3.2 NOT IN 与NOT EXISTS4. 重要总结1. 文章引言
我们在工作的过程中经常使用innot inexistsnot exists来查询比如现在一张项目(project)表表的结构和数据
CREATE TABLE project (id int(11) NOT NULL AUTO_INCREMENT,status varchar(255) DEFAULT NULL,project_name varchar(255) DEFAULT NULL
) ENGINEInnoDB AUTO_INCREMENT6 DEFAULT CHARSETutf8;INSERT INTO project VALUES (1, finish, 太湖佳园);
INSERT INTO project VALUES (2, during, 尚东雅园);
INSERT INTO project VALUES (3, start, 水乡苑一区);
INSERT INTO project VALUES (4, during, 水乡苑二区);查询状态为已完成和进行中的记录我们可以写成如下的SQL语句
select * from project where status in (finish,during);查询结果如下图 这只是我们开发中的一个简单示例接下来我们详细解说 in与not in,exists与not exists的区别以及性能分析。
2. 查询对比
2.1 in和exists
in是把外表和内表作hash连接。
exists是对外表作loop循环每次loop循环再对内表进行查询一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当那么用in和exists差别不大。
如果两个表中一个较小一个较大则子查询表大的用exists子查询表小的用in。
例如表A(小表)表B(大表) -- 效率低用到了A表上cc列的索引
select * from A where cc in(select cc from B) -- 效率高用到了B表上cc列的索引
select * from A where exists(select cc from B where ccA.cc) 相反的 -- 效率高用到了B表上cc列的索引
select * from B where cc in(select cc from A) -- 效率低用到了A表上cc列的索引。
select * from B where exists(select cc from A where ccB.cc) 2.2 not in 和not exists
not in逻辑上不完全等同于not exists如果你误用了not in小心你的程序存在致命的BUG请看下面的例子
-- 创建t1表
create table t_1(c1 int,c2 int);-- 创建t2表
create table t_2(c1 int,c2 int);-- 向t1表中插入数据
insert into t_1 values(1,2);
insert into t_1 values(1,3);-- 向t2表中插入数据
insert into t_2 values(1,2);
insert into t_2 values(1,null); 先后执行如下两条查询语句
语句1
SELECT*
FROMt_1
WHEREc2 NOT IN (SELECT c2 FROM t_2);查询结果是空值如下图 语句2
SELECT*
FROMt_1
WHERENOT EXISTS (SELECT1FROMt_2WHEREt_2.c2 t_1.c2);查询结果c1 1,c2 3如下图所示 正如你所看到的not in出现了不期望的结果集存在逻辑错误。
如果看一下上述两个select语句的执行计划也会不同语句2使用了hash_aj所以请尽量不要使用not in(它会调用子查询)而尽量使用not exists它会调用关联子查询。
如果子查询中返回的任意一条记录含有空值则查询将不返回任何记录。
如果子查询字段有非空限制这时可以使用not in并且可以通过提示让它用hasg_aj或merge_aj连接。
如果查询语句使用了not in那么对内外表都进行全表扫描没有用到索引。而not exists的子查询依然能用到表上的索引。所以无论哪个表大用not exists都比not in 要快。
2.3 in 与 的区别 SELECTNAME
FROMstudent
WHERENAME IN (zhang, wang, zhao);与
SELECTNAME
FROMstudent
WHERENAME zhang
OR NAME wang
OR NAME zhao的结果是相同的。
3. 性能分析
3.1 in和exists
EXISTS的执行流程
SELECT*
FROMt1
WHEREEXISTS (SELECT NULL FROM t2 WHERE y x)可以理解为:
for x in ( select * from t1 ) loop if ( exists ( select null from t2 where y x.x ) then
OUTPUT THE RECORD
end if
end loop in和exists的性能区别
如果子查询得出的结果集记录较少主查询中的表较大且又有索引时应该用in。
反之如果外层的主查询记录较少子查询中的表大又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变这是性能变化的关键: 如果是exists那么以外层表为驱动表先被访问 如果是IN那么先执行子查询
所以我们会以驱动表的快速返回为目标那么就会考虑到索引及结果集的关系了 。
另外IN时不对NULL进行处理如下SQL所示
SELECT1
FROMDUAL
WHERENULL IN (0, 1, 2, NULL)查询结果为空。
3.2 NOT IN 与NOT EXISTS
NOT EXISTS的执行流程
SELECT.....
FROMROLLUP R
WHERENOT EXISTS (SELECTFoundFROMtitle TWHERER.source_id T.Title_ID);可以理解为:
for x in ( select * from rollup ) loop
if ( not exists ( that query ) ) then
OUTPUT
end if;
end loop; 注意NOT EXISTS与NOT IN不能完全互相替换看具体的需求。如果选择的列可以为空则不能被替换。
例如下面语句看他们的区别
select x,y from t; 查询x和y数据如下所示
x y
------ ------
1 3
3 1
1 2
1 1
3 1
5 使用not in和not exists查询结果如下
SELECT*
FROMt
WHEREx NOT IN (SELECT y FROM t t2);查询无结果no rows
SELECT*
FROMt
WHERENOT EXISTS (SELECTNULLFROMt t2WHEREt2.y t.x);查询结果为
x y
------ ------
5 NULL 所以要具体需求来决定
not in和not exists的性能区别
not in只有当子查询中select关键字后的字段有not null约束或者有这种暗示时用not in。另外如果主查询中表大子查询中的表小但是记录多则应当使用not in并使用anti hash join。
如果主查询表中记录少子查询表中记录多并有索引可以使用not exists另外not in最好也可以用/* HASH_AJ */或者外连接is null。
NOT IN在基于成本的应用中较好比如:
SELECT.....
FROMROLLUP R
WHERENOT EXISTS (SELECTFoundFROMtitle TWHERER.source_id T.Title_ID);最好修改成如下方式
SELECT......
FROMtitle T,ROLLUP R
WHERER.source_id T.Title_id ()
AND T.Title_id IS NULL;或者佳:
SELECT/* HASH_AJ */...
FROMROLLUP R
WHEREource_id NOT IN (SELECTource_idFROMtitle TWHEREource_id IS NOT NULL)4. 重要总结
讨论IN和EXISTS。
select * from t1 where x in ( select y from t2 ) 事实上可以理解为
SELECT*
FROMt1,(SELECT DISTINCT y FROM t2) t2
WHEREt1.x t2.y;如果你有一定的SQL优化经验从这句很自然的可以想到t2绝对不能是个大表因为需要对t2进行全表的唯一排序。
如果t2很大这个排序的性能是不可忍受的但是t1可以很大为什么呢
最通俗的理解就是因为t1.xt2.y可以走索引。但这并不是一个很好的解释。
试想如果t1.x和t2.y都有索引我们知道索引是种有序的结构因此t1和t2之间最佳的方案是走merge join。
另外如果t2.y上有索引对t2的排序性能也有很大提高。
select * from t1 where exists ( select null from t2 where y x ) 可以理解为
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y x.x )
then
OUTPUT THE RECORD!
end if
end loop 这个更容易理解t1永远是个表扫描因此t1绝对不能是个大表而t2可以很大因为yx.x可以走t2.y的索引。
综合以上对IN/EXISTS的讨论我们可以得出一个基本通用的结论
IN适合于外表大而内表小的情况EXISTS适合于外表小而内表大的情况。
我们要根据实际的情况做相应的优化不能绝对的说谁的效率高谁的效率低所有的事都是相对的
