MYSQL(二):update语句执行的秘密

MYSQL(二):update语句执行的秘密

在上一篇博客mysql查询里，已经大致讲了MySQL的一些组件，和MySQL的查询语句是如何返回结果的，现在再来研究一下MySQL是如何进行更新操作的，虽然标题是update执行的秘密，但是实际上新增和删除也是一种与update相似的操作(这些操作都涉及到了对硬盘存储的修改)。MySQL处理它们的流程都是一样的。

需要注意：所有的讨论都以Inodb存储引擎为标准。

bin-log日志

之前的博客已经讲过MySQL的组件，bin-log日志是属于Server层的日志，相信对于大多数使用过MySQL的程序员来说，一定不会对bin-log日志感到陌生，但是要强调一下，bin-log日志是MySQL自己的日志，为什么要强调这个，之后的内容会讲明白。

bin-log日志记录了每一条SQL语句的执行，当因为数据库down掉需要恢复的时候将bin-log日志中的记录都执行一遍就可以将数据库的状态恢复到bin-log日志所记录的时间点。需要主义的是，bin-log日志只能将数据库恢复到他记录的时间点。也就是说，如果有些SQL虽然执行成功了，但是还没来得及写入bin-log日志，那么使用bin-log日志恢复数据库的时候，就会丢失掉这一部分数据。所以只依赖bin-log日志来记录数据库的逻辑操作，显然是不足够好的。

既然只有bin-log日志会有这方面的问题，那么我想要我的数据库是crash-safe的，可以恢复到和down之前一样的状态，应该如何做呢。

假如面试的时候有这么一个问题，问你觉得数据库更新在硬盘写入的时候应该先写表数据，还是应该先写日志呢？

其实SQL领域已经有一种技术就是来解答这个问题的，就是WAL技术，全称是Write-Ahead Logging，这种技术的核心就是先写日志，在写数据到磁盘。但是由于bin-log日志是Server层的日志，而数据写入磁盘是存储引擎做的事情，也就是说Server层不可能先把操作记录在日志上，再由存储引擎去写数据。这样的话，玩意存储引擎写入失败了，但是bin-log日志里面已经记录了这条SQL的执行，一样会让bin-log日志和实际存储的数据不一致。

所以Inodb存储引擎为了解决事务存储的一致性，引入了一个新的日志redo_log来解决这一个问题。

redo-log日志

redo-log日志与bin-log日志不同，它是属于存储引擎层的日志，也就是Inodb独有的日志，所以在Server层将执行语句交给了存储引擎之后，存储引擎可以完美的处理先写日志这一个点。那么redo日志是如何写入的呢，它又解决了一些什么样的问题呢。

首先，数据库的表数据都是记录在硬盘上的，如果每一条更新的语句都直接就修改硬盘上记录的表数据那么实时的开销是很大的，这样在业务并发量大的时候，磁盘写入的资源消耗(主要是随机读写)就会成为一个瓶颈。WAL技术可以解决这一个问题，而Inodb是通过redo_log来实现WAL技术的。

现在，我们假设有这样一张表，只有很简单的两个字段：id和num字段。

mysql> create table test (
        id int primary key, 
        num int
);

我们执行一条语句mysql> update test set num=1 where id=1; 这条语句的作用也很简单，就是将主键id为1的这条记录的num的值修改为1。

当这条语句执行的时候，按逻辑会先将id=1这一行数据中的num读到内存中，然后再讲num的值修改为1，最后再将num=1这个结果写回硬盘上。但是在这个写回的时候，Inodb并没有直接将数据写到硬盘上，而是将这条记录的修改操作写到了redo_log中，也就是说redo_log中此时记录了num=1这个更新语句，至此存储引擎会告诉Server层，它已经完成了数据的更新。

为什么要这样呢，你可以看到，在存储引擎返回Server层数据更新成功的时候，数据其实根本还没有写到磁盘上。这样减少更新时硬盘随机读写的开销，另外，更新行数据还可能造成索引的变动，因为索引表是排序的，更新插入会导致当前行在索引树中位置的变动，更坏的情况下，还可以能造成索引树的分裂，但是Inodb更新的时候没有将数据实时写入磁盘，所以这些资源的消耗就都不存在了。只需要将更新记录写入redo_log便可，这消耗的不过是一行顺序写入的资源罢了。大大的减少了更新的时间消耗。

但是，数据始终是要持久化到硬盘上的，那么Inodb在什么时候做这一件事情呢。首先你能想到的肯定是，在某个MySQL数据库系统并不繁忙的时候，会将redo_log中的更新记录读取出来，然后逐步执行，更新硬盘上的表数据。还有一种情况是，当redo_log日志即将要写满的时候，MySQL会将redo_log日志中的更新先同步到硬盘上，以便后续的更新能写入redo_log中。不管是什么情况下，只要数据从redo_log中写入了硬盘，这行记录就会被删除掉。

可以看到，redo_log更像是一个写入缓冲区，用一次简单的磁盘文件顺序写来代替了十分复杂的数据库表更新操作，大大提升了在繁忙的业务下MySQL的并发能力。

同时，由于redo_log的存在，也弥补了bin-log的不足，使得Inodb引擎可以实现crash-safe。在bin-log日志中没有记录到的更新，现在都可以在redo_log中找到了。

总结

1.不要再觉每次SQL更新都是直接写硬盘了！！！！

2.如果有面试官问你，你觉得SQL执行是先写日志还是先写数据，那么总体上问的就是WAL这种思想，这与很多缓存的思想其实是一致的。

思考

上面我讲了执行一条普通的sql时，redo_log日志的工作情况。那么想一想，在一个事务中执行了许多条sql语句，由于事务的原子性要求。万一出现事务中有一条sql失败的时候数据库故障了。那么这种情况下redo_log和bin-log日志要如何保证这整个事务的所有sql都不执行呢?

有兴趣的可以研究一下，redo_log和bin-log日志之间的“两阶段提交”这个概念。