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

第五讲索引教学内容索引是数据库中常用而重要的数据库对象。通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。本讲具体内容索引的概念索引的类型索引的创建和管理方法学习重点及目标学习重点索引的概念和作用索引的类型如何使用企业管理器创建和管理索引如何使用T-SQL语句创建和管理索引学习目标掌握索引的概念和功能结合实际，使用T-SQL合理创建索引5.1SQLServer的数据存储数据存储在数据页页的大小是8KB1M=128页×8页的开始部分是96字节的页首，存储系统信息，如页的类型、页的可用空间量、拥有页的对象的对象ID等）数据页包含数据行中除text、ntext和image数据外的所有数据数据行紧接着页首按顺序放置页尾有一个行偏移表行偏移表页上的每一行都有一个条目记录那一行的第一个字节与页首的距离。行偏移表中的条目序列与页中行的序列相反。Page7上一页FunkSmithMartin...Page1Page5Page6下一页............上一页FunkWhite......上一页WhiteBarr......上一页OtaJones......下一页............下一页............下一页............PageN上一页OtaJones......下一页............…SQLServer数据链SQLServer的数据访问在一个表中扫描所有的数据页数据页Page7AkhtarFunkSmithMartin...Page4Page5Page6...............ConFunkWhite......RuddWhiteBarr......SmithOtaJones...................................................PageNSmithOtaJones.....................…查找姓名为Ota的记录SelectFirstNameFromMemberWhereFirstName=‘Ota’FirstName=Ota???OtaFindSQLServer的数据访问用指向数据页数据的索引数据页Page7ObtOctOdd…...Page4Page5Page6...............BarrBeerBon......BossCarCon......OllimOtaOtoa...................................................PageNSmithSmarkSton.....................…查找姓名为Ota的记录,FirstName为聚集索引SelectFirstNameFromMemberWhereFirstName=‘Ota’FirstName=Ota???OtaAkhtar…ZooBarr…ConOba…Smith索引页5.2索引问题数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。5.2.1索引的概念索引是对数据表中一个或多个字段的值进行排序的结构。表中的一个索引就是一个列表，列表包含了一些值，以及包含这些值的记录在数据表中的存储位置。索引键可以是单个字段、多个字段的组合字段索引自身也需要进行维护，并占用一定的资源。一般只有经常用来检索的字段上建立索引，例如经常在WHERE子句中引用的字段。5.2.2索引的作用通过创建唯一索引，可以保证数据记录的唯一性。可以大大加快数据检索速度。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。在使用ORDERBY和GROUPBY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化器，提高系统性能。5.3索引的类型5.3.1聚集索引和非聚集索引根据索引对数据表中记录顺序的影响，索引可以分为聚集索引（clusteredindex）和非聚集索引（nonclusteredindex）。聚集索引表中的数据页会依照该索引的顺序来存放。表中各行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。数据链内的页和其内的行按聚集索引键值排序。所有插入都在所插入行中的键值与排序顺序相匹配时执行聚集索引中插入行StudNoStudNameStudScore99070403陈俊9299070406周凯6899070413许淑娟7499070422王平80StudNoStudNameStudScore99070403陈俊9299070406周凯6899070413许淑娟7499070422王平80StudNoStudNameStudScore99070422王平8099070403陈俊9299070413许淑娟7499070406周凯68表StudScoreInfo无索引表StudScoreInfo有聚集索引StudNo为表增加一条记录，执行下列语句后InsertIntoStudScoreInfo(‘99070408’,’陈涛’,85)StudNoStudNameStudScore99070403陈俊9299070406周凯6899070408陈涛8599070413许淑娟7499070422王平8099070408陈涛85在这里插入索引的顺序与数据表存储的物理顺序相同在学号StudNo上建立了聚集索引,学号排好序啦非聚集索引非聚集索引不会影响数据表中记录的实际存储顺序。在数据表中记录的实际存储顺序不会因索引的创建而发生变化。可以在一个表中创建多个非聚集索引。一个表最多可以创建249个非聚集索引。非聚集索引与聚集索引一样有B树结构，但是有两个重大差别：1.数据行不按非聚集索引键的顺序排序和存储2.非聚集索引的叶级不包含数据页。非聚集索引中插入行StudNoStudNameStudScore99070403陈俊9299070406周凯6899070413许淑娟7499070422王平80StudNoStudNameStudScore99070422王平8099070403陈俊9299070413许淑娟7499070406周凯68StudNoStudNameStudScore99070422王平8099070403陈俊9299070413许淑娟7499070406周凯68表StudScoreInfo无索引表StudScoreInfo有非聚集索引StudNo为表增加一条记录，执行下列语句后InsertIntoStudScoreInfo(‘99070408’,’陈涛’,85)StudNoStudNameStudScore99070403陈俊9299070406周凯6899070413许淑娟7499070422王平8099070408陈涛8599070408陈涛85在行尾插入记录索引的顺序与数据表存储的物理顺序不相同在学号StudNo上建立了非聚集索引,学号没有排序5.3.2唯一索引和组合索引聚集索引和非聚集索引，考虑其索引键值是否重复,可以判定是否为唯一索引，考虑其索引字段的组成情况，可以判定是否为组合索引。（1）唯一索引在表中创建唯一索引，则该字段或字段组合的值在表中必须具有唯一性，即表中任何两条记录的索引值都不能相同。（2）复合索引基于多个字段的组合创建索引为复合索引。复合索引同时也可以是唯一索引。如果是唯一索引，这个字段组合的取值就不能重复，但此时单独的字段值却可以重复。5.4创建索引5.4.1系统自动创建索引创建或添加主键约束或唯一约束，系统会基于添加约束的字段自动创建唯一性索引。（1）唯一约束字段为数据表中的某一字段设置了唯一约束，系统将基于这个字段自动创建一个非聚集的唯一索引，以确保该字段的唯一性。系统自动创建的索引名为“UQ_数据表名_XXXXXXXX”，索引是聚集还是非聚集取决于是否使用CLUSTERED关键字。系统存储过程sp_helpindex用于查看表中的索引信息EXECsp_helpindexTable_Name（2）主键约束字段在数据表中的某个字段上设置主键约束时，系统将在该字段上自动创建唯一索引，该索引可以是聚集的，也可以是非聚集的如果在企业管理器中设置主键，则系统将会自动创建一个唯一的非聚集索引，索引名为“PK_表名”。在查询分析器中使用T-SQL语句添加主键约束，会创建一个索引名称为“PK_表名_xxxxxxx”，索引是聚集还是非聚集取决于PRIMARYKEY后面使用的关键字，具体情况如下1）使用CLUSTERED或NONCLUSTERED关键字，则生成聚集或非聚集索引。2）不使用关键字，在其他字段上存在聚集索引或不存在聚集索引，则生成非聚集或聚集索引。5.4.2使用向导创建索引5.4.3使用企业管理器创建索引5.4.4使用CREATEINDEX语句创建索引其语法形式如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}(column[ASC|DESC][,...n])[with[PAD_INDEX][[,]FILLFACTOR=fillfactor][[,]IGNORE_DUP_KEY][[,]DROP_EXISTING][[,]STATISTICS_NORECOMPUTE][[,]SORT_IN_TEMPDB]][ONfilegroup]CREATEINDEX命令创建索引各参数说明UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。CLUSTERED：用于指定创建的索引为聚集索引。NONCLUSTERED：用于指定创建的索引为非聚集索引。index_name：用于指定所创建的索引的名称。table：用于指定创建索引的表的名称。view：用于指定创建索引的视图的名称。ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。Column：用于指定被索引的列。PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。CREATEINDEX命令创建索引各参数说明FILLFACTOR=fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQLServer所作的反应。DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在tempdb数据库中。ONfilegroup：用于指定存放索引的文件组。EX1唯一聚集索引CreateTableTest_CreateIndex(TestIDINTNOTNULL,FirstNameChar(10),LastNamechar(10),Salarynumeric(4,1))GoCreateUNIQUECLUSTEREDINDEXIX_TestIDONTest_CreateIndex(TestID)Go创建唯一非聚集索引USENorthwindCREATEUNIQUENONCLUSTEREDINDEXU_CustIDONcustomers(CustomerID)RANCHSantéGourmetJonasBergulfsen…向表中添加有重复键值的记录是不允许的CustomersCustomerIDCompanyNameContactName…QUIC