MySQL性能调优-(四)索引数据结构

数据结构

hash 表

hash 表大家应该都比较熟，hash 表就是一个数组，然后在每一个数组的下面可以添加一个数据桶，以链表的方式进行实现。hash 表是有对应一个下标的，从0 开始进行排列，如果想要往 hash 表放数据的话，必须要经过散列算法，然后计算出对应的位置，将数据放到指定的位置。而散列算法最简单的就是进行取模运算。如果数组的长度是 8，那么在存储数据的时候，计算的下标位置就是 0-7，那么正好能定位到我们数组里面的某一个位置，然后如果不同的数据计算出来的下标值是一模一样的直接在数组下添加链表即可。
hash 表看起来很好，能够根据对应的下标位置直接的定位到某一条记录，但是需要注意 hash 表同样也有着明显的缺点：
1、需要占用大量的内存空间，每次在使用 hash 表的时候需要将全量的数据加载到内存中，此时是比较浪费内存空间的，所以在 mysql 的 memory 存储引擎中使用了 hash 索引，像 innodb 这种存储引擎支持自适应 hash,但是是由 mysql自己控制的，不是认为控制的。
2、每次进行数据查询的时候都是等值查询，需要根据 key 计算出 hash 值 ，然后定位到某一个位置，进行 key 的比较，但是大家在公司的应用场景中更多的可能是范围查询，而 hash 表在进行范围查询的时候必须要挨个匹配，这样的话查询回比较浪费时间，所以不太合适。最终我们可以得出一个结论，如果每次查询都是等值查询，那么 hash 表的方式查询是比较快的，但是如果是范围查询的话，查询比较慢。
3、使用 hash 表在存储数据的时候，需要设计比较优秀的 hash 算法，如果算法设计不合理的话，会导致数据散列不均匀，浪费比较多的存储空间，同时在数据查询的时候也会导致查询效率较低。基于上面这些缺点，在 mysql 中 innodb 和 myisam 的存储引擎并没有使用hash 表来存储索引数据，而 memory 存储引擎使用了 hash 表这样的方式，索引要注意的是这个数据结构的选择是跟存储引擎相关的。

二叉树
经过上面的描述大家已经知道了 hash 表的缺点，那么为什么不用二叉树呢？在 mysql 的早期版本中，确实也使用过二叉树作为索引的数据结构，但是后来换成了 B+树，那么大家就要思考一件事，二叉树为什么不行？其实也非常简单，二叉树有自己独特的优点，就是可以使用二分查找的方式来进行数据检索，如下图，在二叉树中可以插入 1-9 数据：

在上述图中，如果需要进行数据检索，所以可以进行二分查找的方式，但是不得不考虑的是另外的极端情况，如果插入的是递增或者递减的数据呢？如下图：

在上图中，大家发现二叉树退化成了链表，就是一条腿长一条腿短，这样的话，很明显查询又变成了挨个对比的过程，所以此时就不太合适了，而且当插入的数据越多的时候，会导致链表越长，这样的效率一定是比较低的。
二叉平衡树AVL 树
其实大家可以思考，造成上述问题的最关键原因就是树的左右分支不够平衡，因此后续有了二叉平衡树，即 AVL 树。如下图：

AVL 树要求左子树跟右子树的高度只差不能超过 1，因此在进行数据插入的时候会造成 N 个旋转操作来保证树的平衡，因此在进行数据插入的时候效率比较低，查询的效率比较高，这样的话可以理解损失部分插入的性能来满足查询性能的提升，但是如果插入和查询的需求都比较多的时候，怎么解决呢？而且，插入的数据会越来越多，那么也会造成树越来越深，从而导致查询效率的降低。
红黑树
经过上面的分析我们已经知道了 AVL 树存在的问题，在之后的变种中，还有一种数据结构叫做红黑树，这也是一种二叉平衡树，只不过不是严格意义上的平衡树，在 AVL 树中，要求左右子树的高度只差不能超过 1，而红黑树的要求是最长子树只要不超过最短子树的两倍就好，而且还添加了变色的行为来保证这颗树的平衡，向红黑树中插入 1-10 数字，如图所示：

通过上述的分析我们其实可以得出一个结论，无论是何种类型的二叉树，最终都会有一个问题就是随着数据量的增加，树的层数会增加，那么会增加io 的次数，从而影响数据读取的效率，其实造成这种情况最根本的原因在于树中每一个节点的分支比较少，如果分支变多的话，那自然而然能够降低树的高度。

B 树
在 B 树中有一个 degree 的概念，可以翻译成度或者翻译成阶，但表示的是同一个意思，表示每个节点中至多可以存储 N-1 个节点，向 B 树种插入 1-10 的数据，如下图所示：

可以看到每一个节点中可以插入多个 key 值了，可以包含多个分支，这样树的深度就不会变深，那么进行数据查找的时候 io 次数会减少，那么查询的速度就会提升。在实际的 mysql 数据存储中，B 树的数据存储如下图所示：

实例图说明：
每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为 16和 34，P1 指针指向的子树的数据范围为小于 16，P2 指针指向的子树的数据范围为 16~34，P3 指针指向的子树的数据范围为大于 34。
查找关键字过程：
1、根据根节点找到磁盘块 1，读入内存。【磁盘 I/O 操作第 1 次】
2、比较关键字 28 在区间（16,34），找到磁盘块 1 的指针 P2。
3、根据 P2 指针找到磁盘块 3，读入内存。【磁盘 I/O 操作第 2 次】
4、比较关键字 28 在区间（25,31），找到磁盘块 3 的指针 P2。
5、根据 P2 指针找到磁盘块 8，读入内存。【磁盘 I/O 操作第 3 次】
6、在磁盘块 8 中的关键字列表中找到关键字 28。
缺点：
1、每个节点都有 key，同时也包含 data，而每个页存储空间是有限的，如果
data 比较大的话会导致每个节点存储的 key 数量变小
2、当存储的数据量很大的时候会导致深度较大，增大查询时磁盘 io 次数，
进而影响查询性能
当了解了上述结构之后，我们需要想一件事，这样三层的 B 树能存储多少记录呢？ InnoDB 这种存储引擎默认情况下读的是 16kb，一共读取了三个磁盘块，意味着一共读取了 48k 的数据，假如说上面的这些 p 指针和 16 这些 key 值都不需要占用额外的存储空间，一条数据占用 1kb 的空间，那意味着当前节点里面最多存 16 条数据，下一个磁盘块也是 16 条，第三个磁盘块也是 16 条，计算一下的话是 16×16×16，也就是 4096 条数据。这个支撑的数据量也太少了。在生产环境中随便的一个 mysql 表都要上百万条，不可能是只有几万或者几千，这不太可能，这时候就要思考 b 树有什么问题了。

B+树
既然 B 树种存在问题，那么怎么去解决呢？大家其实发现了，刚刚存储的数据量少的最主要原因是因为 B 树的非叶子节点中的 data 占用了大量的存储空间，所以可以考虑将叶子节点上存储数据，而非叶子节点不存储数据。因此 B+树有了独特的应用场景，如下图：

大家发现所有的数据存储到叶子节点中，这样能解决刚刚存在的问题。mysql存储数据的示意图如下所示：
frl在上图中，叶子节点中存储数据，而非叶子节点不存储数据，能保证尽可能多的存储数据，查找数据的方式不变，我们可以来进行一个计算，看一下三层的 B+树能存储多少数据？读取的数据还是 16kb、16kb、16kb，假设，key 值加上 p 指针一共占用 10 个字节，那么 16kb 就是是 16×1000/10,得到结果为 1600，第二层也是 1600，第三层还是 16，所以最终的结果为 40960000 条数据，达到千万级别。而刚刚 B 树是 4096，完全不是一个量级。 因此，在三到四层的 B+树中，基本可以支持千万级别数据量的存储。

innodb 存储引擎索引与数据存放
在上述的描述中，是对于 mysql 的索引结构的统一描述，其实对于不同的存储引擎而言，虽然使用的都是 B+树这样的数据结构，但是实际在存储数据的时候却是不一样的。
在 innodb 存储引擎中，数据是跟索引放在一起的，因此你看到的只有innodb 的文件，其中既存储实际数据，又存储索引数据，因此当查询索引的时候，能直接从叶子节点中获取到需要的数据行，如下图所示：

当数据进行插入的时候，必须要包含一个索引的值，这个值可以是主键，也可以是唯一键，甚至可以是 6 字节的 rowid，那么在实际选择的时候会按照主键、唯一键、6 字节的 rowid 这样的顺序来选择索引的 key 值。
当一个表中存在多个索引的时候，除了一个索引列是跟数据存储在一起的，其他的索引列的叶子节点中存储的时候数据所在的索引的 key 值，如下图，id 是主键，那么数据是跟主键放在一起的，如果再创建一个 name 列的索引，那么 name所对应的那棵 B+树的叶子节点存储的就是 id 的值：

MyIsam 存储引擎索引与数据存放
在 MyIsam 存储引擎中，数据文件跟索引文件是分开存储的，所以你看到的是两个文件，后缀名称为 myi 和 myd，因此在进行数据检索的时候，需要读取两个文件，在索引的数据结构中实际存储的是实际的数据行地址，如下图所示：

通过上面的解释，我们得到了一个结论，不同的存储引擎在使用的时候虽然数据结构是一样的，但是实际存储的方式还是有所不同的。