一篇文章让你搞懂数据库索引

一篇文章让你搞懂数据库索引

​ 索引往往是数据库面试题当中会提到的重点问题，这里对数据库索引的学习进行了一些归纳和总结，并且归纳了一些网上常见的面试题答案，希望可以帮助到同样在备战面试的小伙伴们

数据在磁盘的存储结构

• 磁盘空间被划分为许多大小相同的块（Block）或者页(Page).
• 一个表的这些数据块以链表的方式串联在一起。
• 数据是以行（Row）为单位一行一行的存放在磁盘上的块中,如图所示.
• 在访问数据时，一次从磁盘中读出或者写入至少一个完整的Block。

什么是索引？

​ 索引就像是一本书的目录，可以方便的为你找到你希望找到的内容。索引一般针对的是外层文件系统，特别是数据库。因为计算机系统一般是输入顺序文件，是线性的，因此不支持高校的搜索。建立索引从本质上来说也是一种以时间换空间的思路，建立索引也需要花费存储空间，因此不是越多越好，而是要根据数据类型建立合适的索引。我们知道磁盘IO的速度一般比访问内存要慢上十倍以上，因此通过设计合适的索引，我们可以将索引文件一次性读取到内存中，从而加速我们获取数据的速度。索引的类型有线性索引、静态索引、倒排索引、动态索引、位索引等。

1. 线性索引：按顺序存储索引值，方便二分查找
2. 静态索引：在文件创建、初始装入记录的时候生成，在运行中不改变，除非文件再组织
3. 多分数：例如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可以重复

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博主