小工具      在线工具  汉语词典  css  js  c++  java

SQL更新语句执行流程

sql,数据库 额外说明

收录于:40天前

SQL 的更新执行流程跟查询流程差不多,假设现在有一条更新语句:UPDATE T SET c = 1 WHERE ID = 1

  • 客户端连接MySQL服务,通过连接器创建连接,并验证权限。
  • 更新语句时,表的所有查询缓存数据都会被清除。
  • 之后通过分析器进行词法分析和语法分析,检查update语句是否存在问题。
  • 通过优化器选择索引后,找到最合理的执行计划
  • 执行器负责执行,在存储引擎中找到ID=1的行,然后更新

与查询过程不同,更新过程还涉及到两个重要的日志模块:redolog(重做日志)和binlog(归档日志)。这两个日志也是本文的重点。

我们先来一张图看看流程是什么样的:

一、redolog

1. 什么是 redolog

Redolog是InnoDB存储引擎特有的,是保证事物持久性的重要机制。当mysql意外关闭或崩溃时,确保已提交的事务持久化到磁盘,防止数据丢失;

2. redolog 的作用

Mysql数据以页(16KB)为单位。查询一条数据,会从硬盘加载一页数据。加载的数据称为数据页,将被放入缓冲池中。后续查询将来自缓冲池中的搜索。如果没有命中,就从磁盘加载,减少硬盘IO开销。更新数据也是同样的道理。如果发现Buffer Pool有数据需要更新,就直接在Buffer Pool中更新。

假设我们只在内存的 Buffer Pool 中更新了页数据,事物提交后突然数据库崩溃,导致内存中的数据丢失了,那么这个更新后的数据也一起丢失了,这肯定是不能接受的。那我们要怎么保证数据的持久性呢?一种方法是每次提交事务时,将事务提交修改的所有数据页更新到磁盘。,但是这样做有一个问题:

  • 只修改一条记录就更新整个数据页有点浪费。
  • 一个事务可能包含多个SQL语句,这些语句可能操作Buffer Pool中不相邻的数据页。当这个事务修改的数据页刷新到磁盘时,会产生大量的随机IO。随机IO的开销比顺序IO大。

由于以上原因,我们的想法是在交易提交后保存数据。即使数据库崩溃,事务重启后的数据也能恢复;

所以当我们提交交易时,我们只需要记录该交易提交的修改数据即可,例如:

将第 0 号表空间第 100 号页面中偏移量为 1000 处的值更新为 2

这就是我们所说的重做日志。即使数据库崩溃了,重启后也可以根据redolog日志进行恢复。

innodb_flush_log_at_trx_commit这个参数设置成1的时候,
表示每次事务的redo log都直接持久化到磁盘。这个参数设置成1,这样可以保证MySQL异常重启之后数据不丢失

二、binlog

binlog记录了语句的原始逻辑,比如insert inot value(1,2,3),所以Mysql数据的数据备份,主备等都离不开binlog日志,需要通过来实现数据一致性binlog 日志。

  • Binlog是由MySQL的服务器层实现的,可供所有引擎使用。
  • Binlog是逻辑日志,记录了这条语句的原始逻辑,比如“ID=2的行的c字段加1”。
  • Binlog可以额外写入。 “追加写入”是指binlog文件达到一定大小后,会切换到下一个,不会覆盖之前的日志。
  • sync_binlog参数设置为1的时候,提交事物的时候就会写入到binlog日志,跟redolog日志刷盘时机是一样的

三、二阶段提交

为什么需要“两阶段提交”?这是为了让两条日志之间的逻辑一致

1. 不使用二阶段提交

如果只写一次,应该先写bin-log还是redo-log?

  • 先写bin-log,再写redo-log:当事务提交后,先写bin-log成功,结果在写redo-log时断电宕机了,再重启后由于redo-log中没有该事务的日志记录,因此不会恢复该事务提交的数据。但要注意,主从架构中同步数据是使用bin-log来实现的,而宕机前bin-log写入成功了,就代表这个事务提交的数据会被同步到从机,这意味着从机比主机多了一份数据。
  • 先写redo-log,再写bin-log:当事务提交后,先写redo-log成功,但在写bin-log时宕机了,主节点重启后,会根据redo-log恢复数据,但从机依旧是依赖bin-log来同步数据的,因此从机无法将这个事务提交的数据同步过去,毕竟bin-log中没有撒,最终,从机可能会比主机少一份数据。

2. 使用二阶段提交

为了避免以上的数据不一致问题,Innodb存储引擎使用的两阶段提交方案。
把redolog的写入拆成了两个步骤prepare和commit

  • 如果binlog日志的写入过程中出现异常,因为mysql通过redolog回复数据时,发现redolog还处于prepare阶段,没有对应的binlog日志,就会回滚;
  • 如果redolog提交commit时出现异常,虽然redolog处于prepare阶段,但通过事务ID可以找到对应的binlog日志,因此mysql认为数据完整,会提交redolog日志并恢复数据;

3.两阶段提交有什么问题

binlog 和 redolog 在内存中都对应的缓存空间,binlog 会缓存在 binlog cache,redolog 会缓存在 redolog buffer,它们持久化到磁盘的时机分别由下面这两个参数控制。
一般我们为了避免日志丢失的风险,会将这两个参数设置为 1:

  • 当sync_binlog = 1时,表示每次提交事务时,都会将binlog缓存中的binlog直接持久化到磁盘;
  • 当innodb_flush_log_at_trx_commit = 1时,表示每次提交事务时,都会将redo log buffer中缓存的redo log直接持久化到磁盘;

可以看到,如果sync_binlog和innodb_flush_log_at_trx_commit都设置为1,那么在每个事务提交过程中,至少会调用2次flush操作,一个是flush redo log,另一个是flush binlog,所以这个将成为性能问题。瓶颈。


我是1024,一个专注Java技术、记录生活的博主。

欢迎扫描二维码关注“一零二四”公众号,一起学习,一起进步,多看路,少避坑。

. . .

相关推荐

额外说明

《操作系统》——线程和进程

目录 一、进程的概念 二、进程的结构和特征 2.1 进程的结构 2.2 进程的特征 三、线程的概念 四、进程和线程比较 五、线程的实现方式 一、进程的概念 程序放在磁盘上不叫做进程,只有运行起来之后才叫做进程,进程进程就是进行中的程序! 一次运行就会有一

额外说明

Tomcat临时目录tmp抛出异常错误解决办法

首先,我们应该知道,对于http post请求来说,它需要使用这个临时目录来存储post数据,其次,因为该目录是挂在到/temp目录下的临时文件,那么对于一些操作系统,像centOS将经常删除这个临时目录,所有导致该目录不存在了。 目录 1.在appli

额外说明

后台提交的中文发送到mysql总是乱码。

2 同时中文查询条件查不到数据,但后台调试显示中文,很奇怪 在tomcat中加   <Connector port="8380" protocol="HTTP/1.1"                connectionTimeout="20000"  

额外说明

网络文件系统

NFS 1. nfs简介 1.1nfs特点 NFS(Network File System)即网络文件系统,是FreeBSD支持的文件系统中的一种,它允许网络中的计算机之间通过TCP/IP网络共享资源 在NFS的应用中,本地NFS的客户端应用可以透明的读

额外说明

HTML 表单提交摘要

HTML表单提交总结 概述:随着HTML5的兴起,前端越来越多样化,比如表单的提交,现在就有多种方式可以选择,下面总结一下常见的表单提交方式。 1.最基本的表单提交。 <!DOCTYPE html> <html> <head> <meta charset

额外说明

经典算法学习之------快速排序

快速排序 一、什么是算法 1. 算法的定义 2. 补充的概念 二、交换排序 1. 交换排序介绍 2. 快速排序 3. 伪代码 三、算法实践 1. 算法实现 2. 时间复杂度 3. 空间复杂度 ​ 一、什么是算法 本专栏为《手撕算法》栏目的子专栏:《经典算

额外说明

Redis内存满的最佳解决方案

文章目录 前言 造成内存满原因 数据量过大 键过期机制不合理 内存碎片 内存泄漏 大量短期数据存储 解决方案 1.增加内存 2.设置过期时间 2.1.在set命令中指定过期时间(秒): 2.2.在set命令中指定过期时间(毫秒): 2.3.使用expir

额外说明

《深入理解Java虚拟机》第二版 第二章笔记

目录 二.Java内存区域与内存溢出异常 1  运行时数据区域 1.1  程序计数器(Program Counter Register) 1.2  Java虚拟机栈(JVM Stacks) 1.3  本地方法栈(Native Method Stack)

额外说明

JS, CSS 文件压缩与反压缩工具

JS, CSS 压缩可以去掉文件中的多余字符, 减少文件大小, 减少网络传输时间。 但是, 压缩之后就没有可读性了。 比较适合一些很稳定,较少改动和共用性很高的文件。 目前存在的可以压缩的工具很多,也有不少在线的。 YUICOMPRESSOR是一个不错的

额外说明

使用修复软件快速解决api-ms-win-core-datetime-l1-1-0.dll系列问题

使用DLLEscort软件自动修复 DLLEscort软件能够修复解决常见的Windows系统文件与系统所需的大部分运行库,它可以帮助您一键修复系统软件或游戏丢失的DLL运行库文件,节省您的宝贵时间。 如果是因为缺少了运行库 导致的提示文件找不到错误 通

ads via 小工具