MySQL必知必会：用十一张图讲清楚，当你CRUD时BufferPool中发生了什么！以及BufferPool的优化！（一）

MySQL必知必会：用十一张图讲清楚，当你CRUD时BufferPool中发生了什么！以及BufferPool的优化！（一）
一、收到了大佬们的建议#

1、篇幅偏短 建议稍微加长一点。

这点说的确实挺对 有的篇幅确实比较短 针对这个提议我会考虑将相似的话题放在一篇文章中。但是这可能会导致我中断每天更新的步调 换成隔几天发一篇的步调 但是这个系列的文章一定会写完的

2、Buffer Pool、LRU List、Flush List、Free List相辅相成 建议放在一起串讲。

说的没错 是应该一起串讲。于是有了这篇加餐的文章 下面让我们就一起看下 当你执行CURD时 InnoDB的Buffer Pool中都发生了什么 以及Buffer Pool的优化

二、Let‘s go#

你知道的 MySQL对数据的增删改查都是内存中完成的 这块内存就是Buffer Pool。

你可以像下面这样查看下你的MySQL的Buffer的Buffer Pool的默认大小

上图中的0.125单位为GB 转换成MB就是 1024* 1/8 128MB

总结来说 就是MySQL启动后就会为我们初始化好这块Buffer Pool。如下图

你可以看着上图 然后读下面这段话

MySQL以数据页为单位 从磁盘中读取数据。数据页被读取到内存中 所谓的内存其实就是Buffer Pool。

Buffer Pool中维护的数据结构是缓存页 而且每个缓存页都有它对应的描述信息。

由于MySQL刚启动 还没有从磁盘中读取任何数据页到内存 Buffer Pool 中 那此时Buffer Pool中所有的缓存页其实都是空的。

除了缓存页之外 你还能看到Buffer Pool中存在三个双向链表。分别是FreeList、LRUList以及FlushList。这三个双向链表中维护着缓存页的描述信息。

三、好 假设你读取出来了1个数据页#

当你通过select读取出一个数据页之后 是需要将这个数据页加载进Buffer Pool中的缓存页中的。

那问题来了 MySQL怎么知道该将你读取出来的数据页存放在那个缓存页中呢 相信你看了上图应该也能想到答案了。FreeList这个双向链表不是存放了空闲的缓存页的描述信息吗 那从FreeList中取出一个空间缓存页的描述信息不就好了 于是得到了下面这张图

啰嗦一点 对这张图稍微做一下解读

InnoDB会将你读取出来的数据页加载进Buffer Pool中的缓存页中 然后缓存页的描述信息也会被维护进LRU链表中。链表做了冷热数据分离优化 5/8的区域是热数据区域 3/8的区域算是冷数据区域。 本质上它们都是双向链表 而你新读取的数据页会被放在冷数据区的靠前的位置上。

如果你将该数据页读取出来加载进缓存页中后 间隔没到1s 就使用该缓存页。那么InnoDB是不会将这个描述信息移动到5/8的热数据区域的。

但是当超过1s后 你又去读这个数据页。那这个数据页的描述信息就会被放到热数据区域。如下图

四、假设你一次性读取出来了好多数据页#

白日梦在第 6 篇文章中跟大家分享过 MySQL是存在预读机制的 感兴趣可关注公众号阅读。

假设触发了MySQL的预读机制。一次性从磁盘中读取来N多个缓存页。会得到下面这张图:

因为发生了预读 所以你的一次磁盘IO读出了大量的数据页 但是这些数据页中很可能是有一些是你根本不需要的 仅仅是预读把它们级联查出来了。这时按老规矩 从FreeList中找到空闲的缓存页信息 然后将其从FreeList中移除。根据找到的空闲缓存页的描述信息 将从磁盘中读取出来的数据页加载进去。相应的该缓存页的描述信息也会被维护进LRU链表的冷数据区域。

这时你就会发现这种冷热数据分离的机制多么妙 即使发生了预读又怎么样 根本没有机会将热数据区的描述信息1挤下去。当内存不够用了需要将部分缓存页刷新到磁盘中时 那就从冷数据区域开始刷新好了 反正他们本来就不经常被使用。

同样的 当你超过1s后又访问了冷数据区的缓存页 比如访问了缓存页66和数据页67 该缓存页对应的描述信息是会被提升到热数据区 于是有了下面这张图

那 如果你访问上图中的数据页67 它会移动到描述信息66所在节点的前面去吗

其实MySQL的LRU链表做了优化 数据67是不会往前跑的。

五、假设你修改了某数据页#

假设你执行了update xxx set xxx where id in (xxx,xxx,xxx,xxx)

而符合条件的数据行恰巧就在描述信息1、描述信息66、描述信息67所指向的缓存页中 那BufferPool中会发生什么呢

如下图

你会看到 被你修改了的缓存页的描述信息 被添加到了FlushList这个双向链表中。

想必看到这里你已经知道了 原来FlushList中的节点存放就是被修改了脏数据页的描述信息块。

随着MySQL被使用的时间越来越长 BufferPool的大小就越来越小。等它不够用的时候 就会将部分LRU中的数据页描述信息移除出去 这时如果发现被移除出来的数据页在FLushList中 就会触发fsync的操作 触发随机写磁盘。如果该数据页是干净的 那移除出去就好了。其他也不用干啥。

举个例子 假设需要将描述信息66、描述信息67指向的缓存页落盘。会得到下面这张脑图

描述信息66、67指向的缓存页被刷新进磁盘。 同时从FlushList中将其移除 然后存入FreeList中。完成一个循环

当然 将脏数据页刷新进磁盘的时机除了上图中说的还有好多种情况 白日梦在上一篇文章中有分享。可关注公众号查看哦

下面再看一下关于Buffer Pool的设置和相关的优化。

这样的优化对 MySQL 来说作用微乎其微 今天，我们在这里不展开说明这些问题，而是跟大家介绍在这些优化的层面中，有哪些是优化对 MySQL 数据库来说作用微乎其微，以便我们在产生环境中调优 MySQL 数据库时，避免一些不必要的优化。
华为大佬的“百万级”MySQL笔记，基础+优化+架构一键搞定 MySQL不用多说，大家都知道它是目前最为活跃热门的开源数据库，由于成本低，操作简易的特点，所以在互联网企业中被广泛使用，即使是头部的BATJ。由此可见，想要在互联网行业混得风生水起，或者说想要进入BATJ等一线互联网公司，那么熟练掌握MySQL必定是一块必要的敲门砖。
我又吊打面试官了，凭借MySQL海量数据优化（理论+实战） 朋友们，又见面了，上篇文章咱们讲到MySQL分库分表的方法，这篇文章咱们就针对上一篇文章模拟在MySQL中海量数据的优化方法，文章干货较多，建议三连。 提示：以下是本篇文章正文内容，案例仅供参考