磁盘导致 mysql 独写慢 mysql数据写入磁盘的原理

文章目录

• 一、MySQL InnoDB 存储引擎写入磁盘（落盘）的原理

• 一条 update 语句在写入磁盘的过程

• 为什么必须有“两阶段提交”呢？

• binlog 的写入机制

• 二、MySQL怎么保证持久性、原子性?(MySQL中是如何实现事务提交和回滚的)

• redo log(重做日志) 如何保证事务的持久性？
• undo log(回滚日志) 如何来保证事务的原子性？
• mysql 命令行开启事务提交

• 设置手动提交事务

• 三、隔离性

• 锁机制
• MVCC(Multi-Version Concurrency Control) 多版本并发控制
• 事务隔离级别

• 参考

一、MySQL InnoDB 存储引擎写入磁盘（落盘）的原理

MySQL InnoDB 存储引擎是如何将数据一步一步最终写入到磁盘上的。

一条 insert 语句在写入磁盘的过程中到底涉及了哪些文件？

只要 redo log 和 binlog 保证持久化到磁盘，就能确保 MySQL 异常重启后，数据可以恢复。

这两种日志有以下三点不同。

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

一条 update 语句在写入磁盘的过程

01 | 基础架构：一条SQL查询语句是如何执行的？
参考URL: https://time.geekbang.org/column/article/68319
MySql事务之两阶段提交与redo log、binlog
参考URL: https://www.jianshu.com/p/0b2301d50351

1. 首先 update 进入 server 层后，会进行一些必要的检查，检查的过程中并不会涉及到磁盘的写入。
2. 检查没有问题之后，便进入引擎层引擎层的处理过程。

为了保证事务的持久性，mysql的InnoDB采用了WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

为了保证两份日志的逻辑一致性mysql采用了两阶段提交。

下面是一个 update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

最后三步看上去有点“绕”，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

为什么必须有“两阶段提交”呢？

如果不使用两阶段提交，假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

1.先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。

但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。

然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

2.先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

binlog 的写入机制

mysql先写日志再写磁盘_mysql底层原理解析（一）之日志
参考URL:

binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。状态下图所示。

可以看到，每个线程有自己 binlog cache，但是共用同一份 binlog 文件。

(1) 图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。

(2) 图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IO。

• write 和 fsync 的时机，是由参数 sync_binlog 控制的：
  sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
• sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。

但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

二、MySQL怎么保证持久性、原子性?(MySQL中是如何实现事务提交和回滚的)

参考URL:

redo log 和 undo log 都属于InnoDB的事务日志。

• redo log是用来恢复数据的，用于保障已提交事务的持久性；
• undo log是用来回滚事务的，用于保障未提交事务的原子性。

redo log(重做日志) 如何保证事务的持久性？
undo log(回滚日志) 如何来保证事务的原子性？

redo log(重做日志) 如何保证事务的持久性？

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log 被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在 redo log 记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

既然 redo log 也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：

（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。

（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性

• 引入了redo log，它只用来保存事务对数据页所做的修改,
  你对数据页的修改都记录在redo log了, 宕机了根据redo log的内容恢复就好, 默认设置下事务只要提交就会持久化到磁盘文件redo log中。
  比如，当一条记录需要更新时，Innodb引擎会先把对这个数据页的修改 写到redo log buffer中，事务提交时再把redo log buffer刷到磁盘文件redo log中，这样是追加写，顺序IO速度很快.

MySQL会根据redo log自动在系统空闲时完成更新磁盘数据页的操作, 这个过程也叫做刷盘, 会对MySQL性能有一定影响

记到redo log buffer的时候, 你这个数据页的内容已经读到内存了, 内存里面的这个数据页的内容已经被更新了, 以后都是都内存中的这个最新版本.

你可能还会问, 那只用内存里的数据那宕机了咋办?

这不就体现了redo log的作用, 你对数据页的修改都记录在redo log了, 宕机了根据redo log的内容恢复就好, 默认设置下事务只要提交就会持久化到磁盘文件redo log中。

undo log(回滚日志) 如何来保证事务的原子性？

原子性指的是事务中的所有操作是一个不可分割的整体, 要么全做, 要么全不做.

利用Innodb引擎的undo log(回滚日志)，它记录了回滚一个操作必需的内容

比如，当你update一条数据的时候，就需要这条记录的原始值，回滚的时候，把这条记录再update为原始值

这样，当事务执行失败，就可以根据undo log回滚之前执行成功的操作。

InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

undo log 跟 redo log 一样也是在SQL操作数据之前记录的，也就是SQL操作先记录日志，再进行操作数据。

如上图所示，SQL操作之前会先记录 redo log、undo log 到日志缓冲区，日志缓冲区的数据会记录到os buffer中，再通过调用fsync()方法将日志记录到log file中。

undo log 属于逻辑日志，它记录的是sql执行相关的信息。可以简单的理解为：当insert一条记录时，undo log会记录一条对应的delete语句；当update一条语句时，undo log记录的是一条与之操作相反的语句。当事务需要回滚时，可以从undo log中找到相应的内容进行回滚操作，回滚后数据恢复到操作之前的状态。

当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。

以update操作为例：当事务执行update时，其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态。

如果开启了 binlog 日志，我们还需将事务逻辑数据写入 binlog 文件，且为了保证复制安全，建议使用 sync_binlog=1 ，也就是每次事务提交时，都要将 binlog 日志的变更刷入磁盘。

insert 语句成功提交时，真正发生磁盘数据写入的，并不是 MySQL 的数据文件，而是 redo log 和 binlog 文件。

事务日志 undo log(撤销日志) redo log(记录事务操作日志)

mysql 命令行开启事务提交

MySQL默认采用自动提交（AUTOCOMMIT）模式，不是显示的开启一个事务，每个查询都被当作一个事务执行提交操作。

在当前连接中，可以通过设置AUTOCOMMIT变量来开启或者禁用自动提交功能。

mysql> show variables like ‘AUTOCOMMIT’;
 ±--------------±------+
 | Variable_name | Value |
 ±--------------±------+
 | autocommit | ON |
 ±--------------±------+
 1 row in set (0.03 sec)

1或者ON表示开启；0或者OFF表示禁用。
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.11 sec)
当 autocommit = 0 时，所有的查询都在一个事务中，直到显示的执行 commit 进行提交或者 rollback 进行回滚，该事务才最终结束，同时开启了另一个事务。

对于一个MYSQL数据库(InnoDB)，事务的开启与提交模式无非下面这两种情况：

• autocommit=0： 在用户执行commit命令时提交，用户本次对数据库开始进行操作到用户执行commit命令之间的一系列操作为一个完整的事务周期。
• 若参数autocommit=1(系统默认值)，事务的开启与提交又分为两种状态：
  ①手动开启手动提交：当用户执行start transaction命令时(事务初始化)，一个事务开启，当用户执行commit命令时当前事务提交。从用户执行start transaction命令到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。
  ②自动开启自动提交：如果用户在当前情况下(参数autocommit=1)未执行start transaction命令而对数据库进行了操作，系统则默认用户对数据库的每一个操作为一个孤立的事务，也就是说用户每进行一次操作系都会即时提交或者即时回滚。这种情况下用户的每一个操作都是一个完整的事务周期。

设置手动提交事务

修改默认提交
set autocommit=0;
begin;
start transaction;
事务手动提交：commit;
事务手动回滚：rollback；

练习 demo sql

SHOW DATABASES;
CREATE DATABASE test_db;
use test_db;
create table student(id int, name varchar(100));
insert into student(id,name) values(1,"she1");
select * from student;

三、隔离性

**与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。**隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的Serializable (可串行化)，但实际应用中出于性能方面的考虑很少会使用可串行化。

隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们主要考虑最简单的读操作和写操作，那么隔离性的探讨，主要可以分为两个方面：

• (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
• (一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

锁机制

首先来看两个事务的写操作之间的相互影响。隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。

锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

隔离性：保证事务执行尽可能不受其他事务影响；InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制（包含next-key lock）、MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）

MVCC(Multi-Version Concurrency Control) 多版本并发控制

MVCC的实现,是通过保存数据在某个时间点的多版本快照来实现的.

InnoDB的MVCC是通过在每行记录后面保存2个隐藏的列来实现的,一列保存了行的创建时间,一列保存了行的过期时间(或删除时间).但它们都存储的是系统版本号

MVCC最大的作用是: 实现了非阻塞的读操作,写操作也只锁定了必要的行.

MYSQL的MVCC 只在 read committed 和 repeatable read 2个隔离级别下工作.

在MVCC的机制下,mysql InnoDB(默认隔离级别)的增删改查变成了如下模式:

• INSERT:
  InnoDB 为新插入的每一行保存当前系统版本号做为行版本号。
• DELETE:
  INNODB 为删除的每一行保存当前系统版本号作为行删除标识
• UPDATE:
  InnoDB 为插入的每一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识.
• SELECT:
  InnoDB只查找版本早于当前事务版本的数据行(行的系统版本号小于等于事务的系统版本号)
  行的删除号要么未定义,要么大于当前事务版本号,这样可以确保事务读取到的行,在事务开始之前未被删除.

事务隔离级别

• READ_UNCOMMITTED
  这是事务最低的隔离级别，它充许另外一个事务可以看到这个事务未提交的数据。
• READ_COMMITTED
  保证一个事务修改的数据提交后才能被另外一个事务读取，即另外一个事务不能读取该事务未提交的数据。
• REPEATABLE_READ
  保证一个事务相同条件下前后两次获取的数据是一致的
• SERIALIZABLE
  事务被处理为顺序执行。
  解决所有问题

Mysql默认的事务隔离级别为repeatable_read

参考

浅析事务是什么、mysql是如何实现事务提交和回滚的、保证事务持久性redo log的实现原理、保证事务一致性undo log的实现原理、事务ACID特性及其实现原理
参考URL:
01 | 基础架构：一条SQL查询语句是如何执行的？
参考URL: https://time.geekbang.org/column/article/68319