Mysql原理与调优-Mysql的内存结构

1.绪论

前面说过InnoDB每次查询数据或者更新数据，都是先以16kb的大小将数据读取到内存中，然后对内存中的数据页进行操作。为了减少磁盘IO，Innodb的会先单独的申请一块连续的空间，将从磁盘中的数据页缓存到这片内存中。这片内存就是BufferPool。

2.BufferPool原理

2.1 什么是BufferPool

BufferPool是一块连续的内存区域，主要用来缓存已经从磁盘加载的页。Buffer Pool主要由数据页buffer和change buffer、log buffer三部分组成。而数据页buffer主要存储两部分内容，控制块和数据页。数据页其实就是从磁盘中加载出来的数据内容。控制块是存储数据页的一些元信息,比如页所属的表空间号，页号等。其中数据页部分大小可以通过innodb_buffer_pool_size这个参数进行控制，默认大小为16kb。

2.2 如何判断某个内存页是否已经被缓存到BufferPool中

Mysql会建立一个key为的表空间号+页号，value为控制块的一个hash表，每次访问某个数据页的时候，首先会根据表空间号和页号在该hash表中进行访问，判断该页是否已经加入到了BufferPool中。

3.3 Page页的分类

数据页区的Page分为3类：

1.free page: 未被使用的page页。

2.clean page:已经有数据的page页，并且和磁盘数据保持一致。

3.dirty page：当前数据页和磁盘数据不一致。

为了方便管理这些不同类型的数据页，Mysql通过指针，将同一类型的列串联起来，形成了不同的链表，比如全是free page的链表free list，全是dirty page的链表flush链表。当BufferPool已经全部被写满，但是又需要加载新的page到pool中，这个时候需要淘汰部分页面，Mysql采用的是Lru算法进行淘汰，需要维护一个Lru链表。接下来，我们就来看看这些链表的组成。

3.3 BufferPool中的链表

3.3.1 free 链表

free 链表是由free page的控制块通过指针链接成的一个链表。每次需要从磁盘加载数据时，优先从free链表中取出一块free page，并且将free page的信息从free链表中摘掉，表示该块free page已经被使用。

3.3.2 flush链表

当Buffer Pool中的数据页被修改过后，会被标记为脏页，并加入到flush链表中。Mysql会启动一个线程，将数据定期写入到磁盘中。前面讲缓存的时候，提过，这种模式其实就是异步缓存写入。Mysql是如何将内促中的数据刷入到磁盘中的，我们后面将会介绍。

3.3.3 Lru链表

当Buffer Pool中存储已满，但是需要新的空间的来存储新的页，就需要淘汰一部分旧的数据页。Mysql采用Lru机制进行淘汰。

1. 传统Lru机制

Lru就是最少最近使用，会维护一个lru链表，每次有新的数据页被访问，便会将该数据页加入到链表头部。如果该数据页已经在链表中，也会将其挪移到链表头部。这样链表头部的数据便是最近被访问的数据，每次淘汰只需要淘汰链表尾部的数据即可。

2.传统Lru机制的不足

1.如果执行全表扫描，链表头部的热点数据会被的扫描的数据会被淘汰掉。

2.Mysql存在预读机制，会将一些未被使用的page页加载到内存中，这些页也会将热点数据淘汰掉。

Mysql的预读分为随机预读和线性预读。

线性预读：当磁盘某个区的数据超过innodb_read_ahead_threshold（默认56）页被加载到内存中，会将该区的数据全部加到内存中。

随机预读：如果已经缓存某个区的连续页面超过13个，会将该区的数据全部加载到内存中。

3.改进Lru算法

为了解决上述问题，Mysql将Lru链表分成了两个区域，分别是young区，和old区。

1.数据到达时，先加入old区的头部；

2.如果在Lru链表中存活时间超过1s(由innodb_old_blocks_time控制 )，则会加入到链表头部。

3.4 change buffer

3.4.1.什么是change bufffer

当对二级索引进行进行DML操作（增删改）时，会将sql语句存储到change buffer中，而不会立刻去将磁盘数据加载到内存中，进行更改后刷入磁盘。这样会减少磁盘IO。change buffer 默认大小为buffer pool的25%。可以通过innodb_change_buffer_max_size进行设置。

3.4.2 什么时候将数据刷入到磁盘

1.当change buffer的sql被访问页被加载到内存中时，会实现merge操作。

2.后台线程定期merge。

3.当Mysql实例被关闭时。

3.4.3 change buffer的适用场景

change buffer主要用户写多读少的场景，比如日志类系统。

3.5 Log buffer

Log buffer分为undolog buffer和redolog buffer，他们主要用来缓存的undolog日志和redolog日志。接下来我们可以看看一条事务的执行流程。

1.执行事务的时候，首先记录的事务的undo log日志到 undo log buffer中，会定时将buffer中的redo log日志刷入磁盘。记录undo log主要是为了方便回滚，他是逻辑日志(记录的执行sql语句的相反语句)。

2.将数据写入到redo log buffer中。redo log是物理日志，它的主要作用是保证数据不丢失。

3.将需要修改的page页从磁盘中加载到buffer pool中，并标记位脏页。其中会通过刷盘机制，将数据刷入磁盘。

4.commit事务后，会将redo log buffer中的redo 日志通过二阶段提交的方式持久化到磁盘中。

5.提交事务的时候，也会同时提交bin log日志。

3.6 多实例buffer pool

为了增大并发，一般buffer pool大小超过1g时，可以通过innodb_buffer_pool_instances设置多个buffer pool，这个时候每个buffer pool实例大小为innodb_buffer_pool_size/innodb_buffer_pool_instances。

4. buffer pool的优化

4.1 buffer pool的优化评估

可以通过的以下几个参数查看缓存的命中率：

SHOW STATUS LIKE 'innodb_buffer_pool_read%'

得到结果如下：

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

参数1: innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
参数2: innodb_buffer_pool_read_requests：表示从内存中读取页的请求数。

如果命中率小于90%,可以考虑增加缓存。

4.2 buffer pool调节

buffer pool的关系如上图所示：

buffer pool：可以通innodb_buffer_pool_size设置大小；

instance:一个buffer pool由多个instance组成，可以通过innodb_buffer_pool_instances设置instance的数量；

chunk:一个instance由多个chunk组成，可以通过innodb_buffer_pool_chunk_size设置chunk的大小

page:一个chunk包含多个page，可以通过innodb_page_size设置page大小，默认为16kb。

4.3 buffer pool中数据页的状态

可以通过如下命令查看:

show global status like '%innodb_buffer_pool_pages%';

解释如下：

pages_data: InnoDB缓冲池中包含数据的页数。 该数字包括脏页面和干净页面。

pages_dirty: 显示在内存中修改但尚未写入数据文件的InnoDB缓冲池数据页的数量（脏页刷新）。

pages_flushed: 表示从InnoDB缓冲池中刷新脏页的请求数。

pages_free: 显示InnoDB缓冲池中的空闲页面

pages_misc: 缓存池中当前已经被用作管理用途或hash index而不能用作为普通数据页的数目

pages_total: 缓存池的页总数目。单位是page。

4.4 log buffer的优化

4.4.1 log buffer的相关参数

1.设置log buffer的大小

show variables like 'innodb_log_buffer_size';

2.设置多少个log buffer文件

show variables like 'innodb_log_files_in_group';

3.redo log file文件大小

show variables like 'innodb_log_file_size';

一般存储redo log日志是采用两个文件循环写入的方式。

1.如果这两个文件大小过小，会导致两个文件频繁切换。如果开启的是一个大事务，所有日志文件都写完了，但是事务还未提交，这样日志也无法切换。

2.但是redo log日志过大，当Mysql宕机后，重启恢复可能耗费时间过长。我们一般要求log file的大小能够承载一小时的日志量。