数据库索引

在mysql中主键必须是聚集索引，而sql server中创建表的主键时必须依赖于索引，默认是聚集索引但是可以选择为非聚集索引。

数据库中的数据的物理存储顺序与数据表中聚集索引的顺序相同，假设在mysql中id为主键也就是聚集索引，也就是说当我们查询id顺序在表中比较靠后的一条数据时，它在磁盘中存储的物理地址也比较靠后，同时又因为物理地址顺序与聚集索引的顺序相同，因此只能在一个表中建一个聚集索引。

表中可以包含多个非聚集索引：普通索引、唯一索引等。

在mysql中索引是使用B+树来实现的，对于MyISAM引擎来说，聚集索引的叶子节点存储的是索引所指的数据在磁盘中存储的位置，需要进行再次定位，而InnoDB引擎的聚集索引的叶子节点就是聚集索引所代表的数据；而对于非聚集索引来说它的叶子节点都只存储所需查找数据的主键(即聚集索引)，然后再根据聚集索引来查询具体的数据。

当我们使用查询语句select id， name from table where code = ‘A’； 假如code上有普通索引，非聚集索引叶子节点中存储的是聚集索引的信息即code对应的id信息，因此我们还需要根据id再进入聚集索引的B+数去叶子节点查询具体对应的那条数据，找到对应的name，这种情况就叫做回表(个人理解相当与又查了一次)，如果要解决回表的问题，我们可以在code和name上创建一个联合索引，这样在code、name索引的B+树的叶子节点就会同时出现code、name、id的值，就不需要再次进入聚集索引B+树进行二次查找了。

不管是什么索引，每创建一个索引都会创建一个B+树，保存数据时也需要保存这些树的结构，建立索引的数量越多，表存储所占空间就越大，但它可以大大加快查询的速度，但是会使插入和删除等操作变慢，因为数据插入后需要维护B+树的平衡状态，需要对其进行调整，带来额外的资源消耗。

为什么索引可以加快数据的查询效率：当在磁盘中检索数据时，从磁盘中读取数据本来就比较慢，所以我们需要做的就是尽量减少IO即访问磁盘的次数。如果不使用索引，我们需要顺序的查找数据复杂度为O(N)，如果要提高查询速度可以考虑下以树状结构按一定规律存储一些特定的值，来辅助检索，减少IO次数。

假设树中节点为我们设定的辅助检索的数据项，即索引，可以使用如下的树结构：

(1)二叉查找树（又称二叉搜索树、二叉排序树）：

它的特点为：(1)如果左子树不为空则左子树的点均小于根节点(2)如果右子树不为空，则右子树的点均大于根节点(3)根节点的左右子树也都满足上述规律(4)没有两个节点的值相同

假设二叉查找树如图所示，那么按照该索引从小到大的顺序排序为2、3、5、6、7、8，如果我们不使用索引(索引就是一个树结构)查找8需要顺序查询共6次，但如果使用二叉查找树6-7-8共3次，明显减少了查询的次数。但是由于二叉查找树在特殊的情况下会成为一个一条直线的线性链，所以我们不使用它作为索引的树结构。

(2)AVL树(平衡二叉查找树)

除了满足二叉查找树的要求外，左右子树的高度差不超过1，如果插入或删除节点需要进行旋转，来保持平衡，但耗时太多。维护这种高度的平衡所获得的收益比代价还要多，因此在实际中很少使用它。更多地方使用的是追求局部平衡的红黑树，但如果查询较多而删除和插入较少的情况下，AVL的效率还是要高于红黑树的。

(3)红黑树

它是一种弱平衡的二叉查找树，因此在插入删除时所需的旋转操作比较少，它保证一条路径的长度不会比其他路径长出两倍。

1、每个节点非红即黑； 
2、根节点是黑的； 
3、每个叶节点(叶节点即树尾端NULL指针或NULL节点)都是黑的； 
4、如果一个节点是红的,那么它的两儿子都是黑的； 
5、对于任意节点而言，其到叶子点树NULL指针的每条路径都包含相同数目的黑节点； 
6、每条路径都包含相同的黑节点；

说了这么多，为什么数据库索引不用AVL、红黑树而用B/B+树呢，数据检索时最耗时的是IO，即磁盘的读取次数，B/B+树是一个多叉树，与上述的三种树相比，在有相同节点的情况下，B/B+树的高度会远远小于它们，因此IO次数会少很多。B/B+树是为了磁盘或其它存储设备而设计的一种平衡多路查找树

(4)B树（即B-树）

是一种多路搜索树（并不是二叉的）：

       1.定义任意非叶子结点最多只有M个儿子；且M>2；

       2.根结点的儿子数为[2, M]；

       3.除根结点以外的非叶子结点的儿子数为[M/2, M]；

       4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（根节点2到m-1个关键字）

       5.非叶子结点的关键字个数=指向儿子的指针个数-1；

       6.非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

       7.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的

子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；

       8.所有叶子结点位于同一层；

如M=3

小黑块代表的是该key所代表的内容的实际存储位置

(5)B+树

B+树是应文件系统所需而产生的一种B树的变形树(文件的目录一级一级索引,只有最底层的叶子节点(文件)保存数据.),非叶子节点只保存索引,不保存实际的数据,数据都保存在叶子节点中.这不就是文件系统文件的查找吗?我们就举个文件查找的例子:有3个文件夹,a,b,c, a包含b,b包含c,一个文件yang.c, a,b,c就是索引(存储在非叶子节点), a,b,c只是要找到的yang.c的key,而实际的数据yang.c存储在叶子节点上. 

B+树的性质(下面提到的都是和B树不相同的性质)

1、非叶子节点的子树指针与关键字个数相同； 
2、非叶子节点的子树指针p[i],指向关键字值属于[k[i],k[i+1]]的子树.(B树是开区间,也就是说B树不允许关键字重复,B+树允许重复)； 
3、为所有叶子节点增加一个链指针； 
4、所有关键字都在叶子节点出现(稠密索引). (且链表中的关键字恰好是有序的)； 
5、非叶子节点相当于是叶子节点的索引(稀疏索引),叶子节点相当于是存储(关键字)数据的数据层； 
6、父节点中关键字会在孩子节点中出现，且是孩子节点中最大或最小的关键字

B+树是对B树的一种变形树，它与B树的差异在于：

• 有k个子结点的结点必然有k个关键字，关键字在叶子节点中出现
• 非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中。
• 树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录

为什么说B+树比B树更适合做数据库索引

1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

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

3、由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

索引的一些建立原则

http://c.biancheng.net/view/7366.html

索引的实现除了上面说的B树还有hash结构，不过从5.6之后，我们常用的MYSAIM和INNODB引擎并不支持hash结构的索引，在NDB和MEMORY引擎中支持hash结构的索引。hash索引会对索引字段计算一个hash值，然后将其映射到hash表中，hash索引节点中存储了索引的hash值和索引指向数据的行指针；并且由于不同的索引的hash值有可能相同，因此会出现hash冲突的现象。

hash索引主要用于等值查询，查询条件需要精确匹配索引所有列的查找场景，因为它的hash值是使用索引的完整组合计算出的，根据hash值直接从hash表中获取对应下标找到hash值相同的节点的数据行指针，相较于B树的需要多次IO查找，其本身对应单行查找来说，时间复杂度更低，速度更快；当然这个前提是所有节点的hash值都是唯一的，不存在hash冲突，则sql查询有可能会返回多条数据，如果不想返回多条数据，可以在查询的时候也带入列的具体常量值(需要保证列值是唯一的)。

hash索引与b树索引的区别

1.hash索引节点中并不存储具体数据，而是索引的hash值和对应数据的行指针；b树中，对于非聚簇索引来说，叶子节点存储聚簇索引的值；聚簇索引的叶子节点中存储了具体数据

2.由于hash索引是按照hash值排序并不是按照索引数据来排序，因此它不能用于sql排序语句

3.hash值是按照索引的所有字段来计算的，因此并不满足最左匹配原则

4.hash索引只适用于等值查询的场景，如=、in、<=>，并不支持范围查找，如> 100 ， < 20

5.当hash索引等值查询时，如果索引的hash值不存在hash冲突时，查询速度比B树要快；但如果冲突较多，就需要在查询条件中除了索引的hash值外，再加上具体的字段值，此时就需要遍历对应hash表下标链表中的每一个节点对应的行数据，对查询效率有较大的影响，并不一定比B树要快

同时，我们在使用关系型数据库时经常会进行数据的排序、利用部分索引进行最左匹配查询、范围查询，且大量的索引并不是唯一键，数据库引擎也是支持事务的innodb，因此我们使用的索引为B树结构