从Mysql架构看一条更新sql的执行过程-摩杜云开发者社区

上一篇文章我们讲了一条查询sql时如何执行的. 今天我们继续来看下一条更新sql的执行流程.

在数据库里面，我们说的update操作其实包括了更新、插入和删除。如果大家有看过MyBatis的源码，应该知道Executor里面也只有doQuery()和doUpdate()的方法，没有doDelete()和doInsert()。

更新流程基本流程也是一致的，也就是说，它也要经过解析器、优化器的处理，最后交给执行器。区别就在于拿到符合条件的数据之后的操作。

缓冲池

首先，InnnoDB的数据都是放在磁盘上的，InnoDB操作数据有一个最小的逻辑单位，叫做页（索引页和数据页）。我们对于数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢了。InnoDB使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫Buffer Pool.

从Mysql架构看一条更新sql的执行过程_缓冲池

下一次读取相同的页，先判断是不是在缓冲池里面，如果是，就直接读取，不用再次访问磁盘。

修改数据的时候，先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB里面有专门的后台线程把BufferPool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

BufferPool是InnoDB里面非常重要的一个结构，它的内部又分成几块区域。这里我们趁机到官网来认识一下InnoDB的内存结构和磁盘结构。

InnoDB内存结构和磁盘结构

从Mysql架构看一条更新sql的执行过程_缓冲池_02

内存结构

BufferPool主要分为3个部分：Buffer Pool、Change Buffer、AdaptiveHash Index，另外还有一个（redo）logbuffer。

1.Buffer Pool

BufferPool缓存的是页面信息，包括数据页、索引页。查看服务器状态，里面有很多跟BufferPool相关的信息：

SHOW STATUS LIKE '%innodb_buffer_pool%';

这些状态都可以在官网查到详细的含义，用搜索功能。

从Mysql架构看一条更新sql的执行过程_表空间_03

BufferPool默认大小是128M（134217728字节），可以调整。查看参数（系统变量）：

SHOW VARIABLES like' %innodb_buffer_pool%';

这些参数都可以在官网查到详细的含义，用搜索功能。

从Mysql架构看一条更新sql的执行过程_表空间_04

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

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

2. ChangeBuffer写缓冲

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

这一块区域就是ChangeBuffer。5.5之前叫InsertBuffer插入缓冲，现在也能支持delete和update。

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

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

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

代表ChangeBuffer占BufferPool的比例，默认25%。

3. Log Buffer

思考一个问题：如果BufferPool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据丢失。如果写操作写到一半，甚至可能会破坏数据文件导致数据库不可用。

为了避免这个问题，InnoDB把所有对页面的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作（实现crash-safe）——用它来实现事务的持久性。

从Mysql架构看一条更新sql的执行过程_数据_05

这个文件就是磁盘的 redo log（叫做重做日志），对应于/var/lib/mysql/目录下的 ib_logfile0 和 ib_logfile1，每个 48M。

这种日志和磁盘配合的整个过程，其实就是 MySQL 里的 WAL 技术（Write-Ahead Logging），它的关键点就是先写日志，再写磁盘.

show variables like 'innodb_log%';

从Mysql架构看一条更新sql的执行过程_表空间_06

问题：同样是写磁盘，为什么不直接写到 db file 里面去？为什么先写日志再写磁盘？

我们先来了解一下随机 I/O 和顺序 I/O 的概念:

磁盘的最小组成单元是扇区，通常是 512 个字节。
操作系统和磁盘打交道，读写磁盘，最小的单位是块 Block。

从Mysql架构看一条更新sql的执行过程_表空间_07

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

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

刷盘是随机 I/O，而记录日志是顺序 I/O，顺序 I/O 效率更高。因此先把修改写入日志，可以延迟刷盘时机，进而提升系统吞吐。

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

从Mysql架构看一条更新sql的执行过程_表空间_08

需要注意：redo log 的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自 bufferpool。redo log 写入磁盘，不是写入数据文件。那么，Log Buffer 什么时候写入 log file？在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush 就是把操作系统缓冲区写入到磁盘.

从Mysql架构看一条更新sql的执行过程_表空间_09

redo log 有什么特点？

redo log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有
不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志
redo log 的大小是固定的，前面的内容会被覆盖

从Mysql架构看一条更新sql的执行过程_数据_10

磁盘结构

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

系统表空间 system tablespace

在默认情况下 InnoDB 存储引擎有一个共享表空间（对应文件/var/lib/mysql/ibdata1），也叫系统表空间。InnoDB 系统表空间包含 InnoDB 数据字典和双写缓冲区，Change Buffer 和 UndoLogs），如果没有指定 file-per-table，也包含用户创建的表和索引数据.

数据字典：由内部系统表组成，存储表和索引的元数据（定义信息）。

InnoDB 的页和操作系统的页大小不一致，InnoDB 页大小一般为 16K，操作系统页
大小为 4K，InnoDB 的页写入到磁盘时，一个页需要分 4 次写.

从Mysql架构看一条更新sql的执行过程_缓冲池_11

如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了 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 是顺序写入的，不会带来很大的开销。在默认情况下，所有的表共享一个系统表空间，这个文件会越来越大，而且它的空间不会收缩.

独占表空间 file-per-table tablespaces

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

SHOW VARIABLES LIKE 'innodb_file_per_table';

开启后，则每张表会开辟一个表空间，这个文件就是数据目录下的 ibd 文件（例如/var/lib/mysql/gupao/user_innodb.ibd），存放表的索引和数据。但是其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内。

通用表空间 general tablespaces通用表空间也是一种共享的表空间，跟 ibdata1 类似。可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件可以自定义。语法：

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

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

create table t2673(id integer) tablespace ts2673;

不同表空间的数据是可以移动的。删除表空间需要先删除里面的所有表.

drop table t2673;
drop tablespace ts2673;

临时表空间 temporary tablespaces

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

Redo log tablespace

上面已经介绍过

undo log tablespace

undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态（不包括 select）。如果修改数据时出现异常，可以用 undo log 来实现回滚操作（保持原子性）。在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，属于逻辑格式的日志。

redo Log 和 undo Log 与事务密切相关，统称为事务日志。undo Log 的数据默认在系统表空间 ibdata1 文件中，因为共享表空间不会自动收缩，也可以单独创建一个 undo 表空间。

show global variables like '%undo%';

总结

有了这些日志之后，我们来总结一下一个更新操作的流程，这是一个简化的过程。name 原值是 tom。

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

事务开始，从内存或磁盘取到这条数据，返回给 Server 的执行器；
记录 name=penyuyan 到 redo log；
调用存储引擎接口，在内存（Buffer Pool）中修改 name=penyuyan；