MySQL innoDB 索引实现原理

1 B+树

B+树和二叉树、平衡二叉树一样，都是经典的数据结构。B+树由 B 树和索引顺序访问方法演化而来，但是在现实使用过程中几乎已经没有使用 B 树的情况了。
B+树的定义在很多数据结构书中都能找到，非常复杂，我们概略它的定义，B+树是 B 树的一种变形形式，B+树上的叶子结点存储关键字以及相应记录的地址，叶子结点以上各层作为索引使用。一棵 m 阶的 B+树定义如下:

(1) 每个节点最多可以有 m 个元素；
(2) 除了根节点外，每个节点最少有 (m/2) 个元素；
(3) 如果根节点不是叶节点，那么它最少有 2 个孩子节点；
(4) 所有的叶子节点都在同一层；
(5) 一个有 k 个孩子节点的非叶子节点有 (k-1) 个元素，按升序排列；
(6) 某个元素的左子树中的元素都比它小，右子树的元素都大于或等于它；
(7) 非叶子节点只存放关键字和指向下一个孩子节点的索引，记录只存放在叶子节点中；
(8) 相邻的叶子节点之间用指针相连。

B+树是为磁盘或其他直接存取辅助设备设计的一种 平衡查找树。在 B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。比如：


从上图我们可以归纳出 B+树的几个特征，在 B+树的简要定义中其实已经包括了：
(1) 相同节点数量的情况下，B+树高度远低于平衡二叉树；
(2) 非叶子节点只保存索引信息和下一层节点的指针信息，不保存实际数据记录；
(3) 每个叶子页（LeafPage）存储了实际的数据，比如上图中每个叶子页就存放了 4 条数据记录，当然可以更多，叶子节点由小到大（有序）串联在一起，叶子页中的数据也是排好序的；
(4) 索引节点指示该节点的左子树比这个索引值小，而右子树大于等于这个索引值。

2 InnoDB 记录存储结构

InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时，InnoDB 存储引擎需要一条一条的把记录从磁盘上读出来么？

InnoDB 采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB 中页的大小一般为 16 KB。 也就是在一般情况下，一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容刷新到磁盘中。

3 索引

3.1 聚簇索引

3.1.1 概念

InnoDB 中使用了聚集索引，就是将表的主键用来构造一棵 B+树，并且将整张表的行记录数据存放在该 B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。
聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。另一个优点是：对于主键的排序查找和范围查找速度非常快。

(1) 对于这个目录项记录而言，可以看到其数据内容部分有两个字段，分别为所指向页的记录最小主键值、所指向的页号，即主键值+页号。其表示了其所指向的数据页的记录主键值的下限
(2) 对于存放目录项记录的数据页而言，其内部依然是一个按主键值从小到大排序的单向链表
(3) 对于同一层中存放目录项记录的各数据页（即这里的25号页、29号页）而言，同样亦是一个按页内目录项记录主键值从小到大排序的双向链表

3.2.2 为什么主键要使用有序的

一般来讲，使用一个业务无关的自增（ AUTO_INCREMENT ）ID，可以保证数据在插入时会被按顺序写入。假设我们使用 UUID 作为聚簇索引，在插入数据的时候，聚簇索引所被插入的位置将变得完全随机。大量的随机插入会导致页分裂和碎片非常多。

下图展示了数据插入有序递增时，聚簇索引会如何存储插入的数据行：

可以看到，因为主键是有序的，InnoDB 把每一条记录都存储在上一条记录的后面。当当前页即将写满时（之所以是即将而不是已经，是因为 InnoDB 会预留一点空间用于以后修改数据，默认预留页的 1/16 大小），下一条记录被插入时，将会写入到新的页中去。所有被插入的数据，都将有序地放到聚簇索引最后的位置上去。

对应的，如果使用 UUID 作为主键索引，InnoDB 将完全随机地将数据插入到聚簇索引对应的位置上去：

如上，因为新插入的行的主键不一定比之前插入的大（由于是 UUID，将会非常随机），所以 InnoDB 将无法简单地总是把新行插入到索引的最后，而是需要根据主键 ID 的值为它寻找合适的索引位置，并为其分配空间。使用 UUID 作为聚簇索引，有以下缺点：

(1) 写入的目标页可能已经写入到磁盘而不只是存在于内存中，又或者目标页还没有被加载到内存中，InnoDB 在插入前需要先找到并从磁盘中读取目标页到内存中去，这会产生大量的磁盘随机 IO。
(2) 因为写入是乱序的，InnoDB 需要频繁地做页分裂操作，一遍为新的行分配空间。页分裂需要移动大量数据。
(3) 有序频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

3.2 辅助索引

3.2.1 概念

对于辅助索引(Secondary Index，也称二级索引、非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签( bookmark)。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

3.2.2 回表

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为 回表。也就是根据辅助索引的值查询一条完整的用户记录需要使用到 2 棵 B+树----一次辅助索引，一次聚集索引。

3.3 联合索引

前面我们对索引的描述，隐含了一个条件，那就是构建索引的字段只有一个，但实践工作中构建索引的完全可以是多个字段。所以，将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引，比如 index(a,b)就是将 a,b 两个列组合起来构成一个索引。
千万要注意一点，建立联合索引只会建立 1 棵 B+树，比如，index(name,age,dept)：

ps: 联合索引的建立会变向的给带头大哥建立索引。

3.4 覆盖索引

既然多个列可以组合起来构建为联合索引，那么辅助索引自然也可以由多个列组成。
InnoDB 存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的 IO 操作。所以记住，覆盖索引并不是索引类型的一种。

如果我们查询只想查询id的值SQL为：

select id from users where name = ?

因为只需要id的值，通过name查询的时候，扫描完name索引，我们就能够获得id的值了，所以就不需要再去扫面id索引，就会直接返回。