SQL Server数据库管理、开发与实践 教学课件 ppt 作者 郑诚 第7章 索引与数据完整性

本章要求,第7章 索引与数据完整性,索引的概念 索引的优缺点 索引的分类 索引的操作 索引的分析与维护 数据完整性理论与实现,主要内容,第7章 索引与数据完整性,1.索引的概念 2.索引的优缺点 3.索引的分类 4.索引的操作 5.索引的分析与维护 6.全文索引 7.数据完整性 8.综合实例Transact-SQL维护全文索引,索引是为了加速对表中数据行的检索而创建的一种分散存储结构。它是针对一个表而建立的，每个索引页面中的行都含有逻辑指针，指向数据表中的物理位置，以便加速检索物理数据。因此，对表中的列是否创建索引，将对查询速度有很大的影响。一个表的存储是由两部分组成的，一部分用来存放表的数据页，另一部分存放索引页。从中找到所需数据的指针，然后直接通过该指针从数据页面中读取数据，从而提高查询速度。,7.1 索引的概念,7.2.1 索引的优点 7.2.2 索引的缺点,7.2 索引的优缺点,索引有以下优点： 创建唯一性索引，保证数据库表中每一行数据的唯一性。 大大加快数据的检索速度，这也是创建索引的最主要原因。 加速表与表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 在使用分组和排序子句进行数据检索时，同样可以减少查询中分组和排序的时间。 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。,7.2.1 索引的优点,索引有以下缺点： 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引，那么需要的空间就会更大。 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，降低了数据的维护速度。,7.2.2 索引的缺点,7.3.1 聚集索引 7.3.2 非聚集索引,7.3 索引的分类,聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。 只有当表包含聚集索引时，表中的数据行才按排列顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。 除了个别表之外，每个表都应该有聚集索引。聚集索引除了可以提高查询性能之外，还可以按需重新生成或重新组织来控制表碎片。 聚集索引按下列方式实现： PRIMARY KEY 和 UNIQUE 约束 在创建 PRIMARY KEY 约束时，如果不存在该表的聚集索引且未指定唯一非聚集索引，则将自动对一列或多列创建唯一聚集索引。主键列不允许空值。 在创建 UNIQUE 约束时，默认情况下将创建唯一非聚集索引，以便强制 UNIQUE 约束。如果不存在该表的聚集索引，则可以指定唯一聚集索引。,7.3.1 聚集索引,非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。 从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位器是聚集索引键。,7.3.2 非聚集索引,7.4.1 索引的创建 7.4.2 查看索引信息 7.4.3 索引的修改 7.4.4 索引的删除 7.4.5 设置索引的选项,7.4 索引的操作,1使用企业管理器创建索引 操作步骤如下： （1）启动SQL Server Management Studio，并连接到SQL Server 2008数据库.。 （2）选择指定的数据库“db_2008”，然后展开要创建索引的表，在表的下级菜单中，鼠标右键单击“索引”，在弹出的快捷菜单中选择“新建索引”命令，如图7-1所示。弹出“新建索引”窗体，如图7-2所示。 图7-1 选择“新建索引”,7.4.1 索引的创建,（3）在“新建索引”窗体中单击“添加”按钮，弹出“从表中选择列”窗体，在该窗体中选择要添加到索引键的表列，如图7-3所示。 图7-3 选择列窗体,（4）单击“确定”按钮，返回到“新建索引”窗体，在“新建索引”窗体中，单击“确定”按钮，便完成了索引的创建。 CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,.n ) WITH ,.n ON filegroup := PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB ,CREATE INDEX语句的参数及说明如表7-1所示。 表7-1 CREATE INDEX语句的参数及说明,【例 7-1】 为Student 表的 Sno列创建非聚集索引，SQL语句如下。 USE db_2008 CREATE INDEX IX_Stu_Sno ON Student (Sno) 【例 7-2】 为Student 表的 Sno列创建唯一聚集索引，SQL语句如下。 USE db_2008 CREATE UNIQUE CLUSTERED INDEX IX_Stu_Sno1 ON Student (Sno) 无法对表创建多个聚集索引。 【例 7-3】 为Student 表的 Sno列创建组合索引，SQL语句如下。 USE db_2008 CREATE INDEX IX_Stu_Sno2 ON Student (Sno,Sname DESC),使用索引虽然可以提高系统的性能，增强数据的检索速度，但它需要占用大量的物理存储空间，建立索引的一般原则如下。 （1）只有表的所有者可以在同一个表中创建索引。 （2）每个表中只能创建一个聚集索引。 （3）每个表中最多可以创建249个非聚集索引。 （4）在经常查询的字段上建立索引。 （5）定义text、image和bit数据类型的列上不要建立索引。 （6）在外键列上可以建立索引。 （7）主键列上一定要建立索引。 （8）在那些重复值比较多、查询较少的列上不要建立索引。,1使用企业管理器查看索引 使用企业管理器查看索引的步骤如下： （1）启动SQL Server Management Studio，并连接到SQL Server 2008数据库.。 （2）选择指定的数据库“db_2008”，然后展开要查看索引的表。 （3）鼠标右键单击该表，在弹出的快捷菜单中选择“设计”命令。 （4）弹出“表结构设计”窗体，鼠标右键单击该窗体，在弹出的快捷菜单中选择“索引/键”命令。 （5）打开“索引/键”窗体，如图7-4所示。在窗口的左侧选中某个索引，在窗口的右侧就可以查看此索引的信息，并可以修改相关的信息。 图7-4 索引/键窗体,7.4.2 查看索引信息,2使用系统存储过程查看索引 系统存储过程sp_helpindex可以报告有关表或视图上索引的信息。 语法如下： sp_helpindex objname = 'name' 参数说明： objname = 'name'：用户定义的表或视图的限定或非限定名称。 【例 7-4】 用系统存储过程sp_helpindex，查看db_2008数据库中Student表的索引信息，SQL语句如下。 use db_2008 EXEC Sp_helpindex Student 3利用系统表查看索引信息 查看数据库中指定表的索引信息，可以利用该数据库中的系统表sysobjects（记录当前数据库中所有对象的相关信息）和sysindexes（记录有关索引和建立索引表的相关信息）进行查询，系统表 sysobjects可以根据表名查找到索引表的ID号，再利用系统表sysindexes根据ID号查找到索引文件的相关信息。,【例 7-4】存储过程,【例 7-5】 利用系统表查看“db_2008”数据库中“Student”表中的索引信息，SQL语句及运行结果如图7-5所示。 图7-5 查看Student表中的索引,【例 7-5】查看Student表中的索引,1使用企业管理器修改索引 使用企业管理器修改索引与使用企业管理器查看索引的步骤相同，在“索引/键”窗体中就可以修改索引的相关信息。 2使用Transact-SQL语句更改索引名称 在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、列、别名数据类型或 Microsoft .NET Framework 公共语言运行时 (CLR) 用户定义类型。 语法如下： sp_rename objname = 'object_name' , newname = 'new_name' , objtype = 'object_type' 参数说明： objname = 'object_name'：用户对象或数据类型的当前限定或非限定名称。 newname = 'new_name'：指定对象的新名称。 objtype = 'object_type'：要重命名的对象的类型。 【例 7-6】 利用系统存储过程sp_rename， “IX_Stu_Sno”索引重命名为“IX_Stu_Sno1”，SQL语句如下。 USE db_2008 EXEC sp_rename 'Student.IX_Stu_Sno','IX_Stu_Sno1 注意：要对索引进行重命名时，需要修改的索引名格式必须为“表名.索引名”。,7.4.3 索引的修改,1使用企业管理器删除索引 使用企业管理器删除索引与使用企业管理器查看索引的步骤相同，在“索引/键”窗体，单击“删除”按钮，就可以把当前选中的索引删除。 2使用Transact-SQL语句删除索引 DROP INDEX 语句表示从当前数据库中删除一个或多个关系索引、空间索引、筛选索引或 XML 索引。 DROP INDEX 语句不适用于通过定义 PRIMARY KEY 或 UNIQUE 约束创建的索引。若要删除该约束和相应的索引，请使用带有 DROP CONSTRAINT 子句的 ALTER TABLE。 DROP INDEX 语句的语法如下： DROP INDEX 'table.index | view.index' ,.n ,7.4.4 索引的删除,DROP INDEX 语句的参数及说明如表7-2所示。 表7-2 DROP INDEX 语句的参数及说明 【例 7-7】 删除“Student”表中的“IX_Stu_Sno1”索引，SQL语句如下。 USE db_2008 -判断表中是否有要删除的索引 If EXISTS(Select * from sysindexes where name='IX _Stu_Sno1') Drop Index Student.IX_Stu_Sno1,【例 7-7】删除“Student”表中的“IX_Stu_Sno1”索引,1设置PAD_INDEX选项 PAD_INDEX选项是设置创建索引期间中间级别页中可用空间的百分比。 对于非叶级索引页需要使用PAD_INDEX选项设置其预留空间的大小。PAD_INDEX选项只有在指定了FILLFACTOR选项时才有用，因为PAD_INDEX是由FILLFACTOR所指定的百分比决定。默认情况下，给定中间级页上的键集，SQL Server将确保每个索引页上的可用空间至少可以容纳一个索引允许的最大行。如果FILLFACTOR指定的百分比不够大，无法容纳一行，SQL Server将在内部使用允许的最小值替代该百分比。 【例 7-8】 为“Student”表的Sno列创建一个簇索引“IX_Stu_Sno”，并将预留空间设置为“1