mysql执行sql必须先写入日志_mysql学习笔记---从日志系统分析一条sql更新语句的执行...

1.sql查询流程的流程图

我们知道，一条普通的查询语句的执行流程一般是这样的：

这里的查询缓存是针对表的一个缓存,如果对表有过更新操作,那么查询缓存会立即失效,因此mysql的查询缓存一般情况下意义不大.

2.更新语句的执行方式

上面简单介绍了mysql的查询流程,那么对于更新语句来说,例如

update t_student set name='paul' where id=3;

同样也会经历上面的一个过程,分析器通过语法分析发现这是一个更新语句,然后优化器决定走id这个主键索引,最后交给执行器去连接到存储引擎去执行实际的更新操作.但是我们知道,mysql的数据是存在磁盘上的,它的存储实际上是有个最小的页为单位的,每次读取最小会读取一页的数据到内存里,当我们进行修改时,实际上也是直接对内存里的这行数据进行修改.如果每次修改了都要写回到磁盘中,那还得经过几次io操作定位到这行数据并写入,对于一次普通的写入操作来说这种效率是很低的.那么有没有什么好办法呢?我们来参考生活中的一个例子.

3.mysql的日志系统

3.1 生活中的例子

假设我们开了一家小卖部,我们对每笔交易肯定要进行记账.首先我们有个账本,上面记录了所有的账目(实际的数据)这是一个很大的本子上,每次翻找定位起来效率比较低,那我们可以弄一个小黑板,先将记录临时记在这个小黑板上,同时我可以先将修改记在脑海中(内存)当黑板记满了,或者生意比较好我记不住了或者我空闲下来的时候,再将记忆中的内容抄写到账本里,同时把黑板上的相同的修改内容除去,这样是不是就减少了频繁翻阅账本的次数,从而提高了工作效率~~

3.2 redo log

上面例子中的小黑板,其实就是mysql中innodb特有的日志模块redo log.这种思想类似于io操作中缓冲区的概念.它长下面这个模样~

是一个环形的ringBuffer,上面有两个指针,一个write pos,这个指针用来写,当有更新记录写入时往前走,另一个是check point,是擦除指针,当我们把内存中的数据刷到磁盘里去的时候这个指针就往前走,如果write pos走得太快和check point重叠了,这个环形就满了,就像记账的小黑板满了,那么此时系统不得不暂停一下把redo log的数据刷到磁盘中去,否则就会丢失数据嘛.

这种配合方式称作WAL(Write ahead logging),他的完整流程是:我在执行一条更新语句时,先写入redo log,再更新内存中的页,最后在合适的时候统一将redo log写到磁盘中去,从而更新真正的底层记录.这样即使中途mysql宕机了,由于redo log已经记录了修改,我之前执行的修改记录也不会丢失.就像我们的小店,某天账本突然丢了,由于我将之前的修改都存在了小黑板上,在找回账本后这部分记录仍然还是存在的.

3.3 binlog

binlog这个东西相信不少同学的听说过.我们日常工作中,DBA有时候会吹逼,说数据库可以恢复到最近一两个月的任意时候数据,这里其实就是运用了binlog.那么这玩意跟我们上面介绍的redo log有啥区别呢,区别如下:

redo log是innodb特有的机制,现在mysql的默认存储引擎innodb是后面才引入的,之前还有mysiam等存储引擎,而在存储引擎之上的server层,mysql自己封装了一套日志系统就是binlog 也就是他适用于所有存储引擎.

redo log 是一个环形的数据结构,受配置大小限制,本身存储的内容有限,例如想恢复到一个月前的数据,肯定满足不了.而binlog本身是采用的一种追加的写入方式,一个文件写完了可以继续写下一个文件,能够支持较大的容量.能支持完整时间较长的数据恢复.

redo log记录的是修改的内容,是redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；而binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

4.完整的执行流程和两阶段提交

4.1 执行流程再分析

有了上面的日志系统,我们再来看下一条DML的实际执行过程.

1.执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用聚集索引找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2.执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3.引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4.执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5.执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交(commit)状态，更新完成。

可以发现其实我们平时对mysql的更新操作只是在内存里去做修改,然后写日志到磁盘中就完事了.那肯定会出现内存的数据跟硬盘中的数据不一致的情况吧,这种情况我们称为"脏页",这块知识盆友们可以去google下了解~

然后注意这里的commit状态,跟我们平时的mysql事务中的commit操作不同,可以理解为前者是后者执行过程中的一个中间状态~

4.2 两阶段提交

眼尖的盆友发现了,我们上面的3.4步实际上是在一个事务中的,这里的事务是指存储引擎中的一个内部事务.他避免了两种日志的不一致状态,我们可以想下,如果没有这个事务保证,那么就有可能出现不一致的状态:

1.先写redo log再写binlog 假设我们写完redo log后系统就奔溃了,那么redo log和内存中实际上已经有这个事务了,实际上这条修改已经生效了,但是binlog里又是没有的,假如我们今后用这份binlog来恢复数据库,实际上恢复出来的数据就是不一致的数据.

2.先写binlog再写redo log 假如我们先写binlog,写完之后数据库crash了,由于redolog里没有这一条修改,实际上这个事务是无效的,系统恢复后是没有这条修改的,假如我们今后用这份binlog来恢复数据库,实际上恢复出来的数据就会多一次修改记录.

看吧,如果这里不用两阶段提交的话,就有一定概率会出现两份日志不一致的情况,从而导致后续用日志来恢复回来的数据出现错漏.

5.总结

本文从mysql的日志系统出发,介绍了更新语句的执行流程.以及mysql崩溃后用来恢复的日志记录.

此外还可以优化两个参数:

redo log 用于保证 crash-safe ,**innodb_flush_log_at_trx_commit **这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘.这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

**sync_binlog **这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失.并且这两个参数基本不会影响执行速度,能够提高数据库的可靠性~