mysql数据库内存结构_mysql 内存结构

mysql的内存结构由以下几部分构成：

1.buffer pool  (data page,index page,change buffer page,adaptive hash index,lock info,data dictionary)

2.additional memory pool

3.double write

4.redo log buffer

一.buffer pool

buffer pool 大小设置

1.通常分配物理内存的80%给buffer pool

2.innodb_buffer_pool_size控制buffer pool的大小，增加或者减小innodb_buffer_pool_size的最小单位为innodb_buffer_pool_chunk_size(默认为128M)，innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances * n ；5.7之后可以动态修改buffer pool size，通过命令SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';或者错误日志可以监控动态扩展的进度；当动态扩展buffer pool时，其他节点不允许访问buffer pool。

3.innodb_buffer_pool_instances：当buffer pool很大时，并发访问buffer pool可能会紧张，因为访问buffer pool前需要获得mutex,并发访问会造成mutex的争用；所以建议把buffer pool分成多个实例，每个实例管理自己的free lists, flush lists, LRUs，不会造成mutex的争用；8.0之前，每个buffer pool实例由自己的mutex保护，8.0之后，buffer pool mutex 被list/hash mutex 取代，进一步减少争用，innodb_buffer_pool_instances的取值范围为1-64，保证每个instance有至少1g的内存，可以使buffer pool的管理更高效

buffer pool 结构

Free List：数据库刚启动的时候，LRU 列表为空，此时需要用到的时候直接将Free列表中的页删除，在LRU列表中增加相应的页，维持页数守恒。

Flush List：当LRU列表中的页被修改后，称该页为脏页(dirty page)，即缓冲池中的页和磁盘上的页数据产生了不一致。这时候数据库会通过checkpoint机制将脏页刷新回磁盘，而Flush 列表中的页即为脏页列表。注意脏页也存在于LRU列表

LRU List：1.oldsublist默认占buffer pool的3/8(此比例由innodb_old_blocks_pct参数控制)；2.lru list的分界点是new sublist的链尾与old sublist的链首的交界点；3.当innodb读取一个page到buffer pool(此读取操作可能是用户行为或者是read ahead)，此page的初始位置为midpoint；4.当在innodb_old_blocks_time(默认1000ms)再次访问midpoint处数据或者访问old sublist的page会使page 变young，即移动此page到new sublist的头部，用户的读取行为会使此页变young，而预读不会使此页变young

buffer pool 特性(read ahead)

1.线性预读

如果连续从某一个extent读取超过参数innodb_read_ahead_threshold(默认为56，可取值为0-64)个page，则读取整个extent到内存

2.随机预读

如果发现某一个extent的连续13个page在内存中，则读取整个extent到内存，控制随机预读的开关由innodb_random_read_ahead控制

buffer pool flush相关

1.buffer pool 脏页是由page cleaner threads刷，page cleaner threads的个数是由innodb_page_clearners参数控制，此参数小于innodb_buffer_pool_instances的值，默认是4

2.刷新毗邻页(innodb_flush_neighbors),此功能由innodb_flush_neighbors参数控制，默认是0，即不flush同一extent的page，当此参数设置为1时，flush同一extent相邻的page，当次参数设置为2时，flush同一extent的page

4.flush 脏页触发条件

sharp checkpoint：完全检查点，数据库正常关闭时，会触发把所有的脏页都写入到磁盘上，这就是完全检查点，数据库正常运行过程中不会使用sharp checkpoint。

fuzzy checkpoint：

flush_lru_list checkpoint：innodb_lru_scan_depth(默认为1024)表示page cleaner threads每秒 从lru list的尾部向上扫描并flush脏页的深度

dirty page too much checkpoint：当脏页的数量超过innodb_max_dirty_pages_pct_lwm(默认为0%)的时候，会触发flush，当脏页的数量超过innodb_max_dirty_pages_pct(默认为75%)，则会触发勤快的flush模式

master thread checkpoint：以每秒或者每十秒的速度从缓冲池的脏页列表中刷新一定比例的脏页回磁盘，这个过程是异步的，不会阻塞用户线程。

async/sync flush checkpoint：指的是重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。

自适应刷新：当redo log 的利用率超过 innodb_adaptive_flushing_lwm(默认10%)，会触发自适应脏页刷新，即使innodb_adaptive_flushing参数关闭，innodb_flushing_avg_loops(默认为30，即前30个页的刷新速率)可以有效调整速率

buffer pool 状态的保存和恢复

为了减少数据库restart后的暖机时间，buffer pool的lru信息会记录到information_schema的 innodb_buffer_page_lru表中(此表记录的不是page内的详细数据信息，而是tablespace id以及page id)，并会存储到innodb_buffer_pool_filename指定的file中。

innodb_buffer_pool_dump_at_shutdown(默认为on)：表示关闭数据库的时候dump buffer pool的信息到innodb_buffer_pool_filename指定的文件中

innodb_buffer_pool_dump_pct (默认为25%)：表示关闭数据库的时候dump buffer pool 的比例

innodb_buffer_pool_load_at_startup (默认为on) ：表示开启数据库时加载dump到文件中的page

change buffer

辅助索引不像聚集索引一样是有序的，通常辅助索引是无序，非唯一的，导致辅助索引在磁盘的插入是随机的，为了减少随机io的产生，引入change buffer，用来缓存没有缓存在buffer pool 的page的变化，当产生变更的page缓存到buffer pool中时，合并change buffer中此页的变更到buffer pool中，减少随机io的产生；innodb_change_buffering：all,none,inserts,deletes,changes,purges,innodb_change_buffer_max_size:25%

查看change buffer当前占buffer pool的比例：

SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages, (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages, (SELECT ((change_buffer_pages/total_pages)*100)) AS change_buffer_page_percentage;

adaptive hash index (自适应hash索引)

经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。

只能用于等值比较，例如=， <=>，in

无法用于排序

二.redo log buffer

redo log buffer大小默认是由innodb_log_buffer_size(默认16M)参数控制，刷新到磁盘的行为由参数innodb_flush_log_at_trx_commit控制：

当此参数为0时，表示事务commit不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒(此时间由参数innodb_flush_log_at_timeout控制，默认1s)执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。

当此参数为1时，表示事务commit后立即把 redo log buffer 里的数据写入到os buffer中，并立即执行fsync()操作

当此参数为2时，表示事务commit后立即把 redo log buffer 里的数据写入到os buffer中，但不立即fsync()

三.double write

double write解决的问题是部分写失败(partial page write页断裂)

部分写失败：一个数据页的大小是16K，假设在把内存中的脏页写到数据库的时候，写了2K突然掉电，也就是说前2K数据是新的，后14K是旧的，那么磁盘数据库这个数据页就是不完整的，是一个坏掉的数据页。redo只能加上旧、校检完整的数据页恢复一个脏块，不能修复坏掉的数据页，所以这个数据就丢失了，可能会造成数据不一致，所以需要double write。

流程：

doublewrite由两部分组成，一部分为内存中的doublewrite buffer，其大小为2MB，另一部分是磁盘上共享表空间(ibdata x)中连续的128个页，即2个区(extent)，大小也是2M。

1、当一系列机制触发数据缓冲池中的脏页刷新时，并不直接写入磁盘数据文件中，而是先拷贝至内存中的doublewrite buffer中；

2、接着从两次写缓冲区分两次写入磁盘共享表空间中(连续存储，顺序写，性能很高)，每次写1MB；

3、待第二步完成后，再将doublewrite buffer中的脏页数据写入实际的各个表空间文件(离散写)；(脏页数据固化后，即进行标记对应doublewrite数据可覆盖)

double write的监控

关注点：Innodb_dblwr_pages_written / Innodb_dblwr_writes

开启doublewrite后，每次脏页刷新必须要先写doublewrite，而doublewrite存在于磁盘上的是两个连续的区，每个区由连续的页组成，一般情况下一个区最多有64个页，所以一次IO写入应该可以最多写64个页。而根据以上系统Innodb_dblwr_pages_written与Innodb_dblwr_writes的比例来看，大概在3左右，远远还没到64(如果约等于64，那么说明系统的写压力非常大，有大量的脏页要往磁盘上写)，所以从这个角度也可以看出，系统写入压力并不高。