mysql一条更新语句是如何被执行的——带你了解mysql更新语句执行内部顺序

写在前面

mysql一条查询语句是如何被执行的——带你了解mysql查询语句执行内部顺序

在数据库里面，update操作其实包括了更新、插入和删除。MyBatis源码中，Executor里面也只有doQuery和doUpdate方法，没有doDelete和doInsert方法。

更新流程和查询流程有什么不同呢？
基本流程其实是一致的，也就是说，它也要经过解析器、优化器的处理，最后交给执行器。

区别就在于拿到符合条件的数据之后的操作。

一、缓冲池 Buffer Poll

首先，对于InnoDB存储引擎来说，数据都是放在磁盘上的，存储引擎要操作数据，必须先把磁盘里面的数据加载到内存里面才可以操作。

这里就有个问题，是不是我们需要的数据多大，我们就一次从磁盘加载多少数据到内存呢？比如我们读6个字节。

磁盘IO的读写相对于内存的操作来说是很慢的。如果我们需要的数据分散在磁盘的不同的地方，那就意味着会产生很多次的IO操作。

所以，无论是操作系统也好，还是存储引擎也好，都有一个预读取的概念。也就是说，当磁盘上的一块数据被读取的时候，很有可能它附近的位置也会马上被读取到，这就叫做局部性原理。那么，我们干脆每次多读取一些，而不是用多少读多少。

InnoDB设定了一个存储引擎从磁盘读取数据到内存的最小的单位，叫做页。操作系统也有页的概念。操作系统的页一般是4k，而在InnoDB里面，这个最小的单位默认是16kb大小。如果要修改这个值的大小，需要清空数据重新初始化服务。

参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_size

还有一个问题，操作数据的时候，每次都要从磁盘读取到内存（再返回给Server），有没有办法可以提高效率？有，还是缓存的思想。把读取过的数据页缓存起来。

InnoDB设计了一个内存的缓冲区。读取数据的时候，先判断是不是在这个内存区域里面，如果是，就直接读取，然后操作，不用再次从磁盘加载。如果不是，读取后写到这个内存的缓冲区。

这个内存区域有个专属的名字，叫做Buffer Poll。

修改数据的时候，也是先写入到buffer poll，而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。

InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

总结：Buffer Poll的作用是为了提高读写的效率。

二、Redo log

由于脏刷不是实时的，如果Buffer Pool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据就会丢失。

怎么办？内存的数据必须要有一个持久化的措施。

为了避免这个问题，InnoDB把所有对页面的修改操作专门写入一个日志文件。如果有未同步到磁盘的数据，数据库在启动的时候，会从这个日志文件进行恢复操作（实现crash-safe）。我们说的事务的ACID里面的D（持久性），就是用它来实现的。

这个日志文件就是磁盘的redo log（重做日志）。

这个时候可能会有同学有疑问了：同样是写磁盘，为什么不直接写到db file里面去？为什么先写日志再写磁盘？写日志文件和写到数据文件有什么区别？

1、磁盘寻址的过程

我们先说一下磁盘寻址的过程（机械硬盘）。以下是一个磁盘的构造，磁盘的盘片不停地旋转，磁头会在磁盘表面画出一个圆形轨迹，这个叫做磁道。从内到外半径不同有很多磁道。然后又用半径线，把磁道分割成了扇区（两根射线之内的扇区组成扇面）。如果要读写数据，必须找到数据对应的扇区，这个过程就叫寻址。

如果我们所需要的数据是随机分散在磁盘上不同页的不同扇区中，那么找到相应数据需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，依次进行此过程直到找完所有数据，这个就是随机IO，读取数据速度较慢。

假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序IO。

刷盘是随机IO，而记录日志是顺序IO（连续写），顺序IO效率 更高，本质上是数据集中存储和分散存储的区别。因此先把修改写入日志文件，在保证了内存数据的安全性的情况下，可以延迟刷盘时机，进而提升系统吞吐。

2、redo log特点

redo log位于/var/lib/mysql/目录下的ib_logfile0和ib_logfile1，默认2个文件，每个48M。

show variables like 'innodb_log%';

redo log的特点：

• redo log是InnoDB存储引擎实现的，并不是所有存储引擎都有。支持崩溃恢复是InnoDB的一个特性。
• redo log不是记录数据页更新之后的状态，而是记录的是“在某个数据页上做了什么修改”。属于物理日志。
• redo log的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发buffer pool到磁盘的同步，以便腾出空间记录后面的修改。

除了redo log之外，还有一个跟修改有关的日志，叫做undo log。redo log和undo log与事务密切相关，统称为事务日志。

三、Undo log

undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态，分为insert undo log和update undo log。如果修改数据时出现异常，可以用undo log来实现回滚操作（保持原子性）。

可以理解为undo log记录的是反向的操作，比如insert会记录delete，update会记录update原来的值，跟redolog记录在哪个物理页面做了什么操作不同，所以叫做逻辑格式的日志。

show global variables like '%undo%';

redo log和undo log与事务密切相关，统称为事务日志。

四、更新过程

有了redo log和undo log，我们来总结一下一个更新操作的流程。

update user set name = 'zhangsan' where id=1;

• 1、事务开始，从内存（buffer poll）或磁盘（data file）取到包含这条数据的数据页，返回给Server的执行器。
• 2、Server的执行器修改数据页的这一行数据的值为zhangsan。
• 3、记录name=lisi（原值）到undo log。
• 4、记录name=zhangsan到redo log。
• 5、调用存储引擎接口，记录数据页到buffer pool（修改name=zhangsan）。
• 6、事务提交。

五、InnoDB总体架构

1、内存结构

Buffer Pool主要分为3个部分：Buffer Pool、Change Buffer、Adaptive Hash Index，另外还有一个（redo）log buffer。

（1）Buffer Pool

Buffer Pool缓存的事页面信息，包括数据页、索引页。

Buffer Pool默认大小是128M（134217728字节），可以调整。

-- 查看系统变量
show variables like '%innodb_buffer_pool%';
-- 查看服务器状态，里面有很多buffer pool相关的信息
show status like '%innodb_buffer_pool%';

这些参数都可以在官网查到详细的含义，用搜索功能。
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

内存缓冲池写满了怎么办？InnoDB用LRU算法来管理缓冲池（链表实现，不是传统的LRU，分成了young和old），经过淘汰的数据就是热点数据。

（2）预读机制

首先，InnoDB的数据页并不是都是在访问的时候才缓存到buffer pool的。
InnoDB有一个预读机制（read ahead）。也就是说，设计者认为访问某个page的数据的时候，相邻的一些page可能会很快被访问到，所以先把这些page放到buffer pool中缓存起来。

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-read_ahead.html

这种预读的机制又分为两种类型，一种叫线性预读（异步的）（Linear read-ahead）。为了便于管理，InnoDB中把64个相邻的page叫做一个extent（区）。如果顺序地访问了一个extent的56个page，这个时候InnoDB就会把下一个extent（区）缓存到buffer pool中。

顺序访问多少个page才缓存下一个extent，由一个参数控制：

show variables like 'innodb_read_ahead_threshold';

第二种叫做随机预读（Random read-ahead），如果bufferpool已经缓存了同一个extent（区）的数据页的个数超过13时，就会把这个extent剩余的所有page全部缓存到buffer pool。

但是随机预读的功能默认是不启用的，由一个参数控制：

show variables like 'innodb_random_read_ahead';

很明显，线性预读或者异步预读，能够把可能即将用到的数据提前加载到buffer pool。肯定能提升IO的性能，所以是一种非常有用的机制。

但是预读肯定也会带来一些副作用，就是导致占用的内存空间更多，剩余的空闲页更少。如果说buffer pool size不是很大，而预读的数据很多，很有可能那些真正的需要被缓存的热点数据被预读的数据挤出buffer pool淘汰掉了。下次访问的时候又要先去磁盘。

怎么让这些真正的热点数据不受预读的数据的影响呢？就需要使用LRU机制。

（3）LRU算法

传统的LRU算法，可以使用Map+链表实现。value存的是在链表中的地址。

首先，InnoDB中确实使用了一个双向链表，LRU list。但是这个LRU list放的不是data page，而是指向缓存页的指针。

如果写buffer pool的时候发现没有空闲页了，就要从buffer pool中淘汰数据页了，它要根据LRU链表的数据来操作。

InnoDB的LRU，将LRU list分成了两部分，靠近head的叫做new sublist，用来放热数据（我们把它叫做热区）。靠近tail的叫做old sublist，用来放冷数据（我们把它叫做冷区）。中间的分割线叫做midpoint。也就是对buffer pool做一个冷热分离。

所有新数据加入到buffer pool的时候，一律先放到冷数据区的head，不管是预读的，还是普通的读操作。所以如果有一些预读的数据没有被用到，会在old sublist（冷区）直接被淘汰。
放到LRU list以后，如果再次被访问，都把它移动到热区的head。
如果热区的数据长时间没有被访问，会被先移动到冷区的head部，最后慢慢在tail被淘汰。

在默认情况下，热区占了5/8的大小，冷区占了3/8，这个值由innodb_old_blocks_pct控制，它代表的是old区的大小，默认是37%也就是3/8。

innodb_old_blocks_pct的值可以调整，在5%到95%之间，这个值越大，new区越小，这个LRU算法就接近传统LRU。

如果这个值太小，old区没有被访问的速度淘汰会更快。

到这，预读的问题，通过冷热分离LRU算法解决了，还有没有其他问题呢？
我们先把数据放到冷区，用来避免占用热数据的存储空间。但是如果刚加载到冷区的数据立即被访问了一次，按照原来的逻辑，这个时候我们会马上把它移动到热区。

假设这一次加载然后被立即访问的冷区数据量非常大，比如我们查询了一张几千万数据的大表，没有使用索引，做了一个全表扫描。或者，dump全表备份数据，这种查询属于短时间内访问，后面再也不会用到了。

如果短时间之内被访问了一次，导致它们全部被移动到热区的head，它会导致很多热点数据被移动到冷区甚至被淘汰，造成了缓冲池的污染。

我们得想一个办法，对于加载到冷区然后被访问的数据，设置一个时间窗口，只有超过这个时间之后被访问，我们才认为它是有效的访问。

InnoDB里面通过innodb_old_blocks_time这个参数来控制，默认是1秒钟。也就是说1秒钟之内被访问的，不算数，待在冷区不动。只有1秒钟以后被访问的，才从冷区移动到热区的head。

这样就可以从很大程度上避免全表扫描或者预读的数据污染真正的热数据。

还有没有优化空间呢？
为了避免并发的问题，对于LRU链表的操作是要加锁的。也就是说每一次链表的移动，都会带来资源的竞争和等到。从这个角度上来说，如果要进一步提升InnoDB LRU的效率，就要尽量地减少LRU链表的移动。

比如，把热区一个非常靠近head的page移动到head，有没有这个必要呢？所以InnoDB对于new区还有一个特殊的优化：
如果一个缓存页处于热数据区域，且在热数据区域的前1/4区域（注意是热数据区域的1/4，不是整个链表的1/4），那么当访问这个缓存页的时候，就不用把它移动到热数据区域的头部；如果缓存页处于热区的后3/4区域，那么当访问这个缓存页的时候，会把它移动到热区的头部。

内存缓冲区对于提升读写性能有很大的作用。那么问题来了：当需要更新一个数据页时，如果数据页在Buffer Pool中存在，那么就直接更新就好了。否则的话就需要从磁盘加载到内存，再对内存的数据页进行操作。也就是说，如果没有命中缓冲池，至少要产生一次磁盘IO，有没有优化的方式呢？

这个时候就要用到Change Buffer 写缓冲。

（4）Change Buffer 写缓冲

Change Buffer是Buffer Pool的一部分。

如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert、Delete、Update）的执行速度。

这一块区域就是Change Buffer。5.5之前交Insert Buffer插入缓冲，现在也能支持delete和update。

最后把Change Buffer记录到数据页的操作叫做merge。什么时候发生merge？有几种情况：在访问这个数据页的时候，或者通过后台线程、或者数据库shut down、redo log写满时触发。

如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用Change Buffer（写缓冲）。

可以通过调大这个值，来夸大Change的大小，以支持写多读少的业务场景。

show variables like 'innodb_change_buffer_max_size';

Change Buffer占Buffer Pool的臂力，默认是25%。

（5）Redo Log Buffer

Redo Log也不是每一次都直接写入磁盘，在Buffer Pool里面有一块内存区域（Log Buffer）专门用来保存即将要写入日志文件的数据，默认16M，它一样可以节省磁盘IO。

show variables like 'innodb_log_buffer_size';

需要注意：redo log的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自buffer pool。redo log写入磁盘，不是写入数据文件。

那么，log buffer什么时候写入log file？
在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush就是把操作系统缓冲区写入到磁盘。

log buffer写入磁盘的时机，由一个参数控制，默认是1.

show variables like 'innodb_flush_log_at_trx_commit';

刷盘越快，越安全，但是也会越消耗性能。

2、磁盘结构

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。InnoDB的表空间分为5大类。

（1）系统表空间 system tablespace

在默认情况下InnoDB存储引擎有一个共享表空间（对应文件/var/lib/mysql/ibdata1），也叫系统表空间。

InnoDB系统表空间包含InnoDB数据字典和双写缓冲区，Change Buffer和Undo Logs，如果没有指定file-per-table，也包含用户创建的表和索引数据。

• undo在后面介绍，因为也可以设置独立的表空间。
• 数据字典：由内部系统表组成，存储表和索引的元数据（定义信息）。
• 双写缓冲（InnoDB的一大特性）：InnoDB的页和操作系统的页大小不一致，InnoDB页大小一般为16K，操作系统页大小为4K，InnoDB的页写入到磁盘时，一个页需要分4次写。
  
  如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了4K，就宕机了，这种情况叫做部分写失效（partial page write），可能会导致数据丢失。

show variables like 'innodb_doublewrite';

这个时候可能会有同学就问了，我们不是有redo log吗？但是有个问题，如果这个页本身已经损坏了，用它来做崩溃恢复是没有意义的。所以在对于应用redo log之前，需要一个页的副本。如果出现了写入失效，就用页的副本来还原这个页，然后再应用redo log。这个页的副本就是double write，InnoDB的双写技术。通过它实现了数据页的可靠性。

跟redo log一样，double write由两部分组成，一部分是内存的double write，一部分是磁盘上的double write。因为double write是顺序写入的，不会带来很大的开销。

在默认情况下，所有的表共享一个系统表空间，这个文件会越来越大，而且它的空间不会收缩。

（2）独占表空间 file-per-table tablespaces

我们可以让每张表独占一个表空间。这个开关通过innodb_file_per_table设置，默认开启。

show variables like 'innodb_file_per_table';

开启后，则每张表会开辟一个表空间，这个文件就是数据目录下的ibd文件（例如/var/lib/mysql/cxf/user_innodb.ibd），存放表的索引和数据。

但是其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内。

（3）通用表空间 general tablespaces

通用表空间也是一种共享的表空间，跟ibdata1类似。

可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件可以自定义。语法：

create tablespace test123 add datafile '/var/lib/mysql/test123.ibd' file_block_size=16K engine=innodb;

在创建表的时候可以指定表空间，用ALTER修改表空间可以转移表空间。

create table test123(id int) tablespace test123;

不同表空间的数据是可以移动的。

删除表空间需要先删除里面的所有表：

drop table test123;
drop tablespace test123;

（4）临时表空间 temporary tablespaces

存储临时表的数据，包括用户创建的临时表，和磁盘的内部临时表。对应数据目录下的ibtmp1文件。当数据服务器正常关闭时，该表空间被删除，下次重新产生。

（5）Redo log

磁盘结构里面的redo log，在前面已经介绍过了。

（6）undo log tablespace

undo log的数据默认在系统表空间ibdata1文件中，因为共享表空间不会自动收缩，也可以单独创建一个undo表空间。

3、后台线程

后台线程的主要作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘。后台线程分为：master thread、IO thread、purge thread、page cleaner thread。

• master thread负责刷新缓存数据到磁盘并协调调度其他后台进程。
• IO thread分为insert buffer、log、read、write进程。分别用来处理insert buffer、重做日志、读写请求的IO回调。
• purge thread用来回收undo页。
• page cleaner thread用来刷新脏页。

4、Binlog

除了InnoDB架构中的日志文件，MySQL的Server层也有一个日志文件，叫做binlog，它可以被所有的存储引擎使用。

binlog以事件的形式记录了所有的DDL和DML语句（因为它记录的事操作而不是数据值，属于逻辑日志），可以用来做主从复制和数据恢复。

跟redo log不一样，它的文件内容是可以追加的，没有固定大小限制。

在开启了binlog功能的情况下，我们可以把binlog导出成SQL语句，把所有的操作重放一遍，来实现数据的恢复。

binlog的另一个 功能就是用来实现主从复制，它的原理就是从服务器读取主服务器的binlog，然后执行一遍。

有了这两个日志之后，我们来看一下一条更新语句是怎么执行的（redo不能一次写入了）：

例如一条sql：update user set name = ‘zhangsan’ where id = 1;

• 1、先查询到这条数据，如果有缓存，也会用到缓存。
• 2、把name改成zhangsan，然后调用引擎的API接口，写入这一行数据到内存，同时记录redo log。这时redo log进入prepare状态，然后告诉执行器，执行完成了，可以随时提交。
• 3、执行器收到通知后记录binlog，然后调用存储引擎接口，设置redo log为commit状态。
• 4、更新完成。

注意！1、先记录内存，再写日志文件；2、记录redo log分两个阶段；3、存储引擎和Server记录不同的日志；4、先记录redo，再记录binlog。

（1）为什么需要两阶段提交？

如果我们执行的是把name改成zhangsan，如果写完redo log，还没有写binlog的时候，MySQL重启了。

因为redo log可以在重启的时候用于恢复数据，所以写入磁盘的事zhangsan。但是binlog里面没有记录这个逻辑日志，所以这时候用binlog去恢复数据或者同步到从库，就会出现数据不一致的情况。

所以在写两个日志的情况下，binlog就充当了一个事务协调者。通知InnoDB来执行prepare或者commit或者rollback。

如果写入binlog失败，就不会提交。

简单的说，这里有两个写日志的操作，类似于分布式事务，不用两阶段提交，就不能保证都成功或者都失败。

在崩溃恢复时，判断事务是否需要提交：

• 1、binlog无记录，redolog无记录：在redolog写之前crash，恢复操作：回滚事务。
• 2、binlog无记录，redolog状态prepare：在binlog写完之前的crash，恢复操作：回滚事务。
• 3、binlog有记录，redolog状态prepare：在binlog写完提交事务之前的crash，恢复操作：提交事务。
• 4、binlog有记录，redolog状态commit：正常完成的事务，不需要恢复。