MySQL 外连接和内连接的查询优化怎么做?
目录
1. 表连接方式的分类和需要注意的细节
2. 表连接时底层做了什么事?
3. 左外连接优化方案
4. 内连接优化方案
1. 表连接方式的分类和需要注意的细节
多表连接查询,大体上可以分为内连接与外连接。
内连接的意思就是把两个表有关联的部分都取出来,不分主表和次表,在连接时从我们的角度来说是不分谁是驱动表谁是被驱动表,但 MySQL 的查询优化器底层会做一个初步计算,计算出谁作为驱动表效率更高;
外连接则又分为左外连接,右外连接,满外连接(全外连接)。左外连接和右外连接中是分主表和此表的,所以本篇文章中点来说左外连接与右外连接的优化策略。
左外连接中左表是主表,也就是驱动表,右表作为从表,也就是被驱动表;语法为"表A LEFT JOIN 表B ON 查询条件";
右外连接中右表是主表,作为驱动表,右表是从表,作为被驱动表;语法为"表A RIGHT JOIN 表B ON 查询条件";
2. 表连接时底层做了什么事?
在没有任何索引的情况下,当两张表在进行连接查询操作的时候,实际上底层做的第一件事就是拿出驱动表一条记录与被驱动表的所有记录去做匹配,看是否满足条件,匹配完毕之后,再拿出驱动表的第二条记录与被驱动表的所有记录去做匹配,看是否有满足条件的记录,以此类推,直到驱动表所有记录均与被驱动表的所有记录进行过匹配并得出了满足条件的结果,两张表的连接查询的第一步连接操作就完成了。大致过程如下图所示
3. 左外连接优化方案
(1)小表驱动大表
通过上面表连接时进行匹配可以看出,两个表在进行连接查询时,驱动次数与驱动表的数据量有关,A表的数据量越少,与B表进行驱动的次数越少,所以第一种优化左外连接的方法就是将数据量小的表作为驱动表,让数据量小的表去与数据量大的表进行匹配,就可以减少驱动次数;
(2)给被驱动表的匹配字段添加索引
如下是 employee 员工表和 department 部门表,部门表的主键 部门id
SELECT e.employee_id,d.department_id
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
驱动表与被驱动表进行匹配时,连接条件是 查询条件字段 = 某个值。本例子中就是 员工表中的部门 id = 部门表中的部门 id。
没有给被驱动表的匹配字段添加索引:取出驱动表的一条数据,确定查询字段为 id = 12,那么与被驱动表匹配查询时,就是挨个将被驱动表中的每条数据取出做判断,看被驱动表的 id 是否为12,这其实就是一个全表扫描的过程。假设 employee 表中有20条数据,deparement 部门表中有30条数据,最坏的情况需要匹配 20 * 30 = 600 次,最好的情况是每次取出被驱动表第一条数据恰好能匹配到,只需要匹配20次。
用时间复杂度来表示就是驱动表时间复杂度O(n) * 被驱动表时间复杂度O(n) = O(n^2);索引没有索引时两表连接查询的时间复杂度为O(n^2),三表连接查询就是O(n^3),随着表数量增多,时间复杂度指数级增长。
给被驱动表的匹配字段添加了索引:接着刚才的说,如果被驱动表中给 id 字段添加了索引,那么被驱动表就可以精准查找 id = 12 的这条记录是否存在,查询不到则说明没有返回空,这样就不需要对被驱动表做全表扫描,省去了大量的时间。这里被驱动表的查询字段如果是主键索引,那么B+树的叶子节点就存有完整的数据,如果被驱动表的查询字段是非主键索引,那么就需要进行一次徽标查询操作,每次驱动只需要在 department 部门被驱动表中进行一次查询,效率得到了很大的提升。
用时间复杂度来表示驱动表扫描数据时间复杂度O(n),被驱动表主键索引时时间复杂度为O(logn),非主键索引回表操作时间复杂度为2O(logn),因此有索引时时间复杂度为O(nlogn),比没有索引的O(n^2)降低了很多;
(3) 若有WHERE查询条件,给WHERE查询字段添加索引
假如说在两个表进行了多表连接查询之后,我们还需要进一步的做过滤,一般都会在WHERE中添加过滤条件,此时想要进一步提高SQL的执行效率,就可以给WHERE查询条件的字段上添加索引。如果不加索引就会向上面一样进行一个全表扫描,取出每一条表连接之后的数据与WHERE的查询条件做匹配,加了索引就可以精确判断,迅速过滤掉无用的数据,提高性能。
(4)表连接查询查询条件字段类型一定要保持一致
如果不保持一致,就算我们为被驱动表添加了索引,索引也是失效的,因为数据类型不一致,所以数据库底层就需要全表扫描将每条数据都拿出来,然后先进行数据类型的转换,再去做条件的匹配,因此一定要注意,多表连接查询查询条件的字段类型一定要保持一致,否则会因为隐式类型转换导致索引失效。
4. 内连接优化方案
首先需要明确的一点是,内连接查询表面上是不分主表和此表的,默认来说左边的表就是驱动表,右边的表就是被驱动表。但在执行查询之前,SQL优化器会从和判断查询字段是否存在索引和两张表那个表数据量较少,会选出一个数据量小的作为驱动表,选出查询字段有索引的作为被驱动表。
如下图,是我数据库中的 employee员工表和 department 部门表,
department 部门表中有27条数据,
department_id 是主键,有数据库默认生成的主键索引
employee 员工表中有107条数据;
在 employee 员工表中,我们给 department_id 外键添加普通索引
现在两个表中都有 department_id 的索引,
如下SQL语句,使用 explain 关键字显示查询计划,我们将 employee 写在坐标作为驱动表,将department 部门表作为被驱动表,看看 employee 会作为驱动表吗
EXPLAIN SELECT e.employee_id,d.department_id
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;
可以观察到,虽然SQL语句中将 employee 写在左边作为驱动表,但 explain 执行计划中显示实际执行SQL语句的时候会将 department 作为驱动表,为什么呢?
因为 department 表中只有27条数据,比employee 表的107条数据少得多,能大大减少驱动次数,提高效率,所以内连接查询中,基本上都是哪个数据量少哪个表作为驱动表。
因此在实际开发设计表的时候,如果需要涉及到多表查询,应该尽量在数据量多的表(被驱动表)中经常需要查询的字段上添加索引,可以大大降低表连接查询匹配消耗的时间。
相关文章:

MySQL 外连接和内连接的查询优化怎么做?
目录 1. 表连接方式的分类和需要注意的细节 2. 表连接时底层做了什么事? 3. 左外连接优化方案 4. 内连接优化方案 1. 表连接方式的分类和需要注意的细节 多表连接查询,大体上可以分为内连接与外连接。 内连接的意思就是把两个表有关联的部分都取出…...

Web自动化测试 —— cookie复用
一、cookie简介 cookie是一些数据,存储于用户电脑的文本文件中 当web服务器想浏览器发送web页面时,在链接关闭后,服务端不会记录用户信息 二、为什么要使用Cookie自动化登录 复用浏览器仍然在每次用例开始都需要人为介入若用例需要经常执行&…...

Java自学第1课:安装JDK+Eclipse
1 引言 在学习前,我想说一句,那就是为什么要学习Java。 每个人的出发点都不同,对于做信息化的工程技术人员来说,java不懂,就没法干项目。 尽管有c和matlab等基础,但java看起来与这些语言都不太一样。 做…...

zabbix6.4监控centos
1、关闭防火墙 setenforce 0 #关闭SELinux sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config #设置永久关闭SELinux systemctl stop firewalld.service #关闭防火墙 systemctl disable firewalld.service …...

炫云客户端信用额度如何修改?
现在炫云新注册用户信用额度是100元,但是有人觉得信用额度太高了,想修改信用额度,不知道炫云的信用额度如何修改,今天就教大家如何修改炫云的信用额度。炫云的信用额度在炫云官网和客户端都可以修改。 我们先来看炫云官网如何修改…...

windows jar包文件默认打开方式设置
1、管理员权限打开“注册表编辑器”; 2、定位到计算机\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\FileExts项下,找到.jar项,再选中UserChoice项,其中ProgId值为jarfile 3、定位到计算机\HKEY_CLAS…...

高并发和存储之间的关系是什么?
文章目录 🔊博主介绍🤖博主的简介📥博主的目标 🥤本文内容🍊 一、高并发对存储的压力🍊 二、存储的性能和可扩展性 📢总结 🔊博主介绍 📕我是廖志伟,一名Java…...

Antv/G2 图表坐标轴文字过长时添加省略号
// 格式化文字,超过长度添加省略号chart.axis(city, {label: {formatter: (text) > {// 字符太长添加省略号return text.length > 5 ? ${text.slice(0, 5)}... : text;}}})完整 demo: <!DOCTYPE html> <html lang"en"> &l…...

pycharm更改远程服务器地址
一、问题描述 在运行一些项目时,我们常需要在pycharm中连接远程服务器,但万一远程服务器的ip发生了变化,该如何修改呢?我们在file-settings-python interpreter中找到远程服务器,但是发现ip是灰色的,没有办…...

海康监控摄像机和录像机接入LiveMedia GB28181平台实现远程调取监控视频
海康威视各种型号监控摄像头或硬盘录像机(NVR/HVR)接入LiveMedia GB28181平台配置过程都非常简单明了,但有些细节需要注意,避免走弯路。 1、基本要求 (1) 网络要求 总体来说,只要监控设备和GB28181平台的网络是连通…...

一文全览各种 ES 查询在 Java 中的实现
2 词条查询 所谓词条查询,也就是ES不会对查询条件进行分词处理,只有当词条和查询字符串完全匹配时,才会被查询到。  2.1 等值查询-term 等值查询,即筛选出一个字段等于特定值的所有记录。  SQL&…...

Centralized Feature Pyramid for Object Detection解读
Centralized Feature Pyramid for Object Detection 问题 主流的特征金字塔集中于层间特征交互,而忽略了层内特征规则。尽管一些方法试图在注意力机制或视觉变换器的帮助下学习紧凑的层内特征表示,但它们忽略了对密集预测任务非常重要的被忽略的角点区…...

unity中meta文件GUID异常问题
错误信息: The .meta file Assets/Scripts/Editor/ConvertConfigToBinary/TxtConverter.cs.meta does not have a valid GUID and its corresponding Asset file will be ignored. If this file is not malformed, please add a GUID, or delete the .meta file and…...

【k8s】pod集群调度
调度约束 Kubernetes 是通过 List-Watch **** 的机制进行每个组件的协作,保持数据同步的,每个组件之间的设计实现了解耦。 用户是通过 kubectl 根据配置文件,向 APIServer 发送命令,在 Node 节点上面建立 Pod 和 Container。…...

MathType数学公式编辑器2024官方最新版
Mathtype是一款数学公式编辑器,它可以帮助我们在文档中插入各种复杂的数学公式,使得我们的文档更加专业、规范。在使用Mathtype工具时,我们可以采取以下几种方法: 1. 鼠标直接点击插入公式 打开Mathtype后,在需要插入公…...
Android照搬,可删
1private void initview() {myradioGroup (RadioGroup) this.findViewById(R.id.MainActivity_RadioGroup);//通过id找到UI中的单选按钮组 2res getResources();// 得到Resources对象,从而通过它获取存在系统的资源 icon_home_true res.getDrawable(R.mipmap.ic…...

2022最新版-李宏毅机器学习深度学习课程-P26 自注意力机制
一、应用情境 输入任意长度个向量进行处理。 从输入看 文字处理(自然语言处理) 将word表示为向量 one-hotword-embedding声音信号处理 每个时间窗口(Window, 25ms)视为帧(Frame),视为向量图 每个节点视为…...

【Docker】Linux路由连接两个不同网段namespace,连接namespace与主机
如果两个namespace处于不同的子网中,那么就不能通过bridge进行连接了,而是需要通过路由器进行三层转发。然而Linux并未像提供虚拟网桥一样也提供一个虚拟路由器设备,原因是Linux自身就具备有路由器功能。 路由器的工作原理是这样的ÿ…...
C语言 DAY10 内存分配
1.引入 int nums[10] {0}; //对 int len 10; int nums[len] {0}; //错 是因为系统的内存分配原则导致的 2.概述 在系统运行时,系统为了更好的管理进程中的内存,所以将内存进行了分配,其分配的机制就称为内存分配 1.静态分配原则 1.特点 1、在程序…...
SpringCloud Gateway 网关的请求体body的读取和修改
SpringCloud Gateway 网关的请求体body的读取和修改 getway需要多次对body 进行操作,需要对body 进行缓存 缓存body 动态多次获取 新建顶层filter,对body 进行缓存 import lombok.extern.slf4j.Slf4j; import org.springframework.cloud.gateway.filt…...

智慧医疗能源事业线深度画像分析(上)
引言 医疗行业作为现代社会的关键基础设施,其能源消耗与环境影响正日益受到关注。随着全球"双碳"目标的推进和可持续发展理念的深入,智慧医疗能源事业线应运而生,致力于通过创新技术与管理方案,重构医疗领域的能源使用模式。这一事业线融合了能源管理、可持续发…...
Java 语言特性(面试系列1)
一、面向对象编程 1. 封装(Encapsulation) 定义:将数据(属性)和操作数据的方法绑定在一起,通过访问控制符(private、protected、public)隐藏内部实现细节。示例: public …...
Cesium1.95中高性能加载1500个点
一、基本方式: 图标使用.png比.svg性能要好 <template><div id"cesiumContainer"></div><div class"toolbar"><button id"resetButton">重新生成点</button><span id"countDisplay&qu…...

python/java环境配置
环境变量放一起 python: 1.首先下载Python Python下载地址:Download Python | Python.org downloads ---windows -- 64 2.安装Python 下面两个,然后自定义,全选 可以把前4个选上 3.环境配置 1)搜高级系统设置 2…...

HBuilderX安装(uni-app和小程序开发)
下载HBuilderX 访问官方网站:https://www.dcloud.io/hbuilderx.html 根据您的操作系统选择合适版本: Windows版(推荐下载标准版) Windows系统安装步骤 运行安装程序: 双击下载的.exe安装文件 如果出现安全提示&…...
动态 Web 开发技术入门篇
一、HTTP 协议核心 1.1 HTTP 基础 协议全称 :HyperText Transfer Protocol(超文本传输协议) 默认端口 :HTTP 使用 80 端口,HTTPS 使用 443 端口。 请求方法 : GET :用于获取资源,…...

Netty从入门到进阶(二)
二、Netty入门 1. 概述 1.1 Netty是什么 Netty is an asynchronous event-driven network application framework for rapid development of maintainable high performance protocol servers & clients. Netty是一个异步的、基于事件驱动的网络应用框架,用于…...
【无标题】路径问题的革命性重构:基于二维拓扑收缩色动力学模型的零点隧穿理论
路径问题的革命性重构:基于二维拓扑收缩色动力学模型的零点隧穿理论 一、传统路径模型的根本缺陷 在经典正方形路径问题中(图1): mermaid graph LR A((A)) --- B((B)) B --- C((C)) C --- D((D)) D --- A A -.- C[无直接路径] B -…...

云原生安全实战:API网关Kong的鉴权与限流详解
🔥「炎码工坊」技术弹药已装填! 点击关注 → 解锁工业级干货【工具实测|项目避坑|源码燃烧指南】 一、基础概念 1. API网关(API Gateway) API网关是微服务架构中的核心组件,负责统一管理所有API的流量入口。它像一座…...
Web中间件--tomcat学习
Web中间件–tomcat Java虚拟机详解 什么是JAVA虚拟机 Java虚拟机是一个抽象的计算机,它可以执行Java字节码。Java虚拟机是Java平台的一部分,Java平台由Java语言、Java API和Java虚拟机组成。Java虚拟机的主要作用是将Java字节码转换为机器代码&#x…...