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)插入非连续的数据: