mysql写操作优化

日志

binlog

1. 定义：binlog是mysql归档日志，用于记录“修改数据或可能引起数据变更”的sql语句，不记录查询语句。
2. 作用：1.主从复制；2.数据恢复。
3. 三种格式：statement、row、mixed。statement记录的是sql语句，日志量小，但不够准确，会因为索引选择、limit等因素导致主从数据不一致；row记录的是受影响的数据行，不会有不一致的问题，但日志量大；mixed由mysql自行判断sql是否引起主从不一致，如果会就使用row，反之用statement。生产上一般设置为row格式来保证数据一致性。

WAL和redolog

Mysql使用WAL（Write Ahead Logging）技术来提升DML性能：先写日志，即redo log，再异步写数据，虽然都是写磁盘，但把随机IO变成了顺序IO，提升了写数据的性能。

1. redo log由InnoDB引擎实现，可以在数据库异常重启时恢复数据（内存中的脏页），即所谓的crash-safe能力；
2. redo log使用Physical Logging，以页为单位记录，记录的是某一页内哪一行做了哪些修改，相对的binlog使用的是Logical Logging，记录的是某张表内哪一行做了哪些修改；
3. redo log有固定空间，循环写，当空间用完时需要将日志刷盘；

change buffer

通过redo log将随机IO转变为顺序IO，InnoDB对DML的另一个优化就是引入了change buffer来减少磁盘的随机读。
当执行一条更新语句时，如果buffer pool中不存在需要更新的数据，在没有change buffer的情况下，mysql会将数据页从磁盘读入内存，再更新内存；有了change buffer以后，mysql直接将更新操作记录在change buffer，不会从磁盘加载数据，唯一索引的更新操作例外，因为唯一索引需要将数据读入内存来确保更新满足唯一性约束。

两阶段提交

DML会同时写redolog和binlog，InnoDB通过两阶段提交来保证两个日志的一致性：1.先写redo log，标记为prepare状态；2.写binlog；3.修改redolog，将状态改为commit。

一、为什么要保证redolog和binlog的一致性？
1.如果redolog有记录，binlog无记录，数据库异常重启时，本机通过redolog重放可以恢复数据，但由于binlog无记录，从库无法获取这部分更新，导致主从不一致；
2.如果binlog有记录，redolog无记录，数据库异常重启时，本机无法恢复数据，从库反而可以通过binlog获取这部分丢失数据，导致主从不一致。

二、为什么两阶段提交可以保证redolog和binlog的一致性？
1、如果redolog写失败，redolog和binlog都没有数据，主从一致，便是晴天；
2、如果redolog完成prepare，binlog写失败，则事务会回滚，但redolog和binlog仍然一致；
3、如果redolog完成prepare，binlog完成写入，redolog commit失败，重启后会自动commit，两者还是一致。
即redolog重放前要判断状态，如果是prepare则需要判断binlog的状态，如果是commit则可以直接重放。

一条更新语句的执行流程

1. 内存中不存在目标数据页，如果要更新唯一索引，会从磁盘读取数据到内存并更新，反之将记录写入change buffer；
2. 写redo log，状态标记为prepare；
3. 写binlog；
4. 写redo log，状态标记为commit;