数据库系列之InnoDB存储引擎解密

InnoDB的架构分为内存中的结构和磁盘上的结构，本文简要介绍了MySQL逻辑模块以及InnoDB存储引擎中的内存模块，加深对InnoDB存储引擎的理解。

---

1、MySQL逻辑模块

左边部分是MySQL整体逻辑架构图，自上而下主要包括四个部分：

1. 连接层，服务于C/S程序，实现与SQL的交互
2. 核心服务层，也被称作SQL Layer，完成权限判断、SQL解析、执行计划优化、查询缓存的处理以及所有内置的函数。各个存储引擎提供的功能都集中在这一层，如存储过程、触发器、视图等。
3. 存储引擎层Storage Engine Layer，由多种存储引擎共同组成，负责存储和获取所有存储在MySQL中的数据。每个存储引擎都有自己的优点和缺陷，服务器是通过存储引擎API来与它们交互的。MySQL中的存储引擎可以实现插件式管理，它提供了一系列标准的管理和服务支持，实现基于表的存储引擎。
4. 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

1.1 SQL SELECT语句执行过程

MySQL整个查询执行过程，总的来说分为6个步骤：请求、缓存、SQL解析、优化SQL查询、调用引擎执行、返回结果。

• 连接：客户端向MySQL服务器发送一条查询请求，与connectors交互，连接池认证相关处理。请求会暂时存放在连接池（connection pool)中并由处理器（Management Serveices & Utilities）管理。当该请求从等待队列进入到处理队列，管理器会将该请求丢给SQL接口（SQL Interface）。
• 缓存：SQL接口接收到请求后会将请求进行hash处理，并与缓存中的结果进行比对，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段
• 解析：服务器进行SQL解析(词法语法)、预处理
• 优化：再由优化器生成对应的执行计划
• 执行：MySQL根据执行计划，调用存储引擎的API来执行查询
• 结果：将结果返回给客户端，同时缓存查询结果。

1.1.1 Connectors：客户端/服务端通信协议

MySQL客户端/服务端通信协议是“半双工”的，在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

连接器Connectors负责跟客户端建立连接、获取权限、维持和管理连接。验证通过后，连接器会到权限表里面查出你拥有的权限，之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。一个用户成功建立连接后，即使管理员对这个用户的权限做了修改，也不会影响已经存在连接的权限，修改完后，只有再新建的连接才会使用新的权限设置。

连接完成后，如果没有后续的动作，这个连接就处于空闲状态，可以通过show processlist命令查看。

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |  418 | Waiting on empty queue | NULL             |
|  9 | root            | localhost | NULL | Query   |    0 | init                   | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)

客户端如果太长时间没动静，连接器就会自动将它断开；这个时间是由参数wait_timeout控制的，默认值是8小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query。

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

1.1.2 查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么 MySQL 会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。MySQL将缓存存放在一个引用表 (不要理解成table，可以认为是类似于 HashMap 的数据结构)，通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同 (例如 : 空格、注释)，都会导致缓存不会命中。MySQL 8.0版本中查询缓存的功能已经被删除：

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | NO    |
+------------------+-------+
1 row in set (0.00 sec)

1.1.3 Analyzer分析器

如果缓存没命中，MySQL会对SQL语句进行语法解析。

• 词法分析：MySQL需要识别出SQL语句中的字符串是什么，代表什么，比如从select关键字识别出是一个查询语句
• 语法分析：语法分析器会根据语法规则，判断输入的SQL语句是否满足MySQL语法。如果你 SQL 语句不对，就会收到 You have an error in your SQL syntax 的错误提醒。

1.1.4 Optimizer优化器：查询优化

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本：

mysql> select count(1) from sbtest.sbtest1 where k between 20000 and 21000;
+----------+
| count(1) |
+----------+
|      690 |
+----------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'last_query_cost'; 
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| Last_query_cost | 139.681195 |
+-----------------+------------+
1 row in set (0.00 sec)

Last_query_cost显示要做多少页的随机查询才能得到最后一查询结果，这个结果是根据一些列的统计信息计算得来的，这些统计信息包括 : 每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。

MySQL的查询优化器使用了非常多的优化策略来生成一个最优的执行计划：

1. 在表里面有多个索引的时候，决定使用哪个索引；
2. 重新定义表的关联顺序(多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序)
3. 优化MIN()和MAX()函数(找某列的最小值，如果该列有索引，只需要查找 B+Tree索引 最左端，反之则可以找到最大值)
4. 提前终止查询(比如 : 使用 Limit 时，查找到满足数量的结果集后会立即终止查询)
5. 优化排序(在老版本 MySQL 会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多)

1.1.5 查询执行引擎Actuator

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。比如执行SQL语句：

mysql> select count(1) from sbtest.sbtest1 where k=1;

1. 判断是否有查询权限，有就继续执行没有就返回权限错误。例如判断当前连接对这个表 sbtest.sbtest1有没有执行查询的权限，如果没有，就会返回没有权限的错误。
2. 执行器根据表的引擎定义去调用引擎接口。对于没有索引的表使用全表扫描API：比如我们这个例子中的表sbtest.sbtest1中，k字段没有索引，那么执行器的执行流程是这样的：
   1. 调用InnoDB引擎接口取这个表的第一行，判断 k值是不是1，如果不是则跳过，如果是则将这行存在结果集中；
   2. 调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行。
   3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

1.1.6 返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。结果集返回客户端是一个增量且逐步返回的过程，有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足客户端/服务器通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

2、InnoDB存储引擎

InnoDB的架构分为两块：内存中的结构和磁盘上的结构。InnoDB使用日志先行策略，将数据修改先在内存中完成，并且将事务记录成重做日志(Redo Log)，转换为顺序IO高效的提交事务。这里日志先行，指的是日志记录到数据库以后，对应的事务就可以返回给用户，表示事务完成。但是实际上，这个数据可能还只在内存中修改完，并没有刷到磁盘上去。内存是易失的，如果在数据落地前，机器挂了，那么这部分数据就丢失了。

InnoDB通过redo日志来保证数据的一致性。如果保存所有的重做日志，显然可以在系统崩溃时根据日志重建数据。当然记录所有的重做日志不太现实，所以 InnoDB 引入了检查点机制。即定期检查，保证检查点之前的日志都已经写到磁盘，则下次恢复只需要从检查点开始。

2.1 InnoDB中的后台线程

InnoDB后台线程主要用于维持服务器的正常运行和完成用户提交的任务，主要包括：master thread、page cleaner thread、purge thread、read thread、write thread、redo log thread、insert buffer thread、monitor thread、error monitor thread、lock monitor thread等。

通过以下SQL可以查看当前thread信息：

mysql> select THREAD_ID,NAME from performance_schema.threads;
+-----------+---------------------------------------------+
| THREAD_ID | NAME                                        |
+-----------+---------------------------------------------+
|         1 | thread/sql/main                             |
|         3 | thread/innodb/io_ibuf_thread                |
|         4 | thread/innodb/io_log_thread                 |
|         5 | thread/innodb/io_read_thread                |
|         6 | thread/innodb/io_read_thread                |
|         7 | thread/innodb/io_read_thread                |
|         8 | thread/innodb/io_read_thread                |
|         9 | thread/innodb/io_write_thread               |
|        10 | thread/innodb/io_write_thread               |
|        11 | thread/innodb/io_write_thread               |
|        12 | thread/innodb/io_write_thread               |
|        13 | thread/innodb/page_flush_coordinator_thread |
|        14 | thread/innodb/log_checkpointer_thread       |
|        15 | thread/innodb/log_flush_notifier_thread     |
|        16 | thread/innodb/log_flusher_thread            |
|        17 | thread/innodb/log_write_notifier_thread     |
|        18 | thread/innodb/log_writer_thread             |
|        19 | thread/innodb/srv_lock_timeout_thread       |
|        20 | thread/innodb/srv_error_monitor_thread      |
|        21 | thread/innodb/srv_monitor_thread            |
|        22 | thread/innodb/buf_resize_thread             |
|        23 | thread/innodb/srv_master_thread             |
|        24 | thread/innodb/dict_stats_thread             |
|        25 | thread/innodb/fts_optimize_thread           |
|        26 | thread/mysqlx/worker                        |
|        27 | thread/mysqlx/worker                        |
|        28 | thread/mysqlx/acceptor_network              |
|        32 | thread/innodb/buf_dump_thread               |
|        33 | thread/innodb/clone_gtid_thread             |
|        34 | thread/innodb/srv_purge_thread              |
|        35 | thread/innodb/srv_purge_thread              |
|        36 | thread/innodb/srv_worker_thread             |
|        37 | thread/innodb/srv_worker_thread             |
|        38 | thread/innodb/srv_worker_thread             |
|        39 | thread/innodb/srv_worker_thread             |
|        40 | thread/innodb/srv_worker_thread             |
|        41 | thread/innodb/srv_worker_thread             |
|        42 | thread/sql/event_scheduler                  |
|        43 | thread/sql/signal_handler                   |
|        44 | thread/mysqlx/acceptor_network              |
|        45 | thread/sql/compress_gtid_table              |
|        47 | thread/sql/one_connection                   |
+-----------+---------------------------------------------+
42 rows in set (0.00 sec)

2.1.1 Master Thread

Master thread是核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、undo页的回收等。Master thread内部由多个循环组成，包括主循环、后台循环、刷新循环和暂停循环，master thread会根据数据库运行的状态在不同的循环之间切换。在InnoDB 1.0.x版本之前的主循环中，分两大部分操作：每秒钟的操作和每10秒钟的操作。

1）每秒一次的操作包括：

• 日志缓冲刷新到磁盘，即使这个事务还没有提交（总是），这点解释了为什么再大的事务commit时都很快
• 合并插入缓冲（可能），合并插入并不是每秒都发生，InnoDB会判断当前一秒内发生的IO次数是否小于5，如果是，则系统认为当前的IO压力很小，可以执行合并插入缓冲的操作。
• 至多刷新100个InnoDB的缓冲池的脏页到磁盘（可能），这个刷新100个脏页也不是每秒都在做。

即使某个事务还没有提交，InnoDB存储引擎仍然每秒会将重做日志缓存中的内容刷新到redo log中。

2）每10秒一次的操作包括：

• 刷新100个脏页到磁盘（可能）；
• 合并至多5个插入缓冲（总是）；
• 将日志缓冲刷新到磁盘（总是）；
• 删除无用的undo页（总是）；
• 产生一个检查点（checkpoing）；

在以上过程中，InnoDB存储引擎会先判断过去10s内的磁盘IO操作是否小于200次，如果是InnoDB存储引擎认为当前有足够的磁盘IO操作能力，因此将100个脏页刷新到磁盘。接着，InnoDB存储引擎会合并插入缓冲，之后再将日志缓冲刷新到磁盘。最后InnoDB存储引擎会执行full purge操作，删除无用的undo页，它会先去判断当前系统中已被标记为删除的行是否可以删除，如果可以则可以立即将其删除。

在InnoDB 1.0.x版本之前，InnoDB存储引擎对于IO是有限制的，缓冲池向磁盘刷新做了一定的硬编码，随着磁盘硬件性能提高，这种方式会限制InnoDB对磁盘IO的性能。因此在1.0.x版本之后，InnoDB提供了参数innodb_io_capacity，用来表示磁盘IO的吞吐量，默认值为200。对于刷新到磁盘页的数量，会按照innodb_io_capacity的百分比进行控制：

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

命令show engine innodb status;可以查看master thread信息：

=====================================
2021-08-22 20:38:37 140186194323200 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 255 srv_idle
srv_master_thread log flush and writes: 0

2.1.2 Purge Thread

事务被提交后，其使用的undo log可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页，它的作用是真正的删除记录和删除undo log。

比如语句delete from tb1 where pk=1;

1. 将pk=1的记录标记为删除(delete-mark，infobits)，数据库中pk=1的记录此时还是存在的，空间并没有被释放，该操作为同步操作(SQL执行完，也就标记完成了)
2. Purge为后台线程(purge线程)异步操作，会真正的删除该记录，且空间被释放。purge线程是系统自动的，无法人工控制。

Page页标记为已删除的原因有两点：1）该事物可能需要回滚，先作保留；2）当事物1去删除pk=1且没有提交时，事物2要能看到pk=1的记录(事物的隔离性)。根据不同的过滤条件，对删除标记的处理也不一样，如下表所示：

因此，标记为delete-mark的记录最后会被purge线程回收，Purge会检测记录上是否有其他事物在引用undo，如果没有就可以删除。InnoDB 1.2版本开始，InnoDB支持多个purge thread，这样能够加快undo页的回收，同时离散的读取undo页也可以进一步提升磁盘的随机读取性能，目前MySQL 8.0版本中默认设置为4。

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

2.1.3 Page Cleaner Thread

Page Cleaner Thread是在InnoDB 1.2.x版本新引入的,其作用是将之前版本中脏页的刷新操作都放入单独的线程中来完成,这样减轻了Master Thread的工作及对于用户查询线程的阻塞。

2.1.4 IO Thread

在InnoDB存储引擎中大量使用了异步IO来处理写IO请求，IO Thread的工作主要是负责这些IO请求的回调处理。InnoDB中有4种IO thread，分别为write、read、insert buffer和log IO thread：

• write thread：负责数据库的写操作，可配置多个写线程
• read thread：负责数据库的读取操作，可配置多个读线程
• insert buffer thread: 主要负责插入缓冲区的合并操作
• log thread：用于将重做日志刷新到log file中

通过命令SHOW ENGINE INNODB STATUS可以观察InnoDB中的IO Thread：

--------
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)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
854 OS file reads, 207 OS file writes, 38 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

2.2 InnoDB中的内存结构

内存中的结构主要包括Buffer Pool、Change Buffer、Adaptive Hash Index以及Log Buffer四部分。如果从内存上来看，Change Buffer和Adaptive Hash Index占用的内存都属于Buffer Pool，Log Buffer占用的内存与Buffer Pool独立。

2.2.1 Buffer Pool

Buffer pool是一块内存区域，用来缓存表和索引数据，其大小通过参数innodb_buffer_pool_size来设置，缓存的数据页类型有索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDB的锁信息、字典信息等。Buffer Pool两个最主要的功能：一个是加速读，一个是加速写。加速读呢？就是当需要访问一个数据页面的时候，如果这个页面已经在缓存池中，那么就不再需要访问磁盘，直接从缓冲池中就能获取这个页面的内容。加速写呢？就是当需要修改一个页面的时候，先将这个页面在缓冲池中进行修改，记下相关的重做日志，这个页面的修改就算已经完成了。至于这个被修改的页面什么时候真正刷新到磁盘，通过一种称为checkpoint的机制刷新回磁盘。

基于效率考虑，InnoDB中数据管理的最小单位为页，默认每页大小为16KB，每页包含若干行数据。为了提高缓存管理效率，InnoDB的缓存池通过一个页链表实现，很少访问的页会通过缓存池的LRU算法淘汰出去。InnoDB的缓冲池页链表分为两部分：New sublist(默认占5/8缓存池)和Old sublist(默认占3/8缓存池，可以通过innodb_old_blocks_pct修改，默认值为37)，其中新读取的页会加入到Old sublist的头部，而Old sublist中的页如果被访问，则会移到New sublist的头部。缓冲池的使用情况可以通过show engine innodb status命令查看。其中一些主要信息如下：

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137035776  # 分配给InnoDB缓存池的内存(字节)
Dictionary memory allocated 382389 # 分配给InnoDB数据字典的内存(字节)
Buffer pool size   8192  # 缓存池的页数目
Free buffers       7217  # 缓存池空闲链表的页数目
Database pages     971  # 缓存池LRU链表的页数目
Old database pages 378  
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 829, created 142, written 156
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: 971, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Buffer Pool的初衷，是为了减少和磁盘的I/O交互，最好每次在访问某个页的时候它都已经被缓存到Buffer Pool中了。假设我们一共访问了n次页，那么被访问的页已经在缓存中的次数除以n就是所谓的缓存命中率，我们的期望就是让缓存命中率越高越好。

InnoDB Buffer Pool采用经典的LRU算法来进行页面淘汰，以提高缓存命中率。当Buffer Pool中不再有空闲的缓存页时，就需要淘汰掉部分最近很少使用的缓存页。InnoDB中创建LRU Least Recently Used）链表按照最近最少使用的原则去淘汰缓存页的，当我们需要访问某个页时，可以这样处理LRU链表：

• 如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓存页时，就把该缓存页包装成节点塞到链表的头部。
• 如果该页在Buffer Pool中，则直接把该页对应的LRU链表节点移动到链表的头部。

但是这样做会有一些性能上的问题，比如一次全表扫描或一次逻辑备份就把热数据给冲完了，就会导致导致缓冲池污染问题。Buffer Pool中的所有数据页都被换了一次血，其他查询语句在执行时又得执行一次从磁盘加载到Buffer Pool的操作，这严重的影响到其他查询对 Buffer Pool 的使用，严重的降低了缓存命中率。所以InnoDB存储引擎对传统的LRU算法做了一些优化，在InnoDB中加入了midpoint。新读到的页，虽然是最新访问的页，但并不是直接插入到LRU列表的首部，而是插入LRU列表的midpoint位置。这个算法称之为midpoint insertion stategy。默认配置插入到列表长度的5/8处。midpoint由参数innodb_old_blocks_pct控制。
midpoint之前的列表称之为new列表，之后的列表称之为old列表。可以简单的将new列表中的页理解为最为活跃的热点数据。同时InnoDB存储引擎还引入了innodb_old_blocks_time来表示页读取到mid位置之后需要等待多久才会被加入到LRU列表的热端。可以通过设置该参数保证热点数据不轻易被刷出。

mysql> show variables like 'innodb_old_blocks_%'\g;
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_pct  | 37    |
| innodb_old_blocks_time | 1000  |
+------------------------+-------+
2 rows in set (0.00 sec)

2.2.2 Change Buffer

通常来说，InnoDB辅助索引不同于聚集索引的顺序插入，如果每次修改二级索引都直接写入磁盘，则会有大量频繁的随机IO。Change buffer的主要目的是将对非唯一辅助索引页的操作缓存下来，以此减少辅助索引的随机IO，并达到操作合并的效果。它会占用部分Buffer Pool的内存空间。

在MySQL5.5之前Change Buffer其实为Insert Buffer，最初只支持insert操作的缓存，随着支持操作类型的增加，改名为Change Buffer。如果辅助索引页已经在缓冲区了，则直接修改即可；如果不在，则先将修改保存到Change Buffer。Change Buffer的数据在对应辅助索引页读取到缓冲区时合并到真正的辅助索引页中。Change Buffer内部实现也是使用的 B+ 树。可以通过innodb_change_buffering配置是否缓存辅助索引页的修改，默认为all，即缓存 insert/delete-mark/purge操作。

查看Change Buffer信息也可以通过show engine innodb status命令。

-------------------------------------
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 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

INFORMATION_SCHEMA.INNODB_BUFFER_PAGE提供了buffer pool中每个page的元数据，包括change buffer index和change buffer bitmap pages

mysql> 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;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                   2 |        8192 |                        0.0244 |
+---------------------+-------------+-------------------------------+
1 row in set (0.45 sec)

2.2.3 Adaptive Hash Index

自适应哈希索引(AHI)查询非常快，一般时间复杂度为O(1)，相比B+树通常要查询3~4次，效率会有很大提升。Innodb通过观察索引页上的查询次数，如果发现建立哈希索引可以提升查询效率，则会自动建立哈希索引，称之为自适应哈希索引，不需要人工干预。可以通过 innodb_adaptive_hash_index 开启，MySQL 8.0默认开启。

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

考虑到不同系统的差异，有些系统开启自适应哈希索引可能会导致性能提升不明显，而且为监控索引页查询次数增加了多余的性能损耗，MySQL 5.7更改了AHI实现机制，每个AHI都分配了专门分区，通过 innodb_adaptive_hash_index_parts配置分区数目，默认是8个。

2.2.4 Log Buffer

Log Buffer是重做日志在内存中的缓冲区，大小由innodb_log_buffer_size定义，默认是16M。一个大的Log Buffer可以让大事务在提交前不必将日志中途刷到磁盘，可以提高效率。配置项innodb_flush_log_at_trx_commit用于控制Log Buffer如何写入和刷到磁盘。注意，除了MySQL的缓冲区，操作系统本身也有内核缓冲区。

• 默认为1，表示每次事务提交都会将Log Buffer写入操作系统缓存，并调用配置的"flush" 方法将数据写到磁盘。设置为1因为频繁刷磁盘效率会偏低，但是安全性高，最多丢失 1个事务数据，而设置为0和2则可能丢失1秒以上的事务数据
• 0则表示每秒才将Log Buffer写入内核缓冲区并调用"flush"方法将数据写到磁盘
• 2则是每次事务提交都将Log Buffer写入内核缓冲区，但是每秒才调用"flush"将内核缓冲区的数据刷到磁盘

配置不同的值效果如下图所示：

可以发现，InnoDB基本每秒都会将Log buffer落盘，而InnoDB中使用的redo log和undo log，它们是分开存储的。redo log在内存中有log buffer，对应磁盘上的ib_logfile文件；而undo log是记录在表空间ibd文件中的，InnoDB为undo log会生成undo页，对undo log本身的操作（比如向undo log插入一条记录），也会记录redo log，因此undo log并不需要马上落盘。而redo log则通常会分配一块连续的磁盘空间，然后先写到log buffer，并每秒刷一次磁盘。Redo log必须在数据落盘前先落盘(Write Ahead Log)，从而保证数据持久性和一致性，而数据本身的修改可以先驻留在内存缓冲池中，再根据特定的策略定期刷到磁盘。

2.2.5 Checkpoint

每次执行update、delete等语句更改记录时，缓冲池中的页与磁盘不一致，但是缓冲池的页不能频繁刷新到磁盘中（频率过大性能低），因此增加了write ahead log策略，当事务提交时先写重做日志，再修改内存页，当发生宕机时通过重做日志来恢复。Checkpoint解决以下问题：

• 减少重做日志大小，缩减数据恢复时间
• 缓冲池不够用时将脏页刷回磁盘
• 重做日志不可用时将脏页刷回磁盘（如写满）

InnoDB存储引擎内部，两种checkpoint：Sharp Checkpoint和Fuzzy Checkpoint

• Sharp Checkpoint发生在数据库关闭时，将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数：innodb_fast_shutdown=1。不适用于数据库运行时的刷新。
• 在数据库运行时，InnoDB存储引擎内部采用Fuzzy Checkpoint，只刷新一部分脏页。

InnoDB中通过参数innodb_max_dirty_pages_pct（默认75%）控制checkpoint的频率：

• 若每个重做日志大小为1G，定了了两个总共2G，则： asyn_water_mark = 75%*重做日志总大小；syn_water_mark = 90%*重做日志总大小
• 当checkpoint_age < asyn_water_mark时则不需要刷新脏页回盘
• 当syn_water_mark < checkpoint_age < syn_water_mark 时触发ASYNC FLUSH
• 当checkpoint_age>syn_water_mark触发sync flush，此情况很少发生，一般出现在大量load data或bulk insert时

2.3 InnoDB中的关键特性

2.3.1 Insert Buffer

一般情况下，主键是行唯一的标识符，通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。如果索引是非聚集的且不唯一，在进行插入操作时，数据的存放对于非聚集索引叶子节点的插入不是顺序的，这时需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。这是因为B+树的特性决定了非聚集索引插入的离散性。

Insert Buffer的设计，对于非聚集索引的插入和更新操作，不是每一次直接插入到索引页中，而是先判断插入非聚集索引页是否在缓冲池中，若存在，则直接插入，不存在，则先放入一个Insert Buffer对象中。数据库这个非聚集的索引存放在另一个位置，然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。

Insert buffer的使用需要满足的两个条件：索引是辅助索引；索引不是唯一的。辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生，从而导致Insert Buffer失去了意义。

-------------------------------------
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

上述中seg size表示当前insert buffer的大小、free list len表示空闲列表的长度、size代表已经合并记录页的数量。

2.3.2 Double Write

Insert Buffer带给Innodb存储引擎性能上的提升，double write则带来数据页的可靠性。假如以下部分写失败场景，当数据库正在从内存向磁盘写一个数据页时，数据库宕机，从而导致这个页只写了部分数据，这就是部分写失效，它会导致数据丢失。这时是无法通过重做日志恢复的，因为重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。从上面分析我们知道，在部分写失效的情况下，我们在应用重做日志之前，需要原始页的一个副本，两次写就是为了解决这个问题，下面是它的原理图：

Double write由两部分组成，一部分是内存中的doublewrite buffer，大小为2MB；另一部分是物理磁盘上共享表空间中连续的128个页，即2个extent，大小也为2MB。其实现原理如下：

1. 当刷新缓冲池脏页时，并不直接写到数据文件中，而是先拷贝至内存中的两次写缓冲区。
2. 接着从两次写缓冲区分两次写入磁盘共享表空间中，每次写入1MB
3. 待第2步完成后，再将两次写缓冲区写入数据文件
4. Doublewrite奔溃恢复，如果数据库在页写入数据文件的过程中宕机，在实例恢复时，可以从共享表空间中找到该页副本，将其拷贝覆盖原有的数据页，再应用重做日志即可。

其中第2步是额外的性能开销，但由于磁盘共享表空间是连续的，因此开销不是很大。可以通过参数innodb_doublewrite禁用两次写功能，默认是开启的，强烈建议开启该功能。

mysql> show variables like '%innodb_doublewrite%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_doublewrite            | ON    |
| innodb_doublewrite_batch_size | 0     |
| innodb_doublewrite_dir        |       |
| innodb_doublewrite_files      | 2     |
| innodb_doublewrite_pages      | 4     |
+-------------------------------+-------+
5 rows in set (0.01 sec)

2.3.3 自适应哈希索引

哈希是一种非常快的查找方法，在一般情况时间复杂度为O(1)，而B+树的查找次数，取决于B+树的高度，在生成环境中，B+树的高度一般为3-4层，不需要查询3-4次。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立哈希索引可以提升速度，就建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来的。因此建立的速度非常快，且不要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引。

根据官方文档显示，启用AHI后，读取和写入的速度可以提高2倍，负责索引的链接操作性能可以提高5倍。其设计思想是数据库自由化的，无需DBA对数据库进行人为调整。

2.3.4 Async IO

为了提高磁盘操作性能，InnoDB采用异步IO的方式来处理磁盘操作。与AIO对应的是Sync IO，即每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。但是如果用户发出的是一条索引扫描的查询，那么这条SQL语句可能需要扫描多个索引页，也就是需要进行多次IO操作。在每扫描一个页并等待其完成再进行下一次扫描，显然是没有必要的。用户可以在发出一个IO请求后立即再发出另外一个IO请求，当全部IO请求发送完毕后，等待所有IO操作完成，这就是AIO。AIO的另外一个优势是进行IO Merge操作，也就是将多个IO合并为一个IO操作，这样可以提高IOPS的性能。

在InnoDB 1.1.x之前，AIO的实现是通过InnoDB存储引擎中的代码来模拟的。但是从这之后，提供了内核级别的AIO的支持，称为Native AIO。MySQL可以通过参数innodb_use_native_aio来决定是否启用Native AIO。在InnoDB存储引擎中，read ahead方式的读取都是通过AIO完成，脏页的刷新，也是通过AIO完成。

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

2.3.5刷新邻接页

InnoDB存储引擎在刷新一个脏页时，会检测该页所在区(extent)的所有页，如果是脏页，那么一起刷新。这样做的好处是通过AIO可以将多个IO写操作合并为一个IO操作。该工作机制在传统机械磁盘下有显著优势。但是需要考虑以下两个问题：

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

为此InnoDB存储引擎1.2.x版本提供参数innodb_flush_neighbors来决定是否启用。对于传统机械硬盘建议使用，而对于固态硬盘则可以关闭。

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

---

参考资料：

1. https://blog.csdn.net/hguisu/article/details/7106342
2. https://www.jianshu.com/p/d4cc0ea9d097
3. https://zhuanlan.zhihu.com/p/47581960
4. https://blog.csdn.net/weixin_43927408/article/details/95228030
5. https://www.cnblogs.com/mikevictor07/p/12013507.html
6. https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html
7. 《MySQL技术内幕：InnoDB存储引擎》第2版，姜承尧著

---

转载请注明原文地址：https://blog.csdn.net/solihawk/article/details/119963662
文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！