《MySQL系列-InnoDB引擎02》InnoDB存储引擎介绍

发布时间：2024年01月02日

文章目录

第二章 InnoDB存储引擎

??InnoDB是事务安全的MySQL存储引擎，设计上采用了类似于Oracle数据库的架构。通常来说，InnoDB存储引擎是OLTP应用中核心表的首选存储引擎。

1 InnoDB存储引擎概述

??InnoDB存储引擎是第一个完整支持ACID事务的MySQL存储引擎，其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效的利用以及使用内存和CPU。

2 InnoDB存储引擎的版本

??InnoDB存储引擎被包含所有MySQL数据库的二进制发现版本中。早期InnoDB随着MySQL数据库更新而更新，从MySQL 5.1开始，MySQL数据库允许存储引擎以动态方式加载引擎，这样存储引擎的更新可以不受MySQL数据库版本的限制。所以MySQL 5.1版本有两个InnoDB版本，一个是静态编译的InnoDB版本，一个是动态加载的InnoDB版本(可以将其视为InnoDB 1.0.x版本)，后续InnoDB版本一直在升级并添加新的功能。

# 查看当前使用MySQL的版本
mysql> show variables like 'innodb_version'\G;
*************************** 1. row ***************************
Variable_name: innodb_version
        Value: 5.7.38
1 row in set (0.01 sec)

3 InnoDB体系架构

??如图所示，InnoDB存储引擎有多个内存块，可以人为这些内存块组成一个大的内存池，负责如下工作:

  • 维护所有进程/线程需要访问的多个内部数据结构。
  • 缓存磁盘上的数据，方便快速的读取，同时在对磁盘文件的数据修改之前在这里缓存。
  • 重做日志(redo log)缓冲。
  • …

在这里插入图片描述

??后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行的状态。

3.1 后台线程

??InnoDB存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。Master Thread、IO Thread、Purge Thread 、Page Cleaner Thread等。

1.Master Thread

??核心的后台线程，负责将缓冲池中的数据异步刷新到磁盘，保证数据一致性，包括脏页的属性、合并插入缓冲、UNDO页的回收

2.IO Thread

??在InnoDB存储引擎中大量使用了AIO(Async IO)来处理写IO请求，极大提高数据库性能。而IO Thread住哟处理这些IO请求的回调处理。

??InnoDB 1.0之前有4个IO Thread，分别是write,read,insert buffer,log IO thread。Linux平台下，IO Thread的数量不能进行调整，但是在Windows平台下可以通过innodb_file_io_threas参数来增加IO Thread。

??InnoDB 1.0.x版本开始，read thread和write thread分别增大到4个，并且不再使用innodb_file_io_threads参数，而是分别使用innodb_read_io_threads和innodb_write_io_threads参数进行设置。

# 查看read thread和write thread的数量
mysql> show variables like 'innodb_%io_threads'\G;
*************************** 1. row ***************************
Variable_name: innodb_read_io_threads
        Value: 4
*************************** 2. row ***************************
Variable_name: innodb_write_io_threads
        Value: 4
2 rows in set (0.00 sec)

??使用show engine innodb status\G可以查看InnoDB中的IO Thread:

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
......
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
.......

??从查询结果可以看出，thread 0是insert buffer thread，thread 1是log thread，thread2-5是read thread，thread 6-9是write thread。

3.Purge Thread

??事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。在InnoDB 1.1版本之前，purge操作仅在InnoDB存储引擎的Master Thread中完成。而从InnoDB 1.1版本开始，purge操作可以独立到单独的线程中进行，以减轻Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能。用户可以在MySQL数据库配置文件中添加以下配置来启用独立的Purge Thread:

# my.cnf文件中
[mysqld]
innodb_purge_threads=1

??InnoDB 1.2版本之后，为了加快undo页的回收，进一步利用磁盘的随机读写性能，InnoDB开始支持多个Purge Thread：

mysql> show variables like 'innodb_purge_threads'\G;
*************************** 1. row ***************************
Variable_name: innodb_purge_threads
        Value: 4
1 row in set (0.05 sec)

3.2 内存

1.缓存池

??InnoDB存储引擎是基于磁盘存储的，并将其中记录按照页的方式进行管理。因此可以将其视为基于磁盘的数据库。

??在数据库系统中，由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库整体性能。

??缓冲池简单点说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。

??数据库中读取页的操作流程是：首先将从磁盘读到的页存放在缓冲池中，这个过程称为将页"FIX"在缓冲池中。下一次再读相同页的时候，首先判断该页是否存在缓冲池中。如在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

??数据库中修改操作流程是：首先修改在缓冲池中的页，然后以一定频率刷新到磁盘上。需要注意的是，页从缓冲池刷新回到磁盘的操作并不是每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回到磁盘。同样，也是为了提高数据库的整体性能。

??对于InnocentDB存储引擎而言，缓冲池的配置通过参数innodb_buffer_pool_size来设置。

mysql> show variables like 'innodb_buffer_pool_size'\G;
*************************** 1. row ***************************
Variable_name: innodb_buffer_pool_size
        Value: 134217728
1 row in set (0.00 sec)

??具体来看，缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓存(insert buffer)、自适应哈希索引(adaptive hash index)、InnoDB存储的锁信息(lock info)、数据字典信息(data dictionary)等。不能简单的认为，缓冲池只是缓存索引页和数据页，他们只是占缓冲池很大一部分而已。

在这里插入图片描述

??从InnoDB 1.0.x版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。可通过innodb_buffer_pool_instances进行配置，默认为1。

mysql> show variables like 'innodb_buffer_pool_instances'\G;
*************************** 1. row ***************************
Variable_name: innodb_buffer_pool_instances
        Value: 1
1 row in set (0.00 sec)

??在配置文件中将innodb_buffer_pool_instances设置为大于1的值就可以得到多个缓冲池实例。需要注意的是，当配置多个缓冲池实例时，仅当innodb_buffer_pool_size大小设置为1GB或更大的时候，此选项才生效。指定的总大小将分配给所有缓冲池。为了获得最佳效率，指定组合innodb_buffer_pool_instances和innodb_buffer_pool_size,使得每个缓冲池实例至少是1GB。再通过命令show engine innodb status即可观察。

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2022-11-16 14:51:11 0x7f7f6005a700 INNODB MONITOR OUTPUT
=====================================
......
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 117329
Buffer pool size   8191
Free buffers       7622
Database pages     566
Old database pages 228
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 532, created 34, written 36
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 407 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 566, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
......

??从MySQL 5.6版本开始，还可以通过information_schema架构下的表Innodb_buffer_pool_stats来观察缓存状态

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select pool_id,pool_size,free_buffers,database_pages
    -> from innodb_buffer_pool_stats\G;
*************************** 1. row ***************************
       pool_id: 0
     pool_size: 8191
  free_buffers: 7652
database_pages: 536
1 row in set (0.00 sec)

2.LRU List、Free List、Flush List

??从上一节我们了解到缓冲池是一个很大的内存区域，其中存放各自类型的页。那么InnoDB存储引擎是怎么对这么大的内存区域进行管理的呢？

??通常来说，数据库中的缓冲池是通过LRU(Laster Recent Used，最近最少使用)算法来进行管理的。即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。

??在InnoDB存储引擎中，缓冲池中页的大小默认为16KB，同样使用LRU算法对缓冲池进行管理。稍有不同的是InnoDB存储引擎对传统LRU算法做了一些优化。在InnoDB的存储引擎中，LRU列表还加入了midpoint位置。新读取到的页，虽然是最新访问的页，但并不能直接放入LRU列表的首部，而是放入到LRU列表的midpoint位置。这个位置在InnoDB存储引擎下称为midpoint insertion strategy。在默认配置下，该位置在LRU列表长度的5/8处。midpoint位置可由参数innodb_old_blocks_pct控制，如：

mysql> show variables like 'innodb_old_blocks_pct'\G;
*************************** 1. row ***************************
Variable_name: innodb_old_blocks_pct
        Value: 37
1 row in set (0.01 sec)

??如上查询所示：参数innodb_old_blocks_pct默认值为37，表示新读取的页插入到LRU列表尾端37%的位置。在InnoDB存储引擎中，把midpoint之后的列表称为old列表，之前的列表称为new列表。可以简单的理解为new列表中的页都是最为活跃的热点数据。

??那么为什么要采用midpoint呢？直接将读取的页放到LRU列表的首部不就行了？这是因为若直接将读取到的页放入到LRU的首部，那么某些SQL操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。常见的这类操作比如索引或数据的扫描操作，这类操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说又仅在这次查询操作中需要，并不是活跃的热点数据。如果页被放入LRU列表的首部，那么非常可能将实际需要的热点数据页从LRU列表中移除，那么下次再读取该页的时候，InnoDB存储引擎需要再次访问磁盘。

??为了解决这个问题，InnoDB存储引擎引入另一个参数来进一步管理LRU列表，这个参数是innodb_old_blocks_time，用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。因此当需要执行上述所说SQL操作时，可以通过下面的方法尽可能使LRU列表中热点数据不被刷出。

??set global innodb_old_blocks_pct=20

??LRU列表用来管理以及读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。否则，根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。当页从LRU列表的old部分加入到new部分时，称此时发生的操作为page mode young,而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page made young。可以通命令show engine innodb status来观察LRU列表及Free列表的使用情况和运行状态。

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2022-11-18 14:27:20 0x7f57380b0700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 54 seconds
......
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 141965
Buffer pool size   8191
Free buffers       7652
Database pages     536
Old database pages 217
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 501, created 35, written 39
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 536, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
......

??通过命令show engine innodb status可以看到: 当前Buffer pool size 共有 8191个页，即8191 * 16K，总共有127M的的缓冲池。Free buffers表示当前Free 列表中页的数量，Database pages 表示LRU列表中页的数量。可能的情况是Free buffers与Database pages的数量之和不等于Buffer pool size。因为缓冲池中的页还可能分配给自适应哈希索引、Lock信息、Insert Buffer等页，而这部分页不需要LRU算法进行维护，因此不存在于LRU列表中。

??page made young 显示了LRU列表中页移动到前端的次数，因为该服务器在运行阶段没有改变innodb_old_blocks_time的值，因此not young为0。youngs/s、non-youngs/s表示每秒这两类操作的次数。这里还有一个重要的观察变量-buffer pool hit rate，表示缓冲池的命中率，这个例子中为100%，说明缓冲池运行状态非常良好。通过该值不应该小于95%。如发生Buffer pool hit rate的值小于95%的情况，用户需要观察是否由于全表扫描引起的LRU列表被污染的问题。

??需要注意的是，执行show engine innodb status显示的不是当前的装填，而是过去某个时间范围内InnoDB存储引擎的状态。从上面的例子可以发现，Per second averages calculated from the last 54 seconds代表的信息是过去54秒的数据库状态。

??从InnoDB 1.2版本开始，还可以通过innodb_buffer_pool_stats来观察缓冲池的运行状态，如：

mysql> select pool_id,hit_rate,
    -> pages_made_young,pages_not_made_young
    -> from information_schema.innodb_buffer_pool_stats\G;
*************************** 1. row ***************************
             pool_id: 0
            hit_rate: 0
    pages_made_young: 0
pages_not_made_young: 0
1 row in set (0.00 sec)

??此外，还可以通过表innodb_buffer_page_lru来观察LRU列表每个页的具体信息，例如通过下面的语句可以看到缓冲池LRU列表中space为1的表的页类型：

mysql> select table_name,space,page_number,page_type
    -> from information_schema.innodb_buffer_page_lru
    -> where space = 6;
+-------------------------+-------+-------------+-------------+
| table_name              | space | page_number | page_type   |
+-------------------------+-------+-------------+-------------+
| `mysql`.`help_relation` |     6 |           3 | INDEX       |
| NULL                    |     6 |           2 | INODE       |
| `mysql`.`help_relation` |     6 |           4 | INDEX       |
| NULL                    |     6 |           1 | IBUF_BITMAP |
| `mysql`.`help_relation` |     6 |           5 | INDEX       |
| `mysql`.`help_relation` |     6 |           8 | INDEX       |
+-------------------------+-------+-------------+-------------+
6 rows in set (0.00 sec)

??InnoDB存储引擎从1.0.x版本开始支持压缩页的功能，即将原本的16KB的页压缩为1KB、2KB、4KB和8KB。而由于页的大小发生了变化，LRU列表也有了些许的改变。对于非16KB的页，是通过unzip_LRU列表进行管理的。

3.重做日志缓冲

??InnoDB存储引擎的内存区域除了有缓冲池外，还有重做日志缓冲(redo log buffer)。InnoDB存储引擎首先将重做日志信息先放入到这个缓冲区，然后按照一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置很大，因为一般情况下每一秒会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小内即可。该值可由配置参数innodb_log_buffer_size控制，默认为8MB，可以做调整：

mysql> show variables like 'innodb_log_buffer_size'\G;
*************************** 1. row ***************************
Variable_name: innodb_log_buffer_size
        Value: 16777216
1 row in set (0.00 sec)

??在通常情况下，8MB的重做日志缓冲足以满足绝大部分的应用，因为重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。

  • Master Thread每一秒都将重做日志刷新到重做日志文件
  • 每个事务提交会将重做日志缓冲刷新到重做日志文件
  • 当重做日志缓冲池剩余空间小于1/2时，重做日志刷新到重做日志文件

4.额外的内存池

??额外的内存池容易被DBA忽略，他们认为该值并不重要，恰恰相反，该值同样十分重要。在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆的方式进行的。在堆一些数据结构本身进行分配时，需要从额外的内存池进行申请，当该区域的内存不够时，会从缓冲池中进行申请。假如，分配了缓冲池(innodb_buffer_pool)，但是每个缓冲池中的帧缓冲(frame buffer)还有对应的缓冲控制对象(buffer control block),这些对象记录了一些诸如LRU、锁、等待等信息，而这个对象的内存需要从额外内存池申请。因此，在申请了很大的InnoDB缓冲池时，也应考虑相应的增加这个值。

4 Checkpoint技术

??缓冲池的设计目的是为了协调CPU速度与磁盘速度的鸿沟。因此页的操作首先都是在缓冲池中完成的。如果一条DML语句，比如Update或Delete改变了页中的记录，那么此时页是脏的，即缓冲池中的页要比磁盘的新，那么数据库就需要将新版本的页从缓冲池刷新到磁盘。

??倘若每次一个页发生变化，都要将新页刷新到磁盘，那么这个开销是非常大的。如热点数据集在某几个页中，那么数据库的性能将变得非常差。同时，如果在从缓冲池往磁盘刷新页的时候发生的宕机，那么数据就不能恢复了。为了避免发生数据丢失的问题，当事务数据库系统普遍都采用Write Ahead log策略， 即当事务提交时，先写重做日志，再修改页。当因为宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中持久性(Durability)的要求。

??如下场景需要思考：如果重做日志可以无限的增大，同时缓冲池也足够大，能够缓冲所有数据库的数据，那么是不需要将缓冲池中页的新版本刷新到磁盘。因为当发生宕机时，完全可以通过重做日志来恢复整个数据库系统的数据到宕机发生的时刻。但是这样做是有前提的：

  • 1.缓冲池可以缓存数据库中所有的数据
  • 2.重做日志可以无限增大

??对于第一个前提条件，有经验的用户都知道，当数据库刚开始创建时，表中没有任何数据。缓冲池的确可以缓存所有的数据库文件。然后随着市场的推广，用户的增加，产品越来越受到关注，使用量也越来越大。这时负责后台存储的数据库容量必定会不断增大。当前3TB的MySQL数据库已不少见，但是3TB的内存却非常少见。

??再看第二个前提条件，重做日志可以无限增大。也许是可以的，但是这对成本的要求太高了，同时不便于运维。DBA或SA不能知道什么时候重做日志是否已经接近于磁盘可使用空间的阈值，并且要让存储设备支持可动态扩展也是需要一定的技巧和设备支持的。

??当前即使以上两个条件都满足，那么还需要考虑的是，当数据库运行几个月甚至几年，如果发生宕机，那么通过重做日志恢复的话，需要多久呢？

??因此CheckPoint(检查点)技术的目的是解决以下几个问题：

  • 缩短数据库的恢复时间
  • 缓冲池不够用时，将脏页刷新到磁盘
  • 重做日志不可用时，刷新脏页

??当数据库发生宕机时，数据库不需要重做所有的日志，因为checkpoint之前的页都已经刷新回磁盘。故数据库只需对checkpoint后的的重做日志进行恢复。这样就大大缩短了恢复时间。

??此外，当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，如此页为脏页，那么需要强制执行checkpoint，将脏页也就是页的最新版本刷回磁盘。

??重做日志出现不可用的情况是因为当前事务数据库系统对重做日志的设计是循环使用的，并不是让其无线增大，这从成本及管理上都是比较困难的。重做日志可以被重用的部分是指这些重做日志已经不再需要了，即当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。若此时重做日志还需要使用，那么必须强制产生checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

??对于InnoDB存储引擎而言，其是通过LSN(Log Sequenc Number)来标记版本的。而LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志也有LSN，checkpoint也有LSN。可以通过命令show engine innodb status来观察:

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2022-11-21 10:02:07 0x7f573806e700 INNODB MONITOR OUTPUT
=====================================
......
---
LOG
---
Log sequence number 105133652
Log flushed up to   105133652
Pages flushed up to 105133652
Last checkpoint at  105133643
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
......

??在InnoDB存储引擎中，checkpoint发生的时间、条件及脏页的选择等都非常复杂。而checkpoint所做的事情无外乎是将缓冲池中的脏页刷回到磁盘。不同之处在于每个刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发checkpoint。在InnoDB存储引擎内部，有两种checkpoint，分别位：

  • Sharp Checkpoint
  • Fuzzy Checkpoint

??Sharp CheckPoint发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作，即参数innodb_fast_shutdown=1。

mysql> show variables like '%innodb_fast_shutdown%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

??但是如果数据库在运行的时候也使用Sharp Checkpoint，那么数据库的可能性就会受到很大的影响。故在InnoDB存储引擎内部使用Fuzzy Checkpoint进行页的刷新，即只刷新一部分脏页，而部署刷新所有的脏页回磁盘。

??在InnoDB存储引擎中可能发生如下几种情况的Fuzzy Checkpoint:

  • Master Thread Checkpoint
  • FLUSH_LRU_LIST Checkpoint
  • Aysnc/Sync Flush Checkpoint
  • Dirty Page too much Checkpoint

Master Thread Checkpoint

??Master Thread中发生的Checkpoint，差不多以每秒或没十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，即此时InnoDB存储引擎可以进行其他的操作，用户查询线程不会阻塞。

FLUSH_LRU_LIST Checkpoint

??FLUSH_LRU_LIST Checkpoint是因为InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用。在InnoDB1.1.x版本之前，需要检查LRU列表中是否有足够的可用空间操作发生在用户查询线程中，显然这会阻塞用户的查询操作。倘若没有100个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页，那么需要进行Checkpoint,而这些页来自LRU列表的，因此称为FLUSH_LRU_LIST Checkpoint。

??从MySQL 5.6版本，也就是InnoDB1.2.x版本开始，这个检查被放在一个单独的Page Cleaner线程中进行，并且用户可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认位1024，如:

mysql> show variables like 'innodb_lru_scan_depth'\G;
*************************** 1. row ***************************
Variable_name: innodb_lru_scan_depth
        Value: 1024
1 row in set (0.00 sec)

Aysnc/Sync Flush Checkpoint

??Aysnc/Sync Flush Checkpoint指的是重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时是脏页是从脏页列表选取的。若将已经写入到重做日志的LSN几位redo_lsn，将已经刷新回磁盘最新页的LSN记为checkpoint_lsn，则可定义：

??checkpoint_age = redo_lsn - checkpoint_lsn

再定义以下的变量：

async_water_mark = 75% * total_redo_log_file_size
sync_water_mark = 90% * total_redo_log_file_size

??若每个重做日志文件的大小为1GB，并且定义了两个重做日志文件，则重做日志文件的总大小为2GB。那么asynx_water_mark=1.5GB，sync_water_mark=1.8GB。则:

  • 当checkpoint_age < aync_water_mark时，不需要刷新任何脏页到磁盘；
  • 当async_water_mark < checkpoint_age < sync_water_mark时触发Async Flush，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age<async_water_mark;
  • checkpoint_age > sync_water_mark 这种情况一般很少发生，除非设置的重做日志文件太小，并且在进行类型LOAD DATA的BUCK INSERT操作。此时触发Sync Flush操作，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age < sync_water_mark。

??可见，Async/Sync Flush Checkpoint是为了保证重做日志的循环使用的可能性在InnoDB 1.2.x版本之前，Async Flush Checkpoint会阻塞发现问题的用户查询线程，而Sync Flush Checkpoint会阻塞所有用户查询线程，并且等待脏页刷新完成。从InnoDB 1.2.x版本开始-也就是MySQL 5.6版本，这部分的刷新操作同时放入到了单独Page Cleaner Thread中，故不会阻塞用户查询线程。

Dirty Page too much Checkpoint

??Dirty Page too much Checkpoint指的是脏页的数量太多，导致InnoDB存储引擎强制进行CheckPoint。其目的总的来说还是为了保证缓冲池中有足够可用的页。其可由innodb_max_dirty_pages_pct控制：

mysql> show variables like 'innodb_max_dirty_pages_pct'\G;
*************************** 1. row ***************************
Variable_name: innodb_max_dirty_pages_pct
        Value: 75.000000
1 row in set (0.01 sec)

??innodb_max_dirty_pages_pct值为75表示，当缓冲池中脏页的数量占据75%时，强制进行CheckPoint，刷新一部分的脏页到磁盘。在InnoDB 1.0.x版本之前，该参数默认值为90，之后的版本都为75。

5 Master Thread 工作方式

??在后台线程中提到，Master Thread是核心的后台线程。InnoDB存储引擎的主要工作都是在一个单独线程中完成的。

5.1 InnoDB 1.0.x版本之前的Master Thread

??Master Thread具有最高的线程优先级别。内部由多个循环组成：主循环(loop)、后台循环(backgroup loop)、刷新循环(flush loop)、暂停循环(suspend loop)。Master Thread会根据数据库运行的状态在这四个循环中进行切换。

??Loop被称为主循环，因为大多数的操作都在主循环里，其中有两大部分的操作-即每秒钟的操作和每10秒的操作。伪代码如下：

void master_thread(){
    loop;
    for(int i = 0; i < 10 ; i++){
        do thing once per second
        sleep 1 second if necessary
    }
    do things once per ten seconds
    goto loop;
}

??如伪代码所示，loop循环是通过thread sleep实现的，这意味着其中的每秒一次或每10秒一次是不精准的。当负载很大的时候可能会有延迟。然后，InnoDB源代码中通过其他方法尽量保证整个频率。

每秒一次的操作包括：

  • 日志缓存刷新到磁盘，即使事务还没提交；（总是）

    ??即使某个事物还没有提交，InnoDB存储引擎仍然每秒将重做日志缓冲中的内容刷新到重做日志文件。这一点是是必须要知道的，因为这可以很好的解释为什么再大的事物提交时间也是很短的。

  • 合并插入缓冲；（可能）

    ??合并插入缓冲并不是每秒都会发生的。InnoDB存储引擎会判断当前一秒发生的IO次数是否小于5次，如果小于5次，InnoDB认为当前的IO压力很小，可以执行合并插入缓冲的操作。

  • 至多刷新100个InnoDB的缓冲池中脏页到磁盘；（可能）

    ??刷新100搁脏页也不是每秒都会发生的。InnoDB存储引擎通过判断当前缓冲池中脏页的比例buf_get_modified_ratio_pct是否超过了配置文件中innodb_max_dirty_pages_pct这个参数(默认90，代表90%)，如果超过了这个阈值，InnoDB存储引擎认为需要做磁盘同步的操作，将100个脏页写入磁盘中。

  • 如果当前没有用户获得，则切换到backgroup loop；（可能）

void master_thread(){
    goto loop;
    loop;
    for(int i=0;i<10;i++){
        thread_sleep(1)
        do log buffer flush to disk;
        if(last_one_second_ios < 5)
            do merge at most 5 insert buffer
        if(buf_get_modified_ratio_pct > inodb_max_dirty_pages_pct)
            do buffer pool flush 100 dirty page
        if(no user activity)
            goto backgroup loop
    }
    do things once per ten seconds
    backgroup loop:
    	do something
        goto loop;
}

每十秒一次的操作包括：

  • 刷新100个脏页到磁盘(可能情况)
  • 合并至多5个插入缓冲(总是)
  • 将日志缓冲刷新到磁盘(总是)
  • 删除无用的Undo页(总是)
  • 刷新100个或者10个脏页到磁盘(总是)

??InnoDB存储引擎先判断过去10秒之内的磁盘的IO操作是否小于200次，如果是，InnoDB存储引擎认为当前有足够的磁盘IO操作能力，因此将100个脏页刷新到磁盘。

??接着InnoDB存储引擎会合并插入缓冲。不同于每秒一次操作时可能发生的合并插入缓冲操作，这次的合并插入缓冲操作总会在这个阶段进行。之后，InnoDB存储引擎会再进行一次将日志缓冲刷新到磁盘的操作。这和每秒一次时发生的操作是一样的。

??接着InnoDB存储引擎会进行下一步full purge操作，删除无用的undo页。对表进行update、delete这类操作时，原先的行被标记为删除，但是因为一致性读的关系，需要保留这些行版本的信息，但是在full purge过程中，InnoDB存储引擎会判断当前事务系统中已被删除的行是否可以删除，比如有时候可能还有查询操作需要读取之前版本的undo信息，如果可以删除，InnoDB会立即将其删除。从源代码中可以发现，InnoDB存储引擎在执行full purge操作时，每次最多尝试回收20个undo页。

??然后，InnoDB存储引擎会判断缓冲池中脏页的比例(buf_get_modified_ratio_pct)，如果有超过70%的脏页，则刷新100个脏页到磁盘，如果脏页的比例小于70%，则只需要刷新10%的脏页到磁盘。

??主循环伪代码如下：

void master_thread(){
    goto loop;
    loop;
    for(int i=0;i<10;i++){
        thread_sleep(1)
        do log buffer flush to disk;
        if(last_one_second_ios < 5)
            do merge at most 5 insert buffer
        if(buf_get_modified_ratio_pct > inodb_max_dirty_pages_pct)
            do buffer pool flush 100 dirty page
        if(no user activity)
            goto backgroup loop
    }
    if(last_ten_second_ios < 200)
        do buffer pool flush 100 dirty page
        do merge at most 5 insert buffer
        do log buffer flush to disk
        do pull purge
    if(buf_get_modified_ratio_pct > 70%)
        do buffer pool flush 100 dirty page
    else 
        buffer pool flush 10 dirty page
    goto loop
    backgroup loop:
    	do something
    goto loop;
}

??然后再看一下background loop，若当前没有用户活动(数据库空闲时)或者数据库关闭(shutdown)，就会切换到这个循环。backgroup loop会执行以下操作：

  • 删除无用的undo页（总是）
  • 合并20个插入缓冲（总是）
  • 跳回到主循环（总是）
  • 不断刷新100个页直到符合条件（可能，跳转到flush loop中完成）。

??若flush loop中也没有什么事情可以做了，InnoDB存储引擎会切换到suspend_loop，将Master Thread挂起，等待事件的发生。若用户启用了InnoDB引擎，却没有使用任何InnoDB存储引擎的表，那么Master Thread总是处于挂起状态。

??最后，Master Thread完整的伪代码如下：

void master_thread(){
    goto loop;
    loop;
    for(int i=0;i<10;i++){
        thread_sleep(1)
        do log buffer flush to disk;
        if(last_one_second_ios < 5)
            do merge at most 5 insert buffer
        if(buf_get_modified_ratio_pct > inodb_max_dirty_pages_pct)
            do buffer pool flush 100 dirty page
        if(no user activity)
            goto backgroup loop
    }
    if(last_ten_second_ios < 200)
        do buffer pool flush 100 dirty page
        do merge at most 5 insert buffer
        do log buffer flush to disk
        do pull purge
    if(buf_get_modified_ratio_pct > 70%)
        do buffer pool flush 100 dirty page
    else 
        buffer pool flush 10 dirty page
    goto loop
    backgroup loop:
    	do full purge
        do mere 20 insert buffer
    if not idle:
    	do merge 20 insert buffer
        goto loop:
    else:
    	goto flush loop
    flush loop:
    	do buffer pool flush 100 dirty page
    if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct)
        goto flush loop
    goto suspend loop
        suspend loop;
    	suspend_thread();
    	waiting event;
    goto loop;
}

5.2 InnoDB 1.2.x版本之前的Master Thread

??在InnoDB 1.0.x版本之前，InnoDB引擎对IO是有限制的，但是固态磁盘(SSD)的出现，这种限制很大程度的也限制了存储引擎的IO性能，尤其是写入性能。

??从InnoDB 1.0.x的伪代码中可以看到，InnoDB存储引擎最大只会刷新100个脏页到磁盘、合并20个插入缓冲。如果在写入密集的应用程序中，每秒可能会产生大于100个脏页、如果是产生20个插入缓冲的情况下，Master Thread似乎会"忙不过来"，或者说它是做的很慢。即使磁盘能1秒内处理多于100个页的写入和20个插入缓冲的合并，但是由于hard coding，Master Thread也只会下选择刷新100个脏页和合并20个插入缓冲。同时，当发送宕机需要恢复时，由于很大数据还没刷新回磁盘，会导致恢复的时间可能会很久，尤其对于inset buffer来说。

??这个问题最初由Google的工程师Mark Callaghan提出，之后InnoDB官方对其进行了修正并发布补丁(patch)。InnoDB存储引擎的开发团队参考了Google的patch，提出了类似的方法修正该问题。因此InnoDB Plugin(从InnoDB 1.0.x版本开始)提供了参数innodb_io_capacity，用来表示磁盘IO的吞吐量，默认值为200。对于刷新到磁盘页的数量，会按照innodb_io_capacity的百分比来空值，规则如下:

  • 在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity值的5%
  • 在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity

??若用户使用使用了SSD类的磁盘，或将几块磁盘做了RAID，当存储设备拥有更高的IO速度时，完全可以将innodb_io_capacity的值调的再高点，直到符合磁盘IO的吞吐量为止。

??另一个问题是，参数innodb_max_dirty_pages_pct默认值的问题，在InnoDB 1.0.x版本之前，该值默认为90，意味着脏页占缓冲池的90%。但是该值太大了，因为InnoDB存储引擎在每秒刷新缓冲池和flush loop时会判断这个值，如果该值大于innodb_max_dirty_pages_pct，才刷新100个脏页，如果我们有很大的内存，或者数据库服务器压力很大，这时刷新脏页脏页的速度反而会降低。同样，在数据库的恢复阶段可能需要更多的时间。

??如果将这个调的过低，在性能有所提高的同时，也会对磁盘造成一定的压力。从InnoDB 1.0.x开始，innodb_max_dirty_pct默认值变为了75，和Google测试的80比较接近。这样既可以加快刷新脏页的频率，有能保证磁盘IO的负载。

??InnoDB 1.0.x版本带来的另一个参数是innodb_adaptive_flushing(自适应刷新)，该值影响每秒刷新脏页的数量。原来的刷新规则是：脏页在缓冲池所占比例大于innodb_max_dirty_pages_pct时，刷新100个脏页。由于innodb_adaptive_flushing参数的引入，InnoDB存储引擎会通过一个名为buf_flush_get_desired_flush_rate通过判断产生重做日志的速度来决定最合适的刷新脏页数量。因此，当脏页的比例小于innodb_max_dirty_pages_pct时，也会刷新一定量的脏页。

??还有一个改变是：之前每次进行full purge操作时，最多回收20个Undo页，从Innodb 1.0.x版本开始引入了参数inndb_purge_batch_size,该参数可以空值每次full purge回收的Undo页的数量。该参数的默认值为20，并可以动态的对其进行修改，具体如下：

mysql> show variables like 'innodb_purge_batch_size'\G;
*************************** 1. row ***************************
Variable_name: innodb_purge_batch_size
        Value: 300
1 row in set (0.00 sec)

mysql> set global innodb_purge_batch_size=200;
Query OK, 0 rows affected (0.00 sec)

??通过上述变化重可知，从InnoDB 1.0.x版本开始，Master Thread的伪代码有所改变，如下：

void master_thread(){
    goto loop;
    loop;
    for(int i=0;i<10;i++){
        thread_sleep(1)
        do log buffer flush to disk;
        if(last_one_second_ios < 5)
            do merge at most 5 insert buffer
        if(buf_get_modified_ratio_pct > inodb_max_dirty_pages_pct)
            do buffer pool flush 100 dirty page
        if(no user activity)
            goto backgroup loop
    }
    if(last_ten_second_ios < 200)
        do buffer pool flush 100 dirty page
        do merge at most 5 insert buffer
        do log buffer flush to disk
        do pull purge
    if(buf_get_modified_ratio_pct > 70%)
        do buffer pool flush 100 dirty page
    else 
        buffer pool flush 10 dirty page
    goto loop
    backgroup loop:
    	do full purge
    	`do merge 100% innodb_io_capacity insert buffer`
    if not idle:
    	do merge 20 insert buffer
        goto loop:
    else:
    	goto flush loop
    flush loop:
    	`do buffer pool flush 100% innodb_io_capacity dirty page`
    if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct)
        goto flush loop
    goto suspend loop
        suspend loop;
    	suspend_thread();
    	waiting event;
    goto loop;
}

??很多测试显示，InnoDB 1.0.x版本在性能方面取得了极大的提高，其实这和前面提到的Master Thread的改动是密不可分的，因为InnoD存储引擎的核心操作大部分都是集中在Master Thread后台线程中的。

5.3 InnoDB 1.2.x版本的Master Thread

??在InnoDB 1.2.x版本中再次对Master Thread进行了优化，由此可以看出Master Thread对性能起到的关键作用。在InnoDB 1.2.x版本中，Master的伪代码如下:

if InnoDB is idle
	srv_master_do_idle_tasks();
else
	srv_master_do_active_tasks();

??其中srv_master_do_idle_tasks()就是之前版本中每10秒的操作，srv_master_do_active_tasks()处理的是之前每秒中的操作。同时对于刷新脏页的操作，从Master Thread线程分离到一个单独的Page Cleaner Thread ，从而减轻Master Thread的工作，同时进一步提高了系统的并发性。

6 InnoDB 关键特性

??InnoDB存储引擎的关键特性包括：

  • Insert Buffer (插入缓冲)
  • Double Write (两次写)
  • Adaptive Hash Index (自适应哈希索引)
  • Async IO (异步IO)
  • Flush Neighbor Page (刷新领接页)

??这些特性为InnoDB存储引擎带来了更好的性能以及更高的可靠性。

6.1 插入缓冲

1. Insert Buffer

??Insert Buffer可能是InnoDB存储引擎关键特性中最令人激动与兴奋的一个功能。不过这个名字可能会让人认为插入缓冲是缓冲池中的一个组成部分。其实不然，InnoDB缓冲池中有Insert Buffer信息固然不错，但是Insert Buffer和数据页一样，也是物理页的一个组成部分。

??在InnoDB存储引擎中，主键是行唯一的标识符。通常应用程序中行记录的哈如顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引(Primary Key)一般是顺序的，不需要磁盘的随机读取。比如按下列SQL定义表：

create table zxy (
	a int auto_increment,
	b varchar(30),
	primary key(a)
);

??其中a列是自增长的，若对a列插入null值，则由于其具有auto_increment属性，其值会自动增长。同时页中的行记录按a的值进行顺序存放。在一般情况下，不需要随机读取另一个页中的记录。因此对于这类情况下的插入操作，速度是非常快的。

??注意：并不是所有的主键插入都是有顺序的。若主键类是UUID这样的类，那么插入和辅助索引一样，同样是随机的。即使主键是自增类型，但是插入的是指定的指，而不是NULL指，那么同样可能导致插入并非连续的情况。

??但是不可能每张表上只有一个聚集索引，更多情况下，一张表上有多个非聚集的辅助索引(secondary index)。比如，用户需要按照b这个字段进行查找，并且b这个字段不是唯一的，即表是按如下的SQL语句定义的：

create table zxy (
	a int auto_increment,
	b varchar(30),
	primary key(a),
	key(b)
);

??在这样的情况下产生了一个非聚集的且不是唯一的索引。在进行插入操作时，数据页的存放还是按主键a进行顺序存放的，但是对于非聚集索引叶子节点的插入不再是顺序的了，这时就需要离散的访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。当然这并不是b字段上索引的错误，而是因为B+树的特性决定了非聚集索引插入的离散型。

??需要注意的是，在某些情况下，辅助索引的插入依然是顺序的，或者说是比较顺序的，比如用户购买表中的事件字段。在通常情况下，用户购买时间是一个辅助索引，用来根据时间条件进行查询。但是在插入时却也是根据时间的递增而插入的，因此插入也是“较为”顺序的。

??InnoDB存储引擎开创性的设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer对象中，好似欺骗。数据库这一个非聚集索引已经插入到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge(合并)操作，这时通常能将多个插入合并到一个操作中(因为在一个索引页中)，这样就大大的提高了对于非聚集索引插入的性能。

??然而Insert Buffer的使用需要满足以下两个条件：

  • 索引是辅助索引 (secondary index)
  • 索引不是唯一的 (unique)

??当满足以上两个条件时，InnoDB存储引擎会使用Insert Buffer，这样就能提高插入操作的性能了。不过考虑到这样一种情况：应用程序进行大量的插入操作，这些都涉及了不唯一的非聚集索引，也就是使用Insert Buffer的情况。若此时MySQL数据库发生了宕机，这时势必有大量的Insert Buffer并没有合并到实际的非聚集索引中。因此这时恢复可能需要很长时间，在极端情况下，甚至需要几个销售。

??辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定会有离散读取的情况发生，从而导致Insert Buffer失去了意义。

??而Insert Buffer还存在的一个问题是：在写密集的情况下，插入缓冲会占用过多的缓冲池内存(innodb_buffer_pool)，默认最大可以占用到1/2的缓冲池内存。但是可以通过ibuf_pool_size_per_max_size对插入缓冲大小进行控制。当ibuf_pool_size_per_max_size改为3时，则最大只能占用1/3的缓冲池内存。

2. Change Buffer

??InnoDB从1.0.x版本开始引入了Change Buffer，可以将其视为Insert Buffer的升级。从这个版本开始，InnoDB存储引擎可以对DML操作-INSERT、DELETE、UPDATE都进行缓冲，他们分别是：Insert Buffer、Delete Buffer、Purge Buffer。

??当然和之前Insert Buffer一样，Change Buffer使用的对于依然是非唯一的辅助索引。

??对一条记录进行update操作分为两个步骤：

  • 将原记录标记为已删除
  • 真正将原记录删除

??因此Delete Buffer对应update操作的第一个过程，即将记录标记为删除。Purge Buffer对应update操作的第二个过程，即将记录真正的删除。同时，InnoDB存储引擎提供了参数innodb_change_buffering，用来开启各种buffer的选项。该参数可选的值为：inserts、deletes、purges、changes、all、none。inserts、deletes、purges就是前面讨论的三种情况。changes表示启用inserts和deletes，all表示启用所有，none表示都不启用。参数默认值为all。

??从InnoDB 1.2.x版本开始，可以通过innodb_change_buffer_max_size来控制Change Buffer最大使用内存的数量。

mysql> show variables like 'innodb_change_buffer_max_size'\G;
*************************** 1. row ***************************
Variable_name: innodb_change_buffer_max_size
        Value: 25
1 row in set (0.00 sec)

??innodb_change_buffer_max_size值默认为25，表示最多使用1/4的缓冲池内存空间。需要注意的是，该参数最大有效值是50，也就是最多使用1/2的缓冲池内存空间。

??通过show engine innodb status;可以看到merged operations和discard operation，并且下面显示Change Buffer中每个操作的次数。insert表示insert buffer；delete mark表示delete buffer；delete表示purge buffer；discard operations表示当change buffer发生了merge时，表已经被删除了，此时无需再将记录合并(merge)到辅助索引中。

mysql> show engine innodb status\G;
......
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
......

3. Insert Buffer的内部实现

??通过前面的介绍可以知道Insert Buffer的使用场景是非唯一辅助索引的插入操作。但是对于Insert Buffer具体是什么，以及内部怎么实现还处于模糊阶段。

??其实Insert Buffer的数据结构是一颗B+树。在MySQL 4.1之前的版本中每张表有一颗Insert Buffer B+树。而在现在的版本中，全局只有一颗Insert Buffer B+树，负责对所有表的辅助索引进行Insert Buffer。而这颗B+树存放在共享表空间中，默认也就是idbatal中。因此，视图沟通独立表空间idb文件恢复表中数据时，往往会导致CHECK TABLE失败。这时因为表的辅助索引中数据肯恶搞还在Insert Buffer中，也就是共享表空间中，所以通过idb文件进行恢复后，还需要进行REPLACE TABLE操作来重建表上所有的辅助索引。

??Insert Buffer是一颗B+树，因此其也由叶子节点和非叶子节点组成。非叶子节点存放的是查询的search key(键值)，其构造如下所示：

space | marker | offset

??search key 一共占用9个字节，其中space表示待插入记录所在表空间id，在InnoDB存储引擎中，每个表有一个唯一的space id，可以通过sapce id查询得知是哪张表。space占用4字节。marker占用1字节，它是用来兼容老版本的Insert Buffer。offset表示页所在的偏移量，占用4字节。

??当一个辅助索引要插入到页(space,offset)时，如果这个页不在缓冲池中，那么InnoDB存储引擎首先根据上述规则构造一个search key，接下来查询insert buffer这颗B+树，然后再将这条记录插入到Insert Buffer B+树的叶子节点中。

??对于插入到Insert Buffer B+树叶子节点的记录，并不是直接将待插入的记录插入，而是需要根据如下的规则进行构造：

在这里插入图片描述

??space、marker、page_no字段和之前非叶子节点中含义相同，一共占用9字节。第4个字段metadata占用4字节，其存储的内容如下：

??IBUF_REC_OFFSET_COUNT是保存两个字节的帧数，用来排序每个记录进入Insert Buffer的顺序。因为从InnoDB 1.0.x开始支持Change Buffer，所以这个值同样记录进入Insert Buffer的顺序。通过这个顺序回放(replay)才能得到记录的正确值。

名称字节
IBUF_REC_OFFSET_COUNT2
IBUF_REC_OFFSET_TYPE1
IBUF_REC_OFFSET_FLAGES1

??从叶子节点的第5列开始，就是实际插入记录的各个字段。因此相较于之前的插入记录，Insert Buffer B+树的叶子节点记录需要额外13字节的开销。

??因为启用Insert Buffer索引后，辅助索引页(space,page_no)中的记录可能被插入到Insert Buffer B+树中，所以为了保证每次Merge Insert Buffer页必须成功，还需要有一个特殊的页来标记每个辅助索引页(space,page_no)的可用空间。这个页的类型为Insert Buffer Bitmap。

??每个Insert Buffer Bitmap页用来追踪16384个辅助索引页，也就是256个区(Extent)。每个Insert Buffer Bitmap页都在16384个页的第二个页中。

??每个辅助索引页在Insert Buffer Bitmap页中占用4位(bit)，由如下三个部分组成：

名称大小（bit)说明
IBUF_BITMAP_FREE2表示该辅助索引的可用空间数量
0 表示无可用剩余空间
1 表示剩余空间大于1/32页(512字节)
2 表示剩余空间大于1/16页
3 表示剩余空间大于1/8页
IBUF_BITMAP_BUFFERED11 表示该辅助索引页有记录被缓存在Insert Buffer B+树中
IBUF_BITMAP_IBUF11 表示该页为Insert Buffer B+树的索引页

4. Merge Insert Buffer

??通过前面的介绍可知，Insert/Change Buffer是一颗B+树。若需要实现插入记录的辅助索引不在缓冲池中，那么需要将辅助索引记录首先插入到这颗B+树中。但是Insert Buffer中的记录何时合并(merge)到真正的辅助索引中呢？

??概况的说，Merge Insert Buffer的操作可能发生以下几种情况：

  • 辅助索引页被读取到缓冲池时
  • Insert Buffer Bitmap页追踪到该辅助索引页时已经无可用空间
  • Master Thread

??第一种情况为当辅助索引页被读取到缓冲池中时，例如在这个执行正常的select查询操作，这时需要检查Insert Buffer Bitmap页，然后u企鹅人该辅助索引页是否有记录存放到Insert Buffer B+树种。若有，则将Insert Buffer B+树中该页的记录插入到该辅助索引页中。可以看到对该页多次的记录操作通过一次操作合并到原有的辅助索引页中，因此性能会大幅提高。

??Insert Buffer Bitmap页用来追踪每个辅助索引页的可用空间，并且至少有1/32页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于1/32页，则会强制进行一个合并操作，即强制读取辅助索引页，将Insert Buffer B+树种该页的记录及待插入的记录插入到辅助索引中。

??最后一种情况，之前在分析Master Thread时曾讲到，在Master Thread线程中每秒或10秒会进行一次Merge Insert Buffer的操作，不同之处在于每次进行merge操作页数量不同。

??在Master Thread中，执行merge操作的不止是一个页，而是根据srv_innodb_io_capacity的百分比来决定真正要合并多少个辅助索引页。但InnoDB存储引擎又是根据怎样的算法来得知需要合并的辅助索引页呢？

??在Insert Buffer B+树种，辅助索引页根据(space,offset)都已排序好，所以可以根据(sapce,offset)的排序顺序进行页的选择。然而，对于Insert Buffer页的选择，InnoDB存储引擎并非采用这个方式，它随机的选择Insert Buffer B+树的一个页，读取该页种sapce及之后所需要数量的页。该算法在复杂的情况下应有更好的公平性。同时，若进行merge时，要进行merge的表已经被删除，此时可以直接丢弃已经被Insert Change Buffer的数据记录。

6.2 两次写

??如果说Insert Buffer带给InnoDB存储引擎是性能上的提升，那么doublewrite带给InnoDB存储引擎的是数据页的可靠性。

??当数据库宕机时，可能InnoDB引擎正在写入某个页到表种，而这个页只写了一部分，比如16KB的页，只写了前4KB，之后就发生了宕机，这种情况被称为部分写时效(partial page write)。在InnoDB存储引擎未使用doublewrite技术前，曾经出现过因为部分写失效而导致数据丢失的情况。

??或许大家会想到，如果发生写失效，可以通过重做日志进行恢复。但是必须清楚的认识到，重做日志中记录的是对页的物理操作，如偏移量800，写’aaa’记录。如果这个页本身已经发生了损坏，再对其进行重做是没有意义的。这就是说，在应用(apply)重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewrite。

??doublewrite由两部分组成，一部分是内存中的doublewrite buffer,大小为2MB，另一部分是物理磁盘上共享表空间中连续的128个页，即2个分区，大小同样为2MB。在对缓冲池的脏页进行刷新时，不直接写磁盘，而是会根据memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MB顺序写入共享表空间的物理磁盘上，然后马上调用fsynx函数，同步磁盘，避免缓存写带来的问题。在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入之后，再将doublewrite buffer中的页写入各个表空间文件中，此时写入则是离散的。可以通过以下命令观察到doublewrite运行的情况：

在这里插入图片描述

mysql> show global status like 'innodb_dblwr%'\G;
*************************** 1. row ***************************
Variable_name: Innodb_dblwr_pages_written
        Value: 9234
*************************** 2. row ***************************
Variable_name: Innodb_dblwr_writes
        Value: 1424
2 rows in set (0.00 sec)

??可以看到，doublewrite一共写了9234个页，但实际写入次数为1424。如果发现系统在高峰时的Innodb_dblwr_pages_written:Innodb_dblwr_writes远小于，那么可以说明系统压力并不是很高。

??如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。

??注意：如果用户需要统计数据库生产环境中写入的量，最安全的方法是根据Innodb_dblwr_pages_written来进行统计，这在所有版本的MySQL数据库中都是正确的。

??参数skip_innodb_doublewrite可以禁止使用doublewrite功能,这时可能会发生前面体积的写失效问题。不过如果用户由多个从服务器(slave)，需要提供较快的性能，也许可以通过启用这个参数。不过对于需要提供高可靠的主服务器(master)，任何时候用户都应该确保开启doublewrite功能。

??注意：有些文件系统本身就提供了部分写失效的防范机制，如ZFS文件系统。在这种情况下，用户就不需要开启doublewrite了。

6.3 自适应哈希索引

??哈希(hash)是一种非常快的查找方法，在一般情况下这种查找的时间复杂度为O(1)，即一般需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3-4层，故需要3-4次的查询。

??InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问频率和模式来自动的为某些热点页建立哈希索引。

??AHI有一个要求，即对这个页的连续访问模式必须是一样的。例如对于(a,b)这样的联合索引页，其访问模式可以是以下情况：

  • where a=xxx
  • where a=xxx and b=xxx

??访问模式一样指的是查询的条件一样，若交替进行上诉两种查询，那么InnoDB存储引擎不会对该页造成AHI。此外AHI还有如下要求:

  • 以该模式访问100次
  • 页通过该模式访问了N次，其中页=页中记录 * 1/16

??根据InnoDB存放引擎官方的文档显示，启用AHI后，读取和写入速度可以提高2倍，辅助索引的连接操作性能可以提高5倍，毫无疑问，AHI是非常好的优化模式，其涉及思想是数据库自优化的(self-tuning),即无需DBA对数据库进行了人为调整。

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2022-12-02 16:58:00 0x7f5730120700 INNODB MONITOR OUTPUT
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 3 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

??通过show engine innodb status\G可以查看AHI的使用状况，包括AHI大小、使用情况、每秒使用AHI搜索的情况。值得注意的是，哈希索引只能用来搜索等值的查询，如select * from zxy where name='zxy';而对于其他类型的查找，比如范围查找，是不能使用哈希索引的，因此出现了non-hash searches/s的情况。通过hash search:non-hash searches可以大概了解使用哈希索引后的效率。

??由于AHI是由InnoDB存储引擎控制的，因此这里的信息只供用户参考。不过用户可以通过观察show engine innodb status的结果及参数innodb_adaptive_hash_index来考虑禁用或启用此特性，默认AHI为开启状态。

6.4 异步IO

??为了提高磁盘操作性能，当前的数据库系统都采用异步IO(Async IO)的方式来处理磁盘操作。InnoDB引擎也是如此。

??与Async IO对应的是Sync IO，即没进行一次IO操作，需要等待当前操作结束后才能继续接下来的操作，但是如果用户发出的是一条索引扫码的查询，那么这条SQL查询语句可能需要扫描多个索引页，也就是需要进行多次的IO操作在每扫描一个页等待其他完成后再进行下一次的扫描，这也是需要进行多次IO操作。在每扫描一个页并等待其完成后再进行下一次的扫描，这也是完全没有必要的。用户可以在发出一个IO请求后，立即再发出一个IO请求，当全部请求发送完毕后，等待所有IO操作的完成，也就是Async IO。

??Async IO的另一个优势是可以进行IO Merge操作，也就是将多个IO合并为1个IO，这样可以提高IOPS的性能。例如用户需要访问页的(sapce,page_no)为：

(8,6)、(8,7)、(8,8)

??每个页的大小为16KB，那么同步IO需要进行3次IO操作。而AIO会判断到这三个页是连续的(显然可以通过(space,page_no)可知)。因此AIO底层会发送一个IO请求，从(8,6)开始，读取48KB的页。

??参数innodb_use_native_aio用来控制是否启用Native AIO,在Linux操作系统下，默认值为ON:

mysql> show variables like 'innodb_use_native_aio'\G;
*************************** 1. row ***************************
Variable_name: innodb_use_native_aio
        Value: ON
1 row in set (0.00 sec)

??用户可以通过开启和关闭Native AIO功能来比较InnoDB性能的提升。官方的测试显示，启用Native AIO，恢复速度可以提高75%。

??在InnoDB存储引擎，read ahead方式的读取都是通过AIO完成，脏页的刷新，即磁盘的写入操作则全部由AIO完成。

6.5 刷新邻接页

??InnoDB存储引擎还提供了FLush Neighbor Page(刷新邻接页)的特性。其工作原理为：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区的所有页，如果是脏页，那么就一起进行刷新。这样做的好处显而易见，通过AIO可以将多个IO写入操作合作并未一个IO操作，故该工作机制在传统机械磁盘下有着显著的优势。但是需要考虑如下两个问题：

  • 是不是可能将不怎么脏的页进行了写入，而该页之后很快又变成了脏页。
  • 固态硬盘有着较高的IOPS，是都还需要这个特性？

??为此，InnoDB存储引擎从1.2.x版本开始提供了参数innodb_flush_neghbors，用来控制是否启用该特性。对于传统机械硬盘建议启用该特性，而对于固态硬盘有着较高的IOPS性能的磁盘，则建议将该参数设置为0，关闭此特性。

7 启动、关闭与恢复

??InnoDB是MySQL数据库得存储引擎之一，因此InnoDB存储引擎得启动和关闭，可以说是MySQL实例得启动过程中对InnoDB存储引擎的处理过程。

??在关闭时，参数innodb_fast_shutdown影响着表的存储引擎为InnoDB的行为。该参数可取值为0、1、2，默认值为1。

mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.06 sec)
  • 0 表示在MySQL数据库关闭时，InnoDB需要完成所有的full purge和merge insert buffer，并且将所有的脏页刷新回磁盘。这需要一些时间，有时甚至需要几个小时完成。
  • 1是参数innodb_fast_shutdown的默认值，表示不需要完成上诉的full purge和merge insert buffer操作，但是在缓冲池中一些数据脏页还是会刷新回到磁盘。
  • 2表示不完成full purge和merge insert buffer操作，也不将缓冲池中的数据脏页写回磁盘，而是将日志写入日志文件。这样不会有任何事务的丢失，但是下次MySQL数据库启动时，回进行恢复操作(recovery)。

??当正常关闭MySQL数据库时，下次的启动应该会正常。但是如果没有正常的关闭数据库，比如：用kill命令关闭数据库，MySQL运行中重启服务器，又或者是innodb_fast_shutdown=2。那么下次MySQL数据库启动时会对InnoDB存储引擎的表进行恢复操作。

??参数innodb_force_recovery影响了整个InnoDB存储引擎恢复的状况。该参数默认值为0。代表的是当需要恢复时，进行所有的恢复操作，当不能进行有效恢复时，如数据页发生了corruption,MySQL数据库可能发生宕机(crash)，并把错误写入错误日志中。

mysql> show variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_force_recovery | 0     |
+-----------------------+-------+
1 row in set (0.00 sec)

??但是，在某些情况下，可能并不需要进行完整的恢复操作，因为用户自己知道怎么进行恢复。比如在对一个表进行alter table操作时发生了意外，数据库重启时会对InnoDB表进行回滚操作，对于一个大表来说，这可能需要很长时间。这时用户可以自行进行恢复，如可以把表删除，从备份中重新导入数据到表，可能这些操作的速度远远快于回滚操作。

??参数innodb_force_recovery还可以设置6个非零值：1~6。大的数字包含了前面所有小数字表示的影响，具体情况如下:

  • 1(srv_force_ignore_corrupt): 忽略检查到的corrupt页。
  • 2(srv_force_no_background): 阻止Master Thread线程的运行，如Master Thread线程需要进行full purge操作，而这会导致crash。
  • 3(srv_force_no_trx_undo): 阻止Master Thread线程的运行，如Master Thread线程需要进行full purge操作，而这会导致crash。
  • 4(srv_force_no_ibuf_merge): 不进行插入缓冲的合并操作。
  • 5(srv_force_no_undo_log_scan): 不查看撤销日志(undo log),InnoDB存储引擎会将未提交的事务视为已提交。
  • 6(srv_force_no_log_redo): 不进行前滚的操作。

??需要注意的是，在设置了参数innodb_force_recovery大于0后，用户可以对表进行select、create、drop等操作，但insert、update和delete这类DML操作是不允许的。

文章来源:https://blog.csdn.net/m0_51197424/article/details/135341992
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章