第9章索引.docx

第9章索引.docx

《第9章索引.docx》由会员分享，可在线阅读，更多相关《第9章索引.docx（11页珍藏版）》请在冰豆网上搜索。

第9章索引

第12章索引

教学目标

●理解和掌握使用索引和不使用索引的原因

●理解和掌握创建、删除、重组索引以及查看索引技术

●理解和掌握索引优化技术

12.1概述

●索引是一种与表或视图关联的物理结构，可以用来提高从表或视图中检索数据行的速度。

●索引设计是数据库设计中的重要部分，能够准确的把握索引创建的度（索引的数量、应该在哪些列上建立索引等）,常常是考核一个DBA是否足够优秀的评判依据。

12.2索引的类型和特点

●有两种基本的索引类型：

聚集索引和非聚集索引。

除此之外，还有唯一性索引、包含性列索引、索引视图、全文索引、XML索引等。

先看一下下面的例子，体会索引的作用。

USEtempdb

GO

--建立演示表

CREATETABLEtb（

idint）

--插入三条记录

INSERTtbSELECT1

INSERTtbSELECT2

INSERTtbSELECT3

--删除插入记录中,最前面的两条

DELETEtbWHEREid<3

--再次插入两条记录

INSERTtbSELECT2

INSERTtbSELECT1

--显示结果

SELECT*FROMtb

GO

--加上聚集索引

CREATECLUSTEREDINDEXIDX_tb_id

ONtb（

id）

--显示结果

SELECT*FROMtb

结论：

从中可以看出，没有聚集索引时，表中的数据存储是无序的，当表中有聚集索引时，表中数据存储顺序同聚集索引顺序相同。

理解聚集索引和非聚集索引。

大家回忆一下字典的构成。

1按拼音查字

a-------1

ai------3

ang----6

......

Zhong—999

2按部首查字

一部---23

厂部---8

。

。

。

3其他查字方法

。

。

。

最后是真正的数据。

从字典的组成可以看出，字典的数据真正的存储顺序只有一种，但是我们可以根据不同的标准（拼音、部首等）快速定位要要查找的数据。

我们把拼音查字表称为聚集索引，因为字典是按照拼音的顺序排列的。

把其他查字表称为非聚集索引。

聚集索引的结构特点

●聚集索引是一种数据表的物理顺序与索引顺序相同的索引

非聚集索引的结构特点

其他类型的索引

●除了聚集索引和非聚集索引之外，MicrosoftSQLServer2005系统还提供了一些其他类型的索引或索引表现形式，这些内容包括唯一性索引、包含性列索引、索引视图、全文索引和XML索引。

适合建立索引的情况

经常被查询搜索的列

在orderby子句中使用的列

是外键或主键的列

该列值唯一的列

访问数据的方式

●一般地，访问数据库中的数据，可以采用两种方法：

–表扫描

–索引查找

12.3创建索引

使用CREATEINDEX语句

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]　INDEX索引名ON{表名|视图名}

（列名[ASC|DESC]....n）

[ON文件组]

--创建索引

createindexix_tc1

ontc（教师编号）

数据库引擎优化顾问

●使用MicrosoftSQLServer2005的数据库引擎优化顾问，用户可以方便地创建和选择索引、索引视图和分区的最佳集合。

数据库引擎优化顾问分析一个或多个数据库的工作负荷和实现，其中工作负荷是对要优化的一个或多个数据库执行的一组Transact-SQL语句。

●数据库引擎优化顾问的输入是由SQLServerProfiler生成的跟踪文件、指定的跟踪表或工作负荷。

数据库引擎优化顾问的输出是修改数据库的物理设计结构的建议，其中物理设计结构包括聚集索引、非聚集索引、索引视图、分区等。

查看索引信息

●在MicrosoftSQLServer2005系统中，可以使用一些目录视图和系统函数查看有关索引的信息。

这些目录视图和系统函数如表12-1所示。

12.4索引维护

●索引在创建之后，由于数据的增加、删除、更新等操作会使得索引页出现碎块，为了提高系统的性能，必须对索引进行维护。

这些维护包括查看碎块信息、维护统计信息、分析索引性能、删除重建索引等。

查看索引统计信息

●索引统计信息是查询优化器用来分析和评估查询、确定最优查询计划的基础数据。

●一般地，用户可以通过两种方式访问指定索引的统计信息。

一种方式是使用DBCCSHOW_STATISTICS命令，另一种是使用图形化工具。

查看索引碎片信息

●可以使用两种方式查看有关索引的碎片信息，使用sys.dm_db_index_physical_stats系统函数和使用图形化工具。

维护索引统计信息

●统计信息是存储在SQLServer中的列数据的样本。

这些数据一般用于索引列，但是还可以为非索引列创建统计。

SQLServer维护某一个索引关键值的分布统计信息，并且使用这些统计信息来确定在查询进程中哪一个索引是有用的。

查询的优化依赖于这些统计信息的分布准确度。

查询优化器使用这些数据样本来决定是使用表扫描还是使用索引。

12.5上机实验

●练习创建和重建索引

●练习查看索引碎片信息

索引案例：

首先创建三个表

//学生表

CREATETABLEStudentInfo

（

snochar（3）NOTNULL,

snamechar（8）NOTNULL,

sexchar

（2）NOTNULL,

addreaachar（8）NOTNULL,

dnointNOTnull

）

INSERTINTOStudentInfoVALUES（'001','张平1','女','湖南',2）

INSERTINTOStudentInfoVALUES（'002','张平2','女','北京',2）

INSERTINTOStudentInfoVALUES（'003','张平3','女','湖北',1）

INSERTINTOStudentInfoVALUES（'004','张平4','女','浙江',2）

INSERTINTOStudentInfoVALUES（'005','张平5','女','山东',4）

INSERTINTOStudentInfoVALUES（'006','张平6','女','山东',3）

INSERTINTOStudentInfoVALUES（'007','张平7','女','河北',3）

INSERTINTOStudentInfoVALUES（'008','张平8','女','河南',1）

INSERTINTOStudentInfoVALUES（'009','张平9','女','山西',3）

INSERTINTOStudentInfoVALUES（'010','张平10','女','陕西',4）

//招考信息表

CREATETABLERecruitInfo

（

addresschar（10）NOTNULL,

scorefloatNOTNULL,

snumintNOTNULL

）

INSERTINTORecruitINfoVALUES（'北京',560,220）

INSERTINTORecruitINfoVALUES（'湖南',648.5,65）

INSERTINTORecruitINfoVALUES（'湖北',654,85）

INSERTINTORecruitINfoVALUES（'山东',650,80）

INSERTINTORecruitINfoVALUES（'浙江',638,76）

INSERTINTORecruitINfoVALUES（'河南',639.5,72）

INSERTINTORecruitINfoVALUES（'河北',625,58）

INSERTINTORecruitINfoVALUES（'山西',631,55）

INSERTINTORecruitINfoVALUES（'陕西',635,62）

//部门表

CREATETABLEDepartment

（

dnointNOTNULL,

dnamechar（20）NOTNULL,

dnumintNOTNULL

）

INSERTINTODepartmentVALUES（1,'计算机工程系',220）

INSERTINTODepartmentVALUES（2,'汽车系',80）

INSERTINTODepartmentVALUES（3,'机械工程系',120）

INSERTINTODepartmentVALUES（4,'电子工程系',180）

INSERTINTODepartmentVALUES（5,'工程物理系',40）

INSERTINTODepartmentVALUES（6,'应用数学系',50）

INSERTINTODepartmentVALUES（7,'材料工程系',60）

● 

●编写SELECT*FROMStudentInfo得到以下结果

●然后来看看非聚集索引

●　　基本创建语法：

●--创建非聚集索引

CREATEINDEXName_Index

ONStudentInfo（sname）

● 

● 

●下面看看创建非聚集索引之后的查询

●SELECT*FROMStudentInfo

● 

●结果如下所示：

●可以发现结果和先前未创建非聚集索引以前是一样的结果，为什么会这样呢？

●因为非聚集索引并不会讲数据库中的数据重新排序。

●如果想看非聚集索引的效果，可以强制使用非聚集索引

●--查询时强制使用非聚集索引

SELECT*FROMStudentInfoWITH（INDEX（Name_Index））

●得到的结果如下所示：

●

●

●接下来，看看如何构建唯一的非聚集索引

●--创建唯一非聚集索引

CREATEUNIQUEINDEXSno_IndexONStudentInfo（snoDESC）

●使用关键字    Unique

●当你再试图像表中插入有相同学号的记录时就会跑出异常了

●INSERTStudentInfoVALUES（'006','刘备','男','河南',1）

●抛出的异常如下：

●

●消息2601，级别14，状态1，第1行

不能在具有唯一索引'Sno_Index'的对象'dbo.StudentInfo'中插入重复键的行。

语句已终止。

●

●下面看看聚集索引

●1：

聚集索引一个表中只能存在一个

●2：

使用聚集索引将占用最小的磁盘空间，因为DBMS在插入新行时，会自动重用以前非配给删除的空间

●3：

不再需要orderby语句

●聚集索引的创建语句比非聚集的多了一个Clustered关键字

●--创建聚集索引

CREATECLUSTEREDINDEXSnameClustered_IndexONStudentInfo（sname）

●再来查看数据表内容

●SELECT*FROMStudentInfo得到的结果如下：

●发现数据表中的数据已经拍过序了。

。

。

说明聚集索引会对数据表中的内容进行一个重排

另外，创建多字段的聚集索引也是非常有用的，有时候你需要依据两个字段去查询记录

●CREATECLUSTEREDINDEXSexAddreaaClustered_IndexONStudentInfo（sex,addreaa）

●

●

●对于索引的销毁，采用Drop关键字

●

●DROPINDEXstudentinfo.Sno_Index

●

●

●但是要特别注意的是，必须在索引前面带上table的名字

最后，说几点使用索引的几点原则：

1.对于小的数据表，使用索引并不能提高任何检索性能，因此不需要对其创建索引

2.当用户要检索的字段的数据包含很多数值或很多空值NULL时，为该字段创建索引会大大提高检索效率

3.当用户查询表中的数据时，如果查询结果包含的数据行较少，一般少于数据总数的25%时，使用索引会显著提高查询效率。

反之，索引用户不大

4.索引应该经常在where子句中使用，否则，该索引就没有发生作用

5.建表时，先装数据，再建索引

6.在对数据表进行大量更新时，最好先销毁索引，等数据更新完毕后再创建索引，这样会提高效率。

（因为索引会降低数据更新的效率）

7.如果可能，把数据表和索引存放于不同的磁盘上，这样会提高查询效率