MySQL索引底层数据结构剖析
MySQL索引
索引是一个排好序的数据结构。
为什么要使用索引
索引就是数据库中表的目录一样,可以快速的定位到对应数据所在的页。如果没有索引,那查找一个数据,就得一页一页的去找,花费的时间和效率不计其数。
索引可以有哪些数据结构
二叉树、红黑树、hash、B树、B+树等都可以是索引的数据结构,而MySQL数据库中,只采用了hash和B+树这两种数据结构。下面分析一下为什么数据库中会采用这两种,而不使用其他的数据结构。
数据结构网址 https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
索引数据结构
二叉树
二叉树,是指节点度不大于2的有序树。他的左子树小于右子树,这种有序的结构,十分利于查找。下面是一个二叉树的结构。
对于二叉树的结构,如果我们插入的数据是依次递增的或者是依次递减的,那这个二叉树实际的结构会是一个链表(如下图),这样的话实际查找的效率并不会高,而且我们平常设计表的时候,主键都是依次递增的。
为了解决二叉树的这个问题,出现了下面的一种数据结构——红黑树。
红黑树
红黑树是一种平衡二叉树的变体,他的树的高度差有可能大于1,这种结构虽然不是严格的平衡二叉树,但他的平均性能要高于平衡二叉树。下面是红黑树的一个结构。
红黑树解决了二叉树元素递增插入的一个问题,但他本身也有一个问题。假设这个红黑树是一个满二叉树,他的节点个数 = 2^n -1。假设一个表有100w条数据,树的高度大约是1000
如果查询的数据是叶子节点,那最少要进行1000次磁盘IO。
要解决这个问题,就是要限制住树的高度,在树高不超过3层的情况下,尽量的去存更多的数据,这就是下面这种数据结构。
B 树
B树是一种自平衡树,可以拥有多于2个子节点。下面是B树的一种结构
B树解决了树高的问题,但对于范围查询,他的效率极低,所以在B树的基础上引出了下面的一种数据结构
B+ 树
B+树是B树的一种变形,他的叶子节点包含了所有的元素,叶子节点与叶子节点之间通过指针连接,下面树B+树的结构
这种结构即解决了树高的问题,也解决了范围查询的问题。
MySql中的索引
打开MySQL的索引页面,可以看到有两个索引,BTree和hash。
这里的BTree是B+树。
MySQL中的B+树
MySQL中的B+树,存储的数据在innodb和myisam不同的引擎里面是不一样的,下面分别介绍一下两个引擎的区别。
innodb
- 在innodb中索引主要分为主键索引(聚簇索引)和非主键索引。主键索引叶子节点包含了表的所有数据,所有每个表中必有一个主键索引,如果这个表没有设置主键,那么会根据表的列去判断,列不唯一则默认为主键,没有符合的列,则会在表最后创建一个隐藏的列作为主键。
- 主键索引只能有一个,非主键索引可以有多个
- 非主键索引的叶子节点包含了索引列和主键
主键索引B+树结构
从上图可以看出
- 非叶子节点存放的都是主键
- 叶子节点存放了所有的主键和数据
- 两个叶子节点之间通过双向指针连接
非主键索引B+树结构
从上图可以看出
- 非主键索引的叶子节点存放的是主键而不是所有的数据
MyISAM
MyISAM中没有聚簇索引,主键索引和非主键索引的结构是一样的。
- MyISAM叶子节点存放的是索引列和表数据的地址,所以可以没有主键
- MyISAM索引和表是分开存放的
MySQL中的hash
- hash表对于精准查询的效率非常高
- 不适用于范围查询和模糊查询