MySQL数据写入过程介绍

第一阶段

数据直接写入到磁盘。

问题：速度慢

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

第二阶段

解决方案：

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

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

1、系统内存不足。

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

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

3、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,如果执行了一次mysql I/O,操作系统只完成了3次I/O操作，例如只写入了12KB数据，这时如果系统故障，重启后，磁盘上就会出现不完整的数据页，就算使用redo log也是无法进行恢复的。这种情况被称为写失效（partial page write）。

第五阶段

解决方案：

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秒以后半同步就会降级到异步复制，之后如果主从连接恢复，又会自动恢复到半同步，如果主从连接一直不恢复，主从复制类型就会一直是异步复制，同样存在异步复制的缺点。

总结

新数据写入到数据库过程：

例如执行下面语句，将name为chen行的name列更新为cjc。

update t1 set where;

1.检查待修改页是否在内存buffer中，如果不在，将页从磁盘读取到内存中，如果已经在内存中，准备修改内存数据。

2.修改内存数据之前，先将原值name='chen'写入到undo，用于一致性读或回滚事务，当然涉及undo页修改的操作也会生成对应的redo数据，用来保护生成的undo数据。

3.开始修改内存数据，将name为chen行的name列更新为cjc。

4.生成修改数据对应的redo log buffermysql介绍，重做日志完全写入到redo log file，更新prepare标识。

5.将数据修改操作写入到binlog cache中。

6.binlog写入完成后会传到从库，从库I/O线程将接收到的日志写入到本地relay log日志中，写入完成后向主库返回ack信息，从库SQL线程读取relay log日志，进行数据重放。

7.更新redo日志的commit标识，事务更新完成，客户端可以正常返回。

8.buffer pool中脏数据会根据特定触发条件写入到Double write buffer中，Double write buffer分两次写，每1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘。

###chenjuchao 20221201###

（编辑：云计算网_宿迁站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!