MySQL高级进阶(三)、InnoDB存储引擎详解

小景哥哥博客

InnoDB是事务安全的MySQL存储引擎，设计上采用了类似于Oracle数据库的架构。通常来说，InnoDB存储引擎是OLTP应用中核心表的首选存储引擎。其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效地使用内存和CPU。

• 后台线程

​ InnoDB存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲(insert buffer)、UNDO页的回收等。在InnoDB存储引擎中大量使用了AIO(Async IO)来处理写IO请求，这样可以极大提高数据库的性能，而IO Thread的工作主要是负责这些IO请求的回调(call back)处理。IO Thread分别有write、read、insert buffer和log IO Thread。

​ 事务被提交后，其所使用的undolog可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页。在InnoDB 1.1版本之前，purge操作仅在InnoDB存储引擎的Master Thread中完成。而从InnoDB 1.1版本开始，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能。从InnoDB 1.2开始，InnoDB支持多个Purge Thread，这样做的目的是为了进一步加快undo页的回收。同时由于Purge Thread需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能。

​ Purge Cleaner Thread是在InnoDB 1.2.x版本引入的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

• 内存

​ InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。为了协调CPU速度和磁盘速度的鸿沟，基于磁盘的的数据库系统通常使用缓冲池技术来提高数据库的性能。

​ 对于数据库中页的修改操作，首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为checkpoint的机制刷新回磁盘。

​ 缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲(insert buffer)、自适应哈希索引(adaptive hash index)、InnoDB存储的锁信息(lock info)、数据字典信息(data dictionary)等，不能简单地认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大的一部分而已。

• LRU List、Free List、Flush List

​ 数据库的缓冲池是通过LRU(Latest Recent Used, 最近最少使用)算法来进行管理的。即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。在InnoDB存储引擎中，缓冲池中页的大小默认为16KB，同样使用LRU算法对缓冲池进行管理。并且InnoDB存储引擎对LRU算法进行了一些改进，LRU列表中还加入了midpoint位置。新读取到的页，虽然是最新访问的页，但并不是直接插入到LRU列表的首部，而是放入到LRU列表的midpoint位置。这个算法在InnoDB存储引擎下称为midpoint insertion strategy。默认配置下，该位置在LRU列表长度的 5/8处。把midpoint之后的列表称为old列表，之前的表称为new列表。可以简单地理解为new列表中的页都是最为活跃的热点数据。

​ 改进之后的LRU算法的优点：若直接读取到的页放入到LRU的首部，那么某些SQL操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说又仅仅在这次查询操作中需要，并不是活跃的热点数据。如果页被放入LRU列表的首部，那么非常可能将所需要的热点数据页从LRU列表中移除，而在下一次需要读取该页时，InnoDB存储引擎需要再次访问磁盘。为了解决这个问题，InnoDB存储引擎引入了另一个参数来进一步管理LRU列表，这个参数是innodb_old_blocks_time，用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。

​ LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。否则根据LRU算法，淘汰LRU列表末端的页，将该内存空间分配给新的页。当页从LRU列表的old部分加入到new部分时，称此时发生的操作为page made young，而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page not made young。

​ InnoDB从1.0.x开始支持压缩页的功能，即将原本16KB的页压缩为1KB、2BK、4KB、8KB。对于非16KB的页，是通过unzip_LRU列表进行管理的，通过伙伴算法进行内存的分配。例如从缓冲池中申请大小为4KB的页：

1. 检查4KB的unzip_LRU列表，检查是否有可用的空闲页；
2. 若有，则直接使用；
3. 否则，检查8KB的unzip_LRU列表；
4. 若能够得到空闲页，将页分为2个4KB页，存放到4KB的unzip_LRU列表；
5. 若不能得到空闲页，从LRU列表中申请一个16KB的页，将页分为1个8KB的页、2个4KB的页，分别存放到对应的unzip_LRU列表中。

​ 在LRU列表中的页被修改后，称该页为脏页(dirty page)，即缓冲池中的页和磁盘上的页的数据产生不一致。数据库通过checkpoint机制将脏页刷新回磁盘。Flush列表中的页即为脏页列表。脏页既存在于LRU列表中，也存在于Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

​ 重做日志缓冲(redo log buffer)存放着InnoDB存储引擎的重做日志信息，它按照一定的频率将重做日志刷新到重做日志文件。默认8MB的重做日志缓冲池足以满足绝大多数的应用。重做日志在以下三种情况下会将重做日志缓冲区中的内容刷新到外部磁盘的重做日志文件中。

1. Master Thread每一秒将重做日志缓冲刷新到重做日志文件；

2. 每个事务提交时会将重做日志缓冲刷新到重做日志文件；

3. 当重做日志缓冲池剩余空间小于1\2时，重做日志缓冲刷新到重做日志文件。

• Checkpoint技术

​ 为了避免发生数据丢失的问题，当前事务数据库系统普遍采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。

​ Checkpoint(检查点)可以缩短数据库的恢复时间；缓冲池不够用时，可将脏页刷新到磁盘；重做日志不可用时，刷新脏页。

​ 对于InnoDB存储引擎而言，其是通过LSN(Log Sequence Number)来标记版本的。LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。InnoDB内部有两种Checkpoint，Sharp Checkpoint和Fuzzy Checkpoint。Sharp Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘，也是默认的工作方式，即innodb_fast_shutdown=1。若数据库在运行时也使用Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在InnoDB存储引擎内部使用Fuzzy Checkpoint进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘。

​ InnoDB存储引擎的主要工作都是在一个单独的后台线程Master Thread中完成的。Master Thread具有最高的线程优先级别。其内部由多个循环(loop)组成：主循环(loop)、后台循环(backgroup loop)、刷新循环(flush loop)、暂停循环(suspend loop)。Master Thread会根据数据库运行的状态在loop、background loop、flush loop和suspend loop中进行切换。

​ Loop被称为主循环，因为大多数的操作是在这个循环中，其中有两大部分的操作——每秒的操作和每10秒的操作。

• 每秒一次的操作
  • 日志缓冲刷新到磁盘，即使这个事务还没有提交(总是)；
  • 合并插入缓冲(可能)；
  • 至多刷新100个InnoDB的缓冲池中的脏页到磁盘(可能)；
  • 如果当前没有用户活动，则切换到background loop(可能)。

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

​ 合并插入缓冲(Insert Buffer)并不是每秒都会发生的。InnoDB存储引擎会判断当前一秒发生的IO次数是否小于5次，如果小于5次，InnoDB认为当前的IO压力很小，可以执行合并插入缓冲的操作。

​ 刷新100个脏页也不是每秒都会发生的。InnoDB存储引擎通过判断当前缓冲池中脏页的比例(buf_get_modified_ratio_pct)是否超过了配置文件中innodb_max_dirty_pages_pct这个参数(默认为90，代表90%)，如果超过了这个阈值，InnoDB存储引擎认为需要做磁盘同步的操作，将100个脏页写入磁盘中。

• 每10秒的操作
  • 刷新100个脏页到磁盘(可能)；
  • 合并至多5个插入缓冲(总是)；
  • 将日志缓冲刷新到磁盘(总是)；
  • 删除无用的Undo页(总是)；
  • 刷新100个或者10个脏页到磁盘(总是)。

​ 在以上的过程中，InnoDB存储引擎会先判断过去10秒之内磁盘的IO操作是否小于200次，如果是，InnoDB存储引擎认为当前有足够的磁盘IO操作能力，因此将100个脏页刷新到磁盘。接着，InnoDB存储引擎会合并插入缓冲。不同于每秒一次操作时可能发生的合并插入缓冲操作，这次的合并插入操作总会在这个阶段进行。之后，InnoDB存储引擎会再进行一次将日志缓冲刷新到磁盘的操作。这和每秒一次时发生的操作是一样的。

​ 接着，InnoDB存储引擎会进行一步执行full purge操作，即删除无用的Undo页。对表进行update、delete这类操作时，原先的行被标记为删除，但是因为一致性读(consistent read)的关系，需要保留这些行的版本信息。但是在full purge过程中，InnoDB存储引擎会判断当前事务系统中已被删除的行是否可以删除，如果有时候可能还有查询操作需要读取之前版本的undo信息，如果乐意删除，InnoDB会立即将其删除。InnoDB存储引擎在执行full purge操作时，每次最多尝试回收20个undo页。

​ InnoDB存储引擎会判断缓冲池脏页的比例(buf_get_modified_ratio_pct)，如果有超过70%的脏页，则刷新100个脏页到磁盘，如果脏页的比例小于70%，则刷新10%的脏页到磁盘。

​ 若当前没有用户活动(数据库空闲时)或者数据库关闭，就会切换到background loop。

• background loop执行的操作
  • 删除无用的Undo页(总是)；
  • 合并20个插入缓冲(总是)；
  • 跳回到主循环(总是)；
  • 不断刷新100个页直到符合条件(可能，跳转到flush loop中完成)。

​ 若flush loop中也没有什么事情可做了，InnoDB存储引擎会切换到suspend loop，将Master Thread挂起，等待事件的发生。若用户启用了InnoDB存储引擎，却没有任何InnoDB存储引擎的表，那么Master Thread总是处于挂起的状态。

• InnoDB存储引擎关键特性

  • 插入缓冲(Insert Buffer)

  • 两次写(Double Write)

  • 自适应哈希索引(Adaptive Hash Index)

  • 异步IO(Async IO)

  • 刷新邻接页(Flush Neighbor Page)

• 插入缓冲(Insert Buffer)

​ InnoDB存储引擎开创性地设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放到一个Insert Buffer对象中。数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge操作，这时通常能够将多个插入合并到一个操作中(因为在一个索引页中)，这就大大提高了对于非聚集索引插入的性能。Insert Buffer的使用需要同时满足两个条件：索引是辅助索引(secondary index)、索引不是唯一的。

​ InnoDB从1.0.x版本开始引入了Change Buffer，可将其视为Insert Buffer的升级。InnoDB存储引擎可以对DML(insert、delete、update)都进行缓冲，分别对应于Insert Buffer、Delete Buffer、Purge Buffer。Change Buffer适用的对象依然是非唯一的辅助索引。

​ Insert Buffer的数据结构是一棵B+树，在MySQL4.1之前的版本中每张表有一棵Insert Buffer B+树。而在现在的版本中，全局只有一棵Insert Buffer B+树，负责对所有的辅助索引进行Insert Buffer。而这棵B+树存放在共享表空间中，默认也就是ibdata1中。

• 两次写(Double Write)

​ 如果说Insert Buffer带给InnoDB存储引擎的是性能上的提升，那么doublewrite(两次写)带给InnoDB存储引擎的是数据页的可靠性。

​ 在应用重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewrite。doublewrite由两部分组成，一部分是内存中的doublewrite buffer，大小为2MB，另一部分是物理磁盘上共享表空间中连续的128个页，即2个区(extent)，大小同样为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次、每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间文件中，此时的写入则是离散的。

​ 如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的副本将其复制到表空间文件，再应用重做日志。

• 自适应哈希索引(Adaptive Hash Index)

​ 哈希(hash)是一种非常快的查找方法，在一般情况下这种查找的时间复杂度为O(1)，即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3~4层，故需要3~4次的查询。

​ InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表结构建立哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。AHI有一个要求，即对这个页的连续访问模式必须是一样的。启用AHI后，读取和写入的速度可以提高2倍，辅助索引的连续操作性能可以提高5倍。

• 异步IO (Async IO)

​ 为了提高磁盘操作性能，当前的数据库系统都采用异步IO (Asynchronous IO, AIO)的方式来处理磁盘操作。InnoDB存储引擎亦是如此。用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部IO请求发送完毕后，等待所有IO操作的完成，这就是AIO，这样可以提高IOPS的性能。InnoDB 1.1.x开始，提供了内核级别AIO的支持，称为Native AIO。

• 刷新邻接页(Flush Neighbor Page)

​ InnoDB存储引擎提供了Flush Neighbor Page(刷新邻接页)的特性：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区(extend)的所有页，如果是脏页，那么一起进行刷新。好处显而易见，通过AIO可以将多个IO写入操作合并为一个IO操作，故该工作机制在传统机械硬盘下有着显著的优势。