一篇文章让你搞懂数据库索引
文章目录
索引往往是数据库面试题当中会提到的重点问题,这里对数据库索引的学习进行了一些归纳和总结,并且归纳了一些网上常见的面试题答案,希望可以帮助到同样在备战面试的小伙伴们
数据在磁盘的存储结构
- 磁盘空间被划分为许多大小相同的块(Block)或者页(Page).
- 一个表的这些数据块以链表的方式串联在一起。
- 数据是以行(Row)为单位一行一行的存放在磁盘上的块中,如图所示.
- 在访问数据时,一次从磁盘中读出或者写入至少一个完整的Block。
什么是索引?
索引就像是一本书的目录,可以方便的为你找到你希望找到的内容。索引一般针对的是外层文件系统,特别是数据库。因为计算机系统一般是输入顺序文件,是线性的,因此不支持高校的搜索。建立索引从本质上来说也是一种以时间换空间的思路,建立索引也需要花费存储空间,因此不是越多越好,而是要根据数据类型建立合适的索引。我们知道磁盘IO的速度一般比访问内存要慢上十倍以上,因此通过设计合适的索引,我们可以将索引文件一次性读取到内存中,从而加速我们获取数据的速度。索引的类型有线性索引、静态索引、倒排索引、动态索引、位索引等。
- 线性索引:按顺序存储索引值,方便二分查找
- 静态索引:在文件创建、初始装入记录的时候生成,在运行中不改变,除非文件再组织
- 多分数:例如AVL、红黑树、B+树等数据结构,典型的作用就是保持了较低的树高,便于读取到外存中,同时可以维持O(nlgn)的搜索速度
索引的优点
- 减少服务器需要扫描的数据量
- 避免排序和临时表
- 将随机I/O变成顺序I/O
动态索引
动态索引结构是与静态索引对应的一种数据结构。为什么需要有动态索引呢?
- 索引的结构本身可能发生改变(插入以及删除数据的过程中)
- 目的:保持高性能(检索时间)典型的动态索引数据结构有B树以及B+树
B树
- B树总是树高平衡的,所有叶子结点都在同一层
- 更新和检索操作只影响一些磁盘块,因此性能很好
- B树把相关记录放在同一个磁盘块中,利用了访问局部性来减少磁盘I/O
- B树保证树中至少有一定比例的节点是满的,这样能够改进空间利用效率。同时减少在检索和更新操作期间的磁盘I/O数量
B树的阶受制于磁盘块的大小,通常一个B树的节点大小需要等于一个磁盘块的大小
对于B树,中序遍历BST树得到的是一个有序的数列
B树的操作
- 插入:找到应该包含插入关键码的叶结点,并且检查是否还有空间。如果这个节点中还有空间,那么就插入关键码,如果没有空间,我们就将这个节点分裂成两个结点,并且把中间的关键码提升到父结点中。如果父结点已经满了,就在分裂父结点,并且再次提升中间的关键码。
- 搜索:读出根结点,并且查找其中包含的关键码K1,K2,…,Kn,找到的话检索成功;如果没找到,根据B树的性质我们可以确定需要查找的关键码值是在某个Ki和Ki+1之间,于是我们取pi继续重复查找。如果我们找到某一个空结点,则表示检索失败。
- 删除:不在叶结点中:跟叶后 继对换;在叶子结点中,我们需要考虑两种情况:如果删除后的关键码不少于[m/2-1],对于一个三阶的B树也就是2-3树,我们可以认为删除后结点关键码不为0,那我们可以直接删除。如果删除后少于[m/2-1],那么我们需要在兄弟结点中移动若干个关键码到该结点中来(父结点中的一个关键码要变化);如果兄弟结点关键码个树等于[m/2-1],那么合并。用2-3树来解释就是删除该关键码后改结点关键码为0,兄弟结点关键码为1,那么两个结点合并。
B+树
学完了B树之后,看到B+树我们首先第一个疑问就是它们有什么不同。
B+树和BST,以及典型的B树最显著的差异是B+树只在叶子结点中存储记录,内部结点存储关键码值。这些关键码值,这就意味着内部结点在结构上和叶子结点存在显著差异。内部结点存储关键码值是为了引导索引,把每个关键码与一个指向叶子结点的指针关联起来。叶子结点来存储数据。在B+树纯粹作为索引的情况下,叶结点则存储关键码和指向实际记录的指针,实际记录存储在磁盘文件中。根据记录大小与关键码大小的比例,m阶B+树的叶结点可能存储少于或者多余m条记录。只是简单地要求叶结点存储足够的记录,至少达到半满。B+树的叶结点一般链接起来,形成一个双链表。这样,通过访问链表中的所有叶结点,就可以按照排序的次序遍历全部记录。
总结一下,三个差异是:
- 内部结点只是为了引导索引,只有叶子结点存储指向实际记录的指针
- 叶子结点利用双向链表连接起来
- k个子结点必须有k个关键码
B+树的操作
B+树的插入、查找、删除等操作其实与B树差距不大,但是我们需要意识到的是,非叶子结点保存的其实是叶子结点值的“复写”,我们来列举一下不同的操作
- 插入:类似
- 删除:在叶结点删除后,其上层的副本可以保留,也可以作为一个“分界关键码”存在,或者替换为新的最大关键码
使用B+树与B树相比有什么好处
通过对比b树以及b+树的差异,通过思考可以得出以下两个优点:
-
非叶子节点不会带上指针信息,只有关键码,这样,一个块中可以容纳更多的索引项,一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点,存储效率更高。而B树每个结点都包含了关键码和其指向存储地址的指针。
-
叶子节点之间通过指针来连接,适合范围查询。只要找到了范围内的第一条记录,通过顺序处理结点中的奇遇记录,然后继续下去,经可能深入叶结点链表,就可以找到范围内的其他记录。而对于B树来说,则需要在叶子节点和内部节点不停的往返移动。
-
B+树存储效率更高,检索层次更少(树更矮),因此B+树的应用更为广泛
Mysql的索引
我们知道Mysql有两个存储引擎,分别是Innodb以及Myisam
ISAM(Index Sequential Access Method)(不是MyIsam)
是为了磁盘存储而设计的,结构采取多级索引的结构,同时还分多种索引类型,包括主索引、柱面索引以及磁道索引等
我们可以看到是针对磁盘的柱面、磁道等来进行设计的
myISAM
MyISAM引擎使用B+树作为索引的数据结构,叶节点的data域存放的是数据记录的地址。其中还分为主索引以及辅助索引,但是在结构上并没有什么不同,只是主索引要求key值是唯一的,而辅助索引的key可以重复
![img](https://i-blog.csdnimg.cn/blog_migrate/c3cef05c1d5f75117d8772be1669cb32.png)
Innodb索引
索引的分类
- 主键索引
- 二级索引
- 聚集索引
- 非聚集索引
- 联合索引
- 覆盖索引
- 哈希索引
- 全文索引
聚集索引
聚集索引并不是一种单独的索引类型,而是一种数据存储方式。具体的细节依赖于实现方式,但是InnoDb的聚集索引实际上是在同一个结构中保存了B+树的索引和数据行。
根据上面的介绍,聚集索引是利用B+树来实现的,叶子结点即保存了数据行。由于空间的原因,一条数据只能保存在一个B+树中,一张表只能有一个聚集索引。一般的应用是利用主键来建立聚集索引,如果一张表没有定义主键的话,那么将会隐式的定义一个主键来作为聚集索引。这里扩展出一个点,利用uuid来作为主键是不是一个好的方法呢?答案是否定的,因为我们最好利用递增的整形来作为主键,采用uuid会产生的问题InnoDB无法加单的把一行记录插入到索引的最后,而是需要找一个合适的位置(已有数据的中间位置),甚至产生大量的页分裂并且移动大量数据,在寻找合适位置进行插入时,目标页可能不在内存中,这就导致了大量的随机IO操作,影响插入效率。除此之外,大量的页分裂会导致大量的内存碎片。简而言之就是每次插入都会改变二叉树之前的数据状态。从而导致了页分裂和碎片,因此不适合作为聚集索引。
- 写入的目标页可能已经刷到磁盘中并从缓存中移除,或者还没有加载到缓存中。插入之前必须找到并且从磁盘读取页到内存中,导致大量的随机I/O。
- 写入是乱序到,InnoDB必须的做页分裂操作来为心行分配空间。页分裂会导致移动大量数据,一次插入至少需要修改三个页面不是一个页面
- 由于频繁的页分裂,页会变得稀疏而且不规则填充,所以最终数据会有碎片
聚簇结构的特点:
- 根据主键查询条目时,不用回行(数据就在主键节点下)
- 如果碰到不规则数据插入时,造成频繁的页分裂
非聚集索引
是指表中各行的物理顺序与键值的逻辑(索引)顺序不匹配,表中可以有249个非聚集索引;非聚集索引就像新华字典的偏旁字典,他结构顺序与实际存放顺序不一定一致。非聚集索引叶节点仍然是索引节点,只是有一个指针指向对应的数据块,此如果使用非聚集索引查询,而查询列中包含了其他该索引没有覆盖的列,那么他还要进行第二次的查询,查询节点上对应的数据行的数据。即会产生二次查询问题,查询非聚集索引覆盖以外的列就会变成聚集索引的全索引扫描(index scan)查询来避免二次查询。如何解决二次查询问题呢?这就要用到覆盖索引了。
覆盖索引
如果一个索引包含(或覆盖)所有需要查询的字段的值,我们就称之为覆盖索引。覆盖索引必须存春索引列的值。
- 索引条目远小于数据行大小,如果只需要读取索引没那么会极大减少数据访问量
- 索引是按照列值存储的,对于范围查询来说会比随机从磁盘读取每一行数据的I/O少得多
- 利用系统缓存
- 避免二次查询
主键索引
要求主键中每个值是唯一的
二级索引
叶子节点中存储主键值,每次查找数据时,根据索引找到叶子节点中的主键值,根据主键值再到聚簇索引中得到完整的一行记录。InnoDB有了聚簇索引,为什么还要有二级索引呢?当我们需要为表建立多个索引时,如果都是聚簇索引,那将占用大量内存空间,所以InnoDB中主键所建立的是聚簇索引,而唯一索引、普通索引、前缀索引等都是二级索引。
全文索引
全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值。你可能会说,用 like + % 就可以实现模糊匹配了,为什么还要全文索引?like + % 在文本比较少时是合适的,但是对于大量的文本数据检索,是不可想象的。全文索引在大量的数据面前,能比 like + % 快 N 倍,速度不是一个数量级,但是全文索引可能存在精度问题。
哈希索引
哈希索引(hash index)基于哈希表来实现,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个比较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向同一个哈希条目中。
哈希索引不存储字段值,因此必须读取行来获取数据。同时不是顺序存储的,因此无法用来排序。同时如果是根据(A,B)列来建立的,那么只查询B列的时候无法使用哈希索引。同时还需要处理哈希冲突的问题,所以只适合特定的场合。
Innodb还会创建自适应哈希索引,在某些索引值用的非常频繁的时候,会在内存中基于B+树产生的索引在创建哈希索引,可以通过设置来关闭。
联合索引(多列索引)
最左匹配规则
所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引,那么这条 SQL 语句就可以利用这个联合索引去进行匹配,值得注意的是,当遇到范围查询(>、<、between、like)就会停止匹配。
Mysql有一种索引合并的策略,一定程度上可以用表上的多个单位索引来定位指定的行。如何选择索引的顺序呢?我们需要将选择性最高的列放在索引的最前列,区分度低的放在后面,例如性别、状态等字段区分度很低,我们就把他们放在后面。
InnoDb索引结构注意点
- 数据文件本身就是索引文件
- 表数据文件本身就是按B+Tree组织的一个索引结构文件
- 聚集索引中叶节点包含了完整的数据记录
- InnoDB表必须要有主键,并且推荐使用整型自增主键
参考资料:
数据结构与算法分析
高性能Mysql
中国大学Mooc 北京大学 数据结构与算法
其他csdn博主