写在前

本篇博客承接上一篇 mysql 默认引擎innodb 初探（一）进行对mysql数据库 innodb存储引擎进行探索

mysql默认存储引擎 innodb简介

Innodb是第一个完整支持ACID事务的mysql存储引擎（BDB是第一个支持事务的mysql存储引擎，目前已经停止开发）；

主要特点是 支持行锁，MVCC，事务，外键及一致性非锁读，可以有效利用CPU和内存；

各版本对比如下：

tips : 如果不支持多回滚段，Innodb最大支持并发事务量被限制为1023 

innodb体系架构

innodb存储引擎主要有一些后台线程和相应内存（缓冲池，重做日志缓冲，额外缓冲池）组成；tips : 由于innodb存储引擎是基于磁盘存储的，由于CPU速度和磁盘速度存在巨大差距，因此基于磁盘的数据库通常都会使用缓冲池技术提高数据库的整体性能；

后台线程

后台线程：    master thread ： 将缓冲池数据异步刷新到磁盘，保障数据一致性，包括刷新日志到磁盘，脏页刷新，合并插入缓冲，undo页回收等    IO thread :         innodb大量使用AIO（async IO）来提高数据库的性能         innodb有4个IO线程，read thread, write thread, log io thread, insert buffer thread        可以通过 show variables like "innodb_%io_threads"\G 查看读写线程数        可以使用 show engine innodb status\G  查看innodb存储引擎状态        可以通过 innodb_read_io_threads or innodb_write_io_threads配置read & write IO线程数（innodb 1.0x）    purge thread ：         事务提交后，其所使用的undolog就不再需要，所以需要存储引擎负责回收并分配新undo页；        innodb1.1x之前 purge动作在master thread中完成；        之后可以通过配置 innodb_purge_threads=1 开启独立的purge thread来处理，以减轻master thread负担，提高CPU使用率；    page cleaner thread :         innodb1.2x版本开始，将脏页刷新操作放在单独的page cleaner thread中，进一步减轻了master thread负担，并减轻用户查询线程的阻塞；

缓冲池

缓冲池：    缓冲池本质上就是一块内存区域，通过内存的速度弥补磁盘速度较慢对数据库性能的影响；    在数据库中进行读取操作时，首先判断待读取页是否存在缓冲池中；    如果存在，则缓冲池命中，直接从缓冲池中读取进行后续操作；    否则进行io操作，从磁盘中读取页，然后存放在缓冲池中（这个过程称 将页fix到缓冲池）；    当数据库进行修改操作（insert update delete）时，则首先修改缓冲池中对应的页，然后再以一定频率刷新到磁盘上；    （注意：缓冲池中是页并不是每次都刷新，而是通过checkpoint技术按一定频率刷盘，下面会详细介绍checkpoint技术）    所以缓冲池的大小直接影响数据库的性能；    可以通过 show variables like "innodb_buffer_pool_size"\G 查看缓冲池大小    具体缓冲池包括索引页，数据页，undo页，插入缓冲，自适应哈希索引等（MySIAM只会缓存索引页，可看上面的介绍）

从innodb1.0x开始，运行设置多个缓冲池实例；每个页根据哈希值平均分配到不同缓冲池实例中；
从而减少数据库内部资源的竞争，增加并发处理能力；
可以通过 innodb_buffer_pool_instances配置缓冲池实例个数；
可以通过 show variables like “innodb_buffer_pool_instances” 查看缓冲池实例配置；

innodb存储引擎通过LRU（lastest Recent Used），最少使用算法对内存进行管理，详情见附录A

重做日志缓冲    重做日志记录了innodb的事务信息，当数据库宕机或者出现其他意外情况；可以还原数据到某个时间点，从而保证数据的一致性和事务的持久性；    以下情况下会将重做日志缓冲刷到磁盘上：        master thread 每秒刷盘一次        事务提交时，强制刷盘        重做日志缓冲池剩余空间小于1/2时，刷新到磁盘    所以重做日志缓冲不需要太大，只要保证一秒的事务量即可，一般设置为8M；    可以通过 show variables like "innodb_log_buffer_size" 查看；    可以在配置文件中，通过设置innodb_log_buffer_size 配置重做日志缓冲大小； 

额外缓冲池    一些数据结构本身内存分配，缓冲相关对象（缓冲控制对象），等待等信息会直接从额外缓冲池中进行申请；    当缓冲池配置很大时，也要适当增加额外缓冲池的配置；

checkpoint 介绍

执行DML语句（update,delete）修改页时，该页即为脏页（即缓冲池中的页的版本比磁盘的页要新）；
如果每次页被修改都同步到磁盘的话，开销非常大；特别是热点数据分散在不同页中；

另一种情况，当刷新脏页到磁盘的过程中出现了宕机，数据就不能恢复；
为了能够恢复数据，当前事务型数据库普遍采取了 write ahead log策略（当事务提交时，先写重做日志，再刷新脏页到磁盘）；当发生宕机时，可以 通过重做日志恢复数据到某个时间点；

由于缓冲池的大小是有限的，日志文件也不可能无限增大；所以就引入了checkpoint技术

checkpoint（检查点）主要解决以下问题:    当数据库宕机后，缩短数据恢复时间    当缓冲池不够用时，刷新脏页到磁盘    当重做日志文件不可用时，刷新脏页checkpoint分为以下两种：    Sharp CheckPoint  将所有脏页都刷回磁盘；比较耗时，一般数据库关闭时使用此种工作方式；    Fuzzy CheckPoint  将部分脏页刷回磁盘，数据库运行时使用此种方式；以下情况下innodb存储引擎会触发checkpoint:    Master thread CheckPoint:        Master thread 会定期（每秒或没十秒）刷新一定比例脏页到磁盘；由于此过程异步执行，Innodb存储引擎可以执行其他操作，比如用户查询线程不会阻塞；    FLUSH_LRU_LIST Checkpoint :         innodb存储引擎要求保留一定空闲页；当空闲页小于此数量时会移除LRU列表尾端页，若果这些页中包含脏页，会触发checkpoint，刷新脏页到磁盘；        （由于检查LRU列表及移除尾端页会阻塞用户查询操作， mysql5.6开始，这中checkpoint被放置在 page cleaner thread中）    Async/sync Flush CheckPoint:        当重做日志文件不可用（剩余空间不足）时，会触发此种checkpoint强制将一些脏页刷新回磁盘；        设置如下参数：            redo_lsn : 写入重做日志的LSN，            checkpoint_ls : 刷新回磁盘的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 异步刷新脏页当磁盘，使 checkpoint < async_water_mark            当 checkpoint_age > sync_water_mark 同步 刷新脏页到磁盘，使checkpoint_age < asyn_water_mark            由于Async Flush CheckPoint会阻塞发现问题的用户查询线程，sync flush checkpoint会阻塞所有用户查询线程；             mysql5.6开始将此部分放在 page cleaner thread中；    Dirty page too much CheckPoint：        当脏页太多时，innodb存储引擎会强制刷新脏页回磁盘；        可以通过 innodb_max_dirty_page_pct 配置脏页最大比例量； 

innodb master thread工作方式

InnoDB存储引擎将主要工作都放在 master thread中完成，master thread具有最高线程优先级别（mysql是单进程多线程）；

后台线程逻辑简介

master thread由多个循环组成:    主循环（loop）    后台循环（backgroup loop）    刷新循环（flush loop）    暂停循环（suppend loop）主循环 loop  :     每秒操作：        刷新日志缓冲到磁盘（总是）        合并插入缓冲（当当前IO压力较小时，执行）        刷新至多100脏页到磁盘（当缓冲池脏页比例buf_get_modified_ratio_pct > 配置文件中 innodb_max_dirty_page_pct时刷新）        如果当前没有用户活动，切换到backgroup loop（可能）    每10秒操作：        刷新100个脏页到磁盘（当IO压力不大的情况下执行）        合并至多5个插入缓冲（总是）        刷新日志缓冲到磁盘（总是）        删除无用undo页（总是）        刷新100个或者10个脏页到磁盘（总是）后台循环（backgroup  loop）:    当数据库空闲或者数据库关闭时，切换到此loop    删除无用undo页（总是）    合并20个插入缓冲（总是）    跳回到主循环 或者 跳到flush loop（总是 ）刷新循环（flush loop ）:    不断刷新100个脏页，直到符合条件，跳到suppend loop暂停循环（suppend loop ）:    挂起master thread,等待事件发生，再次唤醒master thread

后台线程，伪代码实现

void master_thread(){    loop:        //每秒操作        for(int i-0; i<10; i++){            thread_sleep(1);   //线程休眠1秒，当负载很大时，可能会小于1秒                      do log buffer flush  to disk   //刷新日志缓冲到磁盘            //若果上一秒io次数小于5，说明当前io压力较小，进行插入缓冲合并            // if(last_one_second_io_num < 5 ){     1.0x之前判断方式，硬编码处理            if(last_one_second_io_num < innodb_io_capacity*5% ){                do merge at most 5 insert buffer            }            //当当前缓冲脏页比例大于配置文件中最大脏页比例时，刷新至多100个脏页到磁盘            if(buf_get_modified_ratio_pct > innodb_max_dirty_page_pct){                // do buffer pool flush  100 dirty pages 1.0x之前，硬编码处理                do buffer pool flush innodb_io_capacity*100% dirty pages            }else if(enable_adaptive_flush){                do buffer pool flush desired amount dirty page //1.0x之后引入自适应刷新策略            }            //如果当前没有活动用户则切换到后台循环            if（ no user activity ）{                goto backgroup loop            }        }        //每十秒操作        //如果10秒内io数量小于200，说明当前io压力较小，则刷新100脏页到磁盘        // 1.0x之前，硬编码处理        // if( last_ten_second_io_num < 200 ){            //    do buffer pool flush  100 dirty pages        // }        if(last_tem_second_io_num < innodb_io_capacity){            do buffer pool flush  innodb_io_capacity*100% dirty pages        }        // do merge at most 5 insert buffer 总是合并至多5个插入缓冲 1.0x之前，硬编码处理         do merge at most innodb_io_capacity*5% insert buffer //总是合并至多5个插入缓冲        do log buffer to disk //总是刷新日志缓冲到磁盘        do full purge //总是删除无用undo页         //根据缓冲脏页比例决定刷新100 or 10个脏页到磁盘         if( buf_get_modified_pct > 70% ){            // do buffer pool flush 100 dirty pages 1.0x之前，硬编码处理            do buffer pool flush innodb_io_capacity*100% dirty pages        }else{            // do buffer pool flush  10 dirty pages  1.0x之前，硬编码处理            do buffer pool flush  innodb_io_capactiy*10% dirty pages        }    backgroup  loop:        do full page //删除无用undo页        // do merge 20 insert buffer 合并20个插入缓冲  1.0x之前，硬编码处理        do merge innodb_io_capacity insert buffer //合并插入缓冲        //如果空闲则跳到flush loop,否则跳到loop        if( not idle ){            goto loop        }else{            goto flush loop        }    flush  loop:        // do buffer pool flush  100 dirty page  1.0x之前，硬编码处理        do buffer pool flush  innodb_io_capacity*100% dirty page        if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct ){            goto flush  loop        }else{            goto suppend loop        }    suppend loop:        suppend_thread() //挂起master thread        waiting event //等待事件        goto loop   //跳到主循环} 

由上面一段伪代码可以清楚了解到，innodb1.0x之前，每秒or十秒的io量写死在代码中，由于SSD的出现及磁盘的发展，需要更高的io处理，于是在1.0x版本，引入的innodb_io_capacity参数来动态调节io吞吐量；

innodb_max_dirty_pages_pect 太大（eg: 90%），如果内存太大或者服务器压力过大，则刷新脏页速度会降低，推荐值为75%

1.0x版本，引入了 innodb_adapitive_flushing（自适应刷新）参数，当脏页比例小于innodb_max_dirty_pages_pect时，会进行自动刷新一定数量脏页；（innodb_adapitive_flushing根据产生重做日志redo log的速度决定最适合的刷新脏页数量）

1.0x版本之前，full purge操作，最多回收20个undo页，从1.0x版本，引入了innodb_purge_batch_size参数，可以调控回收undo页数；
show variables like “innodb_purge_batch_size”\G

1.2x版本开始，master thread中刷新脏页操作，分离到单独的page cleaner thread中，减轻master thread负担并进一步提高 系统并发性。

innodb关键特性

插入缓冲（insert buffer） 提高性能 两次写（double write） 提高安全性 自适应哈希索引（adapitive hash index） innodb内部自动优化 异步IO（async IO） 提高并发 刷新领接页（flush neighbor page） 提高性能

插入缓冲

插入缓冲简介

innodb存储引擎，主键是行唯一的标示符，通常是顺序递增的插入，因此插入聚集索引（primary key）一般是顺序存放的，不需要随机离散读取磁盘，速度很快；

但是通常表不止有聚集索引，还会有一些非聚集索引并且不是顺序存放的，非聚集索引的叶子节点的插入是离散的访问非聚集索引页，随机读取导致了性能下降

对于非聚集索引的插入或更新操作，innodb引入了插入缓冲，
插入或更新非聚集索引时，首先判断对于的非聚集索引页是否在缓冲池中，若果存在，则直接插入；
否则，先放入到inssert buffer对象中，然后再一定的频率或特殊情况下，进行insert buffer 和辅助索引页子节点的merge（合并）操作；（如：master thread 中insert buffer merge）；

插入缓冲使用条件

索引时辅助索引（非聚集索引） 索引不是唯一的（unique key 每次插入前都会进行查找是否已经存在）

Innodb1.0x开始引入Change Buffer，将insert buffer根据DML操作（insert，update，delete）不同，分别进行insert buffer， purge buffer，delete buffer；

update 操作：

将记录标记为删除 真正删除记录

delete buffer 对应将记录标记删除，purge buffer对应将记录真正删除

ps:    当数据库写比较密集时，insert buffer会占用过多缓冲池，默认最大可以占用1/2的缓冲池内存    innodb1.2x开始，可以通过innodb_change_buffer_max_size调节change buffer最大占用缓冲池的百分比

插入缓冲内部实现

insert buffer的数据结构是一颗B+树，默认存放在共享表空间中（ibdata1）【tips:当然缓冲池中页有对应的insert buffer 缓冲区】；

insert buffer B+的非叶子节点存放的是查询key（search key）;

space : 待插入记录所在表的空间id（innodb存储引擎中，每个表有一个唯一space id） marker ： 用于兼容老版本的insert buffer，暂忽略 offset : 表示页所在的偏移量

当辅助索引要插入到辅助索引页中时，若果该辅助索引页不在缓冲池中，则根据上图 规则构造一个search key并插入到 insert buffer B+树中；

插入叶子节点结构如下：

IBUF_REC_OFFSET_COUNT : 两个字节，用于排序每个记录进入insert buffer的顺序

为了能够保证每次合并插入缓冲时，每个辅助索引页都有足够的空间进行存储，需要一个特殊的页用来标记每个辅助索引页的可用空间，称这个页为 insert buffer bitmap

每个insert buffer bitmap页追踪16384个辅助索引页（256个区，详情后续innodb 数据也结构会进行详细讲解）；

进行插入缓冲合并情况

merge insert buffer：

辅助索引页被读取到缓冲池
当select查询时，将辅助索引页读入缓冲池时，需要检查insert buffer bitmap页，然后确认该辅助索引页是否存有记录于insert buffer B+树中，如果存在，则将insert buffer B+树中记录插入到该辅助索引页中

---

insert buffer bitmap页追踪到该辅助索引页已经无可用空间
insert buffer bitmap页用来追踪每个辅助索引页的可用空间 ，保证至少有1/32页的空间可用，否则会强制进行一次合并，将insert buffer B+树中该页的记录插入到该辅助索引页中

---

master thread
主线程，每秒 or 每十秒会进行一次 merge insert buffer操作

两次写（double write）

当innodb存储引擎正在写入某个页到表中，只写了部分数据（16k的页，写入4k）时，发生了宕机，称为部分写失效（partial page write）；
由于重做日志是基于偏移量进行记录的（如偏移299，写’insert data’），如果想通过重做日志进行恢复，必须要页的一份副本；
写数据之前，保存一份页的副本，即doublewrite

double write 有两部分组成：

double write buffer （2MB） 物理磁盘上共享表空间连续的128页 （2MB）

当刷新脏页时，先通过memcpy函数将脏页复制到double write buffer，之后分两次，每次将1MB顺序地写入共享表空间的物理磁盘上（由于是顺序写，速度非常快）；然后再离散的将脏页刷回到具体对应的页中；

tips : 有些系统文件提供了部分写失效的防范措施（eg:ZFS文件系统） ，可以通过 innodb_doublewrite = OFF 禁用两次写

自适应哈希索引

innodb存储引擎会监控对表上各索引的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称为 自适应哈希索引（adapitive hash index AHI）；

AHI通过缓冲池中B+树构造的，因此建立数据很快；

由于哈希索引只能进行等值查询，不能进行范围查询，一定程度上有局限性

可以通过观察has searches 与 non hash searches 考虑是否禁用该特性，默认为开启

异步io（AIO）

当前数据库系统，为了提高磁盘操作性能，都采用了异步IO；

可以通过 show variables like “innodb_use_native_aio”\G 查看是否开启；

刷新邻接页

当刷新一个脏页时，innodb存储引擎会检测该页所在区（extend）的所有页，如果有其他脏页，则一起进行刷新操作；

tips:

对应机械硬盘，邻接页刷新可以将多个IO写操作，通过异步io特性，合并为一个io操作，可以显著提高性能 对应SSD，由于有较高的IOPS，建议关闭此特性

启动 ，关闭和恢复

无

后记

后续会对 innodb存储引擎相关文件进行探索