索引及其应用

第五章索引及其应用SQLServer数据库应用技术SQLServer20002本章主要内容5.1索引的基础知识5.2创建索引5.3索引统计5.4查看与删除索引SQLServer20003数据库中的索引是一个列表，是一个单独的、物理的数据库结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单构成。5.1.1数据的存储与访问5.1.2索引的基本概念5.1.3索引的分类5.1索引的基础知识SQLServer200045.1.1数据的存储与访问一.数据的存储在SQLServer中，数据存储的基本单位是页。SQLServer2000中，页的大小是8KB。每页的开始部分是96个字节的页首，用于存储系统信息，如页的类型、页的可用容量、拥有页的对象ID等。SQLServer200055.1.1数据的存储与访问二.数据的访问SQLServer提供了两种数据访问的方法。–（1）表扫描法在没有建立索引的表内进行数据访问时，SQLServer通过表扫描法来获取所需要的数据。当SQLServer执行表扫描时，它从表的第一行开始进行逐行查找，直到找到符合查询条件的行。–（2）索引法在建有索引的表内进行数据访问时，SQLServer通过使用索引来获取所需要的数据。当SQLServer使用索引时，它会通过遍历索引树来查找所需行的存储位值，并通过查找的结果提取所需的行。SQLServer200065.1.2索引的基本概念索引是依赖于表建立的,它提供了数据库中编排表中数据的内部方法.一个表的存储是由两部分组成:一部分用来存放表的数据页面,另一部分存放索引页面.索引就存放在索引页面上.一般来说,索引不是重排记录的物理顺序生成一个新的表文件，而是选定一个关键字，根据关键字的值对记录进行逻辑排序。按关键字值的顺序记录此关键字值所在记录的记录号。SQLServer20007一、索引的作用通过创建唯一索引，可以保证数据记录的唯一性。可以大大加快数据检索速度。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。在使用ORDERBY和GROUPBY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。5.1.2索引的基本概念SQLServer200085.1.2索引的基本概念二.建立索引的原则(1).定义有主键的数据列一定要建立索引(2).定义有外键的数据列可以建索引(3).在经常查询的数据列最好建立索引(4).对于查询中很少涉及的列,或重复值比较多的列不要建索引(5).对于定义为text、image和bit数据类型的列上不要建立索引.SQLServer200095.1.3索引的分类1.按照索引的顺序与数据表的物理顺序是否相同,可分把索引分为聚集索引与非聚集索引–聚集索引：数据表的物理顺序和索引表的顺序相同,它根据表中的一列或多列的值排列记录。(聚集索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的，它的页节点中存储的是实际的数据。)创建聚集索引的几个注意事项:1.每张表只能有一个聚集索引.2.由于聚集索引改变表的物理顺序,所以应先建聚集索引,后创建非聚集索引.3.创建索引所需的空间来自用户数据库,而不是tempdb数据库4.主键是聚集索引的良好候选者SQLServer2000105.1.3索引的分类–非聚集索引：按照索引的字段排列记录，但是排列的结果并不会存储在表中，而是另外存储。(非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引的页节点存储了组成非聚集索引的关键字值和行定位器。)创建非聚集索引的几个注意事项:1.创建非聚集索引实际上是创建了一个表的逻辑顺序的对象2.索引包含指向数据页上的行的指针3.一张表可创建多达249个非聚集索引4.创建索引时,缺省为非聚集索引SQLServer2000112.唯一索引的概念–唯一索引表示表中每一个索引值只对应唯一的数据记录，–这与表的PRIMARYKEY的特性类似，因此唯一性索引常用于PRIMARYKEY的字段上，以区别每一笔记录。–当表中有被设置为UNIQUE的字段时，SQLSERVER会自动建立一个非聚集的唯一性索引。–而当表中有PRIMARYKEY的字段时，SQLSERVER会在PRIMARYKEY字段建立一个聚集索引。3.复合索引的概念–复合索引是将两个字段或多个字段组合起来建立的索引，而单独的字段允许有重复的值。5.1.3索引的分类SQLServer2000125.2创建索引在SQLServer2000中，只有表或视图的拥有者才可以为表创建索引，即使表中没有数据也可以创建索引。创建索引有3种的方法：··使用企业管理器创建索引··使用向导创建索引··使用CREATEINDEX语句创建索引此外，当对表中的字段设置主键约束时，如果表中没有聚集索引，且主键约束未使用关键字NONCLUSTERED系统会自动创建一个唯一性索引SQLServer200013三.使用T-SQL语句创建索引A.其语法形式如下：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]SQLServer200014B.CREATEINDEX命令创建索引各参数说明如下:UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。CLUSTERED：用于指定创建的索引为聚集索引。NONCLUSTERED：用于指定创建的索引为非聚集索引。index_name：用于指定所创建的索引的名称。table：用于指定创建索引的表的名称。view：用于指定创建索引的视图的名称。ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。Column：用于指定被索引的列。PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。SQLServer200015FILLFACTOR=fillfactor(填充因子)：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQLServer所作的反应。DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在tempdb数据库中。ONfilegroup：用于指定存放索引的文件组。B.CREATEINDEX命令创建索引各参数说明如下:SQLServer200016C.简单例题【例5.1】在数据库student中的stud_grade表中stud_id列上创建名为stud_in_index的聚集索引.【例5.2】在数据库student中的stud_grade表中course_id列上创建名为courseindex的非聚集索引usestudentgocreateclusteredindexstud_id_indexonstud_grade(stud_id)usestudentgocreatenonclusteredindexcourseindexonstud_grade(course_id)SQLServer200017【例5.3】在数据库student的stud_grade表的stud_id列上创建名为id_index的唯一聚集索引.注意:在已有数据的表上创建唯一索引时,如果在该列数值存在重复值,那么系统将返回错误信息.C.简单例题usestudentgocreateuniqueclusteredindexid_indexonstud_grade(stud_id)SQLServer200018【例5.4】在数据库student的stud_grade表中course_id列和grade列上创建名为c_g_index的复合索引.复合索引特征:1.把两列或更多列指定为索引列.2.将复合列作为一个整体进行搜索.3.创建复合索引中的列序不一定与表定义列序相同usestudentgocreateindexc_g_indexonstud_grade(course_id,grade)C.简单例题SQLServer200019D.设置索引选项fillfactor1.fillfactor(填充因子)的值为从1~100的百分比数值,用来指定在创建索引后对数据页的填充比例.其作用是:当系统新建或重建索引时,在每一个索引页上预先留出一部分空间,使得系统在新增索引信息时能够保持索引内容在索引页上尽量连续.它使得索引的页分裂度最小,并可以对性能微调.2.可以使用系统存储过程sp_configure来更改填充因子SQLServer2000203.填充因子只在创建索引时执行;索引创建后,当表中进行数据的添加,删除或更新时,不会保持填充因子.4.PAD_INDEXfillfactor只能指定叶级索引页的数据充满度.PAD_INDEX指定索引非叶级中每个索引页上保持开放的空间,即非叶级的索引页的数据充满度.PAD_INDEX必须和fillfactor一起使用才有效,而且fillfactor的值决定了PAD_INDEX指定的充满度.D.设置索引选项fillfactorSQLServer200021【例5.5】为student数据库中表stud_grade创建基于学号列的非聚集索引xh1_index,其填充因子为60.【例5.6】为表stud_grade创建基于学号列的非聚集索引xh2_index.其中fillfactor和pad_index选项值均为60D.设置索引选项filefactorcreateindexxh1_indexonstud_grade(stud_id)withfillfactor=60createindexxh2_indexonstud_grade(stud_id)withpad_index,fillfactor=60SQLServer200022四.索引的分析与维护A.索引的分析SQLServer提供了多种分析索引和查询性能的方法，经常用到的有两个命令：（1）SHOWPLAN:通过在查询语句中设置SHOWPLAN选项，我们可以选择是否让SQLServer显示查询计划。显示查询计划就是SQLServer将显示在执行查询的过程中连接表时所采取的每个步骤，以及是否选择了哪个索引，从而帮助人们分析有哪些索引被系统采用。语法格式:Setshowplan_all{on|off}或setshowplan_text{on|off}SQLServer200023A.索引的分析（2）STATISTICSIO:数据检索语句所花费的磁盘活动量也是人们较关心的性能之一。通过设置STATISTICSIO项，可以使SQLServer显示磁盘IO信息。设置是否显示磁盘IO的命令为：setstatisticsio{on|off}SQLServer200024【例5.7】在student库中的stud_info表上查询所有男生的姓名和年龄,并显示查询处理过程中的磁盘活动统计信息.usestudentgosetshowplan_alloffgosetstatisticsioongoselectnameas姓名,year(getdate())-ye