MySQL的日志与缓存

要搞清楚MySQL的日志与缓存那么首先必须知道语句的执行流程。

MySQL与InnoDB的日志种类

再开始说明语句的更新流程之前，先来说明一下MySQL与InnoDB的几个重要的日志文件，他们是：binlog、redo_log与undo_log

binlog

binlog是MySQL的归档日志日志，记录的是语句本身执行的逻辑，并没有记录数据文件内容或者修改记录，因此它没有crash-safe的能力。
它主要有如下的作用：
1、用于主备同步，以实现MySQL的高可用与读写分离。
2、用于恢复数据到历史版本，如果DBA承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有的binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。
3、数据的最终一致性，比如可以通过第三方组件如canal模拟MySQL的主从机制，订阅binlog，然后同步数据到kafka，redis等组件。

binlog配置项

1、log-bin用于设置日志保存的路径，如

log-bin=/var/lib/mysql/mysql-bin

2、binlog_format
binlog的记录格式，可以取如下的值：
A.STATEMENT模式。每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致。比如我的博客《事务的ACID特性与隔离性分析》下的“幻读的危害”一节的例子2
B.ROW模式。不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。不会出现一致性问题，而且由于记录了详细的日志，有助于误操作数据后的恢复，缺点是会产生大量的日志。
C.MIXED模式。综合了STATEMENT与ROW的优点，对于会导致一致性问题的SQL会使用ROW格式，对于不会出现一致性问题的SQL会用STATEMENT格式。

3、expire_logs_days
binlog文件保留天数，默认是0，也就是永久保留

4、max_binlog_size
用于控制单个binlog文件的大小，默认是1G。binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

5、sync_binlog
用于控制事务提交时，binlog的刷盘策略。可以取如下的值：
A.sync_binlog=0。每次事务提交时，MySQL只调用write函数写binlog到文件系统的缓冲区中，什么时候刷新到磁盘由操作系统决定，有丢失数据的风险，一般不推荐使用。
B.sync_binlog=1。每次事务提交时，MySQL调用write与fsync函数写binlog到文件系统的缓冲区中，没有丢失数据的风险，推荐使用。
C.sync_binlog=n(n>1)。每次事务提交时，MySQL调用write函数写binlog到文件系统的缓冲区中，但是一旦累积n个事务，就调用fsync函数刷新到磁盘，有丢失数据的风险，但最多丢失n条，如果对性能有很高的要求，可以根据实际情况设置，一般不超过1000。
6、binlog_cache_size
binlog缓存大小，由于MySQL需要保证每个事务的binlog需要连续的，中间不能插入其它事务的日志，为此需要每个线程维护一个，事务提交的时候再一次性的写盘。其默认值为32768，如果事务很大，binlog_cache放不下，那么就需要使用磁盘缓存，这也是我们要避免大事务的原因之一。
7、binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count
binlog_group_commit_sync_delay表示延迟多少微秒后才调用，0表示关闭组提交；
fsync，binlog_group_commit_sync_no_delay_count表示提交的事务累计到多少才调用fsync
它们是或的关系，只要一个满足条件了就调用fsync。它们是binlog的组提交机制，如果想要安全的提高MySQL的吞吐量可以把这2个值设置为非0值，相应的SQL的执行时间也会变长。

redo_log

redo_log是InnoDB特有的，由于MySQL的binlog没有crash-safe的能力，因此InnoDB就引入了redo_log，以支持crash-safe的功能。那为什么要引入redo_log日志才能支持crash-safe能力呢？我们每次提交事务都直接更新数据文件不就行了，由于InnoDB数据文件的存储结构，如果每个事务提交都直接更新数据文件，那么一个事务可能要跟新磁盘的好几个数据页，硬盘的随机写比顺序写慢，特别是机械硬盘。为此InnoDB通过引入redo_log，不但为了crash-safe能力，还为了将多次随机写转换为一次顺序写，提高事务的写入速度。这就是我们经常说的MySQL的WAL机制（write ahead logging）。
redo_log里面记录的是数据文件的修改记录，比如把某某个数据页修改为某某。它一般由多个文件组成，多个文件组成了一个环，是循环写入，它的写入方式与循环队列非常相似，它包含write_pos与check_point两个指针。
write_pos表示要写入位置，当写入日志后，write_pos向前移动，如果移动到最后一个文件的最后位置，则移动到第一个文件的开头位置。
check_point表示日志相对的脏页已经刷盘，日志可以安全的删除（覆盖），当刷新脏页成功后，check_point向前移动，如果移动到最后一个文件的最后位置，则移动到第一个文件的开头位置。
write_pos与check_point相等时，表示日志文件是空的，没有数据要擦除。
write_pos+1与check_point相等时，表示日志文件满了，没有办法写入新的日志，InnoDB需要赶快擦除日志，刷新脏页到数据文件（磁盘），此时事务会堵住，直到有空余的位置写入日志。

redo_log配置选项

1、innodb_log_buffer_size
用来设置缓存还未提交的事务的缓冲区的大小，通俗来说也就是日志缓冲区的大小。一般默认值16MB是够用的，但如果事务之中含有blog/text等大字段或者是一个大事务，这个缓冲区会被很快填满会引发额外的IO负载，这也是要避免大事务的原因之一。可通过查看innodb_log_waits状态，如果不为0的话，则需要增加innodb_log_buffer_size。

2、innodb_log_files_in_group
参数innodb_log_files_in_group指定日志组个数。默认为2个日志组。

3、innodb_log_file_size
用于设定MySQL日志组中每个日志文件的大小。如果参数innodb_log_file_size设置太小，就会导致MySQL的日志文件频繁写满，导致刷新脏页（dirty page）到数据文件（磁盘）的次数增加。从而影响数据库性能。如果参数innodb_log_file_size设置太大的话，虽然大大提升了IO性能，但是当MySQL由于意外（断电，OOM-Kill等）宕机时，二进制日志很大，那么恢复的时间必然很长。而且这个恢复时间往往不可控，受多方面因素影响。所以必须权衡二者进行综合考虑。对于一个高并发的数据库，一般设置为4个1GB的日志文件就足够了。

4、innodb_flush_log_at_trx_commit
用于设置事务提交时，redo-log的刷盘策略。可以取如下的值：
A.innodb_flush_log_at_trx_commit=0，每次事务提交时，InnoDB写完redo_log buffer就返回，然后由InnoDB的主线程每隔1秒刷新到磁盘，InnoDB有丢失数据的风险；一般不推荐设置。
B.innodb_flush_log_at_trx_commit=1，每次事务提交时，InnoDB写完redo_log buffer且调用操作系统的write与fsync成功才返回，InnoDB没有丢失数据的风险；从数据的安全性与完整性角度，推荐这个设置。
C.innodb_flush_log_at_trx_commit=2，每次事务提交时，InnoDB写完redo_log buffer且调用操作系统的write成功就返回，redo_log的完整性依赖于操作系统与InnoDB的主线程每隔1秒的刷新，InnoDB有丢失数据的风险，但是相对于innodb_flush_log_at_trx_commit=0的情况还是好很多，同时又兼顾了效率；为此如果对性能有很高的要求，可以设置为2，而不设置为0。
我们一般说的MySQL的双1设置，就是指的是sync_binlog=1与innodb_flush_log_at_trx_commit=1。可以保证数据库异常重启后，binlog与数据都不会丢失。下文就以双1的配置来介绍日志的2阶段提交。

5、innodb_io_capacity
值越到刷新脏页的速度越到，对于一个高性能的SSD硬盘一般可以设置成1w以上。可以通过如下的命令测试硬盘的随机读写能力：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

如果MySQL的写入速度很慢，TPS 很低，但是数据库主机的IO 压力并不大。那很可能就是这个原因导致的。

6、innodb_flush_neighbors
当innodb_flush_neighbors设置为1时，当要刷新一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个脏页也一起刷掉；而且如果邻居的邻居也是脏页，也会一起刷盘，也就是这个过程会继续蔓延。这个优化对于机械硬盘时代还是很有意义的，但是对于SSD硬盘建议关闭。

redo_log 刷盘时机

1、后台线程每隔1秒刷新redo log_buffer的数据到磁盘；
2、若innodb_flush_log_at_trx_commit=1，事务提交时；
3、redo log_buffer的空间即将占用超过1半。
这三种情况刷盘其实都是将redo log_buffer的数据写入磁盘，但是redo log_buffer是按语句执行顺序写入的，为此可能会将多个事务的redo log写盘，也有可能写入了一个事务的一部分redo_log。

redo_log应用时机

1、redo_log写满了，也就是上文提到到“write_pos+1与check_point相等时，表示日志文件满了，没有办法写入新的日志，InnoDB需要赶快擦除日志，刷新脏页到数据文件（磁盘），此时事务会堵住，直到有空余的位置写入日志。”，这种情况我们需要避免。
2、InnoDB脏页比例达到阈值了，这种情况我们也需要避免
3、InnoDB后台线程定期刷新。

InnoDB 的Change Buffer

Change Buffer是一种用于缓存二级索引页变化的特殊数据结构，是缓冲池中一块独立的区域。当需要修改的二级索引页不在缓冲池中而在磁盘中时，会将这些索引页的变化缓存在Change Buffer中。从定义中可以看出，Change Buffer可以减少随机读的发生。
但是Change Buffer也不能无限的大，为此InnoDB需要定期的合并Change Buffer到数据页，合并的过程如下：
1、从磁盘读入数据页到InnoDB Buffer Pool；
2、将Change Buffer的数据应用到InnoDB Buffer Pool的页，相应的页变成脏页。
那么什么时候会触发合并呢？
1、当需要读取Change Buffer相应的数据页的时候，如某条SELECT语句使用了Change Buffer对应的二级索引；
2、InnoDB后台定期合并；
3、数据库正常的shutdown；
4、当Change Buffer占用InnoDB Buffer Pool的比例达到一定的阈值时，就需要合并Change Buffer到数据页。这个阈值由如下的参数控制：

show variables like 'innodb_change_buffer_max_size';

它指的是change buffer允许占缓冲池总大小的最大比例，默认值为25，最大值为50。
那么什么场景会导致Change Buffer使用不上呢？
1、当二级索引是唯一索引的时候，由于每次更新索引信息都要判断数据的唯一性约束，因此需要从磁盘中读入相应的页到缓存，然后判断是否满足唯一性约束。因此如果业务逻辑已经可以满足唯一性约束了，那么可以把数据库的索引设置成非唯一索引。注意这里是以业务逻辑能够满足唯一性为前期的，比如归档数据库，由于之前已经满足了唯一性约束了，此时我们可以设置成非唯一索引。但是你可能会说设置成非唯一索引可能会导致查询变慢了，因为非唯一索引InnoDB每次都会扫描到第一个不满足的条件为止，其实对于多扫描一行的问题，多扫描的那一行大概率在内存中了，其实就是多一次比较问题，对于性能的影响不大。但是再可重复读下，会导致锁住更多的数据，但是我们可以通过添加limit 1限制，解决锁范围变大与多扫描一行的问题，也可以把隔离级别改成读提交。
2、写完数据后，立马读，此时虽然能利用上Change Buffer了。但是保存到Change Buffer后，立马又要从磁盘读入数据，合并到数据页，反而会导致效率变低。因为此时相当于Change Buffer没有利用上，但是却多了Change Buffer的维护代价。
更多Change Buffer见《innodb-change-buffer》