MySQL 索引原理- 索引与 B+Tree
MySQL索引类型
MySQL的索引一般而言都是B+树,但是也有些时候可以使用哈希索引。
哈希索引:
- 查找速度非常快
- 是一种内存存储引擎
- 是一种散列的查找方式,不支持排序和范围查找
一般情况下我们都是用B+树
- 一种基于硬盘存储所创建的变种树
- 支持排序,范围查找
- 查询速度也非常可观,而且是查找速度是稳定的。
为什么选B+树作为存储结构
那什么是B+树?
呃,这个说来话了,我们以光速扫描一遍数据结构基础
顺序查找
我们知道索引是一种数据结构,假如我们的索引是数组,那么我们查找数据是顺序查找的,查小一点的数字还好说,如果数字太大了,我们几乎要走一遍完整的数组。
二分查找
大家都不陌生吧,但是二分查找要有顺序要求,虽然查询快了,但是要维护顺序,我们的增删慢了。
这里三步就找到了。
二叉树
是二分查找思想的一种二叉树,是有序的。
- 一个节点的左孩子的值比该节点的值小
- 一个节点的右孩子的值比该节点的值大
也是三次就找到了5
二叉树的问题
二叉树的性质导致了向二叉树添加有序的数时,会单向生长。这直接变成链表了,查找速度更加慢了。
平衡二叉树
平衡排序树是一种二叉排序树的变种,为了解决单向生长的问题,加入一些旋转操作来实现左右平衡。
红黑树
红黑树是平衡二叉树的一种,优化了平衡二叉树。
- 如果数据在内存中,红黑树效率非常高
红黑树的问题
- 如果数据才磁盘中,需要一次性加入到内存中,吃内存资源
- 如果数据量很大,那么我们不一定能一次都加载进内容,如果一棵树无法一次性加载所有数据,谈何查找呢
平衡多路查找树(B树)
这时,我们的为文件系统量身定制的新树种诞生了!
B树数一种自平衡树状数据结构,一般用于存储系统上,比如数据库或者文件系统。
我们来看看B树是什么样的:
一个三阶的B树,一个节点最多存放2个节点,即阶数-1。当我们达到阶数(图中是3时),裂变。
B树的特点
- 比如我们要找3,我们需要找4->2->3 走了三步,我们找2 4->2 走了两步,所以不同数据查询速度不一定相同,即查找速度不均衡;
- 当我们要进行范围查找时,比如我们找1~5,我们的查找顺序是:4->2->1->2->3->2->4->5->6 走了9步,因为有很多返回操作,查找需要中序遍历。
扯了这么多,快忘记这篇文章是讲索引了,但是这些东西不讲不行啊!
B树存储数据信息
B树实际上不是我们的MySQL的存储数据结构,实际上是B+树但是很接近了!
假如给定一张表,我用id作为索引。
+-------+--------+-----+--------------+---------------------+-------+
| empid | name | sex | title | birthday | depid |
+-------+--------+-----+--------------+---------------------+-------+
| 1 | 小王 | 男 | 外卖小哥 | 1999-11-19 09:48:58 | 10001 |
| 2 | 小勇 | 男 | 外卖小哥 | 1998-02-23 00:00:00 | 10001 |
| 3 | 小刘 | 男 | 工程师 | 2000-01-13 00:00:00 | 10001 |
| 4 | 小红 | 女 | 清洁工 | 1995-12-12 00:00:00 | 10002 |
| 5 | 小霞 | 女 | 清洁工 | 1989-07-14 00:00:00 | 10002 |
| 6 | 娜姐 | 女 | 管理者 | 2002-03-04 00:00:00 | 10003 |
| 7 | 金哥 | 男 | 管理者 | 1979-09-02 00:00:00 | 10003 |
+-------+--------+-----+--------------+---------------------+-------+
如图,这时我们可以理解为1,2,3,4,5等数字就是主键了,然后Data就是我们具体信息,存放了name,sex,title,birthday,depid等属性值。此时的数据结构还是B树。
我们发现B树的一些问题:
- 查询效率不均衡,比如4我们一步查到,1我们要3步查到
- 范围查找需要中序遍历。
- 每个节点都带数据
在计算机中,所以与空间相关的东西都是按照块(block)进行存取和操作的
那么问题来了,前两点还可以接受,假设我们一个节点的大小是16K,那么我们每次查找就代表了一次I/O,如果我们需要寻址遍历的次数更多,就要更多的IO,很明显我们很多查找操作都在浪费系统资源。这时候我们需要更牛逼的树来解决这些问题。
B+树
B+树和B树的区别
- B+树只有叶子节点存储数据
- 非叶子节点只起了索引的作用
- 所有的叶子节点使用了链表链接。
B+树的优势
- 查询效率均衡
我们看到只有叶子节点存数据,而且叶子节点都在同一层,所以查询次数都一样。 - 磁盘读写代价更低
我们看到只有叶子节点存放数据,非叶子节点不存数据,只是起索引作用,而且我们看到了我们的3,7,9这些非叶子节点实际上能在叶子节点找到对应的索引值相同的节点,假如我们的Data数据是16K,索引是0.06K,那么我们每次磁盘I/O的代价更低。
我们的B+树又称为矮胖树,图中是三阶B+树,实际上我们每个块能存储的索引是B树的很多倍,也就是说我们的数变的又矮又胖,相应的磁盘I/O读写次数减少了很多。 - 随机I/O的次数更少
随机I/O是指读写操作时间连续,但是访问地址不连续,假设时长为10ms。
顺序I/O是指读写操作基于逻辑块逐个连续访问来自相邻地址的数据,假设时长为0.1ms。
在相同情况下,B+树由于存储地址是连续的,所以更多的是顺序I/O,比B树效率高的多。
简单总结:
- 降低磁盘读取代价
- 顺序I/O提高效率
- 查询速度稳定
聚簇索引和非聚簇索引
我们知道常用索引有5种(基于InnoDB)
- 主键索引
- 唯一索引
- 普通索引
- 组合索引
- 全文索引
除了主键索引以外,其他索引都称为辅助索引,字面意思。
在我们的InnoDB中,主键就是聚簇索引,非主键,也就是辅助索引都是非聚簇索引。
什么是聚簇索引和非聚簇索引?
看完这张图,不用解释了吧。主键索引的Data存放完整的信息,非主键索引存放的是主键索引的key,由于我们InnoDB有一个特点:自动生成主键索引
- 如果你指定了主键,那么以此主键为索引。
- 如果你没指定主键,则以一个不重复的列作为主键索引。
- 如果没有一个列满足要求,那么就创建一个组合索引作为主键索引。
- 如果都不满足,则隐式的在表中添加一个列,可以理解为添加了一个id列,值是1,2,3,4递增的。
所以我们InnoDB引擎总是会有聚簇索引的,当我们创建其他索引时,只需要存储key再去查主键索引就不用多浪费空间了。而且我们的矮胖树查询速度很快,不用担心两次查询的速度问题。
MyISAM的B+树
MyISAM的文件系统将数据和索引分离了。
所以无论我们使用主键还是其他列来查,都需要回行(再跑到数据文件里找一遍,不能一次找出),所以MyISAM是不支持聚簇索引的。
MyISAM和InnoDB的区别
InnoDB很多人用,MyISAM没什么人用- 数据存储方式
- InnoDB是由两种文件组成,表结构,数据+索引(放一起了)
- MyISAM是由三种文件组成,表结构,数据,索引
- 索引方式
- 底层都是B+树实现
- InnoDB主键是聚簇索引,辅助索引是非聚簇索引
- MyISAM中数据和索引不在一个文件,所以都是非聚簇
- 其他区别
- 本文讲索引和B+树,其他区别很多,可以去网上查,够你喝一壶的。
总结
- 索引是为了提高数据的检索速度
- 数据库的索引是B+树实现
- InnoDB中主键是聚簇索引,其他非主键是非聚簇索引
- MyISAM是什么东西,好像讲InnoDB的博主都要讲它