mysql重学（一）mysql语句执行流程

思考

  1. 一条查询语句如何执行？
  2. mysql语句中若列不存在，则在哪个阶段报错
  3. 一条更新语句如何执行？
  4. redolog和binlog的区别？
  5. 为什么要引入WAL
  6. 什么是Changbuf？如何工作
  7. 写缓冲一定好吗？
  8. 什么情况会引发刷脏页
  9. 删除语句会造成什么后果？会改变磁盘文件大小吗
  10. 如何收缩空间

1.查询语句

关于一条mysql查询语句在mysql中的执行流程


image.png

如select name from test where id=10;

  1. 连接器---先与mysql服务端连接器建立连接，若查询缓存命中则直接返回 （查询缓存的弊端：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。还容易造成内存泄漏，8.0版本以后删除，是个被抛弃的功能）
查看查询缓存设置
show variables like 'query_cache%';
//my.cnf设置关闭查询缓存
query_cache_type=0

连接完成后，若长时间处于空闲状态，则会自动断开

查看当前mysql的连接状态
show processlist;
查看wait_timeout连接最长闲置时间
show variables like 'wait%';

mysql异常重启现象：使用长连接后，mysql内存可能涨的很快(mysql执行过程使用的内存管理在连接对象中，这些资源断开连接才会被释放)，长连接多了后，内存占用太大，被系统强行杀掉。因此我们需要考虑：1.定期断开长连接（比如对客户端的连接池中的连接设置一个过期时间<wait_timeout）；2.执行较大操作后，再执行mysql_reset_connection来初始化连接资源(5.7以上,当然这是c函数)

int mysql_reset_connection(MYSQL *mysql)
  1. 分析器---词法分析告诉服务端你要干什么（我要找 test表中id为10的名字） ，也就是解析客户端命令的token，生成一颗对应的解析树，然后分析器再进一步检查解析树的合法性，比如表和列是否存在等(其中sql语法错误在这块暴露)

  1. 优化器---服务端会思考该怎么执行最优(比如索引的选择、表的连接顺序)，生成执行计划。存储引擎的各种数据获取方法都是已经定好的静态方法，优化器能决定执行器选择存储引擎的哪个方法去获取数据

  2. 执行器---先检查用户对库对表的权限，再继续执行执行计划

执行器执行子语句的逻辑
1.from字句组装来自不同数据源的数据(先join再on)；
2.where字句基于指定条件对记录进行筛选
3.group by字句将数据划分为多个分组
4.使用聚合函数进行计算
5.使用having字句筛选分组

6.计算所有表达式
7.select的字段
8.使用order by排序结果集
以上每一个步骤都会产生一个虚拟表该虚拟表被用作下一步的输入，只有最后一个表才会返回给调用者

执行器的查询数据逻辑
select * from T where ID=10;
比如我们这个例子中的表 T (innodb引擎)中，ID 字段没有索引（有索引就直接跳到那行），那么执行器的执行流程是这样的：
1.调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。至此，这个语句就执行完成了。 也就是说没有索引的话，会进行全表扫描 慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

  1. 存储引擎--存储数据，提供读写接口，对于执行器来说，是个黑箱

1.2源码角度

源码分析sql执行过程

image.png

2.更新语句

以update a set name=1 where id=1;
主要区别在于在查询到数据之后(select name from a where id=1)，如果是innodb引擎它会进行日志的两阶段提交（WAL技术Write-Ahead Logging先写日志再写磁盘）:

  1. 开启事务，写入redolog（innodb引擎特有），并更新内存
  2. 写入binlog，提交事务，commit
update T set c=c+1 where ID=2;
image.png

需要注意的是上方提到的先写日志也是先写磁盘，只是写日志是顺序写，这也就引出的redolog和binlog的区别:

  1. redolog是Innodb引擎独有的，文件是固定大小的(默认情况下，ib_logfile0 and ib_logfile1两个文件表示)，是循环写的，写满了从头写(记录了某个数据页上做了什么修改,是innodb独有的。)。这种不停的在日志文件末尾追加日志，是磁盘顺序读写，磁盘顺序读写性能很高。
mysqld查看redolog文件大小
 show variables like 'innodb_log_file_size'
my.cnf设置文件大小
innodb_log_file_size=xxxxx
innodb_log_files_in_group

默认在/var/lib/mysql文件夹下


image.png
  1. binlog 是追加写的，写满了再新建文件接着写。
my.cnf设置binlog过期时间，过期删除
expire_logs_days = 5
开启binlog日志
log-bin=mysql-bin

2.1WAL的设计理念来源

关于WAL的设计(摘抄自别人评论，觉得挺有道理)
并不是仅仅为了提升IO性能才设计的WAL。如果仅仅是为了提升性能，那为了WAL所采取的一系列措施也太得不偿失了。 WAL的出现是为了实现关系型数据库的原子性和持久性。实现原子性和持久性的最大困难是“写入磁盘”这个操作并不是原子性的，不仅有“写入”与“未写入”状态，还客观存在“正在写”的中间状态。 由于写入中间状态与崩溃都不可能消除，所以如果不做额外保障的话，将内存中的数据写入磁盘，并不能保证原子性与持久性。所以可能出现以下情形： 1：未提交事务，写入后崩溃（比如修改三个数据，程序还没修改完，但数据库已经将其中一个或两个数据的变动写入磁盘，此时出现崩溃） 2：已提交事务，写入前崩溃（程序已经修改完三个数据，但数据库还未将全部三个数据的变动都写入磁盘，此时出现崩溃） 由于写入中间状态与崩溃都是无法避免的，为了保证原子性和持久性，只能在崩溃恢复后采取补救措施，这种能力就被称为“崩溃恢复”。 为了能够实现崩溃恢复，采取了写日志的方式，写日志成功后再去写磁盘，这种事务实现方式被称为“提交日志（CommitLogging），目前阿里的OceanBase就采用这种方式，但是Commit Logging存在一个巨大缺陷：所有对数据的真实修改都必须发生在事务提交之后，即成功写入日志之后。在此之前，即使磁盘IO有足够的空闲，即使某个事务修改的数据量非常庞大，占用了大量的内存缓冲区，都不允许在事务提交前写入磁盘，因此这种方式对数据库性能的提升十分不利。 基于Commit Logging的问题就，提出了“提前写入日志”（Write-Ahead Logging）的日志改进方案，“提前写入”就是允许在事务提交之前写入变动数据的意思。而对于提前写入磁盘，在数据库崩溃后需要回滚的数据，给出的解决办法是增加另外一种被称为Undo Log的日志类型，当变动数据写入磁盘前，必须先记录Undo Log，以便在事务回滚或者崩溃恢复时根据Undo Log对提前写入的数据变动进行擦除。

mysql的ref也能验证上面这个说法

The redo log is a disk-based data structure used 
during crash recovery to correct data 
written by incomplete transactions.

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。

show variables like 'innodb_flush%';
//my.cnf配置
innodb_flush_log_at_trx_commit = 1

这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

2.2写缓冲ChangeBuffer

我们知道mysql数据存储包含内存与磁盘两个部分，innodb是按数据页(通常为16k)从磁盘读取到内存中的(剩余操作在内存中执行)，当要更新数据时，若目标数据的数据页刚好在内存中，则直接更新。不在呢？
将这个更新操作(也可能是插入)缓存在change buffer中(redolog也会记录这个change buffer操作)等到下一次查询要用到这些数据时，再执行这些操作，改变数据(称为合并操作记录称为merge)。

写缓冲的目的是降低写操作的磁盘IO，提升数据库性能(一次内存操作，一次redolog写盘操作)。
写缓冲除了上面这个情况，也会定期被刷盘的，数据库正常关闭和redo log写满也会进行merge操作

小实验

查看mysql的change_buffer配置
image.png

innodb_change_buffer_max_size

介绍了写缓冲的大小，占整个缓存池的比例，默认25%

innodb_change_buffering

配置哪些写操作启用写缓冲，可以设置all/none/inserts/deletes等。

2.3redolog写满了怎么办？

在上文的changebuffer中我们提到了，mysql是按页读取数据到内存中的，无论要更新的数据是否在内存中，只要是更新操作就一定是在内存中执行。当内存数据页和磁盘数据页内容不一致时，我们称这个内存页为脏页，内存写入磁盘（称为flush操作），两者一致则为干净页

因为redolog是环形日志，当redolog写满时，就需要“擦掉”开头的一部分数据来达到循环写，这里的擦掉指，指将redolog日志的checkpoint位置从 CP推进到CP‘ ，同时将两点之间的脏页刷到磁盘上(flush操作)，此时系统要停止所有的更新操作（防止更新操作丢失）


image.png

除了redolog写满还有什么会引发flush操作？

1.系统内存不足。当要读取新的内存页时就要淘汰一些数据页，如果淘汰的正好是脏页，就要执行一次flush操作
2.Mysql认为系统处于“空闲状态”
3.正常关闭Mysql

flush操作对性能的影响

上述后两者场景(系统空闲和正常关闭)对于性能都没太大影响。
当为第一种redolog写满时，系统无法执行更新操作，所有操作都会堵塞
当为第二种内存不够用时，如果淘汰脏页太多，影响mysql响应时间

后两者刷脏页会影响性能，所以Mysql需要有刷脏页控制策略，可以从以下几个设置项考虑
1.设置innodb_io_capacity告诉innodb所在主机的IO能力

//利用fio工具来测试磁盘随机读写能力

 fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
  1. innodb_max_dirty_pages_pct设置脏页比例上限 控制刷脏页速度
    3.innodb_flush_neighbors=1 来设置“连坐”

一旦一个查询请求在执行过程中需要刷掉一个脏页，可以利用Mysql的一个连坐机制，即在准备刷掉一个脏页时把邻居(前提邻居也是脏页)也拖下水，邻居也可以把他的邻居给拖下水

3.删除语句执行流程

delete from t where  a=300 //假设a为索引

如该命令，在通过分析器-优化器-执行器找到数据后，innodb引擎会把a=300这条记录标记为删除(空间仍存在)，当要再插入一个a为300的值时，可能会复用这个位置（磁盘文件并不会减少，这里指记录的复用）

delete from t where  a>300 and a<500;

如该命令为范围删除，我们知道mysql的数据是按数据页存储的(默认16kb)，万一刚好删掉了一个数据页的记录呢（如上）？答案是整个数据页可能会被复用（有新值插入时，如插入a=400可以直接复用，但是a=600则不能）
另外，如果两个相邻的数据页利用率很小，系统会把两个页上的数据合到其中一个上，另一个数据页被标记为可复用。

delete from t

所有数据页都被标记为可复用，但是磁盘上文件不会变小。
因此delete命令其实只是把记录的位置（或数据页)标记为可复用，但磁盘文件的大小不会变
这些可复用的但是没被使用的空间就成了碎片。
这可能也是业务上更推荐使用软删除的原因吧

3.1只有delelte命令会产生碎片吗

当插入数据时，如果数据是按照索引递增插入，那么索引是紧凑的，但是数据如果是随机插入的呢？
1.如果插入后数据页没满，ok插入
2.如果插入后数据页数据溢出了，那么再插入这些数据时，就不得不再申请一个新的数据页来保存数据

select * from t where id<500 and id>0;
insert into t(id) values(550),(551),(560).....

如上，那么记录id为550的数据就不得不保存到新数据页中，页分裂完成后，旧的数据页留下了500-550的碎片空间（插入数据造成的空洞）
更新索引上的值也会造成碎片空间的产生，更新索引即删除一个旧的索引，再插入一个新值，也就是说，经过大量增删改的表都是可能存在碎片空间的

通过重建表来收缩空间

即重新构建索引树，让索引之间更紧凑

alter table a engine=innodb

关于重建表时能不能进行写操作

5.7之后引入了Online DDL
1.建立一个临时文件，扫描表a主键的所有数据页
2.用数据页中表A的记录生成B+树，存储到临时文件中
3.生成临时文件的过程中，将所有对A表的操作记录到一个日志文件中
4.临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与a表相同的数据文件
5.用临时文件替换a的数据文件
可以看到因为第三步的日志记录，使得重建表时也能进行写操作（alter 语句在启动时需要获取MDL写锁，但是这个写锁在真正拷贝数据之前旧退化成读锁）

重建方法都会扫描原表数据和构建临时文件，当表很大时很消耗IO和CPU资源，推荐使用github开源的gh-ost来做
重建表因为要创建临时文件（额外空间用于拷贝数据），如果表太大，磁盘太小可能会适得其反

参考

1.mysql实战45讲
2.源码角度分析mysql查询语句

最后编辑于：2025-01-18 16:59:15
© 著作权归作者所有,转载或内容合作请联系作者


喜欢的朋友记得点赞、收藏、关注哦！！！

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

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

相关文章

【Docker】Docker入门了解

【Docker】Docker入门了解

文章目录 Docker 的核心概念Docker 常用命令示例&#xff1a;构建一个简单的 C 应用容器1. 创建 C 应用2. 创建 Dockerfile3. 构建镜像4. 运行容器 Docker 优势学习 Docker 的下一步 **一、Docker 是什么&#xff1f;****为什么 C 开发者需要 Docker&#xff1f;** **二、核心概…
阅读更多...
使用langchain ollama gradio搭建一个本地基于deepseek r1的RAG问答系统

使用langchain ollama gradio搭建一个本地基于deepseek r1的RAG问答系统

目录 简介 环境配置 具体实现 安装依赖 定义模型和prompt 加载检索文档 切割 向量存储 创建检索器 实例化 前端搭建 实现效果 小tips 简介 首先介绍一下使用的几个工具&#xff0c;模型和rag的步骤&#xff0c;注&#xff1a;这里只是简单描述一下&#xff0c;不展…
阅读更多...
Python中的函数（下）

Python中的函数（下）

函数返回值 返回单个值 函数可以通过 return 语句返回一个值。一旦执行到 return 语句&#xff0c;函数就会停止执行&#xff0c;并将指定的值返回给调用者。例如&#xff1a; 返回多个值 实际上&#xff0c;Python函数只能返回一个值&#xff0c;但可以通过返回一个元组来模…
阅读更多...
Spring Boot + Facade Pattern : 通过统一接口简化多模块业务

Spring Boot + Facade Pattern : 通过统一接口简化多模块业务

文章目录 Pre概述在编程中&#xff0c;外观模式是如何工作的&#xff1f;外观设计模式 UML 类图外观类和子系统的关系优点案例外观模式在复杂业务中的应用实战运用1. 项目搭建与基础配置2. 构建子系统组件航班服务酒店服务旅游套餐服务 3. 创建外观类4. 在 Controller 中使用外…
阅读更多...
安卓(android)实现注册界面【Android移动开发基础案例教程（第2版）黑马程序员】

安卓(android)实现注册界面【Android移动开发基础案例教程（第2版）黑马程序员】

一、实验目的&#xff08;如果代码有错漏&#xff0c;可查看源码&#xff09; 1.掌握LinearLayout、RelativeLayout、FrameLayout等布局的综合使用。 2.掌握ImageView、TextView、EditText、CheckBox、Button、RadioGroup、RadioButton、ListView、RecyclerView等控件在项目中的…
阅读更多...
Prompt提示词完整案例：让chatGPT成为“书单推荐”的高手

Prompt提示词完整案例：让chatGPT成为“书单推荐”的高手

大家好&#xff0c;我是老六哥&#xff0c;我正在共享使用AI提高工作效率的技巧。欢迎关注我&#xff0c;共同提高使用AI的技能&#xff0c;让AI成功你的个人助理。 许多人可能会跟老六哥一样&#xff0c;有过这样的体验&#xff1a;当我们遇到一个能力出众或对事物有独到见解的…
阅读更多...
智慧园区管理平台实现智能整合提升企业运营模式与管理效率

智慧园区管理平台实现智能整合提升企业运营模式与管理效率

内容概要 在当今数字化的背景下&#xff0c;智慧园区管理平台正逐渐成为企业提升运营效率和管理模式的重要工具。这个平台汇聚了多种先进技术&#xff0c;旨在通过智能整合各类资源与信息&#xff0c;帮助企业实现全面的管理创新。 智慧园区管理平台不仅仅是一个数据处理工具…
阅读更多...
Baklib如何提升企业知识管理效率与市场竞争力的五大对比分析

Baklib如何提升企业知识管理效率与市场竞争力的五大对比分析

内容概要 在信息化时代&#xff0c;企业在知识管理方面面临着巨大的挑战与机遇。为了有效应对这些挑战&#xff0c;“Baklib”作为一个知识中台&#xff0c;通过其高度集成的数字化平台&#xff0c;为企业提供全方位的知识管理解决方案。特别是在以下五个领域&#xff0c;它展…
阅读更多...
c++：vector

c++：vector

1.使用 1.1构造函数 常见的三种构造方式&#xff1a;空构造&#xff0c;拷贝构造&#xff0c;指定元素构造 1.2iterator begin和end也分为正向和反向。 注意&#xff1a;反向迭代器可以反向遍历是因为在定义rbegin和rend函数的时候把尾地址给到了rbegin&#xff0c;而不是说改…
阅读更多...
C++17 搜索器教程：解锁高效搜索新姿势

C++17 搜索器教程：解锁高效搜索新姿势

C17搜索器教程&#xff1a;解锁高效搜索新姿势 C17搜索器简介 在C的发展历程中&#xff0c;C17是一个重要的里程碑&#xff0c;它引入了诸多实用的新特性&#xff0c;搜索器功能便是其中之一。此功能着重对std::search算法进行了强化&#xff0c;使其支持多种搜索策略&#x…
阅读更多...
Transformer+vit原理分析

Transformer+vit原理分析

目录 一、Transformer的核心思想 1. 自注意力机制&#xff08;Self-Attention&#xff09; 2. 多头注意力&#xff08;Multi-Head Attention&#xff09; 二、Transformer的架构 1. 整体结构 2. 编码器层&#xff08;Encoder Layer&#xff09; 3. 解码器层&#xff08;Decoder…
阅读更多...
C语言自定义数据类型详解（二）——结构体类型（下）

C语言自定义数据类型详解（二）——结构体类型（下）

书接上回&#xff0c;前面我们已经给大家介绍了如何去声明和创建一个结构体&#xff0c;如何初始化结构体变量等这些关于结构体的基础知识。下面我们将继续给大家介绍和结构体有关的知识&#xff1a; 今天的主题是&#xff1a;结构体大小的计算并简单了解一下位段的相关知识。…
阅读更多...
【ComfyUI专栏】如何使用Git命令行安装非Manager收录节点

【ComfyUI专栏】如何使用Git命令行安装非Manager收录节点

当前的ComfyUI的收录的自定义节点很多&#xff0c;但是有些节点属于新出来&#xff0c;或者他的应用没有那么广泛&#xff0c;Manager管理节点 有可能没有收录到&#xff0c;这时候 如果我们需要安装需要怎么办呢&#xff1f;这就涉及到我们自己安装这些节点了。例如下面的内容…
阅读更多...
【Block总结】PKI 模块，无膨胀多尺度卷积，增强特征提取的能力|即插即用

【Block总结】PKI 模块，无膨胀多尺度卷积，增强特征提取的能力|即插即用

论文信息 标题: Poly Kernel Inception Network for Remote Sensing Detection 作者: Xinhao Cai, Qiuxia Lai, Yuwei Wang, Wenguan Wang, Zeren Sun, Yazhou Yao 论文链接&#xff1a;https://arxiv.org/pdf/2403.06258 代码链接&#xff1a;https://github.com/NUST-Mac…
阅读更多...
[OO ALV] OO ALV 基础显示

[OO ALV] OO ALV 基础显示

程序代码 REPORT z437_test_2025.DATA gt_spfli TYPE STANDARD TABLE OF spfli. " 内表DATA go_alv TYPE REF TO cl_gui_alv_grid. " 创建和管理ALV表格DATA gs_layout TYPE lvc_s_layo. " 存储ALV表格的布局信息*---------------------…
阅读更多...
jQuery小游戏（二）

jQuery小游戏（二）

jQuery小游戏&#xff08;二&#xff09; 今天是新年的第二天&#xff0c;本人在这里祝大家&#xff0c;新年快乐&#xff0c;万事胜意&#x1f495; 紧接jQuery小游戏&#xff08;一&#xff09;的内容&#xff0c;我们开始继续往下咯&#x1f61c; 游戏中使用到的方法 key…
阅读更多...
Linux的常用指令的用法

Linux的常用指令的用法

目录 Linux下基本指令 whoami ls指令&#xff1a; 文件&#xff1a; touch clear pwd cd mkdir rmdir指令 && rm 指令 man指令 cp mv cat more less head tail 管道和重定向 1. 重定向&#xff08;Redirection&#xff09; 2. 管道&#xff08;Pipes&a…
阅读更多...
蓝桥杯之c++入门（一）【C＋＋入门】

蓝桥杯之c++入门（一）【C＋＋入门】

目录 前言5. 算术操作符5.1 算术操作符5.2 浮点数的除法5.3 负数取模5.4 数值溢出5.5 练习练习1&#xff1a;计算 ( a b ) ⋆ c (ab)^{\star}c (ab)⋆c练习2&#xff1a;带余除法练习3&#xff1a;整数个位练习4&#xff1a;整数十位练习5&#xff1a;时间转换练习6&#xff…
阅读更多...
51单片机开发：定时器中断

51单片机开发：定时器中断

目标&#xff1a;利用定时器中断&#xff0c;每隔1s开启/熄灭LED1灯。 外部中断结构图如下图所示&#xff0c;要使用定时器中断T0&#xff0c;须开启TE0、ET0。&#xff1a; 系统中断号如下图所示&#xff1a;定时器0的中断号为1。 定时器0的工作方式1原理图如下图所示&#x…
阅读更多...
【设计测试用例自动化测试性能测试 实战篇】

【设计测试用例自动化测试性能测试 实战篇】

&#x1f308;个人主页&#xff1a;努力学编程’ ⛅个人推荐&#xff1a; c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构&#xff0c;刷题刻不容缓&#xff1a;点击一起刷题 &#x1f319;心灵鸡汤&#xff1a;总有人要赢&#xff0c;为什么不能是我呢 设计测试用例…
阅读更多...
最新文章

Copyright @ 2022~2023