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

C++初阶-list的底层

目录 1.std::list实现的所有代码 2.list的简单介绍 2.1实现list的类 2.2_list_iterator的实现 2.2.1_list_iterator实现的原因和好处 2.2.2_list_iterator实现 2.3_list_node的实现 2.3.1. 避免递归的模板依赖 2.3.2. 内存布局一致性 2.3.3. 类型安全的替代方案 2.3.…...

循环冗余码校验CRC码 算法步骤+详细实例计算

通信过程&#xff1a;&#xff08;白话解释&#xff09; 我们将原始待发送的消息称为 M M M&#xff0c;依据发送接收消息双方约定的生成多项式 G ( x ) G(x) G(x)&#xff08;意思就是 G &#xff08; x ) G&#xff08;x) G&#xff08;x) 是已知的&#xff09;&#xff0…...

为什么需要建设工程项目管理?工程项目管理有哪些亮点功能?

在建筑行业&#xff0c;项目管理的重要性不言而喻。随着工程规模的扩大、技术复杂度的提升&#xff0c;传统的管理模式已经难以满足现代工程的需求。过去&#xff0c;许多企业依赖手工记录、口头沟通和分散的信息管理&#xff0c;导致效率低下、成本失控、风险频发。例如&#…...

Cilium动手实验室: 精通之旅---20.Isovalent Enterprise for Cilium: Zero Trust Visibility

Cilium动手实验室: 精通之旅---20.Isovalent Enterprise for Cilium: Zero Trust Visibility 1. 实验室环境1.1 实验室环境1.2 小测试 2. The Endor System2.1 部署应用2.2 检查现有策略 3. Cilium 策略实体3.1 创建 allow-all 网络策略3.2 在 Hubble CLI 中验证网络策略源3.3 …...

抖音增长新引擎:品融电商,一站式全案代运营领跑者

抖音增长新引擎&#xff1a;品融电商&#xff0c;一站式全案代运营领跑者 在抖音这个日活超7亿的流量汪洋中&#xff0c;品牌如何破浪前行&#xff1f;自建团队成本高、效果难控&#xff1b;碎片化运营又难成合力——这正是许多企业面临的增长困局。品融电商以「抖音全案代运营…...

【快手拥抱开源】通过快手团队开源的 KwaiCoder-AutoThink-preview 解锁大语言模型的潜力

引言&#xff1a; 在人工智能快速发展的浪潮中&#xff0c;快手Kwaipilot团队推出的 KwaiCoder-AutoThink-preview 具有里程碑意义——这是首个公开的AutoThink大语言模型&#xff08;LLM&#xff09;。该模型代表着该领域的重大突破&#xff0c;通过独特方式融合思考与非思考…...

spring:实例工厂方法获取bean

spring处理使用静态工厂方法获取bean实例&#xff0c;也可以通过实例工厂方法获取bean实例。 实例工厂方法步骤如下&#xff1a; 定义实例工厂类&#xff08;Java代码&#xff09;&#xff0c;定义实例工厂&#xff08;xml&#xff09;&#xff0c;定义调用实例工厂&#xff…...

【电力电子】基于STM32F103C8T6单片机双极性SPWM逆变(硬件篇)

本项目是基于 STM32F103C8T6 微控制器的 SPWM(正弦脉宽调制)电源模块,能够生成可调频率和幅值的正弦波交流电源输出。该项目适用于逆变器、UPS电源、变频器等应用场景。 供电电源 输入电压采集 上图为本设计的电源电路,图中 D1 为二极管, 其目的是防止正负极电源反接, …...

uniapp 开发ios, xcode 提交app store connect 和 testflight内测

uniapp 中配置 配置manifest 文档&#xff1a;manifest.json 应用配置 | uni-app官网 hbuilderx中本地打包 下载IOS最新SDK 开发环境 | uni小程序SDK hbulderx 版本号&#xff1a;4.66 对应的sdk版本 4.66 两者必须一致 本地打包的资源导入到SDK 导入资源 | uni小程序SDK …...

永磁同步电机无速度算法--基于卡尔曼滤波器的滑模观测器

一、原理介绍 传统滑模观测器采用如下结构&#xff1a; 传统SMO中LPF会带来相位延迟和幅值衰减&#xff0c;并且需要额外的相位补偿。 采用扩展卡尔曼滤波器代替常用低通滤波器(LPF)&#xff0c;可以去除高次谐波&#xff0c;并且不用相位补偿就可以获得一个误差较小的转子位…...