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

1、第五讲第五讲 索引索引教学内容教学内容n索引是数据库中常用而重要的数据库对象。索引是数据库中常用而重要的数据库对象。n通过使用索引，可以大大提高数据库的检索速度通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。，改善数据库性能。本讲具体内容本讲具体内容n索引的概念索引的概念n索引的类型索引的类型n索引的创建和管理方法索引的创建和管理方法学习重点及目标学习重点及目标学习重点学习重点n索引的概念和作用索引的概念和作用n索引的类型索引的类型n如何使用企业管理器创建和管理索引如何使用企业管理器创建和管理索引n如何使用如何使用T-SQL语句创建和管理索引语句创建和管理索引学习目标学习目标n掌握

2、索引的概念和功能掌握索引的概念和功能n结合实际，使用结合实际，使用T-SQL合理创建索引合理创建索引5.1 SQL Server的数据存储的数据存储数据存储在数据页页的大小是 8 KB1M=128 页8页的开始部分是 96 字节的页首 ，存储系统信息，如页的类型、页的可用空间量、拥有页的对象的对象 ID 等） 数据页包含数据行中除 text、ntext 和 image 数据外的所有数据数据行紧接着页首按顺序放置 页尾有一个行偏移表行偏移表页上的每一行都有一个条目记录那一行的第一个字节与页首的距离。行偏移表中的条目序列与页中行的序列相反。 Page 7 上一页FunkSmithMartin.Pa

3、ge 1 Page 5 Page 6 下一页.上一页FunkWhite.上一页WhiteBarr.上一页OtaJones.下一页.下一页.下一页.Page N 上一页OtaJones.下一页.SQL Server数据链SQL Server的数据访问的数据访问l在一个表中扫描所有的数据页数据页数据页Page 7 AkhtarFunkSmithMartin.Page 4 Page 5 Page 6 .ConFunkWhite.RuddWhiteBarr.SmithOtaJones.Page N SmithOtaJones.查找姓名为Ota的记录Select FirstName From Membe

4、r Where FirstName=OtaFirstName=Ota?OtaFindSQL Server的数据访问的数据访问l用指向数据页数据的索引数据页数据页Page 7 ObtOctOdd.Page 4 Page 5 Page 6 .BarrBeerBon.BossCarCon.OllimOtaOtoa.Page N SmithSmarkSton.查找姓名为Ota的记录,FirstName为聚集索引Select FirstName From Member Where FirstName=OtaFirstName=Ota?OtaAkhtarZooBarrConObaSmith索引页5.2 索

5、引问题索引问题n数据库中的索引与书籍中的索引类似，在一本书中数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本，利用索引可以快速查找所需信息，无须阅读整本书。书。n在数据库中，索引使数据库程序无须对整个表进行在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。扫描，就可以在其中找到所需数据。n书中的索引是一个词语列表，其中注明了包含各个书中的索引是一个词语列表，其中注明了包含各个词的页码词的页码n数据库中的索引是某个表中一列或者若干列值的集数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻合和相

6、应的指向表中物理标识这些值的数据页的逻辑指针清单。辑指针清单。 5.2.1 索引的概念索引的概念n索引是对数据表中一个或多个字段的值进行排序索引是对数据表中一个或多个字段的值进行排序的的 结构。结构。n表中的一个索引就是一个列表，列表包含了一些表中的一个索引就是一个列表，列表包含了一些值，值， 以及包含这些值的记录在数据表中的存储位以及包含这些值的记录在数据表中的存储位置。置。n索引键可以是单个字段、多个字段的组合字段索引键可以是单个字段、多个字段的组合字段n索引自身也需要进行维护，并占用一定的资源。索引自身也需要进行维护，并占用一定的资源。一一 般般 只有经常用来检索的字段上建立索引，例如只

7、有经常用来检索的字段上建立索引，例如经常在经常在WHERE子句中引用的字段。子句中引用的字段。5.2.2 索引的作用索引的作用n通过创建唯一索引，可以保证数据记录的唯一性。通过创建唯一索引，可以保证数据记录的唯一性。n可以大大加快数据检索速度。可以大大加快数据检索速度。n可以加速表与表之间的连接，这一点在实现数据的参可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。照完整性方面有特别的意义。n在使用在使用ORDER BYORDER BY和和GROUP BYGROUP BY子句中进行检索数据时，子句中进行检索数据时，可以显著减少查询中分组和排序的时间。可以显著减少查询中分组

8、和排序的时间。n使用索引可以在检索数据的过程中使用优化器，提高使用索引可以在检索数据的过程中使用优化器，提高系统性能。系统性能。5.3 索引的类型索引的类型5.3.1 聚集索引和非聚集索引聚集索引和非聚集索引n根据索引对数据表中记录顺序的影响，索引可以分为聚根据索引对数据表中记录顺序的影响，索引可以分为聚集索引（集索引（clustered index）和非聚集索引（）和非聚集索引（nonclustered index）。）。n聚集索引表中的数据页会依照该索引的顺序来存放。表聚集索引表中的数据页会依照该索引的顺序来存放。表中各行的物理顺序与键值的逻辑（索引）顺序相同。一中各行的物理顺序与键值的逻

9、辑（索引）顺序相同。一个表只能包含一个聚集索引。个表只能包含一个聚集索引。n数据链内的页和其内的行按聚集索引键值排序。所有插数据链内的页和其内的行按聚集索引键值排序。所有插入都在所插入行中的键值与排序顺序相匹配时执行入都在所插入行中的键值与排序顺序相匹配时执行聚集索引中插入行聚集索引中插入行StudNoStudNameStudScore99070403陈俊陈俊9299070406周凯周凯6899070413许淑娟许淑娟7499070422王平王平80StudNoStudNameStudScore99070403陈俊陈俊9299070406周凯周凯6899070413许淑娟许淑娟74990704

10、22王平王平80StudNoStudNameStudScore99070422王平王平8099070403陈俊陈俊9299070413许淑娟许淑娟7499070406周凯周凯68表StudScoreInfo无索引表StudScoreInfo有聚集索引StudNo为表增加一条记录，执行下列语句后Insert Into StudScoreInfo(99070408,陈涛陈涛,85)StudNoStudNameStudScore99070403陈俊陈俊9299070406周凯周凯6899070408陈涛陈涛8599070413许淑娟许淑娟7499070422王平王平8099070408 陈涛陈涛 8

11、5在这里插入在这里插入索引的顺序与数据表存储的物理顺序相同在学号StudNo上建立了聚集索引,学号排好序啦非聚集索引非聚集索引n非聚集索引不会影响数据表中记录的实际存储顺非聚集索引不会影响数据表中记录的实际存储顺序。在数据表中记录的实际存储顺序不会因索引序。在数据表中记录的实际存储顺序不会因索引的创建而发生变化。的创建而发生变化。n可以在一个表中创建多个非聚集索引。一个表最可以在一个表中创建多个非聚集索引。一个表最多可以创建多可以创建249个非聚集索引。个非聚集索引。n非聚集索引与聚集索引一样有非聚集索引与聚集索引一样有 B 树结构，但是有树结构，但是有两个重大差别：两个重大差别： 1.数据行

12、不按非聚集索引键的顺序排序和存储数据行不按非聚集索引键的顺序排序和存储2.非聚集索引的叶级不包含数据页。非聚集索引的叶级不包含数据页。非聚集索引中插入行非聚集索引中插入行StudNoStudNameStudScore99070403陈俊陈俊9299070406周凯周凯6899070413许淑娟许淑娟7499070422王平王平80StudNoStudNameStudScore99070422王平王平8099070403陈俊陈俊9299070413许淑娟许淑娟7499070406周凯周凯68StudNoStudNameStudScore99070422王平王平8099070403陈俊陈俊9299

13、070413许淑娟许淑娟7499070406周凯周凯68表StudScoreInfo无索引表StudScoreInfo有非聚集索引StudNo为表增加一条记录，执行下列语句后Insert Into StudScoreInfo(99070408,陈涛陈涛,85)StudNoStudNameStudScore99070403陈俊陈俊9299070406周凯周凯6899070413许淑娟许淑娟7499070422王平王平8099070408陈涛陈涛8599070408 陈涛陈涛 85在行尾插入记录在行尾插入记录索引的顺序与数据表存储的物理顺序不相同在学号StudNo上建立了非聚集索引,学号没有排序5

14、.3.2 唯一索引和组合索引唯一索引和组合索引 聚集索引和非聚集索引，考虑其索引键值是否重复聚集索引和非聚集索引，考虑其索引键值是否重复,可可以判定是否为唯一索引，考虑其索引字段的组成情况以判定是否为唯一索引，考虑其索引字段的组成情况 ，可以判定可以判定 是否为组合索引。是否为组合索引。（1）唯一索引）唯一索引n在表中创建唯一索引，则该字段或字段组合的值在表中必在表中创建唯一索引，则该字段或字段组合的值在表中必须具有唯一性，即表中任何两条记录的索引值都不能相同须具有唯一性，即表中任何两条记录的索引值都不能相同。（2）复合索引）复合索引n基于多个字段的组合创建索引为复合索引。复合索引同时基于多个

15、字段的组合创建索引为复合索引。复合索引同时也可以是唯一索引。如果是唯一索引，这个字段组合的取也可以是唯一索引。如果是唯一索引，这个字段组合的取值就不能重复，但此时单独的字段值却可以重复。值就不能重复，但此时单独的字段值却可以重复。5.4 创建索引创建索引5.4.1 系统自动创建索引系统自动创建索引n创建或添加主键约束或唯一约束，系统会基于添加约束的创建或添加主键约束或唯一约束，系统会基于添加约束的字段自动创建唯一性索引。字段自动创建唯一性索引。（1）唯一约束字段）唯一约束字段n为数据表中的某一字段设置了唯一约束，系统将基于这个为数据表中的某一字段设置了唯一约束，系统将基于这个字段自动创建一个非

16、聚集的唯一索引，以确保该字段的唯字段自动创建一个非聚集的唯一索引，以确保该字段的唯一性。系统自动创建的索引名为一性。系统自动创建的索引名为“UQ_数据表名数据表名_XXXXXXXX”，索引是聚集还是非聚集取决于是否使用，索引是聚集还是非聚集取决于是否使用CLUSTERED关键字。关键字。n系统存储过程系统存储过程sp_helpindex用于查看表中的索引信息用于查看表中的索引信息nEXEC sp_helpindex Table_Name（2）主键约束字段）主键约束字段n在数据表中的某个字段上设置主键约束时，系统将在该字段在数据表中的某个字段上设置主键约束时，系统将在该字段上自动创建唯一索引，该

17、索引可以是聚集的，也可以是非聚上自动创建唯一索引，该索引可以是聚集的，也可以是非聚集的集的n如果在企业管理器中设置主键，则系统将会自动创建一个唯如果在企业管理器中设置主键，则系统将会自动创建一个唯一的非聚集索引，索引名为一的非聚集索引，索引名为“PK_表名表名”。n在查询分析器中使用在查询分析器中使用T-SQL语句添加主键约束，会创建一个语句添加主键约束，会创建一个索引名称为索引名称为“PK_表名表名_xxxxxxx”，索引是聚集还是非聚集取，索引是聚集还是非聚集取决于决于PRIMARY KEY后面使用的关键字，具体情况如下后面使用的关键字，具体情况如下1）使用）使用CLUSTERED或或NO

18、NCLUSTERED关键字，则生成聚集或关键字，则生成聚集或非聚集索引。非聚集索引。2）不使用关键字，在其他字段上存在聚集索引或不存在聚）不使用关键字，在其他字段上存在聚集索引或不存在聚集索引，则生成非聚集或聚集索引。集索引，则生成非聚集或聚集索引。5.4.2 使用向导创建索引使用向导创建索引5.4.3 5.4.3 使用企业管理器创建索引使用企业管理器创建索引5.4.4 使用使用CREATE INDEX语句创建索引语句创建索引其语法形式如下：其语法形式如下：CREATE UNIQUE CLUSTERED| NONCLUSTERED CREATE UNIQUE CLUSTERED| NONCLU

19、STERED INDEX index_name ON table | view ( column ASC | INDEX index_name ON table | view ( column ASC | DESC ,.n ) DESC ,.n ) withwithPAD_INDEXPAD_INDEX,FILLFACTOR=fillfactor,IGNORE_DUP_KEY,FILLFACTOR=fillfactor,IGNORE_DUP_KEY,DROP_EXISTING,DROP_EXISTING ,STATISTICS_NORECOMPUTE ,STATISTICS_NORECOMPUT

20、E,SORT_IN_TEMPDB,SORT_IN_TEMPDB ON filegrou ON filegroup p CREATE INDEX命令创建索引各参数说明命令创建索引各参数说明nUNIQUE：用于指定为表或视图创建唯一索引，即不允许存：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。在索引值相同的两行。 nCLUSTERED：用于指定创建的索引为聚集索引。：用于指定创建的索引为聚集索引。 nNONCLUSTERED：用于指定创建的索引为非聚集索引。：用于指定创建的索引为非聚集索引。nindex_name：用于指定所创建的索引的名称。用于指定所创建的索引的名称。 ntab

21、le：用于指定创建索引的表的名称。用于指定创建索引的表的名称。 nview：用于指定创建索引的视图的名称。用于指定创建索引的视图的名称。 nASC|DESC：用于指定具体某个索引列的升序或降序排序方：用于指定具体某个索引列的升序或降序排序方向。向。 nColumn：用于指定被索引的列。用于指定被索引的列。nPAD_INDEX：用于指定索引中间级中每个页（节点）上保持：用于指定索引中间级中每个页（节点）上保持开放的空间。开放的空间。CREATE INDEX命令创建索引各参数说明命令创建索引各参数说明nFILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数：用于指定在

22、创建索引时，每个索引页的数据占索引页大小的百分比，据占索引页大小的百分比，fillfactor的值为的值为1到到100。nIGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时中插入重复数据时SQL Server所作的反应。所作的反应。 nDROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。聚集索引或者非聚集索引。 nSTATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重：用于指定过期的索引统计不会自动重新计算。

23、新计算。 nSORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。数据库中。 nON filegroup：用于指定存放索引的文件组。：用于指定存放索引的文件组。 EX1 唯一聚集索引唯一聚集索引Create Table Test_CreateIndex( TestID INT NOT NULL, FirstName Char(10), LastName char(10), Salary numeric(4,1)GoCreate UNIQUE CLUSTERED INDEX IX_TestIDON Test_Cr

24、eateIndex(TestID)Go创建唯一非聚集索引创建唯一非聚集索引USE NorthwindCREATE UNIQUE NONCLUSTERED INDEX U_CustIDON customers(CustomerID)RANCH Sant GourmetJonas Bergulfsen向表中添加有重复键值的记录是不允许的CustomerIDCompanyNameContactNameQUICKBONAP12QUICK-StopBon appWalkingHorst KlossLaurence LebihanHenry David ThoreauRANCHRancho grandeS

25、ergio Gutirrez创建唯一非聚集复合索引创建唯一非聚集复合索引USE NorthwindCREATE UNIQUE NONCLUSTERED INDEX U_OrdID_ProdIDON Order Details (OrderID, ProductID)OrderIDProductIDUnitPrice Quantity10248102481024811427214.0009.80034.80012105Discount0.00.00.0复合键复合键列列 1列列 2使用填充因子选项使用填充因子选项(Fillfactor)(Fillfactor)n指定在创建索引后对数据页的填充比例指

26、定在创建索引后对数据页的填充比例 n值越小则数据页上的空闲空间越大，减少在索引增长过程中值越小则数据页上的空闲空间越大，减少在索引增长过程中对数据页进行拆分的需要对数据页进行拆分的需要数据页完全填充数据页完全填充ConFunkWhiteRudd.470401470402470403470501White .470502Barr.470503AkhtarFunkSmithMartinSmith.470601470602470603470604470701Ota.470702MartinPhuaJonesSmithGanio.470801470802470803470804470901Jones .470902Fillfactor 50 = 叶子页叶子页 50% 填满填满ConFunkWhite . 470401470402470403 RuddWhiteBarr . 470501470502470503 AkhtarFunkSmith . 470601470402470603 MartinSmithO