《理工·sqlserver数据库管理标准教程ppt第6章》由会员分享，可在线阅读，更多相关《理工·sqlserver数据库管理标准教程ppt第6章（70页珍藏版）》请在金锄头文库上搜索。
1、第6章 索引及关系完整性,6.1 索引的概述 6.2 索引的分类 6.3 索引的操作 6.4 关系完整性,6.1 索引的概述,索引是数据库中对数据查询的一种优化方式，通过创建索引可以使用户在对数据库查询的时候更加有效率。 虽然在Create Table或者Create View语句中没有指定一个特殊的行序，但是SQL Server确实使用内部索引来有效地进行行选择和排序。当执行一条DML语句，例如执行Select语句时，或者当打开一个SQL游标时，SQL Server自动选择使用的索引。当指定一个主键或者唯一性约束时，SQL Server为基表创建一个内部索引。当然用户也可以使用SQL Cre
2、ate index语句创建附加的索引。,下一页,返回,6.1 索引的概述,下一页,上一页,返回,索引一旦创建就和其相关的表绑定到了一起。就索引的功能来讲，它是为了提高对表查询的效率，脱离了表来讲索引是没有任何意义的。但对于索引，读者在学习的时候要区别于前面讲的Select语句中的group by选项，本书后面还会对两者进行比较。索引是数据库学习的一个难点，很多学生虽然对它的语法熟悉，但对于在什么时候用索引、怎么用却不能够正确处理。如果读者能够理解索引的真正作用，我们就可以通过一些小小的操作使得我们数据库的查询效率有很大提高。,6.1 索引的概述,下一页,上一页,返回,前面我们已经提到，索引的作
3、用是提高数据库的查询效率。在这里，希望各位读者首先要认识到其重要性。数据库是管理信息系统的灵魂，它的好坏直接影响到系统的效率，而数据库各种操作中，查询占了70%多，所以，查询的好坏也就直接影响到数据库的效率了。可能前面的话太抽象，我们来举个例子，银行的取款系统是一个最常见的信息管理系统，用户做所有的操作之前，都要先找到数据库中的相关用户信息。我们试想一下，如果您在自动取款机前面等了半个小时才找到您的相关信息，您还会用这种取款方式吗？可见数据查询的效率，很大程度上决定了软件的成败，读者通过这章的学习，就可以使用索引使数据库更加实用。,6.1 索引的概述,上一页,返回,索引这个词，我们平时都会听到
4、，一般它和目录是一个意思，在数据库中的作用也就是目录的作用。索引一旦创建就和其相关的表绑定到了一起，同时，它也要占用表的资源。如果一个表的索引太多，我们维护起来会很不方便，所以，索引也不是越多越好，读者在开发数据库的时候要实际问题实际分析。一般情况下，我们不应该在经常更新的列上面创建索引。 索引和group by的区别：group by是查询表时的一个选项，它的作用是使得查询的结果按照某一个顺序显示，它不会占用表的资源，也不会使查询更快，它只是改变结果显示的顺序。,6.2 索引的分类,下一页,返回,索引可以分为普通索引和簇索引，SQL Server允许每一个基表有一个簇索引。簇索引使表中的数据
5、与索引一同存储并使行的物理顺序与索引的顺序保持一致。这种结构可以大大提高访问行的性能，特别是对于有连续键值的行集。如果以某种特殊的顺序频繁访问行（例如， 按学号访问学生），检索相关的行集（例如, 指定订单ID的订单项），或者根据值的范围检索行，那么可以考虑在合适的列上使用簇索引。如果这些列已经列在主键约束或者唯一性的约束中，可以在 Create Table的约束子句中增加Clustered关键字。否则，可以在 Create Index语句中增加Clustered关键字，如下所示：,6.2 索引的分类,下一页,上一页,返回,Create Clustered Index EmpNameIXOn s
6、tudent(学号) 以上SQL语言的作用是在student表中的学号列上创建了一个簇索引，它的顺序是学号的升序。如果没有指定Clustered关键字，那么SQL Server就创建一个非簇索引。对于具体的语法，本章后面会做详细说明。,6.2 索引的分类,下一页,上一页,返回,注意：对于一个长键使用簇索引，要考虑到空间性能的平衡。当表有一个簇索引时，SQL Server存储一个簇键值作为“定位器”，用于该表的非簇索引中的内容。对于一个很大的表，这样可能显著增加非簇索引所需的空间，对于较小的表，影响不会太大，对于没有簇索引的表，SQL Server就使用一个内部的行标识符作为存储每一个索引项的定
7、位器。,6.2 索引的分类,上一页,返回,索引对数据库的性能有积极的影响也有消极的影响。简言之，索引可以加快数据检索的速度，但如果索引列的值变化时，SQL Server要更新新基表中的数据项，这样就需要花费一些时间。总的原则是，对于那些在数据库中插入新行时容易发生变化的列，创建索引时，要格外地小心。这些索引应该为平衡更新的影响提供必要的帮助。,6.3 索引的操作,下一页,返回,6.3.1 创建索引定义语法格式在SQL语言中，创建索引的语句是Create Index。Create Index语句所要求的部分相当简单，写一个索引名称，然后指定索引要在其上创建的一个基表和最多16个列。索引名称不能限
8、定，因为它们总是由表的所有者拥有，并且创建在与表相同的数据库中。在一个表的索引集中，索引名称必须唯一。为了避免混淆，使用在数据库中也是唯一的索引名称，即使SQL Server不要求这点也应如此。不能在索引上使用Text, nText, Image, Bit，索引中所有列总长度是900个字节。,6.3 索引的操作,下一页,上一页,返回,使用CREATE INDEX语法形式如下:CREATE UNIQUECLUSTERED|NONCLUSTERED索引名ON表名|视图名(WITH, nON 文件组名 其中为以下属性的组合: PAD _INDEX| FILLFACTOR=填充因子| IGNORE_D
9、UP_KEY| DROP_EXISTING| STATISTICS_NORECOMPUTE| SORT_IN_TEMPDB,6.3 索引的操作,下一页,上一页,返回,各选项的含义如下： UNIQUE 用于创建唯一索引，此时数据行中不允许出现重复的索引值，如果INSERT或UPDATE操作后会导致有重复的索引值出现时系统会报错。 CLUSTERED用于创建索引,它的顺序和数据行物理存储顺序一致，一个表或视图只能有一个簇索引，必须先为视图创建唯一簇索引，然后才能为该视图定义其他索引，必须在创建任何非簇索引之前创建索引，否则创建簇索引时将重建表上现有的非簇索引。 NONCLUSTERED用于创建非簇
10、索引，此时行的物理排序立于索引排序之上，每个表最多可以有249个非簇索引。 FILLFACTOR用于指定在SQL Server创建索引的过程中，各索引页级的填满程度（百分比），可以通过系统存储过程Sp_configure更改默认的填充因子。,6.3 索引的操作,下一页,上一页,返回, PAD_INDEX用于指定维护索引用的中间级中每个索引页上保留的可用空间。 IGNORE_DUP_KEY用于控制当尝试在唯一索引（包括唯一簇索引和唯一非簇索引）的列插入重复的键值时所发生的情况。 DROP_EXISTING用于创建索引时删除指定的、已存在的索引，如已存在，系统会给出警告消息，非簇索引只需重建一次，
11、而且只是在簇索引键不同的情况下才需要，不能使用DROP_EXISTING选项将簇索引转换成非簇索引，但是可以将唯一簇索引更改为非唯一。 STATISTICS_NORECOMPUTE用于指定过期的索引统计，不进行自动重新计算。 若要恢复自动更新统计,可执行没有NORECOMPUTE选项的UPDATE STATISTICS命令。,6.3 索引的操作,下一页,上一页,返回, SORT_IN_TEMPDB用于生成索引的中间排序结果将存储于tempdb数据库中。 下面举几个例子。我们已经创建了一张表student，它的结构如图6-1所示。 例6-1 我们在表student的学号列上建立一个簇索引。 Cr
12、eate clustered index IDstIx On student(学号 desc) 执行的结果如图6-2所示。,图6-1,返回,图6-2,返回,6.3 索引的操作,下一页,上一页,返回,在创建索引时，列的顺序有两种：升序（用asc表示）和降序（用desc表示）。如果在SQL语言中没指定，就默认为升序。 例6-2 在表student的姓名列和年龄列上创建一个普通索引。 Create index NastIx On student(姓名,年龄) 执行的结果如图6-3所示。 用户还可以通过查询分析器左边的对象浏览器查看执行的结果，如上面两例。我们通过图6-4可以看到，执行后，在查询分析中
13、左边的对象浏览器中可以看到两个索引。,图6-3,返回,图6-4,返回,6.3 索引的操作,下一页,上一页,返回,6.3.2 图形界面方式创建索引 索引除了用上一小节中的SQL语言创建，还可以通过SQL Server 2000中的企业管理器通过图形界面方式创建。 这一小节，我们就使用图形界面方式重新建立上一小节中的两个索引。其创建过程如下： 首先打开企业管理器，并找到要创建索引的表student，用鼠标右击表，如图6-5所示。 在弹出的快捷菜单中选择“设计表”命令，进入表的设计界面，如图6-6所示。,图6-5,返回,图6-6,返回,6.3 索引的操作,下一页,上一页,返回, 进入表的设计界面后，
14、用鼠标右击，在弹出的对话框中选择“索引/键”命令，进入索引的创建界面，如图6-7和6-8所示。 在索引的编辑界面，再做如下的设置：在“索引名”文本框中输入IDstIx，然后设置成学号的升序，并选中“创建为CLUSTERED(C)”复选框，如图6-9所示。 对于上一小节的另外一个索引NastIX，操作基本相同，这里就不再详细描述了。,图6-7,返回,图6-8,返回,图6-9,返回,6.3 索引的操作,下一页,上一页,返回,注意： 使用图形界面方式创建索引，可以直观、方便地创建和管理索引。在数据库中创建索引类似于书籍中的目录。对一个没有索引的表进行查询操作时，系统将检索表中的每一个数据行，这就好比
15、在一本没有目录的书中查找信息。数据库中的索引是一个表中所包含的值以及相应的存储位置的列表，这样，查询时不需对整个表进行扫描，就可以查询到所需的数据。 当然，在索引对查询性能带来提高的同时，需要耗费更多的物理空间。此外，为了维护索引，对数据的插入、更新、删除操作需要花费更多的时间。正是因为有建立索引所需要的代价，所以并不需要为表中的每一列都创建索引。,6.3 索引的操作,下一页,上一页,返回,6.3.3 查询索引信息Sp_helpindex查询有关表或视图上索引的信息。如果已经用Update Statistics的Norecompute选项设置了索引，那么在Sp_helpindex的结果集中会显
16、示这个信息。语法：sp_helpindexobjname=name参数：objname=name是当前数据库中表或视图的名称。name的数据类型为nvarchar(776)，没有默认值。返回代码值：0（成功）或1（失败）。,6.3 索引的操作,下一页,上一页,返回,6.3.4 删除索引定义语法格式 对于已经不用的索引，要尽快删除。为了删除数据库中的索引，可以使用下面一条语句： Drop Index 表名|视图.索引名, 如：Drop Index student.IDstIx，即将student上名为IDstIx的索引删除。 使用这条语句时一定要小心，删除以后的索引是不能够恢复的。Drop语句允许使用以逗号分开的对象列表。Drop Index语句的索引名必须由涉及的表或视图名来限定。只能在当前数据库中删除索引。 另外，当删除数据库时，数据库中的所有对象也都被删除了。当删除一张表时，引用该表的所有索引、约束、触发器和许可也被删除。,
