mysql先写日志再写磁盘_Mysql原理(四) 数据与日志

在Mysql中经常会听到WAL机制(Write-Ahead-Logging),主要讲了数据在操作的时候先进行写日志，然后再将数据写入磁盘的过程。之前提到过Mysql中的两个重要的日志文件redo log和bin log。

redo log：Mysql分配给redoLog固定大小区域记录日志，该日志采用循环写从头写到尾然后在从头，同时还有一个记录擦除日志位置的标记checkpoint。当写入点和checkpoint在同一个位置，则停止写日志。如果数据库异常后，提交后的记录不会丢失称为crash-safe

bin log：称为归档日志，是追加写不会覆盖之前的日志。所以可以根据binlog日志可以完成主从备份、数据订阅、恢复数据等工作。

之前也提到mysql执行一条update语句遵从两阶段提交过程，整个流程如下

1.先通过引擎找到对应的行数据

2.对行数据进行修改并调用引擎接口修改这条数据

3.redo log记录更新流程此时处于prepare状态，告知mysql执行器完成操作

4.执行器生成binlog并写入磁盘

5.执行器调用引擎提交事务接口，redo log状态变为commit状态完成整个更新操作。

Redo log进行flush操作

Mysql在记录redo log的时候会先将数据写入到内存中，然后通过flush将内存中的数据写入磁盘中。再次期间会产生脏数据页导致内存和磁盘的数据不一致。这时候mysql就需要刷脏数据页。

Mysql什么时候执行flush操作？

1.当记录redo log的内存满了，会停止写入redo log操作。

2.写入日志太多，发现分配的内存不够，这时候需要淘汰一部分数据页。

3.mysql空闲时会进行flush操作

在flush过程中如果存在读入的数据页没有内存的时候，需要到缓冲池中申请数据页。当数据页不足首先将最久不使用的数据从内存中淘汰，如果是脏数据页还得先进行刷盘才能复用。可是在刷脏页过程中，若淘汰的数据页太多或者日志写满会导致mysql性能降低。所以InnoDB会有刷脏页的控制策略

InnoDB刷盘影响因素：脏页的比重、redo log写盘速度

InnoDB_max_dirty_pages_pct脏页比重上线默认为75%，合理的设置innoDb_io_capacity的值不要超过上限。

同时在刷盘的过程中，mysql会将旁边的数据页也会刷入。InnoDB通过innodb_flush_neighbors参数用来控制这个行为。1表示会刷入相邻的数据2.表示只刷自己。如果磁盘写性能高最好设置为0，减少sql响应时间。

如果mysql在执行操作过程中出现抖动可能由于mysql在flush操作。

Mysql在执行Binlog和redoLog过程有时什么样子呢？

binlog写入机制

执行事务过程，先将日志写入binlog cache，事务提交时再将cache写入到binlog中。对于cache系统会分配一片内存，每个线程一个，参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。超过这个参数则暂存在磁盘中。

事务提交时，执行器将binlog cache完整事务写入binlog中，并清空binlog cache。

整个写入过程分为两步 write和fsync

write：指把日志写入到文件系统page cache，并没有写入磁盘中，速度快

fsync：将数据持久化到磁盘，该过程占用磁盘IOPS。

sync_binlog参数

等于0：表示每次提交事务只write不fsync

等于1：表示每次提交事务都执行fsync

等于n：表示事务在write后，会累积N个事务后才fsync。

为了提升性能，实际场景考虑丢失日志量的可控性会设置到100-1000之间的值。风险：主机发生异常重启，会丢失N个事务binlog日志。

redoLog写入机制

首先确定redo log在写入的时经过redo log buffer、FSPage cache、hard disk

redo log buffer：物理上是Mysql进程内存

FSPage cache：写入磁盘但没有持久化，物理上是在文件系统page cache中hard disk 持久化到磁盘

为了控制redo log写入策略，innodb_flush_log_at_trx_commit参数

等于0，表示事务提交只将redo log留在redo log buffer

等于1，表示每次事务提交都将redo log持久化到磁盘中

等于2，每次事务提交只把redo log写入page cache中。

InnoDB后台线程，每秒把redo logbuffer中日志，调用write写入到文件系统page cache，然后调用fsync持久化到磁盘。

所以在事务执行过程中redo log直接写入redo log buffer，然后隔一秒调用write写入到page cache中，最后持久化到磁盘中。所以说没有提交事务的redo log也有可能持久化到磁盘中。

还有场景会将redo log buffer写入磁盘

1.redo log buffer占用空间达到innoDB_log_buffer_size一半，后台线程主动写入

2.并行事务提交时顺带将这个redo log持久化到磁盘中。

innoDB_flush_log_at_trx_commit设置为1，事务B将redo log buffer里日志全部持久化到磁盘。会带上事务A里的日志。

双1配置：sync_binlog和innoDb_flush_log_at_trx_commit都设置为1，一个事务完整提交需要两次刷盘，一次redo log(prepare)一次binlog。

但是Mysql测试TPS每秒2万，刷盘得四万次？

日志逻辑序列号(LSN)用来对应redo log写入点。LSN记录InnoDB的数据页，保证数据页不会被多次执行重复redo log。

redo log组提交：

比如有三个事务，先到达的会选为组leader。当组成员达到3个，事务1会带着3个事务一起持久化，事务2和3直接返回。

所以一次组提交过程组成员越多，fsync越晚调用，节约IOPS效果越好。

mysql有两个参数

binlog_group_commit_sync_delay：表示延迟多久调用fsync

binlog_grouo_commit_syn_no_delay_count：表示事务累积多少次调用fsync

这两个条件只要满足其一就会调用fsync。

mysql出现性能瓶颈，瓶颈为IO上，可以有哪些方式提升？

1.设置binlog_group_commit_sync_delay和binlog_grouo_commit_syn_no_delay_count减少binlog写盘次数，增加语句响应时间，不会丢数据

2.sync_binlog设置大于1(常见为100-1000)但有风险主机异常会丢失binlog日志。

3.innoDb_flush_log_at_trx_commit设置2，主机掉电会丢数据 innoDb_flush_log_at_trx_commit为0时，如果mysql本身异常重启也会丢数据。

最终crash-safe保证

客户端收到事务成功，事务一定持久化了

客户端收到事务失败，则一定失败

客户端收到异常，需要重连数据库连接，数据只要保证内部(数据和日志、主库和备库一致)就可以