InnoDB的B+Tree索引

InnoDB逻辑存储结构
MySQL表中的所有数据被存储在一个空间内，称之为表空间，表空间内部又可以分为段(segment)、区(extent)、页(page)、行(row),逻辑结构如下图：

• 段(segment)
  表空间是由不同的段组成的，常见的段有：数据段，索引段，回滚段等等，在 MySQL中，数据是按照B+树来存储，因此数据即索引，因此数据段即为B+树的叶子节点，索引段为B+树的非叶子节点,回滚段用于存储undo日志，用于事务失败后数据回滚以及在事务未提交之前通过undo日志获取之前版本的数据，在InnoDB1.1版本之前一个InnoDB,只支持一个回滚段，支持1023个并发修改事务同时进行，在InnoDB1.2版本，将回滚段数量提高到了128个，也就是说可以同时进行128*1023个并发修改事务。

• 区(extent)
  区是由连续页组成的空间，每个区的固定大小为1MB,为保证区中页的连续性，InnoDB会一次从磁盘中申请4~5个区，在默认不压缩的情况下，一个区可以容纳64个连续的页。但是在开始新建表的时候，空表的默认大小为96KB,是由于为了高效的利用磁盘空间，在开始插入数据时表会先利用32个页大小的碎片页来存储数据，当这些碎片使用完后，表大小才会按照MB倍数来增加。

• 页(page)
  页是InnoDB存储引擎的最小管理单位，每页大小默认是16KB，从InnoDB 1.2.x版本开始，可以利用innodb_page_size来改变页size，但是改变只能在初始化InnoDB实例前进行修改，之后便无法进行修改，除非mysqldump导出创建新库，常见的页类型有：数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页。

• 行(row)
  行对应的是表中的行记录，每页存储最多的行记录也是有硬性规定的最多16KB/2-200,即7992行（16KB是页大小，我也不明白为什么要这么算,据说是内核定义）

聚集索引和二级索引
每个InnoDB表都有一个特殊的索引，称为聚集索引（有的翻译为聚簇索引） ，用于存储行数据，通常与主键是一个意思。为了从查询中获取最佳的性能，必须了解使用聚集索引优化查询：

• 为每个表定义主键。如果没有逻辑唯一且非空的列或列集，请添加一个自动递增列作为主键。

• 如果表没有定义主键，MySQL会查找所有键列都不为空的第一个唯一索引，InnoDB 会将其用作聚集索引。

• 如果表没有主键或没有合适的唯一索引，则在InnoDB 内部生成一个隐藏的聚集索引GEN_CLUST_INDEX，该索引在row_id列上（InnoDB 表若没有定义主键，会使用系统的一个默认递增row_id） 。row_id是一个6字节的字段，随着插入新行而单调增加。因此，按row_id排序实际上是按插入顺序排序。

  这里需要注意的是row_id的取值范围，row_id 被定义为一个unsigned long long，但由于它只有6个字节，因此最大值是2^48，row_id超过这个值还是会递增，只是写入的时候只取低48位。那如果由于MySQL长时间运行导致row_id重复的情况，会怎么样呢？会导致重复的row_id行被覆盖，导致数据丢失。（特别注意的是row_id是库实例变量，所有表共享。）

聚集索引如何加快查询速度
通过聚集索引访问行是很快的，因为聚集索引搜索直接指向包含所有行数据的页。聚集索引体系结构通常可以节省磁盘I / O操作。

二级索引与聚集索引的关系
除聚集索引之外的所有索引都称为二级索引。在中InnoDB，二级索引中的每个记录都包含该行的主键列以及为二级索引指定的列。 InnoDB使用此主键值在聚集索引中搜索行。如果主键较长，则辅助索引将使用更多空间，因此短主键能节省空间。后面会以实际样例解析二者的关系。

物理结构
除空间索引外，InnoDB 索引是 B-Tree数据结构。索引页（index page）的默认大小为16KB。可通过参数 innodb_page_size 进行调整，如: 4K、8K、32K、64K 等。当插入新的记录到InnoDB聚集索引中时，InnoDB会保留页面的1/16空间，以便将来插入和更新索引记录。如果是随机插入，则保留比例最多达到1/2。保留的空间为了应对今后数据的指数增长。那什么是页呢？

IBD File的结构
MySQL 把每个表的数据存储在一个 IBD File 也就是 .ibd 文件中，我们先来看看它的结构。

从图中可以看到，除了前3个必须的页以外，从第3页（下标重0开始）开始分配索引页（index page），按表定义中索引的顺序进行分配：

• 第一个索引的root页（一般为聚集索引）。
• 第二个索引的root页（一般为第一个二级索引），如果这个表还有其他索引，依次类推。第5页开始是结点页。
• 结点页存放的是除了root页和叶子页（leaf page）的所有索引的节点。
• 接着就是叶子页，叶子页存放的是聚集索引关联的数据行。因为大部分InnoDB 的记录结构都存放在系统表空间，分配给独立表空间的每个page将会是一个索引类型，同时存储了表的数据。

INDEX结构
既然知道了索引储存在索引页，那我们再来看看索引页的结构。

从上图可以看到，index page中包含FIL Header/Trailer，他们的结构如下：

其中Previous Page和Next Page分别代表上一页的指针和下一页的指针。根据这两个指针，我们很容易就能想到，page连起来就是一个双向链表。Offset代表这个页的唯一编号。

再回到索引页的结构，除了FIL Header/Trailer之外，还有一个重要的组成成分，那就是User Records，User Records 就是整个页面中真正用于存放记录的部分，包括指向下一页的指针（非叶子页）或者行记录（叶子页），而 Free Space 就是空余空间了，它是一个单向链表的数据结构，必须要维护好单链表的有序性，行记录在物理存储上并不是按照顺序的，它们之间的顺序是由 next_record 这一指针控制的。

一个索引页的结构是这样子的：

page分为叶子页（leaf page）和非叶子节页（non-leaf page），叶子页包含了实际的行数据，非叶子页只包含了非叶子页指针或叶子页指针。InnoDB为每一个page分配了一个level级别：叶子页的level是0，随着树分支往上走，level不断递增。

所有的非root页和非叶子页的页，都被称为internal page。

B+Tree
下面用B+Tree的结构图解释一下什么是root page，什么是internal page，什么是leaf page。

从上图就很清楚的标出了三者在树中的位置。并且能看出**User Records **中leaf page和non-leaf page的区别：

leaf page中存的是行记录（上图中的A和B），non-leaf page存的是指向下一页的指针（上图中的6和7）。

叶子页的简化结构图：

非叶子页的简化结构图：

无论是叶子页还是非叶子页， 里面包含的行User Records都有指向下一行的指针（在同一个page里的偏移量）。

到这里，索引的主要结构基本讲完了。

索引检索
前面已经讲到了，索引有两种，聚集索引和二级索引。那MySQL是如何通过他们找到数据的呢？下面我将分别以两个例子讲述查找过程，所用到的索引结构全部为简化后的结构。

聚集索引
eg1. 有以下一条SQL语句，其中ID为聚集索引
SELECT * FROM T WHERE T.id=1;
它的搜索过程如下图所示：

当MySQL优化器选择聚集索引ID进行查询时，就会按照如上图所示，在ID索引树中查找ID=1的记录（查找过程这里就不多说了），找到ID=1的叶子页，前面讲叶子页的时候已经说过了，叶子页中User Records存储的是行记录，直接把数据返回即可。

二级索引
eg2. 有以下一条SQL语句，其中ID为聚集索引，a为二级索引：

SELECT * FROM T WHERE T.a=10;

它的搜索过程如下图所示：

当MySQL选择二级索引a进行查询时，会按照上图所示的查找流程，首先在二级索引树a中查找a=10的记录，该记录存储的内容是聚集索引的值。拿到聚集索引的值以后，MySQL就会到聚集索引树中查找ID=6的记录，就像eg1一样，这个过程叫做回表。

也就是说，基于二级索引的查询需要多扫描一棵索引树。性能肯定不如聚集索引。那么有没有什么办法可以避免回表？有的。那就是覆盖索引。eg2中我们是SELECT *，查询所有字段，但如果我们SELECT id，只查询ID呢，由于a的索引树就存储了ID的值，所以就不用回表了。覆盖索引是一种常用的SQL优化手段。索引下推也能减少回表的次数，有关索引下推的内容，可以参考SQL优化之Select语句。

最左前缀
上面提到的索引都是只引用了单个的列，实际上，MySQL中的索引可以引用多个列，这种索引叫做联合索引，也属于二级索引的一种。一般以元组(col1, col2, …, coln)的形式存在，其中各个元素均为数据表的一列，逐级有序。

eg. 有以下语句，创建一个联合索引a_b_index (a,b)

CREATE TABLE t (
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
KEY a_b_index (a,b) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

联合索引的检索也和单列二级索引一样，只是索引中节点储存的值由独立值变为元组。

eg.3

SELECT * FROM T WHERE T.a=10 and T.b=10;

如下图：

B+Tree的优势
大家应该都知道InnoDB中最常用的索引类型是B+Tree，但是为什么要用B+Tree呢？我用平衡二叉树不行么？我用hash不行么？我用数组不行么？针对这些问题，我们来看看B+Tree有什么优势。

hash索引的数据结构是hash表，不支持范围查询，这个缺点就无法满足大部分人的需求。

数组的缺点也非常明显，当我插入和删除数据时，要做大批量数据的移动，代价太大。

平衡二叉树虽然满足上面的两个要求，但是平衡二叉树会导致树的层级太高。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往也会像数据一样持久化到磁盘上。从上面说到的IBD File的结构就能证明这一点。如果使用平衡二叉树的话，你可以想象一下，一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个索引页。虽然现在固态硬盘的普及，访问磁盘的速度有所提升，但20次也未免太多了点。那有没有什么办法可以减少磁盘的访问次数呢？有的，那就是减少树的高度，树的高度降下来了，访问次数自然就降低了。这就是为什么InnoDB选择B+Tree的原因。

B+Tree其实是一个“N叉树”，在相同层级的约束下，N叉树明显比二叉树容纳更多的数据。以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。由于root page总是常驻内存的，所以查找一个值最多访问3次磁盘。

索引选择性
既然索引能加速查询，是不是越多越好呢？答案是否定的，你要知道凡事都有优缺点，虽然索引能加速查询，但是它会消耗存储空间，并且会给增加，删除，更新带来负担，所以说并不是越多越好，你要在查询和更改性能上做取舍。以下两种情况下不建议建索引：

• 数据少的表不建索引，样表做全表扫描可能会有更好的性能。对于行数少于10行且行长度较短的表，这种情况很常见。
• 索引的选择性较低。即(distinct key/key)的比值。取值范围为(0, 1]，选择性越高的索引价值越大。

索引的维护
上面已经说过了，索引会给增加，删除，更新带来负担，那为什么会带来负担呢？主要是维护索引的有序性导致的。那InnoDB又是如何维护索引的有序性的呢？由于User Records中的内容是单链表结构，所以叶子页的增加，删除，更新完全遵循单链表的规则。

Delete
了解了Insert的过程，我们在来看看Delete的过程又是怎么样的：

删除i=5的记录，需要在leaf page中的User Records中查找k为5的节点，由于leaf page中User Records为单链表，所以需要遍历单链表查找k为5的节点，找到后就断开与它的连接，并打上删除标记(D: Yes)，然后更新垃圾偏移量为@258，被删除的节点的Next record指针指向它自己，并更新垃圾的总大小。到这里为止，InnoDB只是完成了逻辑的删除，i=5的记录任然存在物理磁盘上。那什么时候回触发物理删除呢？ 严格来说，删除标记记录的写磁盘操作将在清除操作期间稍后删除。

Update
我们在来看看Update的过程又是怎么样的：

更新i=5的记录为“abcde”，需要在leaf page中的User Records中查找k为5的节点，由于leaf page中User Records为单链表，所以需要遍历单链表查找k为5的节点，找到后修改v=abcde。

Insert
首先InnoDB会遍历root page和node page找到合适的leaf page进行插入，并更新他们，来看看插入的过程：

在插入的过程，必须保证单链表的有序性。由于每个page的大小默认为16kb，随着我们不断的插入数据，page的剩余容量会逐渐减小，虽然我们这上面提到过InnoDB会为每页保留1/16的空间，来应对今后数据的指数增长，但是空间总会用完的，到那时我们需要在这一页上插入数据又该怎么办呢？

这时候需要申请一个新的页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

分页offset值很大性能问题

在 MySQL中，分页当offset值很大的时候，性能会非常的差，比如limit 100000, 20，需要查询100020条数据，然后取20条，抛弃前100000条，在这个过程中产生了大量的随机I/O,这是性能很差的原因，为了解决这个问题，切入点便是减少无用数据的查询，减少随机I/O。 解决的方法是利用索引覆盖，也就是扫描索引得到id然后再从聚簇索引中查询行记录，我知道有两种方式：

比如从表t1中分页查询limit 1000000,5

• 利用inner join
  select * from t1 inner join (select id from t1 where xxx order by xx limit 1000000,5) as t2 using(id),子查询先走索引覆盖查得id,然后根据得到的id直接取5条得数据。

SELECT  t.*
FROM    (
        SELECT  id
        FROM    myTable
        ORDER BY
                id
        LIMIT 1000000, 30
        ) q
JOIN    myTable t
ON      t.id = q.id

大概的原理是：子查询只用到了索引列，没有取实际的数据，所以不涉及到磁盘IO，所以即使是比较大的 offset，查询速度也不会太差。

• 利用范围查询条件来限制取出的数据
  select * from t1 where id > 1000000 order by id limit 0, 5，即利用条件id > 1000000在扫描索引是跳过1000000条记录，然后取5条即可,这种处理方式的offset值便成为0了，但此种方式通常分页不能用，但是可以用来分批取数据。