配置 Buffer Pool

通过如下两个参数设置BP:
innodb_buffer_pool_size: 设置总容量(bytes)，该值设置的越大越好；5.7 之前需要重启，5.7 开始可以在线修改（调大调小均可）
innodb_buffer_pool_instances: 设置分配多少个缓冲池，设置为多个缓冲池可将热点打散，提高并发性能（建议设置成CPU个数值），默认值是 8 (or 1 if innodb_buffer_pool_size < 1GB)。只有当 innodb_buffer_pool_size 大于或者等于1G时设置该参数才有效。

Buffer Pool 中的链

1.Free List：Buffer Pool 刚启动时，有一个个 innodb_page_size 大小的空白的页，这些页通过 Free List 串联起来
2.LRU List：当读取一个数据页的时候，就从 Free List 中取出一个页，存入数据，并将该页放入到 LRU List 中
3.Flush List：当 LRU List 中的页第一次被修改了，就将该页的指针放入 Flush List （类似 Oracle 的检查点队列）

注意：这些 List 保存的是指向页的指针。LRU List 存放的是所有已经使用的页，里面既有干净页也有脏页，Flush List 中只有指向脏页的指针。

查看 Buffer Pool 的状态

mysql> show engine innodb status\G
-----------------省略其他输出-----------------
Total large memory allocated 137428992
Dictionary memory allocated 167069
Buffer pool size   8191   -- 该Buffer Pool中有多少个页
Free buffers       6958   -- 该Buffer Pool中有多少个空白页（Free List），线上可能看到为0
Database pages     1233   -- 该Buffer Pool中使用了多少页（LRU List）
Old database pages 474    -- old pages（见3.4）
Modified db pages  0      -- 脏页
Pending reads      0
-----------------省略其他输出-----------------

还有一种方法就是查看 information_schema.INNODB_BUFFER_POOL_STATS 视图查看 Buffer Pool 总的使用情况。

通过如下查询可以详细地查看到 LRU 列表上每一个页的情况：

mysql> select * from information_schema.INNODB_BUFFER_PAGE_LRU limit 1 \G

*************************** 1. row ***************************
            POOL_ID: 0                -- 该页在哪个 POOL 中
       LRU_POSITION: 0                -- 该页在 LRU 中的位置
              SPACE: 0                -- space id 表空间号
        PAGE_NUMBER: 7                -- 对应的页号
          PAGE_TYPE: SYSTEM
         FLUSH_TYPE: 1
          FIX_COUNT: 0
          IS_HASHED: NO
NEWEST_MODIFICATION: 537468900        -- 该页最近一次（最新）被修改的LSN值
OLDEST_MODIFICATION: 0                -- 该页在 Buffer Pool 中第一次被修改的 LSN 值，FLush List 是根据该值进行排序的，该值越小，表示该页应该最先被刷新
        ACCESS_TIME: 1601582842
         TABLE_NAME: NULL
         INDEX_NAME: NULL
     NUMBER_RECORDS: 0
          DATA_SIZE: 0
    COMPRESSED_SIZE: 0
         COMPRESSED: NO
             IO_FIX: IO_NONE
             IS_OLD: NO
    FREE_PAGE_CLOCK: 0

通过如下查询可以详细地查看到 Flush List 列表上每一个页的情况：

mysql> SELECT *
FROM information_schema.INNODB_BUFFER_PAGE_LRU
WHERE oldest_modification <> 0
AND oldest_modification <> newest_modification;

LRU List 的管理

    New page --------------------------- mid point -------- Old page

• mid point 的位置由 innodb_old_blocks_pct 参数控制，默认是 37，表示整个链的 37%处，即 3/8 的位置。
• LRU 中 new page 和 old page 是在一个链表上的，访问多的就从 mid point 排到了链表的前面，冷的页就慢慢被挤到了 old page 中，如果 old 中的数据继续被多次访问，还是会回到 new 中。
• 当 Free List 中没有空余的页时（生产环境很可能为0），就需要从 old page 中最后的页（被淘汰的页）给取出，给新的查询所使用，如果被淘汰的页是脏页（page number 在 Flush List 中），则需要先刷回磁盘后，再给新的查询使用。
• 当该页被第一次读取时，将该页先放在 mid point 的位置，因为无法保证该页一定是活跃的（即后面还会被读到）；当被第二次读到时，才将该页放入到 new page 的首部。但实际情况也并不是该页第二次读取时就立即放到 new page，第一次读取后，在 innodb_old_blocks_time 时间内再次被读取也不算数，只有 innodb_old_blocks_time 时间后，再次被读取，才算数(默认 1000ms，即1s)。innodb_old_blocks_time 主要是为了防止扫描语句污染LRU。例如，在一个全表扫描访问中，一个数据页会有多条记录，因此一个数据页会被访问多次，如果不做这个时间限制，该页就会被放到热区，但实际后面却根本没有再使用该页，该页并不是真正的热页。

Buffer Pool 的预热

在 MySQL 刚启动后，Buffer Pool 中页基本都是空的，没有业务数据，需要大量的时间才能把磁盘中的页读入到内存中，导致启动后的一段时间性能很差。

5.6 之前可以使用下面的方法人工预热，强制扫描，将数据刷入 buffer pool，但是不能真正将热点数据放入buffer pool：

mysql> select count(1) from table force index(PRIMARY)
mysql> select count(1) from table FORCE index(index name)

5.6 开始，MySQL 提供了相关参数来人工预热或者自动预热：

mysql> show variables like 'innodb_buffer_pool_dump_pct';            对每个 buffer pool DUMP 出的百分比
mysql> show variables like 'innodb_buffer_pool_filename';            DUMP 出的文件名

mysql> show variables like 'innodb_buffer_pool_dump_at_shutdown';    在停机时自动 dump 出 buffer pool 中的（space,page）
mysql> show variables like 'innodb_buffer_pool_load_at_startup';    启动时自动加载 dump 文件，恢复到buffer pool 中

mysql> set global innodb_buffer_pool_dump_now = 1;                    立即 DUMP，在后台加载的，返回的速度很快
mysql> set global innodb_buffer_pool_load_now = 1;                    立即加载 DUMP 文件，在后台加载的，返回的速度很快

DUMP 和 LOAD 的日志（进度）可以从 error log 中看到，可以使用下面的命令查看状态：

mysql> show status like 'Innodb_buffer_pool_dump_status';
mysql> show status like 'Innodb_buffer_pool_load_status';

Buffer Pool与压缩页

如果 information_schema.INNODB_BUFFER_PAGE_LRU 的 compressed = 'yes' ，表示这个页是压缩页。

压缩页存在于 unzip_LRU 中，可以通过如下面查看压缩页链的长度：

mysql> show engine innodb status G
-----------------省略其他输出-----------------
LRU len: 1233, unzip_LRU len: 320
-----------------省略其他输出-----------------

假设 key_block_size=8K ， Buffer Pool 的页大小是 16K，申请一个16K的空白页，给8K的压缩页使用，这样就多出一个8K的空间 ，该空间会移到8K的 Free List 中去；如果有一个4K的压缩页，就把8K的Free list中的空白页给他用，然后多余的4K的空间移到4K的Free List 中去,通过上述方式，不同大小的页可以在同一个 Buffer Pool 中使用。

被压缩的页需要在Buffer Pool中解压（解压的页是为了更快的查询），原来的压缩页保留在Buffer Pool中（保留压缩页是为了更快的刷回磁盘）。压缩页保留的原因是为了在更新数据的时候，将 redo 添加到压缩页的空闲部分，如果要刷回磁盘，可以直接将该压缩页刷回去。如果该页被写满，则做一次 reorganize 操作（在此之前也要做解压），真的写满了才做分裂。缺点是压缩页占用了Buffer Pool的空间，对于热点数据来说，相当于内存小了，可能造成性能下降（热点空间变小）。所以在开启了压缩后，Buffer Pool的空间要相应增大，启用压缩的前提是，内存尽可能的大。如果启用压缩后节省的磁盘IO能够抵消掉 Buffer Pool "空间变小" 所带来的性能下降，那整体性能还是会上涨。

透明压缩则没有上述压缩页的问题，因为压缩是文件系统层的，对MySQL是透明的。

LSN (Log Sequence Number)

1.REDO日志的LSN
假设当前的REDO日志的LSN为 C ，此时对某个页做修改，则会产生 M 个字节的日志（需要写入M个字节的日志），那此时REDO日志的 LSN 则为 C+M 。依次类推，LSN是一个 单调递增的值（字节数）。

2.页中的LSN
页中也存在LSN，表示该页被修改的时候，多应的日志的LSN是多少，可以通过 information_schema.INNODB_BUFFER_PAGE_LRU 的 NEWEST_MODIFICATION 和 OLDEST_MODIFICATION 两个字段来观察该页最近一次（最新）被修改的LSN值和该页在Buffer Pool中第一次被修改的LSN值。Page中的LSN放在页头，主要用在恢复的时候。Flush list 通过 OLDEST 将页链起来，恢复的时候从 Checkpoint SCN 开始跑日志，向前推进，例如某条日志，如果日志 LSN 小于页的 NEWEST_MODIFICATION LSN，则不需要恢复该条日志，否则就恢复该日志……。

3.CheckPoint LSN
每个数据库中也有一个LSN，表示最后一个刷新到磁盘的页的 OLDEST_MODIFICATION LSN ，表明了该 LSN 之前的数据都刷回到磁盘了，且如果要做恢复操作，也只要从当前这个 CheckPoint LSN 开始恢复，恢复到最后一条重做日志。CheckPoint LSN 写在redo log的前2K空间中。

1).日志中的LSN = CheckPoint的LSN ，则表示所有页都已经刷回磁盘
2).日志中的LSN > CheckPoint的LSN ，则表示还有页没刷到磁盘；如果是宕机，则需要用日志恢复。
3).日志中的LSN < CheckPoint的LSN ，则报错

mysql> show engine innodb status\G
-----------------省略其他输出-----------------
Log sequence number    165913808    -- 当前REDO BUFFER中最新的LSN
Log flushed up to      164814979    -- REDO刷到磁盘的LSN
Pages flushed up to    141544038    -- 最后一个刷到磁盘上的页的最新的LSN（NEWEST_MODIFICATION）
Last checkpoint at     130503656    -- 最后一个刷到磁盘上的页的第一次被修改时的LSN（OLDEST_MODIFICATION）
-----------------省略其他输出-----------------

恢复的时候从 Last checkpoint 开始跑日志，而 Last checkpoint < Pages flushed up to，所以该页不需要被恢复。

CheckPoint 的分类

• Sharp CheckPoint

  • 将所有的脏页刷新回磁盘，通常在数据库关闭的时候
  • innodb_fast_shutdown={1|0}

• Fuzzy CheckPoint

  • 将部分脏页刷新回磁盘，系统影响较小，参数 innodb_io_capacity 定义了其刷新的能力 (the number of I/O operations per second (IOPS)，即一次最多刷新脏页的能力。该值默认值是 200，如果存储 IOPS 性能较好，需要调高该值（SSD 可以设置为 4000-8000， SAS 的 IOPS 在 1000 左右，可以设置为800多）。这个值也不能太高，太高会造成数据库Hang起。

刷新

• Master Thread Checkpoint

  • 从 FLUSH_LIST 中刷新

• FLUSH_LRU_LIST Checkpoint

  • 从 LRU_LIST 中刷新，5.5 以前需要保证在 LRU_LIST 尾部要有100个空闲页（可替换的页），即刷新一部分数据，保证有100个空闲页。从 5.5 开始可以通过 innodb_lru_scan_depth 对这个页的数量进行控制，它定义了每次进行 LRU_LIST 刷新的脏页的数量，作用范围是每个 Buffer Pool，如果总量超过 innodb_io_capacity 是不合理的，建议该值不能超过 innodb_io_capacity / innodb_buffer_pool_instances。

• Async/Sync Flush Checkpoint

  • 重做日志重用
• Dirty Page too much Checkpoint

Double Write介绍

数据库一次 IO 的大小是 8K 或者 16K，而操作系统一次 IO 的大小是 4K，数据库将一个页交给操作系统写入，当操作系统只写了部分数据时，发生了 crash，此时，这个数据库页就变成了一个不完整、不干净的页，它不可以通过 redo log 进行恢复，redo恢复的前提是该页必须是完整、干净的。Double Write 的目的是为了保证数据写入的可靠性， 避免 Partial Write 的情况。

在共享表空间存在一个叫 double write 的段对象 ，这个段由2个区（1M*2，2M固定大小）组成，页在刷新时，先将脏页 copy 到 Double Write Buffer 中（固定2M），然后将 Double Write Buffer 中的对象先写入到共享表空间（ibdata1）中的 Double Write 段对象（1次 IO），再根据（space，page_no）写入到原来的 ibd 文件中。

如果是在写到共享表空间中的 Double Write 段对象时，发生宕机，此刻原来的 ibd file 仍然是完整、干净的，下次启动后是可以用 redo 文件进行恢复，如果是写到 ibd 文件时，发生了宕机，此刻在共享表空间中存在副本 ，可以直接覆盖到 ibd 文件中去（对应的页），然后再进行 redo 进行恢复。简单地说，这两个地方，总有一份数据是正确的。

Double Write的开销：假设每个页大小为16K，则2M的Double Write中存放了128个页，在使用了Double Write之后，IO从原来的128次IO变成了 128 + 1 次IO（而不是128 + 128 次IO），因为 Double Write 的2M数据是顺序刷入磁盘的，是一次IO，该次IO的大小为2M。开启Double Write的性能降低5% ~ 25%（IO Bound场景下降的最厉害）。slave 服务器同样需要开启。

Double Write可以关闭的前提：磁盘设备如Funsion-IO、宝存等支持原子写或者文件系统如 ZFS 、btrfs 支持原子写（使用 copy on wirte 机制，不进行原地更新，而是开辟新的位置，写成功后，将原来的页释放，本质上的思路还是保留一个副本）。

innodb_doublewrite = 0 可以关掉 Double Write。

Insert/Change Buffer

假如一张表，A 列是自增主键列，B 列是有一个非唯一索引，当插入记录时，对于聚簇索引，每次插入都要立即插入对应的聚集索引页中。而对于 B 列上的二级索引（secondary index），在没有 Insert/Change Buffer 时，每次插入一条记录，就要读取一次二级索引的页，然后将记录插入到页中；在有 Insert/Change Buffer 时，当插入一条记录时，先判断对应要插入的二级索引（secondary index）页是否在 Buffer Pool 中，如果在则直接插入，反之，先将其 Cache 起来，放到 Insert/Change Buffer 中，等到该二级索引（secondary index）页被读到时，将 Insert/Change Buffer 中该页对应的记录合并 (Merge，此时性能会有所下降）进去，从而减少I/O操作。所以，Insert/Change Buffer 就是用来提升二级索引(non-unique secondary index)插入的性能。其哲学思想就是使用空间换时间，采用批量插入的方式。

Insert/Change Buffer 是一个持久化的对象，在 ibdata1 中，同时也会写 redo 日志。

MySQL5.5 版本以前叫做 insert buffer，从 MySQL 5.5 以后，改名为 Change Buffer，表示不仅仅适用于 insert。

Insert/Change Buffer 由两个参数来控制，一个是开关，一个是大小：

innodb_change_buffering = all
建议开启 Insert/Change Buffer（默认开启），开启后有30%的性能提升，SSD场景下也建议开启。

• all
• none （禁用）
• inserts
• deletes
• changes =（insert & delete-marking）
• purge

innodb_change_buffer_max_size = 25 （25%）
MySQL5.5版本之前，最大可以设置为 Buffer Pool 的 1/2 ，现在最大只能设置为 1/4

Adaptive Hash Index（自适应Hash索引）

• 搜索的时间复杂度

  • B+树O(T)，只能定位到该记录所在的页；
  • 哈希表O(1)，可以直接定位到记录；
• 可以自己判断是否是活跃的页，如果是活跃的页，可以自动做 Hash，管理员无需人工干预；

• MySQL5.6 版本后，官方不推荐使用自适应 Hash 索引

  • CPU 使用率变高，但是性能没有提升；
• MySQL5.7 中增加了 innodb_adaptive_hash_index_parts，增加分片，以减少竞争；
• 只对等值的操作有意义；

Flush Neighbor Page (FNP)

• 刷新脏页所在区（extent）的所有脏页，合并IO，随机转顺序的优化；

• 对传统机械磁盘有意义，SSD建议关闭次功能

  • innodb_flush_neighbors={0|1|2} （>=MySQL 5.6）
  • 0：表示关闭该功能
  • 1：表示刷新一个区内的脏页
  • 2：表示刷新几个连续的脏页