mysql 【日志】和 【索引】

一 日志

以下的讨论都是以innodb作为mysql的默认引擎为前提。
在innodb作为mysql的引擎是，大致有三种日志：

1.binlog 日志

又称为归档日志，主要功能用于主从数据库间数据备份。它是mysql server层的日志，即mysql自带的日志。
binlog日志是一种逻辑日志，他里边所记录的是一条 SQL 语句的原始逻辑。下面说一下binlog日志如何开启：
在mysql的配置文件my.ini配置文件中，加入以下配置：
bin_log = mysql-bin ## 代表开启binlog，并且产生的binlog文件为mysql-bin.XXXXXX的二进制文件，路径为配置文件中datadir下
binlog_format = ROW ## 代表记录binlog的方式为ROW
binlog的三种格式：

1.1 Statement

Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，
当同步到 slave 之后，再次执行，就得到另外一个结果了。
所以使用 Statement 格式会出现一些数据一致性问题

1.2 Row

从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。
Row 格式的日志内容会非常清楚地记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题

1.2 Mixed

从 MySQL5.1.8 版开始，MySQL 又推出了 Mixed 格式，这种格式实际上就是 Statement 与 Row 的结合。
在 Mixed 模式下，系统会自动判断 该 用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog；对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog

下面为某一个binlog日志在相同的操作下，在statement和row模式下的差别：

binlog 的刷写机制 mysql45讲～23讲
binlog 格式详解 请参考 24讲

2.redo log 日志

redo log（文件名：ib_logfile0,ib_logfile1） 日志又称为重做日志，保证事务的持久性，是innodb引擎自身带有的。一般来说redo log会被分为4个文件，可以循环使用。

2.1WAL(Write Ahead Logging)技术

关键点就是先写日志，再写磁盘。使用WAL技术可以减少与磁盘的交互，增大吞吐量。通常，对于所有的修改数据的操作，都是先将操作写入日志，并更新内存。这个时候redo log和内存的数据页都是脏数据，所以最后需要进行刷盘。

2.2Checkpoint技术

checkpoint个人理解是redo log中的一个标记点，需要和write position结合在一起使用。
write pos是当前记录的位置。checkpoint是当前要擦除的位置。
对于某一内存中的某一数据页，要怎么判定他是脏页呢？使用LSN(Log Sequence Num) mysql45讲-23讲。
对于checkpoint，write position还有数据页，都是对应的LSN。

某一数据页是脏页的条件：
checkpoint’s LSN < 数据页的LSN < write position’s LSN

引入checkpoint的最重要的作用：缩短数据库的恢复时间
如果没有checkpoint并且redo log非常大的时候，崩溃恢复的时间就可能会很长。

2.3刷盘时机：

• InnoDB 的 redo log 写满了，即是 write position 的位置追到了 checkpoint的位置，这时候系统就会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间继续写。Fuzzy Checkpoint
• 系统内存不足，因为 MySQL 的数据都缓存在内存中，当系统的内存不足，那么就会有一部分数据会刷到磁盘中去。Fuzzy Checkpoint
• MySQL 空闲的时候把数据进行刷盘 Sharp Checkpoint
• 关闭数据库的时候，回刷数据回磁盘 Sharp Checkpoint

2.4redo 底层写了什么内容

redo 记录物理日志，记录的是“在某个数据页上做了什么修改”
例如下面的语句 :
update table set a = 1 where id = 1;
那么翻译成物理日志类似于这样 :
把第10表空间的第90号页面的偏移量为1024处的值更新为1
下面是大部分类型的redo log的通用结构：

• type：redo log的类型，目前redo log的类型很多
• Space ID：表空间ID
• page number：页号
• data：一条redo log的内容

先看一下 redo log 的基础类型
redo log类型主要是通过上面记录中的type体现的。比较基础的有以下几个（基础的类似于java里面的基本类型）：

• MLOG_1BYTE：type字段对应的十进制为1，表示在页面的某个偏移量处写入一个字节

• MLOG_2BYTES：type字段对应的十进制为2，表示在页面的某个偏移量处写入两个字节

• MLOG_4BYTES：type字段对应的十进制为4，表示在页面的某个偏移量处写入四个字节

• MLOG_8BYTES：type字段对应的十进制为8，表示在页面的某个偏移量处写入八个字节

• MLOG_WRITE_STRING：：type字段对应的十进制为30，表示在页面的某个偏移量处写入一串数据

现在举一个例子。我们大部分情况下用的自增主键id都是int型或者是long型的，int为四个字节，long为八个字节，现在如果插入一条数据的话，这条数据实际是修改在buffer pool中的，然后通过redo log记录下当前的修改情况。那么这个时候，插入一条id(int)为9的数据的redo log应该是这样子的。

插入数据后 , 含义： 在90表空间，编号为10页面，偏移量为1000处，写入四个字节，具体数据为0000 0000 0000 1001

redo log 顺序写与组提交 （23讲）
redo log 刷写机制（23讲）

3. bin log 和redo log的关系及区别

4.undo log 日志

undo log （文件名：ibdata1）日志又称为回滚日志，保证事务的原子性，两大功能：

• 用于回滚
• 实现MVCC

4.1 实现回滚

对于修改数据的命令（insert，update，delete），会记录一条与之逻辑相反的sql语句到undo log中。
假设表中有表t，并且表中有两个字段a，b。初始数据有(1,1),(2,2),(3,3)
当有一条更新语句更新数据时：
update t set b = 3 where a = 2; #1
就会在undo log文件中新增一条记录：
update t set b = 2 where a = 2; #2
此时，在1语句还没有commit的时候，数据库崩溃了，那么就可以依据2号语句进行回滚，得到崩溃前的数据。

4.2 实现Mvcc

下面用一个图来做案例(以下讨论都是在快照读且隔离级别在RR条件下)

从上图中可以看出，在图中框中圈出来的部门，其实就组成了一个undo链。
在undo 链上面总共有4条undo记录，对于每一条记录，都有一个事务id，一个指针指向前一个undo记录。
在快照读的前提下，不同的事务，对于同一行读取出来的数据有可能是不一样的。
对于当前事务要去读数据时，就需要按照undo链并且结合自己的trx_id以及undo记录中的read_view数组来做判断，看看能读出来哪一个数据。
对于一个read_view数组，我们做以下区分：

其中低水位是处于未提交状态的事务集合中最小的trx_id,高水位是最大的trx_id
接下来，我们继续看一下图1中的三个事务
这里，我们不妨做如下假设：

• 事务A开始前，系统里面只有一个活跃事务ID是99；
• 事务A、B、C的版本号分别是100、101、102，且当前系统里只有这四个事务；
• 三个事务开始前，(1,1）这一行数据的rowtrx_id是90。
  这样，事务A的视图数组就是[99,100], 事务B的视图数组是[99,100,101], 事务C的视图数组是
  [99,100,101,102]
  
  对于当前事务的启动瞬间来说，一个数据版本的rowtrx_id，有以下几种可能
• 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是
  可见的。
• 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的。
• 如果落在黄色部分，那就包括两种情况。
  a. 若 rowtrx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；
  b. 若 rowtrx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。