数据库索引

一.数据库索引

1、索引的原理

数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似，即它们都包含一个数据段和一个指针，指针指向下一个节点（数据块）的内存地址，而且它们都不需要连续存储（即逻辑上相邻的数据块在物理上可以相隔很远）。

对于经过排序的字段，可以使用二分查找，因此只要访问log2 N个数据块。同样，对于已经排过序的非键字段，只要找到更大的值，也就不用再搜索表中的其他数据块了。

索引是一个单独存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针，使用索引可以提高数据库特定数据的查询速度.索引时在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。

2、索引的存储类型

分为两种：B+数和hash索引。

具体和表的存储引擎有关。 MyISAM和InnoDB存储引擎都只支持B+树。
MEMORY/HEAD 存储索引可以支持 HASH 和  B+树索引 。

3、索引的优点与缺点

索引的主要优势是，加快查询速度。

索引的缺点 ：并不是创建越多越好。

1. 创建和维护索引需要耗费时间，并随着数据量的增加而增加；
2. 索引会占用一定的存储空间；
3. 更新索引需要动态维护，降低了速度。

二.索引的分类

1、唯一索引 

唯一索引是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

2、主键索引

数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。

在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

3、聚集索引

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。

如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

三、聚集索引与非聚集索引

1、聚集索引

　　一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。 
　　聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。 
　　　　 
　  聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行 的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此 类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节 省成本。 
　　　　 

　　当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。

2、非聚集索引

　　一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。如下图：

实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：
    　　其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
    　　如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

3、InnoDB和MyISAM存储引擎

虽然都使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 截然不同。

 1、 InnoDB 的数据文件本身就是索引文件。 

MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

而在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这种索引叫做聚集索引。

因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键（MyISAM 可以没有），如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据。 

2、 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说， InnoDB 的所有辅助索引都引用主键作为 data域。

四、B+索引与hash索引对比

hash索引虽然速度效率快，但存在一些劣势：

1、hash不是按照顺序存储的，无法排序；

2、只适合等值比较查询、不能进行区间范围内查询；

3、hash表会发生冲突，冲突多的的情况下，查询速度较慢；

4、冲突多的时候，hash表实际利用率一般不高， 数据量大的时候需要占用更大的空间；

B+树的优势：

其实，数据库索引的实现可以采用红黑树，B-Tree树数据结构。

但是为什么实际上采用的B+Tree呢？
这要从计算机存储原理和操作系统相关知识说起。因为数据表的索引比较大，不能常驻内存，所以以文件形式存储在磁盘中。所以当查询数据的时候就需要I/O操作。高效率查询的目标是较少I/O次数。一次I/O一般读取一页（一般为4k）大小的数据(局部性原理)。如此，在B-树中，每当申请一个新结点时，就以页的大小来申请。也就是说一次I/o可以读取一个一个结点（包含很多key）的数据；而在红黑树结构结构中，逻辑相邻的结点物理上不一定相邻，就是说，读取同等的数据需要多次I/O。所以选择B-树效率更好。
那为何最终选了B+树呢？
因为B+树内节点去掉了data域，因此可以拥有更大的出度，就是说一个结点可以存储更多的内结点，那么I/O效率更高。

五、索引不能加快查询速度的情况

1. WHERE 字句的查询条件里有不等于号（WHERE column!=...）， MYSQL 将无法使用索引；
2. 如果 WHERE 字句的查询条件里使用了函数（如： WHERE DAY(column)=...）；
3. 在 JOIN 操作中（需要从多个数据表提取数据时）， MYSQL 只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用。
4. 如果 WHERE 子句的查询条件里使用了比较操作符 LIKE 和 REGEXP， MYSQL 只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是 LIKE'abc%',MYSQL 将使用索引；如果条件是 LIKE '%abc'， MYSQL 将不使用索引。
5. 在 ORDER BY 操作中， MYSQL 只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY 操作方面也没什么作用。
6. 如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。
7. 如果条件中有 or(并且其中有 or 的条件是不带索引的)，即使其中有条件带索引也不会使用(这也是为什么尽量少用 or 的原因)。注意：要想使用 or，又想让索引生效，只能将 or 条件中的每个列都加上索引。
8. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引。