数据库原理及应用---第5章 SQL Server 2005表的创建和管理.ppt

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 5.8.1索引的基本知识 什么情况下创建非聚集索引 数据量大、更新操作少。 决策支持系统应用程序和主要包含只读数据的表。在创建非聚集索引后， 查询优化器具有更多可供选择的索引用来确定最快的访问方法。数据的低更新特 征意味着索引维护不会降低性能。 联机事务处理应用程序和包含大量更新表的数据库应避免使用过多的非 聚集索引。 窄索引，即列越少越好。 5.8.1索引的基本知识 下述情况适合选择非聚集索引 覆盖查询的列 大量非重复值的列 5.8.2使用SSMS创建索引 使用SSMS创建索引。 请看演示 5.8.3使用T-SQL语句创建索引 CREATE[UNIQUE][CLUSTERED|NONCLUSTERED] INDEX 索引名 ON 表名（列名[ASC|DESC][,…n]） [WITH [PAD_INDEX] [[,]FILLACTOR=fillfactor] [[,]DROP_EXISTING] … ] 5.8.3使用T-SQL语句创建索引 例：在EDUC数据库中为Student表创建一个唯一性聚集索引Studentindex2，索引关键字为SID，升序排列，填充因子是50％（执行该例时先将SID的主键移去） USE EDUC GO CREATE UNIQUE CLUSTERED INDEX Studentindex2 ON Student (SID ASC) WITH FILLFACTOR=50 5.8.4使用SSMS删除索引 使用SSMS删除索引。 请看演示 5.8.5使用T-SQL删除索引 DROP INDEX 表名.索引名 例：删除表Student的索引studentindex USE EDUC GO DROP INDEX Studen.studentindex 5.8.6维护索引 为什么维护索引? 在数据库增长、页拆分、然后最终删除数据时，会产生碎片 。索引碎片的对数据库有很大的危害。 第一、碎片是——浪费的空间。 第二、碎片很容易带来麻烦——记录散布在各处会在检索数据时带来额外的开销。 5.8.6维护索引 检测碎片可以查看碎片的情况 ： sys.dm_db_index_physical_stats( {database_id|NULL} ,{object_id|NULL} ,{index_id|NULL|0} ,{partition_number|NULL} ,{mode|NULL|DEFAULT} ) 5.8.6维护索引 例20： --定义两个临时变量 DECLARE@db_id SMALLINT; DECLARE@object_id INT; --在这里更改临时变量的值，更改数据库和数据对象的名称 SET@db_id=DB_ID(Ndb_student); SET@object_id=OBJECT_ID(Ndb_student.dbo.t_index); 5.8.6维护索引 --检测数据库和数据对象是否存在 IF@db_id IS NULL BEGIN; PRINT N数据库名称不对或指定的数据库不存在; END; ELSE IF@object_id IS NULL BEGIN; PRINT N数据对象名称不对或指定的数据对象不存在; END; ELSE --调用系统函数检测碎片信息 BEGIN; SELECT*FROM sys.dm_db_index_physical_stats(@db_id, @object_id,NULL,NULL,LIMITED); END; GO 5.8.6维护索引 分析检测结果： 【avg_fragmentation_in_percent】单元格：逻辑碎片的百分比。 【fragment_count】单元格：索引中的碎片（物理上连续的叶页）数量。 【avg_fragment_size_in_pages】单元格：索引中一个碎片的平均页数。 5.8.6维护索引 上述信息中，【avg_fragmentation_in_percent】单元格的信息可以用来决定下一步的处理方法。处理索引碎片的方法有： 【avg_fragmentation_in_percent】单元格显示的信息如果=30%，推荐采用索引重组。 【avg_fragmentation_in_percent】单元格显示的信息如果30%，推荐采用联机重建索引。 5.8.6维护索引 例：对EDUC数据库中的i_t_i_studentno索引重组。 --打开数据库 USE[EDUC] GO --重组索引 ALTER INDEX [i_t_i_studentno] ON [dbo]