数据库常见索引解析（B树，B-树，B+树，B*树，位图索引，Hash索引）

1.什么是索引

数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

 

2.索引的优缺点

优点：

 1.大大加快数据的检索速度

 2.创建唯一性索引，保证数据库表中每一行数据的唯一性

 3.加速表和表之间的连接

 4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

缺点

1.索引需要占用数据表以外的物理存储空间

2.创建索引和维护索引要花费一定的时间

3.当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度

 

3.索引类型

（1）唯一索引:UNIQUE 

例如在学生表中给学号字段创建唯一索引：create unique index stusno on student（sno）；

表明此索引的每一个索引值只对应唯一的数据记录，对于单列唯一性索引，这保证单列不包含重复的值。对于多了唯一性索引，保证多列值得组合不重复

（2）主键索引  primary key 即唯一+非空

数据库关系图中在给表定义主键将自动创建主键索引，该索引要求主键中的每个值都唯一且非空。

（3）聚集索引（又叫聚簇索引）：cluster

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值得逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问

 

4.索引的实现原理（这里我们能知道为什么索引会加快查询速度）

 

 

即二叉搜索树   

1.所有非叶子结点至多拥有两个儿子(left和right)

2.所有节点存储一个关键字

3.非叶子节点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树

 

如下图所示

     

 

二叉搜索树搜索路径：

从根结点开始，如果查询的关键字与结点的关键字相等，则命中。否则，如果查询的关键字比结点关键字小，就进入左儿子，反之大则进入右儿子；如果左儿子或右儿子的指针为空，则报告找不到对应的关键字。

拿关键字28来举例，首先与根结点35比较，小则进入左儿子17，又与17比较，大则进入右儿子28，相等则命中。

       如果二叉搜索树的所有非叶子结点的左右子树的结点数目均保持差不多（平衡），那么二叉搜索树的搜索性能逼近二分查找；但它比连续内存空间的二分查找的优点是，改变B树结构（插入与删除结点）不需要移动大段的内存数据，甚至通常是常数开销；

如：

但二叉搜索树在经过多次插入和删除后，有可能导致不同的结构：

右边的也是一个二叉搜索树，但其搜索性能已经是线性的了；同样的关键字集合有可能导致不同的树结构索引；所以，使用二叉搜索树还要考虑尽可能让二叉搜索树保持左图的结构，和避免右图的结构，也就是所谓的“平衡”问题。

 

B-树（B树）

是一种多路搜索树（并不是二叉的）,任意一棵m阶B-树：

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

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

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

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

       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）

 

 

B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

 

B+树

  B+树是B-树的变体，也是一种多路搜索树：

       1.其定义基本与B-树同，除了：

       2.非叶子结点的子树指针与关键字个数相同；

       3.非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；

       5.为所有叶子结点增加一个链指针；

       6.所有关键字都在叶子结点出现；

       如：（M=3）

 

 

B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；

       B+的特性：

       1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；

       2.不可能在非叶子结点命中；

       3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；

       4.更适合文件索引系统；

 

位图索引

1.案例

有张表名为table的表，由三列组成，分别是姓名、性别和婚姻状况，其中性别只有男和女两项，婚姻状况由已婚、未婚、离婚这三项，该表共有100w个记录。现在有这样的查询：     select * from table where Gender=‘男’ and Marital=“未婚”;

姓名(Name)	性别(Gender)	婚姻状况(Marital)
张三	男	已婚
李四	女	已婚
王五	男	未婚
赵六	女	离婚
孙七	女	未婚
...	...	...

1）不使用索引

　　不使用索引时，数据库只能一行行扫描所有记录，然后判断该记录是否满足查询条件。

2）B树索引

　　对于性别，可取值的范围只有'男','女'，并且男和女可能各站该表的50%的数据，这时添加B树索引还是需要取出一半的数据， 因此完全没有必要。相反，如果某个字段的取值范围很广，几乎没有重复，比如身份证号，此时使用B树索引较为合适。事实上，当取出的行数据占用表中大部分的数据时，即使添加了B树索引，数据库如oracle、MySQL也不会使用B树索引，很有可能还是一行行全部扫描。

2.位图索引出马

如果用户查询的列的基数非常的小， 即只有的几个固定值，如性别、婚姻状况、行政区等等。要为这些基数值比较小的列建索引，就需要建立位图索引。

对于性别这个列，位图索引形成两个向量，男向量为10100...，向量的每一位表示该行是否是男，如果是则位1，否为0，同理，女向量位01011。

RowId	1	2	3	4	5	...
男	1	0	1	0	0
女	0	1	0	1	1	...

　　对于婚姻状况这一列，位图索引生成三个向量，已婚为11000...，未婚为00100...，离婚为00010...。

RowId	1	2	3	4	5	...
已婚	1	1	0	0	0
未婚	0	0	1	0	1
离婚	0	0	0	1	0

 　　当我们使用查询语句“select * from table where Gender=‘男’ andMarital=“未婚”;”的时候 首先取出男向量10100...，然后取出未婚向量00100...，将两个向量做and操作，这时生成新向量00100...，可以发现第三位为1，表示该表的第三行数据就是我们需要查询的结果。 

RowId	1	2	3	4	5
男	1	0	1	0	0
and
未婚	0	0	1	0	1
结果	0	0	1	0	0

3.位图索引适应场景

上面讲了，位图索引适合只有几个固定值的列，如性别、婚姻状况、行政区等等，而身份证号这种类型不适合用位图索引。

　　此外，位图索引适合静态数据，而不适合索引频繁更新的列。举个例子，有这样一个字段busy，记录各个机器的繁忙与否，当机器忙碌时，busy为1，当机器不忙碌时，busy为0。

　　这个时候有人会说使用位图索引，因为busy只有两个值。好，我们使用位图索引索引busy字段！假设用户A使用update更新某个机器的busy值，比如update table set table.busy=1 where rowid=100;，但还没有commit，而用户B也使用update更新另一个机器的busy值，update table set table.busy=1 where rowid=12; 这个时候用户B怎么也更新不了，需要等待用户A commit。

　　原因：用户A更新了某个机器的busy值为1，会导致所有busy为1的机器的位图向量发生改变，因此数据库会将busy＝1的所有行锁定，只有commit之后才解锁。

 

Hash索引

索引列会被存储在匹配到的hash bucket里面的表里，这个表里会有实际的数据行指针，再根据实际的数据行指针查找对应的数据行。

概括来说，要查找一行数据或者处理一个where子句，SQL Server引擎需要做下面几件事

1、根据where条件里面的参数生成合适的哈希函数

2、索引列进行匹配，匹配到对应hash bucket，找到对应hash bucket意味着也找到了对应的数据行指针（row pointer）

3、读取数据

哈希索引比起B树索引简单，因为它不需要遍历B树，所以访问速度会更快

Hash索引的缺点：

1、因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询

2、由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作

3、不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。

4、当哈希值大量重复且数据量非常大时（会产生冲突），其检索效率并没有Btree索引高的。