作者 | 李祥
给我一个键盘，我敲敲看～
1、写在前面
作为一名后端开发，数据库对于我自己来说是既熟悉又陌生。说熟悉，是因为从日常工作学习基本上每天都会与它打交道，毕竟CRUD工程师大家都懂得；说陌生，则是因为在日常工作学习中我们对数据库往往更关注于操作层，说通俗点就是更关注于sql语句的编写，而往往忽略一些底层的细节。当然，代码层面与sql语句更具有亲和性这点是必然的，数据库对于开发人员的透明性也是其设计的优秀之处，通过暴露出统一的接口，大家只要稍微懂一些sql的语法就可以对数据库进行大部分CRUD了。但作为一名后端开发，完成CRUD的同时多去关注一些数据库的细节还是有必要的。
本文会分析一条SQL的执行流程，简单阐述SQL在MySQL中执行的的大致过程，最终将关注点落在缓冲池(InnoDB Buffer Pool)上，更多地是剖析Bufer Pool的数据结构及原理。
2、MySQL的架构设计
MySQL从架构上可以分为服务器层与储存引擎，我们通过下图简单了解SQL请求执行流程：
首先，我们的系统会与MySQL数据库服务器建立数据库连接，当请求到来时，系统内的线程负责处理业务逻辑、通过数据库连接发送SQL语句。
相应地，在MySQL服务器内有负责监听连接的线程，当SQL请求到来时监听线程负责获取SQL并转交统一的SQL接口(SQL Interface)去执行。SQL接口(SQL Interface)是MySQL提供的一个组件 ，它是一套执行SQL语句的接口，专门用于执行我们发送给MySQL的那些增删改查的SQL语句。
那么，SQL接口如何执行SQL语句呢？其实MySQL是无法直接理解这些SQL语句的，所以必须有一个组件能将这些SQL语句翻译成MySQL能够理解的语言，这个组件就是查询解析器(Parser)。这个查询解析器就是负责对SQL语句进行解析的，即按照既定的SQL语法，对我们按照SQL语法规则编写的SQL语句进行解析，然后让MySQL服务器理解这个SQL语句要干什么事情。
当我们通过查询解析器理解了SQL语句要干什么之后，接着会找查询优化器(Optimizer)来选择一个最优的查询路径。这里不详细介绍查询优化器的细节，总体来说查询优化器会针对我们编写的复杂的SQL语句生成查询路径树，然后从里面选择一条最优的查询路径出来。相当于他会告诉你，你应该按照一个什么样的步骤和顺序，去执行那些操作。
最后一步，就是把查询优化器选择的最优查询路径，也就是你应该按照一个什么样的顺序和步骤去执行这个SQL语句的计划，把这个计划交给底层的储存引擎去真正的执行，而调用储存引擎接口的正是执行器，即执行器会根据我们的优化器生成的一套执行计划，然后不停的调用存储引擎的各种接口去完成SQL语句的执行计划，大致就是不停的更新或者提取一些数据出来。
以上就是基于MySQL的架构的SQL执行流程：我们的系统通过一个数据库连接发送SQL请求到MySQL上，然后MySQL服务器负责监听的线程从数据库连接中获取SQL，然后会经过SQL接口、解析器、优化器、执行器几个环节，解析SQL语句，生成执行计划，接着由执行器负责这个计划的执行，调用Innodb储存引擎的接口去执行。
3、Innodb储存引擎的架构设计
MySQL服务器根据生成的执行计划，由执行器调用储存引擎的接口去真正执行SQL。我们就以Innodb储存引擎为例，简单描述一下一条更新语句如何基于Innodb储存引擎的执行流程。假设待更新的数据储存在磁盘中还没加载进内存，则执行过程如下图，大致可以分为以下几步：
3.1 加载缓存数据
我们都知道，数据库的数据文件最终都要储存在磁盘中，那么在更新数据时难道要直接更新磁盘中的数据吗？当然不是，毕竟磁盘一次随机IO代价还是比较大的，若是所有的SQL直接去更改磁盘的数据，那么频繁的磁盘随机IO必然会拖慢数据库的速度，进而整个系统的压力都会变大。在计算机系统中，CPU与磁盘速度差异巨大，因此要引入多级缓存来解决这个问题，MySQL解决这一问题的思路与之类似，MySQL会申请一块较大的内存区域，数据的CRUD都会基于内存中的数据进行操作而不是直接去更新磁盘中的数据，这块大的内存区域就是缓冲池即Buffer Pool。下图展示了Innodb的内存数据对象：
具体来看，缓冲池中的数据类型有：索引页、数据页、undo页、插入缓存、自适应哈希索引、InnoDB储存的锁信息、数据字典信息等。
而要更新一条数据时，首先要做的就是将磁盘中这条数据所在的数据页加载到内存中，对应上图的步骤1，更改数据时更改的则是内存中的数据。
3.2 写undo日志
在数据加载到缓冲池之后，此时还不能直接对数据进行更改，具体原因是InnoDB对事务的支持。大家都知道，对于事务中的SQL，只有在commit之后才意味着这条SQL真正执行完成了，相反的若执行失败也会执行rollbak(回滚)。那么，在进行数据更新之前就必须记录数据的旧值，即将数据的旧值写入uodo日志文件，InnoDB的回滚就是基于undo日志文件完成的。
3.3 更新buffer pool中的缓存数据
当我们把要更新的那行记录从磁盘中加载到缓冲池，同时对他加锁之后，而且还把更新之前的旧值写入undo日志文件之后，我们就可以更新这行记录了，更新的时候，先是更新缓冲池中的记录，此时这个数据就是脏数据了。
3.4 写redo日志
在对缓存池中的数据更新完之后，就必须把对内存中的修改写入到一个Redo Log Buffer里去，这也是内存里的一个缓冲区，是用来存放redo日志的。所谓的redo日志，就是记录下来你对数据做了什么修改，比如"id=1这行记录修改了name字段的值为xxx"，这就是一个日志。这个redo日志其实就是用来在MySQL突然宕机的时候，用来恢复更新过的数据的。
3.5 提交事务：redo日志刷入磁盘 -> 写binlog -> 添加commit标志
上面我们提到更新缓冲池中的数据并将redo log写入到内存中，那么如何保证事务呢？我们需要考虑两种场景：1.事务还未提交，此时MySQL宕机；2.事务提交，MySQL宕机。
对于第一种情况，在redo日志写入到内存后，若MySQL宕机会导致内存中缓存池中数据及redo log丢失，但这并不要紧，因为执行一条更新语句，没提交事务，就代表没有执行成功，内存中的数据丢失，磁盘上的数据仍然是更新前的样子，这也是符合预期的。
而对于第二种情况，显然只基于内存的redo log显然不能保证事务commit后一定能执行成功，因为即使在commit时即使内存中已经有了更新后的脏数据和redo log，这个时候MySQL宕机后这些数据都会丢失。所以，在准备提交时，就必须将redo log刷入磁盘，将redo log持久化磁盘后，即使buffer pool中还存在脏数据时宕机，后面重启时依然也可以将磁盘中的redo log重新加载到内存中，这样一来对数据的更新就不会丢失了。
其实，在准备提交事务时，默认情况下MySQL还会同时把binlog写入到磁盘中，并在redo log中记录binlog文件及写入commit 标示，此时一个事务才算提交成功。
那么什么是binlog，和redo log有什么不同?实际上，redo log是一种偏物理性质的重做日志，因为他里面记录的是类似于这样的东西，“对哪个数据页中的什么记录，做了什么修改”，而且redo log本身是属于InnoDB储存引擎特有的一个东西。而binlog叫做归档日志，他里面记录的是偏向于逻辑性的日志，类似于“对users表中的id=1的一行一行数据做了更新操作，更新以后的值是什么”。
最后，当我们把binlog写入到磁盘文件之后，接着就会完成最终的事务提交，此时会把本次更新对于的binlog文件名称和这次的这次更新的binlog日志在文件里的位置，都写入到redo log日志文件里去，同时在redo log日志文件里写入一个commit标记。在完成这个事情之后，才算最终完成了事务的提交。
还有一个问题就是在redo日志中写入commit标记的意义是什么？其实，他是用来保证 redo日志与binlog日志一致的。我们在提交事务时，一共有5、6、7三个步骤，只有三个步骤全部完成即最终在redo日志中写入commit标志才能保证物理上的日志(redo log)与逻辑上的日志(binlog)一致，才能作为最终commit完成的标示。
3.6 脏数据刷入磁盘
结合之前我们提到的，执行器调用InnoDB的接口去执行一条更新语句，其实我们可以理解为：在执行更新时所要做的是1-4个步骤即 加载数据到内存 -> 写undo日志 -> 更新内存数据 -> 写redo日志，至此已经完成了数据在内存中的更改，并且为回滚以及重做准备好了可以参考的log文件。接下来，在事务要提交时，就要执行步骤5-7即 redo log刷入磁盘 -> 写binlog日志到磁盘 -> 在relog log文件中写入binlog日志名及位置以及commit标识，而至此才意味着事务完成。
那么我们想一下，内存中的数据在更改完之后其实已经和磁盘中的数据不一样了，即是脏数据，那么我们是不是需要刷新脏数据到磁盘？当然，理想情况下，在内存无限大以及redo log能够记录所有的操作情况下，即使不将数据刷新入磁盘也能保证更新数据不丢失，大不了就是在MySQL重启后根据redo log在内存中恢复一份更新后的数据就可以了。但这样肯定是不合理的，一是内存以及redo log不可能无限大，二是即是能够满足内存和redo log无限大的前提下，若是更新的数据太多那么在MySQL重启后根据redo log在内存中恢复数据必然要耗费很多时间。所以，内存中的脏数据必然要在合适的时机刷入磁盘，即步骤8，至于在什么时机将数据刷新到磁盘我们后面结合Buffer Pool的结构再来谈。
4、InnoDB缓冲池(Buffer Pool)
前面我们提到了MySQL总体架构以及InnoDB储存引擎的架构设计，在收到SQL请求时MySQL服务器负责获取SQL、解析SQL、优化SQL生成执行计划再由执行器调用InnoDB的接口去执行更新操作，而执行器在调用InnoDB时在首先在更新数据阶段将数据从磁盘加载到内存、写undo日志、更新内存中的数据、写redo log到内存中，又在事务提交阶段将redo log刷入磁盘、写binlog到磁盘、在redo log中写入binlog及commit标识，最后在合适的时机将内存中的脏数据再刷入到磁盘中。通过上图我们可以看出，磁盘数据的加载、更新以及脏数据刷入磁盘是与Buffer Pool要打交道的，那么具体数据如何在缓冲池中加载、组织以及刷新呢，接下来这一部分我们就来关注Buffer Pool的结构以及他是如何运行的。
4.1 Buffer Pool数据结构
在执行更新SQL时，若数据不在缓冲池中，则会先将数据加载到缓冲池中，而缓冲池的结构组成如下图:
数据库的核心数据模型就是表+字段+行的概念，而实际上数据在磁盘上是以数据页(默认大小是16KB)的形式存在的，每一页中放了很多数据行。在MySQL启动时会申请一块内存区域作为Buffer Pool,我们可以通过"show engine innodb status\G"来查看，以下是我个人服务器上的数据：
其实Buffer Pool是由缓存页以及相应的描述数据(或元数据组成)，缓存页就是缓冲池中一块块小的内存空间(默认是16KB)，操作一行数据时就会将这行数据所在的数据页加载到缓冲池中空闲的缓存页中，所以缓冲池中每一个缓存页都会对应磁盘中的一个缓存页(相反则不成立)。而描述数据则是对缓存页的一些描述信息如地址等信息。上图中的Buffer pool size就是缓冲池中总的大小，而Database pages则是缓存页的数量，两者之差就是描述数据的空间大小（大概相当于缓存页大小的5%左右）。
4.2 free链表
MySQL在执行操作时会将磁盘中的缓存页加载到缓冲池中，那么缓冲池中必须有空闲的缓存页可供使用，那么MySQL是如何知道是否有空闲的缓存页呢？实际上，在缓冲池中保存着一个空闲缓存页的列表，叫做free链表，如下图：
所谓的free链表，其实是借助于缓存页的描述数据实现的：free链表实际上是一个双向链表，我们可以将描述数据理解为一个节点，当某一个缓存页是空闲时那么在对应的描述数据中就会保存有相应的前驱节点(描述数据)以及后继节点(描述数据)，然后有另外一个额外的基础节点指向free链表的头节点、尾节点以及对应的数量，这样的话就可以根据空闲的缓存页的描述数据组成一条free链表，如上图。另外，free链表中的节点就是缓冲池的描述数据，缓冲池中的描述数据也只会保存一份，不会像上图画的那样有两份描述数据(只是为了方便阐述)，后面的LRU链表以及flush链表也一样。
在执行增删查改之前，MySQL会先判断数据页有没有被缓存，而查询数据页有没有在缓存中则是借助于数据页缓存哈希表，数据页缓存哈希表会使用表空间号+数据页号作为key，缓存过的数据页地址作为value。MySQL会先去数据页缓存页中查询数据页是否在缓存中，如果在，则使用缓存页进行更新，否则，会将数据页先加载到缓冲池中。
这样一来，当MySQL操作数据时如果发现数据不在缓冲池中就会向在Buffer Pool中找一个空闲的缓存页，而找空闲页的这个实现就要靠free链表来实现，即从基础节点找到free链表，当链表非空时则还存在空闲的缓存页，就将数据所在的数据页放入其中一个空闲页当中，随后将这个缓存页的描述数据在free链表中移除(前后节点指针改动一下即可)，空闲页数量-1。
4.3 flush链表
一旦要更新的数据所在的数据页已经在内存中了就可以对它进行更新了，更新后的数据依然会保留在缓冲池中，那么此时的数据就是脏数据了(和磁盘中的数据不一致)。脏数据保存在内存中，会在合适的时机刷入磁盘(后面会提及)，那么此时就要记录那些是脏数据，这份工作就需要flush链表了,flush链表结构如下图：
和free链表一样，flush链表中的节点其实也是由缓存页的描述数据组成的，也会有一个基础及诶单指向头节点以及尾节点(同样，flsuh链表节点就是缓冲池中的描述数据，不是单独的一份数据，每个节点的前驱节点及后继节点的指针保存在描述数据中)。缓冲池中的数据经过更新之后成为脏数据，此时就会将其加入到flush链表中，具体的操作就是将其描述数据接入到flush链表的节点，后续在合适的时机刷入磁盘。再看一眼之前的参数：
其中Modified db pages就是flush链表中节点的数量即脏数据页的数量。
4.4 LRU链表
前面我们提到了free链表及flush链表，前者保存空闲缓存页的信息，后者保存脏数据页的信息，那么磁盘中的数据加载到缓冲池中是否也有一个链表保存已缓存的的数据页信息呢？是的，这个链表就是LRU链表。LRU即是最近最少使用(Least Recently Used)，很多缓存策略都会用到LRU(如系统缓存，Redis缓存等)策略。首先，LRU链表可用的总大小其实就是缓冲池中缓存页大小，上图中Database pages参数就表示LRU链表中节点数(即已缓存页数量)。磁盘中的数据页要加载到内存中，首先就要去free链表中找到一个空闲的缓存页，然后将裁判中数据页加载到缓存页中，同时该缓存页的描述节点从free链表中移除，加入LRU链表，所以说白了LRU链表中保存的就是以及已经缓存过的数据页的描述数据的节点。LRU链表的整体结构和free链表以及flush链表类似，也就是是由描述数据组成的链表而已，就不再图例展示。
在InnoDB储存引擎中，缓冲池的页的大小默认是16KB，使用LRU算法算法进行管理。稍有不同的是InnoDB储存引擎对传统的LRU算法做了一些优化。在InnoDB储存引擎中，LRU列表还加入了midpoint位置。新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoin位置。在默认情况下，该位置在LRU列表的5/8处。如下图
可以看出，参数innodboldblocks_pct默认值为37，表示新读取的页插入到距LRU尾端的37%的位置。在InnoDB储存引擎中，把midpoint之后的列表成为old列表，之前的列表成为new列表，之前提到Database pages就是LRU链表长度，而Old database pages就是old列表中的缓存页数量，两者之差即new列表缓存页数量(热点数据)。这样一来，缓冲池中的缓存页就有了冷热数据的概念，看下图:
如上图所示，LRU链表中缓存的数据被分成了冷热数据，当数据页第一次被加载到缓存的时候，缓存页会被放到冷数据区域的链表头部，并不会直接加入到热数据区域。这是因为某些SQL操作可能会使缓冲池中的页被刷出，从而影响缓冲池的效率。常见的这类操作为索引或数据的扫描操作，这类操作需要访问表中的许多页甚至全部页，而这些页通常来说仅仅在这次操作中需要，并不是活跃的热点数据。如果页被直接放入到LRU列表的首部，那么可能讲真正的热点数据页从LRU链表中移除，下次需要读取热点数据时需要再次访问磁盘，频繁的磁盘随机IO肯定是影响性能的。
那么什么是热点数据呢，或者说冷数据区域的缓存页什么时候会被放到热数据区域呢？InnoDB中有一个参数是innodboldblocks_time，用于表示页读取到mid之后需要等待多久之后如果再次被访问才会被加入到LRU列表的热数据区域，这个参数默认是1s，即：页被加载到缓冲池中会被放入LRU链表的mid位置(冷数据区域的头部)，在1s之后如果该页再次被访问，则将该页加入到热数据区域。这样的话，会避免全表扫描之类的操作将热数据刷出缓冲池，又能保证真正的热点数据加入到热数据区域。使用"show engine innodb status\G"我们可以看到和LRU链表相关的参数：Database pages表示LRU列表中的数量，pages made young显示了LRU列表中移动到前端的次数，not young表示未移动的缓存页的次数，youngs/s，non-youngs/s表示每秒这两类操作的次数。
另外，在热数据区域，基于LRU算法，在数据被访问之后应该将其移动到LRU链表的头部，始终保证最热点的数据在前面。但是，热数据区域的数据是被频繁访问的，那么频繁的移动也不是太好，因此LRU链表的访问规则被优化了一下，即只有在热数据区域的后3/4部分的缓存页被访问了，才会将其在LRU链表中的数据移动到链表头部去。这样的话，就可以减少链表中的节点移动，又能尽可能地高效访问。
最后再考虑一个问题，LRU链表中的缓存页以及flush链表中的脏数据什么时候刷入磁盘。总体来说，脏数据既保存在flush链表中，又保存在LRU链表中(确切的说是数据保存在缓存页中,LRU或flush链表中的描述数据节点保存着缓存页的地址)。这样一来的话，为了保持LRU链表中尽可能的有可用的缓存页使用，以及flush中的脏数据能在合适的时机刷入到磁盘，就必须在合适的时机将LRU中的缓存页或者flush链表中的脏数据刷入磁盘。
仅仅来看LRU链表的话，首先，后台线程会定时地将LRU冷数据区域尾部的一些缓存页刷入磁盘里，清空这几个缓存页，将他们加入回free链表中去。所以实际上可能缓存页还没用完的时候，就会清空一些缓存页了。另外，如果实在没有缓存页的时候也会从LRU链表尾部淘汰一部分缓存页，把它刷入磁盘和清空，从而腾出缓存页。而flush链表中的脏数据刷入磁盘的策略，则和Checkpoint(检查点)技术相关，涉及到的情况比较多也相对比较复杂一点。而且相关的内容Jinlin以及Young之前的文章也都做了比较详细的描述，这里就不再展开说了，大体意思就是：脏数据会在一些合适的时机刷入到磁盘中。
5、总结
本文简单聊了一下MySQL及InnoDB的数据结构及执行流程，以及InnoDB Buffer Pool(缓冲池)的结构，简单分析了数据从磁盘加载到缓冲池中，以及在缓冲池中查询更新，最后再刷入磁盘的大致流程。总体来说，数据页从free链表中查找空闲缓存页，然后写入缓存页的同时加入到LRU链表及从free链表中移除，最后缓存页也会基于LRU算法进行冷热数据分离，定期淘汰不常用的缓存页，以及在合适的时机将脏数据刷入磁盘。这样，整个缓冲池处于一个动态且高效的运行状态，为我们的CRUD大大提速。更多细节大家可能参考更多书籍以及博文。
全文完
以下文章您可能也会感兴趣：
我们正在招聘 Java 工程师，欢迎有兴趣的同学投递简历到 rd-hr@xingren.com 。
297
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
