《SQL Server2005数据库实用教程 教学课件 ppt 作者 常军林 ppt第五章》由会员分享，可在线阅读，更多相关《SQL Server2005数据库实用教程 教学课件 ppt 作者 常军林 ppt第五章（31页珍藏版）》请在金锄头文库上搜索。
1、,知识目标,了解创建索引的作用和原则 掌握索引的主要类型及其特点,技能目标,能够使用SQL Server Management Studio或CREATE INDEX语句创建索引。 能够使用SQL Server Management Studio或DROP INDEX语句删除索引。,5.1 索引概述 5.2 创建索引 5.3 删除索引 5.4 实训 创建和维护索引,5.1 索引概述 5.1.1 索引的概念 索引就是加快检索表中数据的方法。数据库中的索引和书的目录很相似。在一本书中，利用目录可以快速查找所需信息，无需阅读整本书；在数据库中，索引使数据库程序无需对整个表进行扫描，就可以在其中找到所
2、需数据。索引是一个列表，这个列表中包含了某个表中一列或者若干列的集合，以及这些值的记录在数据表中存储位置的物理地址。,如果没有建立索引，在数据库中查询符合条件的记录时，系统将会从第一条记录开始，对表中的所有记录进行扫描，扫描整个表格是从存储表格的气起始地址开始，依次比较记录，直到找到位置。如果有索引，通过索引查找时，因为索引是有序排列的，所以，可以通过高效的有序查找算法找到索引项，再根据索引项中记录的物理地址，找到查询结果的存储位置。,5.1.2 索引的作用和原则 1索引的作用 （1） 加快数据查询在表中创建索引后，SQL Server将在数据表中为其建立索引页，每个索引页中的行都含有指向数据
3、页的指针，当进行以索引为条件的数据查询时，将大大提高查询速度。 （2） 加快表的连接、排序和分组工作在进行表的连接或使用ORDER BY 和GROUP BY子句检索数据时，都涉及数据的查询工作，建立索引后，可以明显减少表的连接及查询中分组和排序的时间。,（3） 唯一性索引可以保证数据唯一性通过创建唯一索引，可以保证表中的数据不重复，强制实施行的唯一性。 2. 创建索引的原则 索引这么重要，但并不是在每一列上都要创建索引，这是因为： （1） 创建索引要花费时间和占用存储空间； （2） 建立索引加快了检索速度，却减慢了数据修改速度。因为每当执行一次数据修改（包括插入、删除和更新），就要维护索引，修
4、改的数据越多，涉及维护索引的开销也就越大。是否创建索引，在哪些列上创建索引，要,看创建索引和维护索引的代价与因创建索引所节省的时间相比较来定。 建立索引的一般原则是： （1） 对经常用来搜索数据记录的字段建立索引。 （2） 对表中的主键字段建立索引。 （3） 对表中的外键字段建立索引。 （4） 对在查询中用来连接表的字段建立索引。 （5） 对经常用来作为排序基准的字段建立索引。,5.1.3 索引的分类 索引的类型主要有：聚集索引、非聚集索引、唯一索引、视图索引、全文索引、XML索引等。 1聚集索引 聚集索引（Clustered Index）对表在物理数据页中的数据按列进行排序，然后再重新存储到
5、磁盘上。即在聚集索引中，行的物理存储顺序与索引顺序完全相同，即索引的顺序决定了表中行的存储顺序。表数据按照指定作为聚集索引的一个或多个键列排序并存储。由于表中的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个聚集索引。,由于聚集索引的顺序与数据行存放的物理顺序相同，因此，聚集索引最适合于范围搜索。因为相邻的行将被物理地存放在相同的页面上或相邻近的页面上，所以当在用于查找一定范围值的列上创建聚集索引时，聚集索引非常有效。因为，一旦使用聚集索引找到第一条符合条件的数据记录，同范围之后续键值的数据记录就会是相邻排列的。聚集索引的大小是表的5%。 当建立主键约束时，如果表中没有聚集索引，SQ
6、L会自动用主键作为聚集索引。用户可以在表的任何列或列的组合上建立聚集索引，实际应用中，主键是聚集索引的良好候选者。,2非聚集索引 非聚集索引（NonClustered Index）不会改变表中数据行的物理顺序，数据与索引分开存储，通过索引带有的指针与表中的数据发生联系。非聚集索引只是记录指向表中行的位置的指针，这些指针本身有序，通过这些指针可以在表中快速的定位数据。在创建非聚集索引之前，应先创建聚集索引。一个表最多能够拥有249个非聚集索引。在一个表上建立索引默认都是非聚集索引，在一个列上设置唯一性约束时也自动在该列上创建非聚集索引。,3唯一索引 如果要求索引中的字段值不能重复，可以建立唯一索
7、引（Unique Index）。唯一索引要求所有数据行中任意两行中的被索引列或索引列组合不能存在重复值，包括不能有两个空值NULL。也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同的。如果表中有多行的记录在某个字段上具有相同的值，则不能基于该字段建立唯一索引；同样对于多个字段的组合，如果在多行记录上有重复值或多个NULL，也不能在该组合上建立唯一索引。在一个列上建立唯一性约束时，会自动在该列上创建一个唯一性索引。聚集索引和非聚集索引都可以是一个唯一索引或非唯一索引。,4视图索引 将具体化视图和结果集永久的存储在唯一的聚集索引中，而且其存储方法与带聚集索引的表的存储方法相同。创建聚集
8、索引后，可以为视图添加非聚集索引。 5全文索引 一种特殊类型的基于标记的功能性索引，由Microsoft SQL Server全文引擎服务创建和维护，用于帮助在字符串数据中搜索复杂的词。 6XML索引 对XML数据类型创建的索引。,5.2 创建索引 5.2.1 使用SQL Server Management Studio创建索引 使用SQL Server Management Studio创建和修改索引很便捷，具体创建步骤如下： （1） 启动SQL Server Management Studio工具，在“对象资源管理器”中，依次展开各节点到数据库“学生图书管理系统”下的表节点。 （2） 展开
9、“图书信息”表，在“索引”项上右击，在快捷菜单中选择“新建索引”命令，弹出如图5-1所示“新建索引”窗口。,图5-1 新建索引,（3） 在“索引名称”文本框中，输入索引的名字“IX_图书类别”，“索引类型”下拉列表框用于设置索引类型，通过“唯一”复选框，可以设置索引的唯一性。 （4） 单击“添加”按钮，弹出如图5-2所示的对话框，选择“图书类别”列，单击“确定”按钮。,图5-2 选择列,（5） 返回到“新建索引”窗口，其中“排序顺序”列用于设置索引的排列顺序，默认为“升序”。单击“确定”，完成索引的创建过程。 SQL Server Management Studio 的“对象资源管理器”中，依
10、次展开到“索引”节点，右击某个索引的名字，依次选择“编写索引脚本为CREATE 到新查询编辑器窗口”，就可以查看到索引的定义语句，如图5-3所示。,图5-3 查看索引的定义语句,5.2.2 使用 CREATE INDEX语句创建索引 基本语法格式如下： CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX 索引名 ON 表名 （字段名,.n） WITH 索引选项,.n ON 文件组 其中各参数的含义如下。 （1） UNIQUE：建立惟一索引。 （2） CLUSTERED：建立聚集索引。 （3） NONCLUSTERED：建立非聚集索引。 （4） 索引选项。,DR
11、OP_EXISTING：先删除存在的同名索引，再创建新的索引（如果不存在同名索引，系统会给出错误信息）。 IGNORE_DUP_KEY：当向包含于一个唯一聚集索引中的列插入重复数据时，用于控制SQL Server所做的反应。如果为索引指定了IGNORE_DUP_KEY选项，并且执行了创建重复键的INSERT语句，SQL Server将发出警告信息，并跳过此行数据的插入，继续执行下面的插入数据的操作，如果没有为索引指定IGNORE_DUP_KEY选项，SQL Server会发出一条警告信息，并回滚整个INSERT语句。,FILLFACTOR=填充因子：指定在SQL Server创建索引的过程中，
12、各索引页叶级的填满程度。用户指定的FILLFACTOR值可以从1到100，如果没有指定，默认值为0。 【例5.1】在“学生图书管理系统”中“图书信息”表的“作者”列上创建一个唯一的非聚集索引“IX_作者”，创建代码如下： CREATE UNIQUE NONCLUSTERED INDEX IX_作者ON 图书信息（作者）,【例5.2】在“学生图书管理系统”中“图书信息”表的“作者、出版社名称”列上创建一个非聚集索引“IX_作者出版社”，创建代码如下： CREATE NONCLUSTERED INDEX IX_作者出版社ON 图书信息（作者,出版社名称） 【例5.3】在“学生图书管理系统”中“图书
13、信息”表的“作者”列上创建一个非唯一的非聚集索引“IX_作者”，创建代码如下： CREATE NONCLUSTERED INDEX IX_作者ON 图书信息（作者） with drop_existing,在例5.1中已经创建了一个“IX_作者”，所以此代码中with drop_existing作用是将已经存在的“IX_作者”删除，然后再创建一个新的“IX_作者”。如果原来已经存在的索引中没有“IX_作者”这个索引，那么此代码将出现“未能找到任何名为IX_作者 的索引（属于表图书信息）”的运行结果。 查看表的索引信息可以使用系统存储过程sp_helpindex，例如查看“图书信息”表上的索引信息
14、代码如下： Exec sp_helpindex 图书信息,5.3 删除索引 当某个索引不再需要的时候，可以将其从数据库中删除，以回收磁盘空间。如果是创建主键约束或唯一性约束而生成的聚集索引或唯一性索引，必须删除主键约束或唯一性约束，才能删除约束使用的索引。 在SQL Server Management Studio的“对象资源管理器”中依次展开节点，选中要删除的索引，在右键快捷菜单中选择“删除”，在弹出的“删除对象”窗口中单击“确定”按钮即可。,用DROP INDEX 命令删除索引的语法格式如下： DROP INDEX 表名.索引名,. 【例5.4】 用DROP INDEX命令删除“图书信息”
15、表上的“IX_作者”，代码如下： drop index 图书信息.IX_作者,5.4 实训 创建和维护索引 5.4.1 实训目的 1能够使用SQL Server Management Studio创建索引 2掌握CREATE INDEX创建索引语句 3学会查看、修改、删除所创建的索引,5.4.2 实训内容 在第3、4章创建的“学生成绩管理系统”中创建以下索引： 1使用SQL Server Management Studio在“学生表”的“姓名”列上，创建一个名为“IX_姓名”的非唯一性非聚集索引。 2使用CREATE INDEX语句在“学生表”的“姓名、专业”组合列上创建一个名为“IX_姓名专业”的唯一性非聚集索引。 3在“课程表”的“课程名”列上创建一个聚集索引，查看运行结果，看是否能在一个表上创建两个聚集索引。 4删除以上创建的索引。,本章知识框架,习 题 1. 索引的作用是什么？ 2. 索引的主要类型有哪几种？各有什么特点？ 3. 怎样查看索引的信息？ 4. 有几种删除索引的方法？分别是什么？,
