概述
上篇简述了mysql更新语句执行流程，其中很多步骤我都“留了白”，未深入展开，本篇我将通过一个个问题深入解析各个步骤。
流程图
问题1 缓存池中的数据什么时候写入磁盘？
思考：
- 缓存是为了提高更新效率，所以不会每次递交事务就写入磁盘。
- 缓存池是内存，容量有限，必然存在类似“非热点数据” 淘汰的问题。
- 为了提高效率，不会每次全量写磁盘，因为每次加载按“page”加载，所以淘汰至少也是“page” 来。
- 淘汰的数据，如果和磁盘中数据一样，可能就不需要写磁盘，直接淘汰即可。
那实际上，缓存写磁盘策略是什么呢？
1.1 “page”分类
为了区分淘汰的page页是否需要写磁盘，mysql将缓冲池中的page，区分为三个状态：
- 未使用的页: InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。
- 干净的页:缓存中和磁盘内容相同，从磁盘加载到内存，没有被更新过的page就是干净页
- 脏页: 缓存和磁盘数据不同。 数据更新，未写入磁盘时的page。
1.2 实际策略
当缓冲池写满时，当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个page，这时候只能把最久不使用的数据页从内存中淘汰掉。 如果是干净页，就直接淘汰。如果是脏页，需要先将该数据页写入磁盘，再将数据淘汰。
redo-log写满也会导致缓冲池刷脏页。后边redo-log部分详解。
1.3 性能问题
如果缓冲池写满，要读入的数据不在内存中 page较多时，就需要淘汰更多page，随机写就更多，影响查询性能
当Redo-log写满时，新的更新操作导致check-point移动，旧更新操作需要淘汰，如果淘汰的数据在内存中“脏页”太多，导致写磁盘操作过多，必然影响新的更新操作的性能。
1.4 性能优化
1.4.1 控制脏页比例
- innodb_max_dirty_pages_pct : 脏页比例上限，默认值是 75%
1.4.2 加快写磁盘的速度
mysql 通过监控当前脏页比例，结合磁盘io性能，动态调整脏页写磁盘的速度。 控制策略如下：
1）通过 innodb_io_capacity 参数，告诉mysql最大可利用磁盘io。
主流io测试工具有：
- fio:在Linux系统下使用比较方便
- iometer:在window系统下使用比较方便
- Orion是oracle的IO测试软件
设置值不能设置太低，太低无法充分利用磁盘io能力，导致并发不足。实际生产环境中，因为该参数导致性能很低很常见。
2）监控当前脏页比例 M
3）动态计算公式
F1(M)
{
if M>=innodb_max_dirty_pages_pct then
return 100;
return 100*M/innodb_max_dirty_pages_pct;
}
F2(N)
InnoDB 每次写入的日志都有一个序号，N为当前写入的序号跟 checkpoint （下文Redolog详解）对应的序号之间的差值。逻辑复杂.只要知道 N 越大，算出来的值越大就好了.
根据F1和F2得出一个1到100的数字R，让后按照R%* innodb_io_capacity来刷脏页
1.4.3 刷邻居 连坐机制
准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷
好处
减少随机IO
坏处
可能是的原本已经慢了查询，更加慢。
控制
innodb_flush_neighbors=0 :表示不开启“连坐”机制。如果使用的是 SSD 这类 IOPS 比较高的设备的话，建议把 innodb_flush_neighbors 的值设置成 0。因为此时io不是瓶颈，只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间
-
innodb_flush_neighbors=1 ：表示开启
1.4.4 Buffer Pool 大小调整
默认大小
innodb buffer pool chunk size： 缺省128
innodb buffer pool size: 128M
mysql每次按chunk申请内存，知道最大值
控制部分占用
mysql维护这个缓存，大约占用30%到35%.包括：
- 空闲列表
- 使用列表
- LRU： 最近最少。 Java实现LRU —— 大厂常用算法 。
1.mysql利用划分区域的方式实现LRU。 冷三十几，热6十几的比例。
2.全表扫描短时间内读区某一个页内所有数据，导致某个页瞬间很热，但是后续实际上不一定。所以改进
3.改进很多
通用调整
设置总内存的70%左右，通过观察缓存命中率调整
mysql自身优化
- 多实例 （n） 多个缓存实例，增强并发性能，1<n<64 当n>1,但是总size不到1G，那真实只有1个
问题2 Redo-Log Buffer 何时写入Redo-Log 文件
Redo-log文件结构
Redo-log是一个大小固定文件，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，总共就可以记录 4GB 的操作，从头开始写，写到末尾就又回到开头循环写.
redo-log
如上图，write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件,即推动Buffer Pool刷脏页。脏页太多，随机写磁盘操作就多，反过来影响更新操作的性能。
Redo-Log Buffer 主要作用
通常，我们增加内存，是为了减少磁盘随机io操作，但是Redo-Log是顺序写，那Redo-Log Buffer主要作用是什么呢？
1.容易想到的， mysql 有一个通用策略，能用内存的地方尽量用内存。内存替代顺序读，也不是不可以接受，但其实，这不是主要作用
- 更重要原因是Redo-log是固定大小的。 极端情况下，如果一个事务多个更新操作，中间某个操作，正好redo-log写满了，又会降低更新操作的性能，直接写文件增加这种风险的概率。
redo-log 三种状态
- 存在 redo log buffer 中，物理上是在 MySQL 进程内存中，标记为红色状态
- 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，标记为黄色状态
- 持久化到磁盘，对应的是 hard disk，标记为绿色状态
日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了
写入策略
InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，控制写入，它有三种可能取值：
- 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中
- 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘
- 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache
补充策略
InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘.
redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。没有调用 fsync，也就是只留在了文件系统的 page cache中。
并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘.并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。
假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。这种情况会持久化到磁盘。
显然，以上三个补充策略，都可能导致未递交的事务写入磁盘，不一定fsync
双1策略
生产上Redo-log和bing-log搭配使用，同时将innodb_flush_log_at_trx_commit 和 sync_binlog参数同时设置为1。此时 redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的。具体详见mysql innodb 如何实现crash-safe。
问题3 binlog 的写入机制
binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。
binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中
需要注意的是，binlog一个重要作用是主从同步，一旦binlog持久化到磁盘，数据就会从主同步到从，因为一个事务的 binlog 是不能被拆开的，不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。
系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。
binlog写入机制
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 日志。
问题4 redo log 和 binlog 的不同
- redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
- redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
- redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
问题5 MySQL 是如何保证 crash-safe
通过mysql更新流程，我们知道更新步骤中涉及到很多内存操作，如果只讲数据放入内存，就存在数据丢失的情况。
- 在mysql缓冲池中，mysql异常重启丢失数据;
- 在page cache中，机器重启丢失数据
随着硬件的不断发展，相对于前者，后者发生的概率要低很多。
实际上，mysql创建之初，并没有crash-safe能力。只有InnoDB引擎才有，实现的基础就是 Redo-log。
更新简要流程.png
首先，简要更新流程中，缓冲池数据写入磁盘无法控制，时刻3，4，7，8都可以通过参数设置保证数据写入磁盘文件。crash后，缓冲池数据丢失，此时数据可能没有写入磁盘。
前6个时刻发生crash，客户端都会认为操作失败，mysql重启也会回滚内存数据。
如果在时刻8之后crash，mysql重启后，可以根据redolog数据恢复到缓冲池中。
问题主要在时刻8之前crash。此时mysql重启，发现redo-log状态为prepare，则判断对应的事务 binlog 是否存在并完整：
a. 如果是，则提交事务；(时刻7后)
b. 否则，回滚事务。（时刻7前）
问题5 redo-log 为什么必须有“两阶段提交”
redo log 和 binlog 是两个独立的逻辑，类似于分布式系统的两个模块。redo-log用于数据恢复，binlog用于数据备份。
- 先redo-log，后binlog。 假设redo-log完成后，binlog崩溃，用redo-log恢复数据主库，binlog同步备库，出现出局不一致。 可能你会想，用redo-log恢复数据时，去binlog查询下是否有完整数据，有才恢复。 那这样所有redo都不能独立工作了。
- 先binlog，后redo-log。 binlog记录完成，系统崩溃，redo-log失败。系统恢复，主库无法恢复系统崩溃前binlog记录的那条数据。主备不一致。
问题6 MySQL 怎么知道 binlog 是完整的？
一个事务的 binlog 是有完整格式的：
- statement 格式的 binlog，最后会有 COMMIT；
- row 格式的 binlog，最后会有一个 XID event。
另外，在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现
问题7 redo log 和 binlog 是怎么关联起来的?
它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：
- 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
- 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。
问题8 是否能只有 binlog
首先我个人觉得这个问题就不应该问。 binlog时Server层的，设计之初就不具备crash-safe能力，只是用于数据备份的。 所以从历史角度回答否。这个问题就变成了能不能改造binlog让其具备恢复数据的能力。
- 从是否能实现的角度，当然可以。增加类似redo-log的机制就可以
- 从是否合理的角度， 不可以。 开闭原则，如果要改造binlog让其具备crash-safe能力，相当于完全重构了，不是简单的扩展就可以实现。
问题9 是否能只有 redolog
- 历史角度，不可以。 先有binlog后有redo-log
- 能否实现角度，不可以： 单独考虑crash-safe,redo-log可以独立完成，但是redo-log时固定大小的，没有存储完整数据，不能用于系统备份。
问题10 “双1”配置优化
所谓“双1”指：
- innodb_flush_log_at_trx_commit=1， redo-log每次事务提交时都将 redo log 直接持久化到磁盘。
- sync_binlog sync_binlog=1 的时候，表示每次提交事务都会执行 fsync
即：每次递交事务，redo-log和binlog都会刷盘。
但是
其实，双1配置下，redo-log在commit时并不会fsync，只是记录到page cache中，因为redo-log在prepare时需要支持系统恢复，所以在prepare阶段会fsync，到commit时，mysql认为没有必要在刷盘了。
问题
“双1”是否意味着我从 MySQL 看到的 TPS 是每秒两万的话，每秒就会写四万次磁盘? 答案是否，这里用到了组递交（group commit）
LSN
日志逻辑序列号（log sequence number，LSN）的概念。LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。
LSN与组提交
假设三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段，对应的 LSN 分别是 50、120 和 160。
1.trx1 是第一个到达的，会被选为这组的 leader；
2.等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
3.trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；这时候 trx2 和 trx3 就可以直接返回了。
这就是组递交。组员越多，节约磁盘 IOPS 的效果越好
优化
图1为两阶段递交过程。 图2拆解了图1步骤，可以看到。 redo-log的fsync在binlog的write前，binlog的fsync在 redo-log的fsync之后，这样 相当于增加了各自 write和fsync之间的时间间隔，使得组提交效果很好。图2中第三步其实很快，所以binlog的组提交效果不是很好。可以通过参数控制：
- binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用sync;
- binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。
两个是或的关系。
WAL
WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘.
可能会想WAL 机制是减少磁盘写，可是每次提交事务都要写 redo log 和 binlog，这磁盘读写次数也没变少呀
- WAL 机制主要得益于两个方面：redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
- 组提交机制，可以大幅度降低磁盘的 IOPS 消耗。
