innodb存储引擎笔记（下）_innodb_max_dirty_pages_pct 和innodb_flush_log_at_tr

mysql 5.6 optimizer_trace 查看执行计划选择的过程

MVCC

• innodb的多版本使用undo&回滚段来构建
• innodb是聚集索引组织表，每个行记录有3个额外属性：ROW_ID、TRX_ID、ROLL_PTR
• undo记录了更改前的数据镜像，若事务未提交，对隔离级别大于等于read commit的其他事务，它们不应该看到已修改（未提交）的数据，而应一致读取老版本的数据
• 在修改聚集索引记录时，总是同时存储了ROLL_PTR和TRX_ID，可通过该ROLL_PTR找到对应的undo记录，通过TRX_ID来判断该记录的可见性
• 当旧版本记录中的TRX_ID指示对当前事务不可见时，则继续向前（更新的TRX_ID）构建，知道找到一个可见的记录。
• innodb在表空间中保存行的旧版本信息，这些信息被保存在回滚段中
• 事务标识符（TRX_ID）指示最后插入或更新这个行的事务标识符，删除标志也被认为是一个更新，因为它在提交前指示在杭商做了一个标记。
• 回滚指针（DB_ROLL_PTR）指向一个由回滚段写入的undo日志记录。如果一个行被更新了，undo日志记录包含了重建这行更新前的信息的一些必要数据
• 回滚段中的undo日志分为插入日志（主要是insert操作）和更新日志（包含update和delete）。插入日志仅在事务回滚的时候有用，事务提交之后就可以马上删除掉。更新日志在一致性读的时候需要使用，如果当前没有事务再可能使用回滚段中的记录的时候，这些记录就可以删除掉了。因此，最好以适当频率提交事务，否则innodb不能删除掉过期更新日志，回滚段越来越大。
• 回滚段中undo日志记录的物理大小要比其对应的插入或者更新的行要小（只记录修改的列，不记录完成的列）
• 当删除某一行时，改行并不会马上从数据库的物理文件上移除，只有当innodb可以清除更新日志记录的时候，那些行机器对应的索引记录才会真正从物理上删除掉，这个清楚操作成为purge。purge以前需要由主线程来掉地，现在5.6版本已经分离出来了

redo

• ib_logfile文件个数由innodb_log_files_in_group配置决定（至少>=2）,文件名序号从0开始，从ib_logfile0到ib_logfileN
• 文件为顺序写入，循环使用，当达到最后一个文件末尾时，会从第一个文件开始顺序复用，redo文件切换时，会执行一次checkpoint（刷redo log，刷dirty page）
• 实例重启的过程中，实例关闭以后（正常关闭，不是崩溃），redo是可以删掉的，实例开启之后会重新初始化redo，但是undo不能删
• redo log用于记录事务操作变化，记录的是数据被修改之后的值（undo记录的是数据被修改之前的值）
• 不会记录临时表空间上的变化（mysql 5.7起开始有独立的临时表空间）
• redo记录的是逻辑操作，类似binglog，不像oracle是块记录。它需要应用到一个正确的page上面，如果该page本身被破坏了，则无法恢复出正确的数据，所以需要用到double write buffer
• 会先放在log buffer（innodb_log_buffer_size）中，而不是立即写磁盘
• LSN:log sequence number，递增的证书，表示redo总字节数
• 每次写盘后是否flush，由参数innodb_flush_log_at_trx_commit控制
• 从5.5开始，redo的大小不再影响crash recovery的耗时，只影响checkpoint频率，设置较大值可减少IO消耗

redo log buffer刷新条件

• master thread每秒进行刷新
• redo log buffer使用大于1/2进行刷新
• 事务提交时进行刷新
  
  • innodb_flush_log_at_trx_commit={0|1|2}

• 设置innodb_flush_log_at_timeout（默认1秒）

• innodb_flush_log_at_trx_commit

  • 0，事务提交时不将redo log buffer写入磁盘
  • 1，事务提交时将redo log buffer写入磁盘
  • 2，事务提交时将redo log buffer些人操作系统缓存
  • 通常建议设置为1，并且设置sync_binlog=1，以保证数据可靠性（双1）
• innodb_log_buffer
  
  • 通常8-32M就足够了
• innodb_log_file_size
  
  • 一般设置为512M-4G
• innodb_log_files_in_group
  
  • 至少两个文件

redo log和binlog的区别

• redo是物理逻辑日志，binlog是逻辑日志
• redo是发起时间顺序存储，而binlog是按事务提交时间顺序存储
• redo log file循环使用，binlog每次新增一个文件
• binlog更像oracle里的redo归档

undo

• 用于实现MVCC以及回滚
• 当我们对记录做了变更操作时就会产生undo记录，记录变更前的旧数据
• undo记录中存储的是老版本的数据，当一个旧的事务需要读取老版本数据时，为了能读到老版本的数据，需要顺着undo链找到满足可见性的记录。当版本链很长时，通常可以认为这个是比较耗时的操作。
• undo记录默认被记录到系统表空间（ibdata*）中，但是从5.6开始，也可以使用独立的undo表空间
• 大多数对数据的变更操作包括insert/delete/update
• 其中insert操作在事务提交前只对当前事务可见，因此产生的undo日志可以在事务提交后直接删除，归类为insert_undo
• 而对于update/delete则需要维护多版本信息，在innodb里，update和delete操作产生的undo日志被归为一类，即update_undo
• MySQL5.7在undo上面的变化
  
  • innodb_undo_logs，rollback seg数量，比如将2G的undo tablespace，切分成多少分rollback seg。默认128个，实例初始化后不可再修改。每个undo log seg可以最多存放1024个事务
  • innodb_undo_tablespaces，undo log文件数，每个文件默认10MB，数量默认0，最大95个，最小2个，因为在truncate一个undo log文件时，需要保证另外一个是可用的，这样就无需停止业务了
  • innodb_max_undo_log_size，控制最大undo tablespace文件的大小，超过这个值尝试truncate undo logs，truncate后的undo logs大小默认恢复为10M
  • innodb_purge_rseg