MySQL: InnoDB (1) – Exocoetus

# Windows平台通过以下参数增大IO Thread
innodb_file_io_threads (1.0前)

innodb_read_io_threads (1.0.x)
innodb_write_io_threads (1.0.x)

# 观察InnoDB状态
SHOW ENGINE INNODB STATUS

Purge Thread

事务被提交后，所用undolog可能不再需要，需要purge thread来回收已经使用并分配的undo页。

// 1.1版本后，purge操作可以独立到单独线程中进行。

// 1.2版本后，InnoDB支持多个purge thread加快undo页回收。

Page Cleaner Thread

// 1.2.x版本引入，将脏页的刷新操作放到线程中单独完成。

内存

缓冲池

类似计算机中寄存器>内存>硬盘的原理。在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

数据库中页的修改操作，首先修改在缓冲池中的页，再以一定频率刷新到硬盘。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过检查点机制刷新回磁盘。

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

# 缓冲池配置参数
innodb_buffer_pool_size

缓冲池内包含：

索引页
数据页
undo页
插入缓冲
自适应哈希索引
锁信息
数据字典信息

// 1.0.x后允许多个缓冲池实例，每个页根据哈希值平均分配到不同缓冲池实例中。

# 配置缓冲池实例参数
innodb_buffer_pool_instances

LRU List、Free List和Flush List

“内容关于InnoDB如何管理缓冲池”

LRU List

InnoDB中，缓冲池中页大小默认为16KB，且对LRU算法进行了优化：

LRU列表中加入了midpoint位置。新读取到的页虽然是最新访问的页，但不是直接放入LRU列表的首部，而是放到midpoint位置。

之所以放到中部位置，是避免（索引、扫描）等操作将缓冲池的热点数据页从LRU列表中移除。

# midpoint位置控制参数（默认5/8）
innodb_old_blocks_pct

# mid位等待多久后被加入LRU列表热端
innodb_old_blocks_time

Free List

数据库刚启动时，LRU列表是空的，这时页存放在Free列表中。

当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表删除，放入LRU。若无，则淘汰LRU末尾的页，将内存空间分配给新的页。当页从LRU列表中的old部分加入new部分时，称此操作为page made young，因innodb_old_blocks_time的设置而导致页没有从old移动到new则成为page not made young。

buffer_pool_hit_rare：命中率，低于95%需观察全表扫描产生的LRU列表污染。

# 可用于观察缓冲池的运行状态的表
INNODB_BUFFER_POOL_STATS

# 可用于观察每个LRU列表中每个页的具体信息的表
INNODB_BUFFER_PAGE_LRU

>SELECT TABLE NAME,SPACE,PAGE_NUMBER,PAGE_TYPE
->FROM INNODB_BUFFER_PAGE_LRU WHERE SPACE = 1;

1.0.x版本后支持页压缩{16KB->[1KB, 2KB, 4KB, 8KB]}

非16KB的页，通过unzip_LRU列表进行管理。若要从缓冲池中申请4KB的页：

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

# 观察unzip_LRU列表中的页
>SELECT TABLE_NAME,SPACE,PAGE_NUMBER,COMPRESSED_SIZE
->FROM INNODB_BUFFER_PAGE_LRU
->WHERE COMPRESSED_SIZE<>0;

Flush List

在LRU列表中页被修改后，称该页为脏页（因为缓存池中的页和磁盘上的页数据不一致）。数据库会通过检查点机制将脏页刷新回磁盘，而Flush列表中的页即为脏页列表。

脏页既存在于LRU列表中，也存在Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

# 查看脏页数量和类型
>SELECT TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE
->FROM INNODB_BUFFER_PAGE_LRU
->WHERE OLDEST_MODIFICATION>0;

TABLE_NAME为NULL表示该页属于系统表空间

重做日志缓冲

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

InnoDB先将重做日志信息放入这个缓冲区，然后按一定频率将其刷新到重做日志文件。用户需要保证每秒产生的事务量在这个缓冲大小之内。

三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中：

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

# 重做日志缓冲大小配置参数（默认8MB）
innodb_log_buffer_size

# 观察重做日志缓冲大小
>SHOW VARIABLES LIKE 'INNODB_LOG_BUFFER_SIZE'\G;

额外的内存池

对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。

记住一点：申请了很大的InnoDB缓冲池时，也应考虑相应地增加这个值。

Checkpoint技术

/* 基础常识：

DML（数据操作语言，增删改查）。

DDL（数据定义语言，对库表定义的操作）。*/

为了解决一些问题：

每次缓冲池的页更新就要同步到磁盘会使得开销增大
同步磁盘的过程出现宕机数据将不可恢复

因此事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。

由于缓冲池和重写日志不能无限扩大，因此引入检查点技术，目的是：

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

｛PS：检查点和游戏中的检查点一样，游戏角色死亡不用从头开始玩，而是从检查点开始。数据库宕机后也不需要从重做日志的头部开始恢复，从最近的检查点后的重做日志进行恢复即可。｝

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

InnoDB是通过LSN（Log Sequence Number）来标记版本的。而LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。

以下为InnoDB的两种Checkpoint：

Sharp Checkpoint（数据库关闭时刷新所有脏页回磁盘）
Fuzzy Checkpoint（数据库关闭时刷新部分脏页回磁盘）
- Master Thread Checkpoint
  - （每秒或每十秒，异步，查询不阻塞）
- FLUSH_LRU_LIST Checkpoint
  - （若无一定数量空闲页，移除LRU表尾页，脏页走检查点）
- Async/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
  - 当checkpoint_age＜async_water_mark时，不需要刷新任何脏页到磁盘
  - 当async_water_mark＜checkpoint_age＜sync_water_mark时触发Async Flush，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age＜async_water_mark
  - 当checkpoint_age＞sync_water_mark时，触发Sync Flush操作，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足checkpoint_age＜async_water_mark
- Dirty Page too much Checkpoint
  - （脏页的数量太多，导致InnoDB存储引擎强制进行Checkpoint）
  - 参数innodb_max_dirty_pages_pct

Master Thread工作方式

InnoDB 1.0.x版本之前

Master Thread在四种循环中切换：

主循环（Loop）
后台循环（Backgroud Loop）
刷新循环（Flush Loop）
暂停循环（Suspend Loop）

主循环

通过Thread Sleep实现（不精确、大负载下延迟）
一秒一次的操作
- 日志缓冲刷新到磁盘（总是）
- 合并插入缓冲（可能，IO压力小时执行）
- 至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能，脏页比例高执行）
- 如果当前没有用户活动，则切换到background loop（可能）
十秒一次的操作
- 刷新100个脏页到磁盘（可能，IO压力小时）
- 合并至多5个插入缓冲（总是）
- 将日志缓冲刷新到磁盘（总是）
- 删除无用的Undo页（Full Purge，总是）
- 刷新100个或者10个脏页到磁盘（总是，超过70%脏页100，小于则10）

后台循环

数据库空闲或关闭时，切换到后台循环。

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

如果Flush Loop也没事可做，InnoDB会切换到suspend_loop，挂起Master Thread挂起，等待事件的发生。

InnoDB1.2.x版本之前

1：修改之前的设计，提出了参数innodb_io_capacity（默认200），表示磁盘IO的吞吐量（之前硬编码固定100和20太呆了）。

对于刷新到磁盘页的数量，会按照innodb_io_capacity的百分比来进行控制：

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

2：innodb_max_dirty_pages_pct默认值修改为75

3：innodb_adaptive_flushing（自适应地刷新），该值影响每秒刷新脏页的数量。原来的刷新规则是：脏页在缓冲池所占的比例小于innodb_max_dirty_pages_pct时，不刷新脏页；大于innodb_max_dirty_pages_pct时，刷新100个脏页。随着innodb_adaptive_flushing参数的引入，InnoDB存储引擎会通过一个名为buf_flush_get_desired_flush_rate的函数来判断需要刷新脏页最合适的数量。

4：innodb_purge_batch_size，控制每次full purge回收的Undo页的数量，默认值为20。

InnoDB 1.2.x版本

把刷新脏页的操作，从Master Thread线程分离到一个单独的Page Cleaner Thread。

InnoDB关键特性

插入缓冲
两次写
自适应哈希索引
异步IO
刷新邻接页

插入缓冲

前置-索引B+树

B+ 树非叶子节点上不存储数据，仅存储键值
B+ 树的阶数等于键值的数量，三层键值为1000的B+树可存储1000^3个数据
B+ 树索引的所有数据均存储在叶子节点，而且数据按照顺序排列

Insert Buffer

插入缓冲和数据页一样，也是物理页的一个组成部分。

InnoDB存储引擎中，主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。

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

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

PS：这里举一个自己理解的例子：

有一百个学生，学号从1到100打乱顺序排成一条长队（非聚集索引），要根据学号的大小分配到多个教室（索引页）｛如1-20号进入A，21-40号进入B，以此类推｝，A教室在当前学校（缓冲池中），而其他教室不在这个学校。即，学生若是分配到A教室可以直接进入，如果分配到其他教室，则先在空闲教室（插入缓冲）等待，等到攒到一队人一起坐车到其他学校的教室。

使用插入缓冲需要满足：

索引是辅助索引
索引不是唯一（unique）的

但由于Insert Buffer并不是直接插入到表中，而是积累一定量再合并插入，所以此时MySQL数据库宕机则需要更长时间去恢复。

索引之所以不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。

# 查看信息
SHOW ENGINE INNODB STATUS\G;

# Insert Buffer的大小
seg size

# 空闲列表长度
free list len

# 已经合并记录页的数量
size

# 插入的记录数
inserts

# 合并的插入记录数量
merged recs

# 实际读取页的次数
merges

目前Insert Buffer存在一个问题是：在写密集的情况下，插入缓冲会占用过多的缓冲池内存。

Insert Buffer的内部实现

一棵B+树（全局），负责对所有的表的辅助索引进行Insert Buffer。

这棵B+树种在共享表空间中（默认ibdata1）。试图通过独立表空间ibd文件恢复表中数据时，往往会导致CHECK TABLE失败，因为表的辅助索引中的数据可能还在Insert Buffer中，通过ibd文件恢复后，还要进行REPAIR TABLE操作来重建表上所有的辅助索引。

B+树由叶节点和非叶节点组成。非叶节点存放查询的键值（9字节），内容包括：

space（插入记录所在表的表空间id）【4字节】
marker（兼容老版本的Insert Buffer）【1字节】
offset（页所在的偏移量）【4字节】

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

space
marker
offset
metadata
1. IBUF_REC_OFFSET_COUNT【2字节】（用来排序每个记录进入Insert Buffer的顺序）
2. IBUF_REC_OFFSET_TYPE【1字节】
3. IBUF_REC_OFFSET_FLAGS【1字节】
(辅助索引记录…)

启用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个页的第二个页中。

Change Buffer

1.0.x版本开始引入
看作Insert Buffer的升级

InnoDB存储引擎可以对DML操作——INSERT、DELETE、UPDATE都进行缓冲，他们分别是：Insert Buffer、Delete Buffer、Purge buffer 。

适用对象：非唯一的辅助索引

UPDATE操作可能分为两个过程：1.标记为删除，2.真正删除

因此，Update Buffer对应UPDATE的第一个过程，仅标记。Purge Buffer对应第二个操作，统一删除。

// InnoDB 1.2.x版本开始，可以通过参数innodb_change_buffer_max_size来控制Change Buffer最大使用内存的数量.（默认25，最大50）

Merge Insert Buffer

Merge Insert Buffer发生的情况：

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

两次写

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

在应用（apply）重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewrite。

刷新缓冲池脏页并不直接对磁盘进行写操作，而是：

通过memcpy将脏页先复制到内存中的doublewrite
doublewrite buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘
即时调用fsync同步磁盘，避免缓冲写的弊端

如果写操作过程中发生了崩溃，可以通过共享表空间中的doublewrite找到该页副本，复制到表空间文件，再应用重做日志。

# 观察doublewrite运行的情况
mysql＞SHOW GLOBAL STATUS LIKE'innodb_dblwr%'\G;

# 变量表示当前从缓冲池中刷新到磁盘页的数量
Innodb_buffer_pool_pages_flushed

# 参数禁止使用doublewrite功能
skip_innodb_doublewrite

自适应哈希索引

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。

InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

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

WHERE a = ?
WHERE a = ? and b = ?

访问模式一样指的是查询的条件一样，若交替进行上述两种查询，那么InonDB存储引擎不会对该页构造AHI。

其他要求：

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

异步IO

非阻塞避免等待
可以进行IO Merge，提高效率

对于页(8 , 6)、(8 , 7)、(8 , 8)的访问，连续读取三次16KB的页可以合并为读取一次48KB页。

刷新邻接页

工作原理：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。

通过AIO可以将多个IO写入操作合并为一个IO操作，故该工作机制在传统机械磁盘下有着显著的优势。

# 关闭参数，传统机械硬盘推荐开启，固态硬盘建议设为0关闭
innodb_flush_neighbors

启动、关闭和恢复

innodb_fast_shutdown

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

0：表示在MySQL数据库关闭时，InnoDB需要完成所有的full purge和merge insert buffer，并且将所有的脏页刷新回磁盘。这需要一些时间，有时甚至需要几个小时来完成。如果在进行InnoDB升级时，必须将这个参数调为0，然后再关闭数据库。
1：默认值，表示不需要完成上述的full purge和merge insert buffer操作，但是在缓冲池中的一些数据脏页还是会刷新回磁盘。
2：不完成full purge和merge insert buffer操作，也不将缓冲池中的数据脏页写回磁盘，而是将日志都写入日志文件。这样不会有任何事务的丢失，但是下次MySQL数据库启动时，会进行恢复操作（recovery）。

innodb_force_recovery

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

1(SRV_FORCE_IGNORE_CORRUPT)：忽略检查到的corrupt页。
2(SRV_FORCE_NO_BACKGROUND)：阻止Master Thread线程的运行，如Master Thread线程需要进行full purge操作，而这会导致crash。
3(SRV_FORCE_NO_TRX_UNDO)：不进行事务的回滚操作。
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操作是不允许的。