数据库索引详解

1.什么是索引？索引的常见类型有什么？

索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

分类：这里我们主要介绍常见的聚集索引和非聚集索引

聚集索引：对正文内容按照一定规则排列的目录称为聚集索引

非聚集索引：目录自己按照一定规则排列，正文自己按照另一种规则排列，目录主要是保存对正文的一个映射关系，这种称为非聚集索引

看完上边的定义，你可能对聚集索引和非聚集索引还是一头雾水，没关系，看看下边的例子你应该就明白了

以我们的汉语字典为例，我们要查一个字“爱”，我们知道它的发音，我们会自然的翻开目录中的A目录下去找读音为“ai"的字，找到它并找到它具体对应到哪一页，这里A目录下所有字具体在哪一页都是连续的，假设A目录下”爱“对应18页，在A目录中”爱”的前一个字是“矮”，对应的是17页，也就是他们在目录中的相对顺序也是他们在具体页数中的相对顺序，这个就是聚集索引。

但是如果我们遇到一个字，并不知道它的读音，我们就会采用另一种查找方式，根据“偏旁部首”去查找，然后根据这个字后的页码直接翻到某页来找到您要找的字。但你结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如你查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页，这样目录中的排列方式并不是正文实际的排列方式，这就是非聚集索引。

2.索引采取什么数据结构存储？为什么采取这样的数据结构？

大规模的数据不可能全部存储在内存中，故要存储到磁盘上，这样查找读取等操作时就涉及到磁盘IO，那么索引就要尽量减少磁盘IO次数，才能保证查找速度。如果采用普通的二叉查找树结构，会由于树的高度过深进行多次磁盘IO，导致查询效率低下，那么就要尽量减少树的高度，这就引出了B-Tree和B+-Tree,即B树和B+树。

总结：为什么使用B+树？

1.文件很大，不可能全部存储在内存中，故要存储到磁盘上

2.索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数（为什么使用B-/+Tree，还跟磁盘存取原理有关，具体看下边分析）

3. 局部性原理与磁盘预读，预读的长度一般为页（page）的整倍数，（在许多操作系统中，页得大小通常为4k）

4. 数据库系统巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样 每个节点只需要一次I/O 就可以完全载入，(由于节点中有两个数组，所以地址连续)。而红黑树这种结构， h 明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性

以下资料出自：http://blog.csdn.net/v_JULY_v 。

磁盘的构造

磁盘是一个扁平的圆盘。盘面上有许多称为磁道的圆圈，数据就记录在这些磁道上。磁盘可以是单片的，也可以是由若干盘片组成的盘组，每一盘片上有两个面。如上图11.3中所示的6片盘组为例，除去最顶端和最底端的外侧面不存储数据之外，一共有10个面可以用来保存信息

当磁盘驱动器执行读/写功能时。盘片装在一个主轴上，并绕主轴高速旋转，当磁道在读/写头(又叫磁头) 下通过时，就可以进行数据的读 / 写了。

一般磁盘分为固定头盘(磁头固定)和活动头盘。固定头盘的每一个磁道上都有独立的磁头，它是固定不动的，专门负责这一磁道上数据的读/写。

活动头盘 (如上图)的磁头是可移动的。每一个盘面上只有一个磁头(磁头是双向的，因此正反盘面都能读写)。它可以从该面的一个磁道移动到另一个磁道。所有磁头都装在同一个动臂上，因此不同盘面上的所有磁头都是同时移动的(行动整齐划一)。当盘片绕主轴旋转的时候，磁头与旋转的盘片形成一个圆柱体。各个盘面上半径相同的磁道组成了一个圆柱面，我们称为柱面 。因此，柱面的个数也就是盘面上的磁道数。 

磁盘的读写原理及效率

磁盘上的数据需要使用一个三维地址来表示：柱面号、盘面号和块号

读/写磁盘的三个步骤：

(1)  首先移动臂根据柱面号使磁头移动到所需要的柱面上，这一过程被称为定位或查找 。

(2)  如上图11.3中所示的6盘组示意图中，所有磁头都定位到了10个盘面的10条磁道上(磁头都是双向的)。这时根据盘面号来确定指定盘面上的磁道。

(3) 盘面确定以后，盘片开始旋转，将指定块号的磁道段移动至磁头下。

耗费时间：

• 查找时间：即完成步骤（1）的时间，这部分耗时最多
• 等待时间：即完成步骤（3）的时间
• 传输时间：数据通过系统总线送到内存的时间

磁盘读取数据是以盘块(block)为基本单位的。位于同一盘块中的所有数据都能被一次性全部读取出来。而磁盘IO代价主要花费在查找时间Ts上。因此我们应该尽量将相关信息存放在同一盘块，同一磁道中。或者至少放在同一柱面或相邻柱面上，以求在读/写信息时尽量减少磁头来回移动的次数，避免过多的查找时间

B-Tree

这里不对B-Tree的基础概念的介绍，请先自行百度了解。这里主要介绍一个B树查找的例子。

这里用少量数据构造一棵3叉树的形式，实际应用中的B树结点中关键字很多的。上面的图中比如根结点，其中17表示一个磁盘文件的文件名；小红方块表示这个17文件内容在硬盘中的存储位置；p1表示指向17左子树的指针。

我们假设一个盘块刚好只能存储一个结点，那么上图中一个结点就表示一个盘块，其子树指针就是指向另一个盘块的地址。

现在我们来模拟查找文件29的过程：

1. 根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。【磁盘IO操作 1次】    
2. 此时内存中有两个文件名17、 35 和三个存储其他磁盘页面地址的数据。根据算法我们发现：17<29<35，因此我们找到指针 p2 。
3. 根据 p2 指针，我们定位到磁盘块3，并将其中的信息导入内存。【磁盘 IO 操作 2次】     
4. 此时内存中有两个文件名26， 30 和三个存储其他磁盘页面地址的数据。根据算法我们发现：26<29<30，因此我们找到指针 p2 。
5. 根据 p2 指针，我们定位到磁盘块8，并将其中的信息导入内存。【磁盘 IO 操作 3次】     
6. 此时内存中有两个文件名28， 29 。根据算法我们查找到文件名29，并定位了该文件内存的磁盘地址。

分析上面的过程，发现需要3 次磁盘IO操作和3 次内存查找操作。关于内存中的文件名查找，由于是一个有序表结构，可以利用折半查找提高效率。至于 IO 操作是影响整个B树查找效率的决定因素。

当然，如果我们使用平衡二叉树的磁盘存储结构来进行查找，磁盘4次，最多5次，而且文件越多，B树比平衡二叉树所用的磁盘IO操作次数将越少，效率也越高。（这里需要重点解释一下，为什么使用平衡二叉树会有更多的IO操作，个人理解是假设内存一次加载的盘块大小固定，上图中我们已经假设了一个盘块刚好只能存储一个三叉树结点大小，如果换成是二叉树，则相比每次读取的关键字少，所以需要更多次的IO）

B+-Tree

B+树是B树的一种变形，这里要注意B+树的两个重要特点：

1.所有叶子节点包含了全部关键字的信息，以及指向这些关键字记录的指针，且叶子节点本身依关键字的大小自小而大的顺序链接

2.所有非终端节点可以看成是索引，节点中仅含有其子树根节点最大（或最小）的关键字，不包含查找的有效信息（B树则包含）

为什么B+树比B 树更适合实际应用中操作系统的文件索引和数据库索引？

1) B⁺-tree的磁盘读写代价更低

B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

    举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B树(一个结点最多8个关键字)的内部结点需要2个盘快。而B⁺ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B⁺ 树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。

2) B⁺-tree的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3.B+树索引和哈希索引区别

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据从示意图中也能看到。

如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；

同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

哈希索引也不支持多列联合索引的最左匹配规则；

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

4.InnoDB和MyISAM存储引擎

InnoDB的主索引：主索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，所以必须有主键，如果没有显示定义，自动为生成一个隐含字段作为主键

InnoDB的辅助索引：辅助索引也会包含主键列，比如名字建立索引，内部节点 会包含名字，叶子节点会包含该名字对应的主键的值（下图以名字做一个辅助索引）

MyISAM主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，辅助索引可以重复,其叶子节点存储都是数据记录的地址

主索引：

辅助索引：

InnoDB索引和 MyISAM索引 的区别：

一是主索引的区别，InnoDB的数据文件本身就是索引文件(聚集索引），而MyISAM的索引和数据是分开的（非聚集索引）。

二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

5.索引的优点和缺点

优点：

大大加快数据的检索速度（主要原因）

缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

索引需要占物理空间