MySQL数据写入过程介绍

第一阶段

数据直接写入到磁盘。

问题： 速度慢

因为磁盘写入速度比内存写入速度慢很多。

第二阶段

解决方案：

数据先写入内存，后异步刷新到磁盘。

内存中脏数据什么时间刷新到磁盘？

1 、 InnoDB 的 redo log 写满了。

这时候系统会停止所有更新操作，把checkpoint 往前推进， redo log 留出空间可以继续写。

2 、系统内存不足。

当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是 “ 脏页 ” ，就要先将脏页写到磁盘。

3 、 MySQL 认为系统 “ 空闲 ” 的时候。

4 、 MySQL 正常关闭的情况。

这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

问题：

异步：数据还没完全写入磁盘后，内存或系统崩溃，数据丢失。

第三阶段

解决方案：

写入内存后，为了提高速度，并不马上写磁盘，后面会延时批量写入磁盘，同时为了数据安全，引入 redo log ，在内存写入数据时，会同时生成 redo log 数据，记录数据修改操作，用于崩溃恢复。

Redo 记录示例：

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

崩溃恢复：

如果系统崩溃了，内存的数据全部丢失，重启后，只需要按照 redo 记录重新更新一遍数据页，就可以恢复丢失的数据。

为什么 redo log buffer 写入到 redo log file 速度比脏块写入到 datafile 快？

1.redo 日志占用空间非常小。

2.redo 日志是顺序写入磁盘的，速度比随机性快。

redo log buffer 写入到 redo log file 触发条件：

1.log buffer 空间不足时。

通过系统变量 innodb_log_buffer_size 指定 log_buffer 大小，如果 log buffer 的 redo 日志量已经占满 log buffer 总空间 50% 左右时，会将日志刷新到磁盘。

2. 事务提交

事务提交时，可以不把修改过的 buffer pool 页面立即刷新到 datafile 里，但是为了保证持久性，必须要把数据修改时所对应的 redo 日志刷新到磁盘 redo log file ，用于崩溃恢复。

这个过程和 innodb_flush_log_at_trx_commit 参数有关，该参数有 3 个可选值， 0 、 1 、 2 。

参数值为 0 时：

表示事物提交时，不会立即向磁盘同步 redo 日志，这个任务交给后台线程来处理。

参数值为 1 时：

表示在事物提交时需要将 redo 日志同步到磁盘，可以保证事物的持久性，这也是默认值。

参数值为 2 时：

表示事务提交时需要将 redo 日志写入到操作系统缓冲区中，但并不需要保证将日志真正的落盘。

3.buffer pool 中脏页刷新到磁盘 datafile

buffer pool 中脏页刷新到磁盘 datafile 前，业务先执行对应 redo 日志的刷盘。

4. 每 1 秒

后台线程会以每 1 秒一次的频率将 log buffer 中 redo 日志进行刷盘。

5. 正常关闭服务器时

6. 做 checkpoint 时

问题：

1. 写入过程中，还未提交，为了避免脏读，别的会话如何读取修改前的数据。

2. 写入后悔了怎么办。

第四阶段

解决方案：

修改内存数据前，先将旧数据写入到 undo 中，可以通过 undo 中的旧数据进行一致性查询和回滚等操作。

如果没有 undo ，其他会话想要读取另一个会话正在修改还未提交的数据时，为了避免脏读，读取请求会被阻塞，直到另一个会话完成提交或回滚，这在高并发大事务下效率会很低。

问题：

因为 mysql 数据页大小 16KB ，操作系统也大小一般 4KB ，在执行一次 mysql I/O 写入时，对应 4 次 OS I/O, 这种情况被称为写失效（ partial page write ）。此时重启后，磁盘上就是不完整的数据页，就算使用 redo log 也是无法进行恢复的。

第五阶段

解决方案：

Double Write 双写

在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过 memcpy 函数将脏页先复制到内存中的 Double write buffer 。通过 Double write buffer 再分两次，每次 1MB 顺序地写入共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免缓冲写带来的问题。

问题：

如果开启了 binlog ，是先写 binlog 还是先写 redolog?

场景 1 ： 先写 redo log 后写 binlog

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

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

如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，与原库的值不同。

场景 2 ： 先写 binlog 后写 redo log

如果在 binlog 写完之后 crash ，由于 redo log 还没写，崩溃恢复以后这个事务无效，值更新失败。但是 binlog 里面已经记录了修改值的日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行的值与原库的值不同。

可以看到，无论是先写 binlog 在写 redo 还是先写 redo 在写 binlog 都存在问题。

第六阶段

解决方案：

二阶段提交：

两个场景都有问题，所以引入了 “二阶段提交”，将 redo log 的提交分为 prepare 和 commit 两个阶段。

通过二阶段提交，可以解决如下场景问题：

1.redo log(prepare) 执行失败，由于 redo log 没有 commit 标识，并且 binlog 没有写入，对应的事务直接回滚。

2.redo log(prepare) 执行成功， binlog 还没开始写入，由于 redo log 没有 commit 标识，并且 binlog 没有写入，对应的事务直接回滚。

3.redo log(prepare) 执行成功， binlog 写入了部分，系统故障，由于 redo log 没有 commit 标识，并且 binlog 文件不完整，对应的事务直接回滚。

4.redo log(prepare) 执行成功， binlog 写入成功， redo log(commit) 写入失败，检查 redo log(prepare) 成功，并且 binlog 是完整的，直接提交事务。

上述讲解的 binlog 写入是指写入到内存中，也就是 binlog cache 中，那么 binlog 如何刷盘呢？

事务 binlog event 写入流程

binlog cache 和 binlog 临时文件都是在事务运行过程中写入，一旦事务提交， binlog cache 和 binlog 临时文件都会释放掉。而且如果事务中包含多个 DML 语句，他们共享 binlog cache 和 binlog 临时文件。

整个 binlog 写入流程类似如下：

1. 事务开启 ;

2. 执行 dml 语句，在 dml 语句第一次执行的时候会分配内存空间 binlog cache;

3. 执行 dml 语句期间生成的 event 不断写入到 binlog cache;

4. 如果 binlog cache 的空间已经满了，则将 binlog cache 的数据写入到 binlog 临时文件，同时清空 binlog cache;

如果 binlog 临时文件的大小大于了 max_binlog_cache_size 的设置则抛错 ERROR 1197;

5. 事务提交，整个 binlog cache 和 binlog 临时文件数据全部写入到 binlog file 中，同时释放 binlog cache 和 binlog 临时文件。

这块和 sync_binlog 参数有关：

sync_binlog=0 时：

当事务提交之后， MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘，而让 Filesystem 自行决定什么时候来做同步，或者 cache 满了之后才同步到磁盘。

sync_binlog=n 时：

当每进行 n 次事务提交之后， MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。

但是注意此时 binlog cache 的内存空间会被保留以供 THD 上的下一个事务使用，但是 binlog 临时文件被截断为 0 ，保留文件描述符。其实也就是 IO_CACHE( 参考后文 ) 保留，并且保留 IO_CACHE 中的分配的内存空间，和物理文件描述符 ;

6. 客户端断开连接，这个过程会释放 IO_CACHE 同时释放其持有的 binlog cache 内存空间以及持有的 binlog 临时文件。

问题：

如果 mysql 服务器或硬件故障，无法及时启动数据库，如何减少服务中断和数据损失。

第七阶段

解决方案：

主从复制

如果存在从库，主库会将新增的数据产生的 binlog 日志通过 binlog dump 线程传给从库，从库通过 I/O 线程接收传来的日志并写入到 relay log 日志中，最后 SQL 线程解析 relay log 日志进行数据重放。

问题：

主从复制默认是异步复制的，在异步复制中，主库并不关心从库是否接收到完整的日志，直接会进行后面的提交操作，如果在从库还没接收完主库传来的 binlog ，这时主库故障，从库切换为主库，那么在新主库上读取的数据可能会有缺失，导致数据不一致。

第八阶段

解决方案：

半同步复制

主库将新增的数据产生的的 binlog 日志通过 binlog dump 线程传给从库，从库通过 I/O 线程接收传来的日志并写入到 relay log 日志中，最后 SQL 线程解析 relay log 日志进行数据重放。

其中在从库将日志并写入到本地 relay log 后，会给主库返回 ack 消息，告知主库也提交事务了，之后主库才会继续提交事务。

这在一定情况下解决了异步复制的问题，提高了数据的安全性，但是半同步复制还是有一些缺陷。

问题：

1. 从库将日志并写入到本地 relay log 后，主库提交事务，这时主库故障，从库切换为主库，如果 relay log 很大， SQL 线程还没有重放完成，读取新主库的数据是滞后的，数据也不是强一致的，而是最终一致的。

2. 由于安全性和性能总是对立的，安全级别越高，性能通常最差，配置半同步时需要指定超时参数 rpl_semi_sync_master_timeout 默认 10 秒，也就是主从连接超时后，主库会卡住 10 秒等待从库响应， 10 秒以后半同步就会降级到异步复制，之后如果主从连接恢复，又会自动恢复到半同步，如果主从连接一直不恢复，主从复制类型就会一直是异步复制，同样存在异步复制的缺点。