sql_server_2005索引及应用上课

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

2、 索引的基础知识索引的基础知识 10.2 索引的分类索引的分类 10.3 索引的操作索引的操作 10.4设置索引选项设置索引选项10.5索引的分析与维护索引的分析与维护 10.6索引视图索引视图10.7 案例中的索引案例中的索引 51）索引的概念）索引的概念 索引是针对一个表，以表列为基础建立的数据库对象索引是针对一个表，以表列为基础建立的数据库对象，它保存着表中排序的索引列，并且记录了索引列在数据，它保存着表中排序的索引列，并且记录了索引列在数据表中的物理存储位置，实现了表中数据的逻辑排序。表中的物理存储位置，实现了表中数据的逻辑排序。 10.1索引的基础知识索引的基础知识62）使用索引的意

3、义）使用索引的意义 v 可以大大加快数据检索速度。可以大大加快数据检索速度。v 通过创建唯一索引，可以通过创建唯一索引，可以保证数据记录的唯一性。保证数据记录的唯一性。v 在使用在使用ORDER BY和和GROUP BY子句进行检索数据时，子句进行检索数据时，可以显著可以显著减少查询中分组和排序的时间减少查询中分组和排序的时间。v 使用索引可以在检索数据的过程中使用优化隐藏器，使用索引可以在检索数据的过程中使用优化隐藏器，提提高系统性能高系统性能。v 可以加速表与表之间的连接可以加速表与表之间的连接，这一点在实现数据的参照，这一点在实现数据的参照完整性方面有特别的意义。完整性方面有特别的意义。

4、73）使用索引的代价）使用索引的代价 创建索引要花费时间和占用存储空间。创建索引要花费时间和占用存储空间。 创建索引需要占用存储空间，如创建聚集索引需创建索引需要占用存储空间，如创建聚集索引需要占用的存储空间是数据库表占用空间的要占用的存储空间是数据库表占用空间的1.2倍。倍。 创建索引也需要花费时间。在建立索引时，数据创建索引也需要花费时间。在建立索引时，数据被复制以便建立聚集索引，索引建立后，再将旧的未被复制以便建立聚集索引，索引建立后，再将旧的未加索引的表数据删除。加索引的表数据删除。10.1索引的基础知识索引的基础知识8v 建立索引加快了数据检索速度，却减慢了数据修改建立索引加快了数据

5、检索速度，却减慢了数据修改速度。速度。 因为每当执行一次数据的插入、删除和更新操作，因为每当执行一次数据的插入、删除和更新操作，就要维护索引。修改的数据越多，涉及维护索引的开销就要维护索引。修改的数据越多，涉及维护索引的开销也就越大。如果将一些数据行插入到一个已经放满行的也就越大。如果将一些数据行插入到一个已经放满行的数据页面上，还必须将这个数据页面中最后一些数据移数据页面上，还必须将这个数据页面中最后一些数据移到下一个页面中去，这样，还必须改变索引页中的内容，到下一个页面中去，这样，还必须改变索引页中的内容，以保持数据顺序的正确性。这就是对索引的维护。由于以保持数据顺序的正确性。这就是对索引

6、的维护。由于修改数据时要动态维护其索引，所以，对建立了索引的修改数据时要动态维护其索引，所以，对建立了索引的表执行修改操作要比未建立索引的表执行修改操作所花表执行修改操作要比未建立索引的表执行修改操作所花的时间要长。的时间要长。910.1索引的基础知识索引的基础知识4）建立索引的原则）建立索引的原则 l 考虑建索引的列考虑建索引的列 如果在一个列上创建索引，该列就称为索引列。索引列中的值称如果在一个列上创建索引，该列就称为索引列。索引列中的值称为关键字值。考虑建索引的列有如下这些：为关键字值。考虑建索引的列有如下这些： 主键主键 通常，检索、存取表是通过主键来进行的。因此，应该考虑通常，检索、

7、存取表是通过主键来进行的。因此，应该考虑在主键上建立索引。在主键上建立索引。 连接中频繁使用的列连接中频繁使用的列 用于连接的列若按顺序存放，系统可以很快地执行连接。如用于连接的列若按顺序存放，系统可以很快地执行连接。如外键，除用于实现参照完整性外，还经常用于进行表的连接。外键，除用于实现参照完整性外，还经常用于进行表的连接。 对经常用于连接的字段建立索引。对经常用于连接的字段建立索引。1010.1索引的基础知识索引的基础知识l 2. 不考虑建索引的列不考虑建索引的列 建立索引需要产生一定的存储开销，在进行插入和更新数据的操建立索引需要产生一定的存储开销，在进行插入和更新数据的操作时，维护索引

8、也要花费时间和空间，因此，没有必要对表中的所有作时，维护索引也要花费时间和空间，因此，没有必要对表中的所有列都建立索引。创建索引与否以及在哪些列上建立索引，要看建立索列都建立索引。创建索引与否以及在哪些列上建立索引，要看建立索引和维护索引的代价与因建立索引所节省的时间相比哪个更合算。一引和维护索引的代价与因建立索引所节省的时间相比哪个更合算。一般来说，如下一些列不考虑建立索引：般来说，如下一些列不考虑建立索引： 很少或从来不在查询中引用的列，因为系统很少或从来不根据这很少或从来不在查询中引用的列，因为系统很少或从来不根据这个列的值去查找数据行。个列的值去查找数据行。 只有两个或很少几个值的列（

9、如性别，只有两个值只有两个或很少几个值的列（如性别，只有两个值“男男”或或“女女”），以这样的列创建索引并不能得到建立索引的好处。），以这样的列创建索引并不能得到建立索引的好处。 以以bit、text、image数据类型定义的列。数据类型定义的列。 数据行数很少的小表一般也没有必要创建索引。数据行数很少的小表一般也没有必要创建索引。11 根据索引的顺序与数据表的物理顺序是否相同，可以根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型：聚集索引与非聚集索引。把索引分成两种类型：聚集索引与非聚集索引。聚集索引（聚集索引（Clustered Index） 数据表的物理顺序和索引表的顺序

10、相同，它根据表中数据表的物理顺序和索引表的顺序相同，它根据表中的一列或多列值的组合排列记录。的一列或多列值的组合排列记录。非聚集索引（非聚集索引（Nonclustered Index） 数据表的物理顺序和索引表的顺序不相同数据表的物理顺序和索引表的顺序不相同,索引表仅仅索引表仅仅包含指向数据表的指针，这些指针本身是有序的，用于在包含指向数据表的指针，这些指针本身是有序的，用于在表中快速定位数据。表中快速定位数据。10.2索引的分类索引的分类12聚集索引聚集索引 数据表的物理顺序和索引表的顺序相同，它根据表中数据表的物理顺序和索引表的顺序相同，它根据表中的一列或多列的值排列记录。每一个表只能有一

11、个聚集索的一列或多列的值排列记录。每一个表只能有一个聚集索引，因为一个表的记录只能以一种物理顺序存放，在通常引，因为一个表的记录只能以一种物理顺序存放，在通常情况下，使用的都是聚集索引。情况下，使用的都是聚集索引。 聚集索引有利于范围搜索，由于聚集索引的顺序与数聚集索引有利于范围搜索，由于聚集索引的顺序与数据行存放的物理顺序相同，因此，聚集索引最适合于范围据行存放的物理顺序相同，因此，聚集索引最适合于范围搜索，因为相邻的行将被物理地存放在相同或相邻近的页搜索，因为相邻的行将被物理地存放在相同或相邻近的页面上。面上。10.2索引的分类索引的分类13创建聚集索引的几个注意事项：创建聚集索引的几个注

12、意事项：l 每张表只能有一个聚集索引每张表只能有一个聚集索引l 由于聚集索引改变表的物理顺序，所以应先建聚集由于聚集索引改变表的物理顺序，所以应先建聚集索引，后创建非聚集索引索引，后创建非聚集索引l 创建索引所需的空间来自用户数据库，而不是创建索引所需的空间来自用户数据库，而不是TEMPDB数据库数据库l 主键是聚集索引的良好候选者主键是聚集索引的良好候选者14索引页索引页数据页数据页根结点根结点10.2索引的分类索引的分类15聚集索引的结构示意图聚集索引的结构示意图 16在聚集索引上查找数据行在聚集索引上查找数据行Clustered IndexPage 140 - RootPage 100

13、Page 120 Page 130 Page 141Page 145AkhtarBarrConFunkFunk.23345678253413341534.MartinMartinOtaPhuaRudd.12347778587878786078.SmithSmithSmithWhiteWhite.14345778797822341634.AkhtarGanioAkhtarMartinMartinSmithPage 110 GanioHallJonesJonesJones.76788078243459782634.SELECT lastname, firstnameFROM memberWHERE

14、 lastname = OtaClustered IndexPage 140 - RootPage 100 Page 120 Page 130 Page 141Page 145AkhtarBarrConFunkFunk.23345678253413341534.MartinMartinOtaPhuaRudd.12347778587878786078.SmithSmithSmithWhiteWhite.14345778797822341634.AkhtarGanioAkhtarMartinMartinSmithPage 110 GanioHallJonesJonesJones.767880782

15、43459782634.idindid = 1rootsysindexes17非聚集索引的结构示意图非聚集索引的结构示意图 18在非聚集索引上查找数据行在非聚集索引上查找数据行 Non-LeafLevelPage 12 - RootPage 37Page 28Leaf Level(Key Value)Page 41Page 51 Page 61 Page 71 Akhtar.MartinAkhtarBarrConFunkFunk4:706:014:705:034:704:014:706:024:704:02MartinSmith.SmithSmithSmithWhiteWhite4:706:0

16、34:708:044:707:014:704:034:705:02AkhtarGanio.GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03HeapPage 707 Page 808Page 70901020304.AkhtarFunkSmithMatey.Page 704 Page 705 Page 706 010203.ConnFunkWhite.010203.RuddWhiteBarr.010203.SmithOtaJones.01020304.MartinPhuaJonesSmith.010203.Ganio

17、JonesHall.MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01NonClusteredIndexFile ID #4idindid = 2rootsysindexesSELECT lastname, firstnameFROM memberWHERE lastnameBETWEEN Masters AND Rudd Non-LeafLevelPage 12 - RootPage 37Page 28Leaf Level(Key Value)Page 41Page 51 Page 61 Page 71 Akhtar.

18、MartinAkhtarBarrConFunkFunk4:706:014:705:034:704:014:706:024:704:02MartinSmith.SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02AkhtarGanio.GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03HeapPage 707 Page 808Page 70901020304.AkhtarFunkSmithMatey.Page 704 Page 705 Pag

19、e 706 010203.ConnFunkWhite.010203.RuddWhiteBarr.010203.SmithOtaJones.01020304.MartinPhuaJonesSmith.010203.GanioJonesHall.MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01NonclusteredIndexFile ID #419非聚集索引非聚集索引 对于非聚集索引，表的物理顺序与索引顺序不同，对于非聚集索引，表的物理顺序与索引顺序不同，即表的数据并不是按照索引列排序的。即表的数据并不是按照索引列排序的

20、。 索引是有序的，而表中的数据是无序的。一个表索引是有序的，而表中的数据是无序的。一个表可以同时存在聚集索引和非聚集索引，而且，一个表可以同时存在聚集索引和非聚集索引，而且，一个表可以有多个非聚集索引。例如对记录网站活动的日志可以有多个非聚集索引。例如对记录网站活动的日志表可以建立一个对日期时间的聚集索引和多个对用户表可以建立一个对日期时间的聚集索引和多个对用户名的非聚集索引。名的非聚集索引。10.2索引的分类索引的分类20创建非聚集索引的几个注意事项：创建非聚集索引的几个注意事项：l 创建非聚集索引实际上是创建了一个表的逻辑顺序的对创建非聚集索引实际上是创建了一个表的逻辑顺序的对象象l 索引

21、包含指向数据页上的行的指针索引包含指向数据页上的行的指针l 一张表可创建多达一张表可创建多达249个非聚集索引个非聚集索引l 创建索引时，缺省为非聚集索引创建索引时，缺省为非聚集索引21v索引的分类索引的分类系部代码系部代码指针地址指针地址018022035044051067076083系部代码系部代码系部名称系部名称系主任系主任05社会科学部刘克忠02经济管理系刘国峰08建筑系王未起04基础科学部王彬03传播技术系田建国07农林系陈瑞修06机电工程系王伟东01计算机系刘海军10.2 索引的分类索引的分类2210.3创建索引创建索引使用使用 SQL Server Management Stud

22、io创建索引创建索引 使用使用Microsoft SQL Server Management Studio创建索引创建索引的步骤如下。的步骤如下。（1）启动）启动SQL Server Management Studio工具，在工具，在“对象对象资源管理器资源管理器”中，展开中，展开Database Engine实例。实例。（2）展开要在其上创建索引的表，在）展开要在其上创建索引的表，在“Indexes”项上右击，项上右击，在快捷菜单中选择在快捷菜单中选择“New Index”命令。命令。23（3）弹出）弹出“New Index”对话框。对话框。 在在“Index name”文本框中，输入索引的

23、名称。文本框中，输入索引的名称。“Index type”列表框用于设置索引类型，共有列表框用于设置索引类型，共有Clustered、Nonclustered、Primary XML 3项。项。（4）单击）单击“Add”按钮，弹出如图按钮，弹出如图10-4所示的对话框，选所示的对话框，选择要在其上创建索引的列。择要在其上创建索引的列。（5）单击）单击“OK”按钮，回到按钮，回到“New Index”对话框，其中，对话框，其中，“Sort Order”列用于设置索引的排列顺序。列用于设置索引的排列顺序。（6）单击）单击“OK”按钮，即完成了索引的创建过程。按钮，即完成了索引的创建过程。24u使用使

24、用T-SQL语句创建索引语句创建索引使用使用SQL Server Management Studio创建索引在一个创建索引在一个表的一个或多个列上创建索引时，应考虑以下几点：表的一个或多个列上创建索引时，应考虑以下几点： 当在一个表上创建当在一个表上创建PRIMARY KEY约束或约束或UNIQUE约束时，约束时，SQL Server自动创建唯一性索引。不能在已自动创建唯一性索引。不能在已经创建经创建PRIMARY KEY约束或约束或UNIQUE约束的列上创约束的列上创建索引。定义建索引。定义PRIMARY KEY 约束或约束或UNIQUE约束与约束与创建标准索引相比应是首选的方法。创建标准索

25、引相比应是首选的方法。 必须是表的拥有者才能创建索引。必须是表的拥有者才能创建索引。 在一个列上创建索引之前，确定该列是否已经存在在一个列上创建索引之前，确定该列是否已经存在索引。索引。10.3索引的操作索引的操作252. 创建索引的创建索引的SQL语句语法语句语法创建索引使用的是创建索引使用的是CREATE INDEX语句。语句。 CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_nameON table_name（column_name ASC | DESC ,.n ）WITHPAD_INDEX， FILLFACTOR = fillfac

26、tor， DROP_EXISTING10.3索引的操作索引的操作26l UNIQUE：指定创建的索引是唯一索引。如果不使用这个关键：指定创建的索引是唯一索引。如果不使用这个关键字，创建的索引就不是唯一索引。字，创建的索引就不是唯一索引。l CLUSTERED|NONCLUSTERED：指定被创建索引的类型。：指定被创建索引的类型。lindex_name：为新创建的索引指定的名字。：为新创建的索引指定的名字。ltable_name：创建索引的基表的名字。：创建索引的基表的名字。lcolumn_name：索引中包含的列的名字。：索引中包含的列的名字。lASC|DESC：确定某个具体的索引列是升序还

27、是降序排序。：确定某个具体的索引列是升序还是降序排序。lPAD_INDEX和和FILLFACTOR：填充因子，它指定：填充因子，它指定 SQL Server 创建索引的过程中，各索引页的填满程度。创建索引的过程中，各索引页的填满程度。lDROP_EXISTING：删除先前存在的、与创建索引同名的聚集：删除先前存在的、与创建索引同名的聚集索引或非聚集索引。索引或非聚集索引。10.3索引的操作索引的操作27l FILLFACTOR FILLFACTOR的作用是，当系统新建或重建索引时，在每一个索的作用是，当系统新建或重建索引时，在每一个索引页上预先留出一部分空间，使得系统在新增索引信息时能够保持索

28、引页上预先留出一部分空间，使得系统在新增索引信息时能够保持索引内容在索引页上尽量连续。它使得索引的页分裂度最小，并可以对引内容在索引页上尽量连续。它使得索引的页分裂度最小，并可以对性能微调。性能微调。 l PAD_INDEX Fillfactor只能指定叶级索引页的数据充满度。只能指定叶级索引页的数据充满度。PAD_INDEX指定指定索引非叶级中每个索引页上保持开放的空间，即非叶级的索引页的数索引非叶级中每个索引页上保持开放的空间，即非叶级的索引页的数据充满度。据充满度。PAD_INDEX 必须和必须和Fillfactor一起使用，而且一起使用，而且Fillfactor的的值决定了值决定了PA

29、D_INDEX指定的充满度。指定的充满度。PAD_INDEX 选项只有在指定选项只有在指定了了 FILLFACTOR 时才有用，因为时才有用，因为 PAD_INDEX 使用由使用由 FILLFACTOR 所指定的百分比。所指定的百分比。10.3索引的操作索引的操作283. 创建唯一索引创建唯一索引 索引按照结构可分为聚集索引和非聚集索引两种不同索引按照结构可分为聚集索引和非聚集索引两种不同的类型。按照实现的功能分，有一类索引被称作的类型。按照实现的功能分，有一类索引被称作“唯一索唯一索引引”。它既可以采用聚集索引的结构，又可以采用非聚集。它既可以采用聚集索引的结构，又可以采用非聚集索引的结构。

30、索引的结构。10.3索引的操作索引的操作29唯一索引的特征：唯一索引的特征：v 不允许两行具有相同的索引值。不允许两行具有相同的索引值。v 可用于实施实体完整性。可用于实施实体完整性。v 在创建主键约束和唯一约束时自动创建唯一索引。在创建主键约束和唯一约束时自动创建唯一索引。 在已有数据的表上创建唯一索引时，如果在该列数据在已有数据的表上创建唯一索引时，如果在该列数据存在重复值，那么系统将返回错误信息。在实际的编程应存在重复值，那么系统将返回错误信息。在实际的编程应用中会经常使用到唯一索引。因为在一个表中，可能会有用中会经常使用到唯一索引。因为在一个表中，可能会有很多列的列值需要保证其唯一性，

31、如：有身份证号、工号、很多列的列值需要保证其唯一性，如：有身份证号、工号、学号等，可在这些列上创建唯一索引。学号等，可在这些列上创建唯一索引。30在在student数据库中，为数据库中，为“课程课程”表创建一个基于表创建一个基于“课课程号程号”的名为的名为khh_index，唯一性聚集索引，升序，填充，唯一性聚集索引，升序，填充因子因子50%。USE studentGOCREATE UNIQUE CLUSTERED INDEX khh_indexON 课程课程(课程号课程号ASC ) WITHFILLFACTOR = 5010.3索引的操作索引的操作314.创建复合索引创建复合索引 有些索引列

32、只有一列，而有些索引列由两列或更多列组有些索引列只有一列，而有些索引列由两列或更多列组成。我们把由两列或更多列组成的索引称作成。我们把由两列或更多列组成的索引称作“复合索引复合索引”。 复合索引的特征复合索引的特征 把两列或更多列指定为索引列。把两列或更多列指定为索引列。 将复合列作为一个整体进行搜索。将复合列作为一个整体进行搜索。 创建复合索引中的列序不一定与表定义列序相同。创建复合索引中的列序不一定与表定义列序相同。10.3索引的操作索引的操作32 在在student数据库中，为数据库中，为“教师任课教师任课”表创建一个基于表创建一个基于“教师编号，课程号教师编号，课程号”组合列的聚集、复

33、合索引组合列的聚集、复合索引jsrk_index,升序，填充因子升序，填充因子50% 。 USE student GO CREATE CLUSTERED INDEX jsrk_index ON 教师任课教师任课 (教师编号教师编号 ASC, 课程号课程号 ASC) WITH FILLFACTOR = 50 GO 33 在在student数据库中，为数据库中，为“教学计划教学计划”表创建一个基表创建一个基于于“课程号，专业代码课程号，专业代码”组合列的惟一、聚集、复合索组合列的惟一、聚集、复合索引，填充因子引，填充因子80% kc_zy_index。代码如下：。代码如下： USE student

34、 GO CREATE UNIQUE CLUSTERED INDEX kc_zy_index ON 教学计划教学计划(课程号课程号,专业代码专业代码) WITH PAD_INDEX,FILLFACTOR=80,IGNORE_DUP_KEY GO 10.3索引的操作索引的操作345.索引更名索引更名其语法格式如下：其语法格式如下： sp_rename objname = object_name , newname = new_name , objtype = object_type 10.3索引的操作索引的操作35例：将例：将STUDENT数据库中数据库中“课程课程”表的表的pk_kc索引名称更索

35、引名称更改为改为pk_kecheng。其程序清单如下：。其程序清单如下： USE STUDENT GO EXEC sp_rename pk_kc , pk_kecheng GO36删除索引删除索引-使用使用DROP INDEX 语句删除索引语句删除索引 其语法格式如下：其语法格式如下： DROP INDEX 表名表名.索引名索引名,n 例：删除例：删除STUDENT数据库中数据库中“班级班级”表的表的bj_xb_index索引索引 USE STUDENT GO DROP INDEX 班级班级.bj_xb_index GO 10.3索引的操作索引的操作3710.4 设置索引的选项设置索引的选项

36、1.设置设置FILLFACTOR选项选项 设置设置FILLFACTOR值时，应考虑如下因素：值时，应考虑如下因素：l 填充因子的值是从填充因子的值是从 0 到到 100 之间的百分比数值，用来指定在创建索引之间的百分比数值，用来指定在创建索引后对数据页的填充比例。后对数据页的填充比例。l 值为值为 100 时表示页将填满，所留出的存储空间量最小。只有当不会对时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。数据进行更改时（例如，在只读表中）才会使用此设置。l 值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中值越小则数据页上的空闲空

37、间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发生更改时，这种设置更为适当。生更改时，这种设置更为适当。l 使用使用sp_configure系统存储过程可以在服务器级别设置默认的填充因系统存储过程可以在服务器级别设置默认的填充因子。子。l 填充因子只在创建索引时执行；索引创建后，当表中进行数据的添加、填充因子只在创建索引时执行；索引创建后，当表中进行数据的添加、删除或更新时，不会保持填充因子。删除或更新时，不会保持填充因子。 3810.4设置索引的选项设置索引的选项 案例：为案例：为st

38、udnet数据库中数据库中“班级班级”表创建基于表创建基于“系部代码系部代码”列的非聚集索引列的非聚集索引bj_xb_index，其填充因子值为，其填充因子值为60。USE studentGOCREATE INDEX bj_xb_index ON 班级班级(系部代码系部代码)WITH FILLFACTOR=60GO3910.4设置索引的选项设置索引的选项2.设置设置FILLFACTOR选项选项 FILLFACTOR选项用来指定各索引页叶级的填满程选项用来指定各索引页叶级的填满程度，对于非叶级索引页需要使用度，对于非叶级索引页需要使用PAD_INDEX选项设置其选项设置其预留空间的大小。预留空间

39、的大小。PAD_INDEX 选项只有在指定了选项只有在指定了 FILLFACTOR 时才有用，因为时才有用，因为 PAD_INDEX 使用由使用由 FILLFACTOR 所指定的百分比。所指定的百分比。 如果为如果为 FILLFACTOR 指定的百分比不够大，无法指定的百分比不够大，无法容纳一行，容纳一行，SQL Server 将在内部使用允许的最小值替代将在内部使用允许的最小值替代该百分比。该百分比。4010.4设置索引的选项设置索引的选项案例：为案例：为student数据库中数据库中“班级班级”表创建基于表创建基于“系部代系部代码码”列的非聚集索引列的非聚集索引 banji_xibu_in

40、dex,其其FILLFACTOR和和PAD_INDEX选项值均为选项值均为60。 USE student GO CREATE INDEX banji_xibu_index ON 班级班级(系部代码系部代码) WITH PAD_INDEX，FILLFACTOR=60 GO4110.5索引的分析与维护索引的分析与维护1.索引分析索引分析 SQL Server提供了多种分析索引和查询性能的方法，常用的有提供了多种分析索引和查询性能的方法，常用的有SHOWPLAN和和STATISTICS IO语句。语句。 1）SHOWPLAN语句用来显示查询语句的执行信息，包含查询过程中语句用来显示查询语句的执行信息

41、，包含查询过程中 连接表时所采取的每个步骤以及选择哪个索引。其语法格式为：连接表时所采取的每个步骤以及选择哪个索引。其语法格式为： SET SHOWPLAN_ALL ON | OFF SET SHOWPLAN_TEXT ON | OFF 2） STATISTICS IO STATISTICS IO语句用来显示执行数据检索语句所花费的磁盘语句用来显示执行数据检索语句所花费的磁盘活动量信息，可以利用这些信息来确定是否重新设计索引。其语法格活动量信息，可以利用这些信息来确定是否重新设计索引。其语法格式为：式为： SET STATISTICS IO ON|OFF 4210.5索引的分析与维护索引的分析

42、与维护案例：在案例：在student库中的库中的“学生学生”表上查询所有男生的姓名和年龄，并显表上查询所有男生的姓名和年龄，并显示查询处理过程中的磁盘活动统计信息。代码如下：示查询处理过程中的磁盘活动统计信息。代码如下：USE studentGOSET STATISTICS IO ONGOSELECT 姓名姓名,YEAR(GETDATE()-YEAR(出生日期出生日期) AS 年龄年龄FROM 学生学生WHERE 性别性别=男男GOSET STATISTICS IO OFFGO4310.5 索引的分析与维护索引的分析与维护2.索引的维护索引的维护 SQL Server提供了多种维护索引的方法，

43、常用的有提供了多种维护索引的方法，常用的有 DBCC SHOWCONTIG、DBCC INDEXDEFRAG语句。语句。1）DBCC SHOWCONTIG语句语句 该语句用来显示指定表的数据和索引的碎片信息。当对表该语句用来显示指定表的数据和索引的碎片信息。当对表进行大量的修改或添加数据之后，应该执行此语句来查看有进行大量的修改或添加数据之后，应该执行此语句来查看有无碎片。其语法格式如下：无碎片。其语法格式如下： DBCC SHOWCONTIG table_name | table_id | view_name | view_id , index_name | index_id ) 44使用示

44、例：使用示例： DBCC SHOWCONTIG (课程注册课程注册) 当执行此语句时，我们重点看其扫描密度，其理想值当执行此语句时，我们重点看其扫描密度，其理想值为为100%，如果小于这个值，表示表上已有碎片。如果表，如果小于这个值，表示表上已有碎片。如果表中有索引碎片，可以使用中有索引碎片，可以使用DBCC INDEXDEFRAG对碎片对碎片进行整理进行整理 452）使用）使用DBCC INDEXDEFRAG语句语句该语句的作用是整理表中索引碎片，其语法格式为：该语句的作用是整理表中索引碎片，其语法格式为：DBCC INDEXDEFRAG ( database_name | database

45、_id | 0 , table_name | table_id | view_name | view_id , index_name | index_id ) 10.5索引的分析与维护索引的分析与维护 46清除清除student库中库中“学生学生”表中的碎片。代码如下：表中的碎片。代码如下：USE studentGODBCC INDEXDEFRAG(student,学生学生)GO4710.6索引视图索引视图 创建索引视图除要遵照创建标准视图的要求外，还应该注意以下几点：创建索引视图除要遵照创建标准视图的要求外，还应该注意以下几点：l 索引视图只能引用基表，不能引用其它视图。索引视图只能引用基表，不能引用其它视图。l 索引视图引用的所有基表必须与视图位于同一数据库中，并且所有者索引视图引用的所有基表必须与视图位于同一数据库中，并且所有者也与视图相同。也与视图