28-进阶SQL-索引概述

可以看到，上面的例子上，无索引的情况会查找全部的10次得到最终的结果，而有索引的情况会通过二叉排序树的数据结构，只需通过三次的查找就能得到最终的结果，更加的高效。

（这里需要注意，上述二叉树索引结构只是一个示意图，并不是真实的索引结构）

 

在了解索引的数据结构之前，我们先熟悉以下的数据结构：

1. 二叉查找树

二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值

2. 平衡二叉树（AVL Tree）

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1

下面的两张图片，左边是AVL树，它的任何节点的两个子树的高度差<=1；右边的不是AVL树，其根节点的左子树高度为3，而右子树高度为1； 

如果在AVL树中进行插入或删除节点，可能导致AVL树失去平衡，这种失去平衡的二叉树可以概括为四种姿态：LL（左左）、RR（右右）、LR（左右）、RL（右左）。

它们的示意图如下： 

AVL树失去平衡之后，可以通过旋转使其恢复平衡，详细过程参考：BTree和B+Tree详解_b+brtt的结构图-CSDN博客

3. 平衡多路查找树（B-Tree）

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

B-Tree具有以下性质：每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点； 所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中（同样满足二叉树的左小右大原则，每次分裂从中间元素分裂）。

以一颗最大度数为3阶的B-Tree为例（每个节点最多存储2个key，3个指针）
插入数据：001，002，003，004，005，006，007，008，009

4. B+Tree（MySQL使用B+Tree索引的存储结构）

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B+Tree具有以下性质：在B-树基础上，增加一个指向相邻叶子节点的单向链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中。

相比B-Tree来说，B+Tree的所有数据都会出现在叶子节点上，并且叶子节点会形成一个单向链表，每个叶子节点都指向相邻的叶子节点的地址。通过这种方式，B+Tree树进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

以一颗最大度数为3阶的B+Tree为例（每个节点最多存储2个key，3个指针）：

插入数据：001，002，003，004，005，006，007

MySQL 索引数据结构，对经典的B+Tree进行了优化

在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针（不再是单向链表），就形成了带有顺序指针的B+Tree，提高了区间访问的性能。

5. Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决

Hash索引的特点：

1）Hash索引只能用于对等比较（=，in），不支持范围查询（between，>，<，...）

2）无法利用索引完成排序操作

3）查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引

索引引擎支持：

在MySQL中，支持hash索引的是Memory引擎，而innoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的

 

扩展：数据结构可视化网站

Data Structure Visualization (usfca.edu)

 

案例：假设有一张学生表，id为主键

 

这里需要注意：

为什么InnoDB存储引擎选择使用B+Tree索引结构？

1）相比二叉树，层级更少，搜索效率更高

2）相比B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，而B+Tree只有在叶子节点才保存数据。也就是说B-Tree的方式会导致一页中存储的键值减少，指针也跟着减少，要保存大量数据时，只能增加树的高度，导致性能降低

3）相比Hash，B+Tree支持范围匹配以及排序操作

为什么官方建议使用自增长主键作为索引？

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

1）插入连续的数据：

 

2）插入非连续的数据：