InnoDB存储引擎

后台线程

InnoDB存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。

Master Thread

Master Thread是一个非常核心的后台线程,主要负责将缓冲池的数据异步刷新到磁盘,保证数据的一致性,包括脏页的刷新、合并插入缓冲、UNDO页的回收等。

IO Thread

在InnoDB存储引擎使用大量的AIO（Async IO）来处理IO请求，这样可以极大提高数据库的性能。
在Linux平台下 IO Thread的数量不能进行调整，在windows平台1.0.x版本开始分别使用 innodb_read_io_threads 和 innodb_write_io_theads参数进行设置,如:

mysql>SHOW VARIABLES LIKE ‘innodb_version’\G;

mysql> SHOW VARIABLES LIKE ‘innodb_%io_threads’\G;

通过SHOW ENGINE INNODB STATUS\G; 观察 InnoDB 中的IO Thread。

Purge Thread

事务被提交后,其所使用的undolog可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页。
在InnoDB 1.1 版本中 innodb_purge_rgreads设为大于1,存储启动将其设为1,并报错。

在InnoDB1.2版本开始 InnoDB 支持多个Purge Thread。

Page Cleaner Thread

Page Cleaner Thread在InnoDB 1.2.x版本中引入的。其作用是将之前版本中的脏页的刷新操作放入到单独的线程中完成。

InnoDB内存

缓存池

InnoDB存储引擎是基于磁盘存储的,并将其中的记录按照页的方式进行管理。
缓存池简单来说就是一块内存区域。

通过参数innodb_buffer_pool_size配置缓存池: show variables like ‘innodb_buffer_pool_size’\G

内存结构：

具体来看缓冲池中缓存的数据页类型有:
索引页: 缓存数据表索引
数据页: 缓存数据页，占缓冲池的绝大部分
undo页: undo页是保存事务，为回滚做准备的。
插入缓冲(Insert buffer): 上面提到的插入数据时要先插入到缓存池中。
自适应哈希索引(adaptive hash index): 除了B+ Tree索引外，在缓冲池还会维护一个哈希索引，以便在缓冲池中快速找到数据页。
InnoDB存储的锁信息(lock info):
数据字典(data dictionary):
内存中除了缓冲池外外还有:
重做日志缓冲redo log: 为了避免数据丢失的问题，当前数据库系统普遍采用了write ahead log策略，既当事务提交时先写重做日志，再修改写页。当由于发生宕机而导致数据丢失时，可以通过重做日志进行恢复。InnoDB先将重做日志放到这个缓冲区，然后按照一定的频率更新到重做日志文件中。重做日志一般在下列情况下会刷新内容到文件:

1.Master Thread每一秒将重做日志缓冲刷新到重做日志文件
2.每个事务提交时会将重做日志缓冲刷新到重做日志文件
3.当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件
额外内存池: InnoDB存储引擎中，对内存的管理师通过一种称为内存堆的方式进行的，在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。
缓冲池是一个很大的内存区域，InnoDB是如何对这些内存进行管理的呢。答案就使用LRU list。
LRU(Latest Recent Used, 最近最少使用)算法默认的是最近使用的放到表头，最早使用的放到表尾，依次排列。当有LRU填满时有新的进来就把最早的淘汰掉。InnoDB则是在这个基础上进行了修改:

最近使用的不放到表头，而是根据配置放到一定比例处，这个地方叫做midpoint, midpoint之前的成为new列表，之后的成为old列表。淘汰的同样是表尾的页。
为了保证new列表的不经常使用时能够淘汰，设置了一个超时时间:innodb_old_blocks_time，当数据在midpoint(我理解应该是在old列表中，不然这个点的页就一个，变化也比较频繁)的时间超过找个时间时就会被提升到表头，new列表的表尾页则被置换到old列表中。
这么做的原因主要是因为常见的索引或数据的扫描操作会连续读取大量的页，甚至是全表扫描。如果采用原来的LRU算法就会更新全部的缓冲池，其他查询需要的热点数据就会被冲走，导致更多的磁盘读取操作，降低数据库的性能。
LRU是用来管理已经读取的页，当数据库启动时LRU是空列表,既只有表头，没有内容。这时页都放在Free List中。当需要有数据读写时要进行需要获取分页，这时要从Free List中删除分页，然后添加到LRU list中。到一定时间Free List中的分页就会被分配完毕，这时候就正常使用上面的LRU策略。
LRU列表中的页被修改后，称该页为脏页(dirty page),既缓冲池中的数据和磁盘上的数据产生了不一致，这时脏页会被加入到一个Flush 列表中(注意，同时存在两个列表中)。然后根据刷新的机制定时的刷新到磁盘中。

InnoDB 1.0.x开始允许多个缓存实例。

参数innodb_buffer_pool_instances来进行配置默认唯一。

参数innodb_buffer_pool_instances可以设置大于1的值就可以得到多个缓冲池实例。

再通过命令SHOW ENGINE INNODB STATUS\G; 观察内容

mysql5.6开始:
SELECT POOL_ID POOL_SIZE, FREE_BUFFERS,DATABASE_PAGES FROM INNODB_BUFFER_POOL_STATS\G
查看缓冲状态。

LRU List、Free List 和 Flush List

缓存池默认页大小16kB，同样使用LRU算法进行缓冲池进行管理。

通过information_schema架构下的表观察unzip_LRU列表中的页:SELECT TABLE_NAME,SPACE,PAGE_NUMBER,COMPRESSED_SIZE FROM INNODB_BUFFER_PAGE_LRU WHERE C OMPRSSED_SIZE <> 0;

Flush List为脏页列表;

重做日志缓冲

额外的内存池

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

InnoDB存储引擎三大特性

插入缓冲

innodb使用insert buffer”欺骗”数据库:对于为非唯一索引，辅助索引的修改操作并非实时更新索引的叶子页,而是把若干对同一页面的更新缓存起来做合并为一次性更新操作,转化随机IO 为顺序IO,这样可以避免随机IO带来性能损耗，提高数据库的写性能。

IBUF_POOL_SIZE_PER_MAX_SIZE参数修改默认为2 即为最大缓存为1/2 如果将其改为3 这最大能够使用1/3的缓存池内存。

聚簇索引与非聚簇索引的区别

聚集索引的叶子节点存储的是数据，而且是按照物理顺序存储的;非聚集索引叶子节点是地址(也就是聚集索引键地址)，是按照逻辑顺序存储的(以上言论是从网上了解到的，但是，聚集索引也不是按照物理地址连续的，而是逻辑上连续的)。

高并发后的insert会发生什么？

1
2
3

mysql> create table t ( id int auto_increment, 
name varchar(30),primary key (id),key(name));  
Query OK, 0 rows affected (0.21 sec)

我们知道，主键是行唯一的标识符，在应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取，速度会很快，但是我们看到上一个表还有一个叫做name的索引字段，这样的情况下产生了一个非聚集的并且不是唯一的索引。在进行插入操作时，数据页的存放还是按主键id的执行顺序存放，但是对于非聚集索引，叶子节点的插入不再是顺序的了。这时就需要离散地访问非聚集索引页，插入性能在这里变低了。然而这并不是这个name字段上索引的错误，是因为B+树的特性决定了非聚集索引插入的离散性

插入缓冲的实现

在innodb的1.0x版本开始，引入了change buffer，可以把它看成insert buffer的升级版，innodb可以对DML操作-INSERT/DELETE/UPDATE都进行缓冲。
1.将一个辅助索引插入到页(space,offset)
2.检查这个页是否在缓冲池中
在:直接插入
不在:继续
3.缓存进入insert buffer
4.构造一个search key
5.查询insert buffer树
6.生成逻辑记录并插入树中

insert buffer内部实现原理

在mysql4.1版本之后，insert buffer是通过一全局唯一的一个B+树进行管理所有表的辅助索引。而这颗树存放在共享表空间中，格式为ibdata1，所以如果试图通过独立表空间idb文件恢复表中数据的时候，往往会导致CHECK TABLE失败，这是因为表的辅助索引中的数据可能还在INSERT BUFFER中，也就是共享表空间中，所以通过ibd文件进行恢复后，还需要进行REPAIR TABLE 操作来重建表上所有的辅助索引。

insert buffer的b+树的非叶子节点存放的是查询的search key（键值），其构造如图：

space为表空间id
marker用来兼容老版本
offset表示页所在偏移量

叶子节点会比非叶子节点多俩数据，一个是metadata，一个是secondary index record。

metadata 记录的每一列的类型,长度
secondary index record 记录的具体值

为了保证每个辅助索引页Merge Insert Buffer的B+树必须成功，还需要有一个特殊的页用来标记每个辅助索引页（space，page_no）的可用空间。这个页的类型为Insert Buffer Bitmap。它会标记16385个辅助索引页，每个辅助索引页会在其中占用4bit的位置来记录信息，具体信息如下：

Merge Insert Buffer的操作可能发生在以下几种情况下：

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

第一种情况为当辅助索引页被读取到缓冲池中时，例如这在执行正常的SELECT查询操作，这时需要检查Insert Buffer Bitmap页，然后确认该辅助索引页是否有记录存放于Insert Buffer B+树中。若有，则将Insert Buffer B+树中该页的记录插入到该辅助索引页中。可以看到对该页多次的记录操作通过一次操作合并到了原有的辅助索引页中，因此性能会有大幅提高。
Insert Buffer Bitmap页用来追踪每个辅助索引页的可用空间，并至少有1/32页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于1/32页，则会强制进行一个合并操作，即强制读取辅助索引页，将Insert Buffer B+树中该页的记录及待插入的记录插入到辅助索引页中。这就是上述所说的第二种情况。
还有一种情况，之前在分析Master Thread时曾讲到，在Master Thread线程中每秒或每10秒会进行一次Merge Insert Buffer的操作，不同之处在于每次进行merge操作的页的数量不同。

缓冲的限制条件

插入缓冲的启用需要满足一下两个条件：
1）索引是辅助索引（secondary index）
2）索引不适合唯一的
原因是因为插入缓冲本身就是为了解决二级索引离散插入的问题，所以建立一个缓冲区将部分离散的索引数据合并，使用一次大的IO操作统一刷到磁盘，如果索引是唯一的，那这么做将失去意义，而且每次还需要去询问数据页是否已经存在，还会增加额外的IO操作。

插入缓冲性能影响

任何一项技术在带来好处的同时，必然也带来坏处。插入缓冲主要带来如下两个坏处：
1）可能导致数据库宕机后实例恢复时间变长。如果应用程序执行大量的插入和更新操作，且涉及非唯一的聚集索引，一旦出现宕机，这时就有大量内存中的插入缓冲区数据没有合并至索引页中，导致实例恢复时间会很长。
2）在写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认情况下最大可以占用1/2，这在实际应用中会带来一定的问题。

观察

show engine innodb status\g;
 
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 2 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

从上面可以看到其中有一部分叫做INSERT BUFFER AND ADAPTIVE HASH INDEX，其中的seg size指的就是当前insert buffer的大小，具体计算方式为seg_size*16KB =32KB，free list len表示空闲列表的长度，size表示已经合并记录页的数量。

二次写（double write）

基本概念

doublewrite由两部分组成,一部分是内存的doublewrite buffer,大小为2MB,另一部分是物理磁盘上共享表空间中联系的128个页,即2个区(extent),大小同样为2MB。
脏读刷新先复制到内存中的doublewrite buffer，之后doublewrite buffer再分两次，每次1MB写入共享表空间的物理磁盘，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。如图：

通过命令SHOW GLOBAL STATUS LIKE ‘innodb dblwr%’\G观察doublewrite运行的情况。

页断裂（partial write）

所谓页断裂是数据库宕机时(OS重启，或主机掉电重启)，数据库页面只有部分写入磁盘，导致页面出现不一致的情况。那么为什么会不一样呢？因为数据库，OS和磁盘读写的基本单位是块，也可以称之为(page size)block size。我们知道数据库的块一般为8K，16K；而OS的块则一般为4K；IO块则更小，linux内核要求IO block size<=OS block size。磁盘IO除了IO block size，还有一个概念是扇区(IO sector)，扇区是磁盘物理操作的基本单位，而IO 块是磁盘操作的逻辑单位，一个IO块对应一个或多个扇区，扇区大小一般为512个字节。所以各个块大小的关系可以梳理如下：
DB block > OS block >= IO block > 磁盘 sector，而且他们之间保持了整数倍的关系。所以说当数据库突然宕机，就会造成部分DB block的数据实际上并未写入到磁盘的sector中，出现了页断裂的情况，进而导致数据不一致的现象。

数据库日志的三种格式

数据库系统实现日志主要有三种格式，逻辑日志(logical logging)，物理日志(physical logging)，物理逻辑日志(physiological logging)，逻辑日志，记录一个个逻辑操作，不涉及物理存储位置信息，比如mysql的binlog；物理日志，则是记录一个个具体物理位置的操作，比如在2号表空间，1号文件，48页的233这个offset地方写入了8个字节的数据，通过(group_id,file_id,page_no,offset)4元组，就能唯一确定数据存储在磁盘的物理位置；物理逻辑日志是物理日志和逻辑日志的混合，如果一个数据库操作(DDL，DML，DCL)产生的日志跨越了多个页面，那么会产生多个物理页面的日志，但对于每个物理页面日志，里面记录则是逻辑信息。这里我举一个简单的INSERT操作来说明几种日志形式。
比如innodb表T(c1,c2, key key_c1(c1)),插入记录row1(1,’abc’)
逻辑日志：

1	<insert OP, T, 1,’abc’>

逻辑物理日志：因为表T含有索引key_c1, 一次插入操作至少涉及两次B树操作，二次B树必然涉及至少两个物理页面,因此至少有两条日志

1 2	<insert OP, page_no_1, log_body> <insert OP, page_no_2, log_body>

物理理日志：由于一次INSERT操作，物理上来说要修改页头信息(如,页内的记录数要加1)，要修改相邻记录里的链表指针，要修改Slot属性等，因此对应逻辑物理日志的每一条日志，都会有N条物理日志产生。

< group_id,file_id,page_no,offset1, value1>
< group_id,file_id,page_no,offset2, value2>
……
< group_id,file_id,page_no,offsetN, valueN>

因此对于上述一个INSERT操作，会产生一条逻辑日志，二条逻辑物理日志，2*N条物理日志。从上面简单的分析可以看出，逻辑日志的日志量最小，而物理日志的日志量最大；物理日志是纯物理的；而逻辑物理日志则页间物理，页内逻辑，所谓physical-to-a-page, logical-within-a-page。

页断裂和数据一致性

前面我们分析了异常重启导致页断裂的原因，而页断裂就意味着数据库页面不完整，那么数据库页面不完整就意味着数据库不一致。我们知道，数据库异常重启时，自身有异常恢复机制，主流数据库基本原理类似：第一阶段重做redo日志，恢复数据页和undo页到异常crash时的状态；第二阶段，根据undo页的内容，回滚没有提交事务的修改。通过两个阶段保证了数据库的一致性。对于mysql而言，在第一阶段，若出现页断裂问题，则无法通过重做redo日志恢复，进而导致恢复中断，数据库不一致。这里大家可能会有疑问，数据库的redo不是记录了所有的变更，并且是物理的吗？理论上来说，无论页面是否断裂，从上一个检查点对应的redo位置开始，一直重做redo，页面自然能恢复到正常状态。对吗？

redo格式与数据一致性

回到“发生页断裂后，是否会影响数据库一致性”的问题，发生页断裂后，对于利用纯物理日志实现redo的数据库不受影响，因为每一条redo日志完全不依赖物理页的状态，并且是幂等的(执行一次与N次，结果是一样的)，而逻辑物理日志则不行，比如修改页头信息，页内记录数加1，slot信息修改等都依赖于页面处于一个一致状态，否则就无法正确重做redo。而mysql正是采用这种日志类型，另外要说明一点，redo日志的页大小一般设计为512个字节，因此redo日志页本身不会发生页断裂。所以发生页面断裂时，异常恢复就会出现问题，需要借助于double write技术来辅助处理。

doubleWrite的实现

在InnoDB将BP中的Dirty Page刷（flush）到磁盘上时，首先会将（memcpy函数）Page刷到InnoDB tablespace的一个区域中，我们称该区域为Double write Buffer（大小为2MB，每次写入1MB，128个页，每个页16k,其中120个页为后台线程的批量刷Dirty Page，还有8个也是为了前台起的sigle Page Flash线程，用户可以主动请求，并且能迅速的提供空余的空间）。在向Double write Buffer写入成功后，第二步、再将数据分别刷到一个共享空间和真正应该存在的位置。具体的流程如下图所示：

doubleWrite的保护机制

下面来看下在不同的写入阶段，操作系统crash后，double write带来的保护机制：

阶段一：copy过程中，操作系统crash，重启之后，脏页未刷到磁盘，但更早的数据并没有发生损坏，重新写入即可阶段二：write到共享表空间过程中，操作系统crash，重启之后，脏页未刷到磁盘，但更早的数据并没有发生损坏，重新写入即可阶段三：write到独立表空间过程中，操作系统crash，重启之后，发现：（1）数据文件内的页损坏：头尾checksum值不匹配（即出现了partial page write的问题）。从共享表空间中的doublewrite segment内恢复该页的一个副本到数据文件，再应用redo log；（2）若页自身的checksum匹配，但与doublewrite segment中对应页的checksum不匹配，则统一可以通过apply redo log来恢复。）阶段X：recover过程中，操作系统crash，重启之后，innodb面对的情况同阶段三一样（数据文件损坏，但共享表空间内有副本），再次应用redo log即可。

doubleWrite对性能的影响

系统需要将数据写两份，一般认为，Double Write是会降低系统性能的。peter猜测可能会有5-10%的性能损失，但是因为实现了数据的一致，是值得的。Mark Callaghan认为这应该是存储层面应该解决的问题，放在数据库层面无疑是牺牲了很多性能的。事实上，Double Write对性能影响并没有你想象（写两遍性能应该降低了50%吧？）的那么大。在BP中一次性往往会有很多的Dirty Page同时被flush，Double Write则把这些写操作，由随机写转化为了顺序写。而在Double Write的第二个阶段，因为Double Write Buffer中积累了很多Dirty Page，所以向真正的数据文件中写数据的时候，可能有很多写操作可以合并，这样有可能会降低Fsync的调用次数。基于上面的原因，Double Write并没有想象的那么糟。最后发现打开和关闭Double Write对效率的影响并不大。

自适应哈希索引

哈希索引只有Memory, NDB两种引擎支持，Memory引擎默认支持哈希索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。但是，Memory引擎表只对能够适合机器的内存切实有限的数据集。要使InnoDB或MyISAM支持哈希索引，可以通过伪哈希索引来实现，但是innodb还实现了一种叫做自适应哈希索引来达到目的。
InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

状态监控

mysql> show engine innodb status\G
……
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

1、34673：字节为单位，占用内存空间总量

2、通过hash searches、non-hash searches计算自适应hash索引带来的收益以及付出，确定是否开启自适应hash索引

限制

　　1、只能用于等值比较，例如=， <=>，in
　　2、无法用于排序
　　3、有冲突可能
　　4、MySQL自动管理，人为无法干预。

自适应哈希索引的控制

由于innodb不支持hash索引，但是在某些情况下hash索引的效率很高，于是出现了adaptive hash index功能，但是通过上面的状态监控，可以计算其收益以及付出，控制该功能开启与否。

其他关键特性

异步IO

为了提高磁盘操作性能，当前的数据库系统都采用异步IO（Asynchronous IO,AIO）的方式来处理磁盘操作。
与AIO对应的是 Sync IO，Sync IO每次进行IO操作需要等到改操作结束才能继续接下来的操作。但是如果用户是一条索引扫描的查询，那么需要扫描索引页，也就是需要进行多次的IO操作。如果是这样的话就没有必要了。

用户发送一个IO请求就可以发送另一个IO请求，当全部发送完毕，等待所有的IO操作的完成，这就是AIO。
AIO的另一个优势可以进行IO Merge操作，也就是将多个IO何必为一个IO。
若通过Linux操作系统下的iostat 命令可以观察 rrqm/s和wrqm/s。
InnoDB 1.1.x 开始（InnoDB Plugin 不支持）提供了内核级别AIO的支持，称为 NatIve AIO。因此编译或者运行该版本MySQL时需要libaio的支持。若没有则会出现如下的提示：

Native AIO只有windows 和linux系统支持。Mac OSX不支持。参数innodb_use_native_aio 控制师傅启动 liunx默认不启动。

SHOW VARIABLES LIKE ‘innodb_use_native_aio’\G

官方显示，启用NatIve AIO，恢复速度可以提高75%。
read ahead方式读取都是通过AIO完成，脏页的刷新和磁盘写入操作则全部由AIO完成。

刷新邻接页

InnoDB存储引擎还提供了Flush Neighbor Page(刷新邻接页)的特性。工作原理：当刷新脏页时，InnoDB存储引擎会检测所在区的所有页，如果是脏页，那么一起进行刷新。
参数innodb_flush_neighbors 控制是否启用改特性.如果是机械硬盘建议启用，如果是固态硬盘就不建议使用 IOPS性能的磁盘则建议设置为0，关闭此特性。

启动、关闭和恢复

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

当正常关闭MySQL数据库时，下次的启动应该会非常“正常”。但是没有正常关闭如用kill命令关闭数据库，在MySQL 数据库运行中重启服务器，或者关闭数据库时，将参数innodb_fast_shuidown设为了2时,下次MySQL数据库启动时都会对InnoDB存储引擎的表进行恢复操作。

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

如果用户知道怎么进行恢复比如进行alter table操作是发生意外了，数据库重启会对InnoDB进行回滚操作，对于大表涞水需要很长时间，所以可以把表删除，从备份中导入数据到表。速度就会比回滚操作快。

如果innodb_force_recovery 设置为3 则不需要回滚,因此数据库很快就启动完成了。但是需要仔细确认是否需要回滚事务操作。