数据库的索引设计

索引的数据结构选型

加速查找速度的数据结构，常见的有两类：

(1)哈希，例如HashMap，查询/插入/修改/删除的平均时间复杂度都是O(1)；

(2)树，例如平衡二叉搜索树，查询/插入/修改/删除的平均时间复杂度都是O(lg(n))；

可以看到，不管是读请求，还是写请求，哈希类型的索引，都要比树型的索引更快一些，那为什么，索引结构要设计成树型呢？

索引设计成树形，和SQL的需求相关。

对于这样一个单行查询的SQL需求：
select * from t where name=”shenjian”;
确实是哈希索引更快，因为每次都只查询一条记录。

*所以，如果业务需求都是单行访问，例如passport，确实可以使用哈希索引。*

但是对于排序查询的SQL需求：

• 分组：group by
• 排序：order by
• 比较：<、>
• …

哈希型的索引，时间复杂度会退化为O(n)，而树型的“有序”特性，依然能够保持O(log(n)) 的高效率。

任何脱离需求的设计都是耍流氓。

多说一句，InnoDB并不支持哈希索引。

几种树的简单复习

二叉查找树

（英语：Binary Search Tree），也称为二叉搜索树、有序二叉树（ordered binary tree）或排序二叉树（sorted binary tree），是指一棵空树或者具有下列性质的二叉树：

若任意节点的左子树不空，则左子树上所有节点的值均小于它的根节点的值；
若任意节点的右子树不空，则右子树上所有节点的值均大于它的根节点的值；
任意节点的左、右子树也分别为二叉查找树；
没有键值相等的节点。

二叉搜索树查找的的平均时间复杂度为O(logn)，最坏时间复杂度为O(n)

B树

B 树就是常说的“B 减树（B- 树）”，又名平衡多路（即不止两个子树）查找树，它和平衡二叉树的不同有这么几点：

1. 平衡二叉树节点最多有两个子树，而 B 树每个节点可以有多个子树，M 阶 B 树表示该树每个节点最多有 M 个子树
2. 平衡二叉树每个节点只有一个数据和两个指向孩子的指针，而 B 树每个中间节点有 k-1 个关键字（可以理解为数据）和 k 个子树（ **k 介于阶数 M 和 M/2 之间，M/2 向上取整）
3. B 树的所有叶子节点都在同一层，并且叶子节点只有关键字，指向孩子的指针为 null

B树中如何查找数据

因为 B 树的子树大小排序规则，因此在 B 树中查找数据时，一般需要这样：

1. 从根节点开始，如果查找的数据比根节点小，就去左子树找，否则去右子树
2. 和子树的多个关键字进行比较，找到它所处的范围，然后去范围对应的子树中继续查找
3. 以此循环，直到找到或者到叶子节点还没找到为止

B树如何保证平衡

我们知道，平衡的树之所以能够加快查找速度，是因为在添加、删除的时候做了某些操作以保证平衡。

平衡二叉树的平衡条件是：左右子树的高度差不大于 1；而 B 树的平衡条件则有三点：

1. 叶子节点都在同一层
2. 每个节点的关键字数为子树个数减一（子树个数 k 介于树的阶 M 和它的二分之一
3. 子树的关键字保证左小右大的顺序

也就是说，一棵 3 阶的 B 树（即节点最多有三个子树），每个节点的关键字数最少为 1，最多为 2，如果要添加数据的子树的关键字数已经是最多，就需要拆分节点，调整树的结构。

这个图用以表示往 4 阶 B 树中依次插入下面这组数据的过程：

6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

1. 首先明确：4 阶 B 树表示每个节点最多有 4 个子树、3 个关键字，最少有 2 个子树、一个关键字
2. 添加 6，第一个节点，没什么好说的
3. 添加 10，根节点最多能放三个关键字，按顺序添到根节点中
4. 添加 4，还能放到根节点中
5. 添加 14，这时超出了关键字最大限制，需要把 14 添加为子树，同时为了保证“所有叶子节点在同一层”，就需要拆几个关键字作为子树：

拆为：

这个拆的过程比较复杂，首先要确定根节点保留几个关键字，由于“非叶子节点的根节点至少有 2 棵子树”的限制，那就至少需要两个关键字分出去，又因为“子树数是关键字数+1”，如果根节点有两个关键字，就得有三个子树，无法满足，所以只好把除 6 以外的三个关键字都拆为子树。

谁和谁在一个子树上呢，根据“左子树比关键字小、右子树比关键字大”的规律，4 在左子树，10 和 14 在右子树。

继续添加 ：

1. 添加 5，放到 4 所在的子树上
2. 添加 11，放在 10 和 14 所在的右子树上
3. 添加 15，按大小应该放到 10、11 和 14 所在的子树上，但因为超过了关键字数限制，又得拆分

因为“根节点必须都在同一层”，因此我们不能给现有的左右子树添加子树，只能添加给 6 了；但是如果 6 有三个子树，就必须得有 2 个关键字，提升谁做关键字好呢，这得看谁做 6 中间的子树，因为右子树的所有关键字都得比父节点的关键字大，所以这个提升的关键字只能比未来右子树中的关键字都小，那就只有 10 和 11 可以考虑了。

提升 10 吧，没有比它小的做子树，那就只能提升 11 了：

再添加元素也是类似的逻辑：

1. 首先考虑要插入的子树是否已经超出了关键字数的限制
2. 超出的话，如果要插入的位置是叶子节点，就只能拆一个关键字添加到要插入位置的父节点
3. 如果非叶子节点，就得从其他子树拆子树给新插入的元素做孩子

删除也是一样的，要考虑删除孩子后，父节点是否还满足子树 k 介于 M/2 和 M 的条件，不满足就得从别的节点拆子树甚至修改相关子树结构来保持平衡。

总之添加、删除的过程很复杂，要考虑的条件很多，具体实现就不细追究了，这里我们有个基本认识即可。

B+树

B+树比B树的查询性能更高。
一棵 B+ 树需要满足以下条件：

1. 节点的子树数和关键字数相同（B 树是关键字数比子树数少一）
2. 节点的关键字表示的是子树中的最大数，在子树中同样含有这个数据
3. 叶子节点包含了全部数据，同时符合左小右大的顺序

首先第一点不用特别介绍了，在 B 树中，节点的关键字用于在查询时确定查询区间，因此关键字数比子树数少一；而在 B+ 树中，节点的关键字代表子树的最大值，因此关键字数等于子树数。

第二点，除叶子节点外的所有节点的关键字，都在它的下一级子树中同样存在，最后所有数据都存储在叶子节点中。

根节点的最大关键字其实就表示整个 B+ 树的最大元素。

第三点，叶子节点包含了全部的数据，并且按顺序排列，B+ 树使用一个链表将它们排列起来，这样在查询时效率更快。

由于 B+ 树的中间节点不含有实际数据，只有子树的最大数据和子树指针，因此磁盘页中可以容纳更多节点元素，也就是说同样数据情况下，B+ 树会 B 树更加“矮胖”，因此查询效率更快。

B+ 树的查找必会查到叶子节点，更加稳定。

有时候需要查询某个范围内的数据，由于 B+ 树的叶子节点是一个有序链表，只需在叶子节点上遍历即可，不用像 B 树那样挨个中序遍历比较大小。

B+ 树的三个优点：

1. 层级更低，IO 次数更少
2. 每次都需要查询到叶子节点，查询性能稳定
3. 叶子节点形成有序链表，范围查询方便

B+树对于索引的意义在于，
(1)非叶子节点不再存储数据，数据只存储在同一层的叶子节点上；

*B+树中根到每一个节点的路径长度一样，而B树不是这样。*

(2)叶子之间，增加了链表，获取所有节点，不再需要中序遍历；

这些改进让B+树比B树有更优的特性：

(1)范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯；

*范围查询在SQL中用得很多，这是B+树比B树最大的优势。*

(2)叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储；

(3)非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引；

索引

B树被作为实现索引的数据结构被创造出来，是因为它能够完美的利用“局部性原理”。

什么是局部性原理？

局部性原理的逻辑是这样的：

(1)内存读写块，磁盘读写慢，而且慢很多；

(2)磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘IO，提高效率；

通常，一页数据是4K。

(3)局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO；

B树为何适合做索引？

(1)由于是m分叉的，高度能够大大降低；

(2)每个节点可以存储j个记录，如果将节点大小设置为页大小，例如4K，能够充分的利用预读的特性，极大减少磁盘IO；

量化计算

(1)局部性原理，将一个节点的大小设为一页，一页4K，假设一个KEY有8字节，一个节点可以存储500个KEY，即j=500

(2)m叉树，大概m/2<= j <=m，即可以差不多是1000叉树

(3)那么：

一层树：1个节点，1*500个KEY，大小4K

二层树：1000个节点，1000*500=50W个KEY，大小1000*4K=4M

三层树：1000*1000个节点，1000*1000*500=5亿个KEY，大小1000*1000*4K=4G

可以看到，存储大量的数据（5亿），并不需要太高树的深度（高度3），索引也不是太占内存（4G）。

使用B+树查找特定值的时间复杂度为O(logn)，当需要查找两个值之间的多个元素时，通过对叶子节点之间的相连，也使得范围查询更加高效。

如上图，假设查找10到100间的值：

1. 只需要按B+树的最短路径找到10（若10不存在则找10之后最贴近的值）
2. 然后用那些连接来收集10的后续节点，直到找到100

归纳一下，假设需要找M个后续节点，树总共有N个节点，那么这次搜索只需要消耗M+logN。

但对于在数据库中增加或删除一行，B+树需要自我整理和平衡，需要O(logN)时间复杂度，所以使用太多的B+索引，减慢了快速插入，更新，删除表中一行的操作速度

聚集索引和非聚集索引

MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类

理解聚集索引和非聚集索引可通过对比汉语字典的索引。

汉语字典提供了两类检索汉字的方式，

第一类是拼音检索（前提是知道该汉字读音），比如拼音为cheng的汉字排在拼音chang的汉字后面，根据拼音找到对应汉字的页码（因为按拼音排序，二分查找很快就能定位），这就是我们通常所说的字典序；

第二类是部首笔画检索，根据笔画找到对应汉字，查到汉字对应的页码。拼音检索就是聚集索引，因为存储的记录（数据库中是行数据、字典中是汉字的详情记录）是按照该索引排序的；笔画索引，虽然笔画相同的字在笔画索引中相邻，但是实际存储页码却不相邻。

正文内容按照一个特定维度排序存储，这个特定的维度就是聚集索引；

Innodb存储引擎中行记录就是按照聚集索引维度顺序存储的，Innodb的表也称为索引表；因为行记录只能按照一个维度进行排序，所以一张表只能有一个聚集索引。

非聚集索引索引项顺序存储，但索引项对应的内容却是随机存储的；

举个例子说明下：

create table student (

`id` INT UNSIGNED AUTO_INCREMENT,

`name` VARCHAR(255),

PRIMARY KEY(`id`),

KEY(`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

该表中主键id是该表的聚集索引、name为非聚集索引；表中的每行数据都是按照聚集索引id排序存储的；比如要查找name='Arla'和name='Arle'的两个同学，他们在name索引表中位置可能是相邻的，但是实际存储位置可能差的很远。name索引表节点按照name排序，检索的是每一行数据的主键。聚集索引表按照主键id排序，检索的是每一行数据的真实内容。

也就是说查询name='Arle'的记录时，首相通过name索引表查找到Arle的主键id（可能有多个主键id，因为有重名的同学），再根据主键id的聚集索引找到相应的行记录；

聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引，如果还是没有的话，就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。

每张表只有一个聚集索引，因为聚集索引在精确查找和范围查找方面良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增id充当聚集索引）。

从宏观上分析下聚集索引和普通索引的性能差异，还是针对上述student表：

（1）select * from student where id >5000 and id <20000;

（2）select * from student where name > 'Alie' and name < 'John';

第一条SQL语句根据id进行范围查询，因为(5000, 20000)范围内的记录在磁盘上按顺序存储，顺序读取磁盘很快就能读到这批数据。

第二条SQL语句查询（'Alie', 'John'）范围内的记录，主键id分布可能是离散的1，100，20001，5000.....；增加了随机读取数据页几率；所以普通索引的范围查询效率被聚集索引甩开几条街都不止；非聚集索引的精确查询效率还是可以的，比聚集索引查询只增加了一次IO开销。

---

参考文章

https://juejin.im/post/5d135a...
https://zh.wikipedia.org/wiki...
https://juejin.im/entry/5b0cb...
https://zhuanlan.zhihu.com/p/...