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