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

MSQL系列(七) Mysql实战-SQL语句Join,exists,in的区别

Mysql实战-SQL语句Join,exists,in的区别

前面我们讲解了索引的存储结构,B+Tree的索引结构,以及索引最左侧匹配原则及讲解一下常用的SQL语句的优化建议,今天我们来详细讲解一下 我们经常使用的 join, exist, in三者的区别

文章目录

      • Mysql实战-SQL语句Join,exists,in的区别
        • 1.表结构
        • 2.使用 in查询 用户及订单表
        • 3.使用 exists查询 替换 in语句, 查询用户及订单表
        • 4. in exists 用法对比
        • 4. 加索引看下执行结果与not in, not exists对比

1.表结构

新建表结构 user, user_info

#新建表结构 user
CREATE TABLE `user` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',`id_card` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '身份证ID',`user_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名字',`age` int NOT NULL COMMENT '年龄',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表'#新建订单表 order_info
CREATE TABLE `order_info` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',`order_id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '订单ID',`user_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名字',`address` char(32)  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户地址',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表'

先插入测试数据, 插入 5条user 测试数据 2条订单数据

INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (1, '11', 'aa', 10);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (2, '22', 'bb', 20);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (3, '33', 'cc', 30);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (4, '44', 'dd', 40);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (5, '55', 'ee', 50);#2条订单数据
INSERT INTO `test`.`order_info` (`id`, `order_id`, `user_name`, `address`) VALUES (1, '1', 'aa', '北京');
INSERT INTO `test`.`order_info` (`id`, `order_id`, `user_name`, `address`) VALUES (2, '2', 'bb', '上海');
2.使用 in查询 用户及订单表

我们查看下存在订单的用户有哪些?

select * from user where user_name in (select user_name from order_info );
explain select * from user where user_name in (select user_name from order_info )

我们看下Explain执行分析

  • in查询没有驱动表,先执行子查询,然后再执行外层表
  • in 子查询会 使用了临时表 Start Temporary
  • in查询其实把外表和内表 作hash 连接,Using join buffer (hash join) 使用hash 连接, 当子查询数量较多时,hash连接后的数据量特别大
  • order_info 表 type=ALL没有索引, user表也没有索引, type=ALL

在这里插入图片描述
查询结果: 2条数据,正确
在这里插入图片描述

3.使用 exists查询 替换 in语句, 查询用户及订单表

前面我们使用了in来进行查询, 现在我们使用 exists来替换 in,实现查询效果

select * from user where exists (select user_name from order_info  where user.user_name = order_info.user_name);
explain select * from user where exists (select user_name from order_info  where user.user_name = order_info.user_name);

我们看下Explain执行分析

  • Exists 同样也会使用临时表
  • 二者的连接同样也是 hash join
  • Exist是 外层表是驱动表, 先执行外层表,再执行内层表
  • 这样看起来似乎是没有区别的,下面我们专门对比下2者
    在这里插入图片描述
    查询结果2条数据,正确
    在这里插入图片描述
4. in exists 用法对比

前面我们尝试用 in 和 exists 来对比
我们都知道 如果涉及子查询的时候,我们都是小表驱动大表,先查小表,然后查大表,这就导致了in和exists用法的区别

  • in 先执行子查询,使用于内小,外大
  • exist 先执行外层表驱动表,适用于外小,内大
  • in适合 外层大, 内层小, 先执行内层子查询,过滤出来一小部分数据,再用来查外层
  • exist适用于外层小,内层大,先执行外层驱动表查询,出来一部分数据,再查内层表

简单通俗来讲就是下面的案例

如果 order表有1w数据,user表有10条数据, order是大表, user是小表, 采用 in 内小外大的用法

  • select * from order where user_name in ( select user_name from user )
  • in 子查询 user 是小表, 外层order 是大表

如果 user表有1w数据, order表有10条数据,上面的明显是错误的用法,采用 exists的 外小内大的用法

  • select * from order where exists (select user_name from user where user.user_name = order_info.user_name )
  • order 外小, user 内大
4. 加索引看下执行结果与not in, not exists对比

前面我们尝试用 in 和 exists 来对比,加上索引后,对比下 二者的结果
二者全部都使用了索引

alter table user add index idx_name(user_name);
alter table order_info add index idx_name(user_name);
explain select * from user where user_name in (select user_name from order_info )
explain select * from user where exists (select user_name from order_info  where user.user_name = order_info.user_name);
  • order_info 表 type=index, user表 type=ref,全都使用了索引
    在这里插入图片描述
    我们再尝试下 not in 和 not exists
explain select * from user where user_name not in (select user_name from order_info )
explain select * from user where not  exists (select user_name from order_info  where user.user_name = order_info.user_name);
  • not in 查询类型 select type 变成了 PRIMARY 和 DEPENDENT SUBQUERY
  • not in 索引 type类型变成了ALL 和index_subquery
  • not exists 查询类型 select type 依旧是 simple
  • not in 索引 type类型变成了ALL 和ref
    所以我们还是尽量不要用 not in ,not exists 这种SQL语法
    在这里插入图片描述

至此,我们彻底的了解了 in, exists的区别,下一篇我们讲解下 join的原理,通过join原理,我们可以更加了解SQL查询的底层逻辑

相关文章:

MSQL系列(七) Mysql实战-SQL语句Join,exists,in的区别

Mysql实战-SQL语句Join,exists,in的区别 前面我们讲解了索引的存储结构,BTree的索引结构,以及索引最左侧匹配原则及讲解一下常用的SQL语句的优化建议,今天我们来详细讲解一下 我们经常使用的 join, exist&…...

最新壁纸自动采集系统网站PHP源码/360壁纸官方数据接口采集/ZHEYI采集源码

源码介绍: 最新壁纸自动采集系统网站PHP源码,它是ZHEYI自动采集源码,能够在360壁纸官方数据接口采集。很好用的壁纸网站源码分享,仅供学习,请勿商用。 ZHEYI自动采集壁纸PHP源码,能全自动采集高清壁纸网源…...

Redis在分布式场景下的应用

分布式缓存 缓存的基本作用是在高并发场景下对应服务的保护缓冲 – 基于Redis集群解决单机Redis存在的问题 单机的Redis存在四大问题: redis由于高强度性能采用内存 但是意味着丢失的风险单结点redis并发能力有限分布式服务中数据过多 依赖内存的redis 明显单机不…...

2316. 统计无向图中无法互相到达点对数

2316. 统计无向图中无法互相到达点对数 难度: 中等 来源: 每日一题 2023.10.21 给你一个整数 n ,表示一张 无向图 中有 n 个节点,编号为 0 到 n - 1 。同时给你一个二维整数数组 edges ,其中 edges[i] [ai, bi] 表示节点 ai 和 bi 之间…...

Selenium定向爬取海量精美图片及搜索引擎杂谈

我自认为这是自己写过博客中一篇比较优秀的文章,同时也是在深夜凌晨2点满怀着激情和愉悦之心完成的。首先通过这篇文章,你能学到以下几点: 1.可以了解Python简单爬取图片的一些思路和方法 2.学习Selenium自动、测试分析动态网页和正则表达式的区别和共同点 …...

面试题—JAVA基础①

文章目录 1.Java面向对象有哪些特征?2.ArrayList和LinkedList有什么区别?3.Java接口和抽象类有哪些区别?4.hashcode和equals如何使用?5.try-catch6.局部变量和实例变量7.String、StringBuffer、StringBuilder 的区别?8…...

naive-ui的n-data-table标签奇特bug记录

具体参考之前的博文:vueday02——使用naive-ui做一个ACM看榜-CSDN博客 具体代码在这里面 原因:在本地运行的时候,datatable里面使用列表渲染成字符串前端设置样式进行转换,但是在正式部署的时候,这个组件没有将其自动…...

微信小程序OA会议系统个人中心授权登入

在我们的完成微信登入授权之前,首先我们要完成我们前面所写的代码,如果有不会的大家可以去看以下我发的前面几个文章链接我发下面了,各位加油! 微信小程序OA会议系统数据交互-CSDN博客 微信小程序会议OA系统其他页面-CSDN博客 …...

Git(一)Windows下安装及使用Git Bash

目录 一、简介1.1 什么是Git?1.2 Git 的主要特点1.3 什么是 Git Bash? 二、下载三、安装3.1 同意协议3.2 选择安装位置3.3 其他配置(【Next】 即可)3.4 安装完毕3.5 打开 Git Bash 官网地址: https://www.git-scm.com/…...

[AUTOSAR][诊断管理][ECU][$19] 读取ECU的DTC故障信息

一、简介 在车载诊断中常用的诊断协议有ISO 14229等,在协议中主要定义了诊断请求、诊断响应的报文格式及ECU该如何处理诊断请求的应用。其中ISO 14229系列标准协议定义了用于行业内诊断通信的需求规范,也就是UDS。UDS主要应用于OSI七层模型的第七层——…...

前端精度问题 (id 返回的和传给后端的不一致问题)

eg: 后端返回 id 10976458979374929 前端获取到的: 10976458979374928 原因: js 中 Number类型范围-2^53 1 到 2^53 - 1 Number.isSafeInteger()用来判断一个整数是否落在这个范围之内。 java中 Long 类型的取值范围是-2^63 1 到 2^63 - 1, 比JavaScript中大很多&#xff0…...

WPF Material Design UI框架

前言 Material Design in xaml 是开源免费的ui框架&#xff0c;工控软件主打的就是简单界面。 以下简称MD 相关资源 MaterialDesignInXamlToolkit Github 地址 MD 快速启动 MD 案例压缩包 MD 框架使用 启动环境配置 安装Nuget包 App.xaml 配置 <Application x:Class&qu…...

C语言求 3*3 矩阵对角线之和

完整代码&#xff1a; // 求 3*3 矩阵对角线之和 #include<stdio.h>int main() {int n3;int arr[3][3];// 输入矩阵printf("请输入矩阵的元素:\n");for (int i 0; i < n; i){for (int j 0; j < n; j){scanf("%d", &arr[i][j]);}}int su…...

缓存分片中的哈希算法与一致性哈希算法

什么是缓存分片 在高并发场景下&#xff0c;缓存往往成为了瓶颈。这时候&#xff0c;我们可以通过缓存数据分片的方式来解决问题。所谓缓存数据分片&#xff0c;就是将缓存数据按照一定的规则分成多个片段&#xff0c;每个片段由不同的缓存节点负责。这样做有两个好处&#xf…...

线框图软件:Balsamiq Wireframes mac中文介绍

Balsamiq Wireframes mac是一款用于创建线框图的软件工具。它旨在帮助用户快速制作出清晰、简洁的界面原型&#xff0c;以便在设计和开发过程中进行协作和沟通。 Balsamiq Wireframes具有简单直观的用户界面&#xff0c;使用户能够快速添加和编辑各种用户界面元素&#xff0c;如…...

【wxWidgets实现透明wxPanel_核心实现_原创思想】

描述 wxWidgets 根本就没有实现过透明wxPanel容器,你设置wxTRANSPARENT_WINDOW,结果sorry 黑色,哈哈哈哈, 就是和你作对.想想当下那么漂亮的桌面, 背景, 透明, 特效.哎 悲哀啊,实现不了,就那死板的界面特性. 网上找了好久,也是乱七八糟,改底层代码还是算了吧,升级特要命.都是只…...

重大技术问题,iPhone 15 Pro Max面临“烧屏门”风波 | 百能云芯

近期&#xff0c;社交媒体平台上陆续涌现大量用户和数码博主就iPhone 15 Pro Max出现烧屏问题的投诉与评论。 烧屏问题是OLED屏幕常见的一个缺陷&#xff0c;这是由OLED屏幕发光机制引发的&#xff0c;OLED屏幕可视为由无数微小的灯泡-像素点构成&#xff0c;这些像素点可以独立…...

深度学习中的不确定性综述

领域学者&#xff1a; http://www.gatsby.ucl.ac.uk/~balaji/ 论文标题&#xff1a; A Survey of Uncertainty in Deep Neural Networks 论文链接&#xff1a; https://arxiv.org/pdf/2107.03342.pdf 概要 在过去的十年中&#xff0c;神经网络几乎遍及所有科学领域&#x…...

uni-app 小宠物 - 会说话的小鸟

在 template 中 <view class"container"><view class"external-shape"><view class"face-box"><view class"eye-box eye-left"><view class"eyeball-box eyeball-left"><span class"…...

POJ 3470 Walls 树上分桶

今天太晚了&#xff0c;代码先发上&#xff0c;思路明天说吧。 陌上花开&#xff0c;树上分桶 #include <iostream> #include <algorithm> #include <vector> using namespace std; /*** 对于y1不等于y2的&#xff0c;可以用datC求解&#xff0c;对于x1不等…...

CTF show Web 红包题第六弹

提示 1.不是SQL注入 2.需要找关键源码 思路 进入页面发现是一个登录框&#xff0c;很难让人不联想到SQL注入&#xff0c;但提示都说了不是SQL注入&#xff0c;所以就不往这方面想了 ​ 先查看一下网页源码&#xff0c;发现一段JavaScript代码&#xff0c;有一个关键类ctfs…...

2025年能源电力系统与流体力学国际会议 (EPSFD 2025)

2025年能源电力系统与流体力学国际会议&#xff08;EPSFD 2025&#xff09;将于本年度在美丽的杭州盛大召开。作为全球能源、电力系统以及流体力学领域的顶级盛会&#xff0c;EPSFD 2025旨在为来自世界各地的科学家、工程师和研究人员提供一个展示最新研究成果、分享实践经验及…...

【服务器压力测试】本地PC电脑作为服务器运行时出现卡顿和资源紧张(Windows/Linux)

要让本地PC电脑作为服务器运行时出现卡顿和资源紧张的情况&#xff0c;可以通过以下几种方式模拟或触发&#xff1a; 1. 增加CPU负载 运行大量计算密集型任务&#xff0c;例如&#xff1a; 使用多线程循环执行复杂计算&#xff08;如数学运算、加密解密等&#xff09;。运行图…...

NFT模式:数字资产确权与链游经济系统构建

NFT模式&#xff1a;数字资产确权与链游经济系统构建 ——从技术架构到可持续生态的范式革命 一、确权技术革新&#xff1a;构建可信数字资产基石 1. 区块链底层架构的进化 跨链互操作协议&#xff1a;基于LayerZero协议实现以太坊、Solana等公链资产互通&#xff0c;通过零知…...

css3笔记 (1) 自用

outline: none 用于移除元素获得焦点时默认的轮廓线 broder:0 用于移除边框 font-size&#xff1a;0 用于设置字体不显示 list-style: none 消除<li> 标签默认样式 margin: xx auto 版心居中 width:100% 通栏 vertical-align 作用于行内元素 / 表格单元格&#xff…...

CMake控制VS2022项目文件分组

我们可以通过 CMake 控制源文件的组织结构,使它们在 VS 解决方案资源管理器中以“组”(Filter)的形式进行分类展示。 🎯 目标 通过 CMake 脚本将 .cpp、.h 等源文件分组显示在 Visual Studio 2022 的解决方案资源管理器中。 ✅ 支持的方法汇总(共4种) 方法描述是否推荐…...

GC1808高性能24位立体声音频ADC芯片解析

1. 芯片概述 GC1808是一款24位立体声音频模数转换器&#xff08;ADC&#xff09;&#xff0c;支持8kHz~96kHz采样率&#xff0c;集成Δ-Σ调制器、数字抗混叠滤波器和高通滤波器&#xff0c;适用于高保真音频采集场景。 2. 核心特性 高精度&#xff1a;24位分辨率&#xff0c…...

Kafka入门-生产者

生产者 生产者发送流程&#xff1a; 延迟时间为0ms时&#xff0c;也就意味着每当有数据就会直接发送 异步发送API 异步发送和同步发送的不同在于&#xff1a;异步发送不需要等待结果&#xff0c;同步发送必须等待结果才能进行下一步发送。 普通异步发送 首先导入所需的k…...

腾讯云V3签名

想要接入腾讯云的Api&#xff0c;必然先按其文档计算出所要求的签名。 之前也调用过腾讯云的接口&#xff0c;但总是卡在签名这一步&#xff0c;最后放弃选择SDK&#xff0c;这次终于自己代码实现。 可能腾讯云翻新了接口文档&#xff0c;现在阅读起来&#xff0c;清晰了很多&…...

day36-多路IO复用

一、基本概念 &#xff08;服务器多客户端模型&#xff09; 定义&#xff1a;单线程或单进程同时监测若干个文件描述符是否可以执行IO操作的能力 作用&#xff1a;应用程序通常需要处理来自多条事件流中的事件&#xff0c;比如我现在用的电脑&#xff0c;需要同时处理键盘鼠标…...