一条SQL更新语句是如何执行的

问题总结

1. redo log的概念是什么? 为什么会存在.
2. 什么是WAL(write-ahead log)机制, 好处是什么.
3. redo log 为什么可以保证crash safe机制.
4. binlog的概念是什么, 起到什么作用, 可以做crash safe吗?
5. binlog和redolog的不同点有哪些?
6. 物理一致性和逻辑一直性各应该怎么理解?
7. 执行器和innoDB在执行update语句时候的流程是什么样的?
8. 如果数据库误操作, 如何执行数据恢复?
9. 什么是两阶段提交, 为什么需要两阶段提交, 两阶段提交怎么保证数据库中两份日志间的逻辑一致性(什么叫逻辑一致性)?
10. 如果不是两阶段提交, 先写redo log和先写bin log两种情况各会遇到什么问题?

知识点总结

• update语句实现流程

  mysql> update T set c=c+1 where ID=2;

  • 执行器先找到ID = 2这行,ID是主键,引擎直接调用树找到这一行,如果ID = 2这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回
  • 执行器拿到引擎给的数据,把这个值加1,比如原来是N,现在就是N+1,得到新的一行数据,在调用引擎接口写入这行数据
  • 引擎将这行新数据更新到内存中,同时将这个更新操作记录到redo log里面,此时redo log处于prepare状态,并把binlog写入磁盘
  • 执行器调用引擎接口提交事务,引擎把刚刚写入的redo log改成提交状态(commit)更新完成

• redo log是什么
  当数据库对数据做修改的时候，需要把数据页从磁盘读到buffer pool中，然后在buffer pool中进行修改，那么这个时候buffer pool中的数据页就与磁盘上的数据页内容不一致，称buffer pool的数据页为dirty page 脏数据，如果这个时候发生非正常的DB服务重启，那么这些数据还没在内存，并没有同步到磁盘文件中（注意，同步到磁盘文件是个随机IO），也就是会发生数据丢失，如果这个时候，能够在有一个文件，当buffer pool 中的data page变更结束后，把相应修改记录记录到这个文件（注意，记录日志是顺序IO），那么当DB服务发生crash的情况，恢复DB的时候，也可以根据这个文件的记录内容，重新应用到磁盘文件，数据保持一致。

• redo log 是InnoDB特有的,binlog是server层实现的,所有引擎可以使用

• redo log 通常是物理日志,记录的是数据页的物理修改

• redo log(重做日志)是由 redo log filer优先写入缓冲区,根据fsync 写入system文件

• 一条记录更新时候,InnoDB会先写到redo log里面,并更新内存,适当的时候更新磁盘里面

  • 三种更新磁盘次策略 innodb_flush_log_at_trx_commit
    • 默认值为1,innodb_flush_log_at_trx_commit=1，每次commit都会把redo log从redo log buffer写入到system，并fsync刷新到磁盘文件中。
    • innodb_flush_log_at_trx_commit=2，每次事务提交时MySQL会把日志从redo log buffer写入到system，但只写入到file system buffer，由系统内部来fsync到磁盘文件。如果数据库实例crash，不会丢失redo log，但是如果服务器crash，由于file system buffer还来不及fsync到磁盘文件，所以会丢失这一部分的数据。
    • innodb_flush_log_at_trx_commit=0，事务发生过程，日志一直记录在redo log buffer中，跟其他设置一样，但是在事务提交时，不产生redo 写操作，而是MySQL内部每秒操作一次，从redo log buffer，把数据写入到系统中去。如果发生crash，即丢失1s内的事务修改操作。

• redo log 相关参数

  • innodb_log_files_in_group 文件的个数,命名方式 : ib_logfile0，iblogfile1… iblogfilen。默认2个，最大100个。
  • innodb_log_files_size 文件设置大小,默认值为48m,最大值为512G,注意最大值指的是整个redo log系列文件只和,即(innodb_log_files_group * innodb_log_files_szie)不能大于512G
  • innodb_log_group_home_dir 文件存放位置,如果没有设置,则存放在datadir中
  • innodb_log_buffer_size redo log缓存区,默认为8M,可设置1-8M.延迟事务日志事务写入磁盘,把redo log放到该缓冲区,然后根据innodb_flush_log_at_trx_commit参数的设置,再把日志从buffer中flush到磁盘

• mysql crash执行流程是检测redo log file事务是否有prepare状态,在和binlog比较是否有次事务id的记录,如果有commit此事务,保证了事务的一致性

• binglog和redo log 是通过事务XID来进行事务完整性一致性的判断

• 

MYSQL crash重启执行流程

先看redo log 是否有prepare状态的事务,如果有,和binlog做对比,发现有此事务的事务ID记录,则commit此事务,保证了数据的一致性

1 prepare阶段 2 写binlog 3 commit
当在2之前崩溃时
重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。
一致
当在3之前崩溃
重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

redo是什么

当数据库对数据做修改的时候，需要把数据页从磁盘读到buffer pool中，然后在buffer pool中进行修改，那么这个时候buffer pool中的数据页就与磁盘上的数据页内容不一致，称buffer pool的数据页为dirty page 脏数据，如果这个时候发生非正常的DB服务重启，那么这些数据还没在内存，并没有同步到磁盘文件中（注意，同步到磁盘文件是个随机IO），也就是会发生数据丢失，如果这个时候，能够在有一个文件，当buffer pool 中的data page变更结束后，把相应修改记录记录到这个文件（注意，记录日志是顺序IO），那么当DB服务发生crash的情况，恢复DB的时候，也可以根据这个文件的记录内容，重新应用到磁盘文件，数据保持一致。
这个文件就是redo log ，用于记录 数据修改后的记录，顺序记录。它可以带来这些好处：

当buffer pool中的dirty page 还没有刷新到磁盘的时候，发生crash，启动服务后，可通过redo log 找到需要重新刷新到磁盘文件的记录；
buffer pool中的数据直接flush到disk file，是一个随机IO，效率较差，而把buffer pool中的数据记录到redo log，是一个顺序IO，可以提高事务提交的速度；
假设修改 tba 表中 id=2的行数据，把Name=‘B1’ 修改为Name = ‘B2’ ，那么redo日志就会用来存放Name='B2’的记录，如果这个修改在flush 到磁盘文件时出现异常，可以使用redo log实现重做操作，保证事务的持久性
这里注意下redo log 跟binary log 的区别，redo log 是存储引擎层产生的，而binary log是数据库层产生的。假设一个大事务，对tba做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而binary log不会记录，直到这个事务提交，才会一次写入到binary log文件中。binary log的记录格式有3种：row，statement跟mixed，不同格式记录形式不一样。

redo log 位置查找

show variables like "%innodb_log%";

redo 空间管理

Redo log文件以ib_logfile[number]命名，Redo log 以顺序的方式写入文件文件，写满时则回溯到第一个文件，进行覆盖写。（但在做redo checkpoint时，也会更新第一个日志文件的头部checkpoint标记，所以严格来讲也不算顺序写）。

实际上redo log有两部分组成：redo log buffer 跟redo log file。buffer pool中把数据修改情况记录到redo log buffer，出现以下情况，再把redo log刷下到redo log file：

Redo log buffer空间不足
事务提交（依赖innodb_flush_log_at_trx_commit参数设置）
后台线程
做checkpoint
实例shutdown
binlog切换

redo log file覆盖写

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示
write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

undo log 回滚操作

undo是什么

undo日志用于存放数据修改被修改前的值，假设修改 tba 表中 id=2的行数据，把Name=‘B1’ 修改为Name = ‘B2’ ，那么undo日志就会用来存放Name='B1’的记录，如果这个修改出现异常，可以使用undo日志来实现回滚操作，保证事务的一致性。

• undo是逻辑日志,只是将数据结构和页本身逻辑的恢复到原来的样子
• 对数据库进行修改的时候,InnoDB存储引擎不但会产生redo,还会产生一定量的undo,这样如果用户执行的事务或语句由于某种原因失败了,又或者用户一条ROLLBACK语句请求失败了,就可以利用这些undo信息将数据回滚到修改之前的样子

为什么不是物理日志

在多用户并发系统中,可能有数十数千个修改当前页中的某几条数据,同时还有别的事务在对同一个页中另几条记录进行修改,因此不能将一个页混滚到事务开始的样子,采用了逻辑恢复的样子
是insert的时候就delete 是update的时候就采用相对的update来恢复数据

undo 参数

show variables like "%undo%"; 

innodb_undo_log_truncate
InnoDB的purge线程，根据innodb_undo_log_truncate设置开启或关闭、innodb_max_undo_log_size的参数值，以及truncate的频率来进行空间回收和 undo file 的重新初始化。
该参数生效的前提是，已设置独立表空间且独立表空间个数大于等于2个
innodb_max_undo_log_size
控制最大undo tablespace文件的大小，当启动了 innodb_undo_log_truncate 时，undo tablespace 超过 innodb_max_undo_log_size 阀值时才会去尝试truncate。该值默认大小为 1G，truncate后的大小默认为10M。
innodb_undo_tablespaces
设置undo独立表空间个数，范围为0-128， 默认为0，0表示表示不开启独立undo表空间 且 undo日志存储在ibdata文件中。该参数只能在最开始初始化MySQL实例的时候指定，如果实例已创建，这个参数是不能变动的，如果在数据库配置文 件 .cnf 中指定innodb_undo_tablespaces 的个数大于实例创建时的指定个数，则会启动失败，提示该参数设置有误。

innodb_undo_directory
设置rollback segment文件所在位置的路径,这意味着rollback segment可以存放在共享表空间以外的位置,即可以设置为独立表空间,该参数的默认值为”.”,标识当前InnoDB存储引擎的位置的目录

innodb_undo_log
用来设置rollback segment的个数,默认值为128个

undo空间管理

如果需要设置独立表空间，需要在初始化数据库实例的时候，指定独立表空间的数量。
UNDO内部由多个回滚段组成，即 Rollback segment，一共有128个，保存在ibdata系统表空间中，分别从resg slot0 - resg slot127，每一个resg slot，也就是每一个回滚段，内部由1024个undo segment 组成。
回滚段（rollback segment）分配如下：

• slot 0 ，预留给系统表空间；
• slot 1- 32，预留给临时表空间，每次数据库重启的时候，都会重建临时表空间；
• slot33-127，如果有独立表空间，则预留给UNDO独立表空间；如果没有，则预留给系统表空间；
  回滚段中除去32个提供给临时表事务使用，剩下的 128-32=96个回滚段，可执行 96*1024 个并发事务操作，每个事务占用一个 undo segment slot，注意，如果事务中有临时表事务，还会在临时表空间中的 undo segment slot 再占用一个 undo segment slot，即占用2个undo segment slot。如果错误日志中有：Cannot find a free slot for an undo log。则说明并发的事务太多了，需要考虑下是否要分流业务。
  回滚段（rollback segment ）采用 轮询调度的方式来分配使用，如果设置了独立表空间，那么就不会使用系统表空间回滚段中undo segment，而是使用独立表空间的，同时，如果回滚段正在 Truncate操作，则不分配。

undo log 在事务中的流程

事务在undo log segment分配也写入undo log的同时也会产生redo log,当事务提交时候(commit)
1.将undo log 放入列表中,以供之后的purge(pai rui chi)线程使用
2.判断undo log 所在的页是否可以重用,若可以分配给下个事务使用
3.事务提交后,不能马上删除undo log及undo log所在的页,这是因为还有其他事务需要通过undo log来得到行记录之前的版本,故事务提交将undo log 放入一个链表中,是否可以最终删除undo log及undo log所在页由purge线程来判断

当事务提交时候,首先将undo log放入链表中,判断undo页的使用空间是否小于3/4,则表示undo log可以被重用,之后新的undo log 记录在当前的undo log的后面,由于存放undo log的列表是以记录进行组织的,二undo log可能存放着不同事务的undo log,因此prege操作需要设计磁盘的离散读取操作,是一个比较缓慢的过程,此过程可以设置purge线程的个数来提升回收速度

undo log 种类

1.insert undo log是值在insert操作中产生的undo log,因为insert操作记录,只对本身事务可见,对其他事物不可见,顾此undo log可以在事务提交后直接删除.
2.insert undo log记录的是直接修改后的操作,录入a +=1,记录的是a=2
3.update undo log记录的是对delete和update操作产生的undo log,该undo log可能需要提供MVCC机制,故不能再事务提交后就删除,提交时候记录到undo log链表,等待purge线程进行最后的删除
4.delete的是将删除的标记为已删除 delete flag为1,而最终的删除是在pruge操作中完成
update undo log是删除主键的记录,标记为已删除,产生一条新的逆向过程的记录

查看undo数量

show innodb_engine_status中的TRANSACTIONS history list length

purge thread

负责回收已经使用并分配的undo页，purge操作默认是由master thread中完成的，为了减轻master thread的工作，提高cpu使用率以及提升存储引擎的性能。用户可以在参数文件中添加如下命令来启动独立的purge thread。
innodb_purge_threads=1
从innodb1.2版本开始，可以指定多个innodb_purge_threads来进一步加快和提高undo回收速度。