MySQL一般是很多公司目前工作中用的最多的关系型数据库。如果对MySQL不够熟悉,会导致很多时候我们知其然不知其所以然,孔子云,学而不思则罔,因此博主准备对自己过往学习的MySQL索引概念知识进行总结,也分享给有需要的老铁。
索引
我们初学数据库时,很多书上面都有描述索引的概念。很多书上面将索引比喻为一本书的目录,我们通过目录去迅速找到我们所需的章节内容。
这里对索引的概念做一个总结:
索引是帮助MySQL高效获取数据的数据结构。(在MySQL中,数据最终存储在磁盘中)
在MySQL中,其索引的数据结构使用的是B+树。
B+Tree
问:MySQL为什么不用二叉搜索树、平衡二叉树或者B+树?
二叉搜索树
概念:
二叉查找树(Binary Search Tree),(又:二叉搜索树,二叉排序树)它或者是一棵空树,或者是具有下列性质的二叉树: 若它的左子树不空,则左子树上所有结点的值均小于它的根结点的值; 若它的右子树不空,则右子树上所有结点的值均大于它的根结点的值; 它的左、右子树也分别为二叉排序树
二叉搜索树特点为一个父节点最多分裂出两个子节点,且左子树永远小于右子树。
以图为例,若查询col2=71这种情况时,二叉搜索树的搜索效率为4,即目标数据所在树的层数决定了其查找的时间长度。
考虑极端情况,假如一个二叉搜索树仅有左子树和右子树:
此时变成了我们熟悉的另一个数据结构:链表——极大浪费了资源,且搜索效率极低。
平衡二叉树(AVL)
平衡树(Balance Tree,BT) 指的是,任意节点的子树的高度差都小于等于1。
左子树和右子树全部为平衡二叉树,左子树和右子树的深度之差不能超过1。
动画了解平衡二叉树:https://www.cs.usfca.edu/~galles/visualization/AVLtree.html
当插入的数据导致深度之差大于1时会触发树的左旋和右旋。
以下面数据为例,当给col1列添加索引时,二叉搜索树的数据结构很容易变成如下图所示结构:
当使用AVL这种结构时,如下图所示:
时间复杂度由上面需要查询9次变为查询4次。此处明显加快了查询效率,但作为二叉树无可避免会出现一个问题:父节点下面一定带了两个子节点,在数据量很大的情况下,树会纵向不断扩展,而查询某个数据的查询效率跟树的深度有关,找到该条数据的查找次数会很大,而CPU从磁盘获取数据时,每次写入内存都是一次IO,即需要进行N次IO才能找到我们要找的数据。(IO次数过多)
innodb的每次从磁盘读取数据都有一个页大小,innodb_page_size默认大小为16K,如果使用平衡二叉树,需要加载的节点内容(关键字+数据区+子节点引用)大小估计值为20byte,每次IO拿出的数据量远远小于16K!(一次IO目标数据过少造成极大IO浪费)
B-Tree
对于索引中数据为4->8这种情况,一般被划分为三个区域:
(无穷小,4)
(4,8)
(8,无穷大)
改善了AVL的部分缺点,B-Tree有如下特点:
①每个节点的关键字由一个变为多个;
②绝对平衡;
③节点都在同一水平线上
(某一层 关键字的个数 = 路数(图中箭头) + 1 或 路数 - 1)
为了说明为什么不使用B-Tree,需要先引出B+Tree的概念。
B+Tree
和B-Tree比:
①关键字个数和路数间关系为1:1;
②1,28,66划分出的区间为:
[1,28)
[28,66)
[66,无穷大)
③取消了非叶子节点的数据区,所有的数据区内容仅存在最后一层叶子节点中。
④相邻叶子节点天然有序且有指向关系,一个节点的末尾关键字指向了相邻节点的头关键字。
B-Tree与B+Tree的区别
经过对比我们发现,B+Tree有如下优势:
①基于索引的扫表更快。虽然命中某一条数据时,可能存在B-Tree只需要扫到某一层就可以返回这种现象,但是在执行select * from table这类语句时,B-Tree不可避免的需要扫描到所有的节点,而B+Tree只需要扫描叶子节点返回即可;
②基于索引的排序更强。select * from table order by id这类语句的排序动作,B-Tree需要获取到所有数据并在内存中做一次排序动作;而B+Tree的索引天然有序;
③IO的能力得到了加强。MySQL中一页默认大小为16KB,B+Tree非叶子节点移除了数据区,装载的因子更多。