mysql其它补充

exist和in的区别

exists 用于对外表记录做筛选。 exists 会遍历外表，将外查询表的每一行，代入内查询进行判断。

当 exists 里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果 exists 里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。

select a.* from A a where exists(select 1 from B b where a.id=b.id)

in 是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。

select * from A where id in(select id from B)

总结

子查询的表比较大的时候，使用 exists 可以有效减少总的循环次数来提升速度；

当外查询的表比较大的时候，使用 in 可以有效减少对外查询表循环遍历来提升速度。

Join 的算法（NLJ、BNL、BKA）

Nested-Loop Join

在NLJ算法中，MySQL首先选择一个表（通常是小型表）作为驱动表，并迭代该表中的每一行。然后，MySQL在第二个表中搜索匹配条件的行，这个搜索过程通常使用索引来完成。一旦找到匹配的行，MySQL将这些行组合在一起，并将它们作为结果集返回。

工作流程如图：

例如，下面这个语句：

select * from t1 straight_join t2 on (t1.a=t2.a);

在这个语句里，假设t1 是驱动表，t2是被驱动表。我们来看一下这条语句的explain结果。

可以看到，在这条语句里，被驱动表t2的字段a上有索引，join过程用上了这个索引，因此这个语句的执行流程是这样的：

  1. 从表t1中读入一行数据 R；
  2. 从数据行R中，取出a字段到表t2里去查找；
  3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
  4. 重复执行步骤1到3，直到表t1的末尾循环结束。

这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称NLJ。

Nested-Loop Join

NLJ是使用上了索引的情况，如果查询条件没有使用到索引MySQL会选择使用另一个叫作“Block Nested-Loop Join”的算法，简称BNL。

Block Nested Loop Join（BNL）算法与NLJ算法不同的是，BNL算法使用一个类似于缓存的机制，将表数据分成多个块，然后逐个处理这些块，以减少内存和CPU的消耗。

例如，下面这个语句：

select * from t1 straight_join t2 on (t1.a=t2.b);

字段b上是没有建立索引的。

这时候，被驱动表上没有可用的索引，算法的流程是这样的：

  1. 把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
  2. 扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

这条SQL语句的explain结果如下所示：

可以看到，在这个过程中，对表t1和t2都做了一次全表扫描，因此总的扫描行数是1100。由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是：100*1000=10万次。

虽然Block Nested-Loop Join算法是全表扫描。但是是在内存中进行的判断操作，速度上会快很多。但是性能仍然不如NLJ。

join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表t1的所有数据话，策略很简单，就是分段放。

  1. 顺序读取数据行放入join_buffer中，直到join_buffer满了。
  2. 扫描被驱动表跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
  3. 清空join_buffer，重复上述步骤。

虽然分成多次放入join_buffer，但是判断等值条件的次数还是不变的，依然是10万次。

MRR & BKA

MySQL在5.6版本后引入了Batched Key Acess(BKA)算法了。这个BKA算法，其实就是对NLJ算法的优化，BKA算法正是基于MRR。

NLJ算法执行的逻辑是：从驱动表t1，一行行地取出a的值，再到被驱动表t2去做join。也就是说，对于表t2来说，每次都是匹配一个值。这时，MRR的优势就用不上了。

我们可以从表t1里一次性地多拿些行出来，，先放到一个临时内存，一起传给表t2。这个临时内存不是别人，就是join_buffer。

通过上一篇文章，我们知道join_buffer 在BNL算法里的作用，是暂存驱动表的数据。但是在NLJ算法里并没有用。那么，我们刚好就可以复用join_buffer到BKA算法中。

NLJ算法优化后的BKA算法的流程，如图所示：

图中，我在join_buffer中放入的数据是P1~P100，表示的是只会取查询需要的字段。当然，如果join buffer放不下P1~P100的所有数据，就会把这100行数据分成多段执行上图的流程。

如果要使用BKA优化算法的话，你需要在执行SQL语句之前，先设置

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

其中，前两个参数的作用是要启用MRR。这么做的原因是，BKA算法的优化要依赖于MRR。

对于BNL，我们可以通过建立索引转为BKA。对于一些列建立索引代价太大，不好建立索引的情况，我们可以使用临时表去优化。

例如，对于这个语句：

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

使用临时表的大致思路是：

  1. 把表t2中满足条件的数据放在临时表tmp_t中；
  2. 为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；
  3. 让表t1和tmp_t做join操作。

这样可以大大减少扫描的行数，提升性能。

join查询为什么要小表驱动大表

mysql的join实现原理是，以驱动表的数据为基础，“嵌套循环”去被驱动表匹配记录，

select * from a join b on a.bid = b.id

假设 a表10000数据，b表20数据

这里有2个过程，b 表数据最少，查询引擎优化选择b为驱动表，

  • 循环b表的20条数据，
  • 去a表的10000数据去匹配，这个匹配的过程是B+树的查找过程，比循环取数要快的多。

小驱动的方式

for 20条数据 
    匹配10000数据（根据on a.bid=b.id的连接条件，进行B+树查找）

查找次数 20+ log10000

如果使用大表驱动，则查找过程是这样的

for 10000条数据 
    匹配20条数据（根据on a.bid=b.id的连接条件，进行B+树查找）

查找次数 10000+ log20

可以看出来

小表驱动大表：20+ log10000

大表驱动小表：10000+ log20

显然小表驱动大表查询效率要高很多。

参考：

https://www.cnblogs.com/booksea/p/17380941.html

https://www.cnblogs.com/hellotin/p/14227664.html

MySQL单个实例buffer数据和磁盘数据是如何保证强一致性的

InnoDB的页大小默认为16K，可以使用参数innodb_page_size设置， 可设置的值有： 64KB，

32KB，16KB（默认），8KB和4KB。并且在数据校验时也针对页进行计算，即他们是一个整个对待，包括把数据持久化到磁盘的操作。而计算机的硬件和操作系统在极端情况下（比如断电、系统崩溃）时，刚写入了4K或8K数据，那么就不能保证该操作的原子性，称为部分页面写问题（Partial Write Page）。

此时就引入了双写缓存区的机制，当发生极端情况时，可以从系统表空间的Double Write Buffer【磁盘上】进行恢复，下面是InnoDB的架构图、双写和恢复流程图。为了方便对比，将组件放在了相同的位置：


 

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/593476.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

使用python开发的词云图生成器2.0

使用python开发的词云图生成器2.0

使用python开发的词云图生成器2.0 更新部分词云图主要三方库工具介绍和效果工具界面&#xff1a; 代码 更新部分 1.支持选择字体&#xff1b; 2.支持选择词云图形状 词云图 词云图啊&#xff0c;简单来说&#xff0c;它可以把文本数据中的高频关键词变成不同大小、颜色的词汇…
阅读更多...
一篇文章带你深入了解“指针”

一篇文章带你深入了解“指针”

一篇文章带你深入了解“指针” 内存和地址了解指针指针类型const修饰指针指针的运算指针与整数之间的运算指针与指针之间的运算指针的关系运算 void* 指针传值调用和传址调用数组和指针的关系野指针野指针的形成原因规避野指针 二级指针字符指针指针数组数组指针数组传参一维数…
阅读更多...
(三)JVM实战——对象的内存布局与执行引擎详解

(三)JVM实战——对象的内存布局与执行引擎详解

对象的内存布局 对象的实例化 对象的创建方式 - new的方式 - Class的newInstance():反射的方式 - Construct的newInstance() - clone:实现Cloneable接口,默认浅拷贝 - 使用反序列化&#xff1a;将二进制流转化为内存对象 创建对象的步骤 - 判断对象对应的类是否加载、链接、初…
阅读更多...
基础I/O--文件系统

基础I/O--文件系统

文章目录 回顾C文件接口初步理解文件理解文件使用和并认识系统调用open概述标记位传参理解返回值 closewriteread总结 文件描述符fd0&1&2理解 回顾C文件接口 C代码&#xff1a; #include<stdio.h> int main() { FILE *fpfopen("log.txt",&…
阅读更多...
08 - 步骤 表输出

08 - 步骤 表输出

简介 表输出&#xff08;Table Output&#xff09;步骤是用于将 Kettle 中的数据写入关系型数据库表的步骤。它允许用户将数据流中的数据插入、更新或删除到目标数据库表中。 使用 场景 我要将处理完的数据流中的sysOrgCode 跟 plateNumber 保存记录到mysql 1、拖拽表输出…
阅读更多...
2.VAM新建保存修改场景文件

2.VAM新建保存修改场景文件

新建场景 点击返回场景预览 打开游戏的时候&#xff0c;本身就有了一个新场景&#xff0c;因为现在场景里什么也没有&#xff0c;所以是一片黑暗 点击星号打开主菜单会返回主界面 做一个最简单的Helloworld场景 底下有两个模式&#xff0c;游玩模式和编辑模式 编辑场景的时候…
阅读更多...
OpenCV如何使用 GDAL 读取地理空间栅格文件(72)

OpenCV如何使用 GDAL 读取地理空间栅格文件(72)

返回:OpenCV系列文章目录&#xff08;持续更新中......&#xff09; 上一篇:OpenCV的周期性噪声去除滤波器(70) 下一篇 :OpenCV系列文章目录&#xff08;持续更新中......&#xff09; 目录 目标 代码&#xff1a; 解释&#xff1a; 如何使用 GDAL 读取栅格数据 注意 …
阅读更多...
05_G1垃圾收集器

05_G1垃圾收集器

G1垃圾收集器简介 垃圾优先 Garbage-First&#xff08;G1&#xff09;垃圾收集器面向多处理器机器&#xff0c;适用于大内存场景。它尝试在无需太多配置的情况下实现垃圾收集暂停时间目标&#xff0c;并同时实现高吞吐量。G1旨在通过适用于当前目标应用和环境的功能&#xff0…
阅读更多...
go mod

go mod

常用命令 初始化模块 go mod init 模块名下载 go.mod 文件中指明的所有依赖 go mod download github.com/gin-gonic/ginv1.9.(依赖路径)依赖对其&#xff08;使引用的都是所依赖的&#xff09; go mod tidy编辑go.mod go mod edit go mod edit -require"github.com/g…
阅读更多...
记录几种排序算法

记录几种排序算法

十种常见排序算法可以分类两大类别&#xff1a;比较类排序和非比较类排序。 常见的快速排序、归并排序、堆排序以及冒泡排序等都属于比较类排序算法。比较类排序是通过比较来决定元素间的相对次序&#xff0c;其时间复杂度不能突破 O(nlogn)。在冒泡排序之类的排序中&…
阅读更多...
数据结构---时间复杂度+空间复杂度

数据结构---时间复杂度+空间复杂度

算法(algorithm)简单说就是解决问题的方法。方法有好坏&#xff0c;同样算法也是&#xff0c;有效率高的算法&#xff0c;也有效率低的算法。衡量算法的好坏一般从时间和空间两个维度衡量&#xff0c;也就是本文要介绍的时间复杂度和空间复杂度。有些时候&#xff0c;时间与空间…
阅读更多...
js api part3

js api part3

环境对象 环境对象&#xff1a; 指的是函数内部特殊的 变量 this &#xff0c; 它代表着当前函数运行时所处的环境 作用&#xff1a; 弄清楚this的指向&#xff0c;可以让我们代码更简洁 函数的调用方式不同&#xff0c;this 指代的对象也不同 【谁调用&#xff0c; this 就是…
阅读更多...
springboot模块以及非springboot模块构成的多模块maven项目最佳构建方式

springboot模块以及非springboot模块构成的多模块maven项目最佳构建方式

文章目录 背景一般的实现使用spring-boot-dependencies 更优雅的实现. 背景 有时候构建一个多模块maven项目其中某一个模块是web-service需要使用spring boot,其他模块跟spring boot 完全无关,本文总结一下在这个场景下maven项目最佳构建方式. 一般的实现 网上应该也看到过很…
阅读更多...
智能工业相机哪家好？

智能工业相机哪家好？

一、什么是智能工业相机 在工业自动化的浪潮中&#xff0c;智能工业相机扮演着至关重要的角色。它们如同工业领域的“眼睛”&#xff0c;为生产过程提供精准的视觉监测和数据采集。然而&#xff0c;面对众多的智能工业相机品牌&#xff0c;如何选择一款真正适合的产品成为了众多…
阅读更多...
企业开发基础--数据库

企业开发基础--数据库

今天完成了数据库学习的全部内容&#xff0c;在事务&#xff0c;索引&#xff0c;范式中要有个人逻辑上的理解&#xff0c;也算是卡着点完成了大多数预期&#xff0c;还有一个Java游戏未完成&#xff0c;会后续补上。 之后的一周要完成34道数据库练习题以及JDBC&#xff0c;学…
阅读更多...
88、动态规划-乘积最大子数组

88、动态规划-乘积最大子数组

思路&#xff1a; 首先使用递归来解&#xff0c;从0开始到N&#xff0c;每次都从index开始到N的求出最大值。然后再次递归index1到N的最大值&#xff0c;再求max。代码如下&#xff1a; // 方法一&#xff1a;使用递归方式找出最大乘积public static int maxProduct(int[] num…
阅读更多...
Graph RAG：基于知识图谱的检索增强技术与优势对比

Graph RAG：基于知识图谱的检索增强技术与优势对比

身处信息爆炸时代&#xff0c;如何从海量信息中获取准确全面的搜索结果&#xff0c;并以更直观、可读的方式呈现出来是大家期待达成的目标。传统的搜索增强技术受限于训练文本数量、质量等问题&#xff0c;对于复杂或多义词查询效果不佳&#xff0c;更无法满足 ChatGPT 等大语言…
阅读更多...
【Linux】进程间通信 - 管道

【Linux】进程间通信 - 管道

文章目录 1. 进程间通信介绍1.1 进程间通信目的1.2 进程间通信发展1.3 进程间通信分类 2. 管道2.1 什么是管道2.2 匿名管道2.3 用 fork 来共享管道原理2.4 站在文件描述符角度 - 深入理解管道2.5 站在内核角度 - 管道本质2.6 管道读写规则2.7 管道特点 3. 命名管道3.1 匿名管道…
阅读更多...
C语言实战项目--贪吃蛇

C语言实战项目--贪吃蛇

贪吃蛇是久负盛名的游戏之一&#xff0c;它也和俄罗斯⽅块&#xff0c;扫雷等游戏位列经典游戏的行列。在编程语言的教学中&#xff0c;我们以贪吃蛇为例&#xff0c;从设计到代码实现来提升大家的编程能⼒和逻辑能⼒。 在本篇讲解中&#xff0c;我们会看到很多陌生的知识&…
阅读更多...
牛角源码 | 【独立版】商城盲盒源码带uniapp（H5+小程序+APP三端）全开源

牛角源码 | 【独立版】商城盲盒源码带uniapp（H5+小程序+APP三端）全开源

前端uniapp开源代码&#xff0c;可用HBuilder工具无限发行H5、小程序和打包app&#xff0c;后端PHP开源源码&#xff0c;支持二开。 内有安装搭建教程&#xff0c;轻松部署&#xff0c;搭建即可运营&#xff0c;内置永久免费更新地址&#xff0c;后续无忧升级。 下载地址&…
阅读更多...
最新文章

Copyright @ 2022~2023