《MySQL技术内幕--InnoDB存储引擎》笔记--索引篇

前言

最近读了《MySQL技术内幕--InnoDB存储引擎》一书，记一下笔记，也方便以后复习。

索引

InnoDB常见索引:

• B+数索引
• 全文索引
• 哈希索引

本文主要介绍 B+ 索引。

B+ 树索引并不能找到一个给定键值得具体行。B+ 树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

所以数据库在进行 sql 查询时，至少要进行 2 次 IO 操作才能得到数据。

索引的数据结构 -- B+ 树

首先来分析一下以下几种数据结构的优缺点：

• 有序数组

数组是一种很基础且很重要的数据结构，在接下来介绍的 Hash, tree都可以使用数组来实现。数组在 Find 操作中是以线性时间执行的，对于一个无序的数组进行 FindKth 所花费的时间是常数的。但是在索引中，我们通常讨论的是有序数组，在 Find 操作中采用的是二分查找。其时间复杂度是 O(logn)。

int binary_search(const int arr[], int target, int length) {
    int rifht = length - 1;
    int left = 0;
    int mid = 0;
    while (left <= right) {
        mid = (right - left) / 2 + left;
        if (a[mid] < x) {
            left = mid + 1;
        } else if (a [mid] > x) {
            right = mid - 1;
        } else {
            return a[mid];
        }
    }

    return NotFound;
}

数组的优点很明显，查找数据快。但是缺点也很明显，只适合静态数据，而且遇到有数据新增插入，需要移动数据(重新申请空间，拷贝数据，然后释放空间等)，这会很耗时间。

• Hash

哈希表是一种以键-值(K-V)存储的数据结构。通过某一个键就可以快速得到一个值，其时间复杂度也是常数级别的。其思路是: 用特定的哈希函数将 K 换算到数组中的位置，然后将值 V 放到数组中的这个位置。如果遇到不同的 K 计算到相同的位置，这个时候就要解决冲突，解决冲突的办法主要有：分离链接法和开放地址法。一般使用开放地址法的比较多，通常是通常一根指针连接每一次哈希到这个位置的结点。

通过分析哈希数据结构可以看出，哈希表对于范围查找是很无奈的。

• 二叉搜索树和平衡二叉树

二叉搜索树，也称为二叉查找树、有序二叉树或排序二叉树。其性质如下： 1、若任意结点的左子树不空，则左子树上所有结点的值均小于它的根节点的值； 2、若任意结点的右子树不空，则右子树上所有结点的值均大于或等于它的根结点的值； 3、任意结点的左、右子树也分别为二叉查找树。

二叉查找树的查找算法： 1、若b是空树，则搜索失败，否则： 2、若x等于b的根结点的值，则查找成功；否则： 3、若x小于b的根结点的值，则搜索左子树，否则： 4、查找右子树。 二叉搜索树在查找和插入的时间复杂度比较低，为O(logn)。但是二叉搜索树可以任意构造，很容易就可一变成链表形式，这样会导致查询效率降低。所以就引出了平衡二叉树（AVL）。 平衡二叉树除了要满足二叉搜索树的性质外，还要满足任意结点的两个子树的高度最大差是1。在维持这种状态情况下，平衡二叉树的查找性能已经接近最好的了，还不是最好。 平衡二叉树在查找很快，但是由于需要维持高度平衡，所以在插入的时候比较繁琐。当插入一个结点是，这个时候可能会导致平衡失效，所以需要进行多次旋转来维持平衡。

• B+树

根据维基百科介绍 B+ 树的特点：

能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+树多用于数据库和操作系统的文件系统中。在 B+ 树种，所有记录结点都是按键值的大小顺序存放在同一层的叶子结点上，由各叶子结点指针进行连接。结构如下：

在B+树中的节点通常被表示为一组有序的元素和子指针。如果此B+树的阶数是m，则除了根之外的每个节点都包含最少 [m/2] 个元素，最多 m-1 个元素，对于任意的结点有最多 m 个子指针。对于所有内部节点，子指针的数目总是比元素的数目多一个。所有叶子都在相同的高度上，叶结点本身按关键字大小从小到大链接。 所以，可以归纳出 B+ 树的优点： 1、层级更低，IO 次数更少； 2、每次都需要查询到叶子结点，查询性能稳定； 3、叶子结点形成有序链表，范围查询方便。

算法

查找 查找从根节点开始，自顶向下遍历，选择分离值在要查找值的一边子指针。在结点内使用二分查找来确定位置。

插入

Leaf Page(叶子)满	Index Page(索引)满	操作
No	No	直接将记录插入到叶子结点
Yes	No	1.拆分Leaf Page 2.将中间的结点放入到Index Page 3.小于中间结点的记录放左边 4.大于或等于中间结点的记录放右边
Yes	Yes	1.拆分Left Page 2.小于中间结点的记录放左边 3.大于或等于中间结点的记录放右边 4.拆分Index Page 5.小于中间结点的记录放左边 6.大于中间结点的记录放右边 7.中间结点放入上一层的Index Page

删除

叶子结点小于填充因子	中间结点小于填充因子	操作
NO	No	直接将记录从叶子结点删除，如果该结点还是Index Page 的结点，用该结点的右结点代替
Yes	No	合并叶子结点和它的兄弟结点，同时更新Index Page
Yes	Yes	1.合并叶子结点和它的兄弟结点 2.更新Index Page 3.合并Index Pgage和它的兄弟结点

填充因子(innodb_fill_factor): 索引构建期间填充的每个B-tree页面上的空间百分比，其余空间保留给未来索引增长。在插入和删除操作中，填充因子会影响数据也的split和merge的频率。值越小，split和merge的频率也会降低，但是索引会占用更多的磁盘。Innodb中允许设置的最小值是50%。根据官方文档，Innodb 对于聚集索引默认会预留 1/16 的空间保证后续的插入和升级索引。

---

InnoDB中B+树的应用

以上介绍了索引的基本数据结构，现在来分析B+树在InnoDB中的应用。首先我们先要理解两个概念：聚集索引和非聚集索引。

聚集索引和非聚集索引

InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。聚集索引就是按照每张表的主键构造一颗B+树， 同时叶子结点中存放的即为整张表的行记录数据，所以聚集索引的叶子结点也称为数据页。 由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。非聚集索引叶子不包含行记录的全部数据。 非聚集索引只在叶子结点记录了键值和一个书签。该书签可以告诉InnoDB在哪可以找到与索引对应的行数据。由于InnoDB存储引擎表示索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

覆盖索引 下面通过一个实例来讲解一下, innodb在什么情况下会触发对应的索引。创建如下表：

CREATE TABLE `player` (
  `Id` int(10) unsigned NOT NULL,
  `Nickname` varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL,
  `CreateAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `idx_CreateAt` (`CreateAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

第一个查询语句：

select * from player where Id =  1006;

explain结果如下：

第二个查询语句：

select * from player order by Id;

explain结果如下：

第三个查询语句：

select * from player order by CreateAt;

explain结果如下：

上面三个语句中, 第一，第二个语句按照预期执行了。但是第三个却没有。第三个语句不但没有使用idx_CreateAt这个索引进行查询，而且还触发了filesort。这是为什么呢？这个是因为我们选取的是整行数据，然而CreateAt索引并没有覆盖整行数据。刚才提到过，如果非聚集索引最终只能确定主键索引的位置，并不能确定整行数据。虽然在idx_CreateAt这个索引树种，这个索引已经排好序了。但是数据却是按照主键索引排好序存放的，所以最后得到的数据是要重新排列一次的。因此为了减少磁盘的离散操作，优化器选择了使用主键索引。优化器最终选择使用主键索引还是辅助索引其实跟我们查询的数据量有关，如果查询的数据量很少（20%）左右，这个时候，就会使用辅助索引。如果查询的数据量很大，就会直接使用主键索引。

再看以下语句： 语句A:

select * from player where CreateAt <= '2020-12-11 00:00:00';

explain结果如下：

语句B:

select Id, Nickname from player where CreateAt <= '2020-12-11 00:00:00';

explain结果如下：

语句C:

select Id from player where CreateAt <= '2020-12-11 00:00:00';

explain结果如下：

在这三个语句中，A、B都是用了全盘扫描的方式，只有C走了索引。其实只要理解了刚才说的几例子，就很容易解释了。因为在辅助索引中没有覆盖A,B的需要查询的字段，所以就使用了全盘扫描，对于C语句来说，刚好辅助索引可以快速确定到它的位置，索引就选择走了辅助索引。

最左匹配

先添加索引：

alter table player add index idx_name(Nickname);

添加idx_name索引之后，再重新试一下上一节中提到的sql语句，看一下执行过程有没有发生什么变化？

第一个语句：

select * from player where Nickname like "%55%";

explain结果如下：

第二个语句：

select * from player where CreateAt <= '2020-12-11 00:00:00' and Nickname like "%55%";

explain结果如下：

在上面两个sql语句中，第一个使用了全盘扫描，第二个使用了索引。最左匹配可以是字符串索引的最左N个字符，也可以是联合索引的最左M哥字段。使用最左匹配可以节约磁盘空间。

索引下推

在上一节中extra字段出现了using index condition。对于索引下推的介绍可以查看这里,索引下推功能是从 5.6 版本开始支持的。在 5.6 idx_name索引是没有使用上的，需要每次去主键索引表取完整的记录值进行比较。从 5.6 版本开始，由于索引 idx_name 的存在，可以直接取索引的 Nickname 值进行过滤，这样不符合"Nickname like 'Hi%'"条件的记录就不再需要回表操作。

MRR优化 MySQL 5.6 版本开始支持 Multi-Range Read(MRR)优化，MRR 优化的目的是为减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，对于 IO-bound 类型的 SQL 查询语句可带来性能极大提升。

MRR好处：

• MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
• 减少缓冲池中页被替换的次数。
• 批量处理对键值的查询操作。

MRR工作方式:

• 将查询得到的辅助索引存在一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
• 将缓存中的键值根据RowID进行排序。
• 根据RowID的排序顺序来访问实际的文件数据。

总结

文章介绍了索引的数据结构，并且简单分析了几个例子。这个是《MySQL 技术内幕-InnoDB 存储引擎》索引篇的一些记录，同时本文还参考了很多的文档。对于索引的优化和使用，需要结合的业务场景，这样才能发挥innodb的优势，仅仅是书中的理论是无法应付的，更多的是付出实践。 文章有任何纰漏，请给我评论，谢谢~

参考文献

《MySQL 技术内幕-InnoDB 存储引擎》第二版，作者：姜承尧
dev.mysql.com/doc/refman/…
mp.weixin.qq.com/s/QduIxKOyk…
tech.meituan.com/2014/06/30/…
www.eversql.com/choosing-th…
zhuanlan.zhihu.com/p/86137284