MySql 优化：详细解读 InnoDB 存储引擎

InnoDB 独立表空间，支持 MVCC，行锁设计，提供一致性非锁定读，支持外键，插入缓冲，二次写，自适应哈希索引，预读使用聚集的方式存储数据，每张表的存储都是按主键顺序存放。

InnoDB 是事物安全的 MySQL 存储引擎，设计上采用了类似 Oracle 数据库的架构。通常来说，InnoDB 存储引擎是 OLTP 应用中核心表的首选存储引擎，也正是因为其存在，才使得 MySQL 数据库变得更加有魅力。

一、InnoDB 体系架构

1、后台进程：

1. Master Thread：核心线程，负责缓冲池的数据异步入盘，包括脏页刷新、合并插入缓冲、undo 页回收等。

2. IO Thread：包括 read thread 和 writer thread，使用 show variables like '%innodb_%io_thread%';查看。

3. Purge Thread：回收事务提交后不再需要的 undo log，通过 show variables like '%innodb_purge_threads%'; 查看。

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       256Database pages     256  【LRU列表占用页】Old 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 255, created 40, written 670.16 reads/s, 0.06 creates/s, 0.37 writes/sBuffer pool hit rate 943 / 1000 【缓冲池命中率大于95%则良好】, young-making rate 0 / 1000 not 0 / 1000LRU 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，

刷新规则：

1、Master Thread 每秒将一部分重做日志缓冲刷新到重做日志文件

2、每一事务提交时会将重做日志刷新到重做日志文件（如果配置了）

3、重做日志缓冲区使用空间大于 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 pageelse        do buffer pool flush 10 dirty pagegoto loop // 后台循环backgroup loop:// 删除无用的Undodo full purge// 合并20个插入缓冲do merge 20 insert bufferif not idel:    goto loopelse     goto flush loop // 刷新循环flush loop:// 刷新100个脏页到磁盘，直到脏页比例小于innodb_max_dirty_pages_pctdo buffer pool flush 100 dirty pageif(buf_get_modified_ratio_pct>innodb_max_dirty_pages_pct)    goto flush loopgoto 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，大的数字表示包含了前面所有小数字表示的影响。有以下几种：

• （SRV_FORCE_IGNORE_CORRUPT）：忽略检查到的 corrupt 页。

• （SRV_FORCE_NO_BACKGROUND）：阻止 Master Thread 线程运行，如果 master thread 需要进行 full purge 操作，这样会导致 crash。

• （SRV_FORACE_NO_TRX_UNDO）:不进行事务的回滚操作。

• （SRV_FORCE_NO_IBUF_MERGE）:不进行插入缓冲区的合并操作。

• （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 以加快启动过程。