InnoDB存储引擎（二）InnoDB存储引擎

阅读量：3906 次

发布时间：2019-05-23

本文共 10410 字，大约阅读时间需要 34 分钟。

InnoDB存储引擎体系架构如下：

InnoDB体系架构图

show engine innodb status;

注意：显示的不是当前的状态，而是过去某个时间范围内InnoDB存储引擎的状态。从下面的例子可以发现，Per second averages calculated from the last 23 seconds代表的信息为过去23秒内数据库状态。

mysql> show engine innodb status\G;*************************** 1. row ***************************  Type: InnoDB  Name: Status: =====================================2019-09-11 04:31:08 7f4c7398a700 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 23 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 10 srv_active, 0 srv_shutdown, 227 srv_idlesrv_master_thread log flush and writes: 237----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 12OS WAIT ARRAY INFO: signal count 12Mutex spin waits 0, rounds 0, OS waits 0RW-shared spins 12, rounds 360, OS waits 12RW-excl spins 0, rounds 0, OS waits 0Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl------------TRANSACTIONS------------Trx id counter 3128Purge done for trx's n:o < 3125 undo n:o < 0 state: running but idleHistory list length 41LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 1, OS thread handle 0x7f4c7398a700, query id 89 localhost root initshow engine innodb status---TRANSACTION 3107, not startedMySQL thread id 4, OS thread handle 0x7f4c73906700, query id 84 125.70.76.210 root---TRANSACTION 3127, not startedMySQL thread id 3, OS thread handle 0x7f4c73948700, query id 86 125.70.76.210 root--------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, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 0356 OS file reads, 88 OS file writes, 56 OS fsyncs0.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 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 276707, node heap has 0 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s---LOG---Log sequence number 1886077Log flushed up to   1886077Pages flushed up to 1886077Last checkpoint at  18860770 pending log writes, 0 pending chkp writes29 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 137363456; in additional pool allocated 0Dictionary memory allocated 108374Buffer pool size   8192Free buffers       7854Database pages     338Old database pages 0Modified db pages  0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 328, created 10, written 530.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 338, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDBMain thread process no. 1, id 139966295516928, state: sleepingNumber of rows inserted 0, updated 11, deleted 0, read 670.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================1 row in set (0.00 sec)

2.1 InnoDB体系结构

2.1.1 后台线程

后台线程的主要作用是负责刷新内存池中的数据，保证缓存池中的内存缓存的是最近的数据。

1. Master Thread

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

2. IO Thread

大量使用AIO（Async IO）来处理写IO请求（write、read、insert buffer和IO thread），这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调（call back）处理。

有4种类型的IO Thread：write、read、insert buffer和log IO thread

3. Purge Thread

事务被提交后，其所使用的undo log(回滚日志)可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页。

在 1.1版本之前，purge操作仅在InnoDB的Master Thread中完成。而从1.1开始，purge操作可以独立到单独的线程中进行。

4. Page Cleaner Thread

在InnoDB 1.2.x版本引入。作用是将之前版本中脏页的刷新操作都放到单独的线程中完成，以减轻Master Thread的工作

2.1.2 内存

缓冲池

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的系统通常使用缓存池技术来提供数据库的整体性能。

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

对于数据库中页的修改操作，首先修改在缓冲池中的页，然后再以一定的频率（checkpoint机制）刷新到磁盘上。

缓冲池的大小直接影响着数据库的整体性能。

通过命令show variables like 'innodb_buffer_pool_size可以观察缓冲池的大小。134217728/1024/1024 = 128M

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

缓冲池中缓存的数据页类型有：索引页、数据页、UNDO页、插入缓冲（insert buffer）、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等。

InnoDB内存数据对象

从InnoDB 1.0.x版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。减少数据库内部的资源竞争，增加数据库的并发处理能力。

LRU List、Free List和Flush List

缓冲池是一个很大的内存区域，其中存放各种类型的页，那么如何对这么大的内存区域进行管理呢？

LRU List：管理缓冲池中页的可用性

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

InnoDB中，对传统的LRU算法做了一些优化，在列表中加入了midpoint位置。新读取到的页，虽然是最新访问到的页，但并不直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置（默认在LRU列表长度的5/8处）。

LRU List

为什么不使用传统的LRU算法？因为有些操作仅仅是在这次操作中需要，并不是活跃数据，就会导致LRU列表中热点数据被刷出，从而影响缓冲池的效率。

为解决这个问题，引入了另外一个参数来进一步管理LRU列表：innodb_old_blocks_time，用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。

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

Free List

当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入LRU列表中。否则，根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。

Free List

Flush List：管理将页刷新回磁盘

在LRU列表中的页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而Flush列表中的页即为脏页列表。

脏页既存在LRU列表中，也存在于Flush列表中，二者互不影响。

Flush List

重做日志缓冲

InnoDB存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。

该值可由参数innodb_log_buffer_size控制，默认为8MB，一般不需要设置的很大。8388608/1024/1024 = 8M

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

刷新时机（三种）

1. Matster Thread每一秒将重做日志缓冲刷新到重做日志文件

2. 每个事务提交时会将重做日志缓冲刷新到重做日志文件

3. 当重做日志缓冲池剩余空间小于1/2时，重组日志缓冲刷新到重做日志文件

额外的内存池

在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆(heap)的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。例如，分配了缓冲池( innodb_buffer_pool)，但是每个缓冲池中的帧缓冲(frame buffer)还有对应的缓冲控制对象(buffer control block)，这些对象记录了一些诸如LRU、锁、等待等信息，而这个对象的内存需要从额外内存池中申请。

2.2 Checkpoint技术

问题描述

页的操作首先都是在缓冲池中完成的。如果一条DML语句，如Update或Delete改变了页中的记录，那么此时页是脏的，即缓冲池中的页的版本要比磁盘的新。数据库需要将新版本的页从缓冲池刷新到磁盘。

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

Checkpoint技术

Checkpoint（检查点）技术的目的是解决以下几个问题

1. 缩短数据库的恢复时间

2. 当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复。

3. 缓冲池不够用时，将脏页刷新到磁盘

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

5. 重做日志不可用时，刷新脏页

对重做日志的设计都是循环使用的，并不是让其无限增大。数据库发生宕机时，若此时重做日志还需要使用，那么必须产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

通过LSN（Log Sequence Number）来标记版本

触发时机

1. Sharp Checkpoint

数据库关闭时，将所有的脏页都刷新回磁盘，默认工作方式。

2. Fuzzy Checkpoint

数据库运行时，使用Fuzzy Checkpoint只刷新一部分脏页，而不是刷新所有的脏页回磁盘。

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

1. Master Thread Checkpoint

Master Thread差不多每十秒刷新一定比例的脏页回磁盘

2. FLUSH_LRU_LIST Checkpoint

InnoDB需要保证LRU列表中有差不多100个空闲页(innodb_lru_scan_depth=1024)可供使用，如果没有则将LRU列表尾端的页移除，若这些页中有脏页，则需要进行Checkpoint

3. Async/Sync Flush Checkpoint

发生在重做日志文件不可用(日志不能被覆写)的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从Flush_List中选取的

4. Dirty Page too much Checkpoint

脏页的数量太多，导致InnoDB存储引擎强制进行Checkpoint；通过参数innodb_max_dirty_pages_pct控制，默认值为75，当缓冲池中脏页的数量占据75%时，强制进行Checkpoint。

2.3 Master Thread工作方式

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

Loop被称为主循环，因为大多数操作都在这个循环中，其中有两大部分的操作——每秒操作和每10秒的操作。

每秒操作

1. 日志缓冲刷新到磁盘，即使这个事务还没有提交(总是);

2. 合并插人缓冲(可能);

3. 至多刷新100个InnoDB的缓冲池中的脏页到磁盘(可能);

4. 如果当前没有用户活动，则切换到background loop (可能)。

每10秒操作

1. 刷新100个脏页到磁盘(可能的情况下);

2. 合并至多5个插入缓冲(总是);

3. 将日志缓冲刷新到磁盘(总是);

4. 删除无用的Undo页(总是);

5. 刷新100个或者10个脏页到磁盘(总是)。

循环切换：Master Thread -> background loop（可能会跳转到 flush loop） -> flush loop -> suspend loop，将Master Thread挂起，等待事件的发生

2.4 InnoDB关键特性

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

1. 插入缓冲（Insert Buffer）

2. 两次写（Double write）

3. 自适应哈希索引（Adaptive Hash Index）

4. 异步IO（Async IO）

5. 刷新邻接页（Flush Neighbor Page）

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

2.4.1 插入缓冲

非聚集索引的插入是离散的，需要随机读取磁盘，由于随机读取的存在而导致插入操作性能下降，引入插入缓冲是为了提高非聚集索引的插入性能。

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

插入缓冲

使用Insert Buffer需要满足以下两个条件：

1. 索引是辅助索引

2. 索引不是唯一的

在InnoDB 1.0.x后对于DML的操作也可以进行缓冲，它们分别是：Insert Buffer、Delete Buffer、Purge Buffer。

数据结构实现：全局只有一颗Insert Buffer B+树，负责对所有的表的辅助索引进行Insert Buffer。而这颗B+树存放在共享表空间中，默认也就是ibdata1中。

2.4.2 二次写

提高数据页的可靠性

在应用重做日志之前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页（因为原页可能已经损坏，对其重做是没有意义的），再进行重做，这就是double write。 doublewrite架构

2.4.3 自适应哈希索引

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

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

自适应哈希索引有一个要求，即对这个页的连续访问模式必须是一样的。比如联合索引(a, b)，如果交替WHERE a = xx 和 WHERE a = xx and b = xx 是不行的。