mysql撤销上一次执行 mysql怎么撤销上一步

转载

mob64ca13fa2f9e 2024-06-13 08:14:38

文章标签 mysql撤销上一次执行 bat如何执行完上一条命令 dml语句包括哪些数据 MySQL 文章分类 MySQL 数据库

前言

上一篇文章讲了《一条查询语句是如何执行的》，应该很多人都注意到我在前缀 MySQL 相关后面加了个(一)

为啥讲的是更新而不是删除和插入呢？

Because，更新的复杂度要比插入和删除要高，如果已经理解了更新的一整套流程，插入和删除的流程对于你来说也是信手拈来(好像不太恰当，暂时想不出更好的词就士但啦)，所以这里就只讨论更新的，插入和删除的流程大同小异，不再做讨论。

使用哪个数据库存储引擎？

目前市面上流行的还是 MySQL5.7 ，而且大部分系统用的是分布式微服务的架构，考虑并发事务的执行，这里选择讲解 innodb 引擎中的语句更新流程。

从哪些方面讲？

更新的流程相对比较复杂，涉及到数据库 innodb 的事务，所以这里会先从数据库的磁盘结构+内存结构对其内在机理进行剖析，之后通过讲解 redo log + undo log ，数据库端的事务日志以及 binlog，服务层面的日志，还有数据库的二阶段提交保证事务的 ACID(暂不展开)，来让大家对整个流程有一个比较全面的了解。

innodb 的磁盘和内存结构

按照我的思路啊，我们先来看官网提供的这张图：

mysql撤销上一次执行 mysql怎么撤销上一步_MySQL

从图中我们可以看到：

左边是 innodb 的内存结构，其中包含自适应的 hash 索引(adaptive hash index)，Buffer Pool，Change Buffer，最下面是 Log Buffer；
在内存数据刷到磁盘中间有操作系统的缓存；
右边是 innodb 的磁盘结构，包含系统表空间，独占表空间，共享表空间，临时表空间，redo log 和 undo log，用虚线表示的是逻辑存在而非物理存在；

接下来我会挑重点来介绍这些组件。

Buffer Pool

这个是 innodb 的缓冲池空间，保存的是数据页(data page)和索引页(index page)，在修改数据的时候，数据不会直接写入到磁盘中，而会先写入 Buffer Pool(如果页数据在的话则修改 Buffer Pool)，再由内存空间刷入到磁盘空间。

我们可以通过如下命令查看关于 innodb 的 Buffer Pool 的参数，有想详细了解的小伙伴请移步 MySQL 官网：

show variables like '%innodb_buffer_pool%';

mysql撤销上一次执行 mysql怎么撤销上一步_数据_02

show status like '%innodb_buffer_pool%';

mysql撤销上一次执行 mysql怎么撤销上一步_mysql撤销上一次执行_03

有几个点我们需要明确一下：

缓冲池(buffer pool)是一种常见的降低磁盘访问的机制；

使用 Buffer Pool，可以避免每次查询数据都跟磁盘进行 IO，磁盘读写，并不是按需读取，而是按页读取，一次至少读一页数据(1M / 64page=16K)，如果未来要读取的数据就在页中，就能够省去后续的磁盘IO，提高效率，所以说Buffer Pool 是一种减少磁盘 IO 的机制。

2. 缓冲池以页(page)为单位缓存数据；

3.缓冲池使用的内存淘汰算法是LRU，memcache，OS，InnoDB都使用了这种算法；

InnoDB对普通LRU(链表实现)进行了优化：将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

这里再明确几个概念：

脏页：当缓冲池数据和磁盘不一致的时候；
刷脏：把脏页的数据写入磁盘中的时候叫刷脏；
磁盘和缓冲区都是以页为单位，而关于页的大小：

mysql撤销上一次执行 mysql怎么撤销上一步_数据_04

Change Buffer

更新数据页时，数据页已经加载到缓冲池中的时候，可以直接更新；
那如果更新数据页时，如果数据页存在于磁盘，则需要将数据页先读取到内存，再修改内存，不在内存区域的话至少得发生一次 IO，这样不就资源浪费了？

这个时候 change buffer 就出现了，如果要更新的数据不是唯一索引，并且没有数据重复的情况，则不需要确保数据是否会重复。我们将要修改的记录写入到 change buffer 中，再通过 change buffer 一次性同步到磁盘中，减少 IO，5.5 之后叫 change buffer，以前叫 insert buffer (只支持 insert)，这样可以减少 IO 次数，提升修改效率。

最后把 Change Buffer 记录到数据页的操作叫做 merge。什么时候发生 merge？有几种情况：在访问这个数据页的时候，或者通过后台线程、或者数据库 shut down、 redo log 写满时触发。

我们也可以用下面的命令看看 change_buffer 的参数：

mysql撤销上一次执行 mysql怎么撤销上一步_mysql撤销上一次执行_05

show variables like '%change_buffer%';

注意：max_size 不是指 change buffer 的大小，而是指 change buffer 占整个 buffer pool 的比例，默认为 25%。如果数据库大部分索引都是非唯一索引，并且业务是写多读少，这种情况下可以适当调大 change buffer的比例。

Log Buffer + Redo Log

看到这里，相信很多人还是会有很多问号：

既然是用内存空间来做缓冲区，那如果数据库服务器宕机了，那未同步完的数据不都完了？

其实很多小伙伴应该之前有接触过或者听说过 innodb 是支持事务的，而它支持事务的方式是 redo log + undo log 作为事务日志，但是对 redo log 可能没有进行一个比较深入的了解或者说并不知道它实际上是以一个什么样的角色存在。
好了不卖关子了，了解过 redis 的小伙伴应该都知道 redis 也是基于内存的，它做异常恢复的方式是通过持久化到磁盘的方式，那我们MySQL 的 innodb 引擎也是通过类似的方式，在刚才官网的结构图中我们看到磁盘空间中有 redo log，而 redo log 就是通过buffer 区的另外一块区域 log buffer (专门用来保存写入日志文件的数据，默认 16M，可以节省磁盘 IO)进行同步的，这个能力叫crash safe。redo log 在 innodb 中保证了事务的持久性。

redo log 不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志。

这种日志和磁盘配合的整个过程，其实就是 MySQL 里的 WAL 技术(Write-Ahead Logging)，它的关键点就是先写日志，再写磁盘。

那已经有Buffer pool 作为缓存了，为啥还要多此一举再多一个 log buffer呢？

我想问一下大家 kafka 快的原因有哪些知道不？其中有一点就是多这个 log buffer 的原因。没错，这位同学答对了！就是顺序 IO 和随机 IO 的区别，随机 IO 是刷盘的操作，相对而言顺序 IO 效率就高多了，因此先把修改写入日志，可以延迟刷盘时机，进而提升系统吞吐。当然，buffer pool 还是作为刷入磁盘的主角。

那 log buffer 什么时候才会触发刷数据到 redo log 的操作呢？

我们来看下 log buffer 的参数，大小默认是 16M：

show variables like '%log_buffer%';

mysql撤销上一次执行 mysql怎么撤销上一步_数据_06

redo log 既然是存在于磁盘空间，那它是可以找到对应的文件的，在 mysql 目录中对应的文件名如下，默认是两个大小恒为 48M 的 ib_logfile 文件：

mysql撤销上一次执行 mysql怎么撤销上一步_MySQL_07

既然大小是恒定的，那它肯定是会有删除数据的操作的，

mysql撤销上一次执行 mysql怎么撤销上一步_bat如何执行完上一条命令_08

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是还空着的部分，可以用来记录新的操作。如果 write pos 跟 checkpoint 重合了，表示已经没有空间，这时候不能再执行新的更新，得停下来先删除一些记录，把 checkpoint 往前移一下。

那到底什么时候刷盘呢？

我的回答是跟事务相关，先看这条命令：

show variables like '%log_at_trx_commit%';

默认值是 1，

mysql撤销上一次执行 mysql怎么撤销上一步_mysql撤销上一次执行_09

默认是提交事务就写入 log buffer，值为 0 时事务提交后不会刷盘，值为 2 时由操作系统提交。

值含义

0(延迟写)log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush 操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。
1(默认，实时写，实时刷)每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且刷到磁盘中去。
2(实时写，延迟刷)每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file。但是 flush 操作并不会同时进行。该模式下，MySQL 会每秒执行一次 flush 操作。

mysql撤销上一次执行 mysql怎么撤销上一步_MySQL_10

标题上没见到 undo log，为什么不说说 undo log？

redo log 和 undo log 是一对，它们组合到一起就是 innodb 的事务日志，但是 undo log 是关于事务提交回滚的日志，这次主要讨论流程，这里就简单提一下。
undo log(撤销日志或回滚日志)记录了事务发生之前的数据状态(不包括 select)。如果修改数据时出现异常，可以用 undo log 来实现回滚操作(保持原子性)。
在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，属于逻辑格式的日志。
undo Log 的数据默认在系统表空间 ibdata1 文件中，因为共享表空间不会自动收缩，也可以单独创建一个 undo 表空间。(结构图可以看出)

binlog

用过 binlog 的伙伴应该知道binlog 有这几个功能：

记录DDL 和 DML 的逻辑日志
主从复制 (slave 拿到 master 的 binlog 再执行)
数据恢复

MySQL 在 server 层面引入了 binlog, 它可以记录所有引擎中的修改操作，因而可以对所有的引擎使用复制功能，然而这种情况会导致redo log与binlog的一致性问题。在 MySQL 中是通过内部 XA 机制解决这种一致性的问题。

我们先来看看 binlog 怎么配置，在 my.cnf 中配置 binlog：

vi /etc/my.cnflog-bin=mysql-bin #添加这一行就okbinlog-format=ROW #选择row模式server_id=1 #配置mysql replaction需要定义，不能和canal的slaveId重复

业内目前推荐使用的是 row 模式，准确性高，虽然说文件大，但是现在有 SSD 和万兆光纤网络，这些磁盘 IO 和网络 IO 都是可以接受的。

在 innodb 里其实又可以分为两部分，一部分在缓存中，一部分在磁盘上。这里业内有一个词叫做刷盘，就是指将缓存中的日志刷到磁盘上。跟刷盘有关的参数有两个：

sync_binlog 和binlog_cache_size。

这两个参数作用如下：

binlog_cache_size: 二进制日志缓存部分的大小，默认值32ksync_binlog=[N]: 表示每多少个事务写入缓冲，刷一次盘,默认值为0

要注意两点：

binlog_cache_size设过大，会造成内存浪费。binlog_cache_size设置过小，会频繁将缓冲日志写入临时文件。
sync_binlog=0:表示刷新binlog时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘，这个性能最好。sync_binlog=1，代表每次事务提交时就会刷新binlog到磁盘，对性能有一定的影响。sync_binlog=N,代表每N个事务提交会进行一次binlog刷新。
另外，这里存在一个一致性问题，sync_binlog=N，数据库在操作系统宕机的时候，可能数据并没有同步到磁盘，于是再次重启数据库，会带来数据丢失问题。

MySQL 的 binlog 是多文件存储，定位一个 LogEvent 需要通过 binlog filename + binlog position，进行定位。