《数据库》第五讲(一)索引.ppt资料

1、第五讲 索引,教学内容,索引是数据库中常用而重要的数据库对象。 通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。 本讲具体内容 索引的概念 索引的类型 索引的创建和管理方法,学习重点及目标,学习重点 索引的概念和作用 索引的类型 如何使用企业管理器创建和管理索引 如何使用T-SQL语句创建和管理索引 学习目标 掌握索引的概念和功能 结合实际，使用T-SQL合理创建索引,5.1 SQL Server的数据存储,SQL Server的数据访问,在一个表中扫描所有的数据页,数据页,Page 7,Page 4,Page 5,Page 6,Page N,查找姓名为Ota的记录 Select

2、FirstName From Member Where FirstName=Ota,FirstName=Ota?,Ota,SQL Server的数据访问,用指向数据页数据的索引,数据页,Page 7,Page 4,Page 5,Page 6,Page N,查找姓名为Ota的记录,FirstName为聚集索引 Select FirstName From Member Where FirstName=Ota,FirstName=Ota?,Ota,Akhtar,Zoo,索引页,5.2 索引问题,数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。 在数据库中，

3、索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。 书中的索引是一个词语列表，其中注明了包含各个词的页码 数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。,5.2.1 索引的概念,索引是对数据表中一个或多个字段的值进行排序的 结构。 表中的一个索引就是一个列表，列表包含了一些值， 以及包含这些值的记录在数据表中的存储位置。 索引键可以是单个字段、多个字段的组合字段 索引自身也需要进行维护，并占用一定的资源。一 般 只有经常用来检索的字段上建立索引，例如经常在WHERE子句中引用的字段。,5.2.2 索引的作用,通过创建唯一索引，

4、可以保证数据记录的唯一性。 可以大大加快数据检索速度。 可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。 在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。 使用索引可以在检索数据的过程中使用优化器，提高系统性能。,5.3 索引的类型,5.3.1 聚集索引和非聚集索引 根据索引对数据表中记录顺序的影响，索引可以分为聚集索引（clustered index）和非聚集索引（nonclustered index）。 聚集索引表中的数据页会依照该索引的顺序来存放。表中各行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚

5、集索引。 数据链内的页和其内的行按聚集索引键值排序。所有插入都在所插入行中的键值与排序顺序相匹配时执行,聚集索引中插入行,表StudScoreInfo无索引,表StudScoreInfo有聚集索引StudNo,为表增加一条记录，执行下列语句后 Insert Into StudScoreInfo(99070408,陈涛,85),99070408 陈涛 85,非聚集索引,非聚集索引不会影响数据表中记录的实际存储顺序。在数据表中记录的实际存储顺序不会因索引的创建而发生变化。 可以在一个表中创建多个非聚集索引。一个表最多可以创建249个非聚集索引。 非聚集索引与聚集索引一样有 B 树结构，但是有两个重

6、大差别： 1.数据行不按非聚集索引键的顺序排序和存储 2.非聚集索引的叶级不包含数据页。,非聚集索引中插入行,表StudScoreInfo无索引,表StudScoreInfo有非聚集索引StudNo,为表增加一条记录，执行下列语句后 Insert Into StudScoreInfo(99070408,陈涛,85),99070408 陈涛 85,5.3.2 唯一索引和组合索引,聚集索引和非聚集索引，考虑其索引键值是否重复,可以判定是否为唯一索引，考虑其索引字段的组成情况 ，可以判定 是否为组合索引。 （1）唯一索引 在表中创建唯一索引，则该字段或字段组合的值在表中必须具有唯一性，即表中任何两条

7、记录的索引值都不能相同。 （2）复合索引 基于多个字段的组合创建索引为复合索引。复合索引同时也可以是唯一索引。如果是唯一索引，这个字段组合的取值就不能重复，但此时单独的字段值却可以重复。,5.4 创建索引,5.4.1 系统自动创建索引 创建或添加主键约束或唯一约束，系统会基于添加约束的字段自动创建唯一性索引。 （1）唯一约束字段 为数据表中的某一字段设置了唯一约束，系统将基于这个字段自动创建一个非聚集的唯一索引，以确保该字段的唯一性。系统自动创建的索引名为“UQ_数据表名_XXXXXXXX”，索引是聚集还是非聚集取决于是否使用CLUSTERED关键字。 系统存储过程sp_helpindex用于

8、查看表中的索引信息 EXEC sp_helpindex Table_Name,（2）主键约束字段,在数据表中的某个字段上设置主键约束时，系统将在该字段上自动创建唯一索引，该索引可以是聚集的，也可以是非聚集的 如果在企业管理器中设置主键，则系统将会自动创建一个唯一的非聚集索引，索引名为“PK_表名”。 在查询分析器中使用T-SQL语句添加主键约束，会创建一个索引名称为“PK_表名_xxxxxxx”，索引是聚集还是非聚集取决于PRIMARY KEY后面使用的关键字，具体情况如下 1）使用CLUSTERED或NONCLUSTERED关键字，则生成聚集或非聚集索引。 2）不使用关键字，在其他字段上存在

9、聚集索引或不存在聚集索引，则生成非聚集或聚集索引。,5.4.2 使用向导创建索引,5.4.3 使用企业管理器创建索引,5.4.4 使用CREATE INDEX语句创建索引,其语法形式如下： CREATE UNIQUE CLUSTERED| NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,.n ) with PAD_INDEX,FILLFACTOR=fillfactor,IGNORE_DUP_KEY,DROP_EXISTING ,STATISTICS_NORECOMPUTE ,SORT_IN_TEMPDB ON

10、 filegroup ,CREATE INDEX命令创建索引各参数说明,UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。 CLUSTERED：用于指定创建的索引为聚集索引。 NONCLUSTERED：用于指定创建的索引为非聚集索引。 index_name：用于指定所创建的索引的名称。 table：用于指定创建索引的表的名称。 view：用于指定创建索引的视图的名称。 ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。 Column：用于指定被索引的列。 PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。,CREATE INDEX命令

11、创建索引各参数说明,FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。 IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。 DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。 STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。 SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。 ON filegroup：用于

12、指定存放索引的文件组。,EX1 唯一聚集索引,Create Table Test_CreateIndex ( TestID INT NOT NULL, FirstName Char(10), LastName char(10), Salary numeric(4,1) ) Go Create UNIQUE CLUSTERED INDEX IX_TestID ON Test_CreateIndex(TestID) Go,创建唯一非聚集索引,USE Northwind CREATE UNIQUE NONCLUSTERED INDEX U_CustID ON customers(CustomerID

13、),向表中添加有重复键值的记录是不允许的,创建唯一非聚集复合索引,USE Northwind CREATE UNIQUE NONCLUSTERED INDEX U_OrdID_ProdID ON Order Details (OrderID, ProductID),使用填充因子选项(Fillfactor),指定在创建索引后对数据页的填充比例 值越小则数据页上的空闲空间越大，减少在索引增长过程中对数据页进行拆分的需要,数据页完全填充,Fillfactor 50 = 叶子页 50% 填满,EX2 唯一复合索引,Create Unique Index IX_F_LName ON Test_Creat

14、eIndex(FirstName,LastName) With PAD_INDEX, FILLFACTOR=80, IGNORE_DUP_KEY EXEC sp_helpindex Test_CreateIndex,5.5 删除索引,5.5.1 使用企业管理器删除索引 5.5.2 使用DROP INDEX语句删除索引,5.5.1 使用企业管理器删除索引,5.5.2 使用DROP INDEX语句删除索引,语法：DROP INDEX table.index,nssss 其中table为包含索引的表名，index为索引名 例:Drop Index Test_CreateIndex.IX_F_LName,Test_CreateIndex.IX_T