MySQL索引和数据存储模型

使用MySQL的小伙伴们，应该经常听到MySQL索引这个词，那

MySQL 索引是什么呢？

数据库索引，是数据库管理系统（DBMS） 中的一个排序的数据结构，以协助快速查询、更新数据库表数据。

数据是以文件的形式存放在磁盘上的，每一行数据都有它的磁盘地址，如果没有索引，假如我们要在百万条数据中检索一条数据，只能依次遍历这张表的全部数据（循环调用存储引擎的读取下一行数据的接口），直到找到我们需要的数据为止。

如果我们用了索引之后，只需要在索引里检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，我们找到存放数据的磁盘地址后，就可以拿到数据了。

 

索引类型：

1、普通索引（normal），也叫非唯一索引，是最普通的索引，没有任何的限制。

2、唯一索引（unique），唯一索引要求键值不能重复，另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为 null , 主键索引用 primay key 创建。

3、全文索引（fulltext），针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数据这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引，只有文本类型的字段才可以创建全文索引，比如 char ， varchar ， text 。

MyISAM 和 InnoDB 支持全文索引。

 

索引存储模型

在介绍存储模型的时候，给推荐一个网站，在这里可以看存储数据时，存储模型的详细改变。

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

 

1、二叉查找树（BST Binary Search Tree）。

二叉查找树的左子树所有的节点都小于父节点，右子树的节点都大于父节点，投影到平面以后，就是一个有序的线性表。

二叉查找树既能够实现快速查找，又能够实现快速插入。

但是二叉查找树有一个缺点：

二叉查找树的查询耗时和这棵树的深度有关，在最坏的情况下时间复杂度会退化成0（n） 。

 什么是最坏的情况呢？

那就是如果我们插入的数据刚好是有序的，2，6，11，13，17，22 等。

那二叉查找树就会变成链表（“斜树”），在这种情况下不能达到加快检索速度的目的，和顺序查找的效率没有区别。

造成这种情况的原因是，左右子树深度差太大，上图这棵树的左子树根本没有节点----也就是它不够平衡。

 

2、平衡二叉树（AVL Tree）(左旋，右旋) 。

平衡二叉树定义：左右子树深度差绝对值不能超过 1 。

按顺序插入的 1，2，3，4，5，6 ，最后结果是这样的

上图平衡是怎么做到的呢？怎么保证左右子树的深度差绝对值不能超过 1 呢 ？

插入 1，2，3.

因为它是右节点下面接一个右节点，右-右型，所以这个时候我们要把2提上去，这个操作叫做左旋。

同样的，如果我们插入7，6，5，这个时候就会变成左左型，就会发生右旋操作，把6提上去。

所以为了保持平衡， AVL 树在插入和更新数据的时候执行了一系列的计算和调整操作。

平衡的问题解决了，那平衡二叉树作为索引怎么查询数据呢 ？

在平衡二叉树中，一个节点，它的大小是一个固定的单位，作为索引应该存储什么内容呢？

它存储三块内容：

a、索引的键值，比如我们在 id 上面创建了一个索引，我们在用 where id=1 的条件查询的时候，就会找到索引里面的 id 的这个键值。

b、数据的磁盘地址，因为索引的作用就是去查找数据的存放地址。

c、因为是二叉树，它还必须要有左子节点和右子节点的引用，这样我们才能找到下一个节点，比如大于26的时候，走右边，到下一个树的节点，继续判断。

数据如果这样存储在数据磁盘里，我们看一下会有什么问题，

首先，索引的数据，是存放在硬盘上的，查看数据和索引的大小：

当我们用树的结构存储索引的时候，因为拿到一块数据就要在 server层判断是不是需要的数据，如果不是就要再读一次磁盘，访问一个节点就要跟磁盘之间发生一次 IO 。InnoDB 操作磁盘的最小单位是一页（磁盘块），大小是 16 K（16384节）

也就是说，一个树的节点就是 16K 大小。

如果我们一个节点只存储一个键值+数据+引用，例如整形的字段，可能只用了十几个字节或者几十个字节，远远达不到16K，所以访问一个树节点，进行一次 IO 操作，浪费了大量的空间。

所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就需要访问更多的节点，而每访问一次树节点，就需要进行一次IO 操作，意味着和磁盘交互次数越多，消耗的时间就越多。效率低。

就如上图，我们一张表里面有 6 条数据，当我们查询 id = 66 的时候，要查询两个子节点，就需要跟磁盘交互3次，假如我们有上百万条数据呢？这个时间就太久了。

所以我们应该如何解决这种问题呢?

1、尽可能的让每个节点存储更多的数据。

2、节点上的关键字数量越多，我们的指针数也越多，也意味着可以有更多的分叉（路数）。因为分叉越多，树的深度就会减少（根节点是0）.这样，我们的树就从高瘦的形状，变成了矮胖形状，就像把一个瘦高个一锤砸扁了，这个时候，我们的树就不是二叉了，而是多叉，或者叫做多路。

3、多路平衡二叉树（B Tree）(分裂，合并) 。

balanced tree  这个就是我们的多路平衡查找树，也叫做 B Tree （B 代表平衡）。

和 AVL 树一样，B树在枝节点和叶子节点存储键值、数据地址、节点引用。

它有一个特点：分叉数（路数）永远比关键字数多 1 ，比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点。

B Tree 的查找规则是什么样的？

比如在上图表中查找15，因为15小于17 ，所以走左边；因为15大于12，所以走右边，在磁盘块7里面就找到了15，只用了3次IO。

那 B Tree 又是怎么实现一个节点存储多个关键字，还保持平衡呢？跟 AVL 树有什么区别？

比如 Max Degree（路数）是3的时候，我们插入数据1，2，3，在插入3的时候，本来应该在第一个磁盘块，但是如果一个节点有三个关键字，意味着有4个指针，子节点会变成4路，所以这个时候必须进行分裂（其实就是 B+Tree），把中间的数据2提上去，把1和3变成2的子节点。

如果删除节点，会有相反的合并的操作。

注意这里是分裂和合并，跟 AVL 树的左旋和右旋是不一样的。

如果我们继续插入4，5， B Tree 又会出现分裂和合并的操作。

节点的分裂和合并，其实就是 InnoDB 页（ page）的分裂和合并。

4、B + 树 (加强版多路平衡查找树)

B Tree 的效率已经很高了，为什么 MySQL  还要对 B Tree 进行改良，最终使用了 B+Tree 呢 ？

总体上来说，这个B 树的改良版解决的问题比 B Tree更全面。

我们看一下 InnoDB  里面的 B+树的存储结构，如下图：

MySQL 中的 B+Tree 有几个特点？

1、它的关键字数量和路数相等。

2、B + Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才会存储数据。

搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索上图表中的 id = 28，虽然在第一层直接命中了，但是数据地址在叶子节点上，所以还需要继续向下搜索，一直到叶子节点。

3、B + Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成一个有序链表的结构。

InnoDB 中的 B + Tree 这种特点的优势：

1、它是 B Tree的优化版，B Tree 能解决的问题，它都能解决。B Tree 能解决的两大问题。（a、每个节点存储更多的关键字；b、路数更多。）

2、扫库、扫表能力更强。（对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整颗 B + Tree 拿到所有的数据。）

3、B + Tree 的磁盘读写能力更强。（根节点和枝节点不保存数据，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多。）

4、排序能力更强。（因为叶子节点上有下一个数据区的指针，数据形成了链表。）

5、效率更加稳定。（B + Tree 永远是在叶子节点拿到数据的，所以IO次数更稳定，读取数据效率稳定。）

因为 B Tree 和 B + Tree 的特性，它们广泛的用在文件系统和数据库中，例如 Windows 的 HPFS 文件系统，Oracel , MySQL ， SQLServer 数据库。