一文浅谈sql中的 in与not in,exists与not exists的区别以及性能分析
文章目录
- 1. 文章引言
- 2. 查询对比
- 2.1 in和exists
- 2.2 not in 和not exists
- 2.3 in 与 = 的区别
- 3. 性能分析
- 3.1 in和exists
- 3.2 NOT IN 与NOT EXISTS
- 4. 重要总结
1. 文章引言
我们在工作的过程中,经常使用in,not in,exists,not exists
来查询,比如现在一张项目(project
)表,表的结构和数据:
CREATE TABLE `project` (`id` int(11) NOT NULL AUTO_INCREMENT,`status` varchar(255) DEFAULT NULL,`project_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;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 cc=A.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 cc=B.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 (SELECT'Found'FROMtitle 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 (SELECT'Found'FROMtitle 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.x=t2.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
可以很大,因为y=x.x
可以走t2.y
的索引。
综合以上对IN/EXISTS
的讨论,我们可以得出一个基本通用的结论:
IN
适合于外表大而内表小的情况;EXISTS
适合于外表小而内表大的情况。
我们要根据实际的情况做相应的优化,不能绝对的说谁的效率高谁的效率低,所有的事都是相对的
相关文章:

一文浅谈sql中的 in与not in,exists与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. 文章引言 我们在工作的过程中,经常使用in,not in,exists,not exists来…...

2023前端面试题——JS篇
1.判断 js 类型的方式 1. typeof 可以判断出’string’,‘number’,‘boolean’,‘undefined’,‘symbol’ 但判断 typeof(null) 时值为 ‘object’; 判断数组和对象时值均为 ‘object’ 2. instanceof 原理是 构造函数的 prototype 属性是否出现在对象的原型链中的任何位置 …...

微服务中API网关的作用是什么?
目录 什么是API网关? 为什么要用API网关? API网关架构 API网关是如何实现这些功能的? 协议转换 链式处理 异步请求 什么是API网关? Api网关是微服务的重要组成部分,封装了系统内部的复杂结构,客户端…...

python爬虫--xpath模块简介
一、前言 前两篇博客讲解了爬虫解析网页数据的两种常用方法,re正则表达解析和beautifulsoup标签解析,所以今天的博客将围绕另外一种数据解析方法,它就是xpath模块解析,话不多说,进入内容: 一、简介 XPat…...

【论文阅读】基于意图的网络(Intent-Based Networking,IBN)研究综述
IBN研究综述一、IBN体系结构1.1 体系结构:1.2 闭环流程:1.3 IBN的自动化程度(逐步向前演进):二、IBN 的实现方式2.1 意图获取:2.1.1 YANG、NEMO2.1.2 Frenetic、NetKAT、LAI2.2 意图转译:2.2.1 iNDIRA系统2.2.2 基于模…...

【云原生kubernetes】k8s service使用详解
一、什么是服务service? 在k8s里面,每个Pod都会被分配一个单独的IP地址,但这个IP地址会随着Pod的销毁而消失,重启pod的ip地址会发生变化,此时客户如果访问原先的ip地址则会报错 ; Service (服务)就是用来解决这个问题的…...

Python 数据可视化的 3 大步骤,你知道吗?
Python实现可视化的三个步骤: 确定问题,选择图形转换数据,应用函数参数设置,一目了然 1、首先,要知道我们用哪些库来画图? matplotlib Python中最基本的作图库就是matplotlib,是一个最基础的Python可视…...

CSS基础:盒子模型和浮动
盒子模型 所有HTML元素可以看作盒子,在CSS中,"box model"这一术语是用来设计和布局时使用 CSS盒模型本质上是一个盒子,封装HTML元素。 它包括:外边距(margin),边框(bord…...

OpenHarmony使用Socket实现一个TCP服务端详解
点击获取BearPi-HM_Nano源码 ,以D4_iot_tcp_server为例: 点击查看:上一篇关于socket udp实现的解析 查看 TCPServerTask 方法实现: static void TCPServerTask(void) {//连接WifiWifiConnect("TP-LINK_65A8",...

kafka监控工具安装和使用
1. KafkaOffsetMonitor 该监控是基于一个jar包的形式运行,部署较为方便。只有监控功能,使用起来也较为安全(1)消费者组列表 (2)查看topic的历史消费信息. (3)每个topic的所有parition列表(topic,pid,offset,logSize,lag,owner) (4)对consumer消费情况进…...
近期工作感悟
从应届生变为社畜已经半年了,在这里吐槽一下自己的所想给自己看。 首先是心理层面上的,初期大大增加的压力。 我觉得应届生能够来到大厂的,基本都是在大学有去规划学习,对自己技能比较认可的。比如我在学校自学游戏开发ÿ…...

大数据框架之Hadoop:HDFS(三)HDFS客户端操作(开发重点)
3.1 HDFS客户端环境准备 1.根据自己电脑的操作系统拷贝对应的编译后的hadoop jar包到非中文路径(例如:D:\javaEnv\hadoop-2.77),如下图所示。 2.配置HADOOP_HOME环境变量,如下图所示。 3&#…...

多模式支持无线监控技术:主动式定位、被动式定位
物联网空间信息与数字技术发展至今,已经催生了一大批优秀的践行者。在日常与商业应用中,室内外定位领域依托于这一技术的发展,更是在近几年风光无限。但是并不是说室内定位与室外定位都已经相当成熟,相对来说,室内定位…...

Cy5 Alkyne,1223357-57-0,花青素Cyanine5炔基,氰基5炔烃
CAS号:1223357-57-0 | 英文名: Cyanine5 alkyne,Cy5 Alkyne | 中文名:花青素CY5炔基CASNumber:1223357-57-0Molecular formula:C35H42ClN3OMolecular weight:556.19Purity:95%Appear…...

【MySQL】MySQL 中 WITH 子句详解:从基础到实战示例
文章目录一、什么是 WITH 子句1. 定义2.用途二、WITH 子句的语法和用法1.语法2.使用示例3.优点三、总结"梦想不会碎,只有被放弃了才会破灭。" "Dreams wont break, only abandoned will shatter."一、什么是 WITH 子句 1. 定义 WITH 子句是 M…...

c/c++开发,无可避免的模板编程实践(篇一)
一、c模板 c开发中,在声明变量、函数、类时,c都会要求使用指定的类型。在实际项目过程中,会发现很多代码除了类型不同之外,其他代码看起来都是相同的,为了实现这些相同功能,我们可能会进行如下设计…...
mulesoft MCIA 破釜沉舟备考 2023.02.13.04
mulesoft MCIA 破釜沉舟备考 2023.02.13.03 1. An integration Mule application consumes and processes a list of rows from a CSV file.2. One of the backend systems involved by the API implementation enforces rate limits on the number of request a particle clie…...

Camtasia2023最新版本新功能及快捷键教程
使用Camtasia,您可以毫不费力地在计算机的显示器上录制专业的活动视频。除了录制视频外,Camtasia还允许您从外部源将高清视频导入到录制中。Camtasia的独特之处在于它可以创建包含可单击链接的交互式视频,以生成适用于教室或工作场所的动态视…...

Fabric磁盘扩容后数据迁移
线上环境原来的磁盘比较小,随着业务数据的增多,磁盘需要扩容,因此需要把原来docker数据转移至新的数据盘。 数据迁移 操作系统: centOS 7 docker默认的数据目录为/var/lib/docker 创建一个新的目录/opt/dockerdata&…...

大厂光环下的功能测试,出去面试自动化一问三不知
在一家公司待久了技术能力反而变弱了,原来的许多知识都会慢慢遗忘,这种情况并不少见。一个京东员工发帖吐槽:感觉在大厂快待废了,出去面试问自己接口环境搭建、pytest测试框架,自己做点工太久都忘记了。平时用的时候搜…...

Prompt Tuning、P-Tuning、Prefix Tuning的区别
一、Prompt Tuning、P-Tuning、Prefix Tuning的区别 1. Prompt Tuning(提示调优) 核心思想:固定预训练模型参数,仅学习额外的连续提示向量(通常是嵌入层的一部分)。实现方式:在输入文本前添加可训练的连续向量(软提示),模型只更新这些提示参数。优势:参数量少(仅提…...

YSYX学习记录(八)
C语言,练习0: 先创建一个文件夹,我用的是物理机: 安装build-essential 练习1: 我注释掉了 #include <stdio.h> 出现下面错误 在你的文本编辑器中打开ex1文件,随机修改或删除一部分,之后…...

【第二十一章 SDIO接口(SDIO)】
第二十一章 SDIO接口 目录 第二十一章 SDIO接口(SDIO) 1 SDIO 主要功能 2 SDIO 总线拓扑 3 SDIO 功能描述 3.1 SDIO 适配器 3.2 SDIOAHB 接口 4 卡功能描述 4.1 卡识别模式 4.2 卡复位 4.3 操作电压范围确认 4.4 卡识别过程 4.5 写数据块 4.6 读数据块 4.7 数据流…...

视频字幕质量评估的大规模细粒度基准
大家读完觉得有帮助记得关注和点赞!!! 摘要 视频字幕在文本到视频生成任务中起着至关重要的作用,因为它们的质量直接影响所生成视频的语义连贯性和视觉保真度。尽管大型视觉-语言模型(VLMs)在字幕生成方面…...

全志A40i android7.1 调试信息打印串口由uart0改为uart3
一,概述 1. 目的 将调试信息打印串口由uart0改为uart3。 2. 版本信息 Uboot版本:2014.07; Kernel版本:Linux-3.10; 二,Uboot 1. sys_config.fex改动 使能uart3(TX:PH00 RX:PH01),并让boo…...

均衡后的SNRSINR
本文主要摘自参考文献中的前两篇,相关文献中经常会出现MIMO检测后的SINR不过一直没有找到相关数学推到过程,其中文献[1]中给出了相关原理在此仅做记录。 1. 系统模型 复信道模型 n t n_t nt 根发送天线, n r n_r nr 根接收天线的 MIMO 系…...

iview框架主题色的应用
1.下载 less要使用3.0.0以下的版本 npm install less2.7.3 npm install less-loader4.0.52./src/config/theme.js文件 module.exports {yellow: {theme-color: #FDCE04},blue: {theme-color: #547CE7} }在sass中使用theme配置的颜色主题,无需引入,直接可…...

GO协程(Goroutine)问题总结
在使用Go语言来编写代码时,遇到的一些问题总结一下 [参考文档]:https://www.topgoer.com/%E5%B9%B6%E5%8F%91%E7%BC%96%E7%A8%8B/goroutine.html 1. main()函数默认的Goroutine 场景再现: 今天在看到这个教程的时候,在自己的电…...
学习一下用鸿蒙DevEco Studio HarmonyOS5实现百度地图
在鸿蒙(HarmonyOS5)中集成百度地图,可以通过以下步骤和技术方案实现。结合鸿蒙的分布式能力和百度地图的API,可以构建跨设备的定位、导航和地图展示功能。 1. 鸿蒙环境准备 开发工具:下载安装 De…...

消防一体化安全管控平台:构建消防“一张图”和APP统一管理
在城市的某个角落,一场突如其来的火灾打破了平静。熊熊烈火迅速蔓延,滚滚浓烟弥漫开来,周围群众的生命财产安全受到严重威胁。就在这千钧一发之际,消防救援队伍迅速行动,而豪越科技消防一体化安全管控平台构建的消防“…...