mysql innodb内幕_MySql技术内幕 - InnoDB概述

一、InnoDB存储引擎的版本

InnoDB存储引擎被包含于所有MySql数据库的二进制发行版本中，早起跟着数据库一起更新，但是从Mysql 5.1开始允许通过动态加载，这样InnoDB的更新就不受MySql的限制了。

所以在5.1版本中，可以支持两个版本的InnoDB，一个是静态编译的版本，一个是动态加载的版本，官方称这个动态加载的版本为InnoDB Plugin，可将其视为 1.0x版本。

在Mysql 5.5版本中又将InnoDB升级到 1.1x版本，而现在最新的MySql 5.6版本中又升级到了InnoDB 1.2x版本。

这里介绍一下InnoDB 各个版本的变化，为后面理解InnoDB功能的变化埋下种子。

InnoDB各版本功能对比

版本

功能

老版本InnoDB

支持ACID，行锁设计，MVCC

InnoDB 1.0x

继承了上述版本的所有功能，增加了compress和dynamic页格式

InnoDB 1.1x

继承了上述版本的所有功能，增加了Linux AIO，多回滚段

InnoDB 1.2x

继承了上述版本的所有功能，增加了全文索引的支持，在线索引添加

二、InnoDB 体系结构

InnoDB存储引擎有多个内存块，组成了一个大的内存池。

同时后台也存在很多后台进程，这些后台进程主要作用是负责刷新内存池中的数据，保证缓冲池中的内存是最近的数据。此外将已修改的数据文件刷新到磁盘，同时保证数据库发生异常的情况时InnoDB能够正常的运行。

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

Master Thread

主要负责将缓冲池中的数据异步的刷新到磁盘，保证数据的一致性，包括脏页的刷新，合并插入缓冲(Insert Buffer),UNDO 页的回收等等

IO Thread

InnoDB中大量的使用了AIO来完成IO请求，而IO Thread线程主要是负责这些IO请求的回调。

在InnoDB 1.0之前的版本一共有四个IO Thread，分别是write , read , insert buffer  和 log IO Thread 。在Linux中是不能修改线程的数目的，

但是在Windows中可以通过 innodb_file_io_threads 来增大IO Thread 。

从InnoDB 1.0x 版本开始read thread 和 write thread 增大到4个线程，并且不再使用innodb_file_io_threads 了

改成innodb_read_io_threads 和  innodb_write_io_threads  进行设置。

可以通过  show variables like 'innodb_version' \G 来进行查看InnoDB的版本

show variables like 'innodb_%io_threads'\G 来查看当前设置的线程数

show engine innodb status \G  查看IO Thread

Purge Thread

事务被提交之后，其所使用的undo log可能不再需要了，因此需要Purge Thread来回收已经分配并且使用的undo页。在InnoDB 1.1x之前，这个工作都是在Master Thread中完成的

后来才单独启动一个线程专门处理，提高CPU的使用率，在InnoDB 1.1x版本中这个线程的数目只能为1，即使改变了也会报错，从InnoDB 1.2x版本开始才可以改变线程的数目。

Page Cleaner Thread

这个线程是在InnoDB 1.2x 版本引入的，作用是将之前版本中脏页的刷新操作放到单独的线程中来。也是为了减轻Master Thread的压力，减少用户查询的阻塞。

三、内存

MySql的数据文件都是存储在磁盘上的，并将其按照页的方式进行管理。但是CPU速度远远高于磁盘的处理速度，所以基于磁盘的数据库基本都会采用缓冲池的技术来提高数据库的性能。

数据读取的时候，首先判断当前要读取的页在缓冲池中是否有，如果有则直接查找记录返回。如果没有则先去磁盘上把对应的数据页读取到缓冲池中，然后再读取数据返回结果。

同样数据的修改操作也是直接在缓冲池中进行的，然后再以一定的频率刷新到磁盘上，这里需要注意的一点是，将数据页从缓冲池中刷回到磁盘上，并不是在每次页发生更新时就触发，而是通过一种checkpoint的机制刷新回磁盘，这样也是为了整体的提高数据库的性能。

缓冲池的大小由：innodb_buffer_pool_size来设置，下图展示的是缓冲池中都包括什么内容。

从InnoDB 1.0x 版本开始，允许存在多个缓冲池实例，每个页根据哈希值平均分配到不同的缓冲池实例中，这样做可以减少内部资源竞争，提高并发性能。

可以通过innodb_buffer_pool_instances 参数来进行配置。

select pool_id,pool_size,free_buffers,database_pages from innodb_buffer_pool_stats

来查看缓冲池的使用状态。

LRU List

上图可以看到在缓冲池中不仅仅存放数据页，同时还保存了很多其他数据。那么这些页的数据是怎么管理的呢？

在Mysql中是通过LRU来管理数据页的，在InnoDB 存储引擎中，缓冲池中的数据页的大小默认是16K。

这里与传统的LRU算法有所不同，MySql在LRU算法中增加了一个midpoint的位置，新读取到的页，虽然是新访问到的，但是不会将其放到LRU列表的首部，而是放到LRU列表

的midpoint的位置，这个算法在InnoDB存储引擎下称为 midpoint insertion strategy。

在默认的配置下midpoint是通过： innodb_old_blocks_pct 来控制，默认值是37% ，表示新读取到数据页插入到距离LRU列表尾部37%的位置上。

把在midpoint之后的列表称为old列表，之前的列表称为new列表，new列表里面的数据是最为活跃的数据页。

这里为什么不直接把新读取的数据页直接放到LRU列表的首部呢？

是因为如果SQL执行索引或者数据的扫描操作，这类操作会访问列表中的很多数据页，甚至是全部到页，而通常这类操作的数据页仅仅是在这次操作中需要，

并不是活跃的热点数据。所以MySql为了保护热点数据，将新读取的数据插入到midpoint位置。

同时还通过另外一个参数：innodb_old_blocks_time , 来控制数据页被读取到midpoint位置需要等多久之后才会被加入到LRU列表的首部热端。

Free List

在数据库刚才启动的时候，LRU列表是空的，这时全部的页都是存放在Free列表中的，当需要冲缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页

如果存在，则在Free List中删除，添加到LRU List中，若LRU List中淘汰的数据页，在LRU List中删除，添加到Free LIst 中。

page made young :  页从LRU列表的old部分加入到new部分

page not made young : 页因为innodb_old_blocks_time 而没有从old部分移动到new 部分

InnoDB 从 1.0x版本开始支持压缩页功能，既将原本16K的页，压缩为1K，2K，4K，8K。对于非16K的页，是通过unzip_LRU List进行管理的

通过：show variables innodb status , 可以看到unzip_LRU 和 LRU 的长度。

不过这里要知道，在LRU中是包含了unzip_LRU列表的页的。

Flush List

如果在缓冲池中的数据也被修改了，与磁盘上的数据页不一致，这种页被称为脏页，这种页需要通过checkpoint机制刷回到磁盘上。

那么存在与Flush List中的页即为脏页，这里也需要注意的是，脏页同时也存在与LRU列表中

LRU列表是用来维护页的可用性，Flush列表是用来管理将页刷新回磁盘的，二者并不影响。

重做日志缓冲池：

在innoDB缓冲池之外，还有一个重做日志缓冲区，InnoDB首先将redo log写入到这个缓冲区，然后按照一定的频率将其刷新到重做日志文件中。

重做日志一般不需要设置的很大，因为一般情况下每一秒钟会将重做日志刷新到文件，用户只需要保证每秒钟产生的事务量在这个缓冲区的大小

范围之内即可，该缓冲区的大小由：innodb_log_buffer_size来控制。

在下面三种情况下 ， InnoDB会将缓冲区的日志刷新到日志文件中：

Master Thread 每一秒钟将重做日志缓冲刷新到重做日志文件中

每个事务提交时会将重做日志缓冲区的内容刷新到重做日志文件中

当重做日志缓冲剩余空间小于1/2时，会将缓冲区的内容刷新到重做日志文件中

额外的内存池：

常常被忽略的一个部分，这里存储的对象都是一些基本结构对象，就是我们在缓冲池中的一些LRU对象，索引对象，锁对象等等，都是在这部分定义的

因此如果我们申请了一个很大的缓冲池的时候，也应该考虑这部分空间应该响应的增大。

四、Checkpoint技术

数据库在读取数据页的时候，是先要判断需要读取的数据页是否在缓冲池中，没有则需要先从磁盘上将数据页读取到缓冲池上，再进行操作。

DML的操作也同样如此，但是如果修改操作直接是在缓冲池中修改了，这个时候存在与缓冲池中数据页与磁盘上的数据页的内容就不一样了，这个时候存在于缓冲池

中的数据页就叫做脏页，只有将脏页刷新回到磁盘才能保证数据的持久性。

如果每次数据页的修改，都将缓冲池中的内容刷新回磁盘上，那么如果数据的请求集中到几个数据页上时，数据库的性能就会变得非常慢，而且如果在将脏页写回到

磁盘的过程中出现意外情况，那么数据就会发生丢失，而且无法恢复了。所以当前的数据库系统普遍采用了Write Ahead Log (WAL)的策略，既当事务提交时，先写重做日志，

再修改页，这样如果在将缓冲池中数据页刷新到磁盘时发生了意外，在数据库重启的时候，仍然可以通过重做日志来恢复已经完成的事务的数据。

所以WAL在保证数据持久型方面起到了重要的作用，在MySql中采用Checkpoint机制来保证的，主要解决了下面几个问题：

缩短数据库的恢复时间

缓冲池不够用时，将脏页刷新到磁盘上

重做日志不可用时，刷新脏页

基于上面几点作用，当数据库再发生宕机时，数据库不需要重做所有的日志，因为在Checkpoint之前的页都已经刷回到磁盘上了，只需要对Checkpoint后的日志进行恢复

这样就大大的缩短了数据恢复的时间。

在MySql中分为两种Checkpoint:

Sharp Checkpoint

Fuzzy Checkpoint

Sharp Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，通过参数innodb_fast_shutdown = 1

而Fuzzy Checkpoint是在数据库运行中将部分脏页刷新回磁盘，而Fuzzy Checkpoint又分一下几种情况：

Master Thread Checkpoint

FLUSH_LRU_LIST Checkpoint

Async/Sync Flush CHeckpoint

Ditry Pages too much Checkpoint

在Master Thread 中会以每秒和每十秒的频率将脏页从缓冲池中刷新回磁盘，这个过程是异步的，此时Master Thread还可以处理用户的请求，用户的查询不会被阻塞。

FLUSH_LRU_LIST Checkpoint是因为innoDB 引擎需要保证LRU列表中差不多有100个空闲页可用，如果没有的话，会将LRU列表后面的页移除，如果待移除的数据页是脏页的话

那么就需要进行Checkpoint ，只是因为这些数据页是来自LRU的，所以就叫做FLUSH_LRU_LIST Checkpoint。

从InnoDB 1.2x开始，这个LRU列表的检查被单独放到了一个线程中，就是Page Cleaner。并且可以通过参数innodb_lru_scan_depth控制LRU列表可用页的数量，默认值是1024。

Async/Sync Flush Checkpoint指的是重做日志文件不可用的情况下，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页中选取的，若将已经写入到重做日志的LSN记为redo_lsn

将刷新到磁盘最新页的LSN计作checkpoint_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

若每个重做日志文件的大小是1GB，并且定义了两个重做日志文件，那么async_water_mark = 1.5GB , sync_water_mark = 1.8GB

当checkpoint_age

当async_water_mark < checkpoint_age < sync_water_mark时，触发Async Flush，从Flush列表中刷新足够的脏页到磁盘，是其满足第一个条件

当checkpoint_age > sync_water_mark时，除非设置的重做日志文件太小，并且在进行类似LOAD DATA的BULK INSERT操作，此时触发了Sync Flush操作，从Flush中刷新足够的页

可见Async/Sync FLush Checkpoint是为了保证重做日志的循环利用，因为之前Async或者Sync操作会阻碍用户线程，从1.2x 版本之后，将这部分工作迁移到了Page Claner Thread中。

最后一种Dirty Page too much 就是针对缓冲池中的脏页过多而强制进行的Checkpoint，目的就是保证缓冲区中有足够的可用的页。

可以通过参数 innodb_max_dirty_pages_pct来控制，如果数值为75，则表示当缓冲池中脏页数量占75%的时候，就会强制进行Checkpoint，刷新一部分脏页回磁盘。

五、Master Thread的工作方式

说到Master Thread就必须要提及InnoDB 的版本了，在1.0x版本的时候，Master Thread具有很高的优先级，内部有很多的loop，包括主循环loop，后台循环，刷新循环，暂停循环。

Master Thread会根据数据库的状态，在这几个循环中进行切换。

主循环：主要分为每秒操作和每十秒操作，这里Mysql是通过thread sleep的方式来实现的，但是这可能会因为一些情况导致不那么的准确

每一秒操作：

日志缓冲刷新到磁盘，即使这个事务还没提交

合并插入缓冲

至多刷新100个InnoDB的缓冲池中的脏页到磁盘

如果当前没有用户活动，则切换到后台循环状态

即使某个事务还没有提交，InnoDB存储引擎仍然会每秒将重做日志缓冲刷新到重做日志文件中，这也是为什么即使再大的事务提交的时间都是很短的。

合并插入不是每秒都会发生的，InnoDB会判断前一秒发生IO的次数是否小于5次，如果小于5次，则认为当前的压力很小，可以执行合并插入。

同样刷新100个脏页到磁盘也不是每次都发生，innoDB通过判断当前缓冲池中脏页的比例：buf_get_modified_ratio_pct是否已经超过了配置文件中

的innodb_max_dirty_pct这个阈值。

每十秒操作：

刷新100个脏页到磁盘

合并至多5个插入缓冲

将日志缓冲刷新到磁盘

删除无用的undo页

刷新100个或者10个脏页到磁盘

以上的过程中，InnoDB存储引擎会判断过去10秒磁盘IO的操作是否小于200次，如果是，那么会认为当前有足够的IO能力，因此将刷新100个脏页到磁盘，接着会进行

合并插入缓冲，不同于一秒的行为，这个合并插入缓冲是总是会进行的。之后InnoDB 会在进行一次将日志缓冲刷新到磁盘的操作，这与每一秒中的是一样的。

接着InnoDB会进行一次full purge操作，即删除无用的Undo Page页，最多会回收20个Undo Page。

通过对主循环的了解可以知道，其实Mysql是对IO有限制的，但是现在的磁盘的读写速度已经发生了很大的改变，尤其面对是SSD时，其实这种IO的限制是影响到了MySql的性能的。

因为无论什么情况，MySql最大只会刷新100个脏页，合并20个插入缓冲，即使是我们的磁盘能够承受比这个更大的写入，但是MySql也不会改变操作的数目。

后来从1.0x版本提供了innodb_io_capacity这个参数，用于表示磁盘的吞吐量，默认为200，对于刷新回磁盘的页的数量，会根据这个数值的百分比来进行控制：

合并插入缓冲时，为innodb_io_capacity的5%

在从缓冲区刷新脏页时，刷新数量为innodb_io_capacity

在InnoDB 1.2x 版本开始，将Master Thread中的一部分工作迁移到了单独的线程中，将对脏页的处理分离到了一个单独的Page Cleaner Thread中。

六、InnoDB关键特性

1.插入缓冲

在MySql中主键一般是按照顺序自增的，因此插入也是有序的。因此聚集索引(Primary Key)一般都是顺序的，不需要随机读取。

但是一张表中除了聚集索引之外肯定还会存在非聚集索引(Secondary Index),而且还不是唯一的。那么在其插入的时候，数据页还是按照主键索引存放的

但是对于非聚集索引叶子节点的插入不再是顺序的了，就需要离散的访问非聚集索引的页，由于随机读取的存在导致了插入操作的性能下降了。

针对这种情况InnoDB设计了Insert Buffer，对于非聚集索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断需要插入的非聚集索引页

是否存在于缓冲池中，若存在，则直接插入，若不存在，则先放到Insert Buffer对象中，然后再按照一定的频率与索引页进行合并，提高了效率。

Insert Buffer的使用需要同时满足一下两个条件：

索引是非聚集索引

索引不是唯一的

Change Buffer是在1.0x 版本引入的，作用与Insert Buffer类似，是对DML操作(insert，delete , update)都进行缓冲，他们分别是Insert Buffer , Delete Buffer , Purge Buffer

和之前的Insert Buffer一样，都是只适用于非聚集索引。

2.两次写(Doublewrite)

考虑一种情况，如果一个数据页正在缓冲池中，这个时候innoDB正在将该页写入到磁盘上，但是在写入一部分的时候，发生了宕机。这种情况叫做部分写失效。

可能有人会说我们可以通过重做日志来恢复啊，但是有一点我们要知道，重做日志是一个逻辑日志，但是如果这个数据页本身已经发生来损坏，那么再通过重做日志

去恢复是没有意义的，而这个时候就需要doublewrite来处理了。

doublewrite由两部分组成，一部分是在内存中的doublewrite buffer，大小为2MB，另一部分在磁盘上共享表空间中的连续128个页，大小同样为2MB。

在对缓冲池中的脏页进行刷新时，并不是直接的写入磁盘，而是会通过memory函数将脏页先复制到内存中的doublewrite buffer中，之后通过doublewrite buffer

再分两次每次1MB的顺序写入到共享表空间的磁盘上，然后马上调用fsync同步磁盘，避免缓冲写带来的问题。在这个过程中doublewrite是连续的，

因此这个过程是顺序的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer中的页内容写入到各个表空间。

这样就算是发生了宕机，我们也可以从共享表空间中找到当前数据页，然后恢复到对应的表空间中，然后再通过重做日志来恢复其数据。

3.自适应哈希索引

哈希是一种非常快的查找索引，但是其不支持范围查找，所以可以使用的场景是有局限的。

但是在MySql中的大部分索引都是B+ Tree的，这样其查找的次数就会取决于树的高度。这里InnoDB 会监控各个索引页是查询，如果观察到通过哈希索引可以提升查询速度

那么就会建立哈希索引，用来完成对某些热点页的哈希索引。这里也是有一个要求，就是这个页的访问必须模式必须是一样的

因为建立自适应哈希索引是key是我们的查询，如果查询的参数变化了，那么对应的哈希值肯定就是不同的了。

4.异步IO

为了提高磁盘的性能，大多数的数据库系统都会采用异步IO

与AIO对应的Sync IO，就是每次IO操作，需要等待此次操作结束才能继续接下来的操作，但是如果用户发出的是一条索引扫描查询，那么这条查询语句可能需要扫描多个索引页

也就是需要多次IO的交互。在每次扫描一个页并等待其完成后在进行下一次的扫描，这是完全没有必要的。

用户可以在一个IO请求发出之后，再发出另外一个IO请求，当全部的IO请求发送完毕之后，等待所有IO的操作结果，这就是AIO。

同时AIO的另外一个好处就是可以进行IO Merge，这样也是可以提高整体的性能。

在InnoDB 1.1x之前AIO的实现是通过引擎中的代码来模拟实现的，但是总1.1x版本开始，提供了内核级别的AIO，性能提高的更快。

5.刷新邻接页

工作原理是当刷新一个脏页的时候，InnoDB存储引擎会检测该页所在区域所有的页，如果也存在脏页，那么会一起刷新，配合着AIO也可以大大的提供数据库的希能。