MySql优化（三）详细解读InnoDB存储引擎
写在前面：
- 我热爱技术，热爱分享，热爱生活， 我始终相信：技术是开源的，知识是共享的！
- 博客里面的内容大部分均为原创，是自己日常的学习记录和总结，便于自己在后面的时间里回顾，当然也是希望可以分享自己的知识。目前的内容几乎是基础知识和技术入门，如果你觉得还可以的话不妨关注一下，我们共同进步！
- 个人除了分享博客之外，也喜欢看书，写一点日常杂文和心情分享，如果你感兴趣，也可以关注关注！
- 微信公众号：傲骄鹿先生
目录
InnoDB独立表空间，支持MVCC，行锁设计，提供一致性非锁定读，支持外键，插入缓冲，二次写，自适应哈希索引，预读使用聚集的方式存储数据，每张表的存储都是按主键顺序存放。
InnoDB是事物安全的MySQL存储引擎，设计上采用了类似Oracle数据库的架构。通常来说，InnoDB存储引擎是OLTP应用中核心表的首选存储引擎，也正是因为其存在，才使得MySQL数据库变得更加有魅力。
一、InnoDB体系架构
1、后台进程：
1.1 Master Thread：核心线程，负责缓冲池的数据异步入盘，包括脏页刷新、合并插入缓冲、undo页回收等。
1.2 IO Thread：包括read thread 和writer thread，使用show variables like '%innodb_%io_thread%';查看。
1.3 Purge Thread：回收事务提交后不再需要的undo log，通过show variables like '%innodb_purge_threads%'; 查看。
1.4 Page clear thread：脏页的刷新操作，从master thread分离出来。
2、InnoDB存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池，负责如下的工作：
- 维护所有进程/线程需要使用的多个内部数据结构
- 缓存磁盘上的数据，方便快速地读取，同时对磁盘文件数据修改之前在这里缓存
- 重做日志（redo log）缓存
InnoDB内存池主要有以下部分：
- 缓冲池
- 重做日志缓冲
- 额外内存池
以下主要从内存和线程的角度分析InnoDB的架构。
二、内存
2.1 缓冲池
从上图来看，主要包括数据页、索引页、undo页、insert buffer、adaptive hash index、数据字典等，其中索引页和数据页占用多数内存。
配置innodb_pool_buffer_instances将缓冲池分割为多个实例，减少内部竞争(比如锁)。
InnoDB是基于磁盘存储的，并将其中的记录按照页的方式进行管理。而缓冲池就是一块内存区域，主要缓冲数据页和索引页。
InnoDB中对页的读取操作，首先判断该页是否在缓冲池中，若在，直接读取该页，若不在则从磁盘读取页数据，并存放在缓冲池中。
对页的修改操作，首先修改在缓冲池中的页，再以一定的频率（Checkpoint机制）刷新到磁盘。参数：innodb_buffer_pool_size设置缓冲池大小
缓冲池通过LRU（Latest Recent Used，最近最少实用）算法进行管理。最频繁使用的页在LRU列表前端，最少使用的页在尾端，当缓冲池不能存放新读取的页时，首先释放LRU列表尾端的页（页数据刷新到磁盘，并从缓冲次中删除）。
InnoDB对于新读取的页，不是放到LRU列表最前端，而是放到midpoint位置（默认为5/8处）。这是因为一些SQL操作会访问大量的页（如全表扫描），读取大量非热点数据，如果直接放到首部，可能导致真正的热点数据被移除。
2.2 LRU list、free list、flush list
默认的缓冲页大小是16KB，使用LRU算法进行管理，新从磁盘加载的页默认加到LRU列表的midpoint处（尾端算起37%位置处）。通过show engine innodb status输出如下（部分）：
Buffer pool size 512 【缓冲池内存512*16K】
Free buffers 256
Database pages 256 【LRU列表占用页】
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 255, created 40, written 67
0.16 reads/s, 0.06 creates/s, 0.37 writes/s
Buffer pool hit rate 943 / 1000 【缓冲池命中率大于95%则良好】, young-making rate 0 / 1000 not 0 / 1000
LRU len: 256, unzip_LRU len: 0 【LRU列表中的页可被压缩分为1K/2K/4K/8K之类的页】
LRU列表中的页被修改后变为dirty page，此时缓冲池中的页和磁盘不一致，通过checkpoint刷回磁盘，其中Flush list即为dirty page列表。
2.3 重做日志缓冲
重做日志先放到这个缓冲区，然后按一定频率刷新到重做日志文件。配置参数：innodb_log_buffer_size，默认是8MB，
刷新规则：
- Master Thread每秒将一部分重做日志缓冲刷新到重做日志文件
- 每一事务提交时会将重做日志刷新到重做日志文件（如果配置了）
- 重做日志缓冲区使用空间大于1/2
2.4 额外的内存池
内存堆，对InnoDB内部使用的数据结构对象进行管理。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中申请，当该区域的内存不够时，会从缓冲池中申请。
三、线程
InnoDB存储引擎是多线程的模型，因此后台有多个不同的后台线程，负责处理不同的任务。
主要作用：
- 负责刷新内存池中的数据，保证缓冲池的内存缓冲的是最近的数据
- 已修改的数据文件刷新到磁盘文件
- 保证数据库发生异常的情况下InnoDB能恢复到正常状态。
InnoDB运行时主要有以下线程：
Master Thread
负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新，合并插入缓冲（INSERT BUFFER），UNDO页的回收等。
IO Thread
负责AIO请求的回调处理。
参数：innodb_read_io_threads，innodb_write_io_threads
Purge Thread
事务提交后，undo log可能不再需要，由Purge Thread负责回收并重新分配的这些已经使用的undo页。
注意：Purge Thread需要离散地读取undo页。
Page Cleaner Thread
InnoDB 1.2.x引入，将Master Threader中刷新脏页的工作移至该线程，如上面说的FLUSH LRU LIST Checkpoint以及Async/Sync Flush Checkpoint。
四、checkpoint
当每次执行update、delete等语句更改记录时，缓冲池中的页与磁盘不一致，但是缓冲池的页不能频繁刷新到磁盘中（频率过大性能低），因此增加了write ahead log策略，当事务提交时先写重做日志，再修改内存页。当发生宕机时通过重做日志来恢复。checkpint解决以下问题：
（1）减少重做日志大小，缩减数据恢复时间。
（2）缓冲池不够用时将脏页刷回磁盘。
（3）重做日志不可用时将脏页刷回磁盘（如写满）。
show variables like 'innodb_max_dirty_pages_pct'; (默认75%)来控制inndodb强制进行checkpoint。
若每个重做日志大小为1G，定了了两个总共2G，则：
asyn_water_mark = 75 % * 重做日志总大小。
syn_water_mark = 90 % * 重做日志总大小。
（1）当checkpoint_age < asyn_water_mark时则不需要刷新脏页回盘。
（2）当syn_water_mark < checkpoint_age < syn_water_mark 时触发ASYNC FLUSH。
（3）当checkpoint_age>syn_water_mark触发sync flush，此情况很少发生，一般出现在大量load data或bulk insert时。
五、Master Thread工作方式
Master Thread具有最高的线程优先级别，内部由多个循环组成：主循环（loop），后台循环(backgroup loop)，刷新循环(flush loop)，暂停循环(suspend loop)，Master Thread根据数据库运行状态在以上循环切换。
Master Thread主要流程伪代码如下：
void master_thread() {
goto loop;
// 主循环
loop:
for(int i = 0; i < 10; i++) {
// 每秒一次操作
thread_sleep(1)
// 日志缓冲刷新到磁盘，即使这个事务没提交
do log buffer flush to disk
// 合并插入缓冲（如果前一秒IO次数少于5次，InnoDB认为IO压力很小，执行该操作）
if(last_one_second_ios < 5)
do merge at most 5 insert buffer
// 至多刷新100个InnoDB的脏页到磁盘（脏页比例超过innodb_max_dirty_pages_pct）
if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct)
do buffer poll flush 100 dirty page
// 没有用户活动，跳转到 backgroupo loop
if(no user activity)
goto backgroupo loop
}
// 每10秒操作
// 刷新100个脏页到磁盘（过去10秒内IO操作小于200次）
if(last_ten_second_ios < 200)
do buffer pool flush 100 dirty page
// 合并最多5个插入缓冲
do merge at most 5 insert buffer
// 合并最多5个插入缓冲
do log buffer flush to disk
// 删除无用的Undo页（最多20个undo页）
do full purge
//脏页比例超过innodb_max_dirty_pages_pct，刷新100个脏页到磁盘，否则刷新10个脏页
if(buf_get_modified_ratio_pct > 70%)
do buffer pool flush 100 dirty page
else
do buffer pool flush 10 dirty page
goto loop
// 后台循环
backgroup loop:
// 删除无用的Undo
do full purge
// 合并20个插入缓冲
do merge 20 insert buffer
if not idel:
goto loop
else
goto flush loop
// 刷新循环
flush loop:
// 刷新100个脏页到磁盘，直到脏页比例小于innodb_max_dirty_pages_pct
do buffer pool flush 100 dirty page
if(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct)
goto flush loop
goto suspend loop
// 暂停循环
suspend loop:
// 暂停线程
suspend_thread()
// 等待事件
waiting event;
goto loop;
}如上所示，主循环有两大操作，每秒操作和十秒操作。
InnoDB1.0.x优化：
在每秒操作中，Master Thread每次最多刷新100个脏页（脏页比例超过innodb_max_dirty_pages_pct），合并20个插入缓冲，如果在写入密集的应用，处理速度可能太慢了。
从InnoDB 1.0.x开始，提供了通过innodb_io_capacity参数
每秒操作中合并插入缓冲数量为innodb_io_capacity * 5%
刷新脏页数量为innodb_io_capacity
而默认innodb_max_dirty_pages_pct参数值从90调整为75
引入以下参数innodb_adaptive_flushing：自适应刷新
脏页比例小于innodb_max_dirty_pages_pct，也会刷新一定量的脏页（由InnoDB控制刷新策略和数量）
innodb_purge_batch_size：控制每次full purge回收Undo页，默认还是20
InnoDB1.2.x优化：
- InnoDB空闲时，执行原来的10秒一次操作，繁忙时，执行原来的每秒一次操作
- 刷新脏页操作，分离到单独Page Cleaner Thread
六、InnoDB关键特性
InnoDB关键特性包括：
- Insert buffer（插入缓冲）
- double write（两次写）
- adaptive hash index（自适应哈希索引）
- Async IO（异步IO）
- Flush neighbor page（刷新临近页）
Insert buffer
若插入按照聚集索引primary key插入，页中的行记录按照primary存放，一般情况下不需要读取另一个页记录，插入速度很快（如果使用UUID或者指定的ID插入而非自增类型则可能导致非连续插入导致性能下降，由B+树特性决定）。如果按照非聚集索引插入就很有可能存在大量的离散插入，insert buffer对于非聚集索引的插入和更新操作进行一定频率的合并操作，再merge到真正的索引页中。使用insert buffer需满足条件：
（1）索引为辅助索引。
（2）索引非唯一。（唯一索引需要从查找索引页中的唯一性，可能导致离散读取）
Double write
Doubel write保证了页的可靠性，Redo log是记录对页(16K)的物理操作，若innodb将页写回表时写了一部分(如4K)出现宕机，则物理页将会损坏无法通过redolog恢复。所以在apply重做日志前，将缓冲池中的脏页通过memcpy到doublewrite buffer中，再将doublewrite buffer页分两次每次1MB刷入共享表空间的磁盘文件中（磁盘连续，开销较小），完成doublewrite buffer的页写入后再写入各个表空间的表中。
当写入页时发生系统崩溃，恢复过程中，innodb从共享表空间的doublewrite找到该页的副本，并将其恢复到表空间文件中，再apply重做日志。
Adaptive hash index
Innodb根据访问频率对热点页建立哈希索引，AHI的要求是对页面的访问模式必须一样，如连续使用where a='xxx' 访问了100次。建立热点哈希后读取速度可能能提升两倍，辅助索引连接性能提升5倍。
通过show engine innodb status\G;查看hash searches/s, 表示使用自适应哈希，对于范围查找则不能使用。
Async IO
用户执行一次扫描如果需要查询多个索引页，可能会执行多个IO操作，AIO可同时发起多个IO请求，系统自动将这些IO请求合并（如请求数据页[1，2]、[2,3]则可合并为从1开始连续扫描3个页）提高读取性能。
刷新临近页
InnoDB提供刷新临近页功能：当刷新一脏页时，同时检测所在区(extent)的所有页，如果有脏页则一并刷新，好处则是通过AIO特性合并写IO请求，缺点则是有些页不怎么脏也好被刷新，而且频繁的更改那些不怎么脏的页又很快变成脏页，造成频繁刷新。对于固态磁盘则考虑关闭此功能（将innodb_flush_neighbors设置为0）。
七、InnoDB的启动、关闭与恢复
innodb_fast_shutdown
该值影响数据库正常关闭时的行为，取值可以为0/1/2（默认为1）：
【为0时】：关闭过程中需要完成所有的full purge好merge insert buffer，并将所有的脏页刷新回磁盘，这个过程可能需要一定的时间，如果是升级InnoDB则必须将此参数调整为0再关闭数据库。
【为1时(默认)】：不需要full purge和merge insert buffer，但会将缓冲池中的脏页写回磁盘。
【为2时】：不需要full purge和merge insert buffer，也不会将缓冲池中的脏页写回磁盘，而是将日志写入日志文件中，后续启动时recovery。
innodb_force_recovery
参数innodb_force_recovery直接影响InnoDB的恢复情况。
默认值为0：进行所有的恢复操作，当不能进行有效恢复（如数据页corrupt）则将错误写入错误日志中。
某些情况下不需要完整的恢复造成，则可定制恢复策略，参数innodb_force_recovery还可以设置为6个非零值：1-6，大的数字表示包含了前面所有小数字表示的影响。有以下几种：
- 1（SRV_FORCE_IGNORE_CORRUPT）：忽略检查到的corrupt页。
- 2（SRV_FORCE_NO_BACKGROUND）：阻止Master Thread线程运行，如果master thread需要进行full purge操作，这样会导致crash。
- 3（SRV_FORACE_NO_TRX_UNDO）:不进行事务的回滚操作。
- 4（SRV_FORCE_NO_IBUF_MERGE）:不进行插入缓冲区的合并操作。
- 5（SRV_FORCE_NO_UNDO_LOG_SCAN）:不查看undo log，这样未提交的事务被视为已提交。
- 6（SRV_FORCE_NO_LOG_REDO）:不进行redo操作。
在设置了innodb_force_recovery大于0后可对表进行select/create/drop操作，但不能进行insert update和delete等DML。如有大事务未提交，并且发生了宕机，恢复过程缓慢，不需要进行事务回滚则将参数设置为3以加快启动过程。
