数据库应用技术索引及其应用

《数据库应用技术索引及其应用》由会员分享，可在线阅读，更多相关《数据库应用技术索引及其应用（22页珍藏版）》请在装配图网上搜索。

1、第七章第七章 索引及其应用索引及其应用 索引是一种特殊类型的数据库对象，它保存着数据表中一列或几索引是一种特殊类型的数据库对象，它保存着数据表中一列或几列组合的排序结构。为数据表增加索引，可以大大提高数据的检索效列组合的排序结构。为数据表增加索引，可以大大提高数据的检索效率。索引是数据库中一个重要的对象，本章将详细介绍索引的基本概率。索引是数据库中一个重要的对象，本章将详细介绍索引的基本概念、使用索引的意义、创建索引的方法以及对索引的操作。念、使用索引的意义、创建索引的方法以及对索引的操作。v 索引的基础知识索引的基础知识v 索引的分类索引的分类v 索引的操作索引的操作v 索引的分析与维护索引

2、的分析与维护第七章第七章 索引及其应用索引及其应用v 索引的基础知识索引的基础知识 1. 数据存储数据存储 SQLServer 2000 中，数据存储的基本单位是页，其大小是中，数据存储的基本单位是页，其大小是8KB . SQL Server 2000 数据库的数据文件中包含八种页类型数据库的数据文件中包含八种页类型 :数据、索引、数据、索引、文本文本/图像、全局分配映射表与辅助全局分配映射表、页的可用空间、图像、全局分配映射表与辅助全局分配映射表、页的可用空间、索引分配映射表、大容量更改映射表、差异更改映射表。索引分配映射表、大容量更改映射表、差异更改映射表。2 .索引索引 1）索引的概念）

3、索引的概念 索引是以表列为基础建立的数据库对象，它保存着表中排序的索索引是以表列为基础建立的数据库对象，它保存着表中排序的索引列，并且记录了索引列在数据表中的物理存储位置，实现了表中数引列，并且记录了索引列在数据表中的物理存储位置，实现了表中数据的逻辑排序。据的逻辑排序。 2）使用索引的意义）使用索引的意义 索引在数据库中的作用与目录在书籍中的作用类似，都用来提高索引在数据库中的作用与目录在书籍中的作用类似，都用来提高查找信息的速度。查找信息的速度。 第七章第七章 索引及其应用索引及其应用v 索引的基础知识索引的基础知识3）使用索引的代价）使用索引的代价 为每一列都建立索引是不明智的，因为使用

4、索引要付出一定的代价：为每一列都建立索引是不明智的，因为使用索引要付出一定的代价： 索引需要占用数据表以外的物理存储空间。索引需要占用数据表以外的物理存储空间。 创建索引和维护索引要花费一定的时间。创建索引和维护索引要花费一定的时间。 当对表进行更新操作时，索引需要被重建，这样降低了数据的维当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。护速度。4）建立索引的原则）建立索引的原则 认真考虑哪些列应该建索引，哪些列不该建索引。一般原则是：认真考虑哪些列应该建索引，哪些列不该建索引。一般原则是： 主键列上一定要建立索引。主键列上一定要建立索引。 外键列可以建索引。外键列可以建索引。

5、 在经常查询的字段上最好建立索引。在经常查询的字段上最好建立索引。 对于那些查询中很少涉及的列、重复值比较多的列不要建索引对于那些查询中很少涉及的列、重复值比较多的列不要建索引 对于定义为对于定义为text，Image和和Bit数据类型的列上不要建立索引。数据类型的列上不要建立索引。第七章第七章 索引及其应用索引及其应用v索引的分类索引的分类根据索引的存储结构不同将其分为两类：根据索引的存储结构不同将其分为两类：聚集索引聚集索引 聚集索引是指表中数据行的物理存储顺序与索引顺序完全相同。聚集索引是指表中数据行的物理存储顺序与索引顺序完全相同。聚集索引由上下两层组成，上层为索引页，包含表中的索引页

6、面，用聚集索引由上下两层组成，上层为索引页，包含表中的索引页面，用于数据检索，下层为数据页。于数据检索，下层为数据页。非聚集索引非聚集索引 非聚集索引不改变表中数据行的物理存储位置，数据与索引分开非聚集索引不改变表中数据行的物理存储位置，数据与索引分开存储，通过索引带有的指针与表中的数据发生联系。存储，通过索引带有的指针与表中的数据发生联系。第七章第七章 索引及其应用索引及其应用v索引的分类索引的分类索引页数据页根结点第七章第七章 索引及其应用索引及其应用v索引的分类索引的分类系部代码指针地址018022035044051067076083系部代码系部名称系主任05社会科学部刘克忠02经济管理

7、系刘国峰08建筑系王未起04基础科学部王彬03传播技术系田建国07农林系陈瑞修06机电工程系王伟东01计算机系刘海军第七章第七章 索引及其应用索引及其应用v索引的操作索引的操作(创建、查询索引、更名、删除索引、设置选项创建、查询索引、更名、删除索引、设置选项) 创建索引创建索引-利用企业管理器直接创建索引。其具体步骤如下：利用企业管理器直接创建索引。其具体步骤如下： 1）在企业管理器中，选择要创建索引的表（如：教师表），右击该）在企业管理器中，选择要创建索引的表（如：教师表），右击该表。在弹出的菜单中选择表。在弹出的菜单中选择“所有任务所有任务”下的下的“管理索引管理索引”命令，打开命令，打开

8、“管理索引管理索引”对话框。在该对话框中显示了当前表中已有的索引，包对话框。在该对话框中显示了当前表中已有的索引，包含其名称、是不是聚集索引和索引字段的名称。含其名称、是不是聚集索引和索引字段的名称。 2）单击）单击“新建新建”按钮，打开按钮，打开“新建索引新建索引”对话框。设定索引的各属对话框。设定索引的各属性。性。 3）完成后，单击）完成后，单击“确定确定”按钮，即可生成新的索引。按钮，即可生成新的索引。 第七章第七章 索引及其应用索引及其应用v索引的操作索引的操作(创建、查询索引、更名、删除索引、设置选项创建、查询索引、更名、删除索引、设置选项) 创建索引创建索引-利用企业管理器中的索引

9、向导创建索引。利用企业管理器中的索引向导创建索引。 1）在企业管理器中，从）在企业管理器中，从“工具工具”菜单中选择菜单中选择“向导向导”命令，打开命令，打开“选择向导选择向导”对话框。对话框。 2）单击）单击“选择向导选择向导”对话框中对话框中“数据库数据库”左边的加号图标，然后选左边的加号图标，然后选择择“创建索引向导创建索引向导” 。 3）单击）单击“确定确定”按钮，打开按钮，打开“欢迎使用创建索引向导欢迎使用创建索引向导”对话框。对话框。 4）在向导的欢迎对话框中，单击）在向导的欢迎对话框中，单击“下一步下一步”按钮，打开按钮，打开“选择数据选择数据库和表库和表”对话框。对话框。 5）

10、在该对话框中，从数据库名称下拉列表框中选择）在该对话框中，从数据库名称下拉列表框中选择“STUDENT”数据库，从对象名中选择数据库，从对象名中选择“教师任课教师任课”表。表。第七章第七章 索引及其应用索引及其应用v索引的操作索引的操作(创建、查询索引、更名、删除索引、设置选项创建、查询索引、更名、删除索引、设置选项) 创建索引创建索引-利用企业管理器中的索引向导创建索引。利用企业管理器中的索引向导创建索引。 6）选择好数据库和数据表之后，单击）选择好数据库和数据表之后，单击“下一步下一步”按钮，打开按钮，打开“当当前索引信息前索引信息”窗口。在该窗口中，显示了目前已存在的索引。窗口。在该窗口

11、中，显示了目前已存在的索引。 7）单击）单击“下一步下一步”按钮，打开按钮，打开“选择列选择列”对话框。我们从中选择对话框。我们从中选择需要建索引的列，如课程编号、课程号。需要建索引的列，如课程编号、课程号。 8）单击）单击“下一步下一步”按钮，打开按钮，打开“指定索引选项指定索引选项”对话框，在这里对话框，在这里设置索引的属性，可以设置聚集索引或唯一索引，还可设置填充因子设置索引的属性，可以设置聚集索引或唯一索引，还可设置填充因子。 9）指定索引选项后，单击）指定索引选项后，单击“下一步下一步”按钮，出现按钮，出现“创建完成创建完成”对对话框。在其话框。在其“名称名称”文本框中为新建索引指定

12、一个名称，然后单击文本框中为新建索引指定一个名称，然后单击“完成完成”按钮。出现消息提示按钮。出现消息提示“创建索引成功创建索引成功”时，单击时，单击“确定确定”按钮按钮，完成索引创建。，完成索引创建。第七章第七章 索引及其应用索引及其应用v索引的操作索引的操作(创建、查询索引、更名、删除索引、设置选项创建、查询索引、更名、删除索引、设置选项) 创建索引创建索引-利用查询分析器创建索引。利用查询分析器创建索引。其语法格式如下：其语法格式如下：CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX 索引名索引名 ON 表名表名 | 视图名视图名 ( 列名列名 A

13、SC | DESC ,.n ) WITH PAD_INDEX ,FILLFACTOR =填充因子填充因子 ,IGNORE_DUP_KEY ,DROP_EXISTING ,STATISTICS_NORECOMPUTE ,SORT_IN_TEMPDBON filegroup第七章第七章 索引及其应用索引及其应用v索引的操作索引的操作(创建、查询索引、更名、删除索引、设置选项创建、查询索引、更名、删除索引、设置选项)语法注释：语法注释：UNIQUE CLUSTERED | NONCLUSTERED：指定创建索引的类型，其依次为指定创建索引的类型，其依次为唯一索引、聚集索引和非聚集索引。唯一索引、聚集

14、索引和非聚集索引。ASC | DESC：用来指定索引列的排序方式，用来指定索引列的排序方式，ASC是升序，是升序，DESC是降序。是降序。 PAD_INDEX：用来指定索引中间级中每个页（节点）上保持开放的空间。用来指定索引中间级中每个页（节点）上保持开放的空间。 FILLFACTOR （填充因子）：指定在填充因子）：指定在SQL Server创建索引的过程中，各索引页叶创建索引的过程中，各索引页叶级的填满程度。级的填满程度。 IGNORE_DUP_KEY：该选项控制当尝试向属于唯一聚集索引的列插入重复的键值该选项控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。时所发生的情况。

15、DROP_EXISTING：用来指定应除去并重建已命名的先前存在的聚集索引或非聚集用来指定应除去并重建已命名的先前存在的聚集索引或非聚集索引。索引。 STATISTICS_NORECOMPUTE：用来指定过期的索引统计不会自动重新计算用来指定过期的索引统计不会自动重新计算 。 SORT_IN_TEMPDB：指定用于生成索引的中间排序结果将存储在指定用于生成索引的中间排序结果将存储在 tempdb 数据库数据库中。中。 ON filegroup：用来在给定的用来在给定的 filegroup 上创建指定的索引。上创建指定的索引。 第七章第七章 索引及其应用索引及其应用v索引的操作索引的操作(创建、

16、查询索引、更名、删除索引、设置选项创建、查询索引、更名、删除索引、设置选项)例：例：USE STUDENT GOCREATE INDEX bj_xb_index ON 班级(系部代码)GO第七章第七章 索引及其应用索引及其应用v索引的操作索引的操作(创建、查询索引、更名、删除索引、设置选项创建、查询索引、更名、删除索引、设置选项)查询索引信息查询索引信息使用企业管理器查询索引信息使用企业管理器查询索引信息 1）在企业管理器中，选择需要的表，右击该表。在弹出的菜单中选择）在企业管理器中，选择需要的表，右击该表。在弹出的菜单中选择“所有任务所有任务”下的下的“管理索引管理索引”命令，打开命令，打开

17、“管理索引管理索引”对话框。对话框。 2）在该对话框的）在该对话框的“现有索引现有索引”列表中列出了当前表中有多少索引以及列表中列出了当前表中有多少索引以及每个索引的名称、类型和关键值。每个索引的名称、类型和关键值。 3）如果需要了解某个索引更加详细的信息，选择该索引，单击）如果需要了解某个索引更加详细的信息，选择该索引，单击“编辑编辑”按钮，在弹出的按钮，在弹出的“编辑现有索引编辑现有索引”对话框中，可以看到当前索引的对话框中，可以看到当前索引的详细信息。详细信息。 查询索引信息查询索引信息使用使用SQL命令查询索引信息命令查询索引信息其语法格式如下：其语法格式如下： EXEC sp_hel

18、pindex 表名表名 EXEC sp_helpindex 教师 GO 第七章第七章 索引及其应用索引及其应用v索引的操作索引的操作(创建、查询索引、更名、删除索引、设置选项创建、查询索引、更名、删除索引、设置选项)索引更名索引更名其语法格式如下：其语法格式如下： sp_rename objname = object_name , newname = new_name , objtype = object_type 例：将例：将STUDENT数据库中数据库中“课程课程”表的表的pk_kc索引名称更改为索引名称更改为pk_kecheng。其程序清单如下：其程序清单如下： USE STUDENT

19、GO EXEC sp_rename pk_kc , pk_kecheng GO第七章第七章 索引及其应用索引及其应用v索引的操作索引的操作(创建、查询索引、更名、删除索引、设置选项创建、查询索引、更名、删除索引、设置选项)删除索引删除索引-使用企业管理器删除索引使用企业管理器删除索引 1）在企业管理器中，选择要删除索引的表，右击该表。在弹出的菜单中）在企业管理器中，选择要删除索引的表，右击该表。在弹出的菜单中选择选择“所有任务所有任务”下的下的“管理索引管理索引”命令，打开命令，打开“管理索引管理索引”对话框。对话框。 2）在该对话框的）在该对话框的“现有索引现有索引”列表中选择需要删除的索引

20、，单击列表中选择需要删除的索引，单击“删除删除”按钮后，出现一个消息框，让用户确认是否删除索引，单击按钮后，出现一个消息框，让用户确认是否删除索引，单击“是是”按钮按钮，删除索引。，删除索引。 删除索引删除索引-使用使用DROP INDEX 语句删除索引语句删除索引 其语法格式如下：其语法格式如下： DROP INDEX 表名表名.索引名索引名,n 例：删除例：删除STUDENT数据库中数据库中“班级班级”表的表的bj_xb_index索引，程序如下索引，程序如下： USE STUDENT GO DROP INDEX 班级班级.bj_xb_index GO 第七章第七章 索引及其应用索引及其应

21、用v设置索引的选项设置索引的选项-设置设置FILLFACTOR选项选项 为了尽量减少页拆分，在创建索引时，可以选择为了尽量减少页拆分，在创建索引时，可以选择FILLFACTOR（称（称为填充因子）选项，此选项用来指定各索引页叶级的填满程度，这样在为填充因子）选项，此选项用来指定各索引页叶级的填满程度，这样在索引页上就可以留出额外的间隙和保留一定百分比的空间，供将来表的索引页上就可以留出额外的间隙和保留一定百分比的空间，供将来表的数据存储容量进行扩充和减少页拆分。数据存储容量进行扩充和减少页拆分。 例：为例：为STUDENT数据库中数据库中“班级班级”表创建基于表创建基于“系部代码系部代码”列的

22、非聚列的非聚集索引集索引bj_xb_index，其填充因子值为，其填充因子值为60，其程序清单如下：，其程序清单如下： USE STUDENT GO CREATE INDEX bj_xb_index ON 班级班级(系部代码系部代码) WITH FILLFACTOR=60 GO第七章第七章 索引及其应用索引及其应用v设置索引的选项设置索引的选项-设置设置PAD_INDEX选项选项 对于非叶级索引页需要使用对于非叶级索引页需要使用PAD_INDEX选项设置其预留空间的大选项设置其预留空间的大小。小。PAD_INDEX 选项只有在指定了选项只有在指定了 FILLFACTOR 时才有用，因为时才有用

23、，因为 PAD_INDEX 使用由使用由 FILLFACTOR 所指定的百分比。所指定的百分比。例：为例：为STUDENT数据库中数据库中“班级班级”表创建基于表创建基于“系部代码系部代码”列的非聚列的非聚集索引集索引bj_xb_index，其，其FILLFACTOR和和PAD_INDEX选项值均为选项值均为60，其，其程序清单如下：程序清单如下： USE STUDENT GO CREATE INDEX bj_xb_index ON 班级班级(系部代码系部代码) WITH PAD_INDEX，FILLFACTOR=60 GO第七章第七章 索引及其应用索引及其应用v 索引的分析索引的分析1、 S

24、HOWPLAN SHOWPLAN语句用来显示查询语句的执行信息，包含查询过程语句用来显示查询语句的执行信息，包含查询过程中连接表时所采取的每个步骤以及选择哪个索引。其语法格式为：中连接表时所采取的每个步骤以及选择哪个索引。其语法格式为： SET SHOWPLAN_ALL ON | OFF SET SHOWPLAN_TEXT ON | OFF 其中：其中： ON为显示查询执行信息为显示查询执行信息 OFF为不显示查询执行信息（系统默认）为不显示查询执行信息（系统默认）第七章第七章 索引及其应用索引及其应用v 索引的分析索引的分析例：在例：在STUDENT库中的库中的“学生学生”表上查询所有男生的

25、姓名和年龄，并表上查询所有男生的姓名和年龄，并显示查询处理过程。其程序清单如下：显示查询处理过程。其程序清单如下： SET SHOWPLAN_ALL ON GO SELECT 姓名姓名,YEAR(GETDATE()-YEAR(出生日期出生日期) AS 年龄年龄 FROM 学生学生 WHERE 性别性别=男男 GO第七章第七章 索引及其应用索引及其应用v 索引的分析索引的分析2、 STATISTICS IO STATISTICS IO语句用来显示执行数据检索语句所花费的磁盘活语句用来显示执行数据检索语句所花费的磁盘活动量信息，可以利用这些信息来确定是否重新设计索引。其语法格式动量信息，可以利用这

26、些信息来确定是否重新设计索引。其语法格式为：为： SET STATISTICS IO ON|OFF其中：其中： ON为显示信息为显示信息 OFF为不显示信息（系统默认）为不显示信息（系统默认）第七章第七章 索引及其应用索引及其应用v 索引的维护索引的维护1、使用、使用DBCC SHOWCONTIG语句语句该语句用来显示指定表的数据和索引的碎片信息。当对表进行大量的该语句用来显示指定表的数据和索引的碎片信息。当对表进行大量的修改或添加数据之后，应该执行此语句来查看有无碎片。其语法格式修改或添加数据之后，应该执行此语句来查看有无碎片。其语法格式如下：如下： DBCC SHOWCONTIG tabl

27、e_name | table_id | view_name | view_id , index_name | index_id ) 其中：其中：table_name | table_id | view_name | view_id：是要对其碎片信息进行：是要对其碎片信息进行检查的表或视图。如果未指定任何名称，则对当前数据库中的所有表检查的表或视图。如果未指定任何名称，则对当前数据库中的所有表和索引视图进行检查。和索引视图进行检查。当执行此语句时，我们重点看其扫描密度，其理想值为当执行此语句时，我们重点看其扫描密度，其理想值为100%，如果，如果小于这个值，表示表上已有碎片。如果表中有索引碎片，

28、可以使用小于这个值，表示表上已有碎片。如果表中有索引碎片，可以使用 DBCC INDEXDEFRAG命令对碎片进行整理。命令对碎片进行整理。 第七章第七章 索引及其应用索引及其应用v 索引的维护索引的维护2、使用、使用DBCC INDEXDBRE语句语句该语句的作用是整理表中索引碎片，其语法格式为：该语句的作用是整理表中索引碎片，其语法格式为：DBCC INDEXDEFRAG ( database_name | database_id | 0 , table_name | table_id | view_name | view_id , index_name | index_id ) 其中：其中：database_name | database_id | 0：指对其索引进行碎片整理的数据库：指对其索引进行碎片整理的数据库。数据库名称必须符合标识符的规则。如果指定。数据库名称必须符合标识符的规则。如果指定 0，则使用当前数据，则使用当前数据库。库。table_name | table_id | view_name | view_id：指对其索引进行碎片整：指对其索引进行碎片整理的表或视图。理的表或视图。index_name | index_id：需要进行碎片整理的索引名称。：需要进行碎片整理的索引名称。