当前位置: 首页 > news >正文

一文浅谈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循环再对内表进行查询,一直以来认为existsin效率高的说法是不准确的。

如果查询的两个表大小相当,那么用inexists差别不大。

如果两个表中一个较小一个较大,则子查询表大的用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. 语句1
SELECT*
FROMt_1
WHEREc2 NOT IN (SELECT c2 FROM t_2);

查询结果是空值,如下图:

在这里插入图片描述

  1. 语句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_ajmerge_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

  1. 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 
  1. inexists的性能区别

如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in

反之,如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists

其实我们区分inexists主要是造成了驱动顺序的改变(这是性能变化的关键):

  • 如果是exists,那么以外层表为驱动表,先被访问

  • 如果是IN,那么先执行子查询

所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了 。

另外,IN时不对NULL进行处理,如下SQL所示:

SELECT1
FROMDUAL
WHERENULL IN (0, 1, 2, NULL)

查询结果为空。

3.2 NOT IN 与NOT EXISTS

  1. 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 EXISTSNOT IN不能完全互相替换,看具体的需求。如果选择的列可以为空,则不能被替换。

例如下面语句,看他们的区别:

select x,y from t; 

查询xy数据如下所示:

x      y 
------ ------ 
1      3 
3      1 
1      2 
1      1 
3      1 
5 
  1. 使用not innot 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 

所以要具体需求来决定

  1. not innot 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. 重要总结

讨论INEXISTS

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.xt2.y都有索引,我们知道索引是种有序的结构,因此t1t2之间最佳的方案是走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的讨论,我们可以得出一个基本通用的结论:

  1. IN适合于外表大而内表小的情况;
  2. 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消费情况进…...

近期工作感悟

从应届生变为社畜已经半年了,在这里吐槽一下自己的所想给自己看。 首先是心理层面上的,初期大大增加的压力。 我觉得应届生能够来到大厂的,基本都是在大学有去规划学习,对自己技能比较认可的。比如我在学校自学游戏开发&#xff…...

大数据框架之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都会要求使用指定的类型。在实际项目过程中,会发现很多代码除了类型不同之外,其他代码看起来都是相同的,为了实现这些相同功能,我们可能会进行如下设计&#xf…...

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语言&#xff0c;练习0&#xff1a; 先创建一个文件夹&#xff0c;我用的是物理机&#xff1a; 安装build-essential 练习1&#xff1a; 我注释掉了 #include <stdio.h> 出现下面错误 在你的文本编辑器中打开ex1文件&#xff0c;随机修改或删除一部分&#xff0c;之后…...

【第二十一章 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 数据流…...

视频字幕质量评估的大规模细粒度基准

大家读完觉得有帮助记得关注和点赞&#xff01;&#xff01;&#xff01; 摘要 视频字幕在文本到视频生成任务中起着至关重要的作用&#xff0c;因为它们的质量直接影响所生成视频的语义连贯性和视觉保真度。尽管大型视觉-语言模型&#xff08;VLMs&#xff09;在字幕生成方面…...

全志A40i android7.1 调试信息打印串口由uart0改为uart3

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

均衡后的SNRSINR

本文主要摘自参考文献中的前两篇&#xff0c;相关文献中经常会出现MIMO检测后的SINR不过一直没有找到相关数学推到过程&#xff0c;其中文献[1]中给出了相关原理在此仅做记录。 1. 系统模型 复信道模型 n t n_t nt​ 根发送天线&#xff0c; 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配置的颜色主题&#xff0c;无需引入&#xff0c;直接可…...

GO协程(Goroutine)问题总结

在使用Go语言来编写代码时&#xff0c;遇到的一些问题总结一下 [参考文档]&#xff1a;https://www.topgoer.com/%E5%B9%B6%E5%8F%91%E7%BC%96%E7%A8%8B/goroutine.html 1. main()函数默认的Goroutine 场景再现&#xff1a; 今天在看到这个教程的时候&#xff0c;在自己的电…...

学习一下用鸿蒙​​DevEco Studio HarmonyOS5实现百度地图

在鸿蒙&#xff08;HarmonyOS5&#xff09;中集成百度地图&#xff0c;可以通过以下步骤和技术方案实现。结合鸿蒙的分布式能力和百度地图的API&#xff0c;可以构建跨设备的定位、导航和地图展示功能。 ​​1. 鸿蒙环境准备​​ ​​开发工具​​&#xff1a;下载安装 ​​De…...

消防一体化安全管控平台:构建消防“一张图”和APP统一管理

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