mysql 缓冲区 调优

文章目录

• 1、前置知识

• 1.1、Buffer Pool介绍
• 1.2、后台线程

• 1.2.1、Master Thread
• 1.2.2、IO Thread
• 1.2.3、Purge Thread
• 1.2.4、Page Cleaner Thread

• 1.3、重做日志缓冲池

• 2、Buffer Pool 组成

• 2.1、数据页
• 2.2、索引页
• 2.3、undo页
• 2.4、插入缓冲
• 2.5、锁空间
• 2.6、数据字典
• 2.6、自适应哈希索引

• 3、Buffer Pool 内存管理

• 3.0、控制块
• 3.1、Free List
• 3.2、Flush List
• 3.3、LRU List

1、前置知识

1.1、Buffer Pool介绍

MySQL数据库具有可拔插的存储引擎，其中最常用的是InnoDB，而Buffer Pool缓冲池是InnoDB存储引擎中特有的内存结构，MySQL向操作系统内存申请一块内存空间用于Buffer Pool缓冲池使用，因为硬盘和内存性能差距大，所以Buffer Pool缓冲池用于协调CPU速度和硬盘速度的鸿沟，Buffer Pool大幅度提升MySQL数据库的读写性能。

按照我们的惯性思维，这里会有一个疑问：不都说MySQL的数据是基于硬盘存储吗，为什么这里会提到Buffer Pool缓冲池内存这概念？

MySQL当然是通过硬盘持久化存储，Buffer Pool 并不是 MySQL 真正意义上存储数据的单元载体。
MySQL仅仅是借助Buffer Pool提升读写性能，毕竟内存的访问速度要比硬盘快得多！这并不冲突。

我们进行数据的查询操作，MySQL并不是直接从硬盘文件中查找对应的数据信息，会先查看Buffer Pool中是否有想要查询数据。如果有，直接返回给用户；如果没有，去硬盘中的查询想要的数据。查询到结果后会同步到Buffer Pool中，下次用户再次发起查询就不用访问磁盘了，修改操作也是同理（先操作Buffer Pool中的数据，然后数据刷入硬盘的文件中，有点像Redis），我们先站在操作系统维度看看 Buffer Pool 在内存中的样貌：

Buffer Pool缓冲池有数据页，硬盘中MySQL表数据加载到Buffer Pool中就是通过数据页来存放的，Buffer Pool默认大小128MB，InnoDB存储引擎已经将硬盘中的数据划分为一个个页，默认大小16KB，通过页为基本单位，进行硬盘与内存之间的交互。

上面提到了查询、修改等SQL操作，无论是Buffer Pool将修改的页刷盘到硬盘，还是从硬盘加载到Buffer Pool，都是以数据页为单元进行操作的，而不是操作页中的某几行数据。

这里有个注意点，Buffer Pool缓冲池并不是只有一个的，可以申请多个内存区域作为缓冲池同时工作。

1.2、后台线程

之前提到一个概念叫做刷盘，意思是Buffer Pool中缓存页数据会异步刷新到硬盘中，保证了数据的一致性，后台线程的主要作用就是对缓冲池中的页进行进行操作。InnoDB存储引擎后台线程主要有以下几种：

1.2.1、Master Thread

该线程主要用于将Buffer Pool 缓冲池中的数据进行刷盘，保证数据一致性。主要主责包括：脏页刷盘、插入缓冲合并、undo页回收等。

1.2.2、IO Thread

该线程主要用于处理AIO（Async IO）请求回调，因为InnoDB存储引擎中存在大量的异步IO操作，IO Thread可以极大数据库性能。

1.2.3、Purge Thread

当事务提交之后，undo页就没有任何存在的意义了，该线程主要职责就是回收无用的undo页。

上面1.2.1 Master Thread中提到，Master Thread主要职责就包括了回收undo页，但是后续InnoDB版本开始将部分purge操作交给Purge Thread来完成，减少Master Thread的工作压力，提升性能。也就是说回收undo页功能Master Thread和Purge Thread都具备，该线程就是为了替Master Thread分担回收undo页的工作压力。

1.2.4、Page Cleaner Thread

该线程也是为Master Thread分担工作压力，提升数据库性能。不过Page Cleaner Thread分担了什么压力呢？脏页刷盘操作。

1.3、重做日志缓冲池

硬盘中存在重做日志文件，主要用于故障恢复，保证MySQL事务的持久性，重做日志缓冲池就是用于存放重做日志信息，然后按照一定频率刷盘重做日志文件中，常用于数据库的故障恢复场景，这并不是本文章的重点。

2、Buffer Pool 组成

2.1、数据页

当我们进行查询的数据不在缓冲池中时，就会将磁盘中的数据对应的页加载到Buffer Pool中，这就是数据页。当我们对数据页内容进行修改，此时数据页就会变成脏页，而不是直接操作硬盘中的文件页，只需要将脏页刷新到磁盘中，这样通过页为单位交互性能好很多。

2.2、索引页

Buffer Pool缓冲池中，不仅会存放数据页，还会存放索引页。

之所以这样，是因为我们不能保证每次查询操作都能从缓冲池的数据页中拿到想要的结果，此时就需要对磁盘中数据文件进行IO访问操作。如果本次的查询操作命中了索引，我们又该如何知道索引的根节点到底在磁盘中的哪个位置呢？这个时候就需要索引页来帮助我们，当MySQL实例启动时，就会将数据库中的索引根节点放入到缓冲池的索引页中，当我们的查询SQL命中了索引，就不需要在整个磁盘中查找对应的索引根节点了！

2.3、undo页

undo页主要记录事务回滚操作信息，常用于事务回滚操作。

事务如何通过undolog进行回滚操作呢？这个很好理解，我们只需要在undolog日志中记录事务中的反向操作即可，例如：

事务进行insert操作，undolog记录delete操作
事务进行delete操作，undolog记录insert操作
事务进行update操作（a改为b），undolog记录update操作（b改为a）

2.4、插入缓冲

插入缓冲只针对非聚集、不唯一索引页增、删、改操作。

当我们对非聚集、不唯一索引页进行插入、修改操作时，不是直接操作索引页，而是先判断当前索引页是否在Buffer Pool缓冲池中，如果在直接操作索引页即可，如果不在就放入Insert Buffer对象中，然后以一定频率进行插入缓冲和辅助索引页合并操作，大大提升非聚集索引操作性能！

那为什么聚集索引或者说主键索引不需要插入缓冲？因为主键索引插入操作是按照主键顺序递增的，属于顺序插入，不需要随机读取硬盘，性能很快。

2.5、锁空间

锁空间就是专门用来存储锁结构、并发事务的链表的一块内存区域，这里不过多介绍。

2.6、数据字典

MySQL数据库启动时，会自动从硬盘中将系统表相关信息加载到Buffer Pool缓冲池中，有了数据字典，这样当我们使用show index、show tables相关命令就能查到表、索引相关的信息，主要分为以下：

SYS_TABLES：存储所有InnoDB表信息。
SYS_COLUMNS：存储所有用户定义的表字段信息。
SYS_INDEXES：存储所有InnoDB引擎表索引信息。
SYS_FIELDS：存储所有索引的定义信息。

2.6、自适应哈希索引

默认情况下，我们的索引页采用B+Tree的结构，大幅度提高我们对数据库的查询性能，虽然性能已经很好了，但是自适应哈希索引的性能棒不得了！O(1)时间复杂度，查询性能非常高。

自适应哈希索引不需要我们主动人为干涉，它是InnoDB自动生成的，自适应哈希索引针对是热点索引页，而不是整张表，并且生成的条件也比较苛刻。当我们对某个索引页连续的访问模式条件一样，访问模式例如：

where a = xxx
where a = xxx and b = yyy

上面举例这两种访问模式不能交替执行，否则也不会生成自适应哈希索引，那何时自动生成呢，有以下两种情况：

以某个模式访问100次
以某个模式访问 n 次（n = 页中记录 / 16）

3、Buffer Pool 内存管理

3.0、控制块

InnoDB在操作系统中为Buffer Pool缓冲池申请创建了一块连续的内存，内存被划分成一块块缓冲页（之前提过缓冲池是以页为基本单位与磁盘进行交互），InnoDB存储引擎为缓冲池中每个缓冲页都生成了一个控制块，一对一关系。

控制块中记录了数据页所属的表空间、页号、缓冲页地址、链表节点指针等信息。控制块和缓存页关系图如下：发现图中有个内存碎片，这是因为缓冲池剩余空间不够一对控制块和缓存页的大小，这点剩余内存空间就被称为内存碎片：

3.1、Free List

Buffer Pool缓冲池内存被划分为一个个页，但并不是所有页都被使用，有一些页是处于空闲状态的（没存数据），这种空闲页会被Free List进行管理，方便快速查找使用。

当硬盘中的页刷入到Buffer Pool缓冲池中时，就会从Free List中查找是否有空闲页，如果有，就将空闲页从Free List中取出使用（移除）；如果没有，就会使用后续提到的LRU List列表的尾部的数据页。下图中头节点解释：

head：指针，指向 Free List 的第一个控制块。
ail：指针，指向 Free List 的最后一个控制块。
count：数字，记录 Free List 的节点数量。

3.2、Flush List

之前提到过脏页刷盘这个操作，所谓脏页就是缓冲池中缓存页中内容发生了改变（修改、删除、新增），此时这个该页就称为脏页。脏页数据和磁盘中文件数据是不一致的，需要后台线程将数据异步刷新到磁盘中。这些脏页的管理就需要Flush List，结构图跟3.1 Free List大同小异，这里就不重复画了。

3.3、LRU List

知道了Free List维护空闲页，Flush List维护脏页，那么LRU List维护的是什么页？

LRU List用来管理已经读取的页，所以当数据库刚启动时，LRU List也是空的，这时候的空闲页都在Free List中，当需要从硬盘中加载数据页到Buffer Pool时，就会从Free List查找是否有空闲页可以使用，如果没有空闲页就根据LRU算法淘汰LRU List尾部页，将内存空间分配给新页。

硬盘中的页加载到缓冲池中，没有任何修改操作，那就说这个缓冲页是干净的（干净页），或者说脏页数据刷盘到磁盘后，就变成了干净页。不过有一点需要强调，当我们对干净页进行修改操作时，也就是它变成了脏页，此时脏页也不会从LRU List中移除，这个脏页将会同时存在于LRU List和Flush List中。

关于脏页是否同时在LRU、Flush List中存在，这里有些争议，有些人认为脏页不在LRU List中记录，只在Flush List中记录；
不过《MySQL技术内幕 InnoDB存储引擎》这本书中介绍的是：脏页既存在于LRU List，也存在于Flush List

LRU List管理缓存页是通过LRU算法，就是说访问频率低（最近最少使用）的缓存页将会放到LRU List列表尾部，访问频率比较高的热点页将会放到LRU List首部，当可用的空闲页不足时，就会淘汰LRU List链表末尾的数据页。我们先来看下LRU List大致是什么样子：

LRU List的LRU算法跟常规的LRU算法是有区别的，InnoDB之所以使用特殊的LRU算法，主要是考虑到传统的LRU算法有这两个问题：

• 预读无效
• Buffer Pool污染

预读的意思是 Buffer Pool 在加载数据页时，会把它相邻的数据页一起加载到缓冲池中，目的是减少了磁盘IO操作。不过常规LRU算法会将预读的数据页也放置到LRU List头部，这样可能出现预读数据页几乎不会使用到（大大降低LRU List的使用性能）。

Buffer Pool污染大概也是这个意思，如果偶尔做一次大数据量的表查询操作（全表扫描），直接出现许多不常用数据页在LRU List头部，导致本身的热点页被移除。降低了LRU List使用性能。

针对以上常规LRU算法所带来的问题，LRU List是用了特殊LRU算法。上图中可以看到midpoint，通过midpoint为分界线，将midpoint左侧数据页区域称为NEW区，右侧称为OLD区。NEW区域的数据页是经常使用、访问的，这些数据页我们称之为热点页，midpoint位于LRU List链表的5/8处（37 : 63），这个比例可以通过参数innodb_old_blocks_pct调整，这样我们最新访问的数据页不会直接放到NEW区域的头部，而是放到OLD区域的头部。

那么什么时候会从OLD区移动到NEW区呢？InnoDB存储引擎通过一个时间参数innodb_old_blocks_time控制页读取到midpoint位置时，等待多久才会加入到NEW区，这个时间默认为1000ms。如果后续的访问时间与第一次访问的时间不在这个时间间隔内，那么该缓存页就会移动到 NEW 区域的头部，这就是LRU List管理缓存页的方式。