从零开始带你成为MySQL实战优化高手学习笔记（二） Innodb中Buffer Pool的相关知识

 

目录

一、为什么要有Buffer Pool？

二、Buffer Pool的大小设置多少合适？

1、buffer pool的默认大小是多少？

2、何时应该调整buffer pool的大小？

3、InnoDB缓冲池当前到底使用了多少内存？

4、何为InnoDB页面？

三、Buffer Pool是如何初始化的？

1、如何知道哪些缓存页是空的？

2、如何知道磁盘中的数据页有没有被缓存？

3、用什么定位缓存中更新过的数据？

---

一、为什么要有Buffer Pool？

 

在上篇文章从零开始带你成为MySQL实战优化高手学习笔记（一）中学习到了一条语句到底是怎么执行的。

先后经历了获取数据、解析sql语句表达的意思，找出最优查询路径，调用存储引擎接口，然后又简单了解了存储引擎是怎么工作的。

为什么要搞这么多东西，直接对磁盘进行处理不就好了吗？慢，太慢了！哪有内存快。

 

所以，实际上对数据库的增删改都是在内存中的buffer pool中进行的，我们都知道，当断电后内存中的数据不会保存，所以又有了redo log机制，还涉及到undo log、binlog等等来确保当数据库出现宕机，数据不错乱。

提示：可以使用命令：SHOW ENGINE INNODB STATUS来查看关于buffer pool的相关数据。

 

二、Buffer Pool的大小设置多少合适？

在MySQL5.5之前，广泛使用的存储引擎是MyISAM，它使用操作系统缓存来缓存数据，但InnoDB使用buffer pool。

那buffer pool的默认大小是多少？什么时候需要修改？又如何修改那？

1、buffer pool的默认大小是多少？

buffer pool的大小由参数innodb_buffer_pool_size设置，默认为128M，可以通过以下语句查询

mysql> show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set, 1 warning (0.01 sec)

通过以下语句设置

mysql> SET GLOBAL innodb_buffer_pool_size = 4294967296  

但实际上，InnoDB保留了额外的内存，所以实际总分配空间要比设置的值要大，额外的内存是干嘛的，后面讲。

注意：缓冲池大小必须设置为innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数，如果不是，缓冲池会自动调整。

2、何时应该调整buffer pool的大小？

有一个计算公式可以计算缓冲池性能

innodb_buffer_pool_reads/innodb_buffer_pool_read_request *100

innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。

innodb_buffer_pool_read_requests：表示从内存中读取的请求数。

如果值很小，意味着缓冲池大小可以满足大部分请求，从磁盘读取所占比例很小，就无需修改buffer pool的大小。

还有一个InnoDB buffer pool 命中率的计算公式：

InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100

如果这个值低于0.99，你就考虑增加buffer pool大小吧

3、InnoDB缓冲池当前到底使用了多少内存？

通过把可用数据与InnoDB页面大小相乘，可以得出实际使用内存大小。

//可用数据
select variable_value from performance_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data'
//注意： MySQL5.7.6以后，GLOBAL_STATUS表中提供的信息从Performance Schema获取，之前的版本请写为information_schema

//页面大小
select variable_value from performance_schema.global_status where variable_name = 'innodb_page_size'

InnoDB页面？

4、何为InnoDB页面？

你想，数据库中的数据最终都是要存到磁盘中的，怎么存？按行？按列？二维数组存一个表？

InnoDB页面（数据页）是mysql抽象出来的数据单位，每一页数据有很多行。默认一页数据16KB。存到buffer pool里就叫缓存页，

但是光一个缓存页是不行的，对整个表来说，我最起码得知道它是属于谁的吧？

 

所以，每个缓存页还具有描述信息，包含所属表空间，缓存页编号，在buffer pool中的地址等等，所以前边说实际总分配空间要比设

置的值大。

大体上，用下图可以描述：

三、Buffer Pool是如何初始化的？

数据库启动时，会按照设置的大小去内存申请一块内存（应该是连续的，并且比设定值大一些）,然后根据每页16KB的大小和描述数据去划分每一个缓存页。

所以刚开始，肯定缓存页中都是空的。但是，怎么知道哪些缓存页是空的呐？

1、如何知道哪些缓存页是空的？

这就需要引入一个双向链表，数据库实现了一个free链表，每个节点就是一个空闲的缓存页的描述数据块的地址，注意，不是缓存页的起始地址，是缓存页的描述数据块的地址。

free链表是带头结点的链表，头结点存储链表的长度，也就是空闲缓存页的数量。

又加了一个链表，空间开销是不是有点大？其实并不是这样，free链表本身就是由描述数据块组成的，只不过每个数据块多了两个指针。

2、如何知道磁盘中的数据页有没有被缓存？

前面有说，在做增删改查的时候最开始都是去buffer pool中找，如果没有再把磁盘中的信息同步到buffer pool中，那数据库是怎么知道磁盘中的数据页有没有被缓存呐？

这时，数据库又加了个哈希表数据结构，用表空间+数据页号作为key，缓存页的地址作为value，每次从磁盘读取数据页到内存后，哈希表中就会存一条数据，如果下次再次使用这条数据就可以直接从哈希表中找到缓存页地址。

 

如果你看过上篇文章，肯定知道更新数据时最先更新buffer pool中的数据，然后再刷到磁盘中，那么问题来了。

3、用什么定位缓存中更新过的数据？

双向链表flush，原理和free链表一样，当缓存页中的数据更新，缓存页的描述数据块的地址就会存在flush链表中。

 

系列学习笔记：

从零开始带你成为MySQL实战优化高手学习笔记（一）

从零开始带你成为MySQL实战优化高手学习笔记（二） 关于buffer pool的相关知识

从零开始带你成为MySQL实战优化高手学习笔记（三）MySql byffer pool的运行过程

 

欢迎关注微信公众号，公众号的好处是可以持续保持联系。