索引的原理及相关知识
索引的原因及相关知识
来源于网络整理：
http://www.iteye.com/topic/1113622
http://chriszeng87.iteye.com/blog/1180762????????????????? http://www.cnblogs.com/king1302217/archive/2010/05/11/1732250.html
一、索引的概念
??????? 索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。
建立索引的目的是加快对表中记录的查找或排序。
为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。
?
二、索引的特点
??? 1.索引可以加快数据库的检索速度
??? 2.索引降低了数据库插入、修改、删除等维护任务的速度
??? 3.索引创建在表上，不能创建在视图上
??? 4.索引既可以直接创建，也可以间接创建
??? 5.可以在优化隐藏中，使用索引
??? 6.使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引
??? 7.其他
?
三、索引的优点
??? 1.创建唯一性索引，保证数据库表中每一行数据的唯一性
??? 2.大大加快数据的检索速度，这也是创建索引的最主要的原因
??? 3.加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
??? 4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
??? 5.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。
?
四、索引的缺点
??? 1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
??? 2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
??? 3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度
?
索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：
在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。
?
六、索引的存储
索引包含由表中的一列或多列生成的键。这些键存储在一个结构（B 树）中，不同于二叉树。同一个分支下有一个或多个子节点。
B树的简单结构：
?
(从图可见，当我们插入关键字4时，由于原结点已经满了，故进行分裂，基本按一半的原则进行分裂，然后取出中间的关键字2，升级（这里是成为根结点）。其它的依类推，就是这样一个大概的过程。)
一条索引记录中包含的基本信息包括：键值 + 逻辑指针。
?
?
那么索引是如何实现的呢？大多数DB厂商实现索引都是基于一种数据结构——B树。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表。B树的定义是这样的：一棵m(m>=3)阶的B树是满足下列条件的m叉树：
1、每个结点包括如下作用域(j, p0, k1, p1, k2, p2, ... ki, pi) 其中j是关键字个数，p是孩子指针
2、所有叶子结点在同一层上，层数等于树高h
3、每个非根结点包含的关键字个数满足[m/2-1]<=j<=m-1
4、若树非空，则根至少有1个关键字，若根非叶子，则至少有2棵子树，至多有m棵子树
看一个B树的例子，针对26个英文字母的B树可以这样构造：
?
可以看到在这棵B树搜索英文字母复杂度只为o(m)，在数据量比较大的情况下，这样的结构可以大大增加查询速度。然而有另外一种数据结构查询的虚度比B树更快——散列表。Hash表的定义是这样的：设所有可能出现的关键字集合为u，实际发生存储的关键字记为k，而|k|比|u|小很多。散列方法是通过散列函数h将u映射到表T[0,m-1]的下标上，这样u中的关键字为变量，以h为函数运算结果即为相应结点的存储地址。从而达到可以在o(1)的时间内完成查找。
然而散列表有一个缺陷，那就是散列冲突，即两个关键字通过散列函数计算出了相同的结果。设m和n分别表示散列表的长度和填满的结点数，n/m为散列表的填装因子，因子越大，表示散列冲突的机会越大。
因为有这样的缺陷，所以数据库不会使用散列表来做为索引的默认实现，Mysql宣称会根据执行查询格式尝试将基于磁盘的B树索引转变为和合适的散列索引以追求进一步提高搜索速度。
?
?
?
七、索引分类
??? 1.直接创建索引和间接创建索引
??? 直接创建索引： CREATE INDEX mycolumn_index ON mytable (myclumn)
??? 间接创建索引：定义主键约束或者唯一性键约束，可以间接创建索引
??? 2.普通索引和唯一性索引
??? 普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)
??? 唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用
??? CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
??? 3.单个索引和复合索引
??? 单个索引：即非复合索引
??? 复合索引：又叫组合索引，在索引建立语句中同时包含多个字段名，最多16个字段
??? CREATE INDEX name_index ON username(firstname,lastname)
??? 4.聚簇索引和非聚簇索引(聚集索引，群集索引)
?? 聚簇索引：物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列
??? CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH
??? ALLOW_DUP_ROW(允许有重复记录的聚簇索引)
?? 非聚簇索引：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)
?
?
?
聚集索引和非聚集索引
?
动作描述使用聚集索引使用非聚集索引列经常被分组排序应应返回某范围内的数据应不应一个或极少不同值不应不应小数目的不同值应不应大数目的不同值不应应频繁更新的列不应应外键列应应主键列应应频繁修改索引列不应应
?
事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。
（三）结合实际，谈索引使用的误区
理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综
合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。
1、主键就是聚集索引
这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。
通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但笔者认为这样做意义不大。
显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。
从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。
在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。
通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的2年，那么您的首页显示速度
理论上将是原来速度8倍，甚至更快。
在这里之所以提到“理论上”三字，是因为如果您的聚集索引还是盲目地建在ID这个主键上时，您的查询速度是没有这么高的，即使您在“日期”这个字段上建立的索引（非聚合索引）。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现（3个月内的数据为25万条）：
?
?
???????????????????????????????????????????????????????????? 聚集索引
?
?
?
非聚集索引一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
?
?
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。如下图：
???????????????????????????????????????????????????????????????? 非聚集索引
?
?
八.索引的类型
有两种基本的索引结构，也就是索引文件的保存方式，一个是顺序索引，就是根据值的顺序排序的（这个文件里面的值，也就是为其建索引的字段值，是顺序的放在索引文件里面），另外一个是散列索引，就是将值平均分配到若干散列桶中，通过散列函数定位的。
?
顺序索引
顺序索引下面又有很多概念。
如果被索引的字段本身按照一定的顺序排序，那么这种索引叫做聚集索引。否则叫做非聚集索引。如果被索引的字段的每个值都有一个索引与其对应，那么这种索引叫做稠密索引，否则叫做稀疏索引。
????? 顺序索引分为两类，单级索引（不怎么用）和多级索引（通常是B+树，大量使用）。
????? 单级索引就是把所有的索引字段以及对应的文件位置按顺序一个个的排列出来，这种索引查找起来比较慢，因为是顺序存储的，可以使用二分查找法，但是总体来说效率不高，这种索引是最基础的索引，一般不用，ORACLE里面好像不支持这种索引。
???? 多级索引实际上就是在单级索引之上再加索引（稀疏索引），也就是指向索引的索引，二级索引上面还可以再加三级索引，可以不停的加，加到最后最上层只剩下一个节点（根节点），就成了一个树状结构了。
???? 我们经常听到B+树就是这个概念，用这个树的目的和红黑树差不多，也是为了尽量保持树的平衡，当然红黑树是二叉树，但B+树就不是二叉树了，节点下面可以有多个子节点，数据库开发商会设置子节点数的一个最大值，这个值不会太小，所以B+树一般来说比较矮胖，而红黑树就比较瘦高了。
关于B+树的插入，删除，会涉及到一些算法以保持树的平衡，这里就不详述了。ORACLE的默认索引就是这种结构的。
????? 如果经常需要同时对两个字段进行AND查询,那么使用两个单独索引不如建立一个复合索引，因为两个单独索引通常数据库只能使用其中一个，而使用复合索引因为索引本身就对应到两个字段上的，效率会有很大提高。
散列索引
第二种索引叫做散列索引，就是通过散列函数来定位的一种索引，不过很少有单独使用散列索引的，反而是散列文件组织用的比较多。
散列文件组织就是根据一个键通过散列计算把对应的记录都放到同一个槽中，这样的话相同的键值对应的记录就一定是放在同一个文件里了，也就减少了文件读取的次数，提高了效率。
散列索引呢就是根据对应键的散列码来找到最终的索引项的技术，其实和B树就差不多了，也就是一种索引之上的二级辅助索引，我理解散列索引都是二级或更高级的稀疏索引，否则桶就太多了，效率也不会很高。
?
位图索引
位图索引是一种针对多个字段的简单查询设计一种特殊的索引，适用范围比较小，只适用于字段值固定并且值的种类很少的情况，比如性别，只能有男和女，或者级别，状态等等，并且只有在同时对多个这样的字段查询时才能体现出位图的优势。
位图的基本思想就是对每一个条件都用0或者1来表示，如有5条记录，性别分别是男，女，男，男，女，那么如果使用位图索引就会建立两个位图，对应男的10110和对应女的01001,这样做有什么好处呢，就是如果同时对多个这种类型的字段进行and或or查询时，可以使用按位与和按位或来直接得到结果了。
总结：
B+树最常用，性能也不差，用于范围查询和单值查询都可以。特别是范围查询，非得用B+树这种顺序的才可以了。
HASH的如果只是对单值查询的话速度会比B+树快一点，但是ORACLE好像不支持HASH索引，只支持HASH表空间。
位图的使用情况很局限，只有很少的情况才能用，一定要确定真正适合使用这种索引才用（值的类型很少并且需要复合查询），否则建立一大堆位图就一点意义都没有了。
