第9章索引.docx

1、第9章 索引第12章 索引教学目标 理解和掌握使用索引和不使用索引的原因 理解和掌握创建、删除、重组索引以及查看索引技术 理解和掌握索引优化技术12.1 概述 索引是一种与表或视图关联的物理结构，可以用来提高从表或视图中检索数据行的速度。 索引设计是数据库设计中的重要部分，能够准确的把握索引创建的度（索引的数量、应该在哪些列上建立索引等）,常常是考核一个DBA是否足够优秀的评判依据。 12.2 索引的类型和特点 有两种基本的索引类型：聚集索引和非聚集索引。除此之外，还有唯一性索引、包含性列索引、索引视图、全文索引、XML索引等。先看一下下面的例子，体会索引的作用。USE tempdbGO- 建

2、立演示表CREATE TABLE tb( id int)- 插入三条记录INSERT tb SELECT 1INSERT tb SELECT 2INSERT tb SELECT 3- 删除插入记录中,最前面的两条DELETE tb WHERE id 3- 再次插入两条记录INSERT tb SELECT 2INSERT tb SELECT 1- 显示结果SELECT * FROM tbGO- 加上聚集索引CREATE CLUSTERED INDEX IDX_tb_id ON tb( id)- 显示结果SELECT * FROM tb结论：从中可以看出，没有聚集索引时，表中的数据存储是无序的，当

3、表中有聚集索引时，表中数据存储顺序同聚集索引顺序相同。理解聚集索引和非聚集索引。大家回忆一下字典的构成。1按拼音查字a-1ai-3ang-6.Zhong9992按部首查字一部-23厂部-8。3其他查字方法。最后是真正的数据。从字典的组成可以看出，字典的数据真正的存储顺序只有一种，但是我们可以根据不同的标准（拼音、部首等）快速定位要要查找的数据。我们把拼音查字表称为聚集索引，因为字典是按照拼音的顺序排列的。把其他查字表称为非聚集索引。聚集索引的结构特点 聚集索引是一种数据表的物理顺序与索引顺序相同的索引 非聚集索引的结构特点 其他类型的索引 除了聚集索引和非聚集索引之外，Microsoft SQ

4、L Server 2005系统还提供了一些其他类型的索引或索引表现形式，这些内容包括唯一性索引、包含性列索引、索引视图、全文索引和XML索引。适合建立索引的情况经常被查询搜索的列在order by 子句中使用的列是外键或主键的列该列值唯一的列访问数据的方式 一般地，访问数据库中的数据，可以采用两种方法： 表扫描 索引查找 12.3 创建索引 使用CREATE INDEX语句 CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEX 索引名 ON 表名|视图名 ( 列名 ASC | DESC .n) ON 文件组 -创建索引create index ix_tc1on tc(

5、教师编号)数据库引擎优化顾问 使用Microsoft SQL Server 2005的数据库引擎优化顾问，用户可以方便地创建和选择索引、索引视图和分区的最佳集合。数据库引擎优化顾问分析一个或多个数据库的工作负荷和实现，其中工作负荷是对要优化的一个或多个数据库执行的一组Transact-SQL语句。 数据库引擎优化顾问的输入是由SQL Server Profiler生成的跟踪文件、指定的跟踪表或工作负荷。数据库引擎优化顾问的输出是修改数据库的物理设计结构的建议，其中物理设计结构包括聚集索引、非聚集索引、索引视图、分区等。查看索引信息 在Microsoft SQL Server 2005系统中，可

6、以使用一些目录视图和系统函数查看有关索引的信息。这些目录视图和系统函数如表12-1所示。12.4 索引维护 索引在创建之后，由于数据的增加、删除、更新等操作会使得索引页出现碎块，为了提高系统的性能，必须对索引进行维护。这些维护包括查看碎块信息、维护统计信息、分析索引性能、删除重建索引等。查看索引统计信息 索引统计信息是查询优化器用来分析和评估查询、确定最优查询计划的基础数据。 一般地，用户可以通过两种方式访问指定索引的统计信息。一种方式是使用DBCC SHOW_STATISTICS命令，另一种是使用图形化工具。查看索引碎片信息 可以使用两种方式查看有关索引的碎片信息，使用sys.dm_db_i

7、ndex_physical_stats系统函数和使用图形化工具。维护索引统计信息 统计信息是存储在SQL Server中的列数据的样本。这些数据一般用于索引列，但是还可以为非索引列创建统计。SQL Server维护某一个索引关键值的分布统计信息，并且使用这些统计信息来确定在查询进程中哪一个索引是有用的。查询的优化依赖于这些统计信息的分布准确度。查询优化器使用这些数据样本来决定是使用表扫描还是使用索引。12.5 上机实验 练习创建和重建索引 练习查看索引碎片信息 索引案例：首先创建三个表/学生表CREATE TABLE StudentInfo( sno char(3) NOT NULL, sna

8、me char(8) NOT NULL, sex char(2) NOT NULL, addreaa char(8) NOT NULL, dno int NOT null)INSERT INTO StudentInfo VALUES(001,张平1,女,湖南,2)INSERT INTO StudentInfo VALUES(002,张平2,女,北京,2)INSERT INTO StudentInfo VALUES(003,张平3,女,湖北,1)INSERT INTO StudentInfo VALUES(004,张平4,女,浙江,2)INSERT INTO StudentInfo VALUES

9、(005,张平5,女,山东,4)INSERT INTO StudentInfo VALUES(006,张平6,女,山东,3)INSERT INTO StudentInfo VALUES(007,张平7,女,河北,3)INSERT INTO StudentInfo VALUES(008,张平8,女,河南,1)INSERT INTO StudentInfo VALUES(009,张平9,女,山西,3)INSERT INTO StudentInfo VALUES(010,张平10,女,陕西,4)/招考信息表CREATE TABLE RecruitInfo( address char(10) NOT

10、NULL, score float NOT NULL, snum int NOT NULL )INSERT INTO RecruitINfo VALUES(北京,560,220)INSERT INTO RecruitINfo VALUES(湖南,648.5,65)INSERT INTO RecruitINfo VALUES(湖北,654,85)INSERT INTO RecruitINfo VALUES(山东,650,80)INSERT INTO RecruitINfo VALUES(浙江,638,76)INSERT INTO RecruitINfo VALUES(河南,639.5,72)IN

11、SERT INTO RecruitINfo VALUES(河北,625,58)INSERT INTO RecruitINfo VALUES(山西,631,55)INSERT INTO RecruitINfo VALUES(陕西,635,62)/部门表CREATE TABLE Department( dno int NOT NULL, dname char(20) NOT NULL, dnum int NOT NULL)INSERT INTO Department VALUES(1,计算机工程系,220)INSERT INTO Department VALUES(2,汽车系,80)INSERT

12、INTO Department VALUES(3,机械工程系,120)INSERT INTO Department VALUES(4,电子工程系,180)INSERT INTO Department VALUES(5,工程物理系,40)INSERT INTO Department VALUES(6,应用数学系,50)INSERT INTO Department VALUES(7,材料工程系,60) 编写SELECT * FROM StudentInfo得到以下结果 然后来看看非聚集索引 基本创建语法： -创建非聚集索引CREATE INDEX Name_Index ON StudentInfo

13、(sname) 下面看看创建非聚集索引之后的查询 SELECT * FROM StudentInfo 结果如下所示： 可以发现结果和先前未创建非聚集索引以前是一样的结果，为什么会这样呢？ 因为非聚集索引并不会讲数据库中的数据重新排序。 如果想看非聚集索引的效果，可以强制使用非聚集索引 -查询时强制使用非聚集索引SELECT * FROM StudentInfo WITH (INDEX (Name_Index) 得到的结果如下所示： 接下来，看看如何构建唯一的非聚集索引 -创建唯一非聚集索引CREATE UNIQUE INDEX Sno_Index ON StudentInfo(sno DESC

14、) 使用关键字 Unique 当你再试图像表中插入有相同学号的记录时就会跑出异常了 INSERT StudentInfo VALUES(006,刘备,男,河南,1) 抛出的异常如下： 消息 2601，级别 14，状态 1，第 1 行不能在具有唯一索引 Sno_Index 的对象 dbo.StudentInfo 中插入重复键的行。语句已终止。 下面看看聚集索引 1：聚集索引一个表中只能存在一个 2：使用聚集索引将占用最小的磁盘空间，因为DBMS在插入新行时，会自动重用以前非配给删除的空间 3：不再需要order by 语句 聚集索引的创建语句比非聚集的多了一个Clustered关键字 -创建聚集

15、索引CREATE CLUSTERED INDEX SnameClustered_Index ON StudentInfo (sname) 再来查看数据表内容 SELECT * FROM StudentInfo得到的结果如下： 发现数据表中的数据已经拍过序了。说明聚集索引会对数据表中的内容进行一个重排另外，创建多字段的聚集索引也是非常有用的，有时候你需要依据两个字段去查询记录 CREATE CLUSTERED INDEX SexAddreaaClustered_Index ON StudentInfo(sex,addreaa) 对于索引的销毁，采用Drop关键字 DROP INDEX stude

16、ntinfo.Sno_Index 但是要特别注意的是，必须在索引前面带上table的名字最后，说几点使用索引的几点原则：1.对于小的数据表，使用索引并不能提高任何检索性能，因此不需要对其创建索引2.当用户要检索的字段的数据包含很多数值或很多空值NULL时，为该字段创建索引会大大提高检索效率3.当用户查询表中的数据时，如果查询结果包含的数据行较少，一般少于数据总数的25%时，使用索引会显著提高查询效率。反之，索引用户不大4.索引应该经常在where子句中使用，否则，该索引就没有发生作用5.建表时，先装数据，再建索引6.在对数据表进行大量更新时，最好先销毁索引，等数据更新完毕后再创建索引，这样会提高效率。（因为索引会降低数据更新的效率）7.如果可能，把数据表和索引存放于不同的磁盘上，这样会提高查询效率