主键重复和唯一索引冲突_深入了解数据库索引

对于开发人员来说，与关系型数据库打交道至少使用或知道数据库索引（index)。它其实是以空间换时间的方式，对已经存在的数据表建立索引，从而使得每次通过索引的检索时间更短。如果没有索引，一个带有where子句的查询会扫描所有元祖并返回与查询匹配的的值，显然这是效率非常低的方法，特别是数据表的上万甚至上百万条记录时。

File Organization

File Organization讲的是数据库文件记录（record)在磁盘中的物理存储方式，而这也决定了records的获取（access）方式。主要有以下四种文件组织方式：

1.Heap File

记录（record）在文件中存储的的顺序是按照它们插入的顺序，新的数据总是插入到尾部。故这种方式对于插入操作比较高效，每次buffer有新的数据插入，就直接将该块数据重写到disk而不影响其他数据。每次更新最后一个文件块的地址会被保存到文件的header。但是搜索的平均开销会变得比较大。Heap File这种结构通常与secondary index结合使用。

2.Sorted File

我们可以将文件里的每条记录/元组按照它们其中一些属性的值进行排序。这些属性一般是主键（key) 因为每一条记录中的key值不会重复。这种在搜索上会比较高效率，比如采用二分搜索法。当然，要在非排序属性上查找并不能加快，这时可以创建按其他属性排序的副本。

这种结构的插入和删除开销会比较大，因为要保持顺序不被破坏，每次要先找到对应顺序的位置，然后插入或删除所在位置后面的每一个记录都要往后移或前移一位。

3.Hash File

Hash File就是采用哈希函数将key值映射到哈希值，主要有两个问题需要考虑：（1）选择怎样的哈希函数：哈希函数的选择需要考虑速度和映射后的冲突率（collision rate)之间的取舍。因为速度快说明这个哈希函数的计算量小，也就更可能得到相同的值，理想的哈希函数是每两个不同的key值，映射后的哈希值都会不同。（2）哈希模式：在冲突很难避免的情况，自然也要考虑遇到哈希值冲突时该如何解决。这也是哈希表和另外插入键值之间所需开销的之间的权衡。根据哈希表是否固定可分为静态和动态的方法，由于篇幅原因本文不做详细展开描述。

4.Tree Structure

树结构主要指的就是B树以及它的变种B+,B-树。该结构的好处在于能够使用二分搜索或n路搜索加快搜索速度。

那么，究竟如何创建索引以及创建什么样的索引才能更好地加快我们平时的数据库检索速度呢？这就需要了解不同索引的结构和特点。

Search Key

Search Key 是用来查找数据库记录的属性集合。索引是由一个个index entry组成的，每个entry由两部分组成：search key和pointer <K(i),P(i)>. 。pointer就是指向对应search key数据块地址的指针。

根据是否按照search key的顺序存储，可以将索引分为有序索引（ordered indices)和哈希索引（hash indices)。

索引种类

首先根据 可分为单层索引和多层索引。单层索引有主键索引primary index，聚集索引clustering index和次级索引secondary index。primary index和clustering index都是按照文件记录的物理顺序排列的。区别在于primary index的search key是主键，也就是说每个记录的search key是独一无二的。而clustering index的search key是非主键，所以在不同的记录这个域内可能有多个相同的值。由于物理顺序只有一种，因此primary index或clustering index有且只有一种。另一种secondary index由于不是按照物理顺序的属性来排列，所以同一个data file可以有很多种secondary index。

还有另外一种分类方法，根据索引是否包含每个search key的数据记录分为密集索引dense index和稀疏索引sparse index。dense index包含search key 的每个记录，而dense index只包含search key的部分记录。Primary index是一种稀疏索引，因为它每个index entry的指针指向的是每个数据块的第一条记录，叫作anchor record。Primary key的一个主要问题是，插入和删除新的记录开销会很大。比如每次插入一个记录，不仅要移动后面的记录来腾出位置，而且要改变index entries, 因为anchor record也随着record的移动改变了。可以增加溢出（overflow)文件或链表来存储新的记录，来改善这个问题。

聚簇索引clustering index是在有序非主键（ordered,non-key)上创建的索引，因为是非主键，不同的记录在该属性上就可能有不同的值。但是每个值都只创建一个索引，而索引的指针指向的是第一个出现该值的记录所在的数据块（通常是数据块的第一个记录，但若已经有指针指向，就顺移到第二个，以此类推），如下图Figure 18.2所示。所以聚簇索引也是一个稀疏索引。

次级索引secondary index是在主键索引primary index已经存在的情况下对数据记录获取的补充。它可以在每个值都是独一无二的候选码candidate key上创建，也可以在非键属性，即有重复值的属性上创建。故一个数据文件可以创建多个次级索引。对于在候选码上创建的次级索引，每个index entries上都分别对应每一条数据记录。因此这种索引是密集索引。由于是在候选码创建的，因此是无序的。但次级索引也可以建立在非候选码的属性上，这时就存在多个记录在这个属性上有相同值的情况。这时有三种选择：（1）带有重复的search key的index指向对应的记录；（2）对于pointer保存一个list <P(i,1),...,P(i,k)> 指向带有同一个K(i)的记录 （3)指针先指向一个block, block中包含带有相同Key值的不同记录的地址

次级索引的主要目的也是降低搜索时间，同时要额外的存储空间来存储索引。以下表总结了上述几种索引的特性。

多层索引Multilevel indexes

当一个索引文件的大小超过一个数据块data block的大小时，可以通过加多一层指向索引的索引，即指针部分是索引所在数据块的地址。这就形成了多层索引，如果有需要还可以一直加更多的层。多层索引通过减少数据块读取次数来加快检索速度。

不管是主键索引，聚簇索引还是次级索引都可以构造多层索引，只要第一层索引的每一个key值是唯一的，且索引的长度是固定的。第一层指的是存放记录的数据块的那一层。

ISAM (Indexed Sequential Access Method)

ISAM是一种典型的多层索引数据结构。它的特点是除了第一层，即数据块所在层。其他层都是由key和指针相间组成。如<p1,k1,p2,k2>,p1指向小于k1的索引或者数据地址，p2指向大于k1小于k2的索引或者数据地址。另一个主要特点是它的结点数目和第一层的数据块是固定的。如果要插入新的数据只能通过新建overflow page，但这样会使搜索的时间随数据增加线性增长。降低了原本在树结构上可以使用二分搜索的优势。为了解决这个问题，就有了动态的多层索引，代表是B-树和B+树。

M路搜索树（M-way Search Tree)

B-树和B+树是基于M路搜索树，M表示每个结点最多有M个子节点，也即（M-1）个key,同时每个key都指向一个具体的记录。B-在M路搜索树的基础上增加了一些约束，以达到树结构的高效利用，提高搜索效率。

1.除叶结点和根节点外，每个结点至少要有M/2个孩子结点。(若M/2为小数，则向上取整）这条规则的作用是保证树的高度不会增长太快

2.根节点至少要有2个子节点

3.所有的的叶子结点必须在同一层。（所以B-树也叫自平衡树）

4.树的构造是自底向上的（bottom-up)。意思是在构造树的过程中，当插入的值使得某个结点大于M个值时，就要将这个结点分裂（split）。然后根据增加的结点是否超出父节点的子树上限来构造父节点来容纳新的值。这个过程中，自然而然地确保了叶子结点在同一层。

B+树和B-树相比在于它只有叶子结点的键值才有对应的指针指向数据。因此它的每个非叶结点里的值需要出现在叶子结点。另外，每个叶子节点之间像链表一样有指针从小到大指向下一个结点。因此B+树也是个Dense index。