【MySQL】MySQL为什么用B+树做索引而不用B-树或红黑树？

思考：

1.什么是数据库索引？

数据库查询是数据库的主要功能之一，最基本的查询算法是顺序查找（linear search）时间复杂度为O(n)，显然在数据量很大时效率很低。优化的查找算法如二分查找（binary search）、二叉树查找（binary tree search）等，虽然查找效率提高了。但是各自对检索的数据都有要求：二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织）。所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构。这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

2.索引的优点

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

3.索引的缺陷

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

4.不应该创建索引的的列具有特点

1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

2. 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

3. 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

4. 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

5. 数据库中的索引类型：聚集索引、非聚集索引

聚簇索引：将数据存储和索引放在一起、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻的存放在磁盘上的。

非聚簇索引：叶子节点不存储数据，存储的是数据行地址，也就是说根据索引查找到数据行的位置再去磁盘查找数据，这就有点类似一本书的目录，比如要找到第三章第一节，那就现在目录里面查找，找到对应的页码后再去对应的页码看文章。

索引数据结构的选择 （二叉树，红黑树，Hash，B+树详解）

引言： 当我们创建索引的时候，我们需要选择索引方法（默认B+树）

思考：上文说过，二叉树、红黑树等数据结构都可以用来实现索引，但是那为什么MySQL只为我们提供了BTree和HASH这两种方法？当我们创建索引时该选择哪种索引方法？（重点）

我们都知道评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

对树索引结构选择的分析

1.为什么不用二叉树？

eg: 当数据库插入的数据有序（2,3,5,6,7,8）时，把2作为头结点时，就变成了斜树，这样在查询的时候就相当于链表一样，做一次全表扫描（相当于索引失效）。

2. 我们都知道红黑树可以自动平衡，这样就解决了斜树的问题。但是为什么不用红黑树呢？

红黑树性质：

（1）每个节点或者是黑色，或者是红色。
（2）根节点是黑色。
（3）每个叶子节点（NIL）是黑色。 [注意：这里叶子节点，是指为空(NIL或NULL)的叶子节点！]
（4）如果一个节点是红色的，则它的子节点必须是黑色的。
（5）从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。

有了上面的几个性质作为限制，即可避免二叉查找树退化成单链表的情况。但是，仅仅避免这种情况还不够，这里还要考虑某个节点到其每个叶子节点路径长度的问题。如果某些路径长度过长，那么，在对这些路径上的节点进行增删查操作时，效率也会大大降低。这个时候性质4和性质5用途就凸显了，有了这两个性质作为约束，即可保证任意节点到其每个叶子节点路径最长不会超过最短路径的2倍。原因如下：

当某条路径最短时，这条路径必然都是由黑色节点构成。当某条路径长度最长时，这条路径必然是由红色和黑色节点相间构成(性质4限定了不能出现两个连续的红色节点)。而性质5又限定了从任一节点到其每个叶子节点的所有路径必须包含相同数量的黑色节点。

红黑树示例图：

这些约束强制了红黑树的关键性质: 从根到叶子的最长的可能路径不多于最短的可能路径的两倍长。结果是这个树大致上是平衡的。因为操作比如插入、删除和查找某个值的最坏情况时间都要求与树的高度成比例，这个在高度上的理论上限允许红黑树在最坏情况下都是高效的，而不同于普通的二叉查找树。

平衡二叉树的性质：

它是一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。这个方案很好的解决了二叉查找树退化成链表的问题，把插入，查找，删除的时间复杂度最好情况和最坏情况都维持在O(logN)。但是频繁旋转会使插入和删除牺牲掉O(logN)左右的时间，不过相对二叉查找树来说，时间上稳定了很多。

平衡二叉树和红黑树区别：

1、红黑树放弃了追求完全平衡，追求大致平衡，在与平衡二叉树的时间复杂度相差不大的情况下，保证每次插入最多只需要三次旋转就能达到平衡，实现起来也更为简单。
2、平衡二叉树追求绝对平衡，条件比较苛刻，实现起来比较麻烦，每次插入新节点之后需要旋转的次数不能预知。

红黑树和AVL树的区别在于它使用颜色来标识结点的高度，它所追求的是局部平衡而不是AVL树中的非常严格的平衡。红黑树是牺牲了严格的高度平衡的优越条件为代价红黑树能够以O(log2 n)的时间复杂度进行搜索、插入、删除操作。此外，由于它的设计，任何不平衡都会在三次旋转之内解决。

然而，红黑树毕竟还是二叉树，一个结点点最多拥有两个直接子结点，当我们插入大量数据的时候，会导致的树的高度过高，I/O渐进复杂度为O(h)。如果要查找14，就需要查询5次，所以还是没有有效的减少查找过程中磁盘I/O的存取次数。

3.为什么使用B树

B-树是一种多路自平衡的搜索树 它类似普通的平衡二叉树()，不同的一点是B-树允许每个节点有更多的子节点。B-Tree 相对于 AVLTree 缩减了节点个数，使每次磁盘 I/O 取到内存的数据都发挥了作用，从而提高了查询效率。I/O渐进复杂度为O(h)

注意B-树就是B树，-只是一个符号。
如果要查找14，就需要查询2次，虽然和红黑树的I/O渐进复杂度一样，但是红黑树结构，h明显要深得多。

深度理解：

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

 mysql> show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree有如下特性：

1、定义任意非叶子结点最多只有M个儿子，且M>2；
2、根结点的儿子数为[2, M]；
3、除根结点以外的非叶子结点的儿子数为[M/2, M]；
4、每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5、非叶子结点的关键字个数=指向儿子的指针个数-1；
6、非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7、非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8、所有叶子结点位于同一层；

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

4.再度优化 【 B-Tree —》升级 —》B+Tree 】

B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。B+树中的B代表平衡（balance），而不是二叉（binary）。B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

数据库B+Tree对于B-Tree优化思路有：

1. 非叶子节点data，只存储索引，可以放更多的索引。
2. 所有叶子节点之间都有一个链指针（顺序访问指针，可以提高访问的性能）。
3. 数据记录都存放在叶子节点中，查询性能稳定，B树查找最坏情况下到叶子节点。

与B-Tree不同的点：

1、非叶子节点的子树指针与关键字个数相同；
2、非叶子节点的子树指针p[i],指向关键字值属于[k[i],k[i+1]]的子树.(B树是开区间,也就是说B树不允许关键字重复,B+树允许重复)；
3、为所有叶子节点增加一个链指针；
4、所有关键字都在叶子节点出现(稠密索引). (且链表中的关键字恰好是有序的)；
5、非叶子节点相当于是叶子节点的索引(稀疏索引),叶子节点相当于是存储(关键字)数据的数据层；
6、更适合于文件系统；
同时： 在B+树中，具有n个关键字的节点只有n棵子树，而B树中具体n个关键字的节点具有（n+1）棵子树。在B+树中，叶节点包含信息，所有非叶节点仅仅起到索引的作用，非叶节点中的每个索引项只含有对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址。叶节点包含了全部关键字，即在非叶节点中出现的关键字也会出现在叶节点中，而在B树种，叶节点包含的关键字和其他节点包含的关键字是不重复的。

深度理解：

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

另一种图表示：

这里解释一下为什么我们看到的标准的B+树中，叶节点之间连接的是单向指针，而在有些地方看到双向指针，这是因为InnoDB存储引擎在使用B+树作为索引存储结构时进行的改造，方便索引到叶节点之后进行范围查找，直接通过指针就可以找到上/下一页的地址。

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节）-----这里使用BIGINT来计算，指针类型也一般为6个字节，也就是说根节点一个页中大概存储16KB/(8B+6B)=1170个键值，而叶子节点的一个页（因为InnoDB存储引擎叶子节点还要存储数据）大概存储16KB/1KB（假设为1kb --索引+数据）= 16。也就是说一个深度为3的B+Tree索引可以维护1170 * 1170 * 16 = 2000多万条记录。所以当Mysql数据个数为2000多万（反正数据很大时）查询性能会急剧下降。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2 ~ 4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

对Hash索引方式的分析

原理： 哈希索引用索引列的值计算该值的hashCode（hash方法），然后在hashCode相应的位置存执该值所在行数据的物理位置（指针），然后去哈希表中去找。

哈希表：存储着对应关系，槽编号是循序的，值数据行不是槽（Slot） 值（Value）。

优点：因为使用散列算法，因此访问速度非常快。

哈希冲突：不同的值得到了相同的哈希码，例如f(‘tao’)=2323 f(‘wang’)=2323,此时就是出现了哈希冲突，当出现哈希冲突时，相同的数据会存储在链表中，遍历链表找到符合的。

特点：

1）哈希索引只包含哈希码和指针，不存储数据字段值。
2）哈希索引数据并不是按循序存储的，因此无法用于排序。
3）因为要通过查询值计算确定的哈希码，所以哈希索引不支持部分匹配，不支持范围查找，只支持等值比较查询。
4）当哈希冲突很多的时候，效率会降低。

使用场景分析

特点 2 、3也是Mysql中为什么默认的索引方法不是Hash方法的原因。当然如果是简单的精确查询，就可以使用Hash方法了，查询效率会比BTree索引有很大的提升。

总结

平衡二叉树的问题

特点：

1.非叶子节点最多拥有两个子节点。
2.非叶子节值大于左边子节点、小于右边子节点。
3.树的左右两边的层级数相差不会大于1。
4.没有值相等重复的节点。

为了解决二叉树数据有序时出现的线性插入树太深问题，树的深度会明显降低，虽然极大提高性能，但是当数据量很大时，一般mysql中一张表达到3-5百万条数据是很普遍，因此平衡二叉树的深度会非常大，mysql读取时会消耗大量IO。不仅如此，计算机从磁盘读取数据时以页(4KB)为单位的，每次读取4096byte。平衡二叉树每个节点只保存了一个关键字(如int即4byte)，浪费了4092byte，极大的浪费了读取空间。

B-树相对于平衡二叉树的优点

平衡二叉树基本都是存储在内存中才会使用的数据结构。

在大规模数据存储的时候，平衡二叉树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。

我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。

磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定。

所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B-树可以有多个子女，从几十到上千，可以降低树的高度，解决了平衡二叉树读取消耗大量内存空间的问题。

B-树的其他优点

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

为了达到这个目的，在实际实现B-Tree还使用了如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

另外如果经常访问的数据离根节点很近，而B树的非叶子节点本身存有关键字其数据的地址，所以这种数据检索的时候会相对其他数据结构更快。

B+树相对B-树的优点

B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。

所以从Mysql(Inoodb)的角度来看，B+树是用来充当索引的，一般来说索引非常大，尤其是关系性数据库这种数据量大的索引能达到亿级别。

所以为了减少内存的占用，索引也会被存储在磁盘上。那么Mysql如何衡量查询效率呢？– 磁盘IO次数。

B-树/B+树 的特点就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数。

但是B-树的每个节点都有data域(指针)，这无疑增大了节点大小，增加了磁盘IO次数，磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时。

所以我们可以看到B+树的优点：

1、B+树的层级更少。

相较于B树B+每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快；

2、B+树查询速度更稳定。

B+所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;

3、B+树天然具备排序功能。

B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。

4、B+树全节点遍历更快。

B+树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

为什么说B+树比B树更适合数据库索引？

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

2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3、由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

PS：我在知乎上看到有人是这样说的,我感觉说的也挺有道理的：

他们认为数据库索引采用B+树的主要原因是：B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

final

我们在MySQL中的数据一般是放在磁盘中的，读取数据的时候肯定会有访问磁盘的操作，磁盘中有两个机械运动的部分，分别是盘片旋转和磁臂移动。盘片旋转就是我们市面上所提到的多少转每分钟，而磁盘移动则是在盘片旋转到指定位置以后，移动磁臂后开始进行数据的读写。那么这就存在一个定位到磁盘中的块的过程，而定位是磁盘的存取中花费时间比较大的一块，毕竟机械运动花费的时候要远远大于电子运动的时间。当大规模数据存储到磁盘中的时候，显然定位是一个非常花费时间的过程，但是我们可以通过B树进行优化，提高磁盘读取时定位的效率。

为什么B类树可以进行优化呢？我们可以根据B类树的特点，构造一个多阶的B类树，然后在尽量多的在结点上存储相关的信息，保证层数尽量的少，以便后面我们可以更快的找到信息，磁盘的I/O操作也少一些，而且B类树是平衡树，每个结点到叶子结点的高度都是相同，这也保证了每个查询是稳定的。

总的来说，B/B+树是为了磁盘或其它存储设备而设计的一种平衡多路查找树(相对于二叉，B树每个内节点有多个分支)，与红黑树相比，在相同的的节点的情况下，一颗B/B+树的高度远远小于红黑树的高度(在下面B/B+树的性能分析中会提到)。B/B+树上操作的时间通常由存取磁盘的时间和CPU计算时间这两部分构成，而CPU的速度非常快，所以B树的操作效率取决于访问磁盘的次数，关键字总数相同的情况下B树的高度越小，磁盘I/O所花的时间越少。

联合索引：联合索引在B+树上结构
联合索引在B+树上的组织方式：联合索引(col1, col2,col3)也是一棵B+Tree，其非叶子节点存储的是第一个关键字的索引，而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。

原文地址：
1、mysql为什么用B 树做索引_为什么Mysql用B+树做索引而不用B-树或红黑树？
2、Mysql进阶之路（二）----Mysql底层索引原理
3、为什么MySQL数据库索引选择使用B+树？
4、一看就懂的：MySQL数据页以及页分裂机制
5、再有人问你MySQL索引原理，就把这篇文章甩给他！
6、假设没有任何索引，数据库是如何根据查询语句搜索数据的？