main的MySQL系列之：尬聊buffer pool

从一个简单的sql语句说起

select name from order_base where id = 1;

MySQL大体分为Server层和存储引擎层，内置函数都是Server层实现，跨存储引擎的功能如存储过程、视图、触发器等也是在Server层实现的。

大体执行流程就是

• 客户端与连接器建立连接，验证密码，获取权限
• 判断查询缓存，如命中直接返回结果
• 分析器进行词法分析出select是一个查询，order_base是表名，name是列名，并判断表、列是否都存在
• 分析器进行语法分析，分析语句是否符合语法规则
• 优化器确定语句的执行方案
• 执行器判断这个用户对表是否有查权限，后根据引擎的定义去使用引擎提供的接口
• 调用innodb获取满足条件接口的第一行，之后获取满足条件的下一行

更新语句也类似，如：

update order_base set name = "b" where name = "a";

会调用innodb满足条件的第一行，然后修改这行，然后调用写接口，然后获取满足条件的下一行，以此类推。

其中并不是所有的数据都是从磁盘中直接读取的，而是使用了一个名为buffer pool的缓存池。

buffer pool

首先要明确一个概念，就是MySQL在磁盘读写时候，不是按需读取，而是按页读取(默认页大小16kb)。在进行读操作时候，首先会判断页是否在buffer pool中，如果存在就直接返回，如果不存在就从磁盘读页然后放到buffer pool中；如果一个更新操作，也会首先会判断页是否在buffer pool中，如果存在就直接返回，如果不存在就从磁盘读页然后放到buffer pool中，然后更新buffer pool(更新操作还会涉及到redo、binlog、插入缓冲等等等)。

通俗点来说，buffer pool就是一个LRU链表，传统的LRU链表是在插入的时候将插入的节点放到头节点，如链表长度过长就删除尾结点，在更新、查找时候将节点放到头结点。

但是考虑MySQL是按页加载，在一次全表扫描时候，LRU会插入大量页并将高频访问页移除。又因为预读机制，数据被逻辑存放在一个表空间tablespace中，表空间由段segment、区extent、页page组成，预读机制会预读一些额外页进buffer pool中，如这些额外的不是高频访问的，会将LRU列表的高频数据清空，这种被称为缓冲池污染。

其中：

• 随机预读:当一个区中随机13个页面(13为默认值)被加载到buffer pool中，会将这个区中所有页面都加载到buffer pool中。随机预读默认是关闭，由变量innodb_random_read_ahead控制
• 线性预读:当一个区中有连续56个页面(56为默认值)被加载到buffer pool中，会将这个区中的所有页面都加载到buffer pool中

所以MySQL缓冲池LRU分为young区(也有的叫new-sublist)和old区(也有的叫old-sublist)来防止缓冲池污染，young在列表头部为热数据，分界点为midpoint(默认37%)，就是有37%的数据是old区。

如有一个页号50的新页被加入到buffer pool，50会被加入old列表头部，50会比young区的页更早被淘汰，old列表尾部页号为7的会被淘汰。

然后过了一段时间页号50被读到，50这页会被加入young区的头部，此时并不会有页被淘汰。

这里可以查看innodb引擎状态来观测buffer pool。

show engine innodb status

注意show engine innodb status并不是实时的，如下图表示是32秒前的状态：

其中Pages made young表示数据页从old区移动到young区的累积量，Pages made not young表示数据从old区被淘汰的累计量，youngs/s和non-youngs/s代表每秒的次数。

如果non-youngs/s很高，意味着有很多全表扫描（存疑很高是多高，多高会影响性能，怎么衡量）。

还可以使用语句来查看buffer pool状态：

select * from information_schema.innodb_buffer_pool_status

‍ 注意，在数据库启动的时候之后，所有插入的数据页都是young page，根据5.6的源码，当有大于512个数据页时候才会发生young、old调整。

可以通过语句来查看每个LRU页都存了什么：

select * from information_schema.innodb_buffer_page_lr

如当前innodb_buffer_pool_status的状态为：

说明buffer pool一共有8191个数据页(使用的和没使用的)，其中有7170个Free List可以被使用，有1020个页已经有数据了，old列表上面有356个页，有0个页被修改了(MODIFIED_DATABASE_PAGES)，已经有503个页从old列表移动到了young上面：

select page_type,count(page_type) from information_schema.innodb_buffer_page_lru group by page_type;

可见这1020个数据页上面，有611个是索引页，259个undo页。

Free List

当一个新的数据页被加入到buffer pool时候，是通过Free List来判断是否有空闲页可被加入，空闲链表被称为Free List，如没有空闲缓存页，就要从old列表中删除页。

上图的控制块被称为ctl，ctl有一个指针指向缓存页，一个ctl和一个缓存页为一个block，buffer pool中block的数量是一定的，最后会因为空间不足以存放一个block而产生碎片。

Free List上有指针指向一个ctl，每当需要从磁盘中加载一个页到Buffer Pool中时，就从Free List中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的Free链表节点从链表中移除(如果删除的页存在数据修改，还会触发刷脏页)，表示该缓存页已经被使用了。

buffer pool实例

buffer pool会有多个实例，通过innodb_buffer_pool_instances来配置，每个实例大小为innodb_buffer_pool_size/innodb_buffer_pool_instances，需要注意的是当innodb_buffer_pool_size小于1G时候，buffer pool实例总是一个(存疑，buffer pool应该配置多大才会最大限度提高性能)。

各个实例之间没有竞争关系，可以并发读取与写入(存疑，如何保证并发写的顺序问题)。所有实例的内存大小在数据库启动的时候被分配，直到数据库关闭内存才予以释放。每个实例有一个page hash链表，使用space_id和page_no就能快速找到已经被读入内存的数据页(存疑，找是在哪个阶段查找的？？个人认为是执行器阶段？？查找的具体执行细节是什么？？)，而不用线性遍历去查找。通过space id和page no可以直接找到对应的数据页，如果找不到那就要去磁盘查找。

当时用show engine innodb status或者information_schema.innodb_buffer_pool_status来查看buffer pool状态，可以看见多个实例。

我感觉buffer pool是重中之中，里面不但存放了数据页，还有undo页、索引页、插入缓冲页、自适应hash、锁信息等等，感觉MySQL的原理性概念都离不开这个缓冲池，我对这块理解也很薄弱，希望以后能更深入了解一下吧～