Mysql技术内幕（一）--Mysql体系和InnoDB存储引擎（超详细）

前言

其实我想写一篇跟Mysql有关的博客很久了，一方面是想自己做一个较为全面的知识总结，一方面，现在再看《Mysql技术内幕-InnoDB存储引擎》。写下这篇文章，目的方便日后自己对Mysql方面知识的复习，也希望大家能够收获满满，主要是面向有一定的Mysql知识基础的人查缺补漏or复习用的。（讲道理，我写这篇文章还是花了很多功夫，尽量把每个知识点都讲到位，很多都是面试容易问的知识点，希望大家认真看，重点是1.3.1小结的InnoDB引擎）

Mysql技术内幕系列文章

Mysql技术内幕系列文章

一.Mysql体系结构和存储引擎

1.1 数据库和实例

1.数据库的概念：
物理操作系统文件或者其他形式文件类型的集合。在Mysql中，数据库文件可以是frm、ibd、MYD等形式的文件。
2.实例：
Mysql数据库由后台线程以及一个共享内存区组成，而共享内存区可以被运行的后台线程共享。（数据库实例才是真正用于操作数据库文件的角色）

Mysql数据库实例在系统上的表现就是一个进程：

ps -ef | grep mysql

结果：

稍微工整点的信息如下：

 74 99430     1   0  3:41下午 ??         0:03.66 /usr/local/mysql/bin/mysqld 
 --user=_mysql --basedir=/usr/local/mysql 
 --datadir=/usr/local/mysql/data 
 --plugin-dir=/usr/local/mysql/lib/plugin 
 --log-error=/usr/local/mysql/data/mysqld.local.err 
 --pid-file=/usr/local/mysql/data/mysqld.local.pid 
 --keyring-file-data=/usr/local/mysql/keyring/keyring 
 --early-plugin-load=keyring_file=keyring_file.so
  501   996   992   0  8:00下午 ttys000    0:00.00 grep mysql

可以看出，启动实例的时候，Mysql会读取各种配置文件，根据配置文件的参数来启动数据库实例。

这里还需要强调一点，用户对数据库数据的任何操作，包括数据库定义、数据查询、数据维护、数据库运行控制等都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

1.2 Mysql体系结构

首先来看下Mysql数据库的体系结构：

1. 连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求。

2. 管理服务和工具组件：系统管理和控制工具，例如备份恢复、Mysql复制、集群等 。

3. sql接口：接受用户的SQL命令，并且返回用户需要查询的结果。

4. 查询解析器：SQL命令传递到解析器的时候会被解析器验证和解析。(权限、语法结构)

5. 查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化。

6. 缓存：如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

7. 插入式存储引擎：存储引擎说白了就是如何管理操作数据（存储数据、如何更新、查询数据等）的一种方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型。（即存储和操作此表的类型）

8. 物理文件

Mysql数据库区别于其他数据的最重要的特点就是其插件式的表存储引擎，提供了一系列标准的管理和服务支持。（存储引擎是基于表的，而不是数据库）

1.3 Mysql存储引擎

其实Mysql支持的存储引擎还挺多的，这里就主要讲两个：InnoDB和MyISAM一引擎。

1.3.1 InnoDB引擎☆

先来说下这个引擎的特点：（相信大家都有一定的基础和了解的，而且我自己感觉，这些每一点拿出来都是一个面试的考点或者重要知识点，当然我会对我了解的知识做一个详细的展开（尽量！尽量！😆））

1. InnoDB存储引擎支持事务，其特点是行锁设计、支持外键、支持非锁定锁（即默认读取操作不会产生锁）。并且Mysql5.5.8版本起，默认的存储引擎就是InnoDB了。
2. InnoDB通过使用多版本并发控制MVCC来获得高并发性，并且实现了SQL标准的4种隔离级别，默认是Repeatable级别（重复读）。
3. 使用一种被称为next-key的连接锁策略来避免幻读的产生。
4. InnoDB还提供了插入缓冲、二次写、自定义哈希索引、预读等4大特性。
5. InnoDB引擎采用了聚集的方式，因此每张表的存储都是按照主键的顺序进行存放。
6. 如果没有显式的在表中定义主键，那么InnoDB会为每一行生成一个6字节大小的RowId，并以此为主键。（换句话说，无论咋样，一张表必有主键）

接下来就尽量对上面的特点做一个展开（个别不做深究）。

（1）存储策略和存储大小

InnoDB存储数据的策略有两种：

1. 共享表空间存储方式。

InnoDB的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多歌文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制，官方指出InnoDB表空间的最大限制是64TB。（敲黑板🤓，我曾经面试被问到这个，我没说出来，其实实际上也要看底层的存储数据结构啥的，但是这个最大限制还是记以下64TB，到时候好歹你得说出来这个数字）

2. 独享表空间存储方式。

每个表的数据以一个单独的文件来存放，此时的单表限制，就变成文件系统的大小限制了。

（3）事务的ACID

首先扯一下哈：InnoDB是第一个完整支持ACID事务的Mysql存储引擎（DBD是第一个支持事务的InnoDB存储引擎，但是注意前面还带了个ACID！别搞混了）

其次再说下什么是事务？
事务是并发控制的单位，用户定义的一个操作序列，其中事务必须满足ACID特性。

言归正传，什么是ACID？

1. A（Atomicity）-原子性：表示事务内部操作不可分割，要么全部提交成功，要么全部回滚。
2. C（Consistency）-一致性：事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。
3. I（Isolation）-隔离性：事务之间不能互相干扰。
4. D（Durability）-持久性：一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。

讲完ACID，那好说了，咱来接着扯：Mysql的事务是怎么实现的？（也是面试官问过我的一个问题，当时蒙了，不知道咋回答，现在好了🤣🤣）都说，回答问题要讲究层次性，那就看我表演：

首先，Mysql的事务的实现即为ACID的实现。
第一：事务的原子性是通过undo log来实现，也就是所谓的回滚操作。 undo log记录了数据被修改之前的信息以及新增、删除的信息。undo log就是通过生成操作相反的sql语句来实现，举几个栗子🌰：
1.若undo log中有新增记录，则生成删除该记录的sql。
2.若undo log中有删除记录，则生成生成该记录的sql。
3.若undo log中有修改记录，则生成修改至原先语句的sql。
因此，所谓的回滚操作就是根据undo log做一个逆向操作。
第二：事务的持久性（这里就说几个重要的点，因为说白了，持久性跟存储有关）：1.redolog在提交commit前会写一次数据，顺序存储。
2.InnoDB的二次写以及自带的buffer pool。
第三：事务的隔离性则通过4种隔离级别来实现。
第四：事务的一致性：其实现依赖于以上3个特性的实现、即回滚、恢复、隔离机制。

（3）MVCC和隔离级别

上文提到了InnoDB通过使用多版本并发控制MVCC来获得高并发性，并且实现了SQL标准的4种隔离级别，那接下来就对这两点来进行阐述。

首先MVCC，全名（Multi-Version Concurrency Control），是Mysql的InnoDB存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。

基本思想： 利用多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系。（在MVCC中事务的修改操作，delete、insert、update这3个操作会为数据行新增一个版本快照）。

接下来先把4个事务隔离级别的基本概念说清楚（等级从小到大依次排序）：

1. Read uncommitted（读未提交）：避免了更新丢失，却可能出现脏读。（表现：读写并行）
2. Read committed（读提交）：避免了脏读，但是可能出现不可重复度。（表现：MVCC、读写分离）
3. Repeatable read（重复读）：避免了不可重复读和脏读，但是有可能出现幻读。（默认的隔离级别，表现：读写锁、MVCC）
4. Serializable（序列化，也叫串行化）：事务只能一个接着一个的执行，不能并发执行，可以解决幻读问题。

其次，我觉得还得把上面提到的几个专业名词解释下：

1. 脏读

1.A进行了一条数据操作，但是没有提交事务，如果此时B进行这条数据的查询，是可以查到A的数据操作结果的。
2.后来A还没有提交事务，反而不提交了或者进行了事务回滚，那么B查询到的数据就是脏数据。

2. 不可重复读（侧重于修改）

事务A多次读取到同一个数据，而B在A多次读取的过程中，对数据进行了修改，导致事务A多次多去同一个数据的时候，结果不一致。

3. 幻读（侧重于增加或者删除）

再一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多或者少的那一行数据叫做幻行。

（4）Next-Key Locks

MVCC不能解决幻读的问题，而Next-Key Locks就是为了解决这个问题而存在的。在可重复读级别下，使用MVCC+Next-Key Locks可以解决幻读问题。

一：Record Locks：
锁定一个记录上的索引，而不是记录本身，如果表没有设置索引，由于InnoDB会自动加一个隐藏的主键，因此Record Locks依然可以使用。
二：Gap Locks：
锁定索引之间的间隙，但是不包含索引本身。

举个例子：
select classId from user between 20 and 30 FOR UPDATE;
那么当一个事务执行上述sql的时候，其他事务就不能再user.classId中插入20-30之间的数据。

而Next-Key Locks是上述两种锁的一个结合，不仅锁定一个记录上的索引，也锁定索引之间的缝隙。他锁定一个前开后闭的区间，例如一个索引包含以下值：10，11，13，20，那么就会锁定这么几个区间：

1. (-∞，10]
2. (10,11]
3. (11,13]
4. (13,20]
5. (20,+∞)

Next-Key Locks就先讲到这里。（🤣🤣再讲下去怕自己献丑）

（5）InnoDB的4大特性

强调下，这里说的是InnoDB索引的4大特性，而不是说事务的4大特性-ACID，大家不要混淆啦。

1. 插入缓冲（insert buffer）：对于非聚簇类索引的插入和更新操作，如果该索引页在缓存中，那么直接插入，先插入到缓冲区中，再以一定的频率和索引页合并。
2. 二次写（double write）：写数据前，将数据线写入一块独立的物理文件位置（ibdata）然后再写道数据页中。
3. 自定义哈希索引（ahi）：自定义哈希索引即将字典类型的索引通过哈希函数映射于一张表，让查询的时候更加迅速。
4. 预读（read ahead）：InnoDB在IO的优化上做出了预读机制，就是发起一个IO请求，异步地在缓冲池中预先回迁若干页面，预计把可能用到的数据页返回。

这一部分其实还有更详细的展开，将在后文进行赘述。（不然总感觉，重点都仍前面了，前面太胖，后面太瘦！丑！🙄🙄）接下来就讲一下MyISAM引擎先缓缓。

1.3.2 MyISAM引擎（了解）

和InnoDB引擎相比，MyISAM不支持事务、表锁设计，但是支持全文索引。MyISAM存储引擎的另一个与众不同的地方是他的缓冲池只缓存索引文件而不缓存数据文件。

另外，还记得上文我提到了，InnoDB的最大存储限度有一个64TB吗，Mysql5以上的时候，如果用的是MyISAM引擎，那么支持256TB的单表数据。

MyISAM和InnoDB的区别

比较内容	MyISAM	InnoDB
构成上的区别	每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。	基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB
是否支持事务	不支持	支持
支持的锁	行锁	表锁
是否有MVCC	不支持	支持
是否支持外键	不支持	支持
是否支持全文索引	支持	不支持
操作的速度	建议如果执行大量的select语句，使用MyISAM	如果数据执行大量的insert和update操作，出于性能考虑，使用InnoDB表
表的具体行数	MyISAM会保存好表中的行数，因此对于count操作很快	InnoDB则没有，需要扫描一遍全表计算

此外，还可以通过命令SHOW ENGINES来查看各个引擎：

二.InnoDB存储引擎

这里会对InnoDB做一个较为全面的阐述。

2.1 InnoDB体系架构

先来给大家看一下InnoDB存储引擎的体系架构图是咋样的：

可以看出，InnoDB有多个内存块，而这些内存块组成一个大的内存池，负责很多工作：

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

那么图中的后台线程是干啥的？
首先我们应该意识到一点：InnoDB存储引擎是一个多线程的模型，因此有多个不同的后台线程也是正常的。

后台线程包括：

（1）Master Thread

作为一个非常核心的后台线程，主要负责：

• 将缓冲池的数据异步刷新到磁盘。
• 保证数据的一致性：包括脏页的刷新、合并插入缓冲、Undo页的回收。

InnoDB 1.0.x版本前的Master Thread
Master Thread内部由多个循环loop组成，并且在多个循环状态之间切换。

• 主循环（loop）
• 后台循环（background loop）
• 刷新循环（flush loop）
• 暂停循环（suspend loop）

大部分操作在主循环中，分为每秒的操作和每十秒的操作。
每秒的操作包括：

1.日志缓冲刷新到磁盘，即使这个事务还没有提交。（总是）
2.合并插入缓冲。（可能）
3.最多刷新100个InnoDB的缓冲池中的脏页到磁盘。（可能）
4.如果当前没有用户活动，则切换到background loop后台循环。（可能）
-------background loop执行的操作：
-------1.删除无用的Undo页。（总是）
-------2.合并20个插入缓冲。（总是）
-------3.跳回到主循环。（总是）
-------4.不断刷新100个页直到符合条件。（可能）

每十秒的操作包括：

1.刷新100个脏页到磁盘。（可能）
2.合并最多5个插入缓冲（总是）
3.将日志缓冲刷新到磁盘。（总是）
4.删除无用的Undo页。（总是）
5.刷新100个或者10个脏页到磁盘。（总是）

InnoDB 1.2.x版本前的Master Thread
改变：

1. innodb_max_dirty_pages_pct的值由90改变到75.(缓冲池中的脏页达到这个百分比，则执行Checkpoint)
2. 引入参数innodb_purge_batch_size，控制每次进行full purge操作时，回收的Undo页的个数。

full purge：为了解决数据Page和Undo Log膨胀的问题，引入purge机制进行回收。

InnoDB 1.2.x版本的Master Thread
对于刷新脏页的操作，从Master Thread线程分离到一个单独的Page Cleaner Thread线程， 从而减轻了Master Thread的工作。

（2）IO Thread

InnoDB存储引擎中大量使用了AIO来处理写IO请求，以便提高数据库的性能，而IO Thread的工作就是负责这些IO请求的回调处理。

这里列举比较重要的几种IO线程：

• write IO Thread（默认4个）
• read IO Thread（默认4个）
• insert buffer IO Thread
• log IO Thread

查看线程的IO个数：

SHOW VARIABLES LIKE 'innodb_%io_threads'

结果：

还可以使用命令来观察InnoDB中的IO Thread(输出结果很长，建议复制然后粘贴到文本里面进行观看)

SHOW ENGINE INNODB STATUS

我这里先把所有的信息给打印出来（有点长，这里直接跳过，看后续短的，下文的部分内容直接从该部分筛选出）

=====================================
2020-12-08 16:52:21 0x70000b528000 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 28 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6 srv_active, 0 srv_shutdown, 4241 srv_idle
srv_master_thread log flush and writes: 4247
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4
OS WAIT ARRAY INFO: signal count 4
RW-shared spins 0, rounds 7, OS waits 3
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 7.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 14086
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422153056951872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422153056950968, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422153056950064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (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
377 OS file reads, 95 OS file writes, 15 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
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 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 4732508
Log flushed up to   4732508
Pages flushed up to 4732508
Last checkpoint at  4732499
0 pending log flushes, 0 pending chkp writes
15 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 109350
Buffer pool size   8191
Free buffers       7785
Database pages     406
Old database pages 0
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 349, created 57, written 72
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: 406, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=99430, Main thread ID=123145486880768, state: sleeping
Number of rows inserted 109, updated 0, deleted 0, read 134
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

接下来挑出本环节的重点信息（看注释）：

FILE I/O
--------
# 第一个线程是insert buffer thread
I/O thread 0 state: waiting for i/o request (insert buffer thread)
# 第二个线程是log thread
I/O thread 1 state: waiting for i/o request (log thread)
# 后续4个读4个写线程，跟文章内容相呼应
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)

（3）Purge Thread

事务被提交后，其所用的undolog可能不再需要，那么则需要一个线程去回收已经使用并分配的undo页。而做这份工作的线程就叫做Purge Thread。

SHOW VARIABLES LIKE 'innodb_purge_threads'

可见，也是默认4个

（4）Page Cleaner Thread

Page Cleaner Thread的作用是将之前版本中脏页的刷新操作都放入到单独的线程中去完成。 其目的：减轻原Mster Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB的性能。

2.2 InnoDB内存

先给大家看一下其内存构造：

2.2.1 缓冲池

InnoDB存储引擎是基于磁盘存储的，并且将其中的记录按照页的方式进行管理。 ，而基于磁盘的数据库系统通常需要使用缓冲池技术来提高数据库的整体性能。

作用：

1. 对于数据库中的读取页的操作：首先将磁盘读取到的页放到缓冲池，那么下一次读取的时候，先去看缓存中有木有，有则直接命中返回，无则去磁盘上读取。
2. 对于数据库中的修改页的操作：首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。（通过一种Checkpoint的机制刷新到磁盘，下文会讲到）

缓冲池作为内存中最大的一块，也包含了很多数据页类型：索引页、数据页、undo页、插入缓冲、自定义哈希索引、锁信息、数据字典信息等。同时InnoDB存储引擎还允许有多个缓冲池实例。

多个缓冲池实例有啥用？每个页根据哈希值平均分配到不同缓冲池实例中，减少数据库内部的资源竞争，增加数据库的并发处理能力，而我们InnoDB存储引擎默认的实例个数是1个，当然也可以进行更改。

SHOW VARIABLES LIKE 'innodb_buffer_pool_instances'

只要将值设置为大于1的数字就可以得到多个缓冲池实例

2.2.2 InnoDB的LRU算法☆

缓冲池存放了各种类型的页，而InnoDB存储引擎则使用了一种经过优化的LRU算法来堆缓冲池进行管理，即在LRU的基础上增加了一个midpoint的位置，新读取到的页并不会直接放入到LRU列表的首部，而是放到LRU列表的midpoint位置。默认情况下，这个位置在LRU列表长度的5/8处。

SHOW VARIABLES LIKE 'innodb_old_blocks_pct'

结果：意思是37%，也就是接近3/8的位置，即把新读取的页插入到LRU列表尾端的37%位置。InnoDB中，把midpoint之后的列表叫做old列表，之前的列表叫做new列表。（其中的页都是热点数据）

问题1：为什么不采用普通的LRU算法，直接将读取的页放入到LRU列表的首部呢？

回答：
如果直接吧读取到的页放入到LRU列表的首部，那么某些SQL操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。

问题2：那么所谓的某些操作是啥？为啥会影响呢？

回答：
1.如索引或者数据的扫描操作。
2.比如全表扫描，需要访问到表中的全部页，问题是缓冲池的大小是有限的呀，那么我这次全表扫描，如果全部放入到LRU列表的首部，那么非常可能会将比较重要的热点数据页从LRU列表中移出，当下一次需要读取热点数据的时候，又得去访问磁盘。
3.此外，InnoDB还引入一个时间参数innodb_old_blocks_time来保证：页读取到mid位置后需要等待多久会被加入到LRU列表的热端（new部分），以尽可能的让LRU列表中热点数据不被移出。

还记得我上文放了一个很长的InnoDB状态信息吗？（命令是SHOW ENGINE INNODB STATUS,这个命令还挺有用的，可以看到引擎的状态信息）这里再把其中的部分信息拉出来（注释很重要）：

# 当前共有8191个页，而每个页的数据大小默认为16k，那么这里加起来就是8191*16k。即128MB
Buffer pool size   8191
# Free buffers代表当前Free列表中页的数量
Free buffers       7785
# 代表LRU列表中页的数量，一般来说，LRU列表页数量+Free列表数量≈缓冲池页数量
# 为什么是约等于？因为缓冲池中的页还可能被分配给自定义哈希索引呀、Lock信息呀等等
Database pages     406
Pending writes: LRU 0, flush list 0, single page 0
# Pages made young代表LRU列表中页移动到前端的次数，下面一行是他的每秒操作次数
# 因为服务器在运行阶段没有改变innodb_old_blocks_time值，因此not young为0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
# LRU列表中共有406个页，而unzip_LRU列表中有0个页，注意：前者包含后者
LRU len: 406, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

上文提到了一个unzip_LRU，InnoDB引擎在1.0.x版本就开始支持页的压缩功能了。原本页的大小是16K，现在可以压缩为1、2、4、8KB，而这些非16KB的页，通过unzip_LRU列表来管理。

unzip_LRU列表对不同压缩页大小的页进行分别管理，例如需要从缓冲池中申请页为4KB的大小，过程如下：

1. 检查4KB的unzip_LRU列表,检查是否有可用的空闲页。
2. 如果有，直接使用。
3. 如果没有，检查8KB的unzip_LRU列表。
4. 如果能够得到空闲页，将页分成2个4KB页，存放到4KB的unzip_LRU列表。
5. 如果不能得到空闲页，从LRU列表中申请一个16KB的页，将页分为1个8KB的页、2个4KB的页，分别存放到对应的unzip_LRU列表中。

在LRU列表中的页如果被修改了，那么这种页叫做脏页。 即缓冲池中的页he磁盘上的页的数据产生了不一致，而这个时候数据库会通过Checkpoint机制将脏页刷新到磁盘，而Flush列表中的页即为脏页列表。

注意：

• 脏页既存在于LRU列表，也存在与Flush列表中。
• LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新到磁盘，两者是独立的互相不影响的。

当然，Flush列表也可以通过”万能“的SHOW ENGINE INNODB STATUS来查看：

2.2.3 重做日志缓冲

InnoDB存储引擎的内存区域除了有缓冲池外，还有重做日志缓冲（redo log buffer）。

作用：
InnoDB存储引擎首先将重做日志信息放到该缓冲区中，然后按照一定的频率将其刷新到重做日志文件中，默认重做日志缓冲的大小为8MB，参数由innodb_log_buffer_size控制

缓冲刷新至文件的3种时机：

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

2.2.4 额外的内存池

在InnoDB存储引擎中，堆内存的管理是通过一种称为内存堆的方式进行的。在对一些数据结构本身的内存进行分配的时候，需要从额外的内存池中进行申请，当该区域的内存不够的时候，才会从缓冲池进行申请。

2.3 Checkpoint

为了避免数据库发生数据丢失，当前事务数据库系统普遍采用了一种策略叫做：Write Ahead Log策略。 即事务提交的时候，先写重做日志，再修改页。而发生宕机而导致数据丢失的时候，就可以通过重做日志来完成数据的恢复。

Checkpoint技术的目的就是解决以下几个问题：

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

重做日志出现不可用的原因？
回答：
因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让日志无限增大，重做日志可以被重用的部分是指这些日志已经不再需要了，那么这部分就可以被覆盖。但是万一覆盖之后，之前的部分又需要使用了，那么必须强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

此外，当数据库发生宕机需要恢复的时候，不需要重做所有的日志，因为Checkpoint之前的页已经能保证刷新到磁盘中了，所以数据库只需要对之后的重做日志进行恢复即可，这样大大缩短了恢复的时间。并且，当缓冲池不够用的时候，根据LRU算法会把最近最少使用的页给移除，如果该页为脏页，那么需要强制执行Checkpoint，将脏页刷回磁盘。

InnoDB存储引擎内部有两种Checkpoint：

• Sharp Checkpoint：发生在数据库关闭时，将所有的脏页刷新到磁盘，默认工作方式。，通过参数innodb_fast_shutdown=1实现
• Fuzzy Checkpoint：只刷新一部分脏页。

这里举出4种Fuzzy Checkpoint的情况：

• Master Thread Checkpoint

以每秒或者每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回到磁盘。

• FLUSH_LRU_LIST Checkpoint

因为InnoDB引擎需要保证LRU列表中需要有差不多100个空闲页可供使用，所以如果没有足够的空闲页，那么InnoDB引擎会将LRU列表尾端的页移除，那如果有脏页，则进行Checkpoint。 通过参数innodb_lru_scan_depth指定，默认1024

SHOW VARIABLES LIKE 'innodb_lru_scan_depth'

结果：

• Async/Sync Flush Checkpoint

指的是重做日志文件不可用的情况，这时候需要强制将一些页刷新到磁盘，为了保证重做日志的循环使用。

• Dirty Page too much Checkpoint

当脏页的数量太多，导致InnoDB存储引擎强制进行Checkpoint，目的是为了保证缓冲池中有足够的可用的页。通过参数innodb_max_dirty_pages_pct控制

默认大小75%，即缓冲池中脏页数量占据75%时候，强制进行Checkpoint，刷新一部分的脏页到磁盘。

三.InnoDB关键特性

3.1 插入缓冲☆

3.1.1 Insert Buffer

Insert Buffer和数据页一样，也是物理页的一个组成部分。虽然上文当中提到了插入缓冲，但是实际上，我自己也很难理解这是个啥玩意😅😅，因此，我准备把书上的这段背景给搬下来，根据我的理解来进行修饰，让大家方便去理解什么是Insert Buffer。

先来给大家复习复习聚簇和非聚簇的概念哈~：

• 聚簇索引：数据行的物理顺序和列值的逻辑顺序相同，一张表只能有一个聚簇索引。
• 非聚簇索引：索引的逻辑顺序和磁盘上行的物理存储顺序不同，一个表中可以有多个非聚簇索引。

既然都说了人家的概念了，不把人家的区别说下咋行：

1. 聚簇索引：其叶子节点就是数据节点，理解为主键，唯一。
2. 非聚簇索引：其叶子节点是索引文件（列+页号+主键），要想返回数据可能需要经过回表查询，可以有多个。

回归正传，讲一下Insert Buffer的背景~

背景：
在InnoDB存储引擎中，主键是行唯一的标识符大家都知道，通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。 也因此，插入聚簇索引一般是顺序的，不需要磁盘的随机读取。
问题来了：一张表一般除了一个主键，还有多个非聚簇索引，我这里假设一张表中的聚簇索引为a，非聚簇索引为b，如：

CREATE TABLE t(
	a int AUTO_INCREMENT,
	b VARCHAR(30),
	PRIMARY KEY(a),
	KEY(b)
);

那么这张表在进行插入操作的时候，页的存放还是按照主键a进行顺序存放的（重点！重点！😑😑），但是！对于非聚簇索引b的叶子节点的插入不再是顺序的了，这时候需要离散的访问非聚簇索引页，即随机读取。（B+树的特性决定了非聚簇索引插入的离散型）

那么，InnoDB存储引擎开创了Insert Buffer的目的是啥呢？

对于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚簇索引页是否在缓冲池中。
如果在——>直接插入。
如果不在——>先放入到Insert Buffer对象中，然后再以一定的频率将Insert Buffer和辅助索引页子节点进行合并操作。
那么这时候能够将多个插入合并到一个操作中，就大大提高了对于非聚簇索引插入的性能。

那么以我的理解：

1. 把Insert Buffer看做是一个大容器，把每次非聚簇索引的插入当做一次任务。
2. 因为非聚簇索引的插入具有离散型，那么如果把多个非聚簇索引的插入绑定在一块，形成一个大的插入事件，提高插入性能。

现在想想，其实Insert Buffer也就是这样，没啥大不了的🤣（当然这是个非常厉害的东西），现在讲一下使用Insert Buffer需要满足的条件，有俩：

1. 索引是辅助索引（非聚簇）。
2. 索引不是唯一的（unique）。

这里，万能的SHOW ENGINE INNODB STATUS又来了，他同样可以查看插入缓冲的信息：

# seg size代表Insert Buffer的大小为 2*16KB
# free list len代表空闲列表的长度
# size代表已经合并记录页的数量
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.1.2 Change Buffer

前面的Insert Buffer可以说是针对非聚簇索引的插入操作，那么自从InnoDB1.0.x版本后，引入了Insert Buffer的升级版：Change Buffer，可以对DML操作（insert，update，delete）都进行缓冲，这里分别对应了Insert Buffer、Purge Buffer、Delete Buffer，使用参数innodb_change_buffering来开启各种Buffer选项。（默认是all，即全开启）

值得注意的是，因为Change Buffer是升级版，所以它适用的对象依然是非唯一的辅助索引。
比如对一条记录进行update操作可以分为2个过程：

1. 将记录标记为已删除。（对应Delete Buffer）
2. 真正将记录删除。（对应Purge Buffer）

3.1.3 Insert Buffer的内部实现原理☆

Insert Buffer的数据结构是一颗B+树。 目前版本全局只有一颗Insert Buffer B+树，负责对所有的表的辅助索引进行Insert Buffer。这颗B+树存放在共享表空间中，默认是ibdata1中，其非叶子节点存放的是查询的search key（键值）。

search key的结构：

1. space（4字节）：待插入记录所在表的表空间id（唯一），可以通过该id得知哪张表。
2. marker（1字节）：兼容老版本的Insert Buffer。
3. offset（4字节）：页所在的偏移量

插入原理：
当一个辅助索引需要插入到页中的时候，如果这个也不在缓冲池中，那么InnoDB存储引擎首先会根据上述的数据结构构造一个search key，接下来查询Insert Buffer这颗B+树，然后江浙条记录插入到其叶子节点中。

插入后的结构：
如图：Insert Buffer叶子节点中的记录，相比之前，多了一个metadata

其中metadata的存储内容包括（4字节）：

其中IBUF_REC_OFFSET_COUNT用来排序每个记录进入Insert Buffer的顺序。 并且这里大家可以看出，Insert Buffer的B+树存储叶子节点，需要额外的13字节的开销（9字节的search key和4字节的metadata），后续的列表就是记录的实际字段了。

3.1.4 Merge Insert Buffer

上一小节，主要讲了什么我们需要理清楚，是插入缓冲的时候，如果插入记录的辅助索引页不在缓冲池中，会发生的过程，即记录插入到Insert Buffer B+树中。那么什么时候把Insert Buffer的记录合并到真正的辅助索引中呢？ 这个问题将在这一小节解释。

直接总的来说，Merge Insert Buffer的时机有这么3中：

1. 辅助索引页被读取到缓冲池。

比如执行select操作，这时候需要先检查Insert Buffer Bitmap页，确认该辅助索引页是否有记录存放在Insert Buffer B+树中，如果有，则把树中的记录插入到辅助索引页中。
注意，注意：大家可以理解为，Insert Buffer B+树只是一个中间件，缓存记录的地方，而辅助索引页是辅助索引记录的最终归宿。

2. Insert Buffer Bitmap页追踪到该辅助索引页已经没有可用空间的时候。

若插入辅助索引记录时检测到插入记录后辅助索引页的可用空间小于1/32，那么这个时候会强制进行Merge Insert Buffer。

3. Master Thread。

每秒或者每十秒会进行一次Merge Insert Buffer操作。

上文多次提到了Insert Buffer Bitmap，这里来稍微解释下是个啥东西：
为了保证每次Merge Insert Buffer页必须成功，需要一个特殊的页来标记每个辅助索引页的可用空间，而这个也的类型为Insert Buffer Bitmap。

其结构：

3.2 二次写

Insert Buffer带给InnoDB存储引擎的是性能上的提升，而二次写（doublewrite）带给他的是数据页的可靠性。

再讲二次写之前，先给解释两个专有名词：写失效

当发生数据库宕机的时候，可能InnoDB存储引擎正在写入某个页到表中，而这个页中只写了一部分，比如一共16KB的页，只写了前4KB，之后发生了宕机，那么这种情况称之为写失效。

虽然发生写失效的时候，可以通过重做日志来进行恢复，但是重做日志中记录的是对页的物理操作，如果这个页本身发生了损坏，那么重做是没有意义的。 因此，我们需要在重做日志前，用户需要一个页的副本，当写入失效发生的时候，先通过页的副本来还原，再进行重做，这就是二次写。

doublewrite的结构如下，由两个部分组成：

• 一部分是内存中的doublewrite buffer，大小为2MB。
• 一部分是物理磁盘上共享表空间中连续的128个页，大小为2MB。

工作原理：

1. 在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存当中的doublewrite buffer。
2. 之后通过doublewrite buffer分两次操作，每次1MB，顺序的写入共享表空间的物理磁盘上。
3. 调用fsync函数，同步磁盘，避免缓冲写带来的问题。
4. 完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间文件中。

问题：如果操作系统在将页写入磁盘的过程中发生了崩溃，怎么办？

回答：
恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志进行恢复。

3.3 自适应哈希索引

首先大家应该知道一点，哈希是一种非常快的查找方法，一般时间复杂度为O（1），B+树的查找次数，取决于B+树的高度。InnoDB会监控表上各个索引页的查询，如果说观察到建立哈希索引可以带来速度提升，那么会建立一个哈希索引，也就是自适应哈希索引（Adaptive Hash Index ，AHI）。

AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，InnoDB会自动根据访问的频率和模式来自动的为某一些热点页建立哈希索引，并且默认AHI功能默认开启。

当然，AHI的相关信息我们也是能查看的，这里又要把万能语句搬上来了🤣🤣

SHOW ENGINE INNODB STATUS

部分内容：

Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
# hash searches/s代表AHI的使用情况，即每秒使用AHI搜索的情况
# non-hash searches/s当然就是不能使用哈希索引的情况
0.00 hash searches/s, 0.00 non-hash searches/s

3.4 异步IO和刷新邻接页

首先说下异步IO：
很简单，就是用户发起一个IO请求后立即在发送一个IO请求，当所有IO请求发送完毕后，等待所有IO操作的完成，核心是无需等待第一个IO请求的返回结果。

AIO的另一个优势就是可以进行IO Merge操作，也就是将多个IO操作合并为1个IO，这样可以提高IOPS（可以视为是每秒的读写次数）的性能。这里举一个书中的例子：

例如用户需要访问页的（space，page_no）为：(8,6)，(8,7)，(8,8)
那么每个页的大小为16KB，那么同步IO需要进行3次IO操作，而AIO会判断到这3个页是连续的，因此AIO底层会发送一个IO请求，从（8，6）开始，一次性读取48KB的页。

再来说下刷新邻接页：
InnoDB存储引擎提供了刷新邻接页（Flush Neighbor Page）的特性，其工作原理为：

1. 当刷新一个脏页的时候，InnoDB存储引擎会检测到该页所在区的所有页。
2. 如果是脏页，那么一个区的所有页一起进行刷新。

通过AIO可以将多个IO写入操作合并为一个IO操作，那么该工作机制在传统的机械硬盘下肯定是有显著的优势，但是也产生了俩问题：

• 如果将不怎么脏的页进行了写入，但是之后页又很快变成了脏页咋办？
• 固态硬盘有着较高的IOPS（我猜大家的游戏都是安装在固态的吧😆😆），是否还需要这个特性？

不过对于这个问题，InnoDB提供了参数innodb_flush_neighbors，用来控制是否启用该特性（对于固态硬盘有着较高IOPS性能的磁盘，建议将此参数设置为0，关闭该特性）

写到这里，终于写完了（系列文章还会继续更新），自己也学到了很多Mysql的细节知识，也希望大家能够有一个很好地收获和知识梳理。最后，创作不易，给个3连吧哈哈~😏😏（当然写博客的最终目的是为了知识分享和自己的日常复习，不是为了3连）