索引论文.docx
《索引论文.docx》由会员分享，可在线阅读，更多相关《索引论文.docx（20页珍藏版）》请在冰豆网上搜索。
索引论文
n7.1索引简介
索引的定义
一、索引的概念
索引就是加快检索表中数据的方法,是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。
数据库的索引类似于书籍的索引。
在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。
在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不是将读取每条记录的所有信息进行匹配。
通过加索引，一个通常要花费几个小时来完成的查询只要几分钟就可以完成。
因此没有理由bu对需要频繁查询的表增加索引。
二、索引的特点
1.索引可以加快数据库的检索速度,索引是提高数据库性能的重要方式。
通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。
举个例子，查字典，如果不使用音序，需要从字典的400页中逐页来找。
但是，如果提取拼音出来，构成音序表，就只需要从10多页的音序表中直接查找。
这样就可以大大节省时间。
因此，使用索引可以很大程度上提高数据库的查询速度。
2.索引降低了数据库插入、修改、删除等维护任务的速度
3.索引创建在表上，不能创建在视图上
7索引由数据库表中一列或多列组合而成
4.索引既可以直接创建，也可以间接创建
5.可以在优化隐藏中，使用索引
6.使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引.
三、索引分类
1.直接创建索引和间接创建索引
直接创建索引：
CREATEINDEXmycolumn_indexONmytable（myclumn）
间接创建索引：
定义主键约束或者唯一性键约束，可以间接创建索引
2.普通索引和唯一性索引
普通索引：
CREATEINDEXmycolumn_indexONmytable（myclumn）
唯一性索引：
保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用
CREATEUNIQUECOUSTEREDINDEXmyclumn_cindexONmytable（mycolumn）
3.单个索引和复合索引
单个索引：
即非复合索引
复合索引：
又叫组合索引，在索引建立语句中同时包含多个字段名，最多16个字段
CREATEINDEXname_indexONusername（firstname,lastname）
4.聚簇索引和非聚簇索引（聚集索引，群集索引）
聚簇索引：
物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列
CREATECLUSTEREDINDEXmycolumn_cindexONmytable（mycolumn）WITH
ALLOW_DUP_ROW（允许有重复记录的聚簇索引）
非聚簇索引：
CREATEUNCLUSTEREDINDEXmycolumn_cindexONmytable（mycolumn）
四索引的存储
不同的存储引擎定义了每个表的最大索引数和最大索引长度。
所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。
有些存储引擎支持更多的索引数和更大的索引长度。
索引有两种存储类型，包括B型树（BTREE）索引和哈希（HASH）索引。
InnoDB和MyISAM存储引擎支持BTREE索引，MEMORY存储引擎支持HASH索引和BTREE索引，默认为前者。
如何建立索引
基于合理的数据库设计，经过深思熟虑后为表建立索引，是获得高性能数据库系统的基础。
而未经合理分析便添加索引，则会降低系统的总体性能。
索引虽然说提高了数据的访问速度，但同时也增加了插入、更新和删除操作的处理时间。
是否要为表增加索引、索引建立在那些字段上，是创建索引前必须要考虑的问题。
解决此问题的一个比较好的方法，就是分析应用程序的业务处理、数据使用，为经常被用作查询条件、或者被要求排序的字段建立索引。
1创建索引的应注意原则
一般来说，应该在这些列上创建索引，例如：
在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
基于优化器对SQL语句的优化处理，我们在创建索引时可以遵循下面的一般性原则：
（1）为经常出现在关键字orderby、groupby、distinct后面的字段，建立索引。
在这些字段上建立索引，可以有效地避免排序操作。
如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
（2）在union等集合操作的结果集字段上，建立索引。
其建立索引的目的同上。
（3）为经常用作查询选择的字段，建立索引。
（4）在经常用作表连接的属性上，建立索引。
（5）考虑使用索引覆盖。
对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。
除了以上原则，在创建索引时，我们还应当注意以下的限制：
（1）限制表上的索引数目。
对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。
索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
（2）不要在有大量相同取值的字段上，建立索引。
（3）避免在取值朝一个方向增长的字段（例如：
日期类型的字段）上，建立索引；对复合索引，避免将这种类型的字段放置在最前面.
（4）对复合索引，按照字段在查询条件中出现的频度建立索引。
（5）删除不再使用，或者很少被使用的索引。
将它们删除，从而减少索引对更新操作的影响。
同样，对于有些列不应该创建索引。
一般来说，不应该创建索引的的这些列具有下列特点：
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。
反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。
大比例，即需要在表中搜索的数据行的比例很大。
增加索引，并不能明显加快检索速度。
第三，对于那些定义为text,image和bit数据类型的列不应该增加索引。
这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。
这是因为，修改性能和检索性能是互相矛盾的。
当增加索引时，会提高检索性能，但是会降低修改性能。
当减少索引时，会提高修改性能，降低检索性能。
因此，当修改性能远远大于检索性能时，不应该创建索引。
2创建索引的方法
创建索引有多种方法，这些方法包括直接创建索引的方法和间接创建索引的方法。
直接创建索引，例如使用CREATEINDEX语句或者使用创建索引向导，间接创建索引，例如在表中定义主键约束或者唯一性键约束时，同时也创建了索引。
虽然，这两种方法都可以创建索引，但是，它们创建索引的具体内容是有区别的。
使用CREATEINDEX语句或者使用创建索引向导来创建索引，这是最基本的索引创建方式，并且这种方法最具有柔性，可以定制创建出符合自己需要的索引。
在使用这种方式创建索引时，可以使用许多选项，例如指定数据页的充满度、进行排序、整理统计信息等，这样可以优化索引。
使用这种方法，可以指定索引的类型、唯一性和复合性，也就是说，既可以创建聚簇索引，也可以创建非聚簇索引，既可以在一个列上创建索引，也可以在两个或者两个以上的列上创建索引。
通过定义主键约束或者唯一性键约束，也可以间接创建索引。
主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。
在创建主键约束时，系统自动创建了一个唯一性的聚簇索引。
虽然，在逻辑上，主键约束是一种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯一性的聚簇索引。
换句话说，在物理实现上，不存在主键约束，而只存在唯一性的聚簇索引。
同样，在创建唯一性键约束时，也同时创建了索引，这种索引则是唯一性的非聚簇索引。
因此，当使用约束创建索引时，索引的类型和特征基本上都已经确定了，由用户定制的余地比较小。
当在表上定义主键或者唯一性键约束时，如果表中已经有了使用CREATEINDEX语句创建的标准索引时，那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。
也就是说，主键约束或者唯一性键约束创建的索引的优先级高于使用CREATEINDEX语句创建的索引。
索引有两个特征，即唯一性索引和复合索引。
唯一性索引保证在索引列中的全部数据是唯一的，不会包含冗余数据。
如果表中已经有一个主键约束或者唯一性键约束，那么当创建表或者修改表时，SQLServer自动创建一个唯一性索引。
然而，如果必须保证唯一性，那么应该创建主键约束或者唯一性键约束，而不是创建一个唯一性索引。
当创建唯一性索引时，应该认真考虑这些规则：
当在表中创建主键约束或者唯一性键约束时，SQLServer自动创建一个唯一性索引；如果表中已经包含有数据，那么当创建索引时，SQLServer检查表中已有数据的冗余性；每当使用插入语句插入数据或者使用修改语句修改数据时，SQLServer检查数据的冗余性：
如果有冗余值，那么SQLServer取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认；只能在可以保证实体完整性的列上创建唯一性索引，例如，不能在人事表中的姓名列上创建唯一性索引，因为人们可以有相同的姓名。
复合索引就是一个索引创建在两个列或者多个列上。
在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。
当创建复合索引时，应该考虑这些规则：
最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节，也就是说复合列的长度不能太长；
在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，应该首先定义最唯一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；为了使查询优化器使用复合索引，查询语句中的WHERE子句必须参考复合索引中第一个列；当表中有多个关键列时，复合索引是非常有用的；使用复合索引可以提高查询性能，减少在一个表中所创建的索引数量
索引的优点及缺点
1优点：
创建索引可以大大提高系统的性能。
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
虽然增加索引有如此多的优点，但是仍能不对表中的每一个列创建一个索引，这是非常不明智的。
这是因为，增加索引也有许多不利的一个方面。
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
索引的调用
索引是一种树状结构，其中存储了关键字和指向包含关键字所在记录的数据页的指针。
当使用索引查找时，系统沿着索引的树状结构，根据索引中关键字和指针，找到符合查询条件的的记录。
最后，将全部查找到的符合查询语句条件的记录显示出来。
索引的优化
想要优化索引，必须了解不同索引类型的不同特点，
聚集索引，表中存储的数据按照索引的顺序存储，检索效率比普通索引高，但对数据新增/修改/删除的影响比较大
非聚集索引，不影响表中的数据存储顺序，检索效率比聚集索引低，对数据新增/修改/删除的影响很小
1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。
连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：
外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。
----2.查看执行方案的方法--用setshowplanon，打开showplan选项，就可以看到连接顺序、使用何种索引的信息；想
看更详细的信息，需用sa角色执行dbcc（3604,310,302）。
----1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时
要尽可能将操作移至等号右边。
----2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把
子句拆开；拆开的子句中应该包含索引。
----3.要善于使用存储过程，它使SQL变得更加灵活和高效。
SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。
其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体
1B树与T树特点
B树是我们常使用的索引结构，它是一种动态平衡多路查找树，其节点图和形式图如下图1.1和图1.2所示，因为B树结构宽而浅，并且从根节点到任一叶节点都具有相同路径长度，而且该路径长度最短，所以B树可以尽量减少磁盘访问次数[3]。
与B树相比，我们使用的索引结构T树优缺点非常明显，T树是一种一个节点中包含多个关键字的平衡二叉树，其节点图和形式图如下图1.3和图1.4所示，T树的优点就是每个节点只有两个指向其它节点的指针，每访问到一个新的索引节点，索引的范围减少一半[4]。
它的缺点就是增加了树的高度，使节点的检索次数增加。
2性能分析
我们在学术期刊上很少在时间和空间复杂度上比较B树与T树之间的性能，本文在此进行具体分析，首先，从时间复杂度来说，如果一颗包含N个关键字，高度为h,阶数为m的B树，深度为1时至少有两个节点，根据B树性质，除根节点和叶子节点，其它结点至少有m/2个孩子，所以深度为2时至少有2×（m/2）个节点，深度为3时至少有2×（m/2）2个节点，等等，直到深度为h时至少有2×（m/2）h-1个节点。
考虑到若有N个关键字，则叶子节点数为N+1，关键字个数N满足不等式N+1≥2×（m/2）h-1即：
h≤log（m/2）（（N+1）/2）+1因而时间复杂度为O（log（m/2）（（N+1）/2））。
但是，T树为一种特殊二叉树，即一个节点内包含多个关键字的平衡二叉树，T树的查找类似二叉树，唯一的区别是在T树节点的最大值和最小值之间进行比较，而不是与单个值比较，首先，从树的根节点开始查找。
如果查找的值比节点最小值小，在左子树中查找；如果比最大值大，则在右子树中查找；否则，在当前节点中折半查找。
同时我们可知包含有N个关键字的T树，它将具有的节点数为N/K。
由此推断比较次数为log2N+（1/2）×log2N/K（N为T树关键字的数目，K为节点中元素数目），时间复杂度为O（log2N+（1/2）×log2N/K）。
从空间复杂度来说，B树的空间与B树的阶相关，它包括叶子节点空间和非叶子节点空间，,而T树的空间就是N/K个节点的空间。
当索引数量较少时进行插入操作，B树插入速度要比T树慢;但是当索引数量很大时,B树的插入速度要比T树快。
这是因为与T树相比,B树在插入时要更新结点内索引以及记录地址数组,如果记录量很少时,这些额外开销要多于结点内索引定位时带来的开销,所以B树速度稍慢。
然而，如果记录量很大时,B树利用结点内索引定位减少的开销抵消了更新它所造成的开销,所以B树速度比T树要快。
索引该如何设计才高效?
1、如果我们仅仅只是这样告诉对方的：
“帮我确认一本数据库类别的讲述MySQL的叫做《MySQL性能调优与架构设计》的书是否在藏”，结果又会如何呢?
朋友只能一个大类区域一个大类区域的去寻找“数据库”类别，然后再找到“MySQL”范畴，再看到我们所需是否在藏。
由于我们少说了一个“计算机类”，朋友就必须到每一个大类去寻找。
所以，我们应该尽量让查找条件尽可能多的在索引中，尽可能通过索引完成所有过滤，回表只是取出额外的数据字段。
2、如果我们是这样说的：
“帮我确认一本讲述MySQL的数据库范畴的计算机丛书，叫做《MySQL性能调优与架构设计》，看是否在藏”。
如果这位朋友并不知道计算机是一个大类，也不知道数据库属于计算机大类，那这位朋友就悲剧了。
首先他得遍历每个类别确认“MySQL”存在于哪些类别中，然后从包含“MySQL”书籍中再看有哪些是“数据库”范畴的（有可能部分是讲述PHP或者其他开发语言的），然后再排除非计算机类的（虽然可能并没有必要），然后才能确认。
所以，字段的顺序对组合索引效率有至关重要的作用，过滤效果越好的字段需要更靠前。
3、如果我们还有这样一个需求（虽然基本不可能）：
“帮我将图书馆中所有的计算机图书借来”。
朋友如果通过索引来找，每次都到索引柜找到计算机书籍所在的区域，然后从书架上搬下一格（假设只能以一格为单位从书架上取下，类比数据库中以block/page为单位读取），取出第一本，然后再从索引柜找到计算机图书所在区域，再搬下一格，取出一本…如此往复直至取完所有的书。
如果他不通过索引来找又会怎样呢?
他需要从地一个书架一直往后找，当找到计算机的书，搬下一格，取出所有计算机的书，再往后，直至所有书架全部看一遍。
在这个过程中，如果计算机类书籍较多，通过索引来取所花费的时间很可能要大于直接遍历，因为不断往复的索引翻阅所消耗的时间会非常长。
（延伸阅读：
这里有一篇以前写的关于Oracle的文章，索引扫描还是全表扫描（IndexScanOrFullTableScan））
所以，当我们需要读取的数据量占整个数据量的比例较大抑或者说索引的过滤效果并不是太好的时候，使用索引并不一定优于全表扫描。
4、如果我们的朋友不知道“数据库”这个类别可以属于“计算机”这个大类，抑或者图书馆的索引系统中这两个类别属性并没有关联关系，又会怎样呢?
也就是说，朋友得到的是2个独立的索引，一个是告知“计算机”这个大类所在的区域，一个是“数据库”这个小类所在的区域（很可能是多个区域），那么他只能二者选其一来搜索我的需求。
即使朋友可以分别通过2个索引检索然后自己在脑中取交集再找，那这样的效率实际过程中也会比较低下。
所以，在实际使用过程中，一次数据访问一般只能利用到1个索引，这一点在索引创建过程中一定要注意，不是说一条SQL语句中Where子句里面每个条件都有索引能对应上就可以了。
（一）深入浅出理解索引结构
实际上，您可以把索引理解为一种特殊的目录。
微软的SQLSERVER提供了两种索引：
聚集索引（clusteredindex和非聚集索引（nonclusteredindex，下面，我们举例来说明一下聚集索引和非聚集索引的区别：
其实，我们的汉语字典的正文本身就是一个聚集索引。
比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。
如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。
另一种方法，需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。
但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。
我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。
通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。
进一步引申一下，我们可以很容易的理解：
每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。
（二）何时使用聚集索引或非聚集索引
下面的表总结了何时使用聚集索引或非聚集索引（很重要）。
动作描述
使用聚集索引
使用非聚集索引
外键列
应
应
主键列
应
应
列经常被分组排序（orderby）
应
应
返回某范围内的数据
应
不应
小数目的不同值
应
不应
大数目的不同值
不应
应
频繁更新的列
不应
应
频繁修改索引列
不应
应
一个或极少不同值
不应
不应
事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。
如：
返回某范围内的数据一项。
比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。
1
SQLSERVER默认是在主键上建立聚集索引的。
通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。
我们的这个办公自动化的实例中的列Gid就是如此。
此时，如果我们将这个列设为主键，SQLSERVER会将此列默认为聚集索引。
这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但笔者认为这样做意义不大。
显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。
从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。
在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。
这就使让ID号这个主键作为聚集索引成为一种资源浪费。
其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。
在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。
通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。
虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。
事实上，我们完全可以让用户打开系统首页时，数据
升级会员 