闲谈Mysql中的Buffer pool

应用系统分层架构，为了加速数据访问，会把最常访问的数据，放在缓存里，避免每次都去访问数据库。

操作系统，会有缓冲池(buffer pool)机制，避免每次访问磁盘，以加速数据的访问。

MySQL作为一个存储系统，同样具有缓冲池(buffer pool)机制，以避免每次查询数据都进行磁盘IO。

预读

磁盘读写，并不是按需读取，而是按页读取，一次至少读一页数据（一般是4K），如果未来要读取的数据就在页中，就能够省去后续的磁盘IO，提高效率。

数据访问，通常都遵循“集中读写”的原则，使用一些数据，大概率会使用附近的数据，这就是所谓的“局部性原理”，它表明提前加载是有效的，确实能够减少磁盘IO。

InnoDB的缓冲池缓存

缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。

磁盘访问按页读取能够提高性能，所以缓冲池一般也是按页缓存数据。

预读机制启示了我们，能把一些“可能要访问”的页提前加入缓冲池，避免未来的磁盘IO操作。当然并非所有数据都会放到缓冲池中，缓冲池中的数据易失且存储容量小。因此只能把“最热”的数据放到“最近”的地方，以“最大限度”的降低磁盘访问。

缓冲池淘汰算法

LRU

把入缓冲池的页放到LRU的头部，作为最近访问的元素，从而最晚被淘汰。

页已经在缓冲池里，那就只做“移至”LRU头部的动作，而没有页被淘汰。页不在缓冲池里，除了做“放入”LRU头部的动作，还要做“淘汰”LRU尾部页的动作。

但是这种情况会造成预读失效和缓冲池污染

预读失效

提前把页放入了缓冲池，但最终MySQL并没有从页中读取数据。

解决方法

需要让预读失败的页，停留在缓冲池LRU里的时间尽可能短，让真正被读取的页，才挪到缓冲池LRU的头部。以保证，真正被读取的热数据留在缓冲池里的时间尽可能长。

将LRU分为新生代(new sublist)、老生代(old sublist)，新生代的尾(tail)连接着老生代的头(head)。新页（例如被预读的页）加入缓冲池时，只加入到老生代头部。

如果数据真正被读取（预读成功），才会加入到新生代的头部。如果数据没有被读取，则会比新生代里的“热数据页”更早被淘汰出缓冲池。

缓冲池污染

当某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降，这种情况叫缓冲池污染。

解决方法

MySQL缓冲池加入了一个“老生代停留时间窗口”的机制。

假设T=老生代停留时间窗口，插入老生代头部的页，即使立刻被访问，并不会立刻放入新生代头部。只有满足“被访问”并且“在老生代停留时间”大于T，才会被放入新生代头部。

InnoDB参数

innodb_buffer_pool_size

配置缓冲池的大小，在内存允许的情况下，DBA往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。

innodb_old_blocks_pct

老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37。

innodb_old_blocks_time

老生代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部。