数据库索引总结

索引的概念：

索引是对现有数据进行排序，在排序的结果中搜索，效率会很高。用主键 作为查询条件，主键会自动创建索引，搜索速度快；在表中的查询远远高于增删改时， 建立索引有意义。

为什么要创建索引呢（优点）？

这是因为，创建索引可以大大提高系统的性能。
第一， 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二， 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三， 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四， 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五， 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

建立方向索引的不利因素（缺点）

第一， 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二， 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三， 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

那么在任何时候都应该加索引么？

这里有几个反例：A.如果每次都需要取到所有表记录，无论如何都必须进行全表扫描了，那么是否加索引也没有意义了。B.对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义。C.对于记录比较少的表，增加索引不会带来速度的优化反而浪费了存储空间，因为索引是需要存储空间的，而且有个致命缺点是对于update/insert/delete的每次执行，字段的索引都必须重新计算更新。所以并不是任何情况下都改建立索引的。

一般来说，应该在这些列上创建索引

第一， 在经常需要搜索的列上，可以加快搜索的速度；
第二， 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
第三， 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
第四， 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
第五， 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
第六， 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

创建索引的方法

创建索引有多种方法，这些方法包括直接创建索引的方法和间接创建索引的方法。
第一， 直接创建索引，例如使用CREATE INDEX语句或者使用创建索引向导。
第二， 间接创建索引，例如在表中定义主键约束或者唯一性键约束时，同时也创建了索引。
虽然，这两种方法都可以创建索引，但是，它们创建索引的具体内容是有区别的。
使 用CREATE INDEX语句或者使用创建索引向导来创建索引，这是最基本的索引创建方式，并且这种方法最具有柔性，可以定制创建出符合自己需要的索引。在使用这种方式 创建索引时，可以使用许多选项，例如指定数据页的充满度、进行排序、整理统计信息等，这样可以优化索引。使用这种方法，可以指定索引的类型、唯一性和复合 性，也就是说，既可以创建聚簇索引，也可以创建非聚簇索引，既可以在一个列上创建索引，也可以在两个或者两个以上的列上创建索引。
通过定义主键约束或者唯一性键约束，也可以间接创建索引。主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建 了一个唯一性的聚簇索引。虽然，在逻辑上，主键约束是一种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯一性的聚簇索引。换句话说，在物理 实现上，不存在主键约束，而只存在唯一性的聚簇索引。同样，在创建唯一性键约束时，也同时创建了索引，这种索引则是唯一性的非聚簇索引。因此，当使用约束 创建索引时，索引的类型和特征基本上都已经确定了，由用户定制的余地比较小。
当在表上定义主键或者唯一性键约束时，如果表中已经有了使用 CREATE INDEX语句创建的标准索引时，那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说，主键约束或者唯一性键约束创建的索引的优先 级高于使用CREATE INDEX语句创建的索引。

聚簇索引和非聚簇索引

聚簇索引：
索 引的结构类似于树状结构，树的顶部称为叶级，树的其它部分称为非叶级，树的根部在非叶级中。同样，在聚簇索引中，聚簇索引的叶级和非叶级构成了一个树状结 构，索引的最低级是叶级。在聚簇索引中，表中的数据所在的数据页是叶级，在叶级之上的索引页是非叶级，索引数据所在的索引页是非叶级。在聚簇索引中，数据 值的顺序总是按照升序排列。
应该在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引。当创建聚簇索引时，应该考虑这些因素：每一个表只能 有一个聚簇索引，因为表中数据的物理顺序只能有一个；表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚 簇索引改变了表中行的物理顺序，数据行按照一定的顺序排列，并且自动维护这个顺序；关键值的唯一性要么使用UNIQUE关键字明确维护，要么由一个内部的 唯一标识符明确维护，这些唯一性标识符是系统自己使用的，用户不能访问；聚簇索引的平均大小大约是数据表的百分之五，但是，实际的聚簇索引的大小常常根据 索引列的大小变化而变化；在索引的创建过程中，SQL Server临时使用当前数据库的磁盘空间，当创建聚簇索引时，需要1.2倍的表空间的大小，因此，一定要保证有足够的空间来创建聚簇索引。
当 系统访问表中的数据时，首先确定在相应的列上是否存在有索引和该索引是否对要检索的数据有意义。如果索引存在并且该索引非常有意义，那么系统使用该索引访 问表中的记录。系统从索引开始浏览到数据，索引浏览则从树状索引的根部开始。从根部开始，搜索值与每一个关键值相比较，确定搜索值是否大于或者等于关键 值。这一步重复进行，直到碰上一个比搜索值大的关键值，或者该搜索值大于或者等于索引页上所有的关键值为止。
非聚簇索引：
非聚簇索引的结构也是树状结构，与聚簇索引的结构非常类似，但是也有明显的不同。
在非聚簇索引中，叶级仅包含关键值，而没有包含数据行。非聚簇索引表示行的逻辑顺序。 非聚簇索引有两种体系结构：一种体系结构是在没有聚簇索引的表上创建非聚簇索引，另一种体系结构是在有聚簇索引的表上创建非聚簇索引。
如 果一个数据表中没有聚簇索引，那么这个数据表也称为数据堆。当非聚簇索引在数据堆的顶部创建时，系统使用索引页中的行标识符指向数据页中的记录。行标识符 存储了数据所在位置的信息。数据堆是通过使用索引分配图（IAM）页来维护的。IAM页包含了数据堆所在簇的存储信息。在系统表sysindexes中， 有一个指针指向了与数据堆相关的第一个IAM页。系统使用IAM页在数据堆中浏览和寻找可以插入新的记录行的空间。这些数据页和在这些数据页中的记录没有 任何的顺序并且也没有链接在一起。在这些数据页之间的唯一的连接是IAM中记录的顺序。当在数据堆上创建了非聚簇索引时，叶级中包含了指向数据页的行标识 符。行标识符指定记录行的逻辑顺序，由文件ID、页号和行ID组成。这些行的标识符维持唯一性。非聚簇索引的叶级页的顺序不同于表中数据的物理顺序。这些 关键值在叶级中以升序维持。
当非聚簇索引创建在有聚簇索引的表上的时候，系统使用索引页中的指向聚簇索引的聚簇键。聚簇键存储了数据的位置信 息。 如果某一个表有聚簇索引，那么非聚簇索引的叶级包含了映射到聚簇键的聚簇键值，而不是映射到物理的行标识符。当系统访问有非聚簇索引的表中数据时，并 且这种非聚簇索引创建在聚簇索引上，那么它首先从非聚簇索引来找到指向聚簇索引的指针，然后通过使用聚簇索引来找到数据。
当需要以多种方式检索数据时，非聚簇索引是非常有用的。当创建非聚簇索引时，要考虑这些情况：在缺省情况下，所创建的索引是非聚簇索引；在每一个表上面，可以创建不多于249个非聚簇索引，而聚簇索引最多只能有一个。
索引失效：
当数字型字段遇到非等值操作符时，无法用到索引；如果对索引字段进行了某种左值操作，无法用到索引；如果对索引字段进行了函数操作，无法用到索引；Null值不适合索引，要加索引的列上一般添加一个not null约束，并给一个默认值。区分度低的列不适合建索引。用like进行模糊查询时，如果第一个就是模糊的匹配符，例如where name like ‘%java’，查询不会走索引，其他情况都能用索引。

索引原理

数据结构：B+树
关于B-Tree的一些知识点：
1：叶节点具有相同的深度，左子树跟右子树的深度一致
2：叶节点的指针为空
3：节点中的数据key从左到右递增排列
在这里需要说明下的是，BTree的结构里每个节点包含了索引值和表记录（数据值）的信息，我们可以按照Map集合这样理解：key=索引，value=表记录，
优点：
BTree的结构可以弥补红黑树的缺点，解决数据量过大时整棵树的深度过长的问题。
相同数量的数据只需要更少的层，相同深度的树可以存储更多的数据，查找的效率自然会更高。
缺点：
在查询单条数据是非常快的。
但如果范围查的话，BTree结构每次都要从根节点查询一遍，效率会有所降低，
因此在实际应用中采用的是另一种BTree的变种B+Tree（B+树）。
因为btree树的叶子节点不是连续的。就像链表那样。
另外，由于插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作（反正就是和平衡二叉树和红黑树一样各种旋转）以保持B-Tree性质。
B+Tree存储结构，
只有叶子节点存储数据
新的B+树结构没有在所有的节点里存储记录数据，而是只在最下层的叶子节点存储数据，
上层的所有非叶子节点只存放索引信息，这样的结构可以让单个节点存放下更多索引值，增大度Degree的值，提高命中目标记录的几率。
这种结构会在上层非叶子节点存储一部分冗余数据，但是这样的缺点都是可以容忍的，因为冗余的都是索引数据，不会对内存造成大的负担。
优化的作用：
（1）B+树每个节点可以包含更多的节点，这样做有两个原因,一个是降低树的高度。另外一个是将数据范围变为多个区间，区间越多，数据检索越快
（2）非叶子节点存储key，叶子节点存储key和数据
（3）叶子节点两两指针相互链接（符合磁盘的预读特性），顺序查询性能更高