数据库MySQL,一条sql not in了一条子查询如何sql优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了数据库MySQL,一条sql not in了一条子查询如何sql优化,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3774字,纯文字阅读大概需要6分钟。
内容图文
![数据库MySQL,一条sql not in了一条子查询如何sql优化](/upload/InfoBanner/zyjiaocheng/864/59522d058e5546dc94a40a98725a0f11.jpg)
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)。
为什么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,所以查询结果永远为空。
为什么在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,那用什么代替呢?
三种解决办法:
仍使用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
使用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
使用not exists代替
共有的不要了,要独有的。
select * from a
where not exists(
select 1 from b where a.col = b.col
);
来自连接https://blog.csdn.net/ZZh1301051836/article/details/90699394
内容总结
以上是互联网集市为您收集整理的数据库MySQL,一条sql not in了一条子查询如何sql优化全部内容,希望文章能够帮你解决数据库MySQL,一条sql not in了一条子查询如何sql优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。