当前位置:   article > 正文

SQL优化 —— in与not in_sql not in

sql not in

1 in

文章从三方面介绍:查询集合,查询出错率,查询效率。主要讲什么时候可以用in,用in效果不佳时,用什么替代。总结出两点:

  • in后的查询集合不确定
    例如in (select…),应判断内查询与外查询的关系。当内查询的表小时(小于外查询的表),用in效率高。当外查询的表小时(小于内查询的表),用exists效率高。(因为in 先执行内查询,再执行外查询;而 exists先执行外查询再执行内查询)
  • in后的查询集合是确定且有限
    集合内的值连续时,应尽可能使用between …and 。
    集合内的值不连续时,可以用in,例如in (1,3,7)。

1.1 查询集合

in后的查询集合不确定时,例如in (select…),应判断内查询与外查询的关系。当内查询的表小时(小于外查询的表),用in效率高。当外查询的表小时(小于内查询的表),用exists效率高。(因为in 先执行内查询,再执行外查询;而 exists先执行外查询再执行内查询)
in后的查询集合是确定且有限的集合,且集合内的值连续时,应尽可能使用between …and 。
in后的查询集合是确定且有限的集合,但集合内的值不连续时,可以用in,例如in (1,3,7)。

1.2 查询出错率

SQL中用in 或 not in 容易出错,所以应尽量避免使用in或not in。什么时候可以使用?确定且有限的集合时,可以使用。如 IN (0,1,2)。

下面的例子参考自:SQL性能优化 - 避免使用 IN 和 NOT IN
在这里插入图片描述
在这里插入图片描述
一位大神曾经说过,如果是确定且有限的集合时,可以使用。如 IN (0,1,2)。
参考文献:[笔记] SQL性能优化 - 避免使用 IN 和 NOT IN

1.3 查询效率

一句话总结:in先执行子查询,再执行主查询;而exists先执行主查询,再执行子查询。

具体的:

in在查询的时候,首先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。
exists先执行主查询,再根据主查询的结果,执行子查询。
具体例子,见参考文献。
应用场景:
当子查询的结果较小,且主查询的表较大且有索引时,应用in。
当主查询的结果较小,而子查询的表较大且有索引时,应用exists。

即先把大表减小,然后再去匹配另一个表。

in和exists的不同,即驱动顺序的不同(这是性能变化的关键)。如果是exists,那么以外层表为驱动表,先被访问。如果是IN,那么先执行子查询,以内层表为驱动表。
所以我们以驱动表的快速返回为目的(即越快获得驱动表,越好),那么就会考虑到索引及结果集的关系了 。另外IN时不对NULL进行处理。

in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

参考文献:SQL中in和exists的区别

2 not in

2.1 查询集合中有null,不使用not in

如果查询集合中有null,不适合用not in,容易出错(查询结果为空)。例如:x not in (1,2,null)。

  1. 为什么x not in (1,2,null)会出错呢?
    因为x not in (1,2,null) 等价于x!=1 and x!=2 and x!=null ,而在sql中,对于任意x,x!=null永远为false,所以整体结果永远为false,所以查询结果永远为空。

  2. 为什么在sql中,x!=null为false呢?
    参考文献:SQL条件!=null查不出数据
    sql的逻辑表达式有三种可能值:true、false、unknown。
    其他语言的逻辑表达式都是只有两种可能值,而sql中有三种,这是sql特有的。从字面上理解unknown就是:什么都不知道。在sql中将任何值(包括null本身)与null作比较,都会返回unknown。而在查询表达式(having、where)中, unknown会被视为false。
    但是并不是所有情况下,都会将unknown视为false,在 check约束中,就会将unknown视为true。所以,在用check约束将字段设置为>=0时,还可以向该字段中插入null值,因为在check中,null>=0的结果unknown,会被视为true。

2.2 查询集合中有null,不使用not in,那用什么代替呢?

三种解决办法:

  1. 仍使用not in ,但修改sql语句。
    将in后的查询结果中的null过滤掉。
    例如:
select name from table1 where name not in (
 select name from table2 where name is not null
);
  • 1
  • 2
  • 3
  1. 使用join 代替
select * 
from a left join b on a.id=b.aid
where b.aid is null
  • 1
  • 2
  • 3
用and:
select Table_A.ID,Table_A.name 
from Table_A left join Table_B on Table_A.ID=Table_B.ID and Table_B.ID is null
用where:
select Table_A.ID,Table_A.name 
from Table_A left join Table_B on Table_A.ID=Table_B.ID where Table_B.ID is null
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  1. 使用not exists代替
共有的不要了,要独有的。
select * from a 
where not exists(
     select 1 from b where a.col = b.col
);
  • 1
  • 2
  • 3
  • 4
  • 5
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Monodyee/article/detail/447805
推荐阅读
相关标签
  

闽ICP备14008679号