MySQL索引数据结构的选择
1.二叉树及其衍生树
各种树形:多叉树----->二叉树----->平衡树(AVL树)----->红黑树
对于二叉树来说,会存在一种偏向的情况,如下图,使得效率和遍历查找相同。
平衡树:AVL树是一颗严格意义上的平衡树,最高子树跟最低子树高度之差不能超过1,因此在进行元素插入的时候,会进行1到N此的旋转,严重影响插入的性能。
红黑树:红黑树是基于AVL树的升级,损失部分查询的性能,来提高插入的性能,在红黑树中最低子树和最高子树之差小于2倍即可,在插入的时候,不需要进行N多次的旋转操作,而且还加入了变色的特性,来满足插入和查询性能的平衡。
总结:无论是二叉树、AVL树还是红黑树,当数据量非常大时,都会因为树的深度过深而造成磁盘io次数变多的情况,影响数据读取的效率。
2.Hash(散列表)
特点:每次再添加索引的时候需要计算指定列的hash值,取模运算后计算出下标,将元素插入下标位置即可。
举例:比如说下面这张表中的数据,执行sql语句
select * from table where clo2 = 22;
会对结果集进行一次hash运算,获取到散列值,然后可以很快的获取到磁盘文件地址。此时,甚至感觉比b树的效率更快,但是当执行范围查找的sql语句时
select * from table where clo2 > 22;
会根据散列值定位到22的磁盘地址,但是由于是大于查找,所以就相当于逐行的对数据进行比较,进行全表扫描,大大的降低了效率。
总结缺点:
- 范围查找效率低下
- hash表在使用的时候,需要将全部的数据加载到内存,比较耗费内存空间。
- hash索引不稳定,性能不可预测,当存在大量重复数据的时候,会发生hash碰撞。
3.B树(B-树)
特点:
- 所有的键值分布在整棵树中。
- 非叶子结点带有数据,搜索有可能在非叶子结点结束。
- 每个结点最多有用m个子树,根节点至少有2个子树,分支结点至少拥有m/2个子树。
- 所有叶子结点都在同一层,每个节点最多可以有m-1个key,并且以升序排列。
缺点:
- 每个节点都有key,也包含数据data,每个页存储空间都是有限的(一个磁盘块占4k),如果data比较大的话,会导致每个结点存储的key数量减小(每个磁盘块只需要一次io就可以读取到所有的数据,如果data占1kb,那么一个磁盘块就只能存4条数据,整体树也就只能存64条数据,十分的有限)。
- 当存储的数据量很大到时候,会导致深度较大,增大查询时磁盘io的次数,影响性能。
4.B+树(MySQL默认)
B+树在B-树的基础上做了一下优化:
特点:
- 非叶子结点存储key,叶子结点存储key和数据(InnoDB存储引擎)。
- 叶子结点两两指针相互连接(符合磁盘预读的特性),顺序查询性能提高 。
- B+Tree每个结点包含更多的key,这样做的原因是:
- 降低树的高度
- 数据范围变为多个区间,区间越多,数据检索越快。
B+树叶子结点上有双向的指针,使得所有的叶子结点之间形成一种链式环结构,优化了hash范围查找的缺点(比如说,要查询大于15的数据,则可以直接通过指针向后读取,而不用在重新从根节点进行磁盘io读取,大大增加了效率)。
5.总结
综合考虑后,MySQL选择了B+树作为默认的索引数据结构。树型深度只有3层就可以存放百万级别以上的数据,而且无论是随机查找还是范围查找效率都很高。
SQL选择了B+树作为默认的索引数据结构。树型深度只有3层就可以存放百万级别以上的数据,而且无论是随机查找还是范围查找效率都很高。