当前位置: 首页 > 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不等…...

脑机新手指南(八):OpenBCI_GUI:从环境搭建到数据可视化(下)

一、数据处理与分析实战 &#xff08;一&#xff09;实时滤波与参数调整 基础滤波操作 60Hz 工频滤波&#xff1a;勾选界面右侧 “60Hz” 复选框&#xff0c;可有效抑制电网干扰&#xff08;适用于北美地区&#xff0c;欧洲用户可调整为 50Hz&#xff09;。 平滑处理&…...

《Qt C++ 与 OpenCV:解锁视频播放程序设计的奥秘》

引言:探索视频播放程序设计之旅 在当今数字化时代,多媒体应用已渗透到我们生活的方方面面,从日常的视频娱乐到专业的视频监控、视频会议系统,视频播放程序作为多媒体应用的核心组成部分,扮演着至关重要的角色。无论是在个人电脑、移动设备还是智能电视等平台上,用户都期望…...

AtCoder 第409​场初级竞赛 A~E题解

A Conflict 【题目链接】 原题链接&#xff1a;A - Conflict 【考点】 枚举 【题目大意】 找到是否有两人都想要的物品。 【解析】 遍历两端字符串&#xff0c;只有在同时为 o 时输出 Yes 并结束程序&#xff0c;否则输出 No。 【难度】 GESP三级 【代码参考】 #i…...

关于iview组件中使用 table , 绑定序号分页后序号从1开始的解决方案

问题描述&#xff1a;iview使用table 中type: "index",分页之后 &#xff0c;索引还是从1开始&#xff0c;试过绑定后台返回数据的id, 这种方法可行&#xff0c;就是后台返回数据的每个页面id都不完全是按照从1开始的升序&#xff0c;因此百度了下&#xff0c;找到了…...

Java-41 深入浅出 Spring - 声明式事务的支持 事务配置 XML模式 XML+注解模式

点一下关注吧&#xff01;&#xff01;&#xff01;非常感谢&#xff01;&#xff01;持续更新&#xff01;&#xff01;&#xff01; &#x1f680; AI篇持续更新中&#xff01;&#xff08;长期更新&#xff09; 目前2025年06月05日更新到&#xff1a; AI炼丹日志-28 - Aud…...

MySQL用户和授权

开放MySQL白名单 可以通过iptables-save命令确认对应客户端ip是否可以访问MySQL服务&#xff1a; test: # iptables-save | grep 3306 -A mp_srv_whitelist -s 172.16.14.102/32 -p tcp -m tcp --dport 3306 -j ACCEPT -A mp_srv_whitelist -s 172.16.4.16/32 -p tcp -m tcp -…...

力扣-35.搜索插入位置

题目描述 给定一个排序数组和一个目标值&#xff0c;在数组中找到目标值&#xff0c;并返回其索引。如果目标值不存在于数组中&#xff0c;返回它将会被按顺序插入的位置。 请必须使用时间复杂度为 O(log n) 的算法。 class Solution {public int searchInsert(int[] nums, …...

蓝桥杯 冶炼金属

原题目链接 &#x1f527; 冶炼金属转换率推测题解 &#x1f4dc; 原题描述 小蓝有一个神奇的炉子用于将普通金属 O O O 冶炼成为一种特殊金属 X X X。这个炉子有一个属性叫转换率 V V V&#xff0c;是一个正整数&#xff0c;表示每 V V V 个普通金属 O O O 可以冶炼出 …...

VM虚拟机网络配置(ubuntu24桥接模式):配置静态IP

编辑-虚拟网络编辑器-更改设置 选择桥接模式&#xff0c;然后找到相应的网卡&#xff08;可以查看自己本机的网络连接&#xff09; windows连接的网络点击查看属性 编辑虚拟机设置更改网络配置&#xff0c;选择刚才配置的桥接模式 静态ip设置&#xff1a; 我用的ubuntu24桌…...

tomcat入门

1 tomcat 是什么 apache开发的web服务器可以为java web程序提供运行环境tomcat是一款高效&#xff0c;稳定&#xff0c;易于使用的web服务器tomcathttp服务器Servlet服务器 2 tomcat 目录介绍 -bin #存放tomcat的脚本 -conf #存放tomcat的配置文件 ---catalina.policy #to…...