索引概述讲稿.doc

索引概述 高丽伟 学号：2011020828 指导老师：陈梅老师P2 思路导图P3 Question考虑如下一个雇员记录的文件，每条记录包含字段：年龄、姓名、薪水；要求：查询员工记录中，所有薪水高于5000美元的雇员？传统思路：扫描整个文件来找到这样的记录集！（海量数据）P4 提出索引索引的概念：在磁盘上组织数据记录的一种数据结构。对数据库表中一列或多列的值进行排序，使用索引可快速访问数据库表中的特定信息。索引的作用是优化某类数据检索。快速访问数据库表中的特定信息，加快对表中记录的查找或排序。P5 1、索引的分类按照不同的分类方式有一下三种划分方式：1) 按数据的物理存放顺序分为聚簇索引和非聚簇索引。P6 聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。聚簇索引确定表中数据的物理顺序，因此一个表只能包含一个聚簇索引，但该索引可以包含多个列。聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻，这样就能显著提高多行相关索引的速度。P7非聚簇索引，叶级页指向表中的记录，记录的物理顺序与逻辑顺序没有必然的联系。非聚簇索引则更象书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。 非聚簇索引：数据记录的顺序与某一索引的数据项顺序没有关系。聚簇索引能提高多行检索的速度而非聚簇索引对于单行的检索很快。2) 按数据文件的查找关键字 主索引 P8如果数据文件是按某个索引的查找关键字来排序的话则称该索引是主索引，主索引的记录排序与数据文件相同。下图为一个银行例子中的account记录的的顺序文件。顺序文件：一个主索引文件加在查找关键字上排序的数据文件。在图示的例子中，branch-name是搜索码，而记录按照该搜索码顺序存放。 辅助索引P9如果数据文件的排序关键字与某个索引的查找关键字不同的话则称该索引为辅助索引，辅助索引的记录排序与数据文件不同。辅助索引提供了访问文件中除了已有的主访问方法之外的一种辅助方法。可以创建在一个候选码上，也可以创建在非码字段上。如果创建在一个候选码的字段上，这样对每个记录有唯一的取值，如果创建在非码字段上，可以有重复的取值。下图为给出了一个辅助索引结构，它在account文件的搜索码balance上使用了一层额外间接。 和主索引相比，由于项数目更多，辅助索引需要更多的存储空间和更长的搜索时间。然而，对于任意一个纪律来说，辅助索引对其搜索时间的改善比主索引更加显著。3) 稠密索引和稀疏索引P10稠密索引：在数据文件中对于每个查找关键字的值都对应一个索引记录或索引入口稀疏索引：在数据文件中某些（而不是所有）查找关键字的值在索引文件中存在索引记录P11稀疏索引：在数据文件中某些（而不是所有）查找关键字的值在索引文件中存在索引记录P12稠密索引文件的记录在数据区存放是任意的，但索引是按序的。这类文件的索引查找、更新都较方便，但由于索引项多，占用空间较大。 稀疏索引文件的数据记录要求按关键字顺序排列。因此，其特点是索引项少，管理方便，但插入、删除记录代价较高。 在实际应用中，更常用的是稀疏索引。P13 2.索引的属性在关系数据模型中描述某个实体的一种事实，相当于索引表中的一个栏。在SQL Server 2005中，索引的属性我们用如下框图表示：索引的数据结构 树的索引 树结构索引能够支持等值查找和范围查找。 哈希索引一种将任意长度的输入压缩到某一固定长度的输出的方法；Hash函数:将查找键为参数并计算出一个介于0到N-1的整数，其中N是桶的数目；基于哈希的索引仅能支持等值查找。 位图索引我们目前大量使用的索引一般主要是B*Tree索引，在索引结构中存储着键值和键值的RowID，并且是一一对应的。而位图索引主要针对大量相同值的列而创建(例如：类别，操作员，部门ID，库房ID等)，索引块的一个索引行中存储键值和起止Rowid，以及这些键值的位置编码中的每一位表示键值对应的数据行的有无。一个位图索引块可能指向的是几十甚至成百上千行数据的位置。这种方式存储数据，相对于B*Tree索引，占用的空间非常小，创建和使用非常快。三、索引的优缺点创建索引可以大大提高系统的性能。1.建立索引的特点1）在经常需要搜索的列上，可以加快搜索的速度；2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。2索引的优点第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。3索引的缺点第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。 第三，对于那些定义为text， image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少，不利于使用索引。 第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。 4. 不应该建立索引的情况第一，对于那些在查询中很少使用或者参考的列不应该创建索引。第二，对于那些只有很少数据值的列也不应该增加索引。第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。第四，当修改性能远远大于检索性能时，不应该创建索引。 Oralce数据库中如何选择合适的索引类型虽然说，在表中是否创建索引，不会影响到Oracle数据库的使用，若没有建立相关索引的话，则数据库在查询记录的时候，不得不去查询整个表。当表中的记录比较多的时候，其查询效率就会很低。所以，合适的索引，是提高数据库运行效率的一个很好的工具。不过，并不是说表上的索引越多越好。故在数据库设计过程中，还是需要为表选择一些合适的索引。在理论上，虽然一个表可以设置无限的索引。但是，数据库管理员需要知道，表中的索引越多，维护索引所需要的开销也就越大。每当数据表中记录有增加、删除、更新变化的时候，数据库系统都需要对所有索引进行更新。在索引建立上，我们总结了如下几个原则。原则一：在基数小的字段上要善于使用位图索引。基数是位图索引中的一个基本的定义，它是指数据库表中某个字段内容中不重复的数值。如在员工信息表中的性别字段，一般就只有男跟女两个值，所以，其基数为2;婚姻状况字段的话，则其只有已婚、未婚、离婚三种状态，其基数就为3;民族一览内也是只有有限的几个值。对于要查询基数小的字段，如现在用户想查找所有婚姻状况为“已婚”的“女性”时，利用位图索引可以提高查询的效率。这主要是因为标准索引是通过在索引中保存排序过的索引列以及对应的ROWID来实现的。若我们在基数小的列上建立标准索引的话，则其会返回大量的记录。而当我们在创建位图索引的时候，在Oracle会对整个表进行扫描，并且会为索引列的每个取值建立一个位图。若内容相同，则在位图上会以一个相同的数字表示。此时，若这个字段的基数比较小的话，则若需要实现对整个字段的查询的话，效率就会非常的高。因为此时，数据库只要位图中数字相同的内容找出来即可。除了在数据表某列基数比较小的情况下，采用位图索引外，我们往往在一些特殊的情况下，也会建议采用位图索引。最常见的情况是，在Where限制条件中，若我们多次采用AND或者OR条件时，也建议采用位图索引。因为当一个查询饮用了一些部署了位图索引的列的时候，这些位图可以很方便的与AND或者Or 运算符操作结合以快速的找出用户所需要的记录。但是，这里要注意，不是在条件语句中包含运算符的时候，采用位图索引都能够提供比较高的效率。一般来说，只有AND 或者OR运算符的时候，位图索引才会比较具有优势。若此时用户采用大于号或者不等于号作为条件语句中的限制条件的时候，则往往采用标准索引具有更大的优势。我们在数据库设置中，一般只有在三种情况下才采用位图索引。一是列的基数比较小，而有可能需要根据这些字段的内容查找相关的记录;二是在条件语句中，用到了AND或者OR运算符的时候。除了这两种情况外，最好能够采用其他适合的索引。 第三种情况是，需要用到NULL作为查询的限制条件。原则二：创建索引的一些限制条件。并不是说，表或者列建立的索引越多越好。相反，索引建的越多，有时会反而会影响数据库运行的整体性能。所以，在建立索引的时候，仍然会有一些限制条件。一是不要对一些记录内容比较少的表建立索引。在一个应用系统设计的时候，如设计一个ERP系统的数据库，其虽然有几千张表。但是，并不是每张表都有大量记录的。相反，其中有近一半左右的数据表，可能其存储的数据不会超过百条。如员工登陆帐户密码表、企业部门信息表等等。对于这些记录内容比较少的表，我们建立最好不要为其建立索引。无论是表上的，还是字段上，都不要建立索引。二是若表中的内容比较大，但是，这个表基本上不怎么查询的时候，则只需要在表上建立索引即可;而不需要在字段上建立索引。如现在在ERP系统中，有一张表是“AD_Table”。其存储的是这个数据库中相关表的信息。这张表只有在数据库设计的时候才会用到。故这张表中的记录虽然比较多，但是由于用户用的比较少，所以，一般没有必要为这张表建立列级别上的索引。而直接用表索引来代替。三是在一些NULL字段上，要根据实际情况来判断是否要建立索引。如现在有一张人事档案的表格，其上面有两个字段，分别为“身份证号码”与“地区”。有时会为了某个原因，企业需要所有员工都在系统中登记他们的身份证号码，以方便他们办工资卡、社会保险等等。所以人事管理可能需要经常的查询系统，看看有没有没有身份证号码的员工信息。此时，就需要利用条件“IS NULL”来查询我们所需要的记录。故为了提高查询效率，若某个记录可能为空，并且经常需要以NULL为条件进行查询的时候，则最好给这个字段添加一个索引，并且最好建立位图索引。相反，若虽然可能会以NULL这个条件作为查询的限制语句，但是，用的不是很多的时候，则就没有必要为其建立索引。原则三：多表连接查询的索引设计。 如现在有一个人事管理系统。人事经理想知道员工的社保缴纳情况。要得到这份报表的话，就可能需要关联其他相关信息表格以便查询到用户所需要的内容。为此，需要用Join语句关联这些表。为了提高数据库的查询效率，这些用来关联的字段，最好能够建立索引。这可以显著的提高查询的速度。原则四：在表的更新速度与查询速度之间寻求一个平衡点。众所周知，索引本身并不影响数据库的使用，其主要是为了提高数据库的查询效率。但是，由于当数据库的表中的数据更新的时候，包括记录的增加、删除、更改等等，都会对虽有的索引进行更新。很明显，索引虽然可以提高查询速度。但是，也会对一些表的更新操作产生不良的影响。当在表中建立的索引越多，这个不利影响也会越大。故数据库管理员在设置索引的时候，还需要注意，在这两个之间需要一个均衡点。按照