MySQL 二.InnoDB存储引擎

MySQL二 InnoDB存储引擎

InnoDB 是使用MySQL数据库处理OLTP应用中核心数据表的首选存储引擎；InnoDB 也是MySQL数据库的核心；

1.1 InnoDB 概述

5.5 版本后默认使用的存储引擎就是InnoDB； 是所有存储引擎里面第一个完整支持ACID事务的存储引擎，行锁设计，支持MVCC ,支持外键，提供一致性非锁定读，设计上有效利用内存和CPU;

很多大型网站在使用MySQL；

InnoDB 是一个高性能，高可用，高可扩展性的存储引擎；

1.2 InnoDB版本

功能支持 和以及对应的版本

Feature	Support
B-tree indexes	Yes
Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.)	Yes
Cluster database support	No
Clustered indexes	Yes
Compressed data	Yes
Data caches	Yes
Encrypted data	Yes (通过加密功能在服务器上实现；在MySQL 5.7及更高版本中，支持静态数据加密.)
Foreign key support	Yes
Full-text search indexes	Yes (MySQL 5.6及更高版本中提供了对全文索引的支持.)
Geospatial data type support	Yes
Geospatial indexing support	Yes (MySQL 5.7及更高版本中提供了对地理空间索引的支持.)
Hash indexes	No (InnoDB内部利用哈希索引实现其自适应哈希索引功能.)
Index caches	Yes
Locking granularity	Row
MVCC	Yes
Replication support (Implemented in the server, rather than in the storage engine.)	Yes
Storage limits	64TB
T-tree indexes	No
Transactions	Yes
Update statistics for data dictionary	Yes

1.3 InnoDB 体系架构

InnoDB buffer pool 内存存储的数据有

• 所有进程/线程需要访问的多个内部数据结构
• 磁盘数据缓存 磁盘数据的修改也是现在这里进行修改
• 日志缓冲 redo.log
• 其他。。。

InnoDB还有许多后台线程负责保证磁盘数据和内存数据的一致性：

• 保证缓冲池数据是最近使用的数据
• 修改的数据刷新到磁盘
• 保证数据库异常是InnoDB恢复到正常状态

1.3.1 后台线程

InnoDB引擎被设计成多线程模型，主要有多个不同的线程，处理不同的任务

1.Master Thread

缓冲池中的数据异步刷新到磁盘，保证数据一致性：脏页的刷新，合并插入缓冲 insert buffer ,undo页的回收

2.IO Thread

MySQL使用AIO （Async）来处理SQL请求 , 对于请求的回调处理则是由IO Thread 线程完成;

0 = insert buffer thread

1= log thread

读线程ID小于写线程ID

show engine `InnoDB` status 

........
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)

3.Purge Thread

事务被提交之后，事务所使用的undo log 可能不在使用了，需要Purge Thread来回收该undo页;可以在MySQL的配置中添加InnoDB_purge_thread 属性值来配置该线程的个数以加快回收undo页;而且Purge Thread是处理离散的undo页，可以更大性能的发挥磁盘的新能

4.Page Cleaner Thread

InnoDB 数据实载InnoDB 1.2.x 版本引入。原来Master Thread线程功能之一的脏页数据刷新到磁盘的工作交给了Page Cleaner Thread;减轻Master Thread 负载

1.3.2 内存 1.缓冲池

缓存内存有两种一种是buffer 理解为写入的缓存，一种是cache 读取的缓存；

MySQL数据库记录的数据是最后写入此磁盘的，由于写入磁盘新能比较低，一般是写写入内存，然后分批次异步刷新到磁盘 （可了解CheckPoint）；

读取数据的时候，会先把数据加载到内存cache ，后面如果再次读取，会优先读缓存里面的数据，加快响应；

现在的版本一个数据库可以对应多个缓冲池实例，数据页可以通过hash来分配到不同的实例

Variable_name	Value
InnoDB_buffer_pool_instances	1

2.LRU List ,Free List ，Flush List

LRU Latest Recent used 最近最少使用算法来管理缓冲池中的页数据，页的大下为16KB,最近最少使用的数据在链表的尾部；当内存不够无法添加新的页数据的时候，会优先剔除尾部数据；

InnoDB在使用LRU 时也做了一部分优化，就是最新的数据也不是放在链表的最头部位置，这个位置大概是在链表的5/8 约等于距离尾部 37%的位置 （midpoint 冷热数据交接点 可以这样理解），这个有一个参数控制，因为有些数据比如全表扫描的数据，是比较大的 如果是放在最前面可能会导致真正被使用的数据被刷出缓冲池

show variables like '%InnoDB_old_blocks_pct%'

Variable_name	Value
InnoDB_old_blocks_pct	37

那么何时数据别刷新到表头位置？有一个参数

show variables like '%InnoDB_old_blocks_time%'

Variable_name	Value
InnoDB_old_blocks_time	1000

当数据页在被插入到mid 后还能存活1000ms并且没有被刷出的时候，会把该数据页数据刷新到LRU List 的表头

当数据库启动时。LRU List 这个时候还是空的 当缓存区需要加页数据的时候，会先加载Free 列表中，然后判断LRU List 是否可以存放数据，如果可以就把Free 里面数据页刷新到LRU List，Free列表中页删除；

在做数据库运维的时候需要注意一个参数 buffer pool hit rate 缓存命中率 这个越高越好 低于95% 是需要注意，避免全表扫描对LRU List 的污染

当缓存页数据被修改的时候，数据页就变成脏页 dirty page ；前面有介绍，脏页数据会通过checkpoint机制刷新到磁盘；在刷新之前 脏页数据会被刷新到Flush List 中 ，需要注意的是脏页在LRU，Flush 列表中会同时存在

3.重做日志缓存

InnoDB除了缓存区的内存之外，还有一个重做日志缓存(redo log buffer ) InnoDB存储引擎首先会把重做日志信息放到这个缓存区 一般不需要把这个区域设置的很大

show variables like '%InnoDB_log_buffer_size%'

Variable_name	Value
InnoDB_log_buffer_size	16777216

现在的版本默认是是16M ,可以满足绝大部分的应用需求，因为重做日志在下面三种情况下会把缓存区刷新到磁盘

1.Master thread 一秒一次

2.事务提交（也有三个配置0 一秒一次 1.提交后刷盘持久化 2.写入OS cache 存在丢失风险 由OS 后续写入磁盘）

3.日志缓存区剩余空间小于1/2 时

4.额外的内存池

当缓冲池不够时，会在在这个额外的内存池申请；比如每个缓冲池里面的帧缓存frame buffer 和缓存控制对象的内存对象需要现在这个额外的内存池申请；当InnoDB缓冲池大小调整的时候，额外的内存池也应该响应的调整；

1.4 CheckPoint

缓冲池是为了协调CPU 和磁盘的速度；数据的增删改都是在缓冲池中完成的，如果是大批量的修改数据，且每一个页发生变化就把新的数据页刷新到磁盘，那这个性能就比较差了，且写入时发生故障，更是无法恢复；所以现在的事务数据库 都采用Write Ahead log 的方式 先写重做日志 在刷新到磁盘；就算是宕机，也可以通过重做日志来恢复；

可以这样理解：redo log 记录的是脏页记录 也就是要更新的数据页:记录的是对页的物理操作，比如某某页偏移量多少修改成什么

假设一下场景：

1.缓冲池足够大 大带数据库数据都可以容纳

2.redo log 也无限大

优点：不用吧数据刷新到磁盘；因为 每次都可以宕机后恢复；

缺点：数据量过大 恢复时间要很长 ；数据库文件可以很大，但是内存不可能很大；难以运维

基于上述CheckPoint 用于解决上面问题

1.缩短恢复时间

2.缓冲池不够时，刷出脏页刷新到磁盘

3.重做日志不够时，刷新脏页

宕机恢复后只需要重做上一个CheckPoint后的日志恢复

接到上面的 第2点缓冲池不够时，溢出最近不用的数据页，若包含脏页 触发CheckPoint ;还有很多条件，比如是CheckPoint 发生的时间，条件 ，脏页大小；

在InnoDB中CheckPoint 也分为两种

• Sharp CheckPoint ：数据库将要关闭的时候触发；刷新所有脏页，会影响数据库，正常运行的时候触发，数据库的可用性会受到很大的影响(全量更新)

• Fuzzy CheckPoint ：更新一定数量的脏页

  Fuzzy CheckPoint也分为好几种

  1. master thread CheckPoint

  定时几秒去刷新脏页列表 flush list 里面的数据页到磁盘

  2. flush lru list CheckPoint

​ lru list 要保证100个数据页可以使用 ，数据页不够的时候把尾部不用的数据溢出，有脏页的话，数据页CheckPoint刷新，数据页都是来自于LRU List

​ 3. Async/sync Flush CheckPoint

现在log 日志位置为log1 上一次CheckPoint log日志为 log2

下次CheckPoint 需要的文件大下是 log3 = log1 - log2

如果设置log file 大小是2G

flag1 = 2G*75% = 1.5G

flag2 = 2G*90% = 1.8G

log3 < flag1 一下的时候 不触发

flag1 < log3 < flag2 触发 Async Flush 异步刷盘 直到满足 log3 < flag1

log3 >flag2 触发 Sync Flush 刷盘足够多的数据页 直到满足 log3 < flag1 也是最后一种情况 dirty page too much CheckPoint

​ 4.dirty page too much CheckPoint

dirty page 占比 一般是90%和75% 会触发的CheckPoint

1.5 Master Thread

InnoDB 的主要工作都是在这个单独的后台线程

1.0.x版本InnoDB

Master Thread具有最高优先级别的后台线程；内部有多个循环状态组成

1. 主loop
2. 后台loop background loop
3. 循环刷新 flush loop
4. 循环暂停 suspend loop

Master Thread 会根据MySQL的运行状态调整 其线程的状态

主loop 中 有个循环周期为10 的循环 10s，所以前面说有些任务是秒级或者是10秒级别 每秒完成的工作为

1. 重做日志缓存刷新到磁盘，即使这个事务还没有提交，这样的好处是即使是非常大的事务commit的时候页可以快速提交；一定
2. InnoDB存储引擎根据前一面的IO 次数 小于5的情况下 会执行合并插入缓存；可能
3. 最大提交100 个脏页数据同步到磁盘 如果是超过了设置的最大值innoDB_max_dirty_pages_pct 就会100个脏页数据做同步；可能
4. 当前没有用户操作的话 转变成background loop

循环10次 的结果是

最多刷新100个脏页到磁盘，合并最多五个插入缓冲，日志缓存刷到磁盘，删除无用的Undo 页

接下来还会做一个判断 IO 次数<200 =>磁盘仍有很强的IO 操作能力>100 个脏页刷新到磁盘；合并最多插入缓冲；日志缓存刷到磁盘 [相当于1s 内的任务在做一次] ；full purge 会有undo 页

做完了切换到 background loop

1. 删除无用undo 一定

2. 合并插入缓冲一定

3. 跳回主循环 可能

4. 刷新100 脏页 直到满足条件 可能在flush loop中完成

如果跳转到 flush loop 页没有事情可以做的话 就会切换到suspend loop 状态这个时候 Master Thread 将会处于挂起的状态，直到事件发生；

如果用户启用了InnoDB，却没有任何的基于InnoDB引擎的表，那么就会处于挂起的状态；

1.1.x版本InnoDB

对于前面的版本 对磁盘IO 的数目都是硬编码；比如100 个脏页，20个插入缓存，但是随着硬件的提升，SSD磁盘的加入，为了提升性能就修改成了配置的方式

1.2.x版本InnoDB

脏页的刷新；不在使用Master Thread 而是交给 Page Cleaner Thread

1.6 InnoDB的关键特性

• 插入缓存 Insert buffer (新版本叫：Change Buffer)
• 两次写 Double Write
• 自适应hash 索引 Adaptive Hash Index
• 异步IO Async IO
• 刷新临近页 Flush Neighbor Page

1.插入缓存 1.Insert buffer

在缓冲池和物理数据页都是有 Insert Buffer;

在InnoDB ,主键是唯一索引，一般情况下插入数据的主键是使用自增的，所以插入的主键聚集索引是顺序的，磁盘是顺序读写(如果主键是UUID ，就和辅助索引一样，随机读写的了，还有就是有的主键是自增的，但是插入是指定的主键，也有可能是随机读写的);就如下

CREATE table table3(
    id  int auto_increment,
    demo_id int primary key ,
    name varchar(50)
)

在插入数据时 字段id == null 的时候，数据库会默认生产一个自增的属性

这个时候如果name 字段变成了辅助索引，数据存储是以B+ 树的方式，这样这个字段的存储方式就是这些辅助索引的插入方式是随机读写的；具有离散性；

为此InnoDB设计了Insert Buffer ,对于非聚集索引的插入更新，会先在缓冲池里面的索引页是否存在，如果存在，直接操作；不存在的话，放到一个Insert Buffer 对象中(数据还没有听不到磁盘 ，看起来是已经完成了)；后续在按照一定的频率对Insert Buffer 中的辅助索引页进行merge到磁盘；这里的merge 一般是对辅助索引页的多个IO操作合并成一次IO操作；

使用 Insert Buffer 需要满足的条件

1. 辅助索引 (secondary index):主键索引是连续的 不需要该特性；
2. 索引不是唯一的 unique :如果是唯一索引，每次插入都要读取索引页来判断唯一性，这就没意思了

注意：如果在Insert Buffer 的辅助索引还没有更新是宕机，会有很多的辅助索引没有更新到磁盘，恢复要很长时间(借助ibdata1 Insert Buffer B+ tree )；

2.Change Buffer

Insert Buffer 升级版 对DML 操作的 insert delete update 都可以进行缓冲

对应buffer：insert buffer delete buffer purge buffer 必须瞒住上面两个条件

3.Insert buffer 实现

Insert Buffer B+ tree 记录所有要IO操作的辅助索引页 ；数存放在共享表空间中 ==》ibdata1 中

非叶子节点 ==>search key ==>（space: 表id 4byte;market 兼容老版本 1byte；offset :页的偏移量，具体的那个物理数据页 4byte ）

流程：当辅助索引页要插入到页 (space ,page_no),如果缓冲池里面的index page 没有，那么InnoDB会构造一个search key ,添加到 Insert Buffer B+ tree

然后再插入一条数据到叶子节点 13byte+ 索引数据

为了保证 Merge insert Buffer 成功 （需要辅助索引页还可以添加数据，需要bitmap 来记录 ）

设计了一个特殊的页(16K；数据类型是bitmap)来标记辅助索引页,每个bitmap可以标记16384个;在bitmap 中 一个辅助索引页记录占用4bit ;

名称	大小 BIT	说明
IBUF_BITMAP_FREE	2	0: 无空间 1：>1/32页 2：>1/16页 3：>1/8页
IBUF_BITMAP_FREEREAD	1	1：该(地址对应)辅助索引页被缓存 在Insert Buffer B+ tree
IBUF_BITMAP_IBUF	1:	1：该页为 Insert Buffer B+ tree 索引页

4.Merge Insert Buffer

Insert/Change Buffer 什么时候 merge 到真正的辅助索引里面去；

情况1.查询使用辅助索引页：根据查询用到的辅助索引页去bitmap查询,判断其中的辅助索引页是否需要更新；有则触发merge

情况2.Insert buffer Bitmap 对应的辅助索引页无空间可用；当可用的空间，1/32 的时候，强制merge

情况3.master Thread: 1s 一次读取一定比例的辅助索引页 merge;随机选中一个insert buffer 页；

2.两次写 doublewrite

提升InnoDB 数据页可靠性

这些脏数据页需要刷新到磁盘上，使修改永久的保存，而double write就产生在将脏数据页刷盘的过程中。刷盘是一份脏数据写到共享表空间，一份写到真正的数据文件永久的保存。写了两次脏数据页，就叫double write

redo log是恢复数据页修改的数据，若果是整个页都出现了问题就不能通过redo log 来处理了

3.自适应哈希

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升；

经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。

降低对二级索引树的频繁访问资源；

缺点还很多；看情况使用；

4.异步IO

提高磁盘新能；当前的数据库系统多采用异步IO 方式 Asynchronous IO，AIO

AIO 之前对应的就是Sync IO

Sync IO 一次请求就一次IO,并且IO是阻塞的,下一个IO 需要等待上一个IO 的结束

AIO ：可以发起连续的IO 操作，并且会 IO merge 比如InnoDB发起三个IO请求（space page_no）

(8,6);(8,7);(8,8) Sync IO 需要进行三次IO ,AIO 判断三个数据页是连续的 ，会发起一个IO 请求；从(8,6)，开始连续读取48KB，3个数据页

1.1.x之前是InnoDB存储引擎模拟AIO;之后则是操作系统内核级别的支持AIO,称为native AIO；linux 和Windows支持；macOS 未提供

AIO,称为native AIO；linux 和Windows支持；macOS 未提供

5.刷新临近页

InnoDB提供了Flush Neighbor page 刷新临近页支持；

当刷新一个脏页时，InnoDB会检测该页所在的区（extent）的所有页，如果也是脏页，那就一起刷新；

这个功能可以配置；

1.7 启动关闭恢复

在关闭是 innodb_fast_shutdown 参数影响表的存储引擎InnoDB的行为；参数为0,1,2 默认1

0：最安全 所有buffer操作完成 脏页数据全部刷盘 耗时长

1：buffer数据不会操作 ，脏页数据全部刷盘

2：buffer数据不会操作 ，脏页数据不处理，日志都会记录；不会有事务丢失 ，数据库下次启动再恢复数据recovery；还有一个参数innodb_force_recovery 1~6: 这里省略了；表示recovery 恢复的级别；