3、InnoDB存储引擎

一、InnoDB体系架构

InnoDB存储引擎有多个内存块，这些内存块组成了一个大的内存池。后台线程主要负责刷新内存池中的数据、将已修改的数据刷新到磁盘。

1.1 后台线程

InnoDB后台有多个不同的线程，用来负责不同的任务。主要有如下：

• Master Thread

  这是最核心的一个线程,主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括赃页的刷新、合并插入缓冲、UNDO 页的回收等.

• IO Thread

  在 InnoDB 存储引擎中大量使用了异步 IO 来处理写 IO 请求, IO Thread 的工作主要是负责这些 IO请求的回调处理

• Purge Thread

  事务被提交之后, undo log 可能不再需要,因此需要 Purge Thread 来回收已经使用并分配的 undo页. InnoDB 支持多个 Purge Thread, 这样做可以加快 undo 页的回收。

• Page Cleaner Thread

  Page Cleaner Thread 是在InnoDB 1.2.x版本新引入的,其作用是将之前版本中脏页的刷新操作都放入单独的线程中来完成,这样减轻了 Master Thread 的工作及对于用户查询线程的阻塞。

1.2 内存

InnoDB 存储引擎是基于磁盘存储的,也就是说数据都是存储在磁盘上的,由于 CPU 速度和磁盘速度之间的鸿沟，InnoDB 引擎使用缓冲池技术来提高数据库的整体性能。

缓冲池（innoDB_buffer_pool）简单来说就是一块内存区域.在数据库中进行读取页的操作,首先将从磁盘读到的页存放在缓冲池中,下一次读取相同的页时,首先判断该页是不是在缓冲池中,若在,称该页在缓冲池中被命中,直接读取该页。

否则,读取磁盘上的页。对于数据库中页的修改操作,首先修改在缓冲池中页,然后再以一定的频率刷新到磁盘,并不是每次页发生改变就刷新回磁盘。

缓冲池中缓存的数据页类型有:索引页、数据页、 undo 页、插入缓冲、自适应哈希索引、 InnoDB 的锁信息、数据字典信息等。索引页和数据页占缓冲池的很大一部分。在InnoDB中，缓冲池中的页大小默认为16KB。

我们已经知道这个Buffer Pool其实是一片连续的内存空间，那现在就面临这个问题了：怎么将磁盘上的页缓存到内存中的Buffer Pool中呢？直接把需要缓存的页向Buffer Pool里一个一个往里怼么？NO，为了更好的管理这些被缓存的页，

InnoDB为每一个缓存页都创建了一些所谓的控制信息，这些控制信息包括该页所属的表空间编号、页号、页在Buffer Pool中的地址，一些锁信息以及LSN信息（锁和LSN这里可以先忽略），当然还有一些别的控制信息。

每个缓存页对应的控制信息占用的内存大小是相同的，我们就把每个页对应的控制信息占用的一块内存称为一个控制块吧，控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存放到Buffer Pool 的前边，

缓存页被存放到 Buffer Pool 后边，所以整个Buffer Pool对应的内存空间看起来就是这样的：

| 控制块1 | 控制块2 | 控制块3 | 碎片 | 缓存页3 | 缓存页2 | 缓存页1 |控制块1——>缓存页1，

控制块2——>缓存页2，

控制块3——>缓存页3

控制块和缓存页之间的那个碎片是个什么呢？你想想啊，每一个控制块都对应一个缓存页，那在分配足够多的控制块和缓存页后，可能剩余的那点儿空间不够一对控制块和缓存页的大小，自然就用不到喽，这个用不到的那点儿内存空间就被称为碎片了。

当然，如果你把Buffer Pool的大小设置的刚刚好的话，也可能不会产生碎片

前面我们知道了缓冲池的结构。接下来说InnoDB存储引擎是怎么对缓冲池进行管理的。推荐先去看看《Mysql技术内幕》这本书

free list补充：

当我们最初启动MySQL服务器的时候，需要完成对Buffer Pool的初始化过程，就是分配Buffer Pool的内存空间，把它划分成若干对控制块和缓存页。

但是此时并没有真实的磁盘页被缓存到Buffer Pool中（因为还没有用到），之后随着程序的运行，会不断的有磁盘上的页被缓存到Buffer Pool中，

问：从磁盘上读取一个页到Buffer Pool中的时候该放到哪个缓存页的位置呢？或者说怎么区分Buffer Pool中哪些缓存页是空闲的，哪些已经被使用了呢？

答：我们最好在某个地方记录一下哪些页是可用的，我们可以把所有空闲的页包装成一个节点组成一个链表，这个链表也可以被称作Free链表（或者说空闲链表）。

因为刚刚完成初始化的Buffer Pool中所有的缓存页都是空闲的，所以每一个缓存页都会被加入到Free链表中，假设该Buffer Pool中可容纳的缓存页数量为n，那增加了Free链表的效果图就是这样的：

从图中可以看出，我们为了管理好这个Free链表，特意为这个链表定义了一个控制信息，里边儿包含着链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。

我们在每个Free链表的节点中都记录了某个缓存页控制块的地址，而每个缓存页控制块都记录着对应的缓存页地址，所以相当于每个Free链表节点都对应一个空闲的缓存页。

有了这个Free链表事儿就好办了，每当需要从磁盘中加载一个页到Buffer Pool中时，就从Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的Free链表节点从链表中移除，表示该缓存页已经被使用了。

简单回顾一下，为什么讲free list？是为了讲怎么管理buffer pool对吧。那free list就相当于是数据库服务刚刚启动没有数据页时，维护buffer pool的空闲缓存页的数据结构。

下面再来简单地回顾Buffer Pool的工作机制。Buffer Pool两个最主要的功能：

• 一个是加速读，加速读呢？ 就是当需要访问一个数据页面的时候，如果这个页面已经在缓存池中，那么就不再需要访问磁盘，直接从缓冲池中就能获取这个页面的内容。

• 一个是加速写。加速写呢？就是当需要修改一个页面的时候，先将这个页面在缓冲池中进行修改，记下相关的重做日志，这个页面的修改就算已经完成了。至于这个被修改的页面什么时候真正刷新到磁盘，这个是后台刷新线程来完成的。

在实现上面两个功能的同时，需要考虑客观条件的限制，因为机器的内存大小是有限的，所以MySQL的InnoDB Buffer Pool的大小同样是有限的，如果需要缓存的页占用的内存大小超过了Buffer Pool大小，

也就是Free链表中已经没有多余的空闲缓存页的时候岂不是很尴尬，发生了这样的事儿该咋办？当然是把某些旧的缓存页从Buffer Pool中移除，然后再把新的页放进来。

问：移除哪些缓存页呢？

答：为了回答这个问题，我们还需要回到我们设立Buffer Pool的初衷，我们就是想减少和磁盘的I/O交互，最好每次在访问某个页的时候它都已经被缓存到Buffer Pool中了。假设我们一共访问了n次页，

那么被访问的页已经在缓存中的次数除以n就是所谓的缓存命中率，我们的期望就是让缓存命中率越高越好。

问：怎么提高缓存命中率呢？

答：InnoDB Buffer Pool采用经典的LRU算法来进行页面淘汰，以提高缓存命中率。当Buffer Pool中不再有空闲的缓存页时，就需要淘汰掉部分最近很少使用的缓存页。

问：我们怎么知道哪些缓存页最近频繁使用，哪些最近很少使用呢？

答：神奇的链表再一次派上了用场，我们可以再创建一个链表，由于这个链表是为了按照最近最少使用的原则去淘汰缓存页的，所以这个链表可以被称为LRU链表（Least Recently Used）。

当我们需要访问某个页时，可以这样处理LRU链表：* 如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓存页时，就把该缓存页包装成节点 塞到链表的头部。

如果该页在Buffer Pool中，则直接把该页对应的LRU链表节点移动到链表的头部。

问：但是这样做会有一些性能上的问题，比如你的一次全表扫描或一次逻辑备份就把热数据给冲完了，就会导致导致缓冲池污染问题！Buffer Pool中的所有数据页都被换了一次血，其他查询语句在执行时又得执行一次从磁盘加载到Buffer Pool的操作，

而这种全表扫描的语句执行的频率也不高，每次执行都要把Buffer Pool中的缓存页换一次血，这严重的影响到其他查询对 Buffer Pool 的使用，严重的降低了缓存命中率 ！

答：所以InnoDB存储引擎对传统的LRU算法做了一些优化，在InnoDB中加入了midpoint。新读到的页，虽然是最新访问的页，但并不是直接插入到LRU列表的首部，而是插入LRU列表的midpoint位置。

这个算法称之为midpoint insertion stategy。默认配置插入到列表长度的5/8处。midpoint由参数innodb_old_blocks_pct控制。midpoint之前的列表称之为new列表，之后的列表称之为old列表。可以简单的将new列表中的页理解为最为活跃的热点数据。

同时InnoDB存储引擎还引入了innodb_old_blocks_time来表示页读取到mid位置之后需要等待多久才会被加入到LRU列表的热端。可以通过设置该参数保证热点数据不轻易被刷出。

基本拿下了LRU list后，我们继续。前面我们讲到页面更新是在缓存池中先进行的，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页（英文名：dirty page）。

问：思考一下这些被修改的页面什么时候刷新到磁盘？以什么样的顺序刷新到磁盘？

答：最简单的做法就是每发生一次修改就立即同步到磁盘上对应的页上，但是频繁的往磁盘中写数据会严重的影响程序的性能（毕竟磁盘慢的像乌龟一样）。

所以每次修改缓存页后，我们并不着急立即把修改同步到磁盘上，而是在未来的某个时间点进行同步，由后台刷新线程依次刷新到磁盘，实现修改落地到磁盘。

问：但是如果不立即同步到磁盘的话，那之后再同步的时候我们怎么知道Buffer Pool中哪些页是脏页，哪些页从来没被修改过呢？总不能把所有的缓存页都同步到磁盘上吧，假如Buffer Pool被设置的很大，

比方说300G，那一次性同步这么多数据岂不是要慢死！

答：所以，我们不得不再创建一个存储脏页的链表，凡是在LRU链表中被修改过的页都需要加入这个链表中，因为这个链表中的页都是需要被刷新到磁盘上的，所以也叫FLUSH链表，有时候也会被简写为FLU链表。

链表的构造和Free链表差不多，这里就不细讲了，这里的脏页修改指的此页被加载进Buffer Pool后第一次被修改，只有第一次被修改时才需要加入FLUSH链表

（代码中是根据Page头部的oldest_modification == 0来判断是否是第一次修改），如果这个页被再次修改就不会再放到FLUSH链表了，因为已经存在。需要注意的是，脏页数据实际还在LRU链表中，

而FLUSH链表中的脏页记录只是通过指针指向LRU链表中的脏页。并且在FLUSH链表中的脏页是根据oldest_lsn（这个值表示这个页第一次被更改时的lsn号，对应值oldest_modification，每个页头部记录）进行排序刷新到磁盘的，

值越小表示要最先被刷新，避免数据不一致。

注意：脏页既存在于LRU列表中，也存在与Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

这三个重要列表（LRU list， free list，flush list）的关系如下：

Free链表跟LRU链表的关系是相互流通的，页在这两个链表间来回置换。而FLUSH链表记录了脏页数据，也是通过指针指向了LRU链表，所以FLUSH链表被LRU链表包裹。

二、CheckPoint 技术

说完缓冲池，下面说CheckPoint技术。CheckPoint技术是用来解决如下几个问题：

• 缩短数据库恢复时间

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

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

缩短数据库恢复时间，重做日志中记录了的checkpoint的位置，这个点之前的页已经刷新回磁盘，只需要对checkpoint之后的重做日志进行恢复。这样就大大缩短了恢复时间。

缓冲池不够用时，根据LRU算法，溢出最近最少使用的页，如果页为脏页，强制执行checkpoint，将脏页刷新回磁盘。

重做日志不可用，是指重做日志的这部分不可以被覆盖，为什么？因为：由于重做日志的设计是循环使用的。这部分对应的数据还未刷新到磁盘上。数据库恢复时，如果不需要这部分日志，即可被覆盖；如果需要，必须强制执行checkpoint，

将缓冲池中的页至少刷新到当前重做日志的位置。

checkpoint每次刷新多少页到磁盘？每次从哪里取脏页？什么时间触发checkpoint？

InnoDB存储引擎内部，两种checkpoint，分别为:

• Sharp Checkpoint

  Sharp Checkpoint发生在数据库关闭时，将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数：innodb_fast_shutdown=1。不适用于数据库运行时的刷新。

• Fuzzy Checkpoint

  在数据库运行时，InnoDB存储引擎内部采用Fuzzy Checkpoint，只刷新一部分脏页。以下是几种发生Fuzzy Checkpoint的情况：

• 1. MasterThread Checkpoint 异步刷新，每秒或每10秒从缓冲池脏页列表刷新一定比例的页回磁盘。异步刷新，即此时InnoDB存储引擎可以 进行其他操作，用户查询线程不会受阻。

• 2. FLUSH_LRU_LIST Checkpoint InnoDB存储引擎需要保证LRU列表中差不多有100个空闲页可供使用。在InnoDB 1.1.x版本之前，用户查询线程 会检查LRU列表是否有足够的空间操作。如果没有，根据LRU算法，

  溢出LRU列表尾端的页，如果这些页有脏页， 需要进行checkpoint。因此叫：flush_lru_list checkpoint。

InnoDB 1.2.x开始，这个检查放在了单独的进程（Page Cleaner）中进行。好处：

1.减少master Thread的 压力

2.减轻用户线程阻塞。 设置参数：innodb_lru_scan_dept：控制LRU列表中可用页的数量，该值默认10243.Async/Sync Flush Checkpoint 指重做日志不可用的情况，需要强制刷新页回磁盘，此时的页时脏页列表选取的。

这种情况是保证重做日志的可用性，说白了就是，重做日志中可以循环覆盖的部分空间太少了，换种说法，就是极短时间内产生了大量的redo log。 接下来会有几个变量 InnoDB存储引擎，通过LSN（Log Sequence Number）来标记版本，

LSN是8字节的数字。每个页都有LSN，重做日志有LSN，checkpoint有LSN。 已经写入到重做日志的LSN记为：redo_lsn，将已经刷新回磁盘的最新页LSN记为：checkpoint_lsn 有如下定义:

• checkpoint_age = redo_lsn - checkpoint_lsn

• async_water_mark = 75% * total_redo_file_size

• sync_water_mark = 90% * total_redo_file_size

• 刷新过程如下图所示：

  

• 若每个重做日志文件的大小为 1GB，并且定义了两个重做日志文件，则重做日志文件的总大小为 2GB。 即：

  • async_water_mark=1.5GB，

  • sync_water_mark=1.8GB

    3.1.当checkpoint_age < async_water_mark 时，不需要刷新任何脏页到磁盘;

    3.2.当async_water_mark < checkpoint age < sync_water_mark 时触发 Async Flush，从 Flush 列表中刷新足够的脏页回磁盘，使得刷新后满足 checkpoint age < async_water_ mark;

    3.3.当 checkpoint_age > sync_water_mark 时触发 Sync Flush 操作，这种情况一般很少发生，除非 设置的重做日志文件太小，并且在进行类似LOAD DATA的 BULK INSERT操作，

    从 Flush 列表中刷新 足够的脏页回磁盘，使得刷新后满足 checkpoint_age < async_water_mark。

    MySQL 官方版本并不能查看刷新页是从 Flush列表中还是从LRU列表中进行 Checkpoint 的，也不知道因为重做日志而产生的 Async/Sync Flush 的次数。但是 InnoSQL版本提供了方法，

    可以通过命令 SHOW ENGINE INNODB STATUS 来观察

    4. Dirty Page too much Checkpoint 即脏页太多，强制checkpoint.保证缓冲池有足够可用的页。 参数设置：innodb_max_dirty_pages_pct = 75 表示：当缓冲池中脏页的数量占75%时，强制checkpoint。 1.0.x之后默认75

三、InnoDB关键特性

3.1 插入缓冲

Insert Buffer是InnoDB存储引擎关键特性中最令人激动与兴奋的一个功能。不过这个名字可能会让人认为插入缓冲是缓冲池中的一个组成部分。其实不然，InnoDB缓冲池中有Insert Buffer信息固然不错，

但是Insert Buffer和数据页一样，也是物理页的一个组成部分。

一般情况下，主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。因为，对于此类情况下的插入，速度还是非常快的。

（如果主键类是UUID这样的类，那么插入和辅助索引一样，也是随机的）。

如果索引是非聚集的且不唯一。在进行插入操作时，数据的存放对于非聚集索引叶子节点的插入不是顺序的，这时需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。

这是因为B+树的特性决定了非聚集索引插入的离散性。

Insert Buffer的设计，对于非聚集索引的插入和更新操作，不是每一次直接插入到索引页中，而是先判断插入非聚集索引页是否在缓冲池中，若存在，则直接插入，不存在，则先放入一个Insert Buffer对象中。

数据库这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），

这就大大提高了对于非聚集索引插入的性能。

需要满足的两个条件：

• 索引是辅助索引

• 索引不是唯一的

辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生，从而导致Insert Buffer失去了意义。

3.2 两次写（doublewrite）

如果说插入缓冲是为了提高写性能的话，那么两次写是为了提高可靠性。

介绍两次写之前，说一下部分写失效：

想象这么一个场景，当数据库正在从内存向磁盘写一个数据页时，数据库宕机，从而导致这个页只写了部分数据，这就是部分写失效，它会导致数据丢失。这时是无法通过重做日志恢复的，

因为重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。

从上面分析我们知道，在部分写失效的情况下，我们在应用重做日志之前，需要原始页的一个副本，两次写就是为了解决这个问题，下面是它的原理图：

两次写（doublewrite）的工作流程：

两次写需要额外添加两个部分：

• 内存中的两次写缓冲（doublewrite buffer），大小为2MB

• 磁盘上共享表空间(ibdata x)中连续的128页，即2个区(extent)，大小也为2MB

原理是这样的：

• 当刷新缓冲池脏页时，并不直接写到数据文件中，而是先拷贝至内存中的两次写缓冲区。

• 接着从两次写缓冲区分两次写入磁盘共享表空间中，每次写入1MB

• 待第2步完成后，再将两次写缓冲区写入数据文件

这样就可以解决上文提到的部分写失效的问题，因为在磁盘共享表空间中已有数据页副本拷贝，如果数据库在页写入数据文件的过程中宕机，在实例恢复时，可以从共享表空间中找到该页副本，将其拷贝覆盖原有的数据页，

再应用重做日志即可。

其中第2步是额外的性能开销，但由于磁盘共享表空间是连续的，因此开销不是很大。可以通过参数skip_innodb_doublewrite禁用两次写功能，默认是开启的，强烈建议开启该功能。

有人问如果保存日志的过程中掉电怎么办？最初始的想法是把一条日志的数据一次性写入硬盘，相当于一个原子操作，然而这并不可行，因为硬盘通常以512字节为单位进行操作，日志数据一超过512字节就不可能一次性写入了。所以实际上是这么做的：给每一条日志设置一个结束符，只有在日志写入成功之后才写结束符，如果一条日志没有对应的结束符就会被视为无效日志，直接丢弃，这样就保证了日志里的数据是完整的。

参考文档：
InnoDB关键特性之double write

Mysql InnoDB存储引擎解析

3.3 自适应哈希索引

哈希是一种非常快的查找方法，在一般情况时间复杂度为O(1)。而B+树的查找次数，取决于B+树的高度，在生成环境中，B+树的高度一般为3-4层，不需要查询3-4次。

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以提升速度，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来的。因此建立的速度非常快，

且不要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引。（索引都是用来搜索页的）

AHI有一个要求，对这个页的连续访问模式(查询条件)必须一样的。例如联合索引(a,b)其访问模式可以有以下情况:

• WHERE a=XXX;

• WHERE a=xxx AND b=xxx;

若交替进行上述两张查询，InnoDB存储引擎不会对该页构造AHI。此外AHI还有如下要求：

• 以该模式访问了100次；

• 页通过该模式访问了N次，其中N=页中记录/16。

根据官方文档显示，启用AHI后，读取和写入的速度可以提高2倍，负责索引的链接操作性能可以提高5倍。其设计思想是数据库自由化的，无需DBA对数据库进行人为调整。

3.4 异步IO(AIO)

为了提高磁盘操作性能，当前的数据库系统都采用异步IO的方式来处理磁盘操作。InnoDB也是如此。

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

在每扫描一个页并等待其完成再进行下一次扫描，这是没有必要的。用户可以在发出一个IO请求后立即再发出另外一个IO请求，当全部IO请求发送完毕后，等待所有IO操作完成，这就是AIO。

AIO的另外一个优势是进行IO Merge操作，也就是将多个IO合并为一个IO操作，这样可以提高IOPS的性能。

在InnoDB 1.1.x之前，AIO的实现是通过InnoDB存储引擎中的代码来模拟的。但是从这之后，提供了内核级别的AIO的支持，称为Native AIO。Native AIO需要操作系统提供支持。Windows和Linux都支持，

而Mac则未提供。在选择MySQL数据库服务器的操作系统时，需要考虑这方面的因素。

MySQL可以通过参数innodb_use_native_aio来决定是否启用Native AIO。在InnoDB存储引擎中，read ahead方式的读取都是通过AIO完成，脏页的刷新，也是通过AIO完成。

3.5 刷新邻接页

InnoDB存储引擎在刷新一个脏页时，会检测该页所在区(extent)的所有页，如果是脏页，那么一起刷新。这样做的好处是通过AIO可以将多个IO写操作合并为一个IO操作。

该工作机制在传统机械磁盘下有显著优势。但是需要考虑下列两个问题:

• 是不是将不怎么脏的页进行写入，而该页之后又会很快变成脏页？

• 固态硬盘有很高IOPS，是否还需要这个特性？

为此InnoDB存储引擎1.2.x版本开始提供参数innodb_flush_neighbors来决定是否启用。对于传统机械硬盘建议使用，而对于固态硬盘可以关闭。

四、InnoDB索引

4.1 InnoDB索引使用的是B+树的数据结构

InnoDB的数据文件本身就是索引文件，在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引

是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），

如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

InnoDB默认创建的主键索引是聚簇索引（Clustered Index），其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。mysql创建表时默认使用的是InnoDB存储引擎，

由于InnoDB使用的是聚簇索引，聚簇索引中的叶子节点则记录了主键值、事务id、用于事务和MVCC的回流指针以及所有的剩余列

• 聚集索引

  聚集索引是按每张表的主键构造的一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引 的节点成为数据页，这个特性决定了索引组织表中数据也是索引的一部分。

  由于实际的数据页只能按照一颗 B+树进行排序，所以每张表只能拥有一个聚集索引。查询优化器非常倾向于采用聚集索引，因为其直接存储 行数据，所以主键的排序查询和范围查找速度非常快。

  不是物理上的连续，而是逻辑上的，不过在刚开始时数据是顺序插入的所以是物理上的连续，随着数据增删， 物理上不再连续。

• 辅助索引

  辅助索引页级别不包含行的全部数据。叶节点除了包含键值以外，每个叶级别中的索引行中还包含了一个书 签，该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。其中存的就是聚集索引的键。

  辅助索引的存在并不影响数据在聚集索引的结构组织。InnoDB会遍历辅助索引并通过叶级别的指针获得指向 主键索引的主键，然后通过主键索引找到一个完整的行记录。当然如果只是需要辅助索引的值和主键索引的值，

  那么只需要查找辅助索引就可以查询出索要的数据，就不用再去查主键索引了。

4.2 各种树形结构—B+树

B/B+/B*三种树有相似的操作，比如检索/插入/删除节点。这里只重点关注插入节点的情况，且只分析他们在当前节点已满情况下的插入操作，因为这个动作稍微复杂且能充分体现几种树的差异。

与之对比的是检索节点比较容易实现，而删除节点只要完成与插入相反的过程即可（在实际应用中删除并不是插入的完全逆操作，往往只删除数据而保留下空间为后续使用）。

• 搜索二叉树：每个节点有两个子节点，数据量的增大必然导致高度的快速增加，显然这个不适合作为大量数 据存储的基础结构。

  

• B树：一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足： ┌m/2┐ - 1 <= j <= m - 1；一个节点的子节点数量会比关键字个数多1，

  这样关键字就变成了子节点的 分割标志。一般会在图示中把关键字画到子节点中间，非常形象，也容易和后面的B+树区分。由于数据同 时存在于叶子节点和非叶子结点中，无法简单完成按顺序遍历B树中的关键字，必须用中序遍历的方法。

• B树的分裂，上图的红色值即为每次新插入的节点。每当一个节点满后，就需要发生分裂（分裂是一个 递归过程，参考上面7的插入导致了两层分裂），由于B树的非叶子节点同样保存了键值，

  所以已满节点分 裂后的值将分布在三个地方：1原节点，2原节点的父节点，3原节点的新建兄弟节点（参考5，7的插入过程）。 分裂有可能导致树的高度增加（参考3，7的插入过程），也可能不影响树的高度（参考5，6的插入过程）。

  

• B+树：一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足： ┌m/2┐ - 1 <= j <= m；子树的个数最多可以与关键字一样多。非叶节点存储的是子树里最小的关键字。

  同时数据节点只存在于叶子节点中，且叶子节点间增加了横向的指针，这样顺序遍历所有数据将变得非常容易。

  

• B+树的分裂：当一个结点满时，分配一个新的结点，并将原结点中1/2的数据复制到新结点，最后在父结点 中增加新结点的指针；B+树的分裂只影响原结点和父结点，而不会影响兄弟结点，所以它不需要指向兄弟节点的指针。

  

• B*树：一棵m阶B树是一棵平衡的m路搜索树。最重要的两个性质是1每个非根节点所包含的关键字个数 j 满足：┌m2/3┐ - 1 <= j <= m；2非叶节点间添加了横向指针。

  

• B*树的分裂：当一个结点满时，如果它的下一个兄弟结点未满，那么将一部分数据移到兄弟结点中，再在原结 点插入关键字，最后修改父结点中兄弟结点的关键字（因为兄弟结点的关键字范围改变了）。

  如果兄弟也满了， 则在原结点与兄弟结点之间增加新结点，并各复制1/3的数据到新结点，最后在父结点增加新结点的指针。可以 看到B*树的分裂非常巧妙，因为B*树要保证分裂后的节点还要2/3满，

  如果采用B+树的方法，只是简单的将已满 的节点一分为二，会导致每个节点只有1/2满，这不满足B*树的要求了。所以B*树采取的策略是在本节点满后，

  继续插入兄弟节点（这也是为什么B*树需要在非叶子节点加一个兄弟间的链表），直到把兄弟节点也塞满，然后 拉上兄弟节点一起凑份子，自己和兄弟节点各出资1/3成立新节点，

  这样的结果是3个节点刚好是2/3满，达到B*树 的要求，皆大欢喜。

B+树适合作为数据库的基础结构，完全是因为计算机的内存-机械硬盘两层存储结构。内存可以完成快速的随机访问 （随机访问即给出任意一个地址，要求返回这个地址存储的数据）但是容量较小。

而硬盘的随机访问要经过机械动作 （1磁头移动 2盘片转动），访问效率比内存低几个数量级，但是硬盘容量较大。典型的数据库容量大大超过可用内 存大小，这就决定了在B+树中检索一条数据很可能要借助几次磁盘IO操作来完成。

如下图所示：通常向下读取一个 节点的动作可能会是一次磁盘IO操作，不过非叶节点通常会在初始阶段载入内存以加快访问速度。同时为提高在节 点间横向遍历速度，

真实数据库中可能会将图中蓝色的CPU计算/内存读取优化成二叉搜索树 （InnoDB中的page directory机制）。

总结：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键 字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，

相对 IO读写次数就降低了。

B+树与B树的不同：

• B+树非叶子节点不存在数据只存索引，B树非叶子节点存储数据

• B+树查询效率更高。B+树使用双向链表串连所有叶子节点，区间查询效率更高（因为所有数据都在B+树的叶子节点，扫描数据库只需扫一遍叶子结点就行了），但是B树则需要通过中序遍历才能完成查询范围的查找。

• B+树查询效率更稳定。B+树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也 可能在，这样就会造成查询的效率的不稳定

• B+树的磁盘读写代价更小。B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小， 通常B+树矮更胖，高度小查询产生的I/O更少。

B+树是B树的改进，简单地说是：只有叶子节点才存数据，非叶子节点是存储的指针；所有叶子节点构成一个有序链表

而真实数据库中的B+树应该是非常扁平的，可以通过向表中顺序插入足够数据的方式来验证InnoDB中的B+树到底有 多扁平。我们通过如下图的CREATE语句建立一个只有简单字段的测试表，然后不断添加数据来填充这个表。

通过下图的统计数据（来源见参考文献1）可以分析出几个直观的结论，这几个结论宏观的展现了数据库里B+树的尺度。

• 1、每个叶子节点存储了468行数据，每个非叶子节点存储了大约1200个键值，这是一棵平衡的1200路搜索树！

• 2、对于一个22.1G容量的表，也只需要高度为3的B+树就能存储了，这个容量大概能满足很多应用的需要了。如果 把高度增大到4，则B+树的存储容量立刻增大到25.9T之巨！

• 3、对于一个22.1G容量的表，B+树的高度是3，如果要把非叶节点全部加载到内存也只需要少于18.8M的内存 （如何得出的这个结论？因为对于高度为2的树，1203个叶子节点也只需要18.8M空间，而22.1G从良表的高度是3，

  非叶节点1204个。同时我们假设叶子节点的尺寸是大于非叶节点的，因为叶子节点存储了行数据而非叶节点只有 键和少量数据。），只使用如此少的内存就可以保证只需要一次磁盘IO操作就检索出所需的数据，效率是非常之高的。

  

4.3 MySQL的存储引擎和索引

可以说数据库必须有索引，没有索引则检索过程变成了顺序查找，O(n)的时间复杂度几乎是不能忍受的。我们非常容易想象出一个只有单关键字组成的表如何使用B+树进行索引，只要将关键字存储到树的节点即可。

当数据库一条记录里包含多个字段时，一棵B+树就只能存储主键，如果检索的是非主键字段，则主键索引失去作用，又变成顺序查找了。这时应该在第二个要检索的列上建立第二套索引。 这个索引由独立的B+树来组织。

有两种常见的方法可以解决多个B+树访问同一套表数据的问题，一种叫做聚簇索引（clustered index ），一种叫做非聚簇索引（secondary index）。这两个名字虽然都叫做索引，但这并不是一种单独的索引类型，

而是一种数据存储方式。对于聚簇索引存储来说，行数据和主键B+树存储在一起，辅助键B+树只存储辅助键和主键，主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说，主键B+树在叶子节点存储指向真正数据行的指针，而非主键。

• InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14" 这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

  若对Name列进行条件搜索，则需要两个步骤：

  • 第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。

  • 第二步使用主键 在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

• MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不 同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。

  表数据存储在独立的地方，这两颗B+ 树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立 的，通过辅助键检索无需访问主键的索引树。

  

我们重点关注聚簇索引，看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

• 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行 数据返回了，如果按照主键Id来组织数据，获得数据更快。

• 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时 辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，

  换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据 库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），

  使用聚簇索引就可以保证不管这个主键B+ 树的节点如何变化，辅助索引树都不受影响。

4.4 Page结构

在数据库中， 不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说存储空间的基本单位是页。一个页就是一棵树B+树的节点，数据库I/O操作的最小单位是页，与数据库相关的内容都会存储在页的结构里。

所以理解InnoDB的实现不得不提Page结构，Page是整个InnoDB存储的最基本构件，也是InnoDB磁盘管理的最小单位，与数据库相关的所有内容都存储在这种Page结构里。Page分为几种类型，

常见的页类型有数据页（B-tree Node）Undo页（Undo Log Page）系统页（System Page） 事务数据页（Transaction System Page）等。单个Page的大小是16K（编译宏UNIV_PAGE_SIZE控制），

每个Page使用一个32位的int值来唯一标识，这也正好对应InnoDB最大64TB的存储容量（16Kib * 2^32 = 64Tib）。一个Page的基本结构如下图所示：

每个Page都有通用的头和尾，但是中部的内容根据Page的类型不同而发生变化。Page的头部里有我们关心的一些数据，下图把Page的头部详细信息显示出来：

我们重点关注和数据组织结构相关的字段：Page的头部保存了两个指针，分别指向前一个Page和后一个Page，头部还有Page的类型信息和用来唯一标识Page的编号。根据这两个指针我们很容易想象出Page链接起来就是一个双向链表的结构。

 

• 在一棵B+树中，每个节点为都是一个页，每次新建节点的时候，就会申请一个页空间

• 同一层的节点之间，通过页的结构构成了一个双向链表

• 非叶子节点为，包括了多个索引行，每个索引行里存储索引键和指向下一层页面的指针

• 叶子节点，存储了关键字和行记录，在节点内部（也就是页结构的内部）记录之间是一个单向的链表

Page的主体内容，我们主要关注行数据和索引的存储，他们都位于Page的User Records部分，User Records占据Page的大部分空间，User Records由一条一条的Record组成，每条记录代表索引树上的一个节点（非叶子节点和叶子节点）。

在一个Page内部，单链表的头尾由固定内容的两条记录来表示，字符串形式的"Infimum"代表开头，"Supremum"代表结尾。这两个用来代表开头结尾的Record存储在System Records的段里，

这个System Records和User Records是两个平行的段。InnoDB存在4种不同的Record，它们分别是1主键索引树非叶节点 2主键索引树叶子节点 3辅助键索引树非叶节点 4辅助键索引树叶子节点。

这4种节点的Record格式有一些差异，但是它们都存储着Next指针指向下一个Record。后续我们会详细介绍这4种节点，现在只需要把Record当成一个存储了数据同时含有Next指针的单链表节点即可。

User Record在Page内以单链表的形式存在，最初数据是按照插入的先后顺序排列的，但是随着新数据的插入和旧数据的删除，数据物理顺序会变得混乱，但他们依然保持着逻辑上的先后顺序。

把User Record的组织形式和若干Page组合起来，就看到了稍微完整的形式。

现在看下如何定位一个Record：

• 通过根节点开始遍历一个索引的B+树，通过各层非叶子节点最终到达一个Page，这个Page里存放的都是叶子节点。

• 在Page内从"Infimum"节点开始遍历单链表（这种遍历往往会被优化，二分查找），如果找到该键则成功返回。如果 记录到达了"supremum"，说明当前Page里没有合适的键，这时要借助Page的Next Page指针，

  跳转到下一 个Page继续从"Infimum"开始逐个查找。

  

详细看下不同类型的Record里到底存储了什么数据，根据B+树节点的不同，User Record可以被分成四种格式，下图种按照颜色予以区分。

4.4.1、主索引树非叶节点（绿色）

• 1、子节点存储的主键里最小的值（Min Cluster Key on Child），这是B+树必须的，作用是在一个Page里定位到具体的记录的位置。

• 2、最小的值所在的Page的编号（Child Page Number），作用是定位Record。

4.4.2、主索引树叶子节点（黄色）

• 1、主键（Cluster Key Fields），B+树必须的，也是数据行的一部分

• 2、除去主键以外的所有列（Non-Key Fields），这是数据行的除去主键的其他所有列的集合。这里的1和2两部分加起来就是一个完整的数据行。

4.4.3、辅助索引树非叶节点非（蓝色）

• 1、子节点里存储的辅助键值里的最小的值（Min Secondary-Key on Child），这是B+树必须的，作用是在一个Page里定位到具体的记录的位置。

• 2、主键值（Cluster Key Fields），非叶子节点为什么要存储主键呢？因为辅助索引是可以不唯一的， 但是B+树要求键的值必须唯一，所以这里把辅助键的值和主键的值合并起来作为在B+树中的真正键值， 保证了唯一性。

  但是这也导致在辅助索引B+树中非叶节点反而比叶子节点多了4个字节。（即下图中蓝色 节点反而比红色多了4字节）

• 3、最小的值所在的Page的编号（Child Page Number），作用是定位Record。

4.4.4、辅助索引树叶子节点（红色）

• 1、辅助索引键值（Secondary Key Fields），这是B+树必须的。

• 2、主键值（Cluster Key Fields），用来在主索引树里再做一次B+树检索来找到整条记录。

  

结合B+树的结构和前面介绍的4种Record的内容，我们终于可以画出一幅全景图。由于辅助索引的B+树与主键索引有相似的结构，这里只画出了主键索引树的结构图，只包含了"主键非叶节点"和"主键叶子节点"两种节点，

也就是上图的的绿色和黄色的部分。

把上图还原成下面这个更简洁的树形示意图，这就是B+树的一部分。注意Page和B+树节点之间并没有一一对应的关系，Page只是作为一个Record的保存容器，它存在的目的是便于对磁盘空间进行批量管理，

上图中的编号为47的Page在树形结构上就被拆分成了两个独立节点。

类似如下图：

InnoDB索引总结：

每一个非叶子节点都是一个索引，存储的是记录里最小的索引值，而每个索引对应多个Page，每个 Page有多个组，每组存储多条记录，而页目录存储的是槽，槽相当于分组记录的索引，

每个槽 指针指向了不同组的最后一个记录，我们通过槽定位到组，再查看组中的记录。<br/> 页的主要作用是存储记录，在页中记录以单链表的形式进行存储。 单链表优点是插入、删除方便，缺点是检索效率不高，

最坏的情况要遍历链表所有的节点。因此页目录中提供了二分查找的方式，来提高记录的检索效率。