SQL Server 2005 第8章 索引及其应用

Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,Click to edit Master title style,第,8,章 索引及其应用,索引的概述,1,创建索引,2,管理和维护索引,3,表,视图,索引,存储过程,触发器,数据库,技术基础,数据库对象,查询技术,安全控制,并发控制,开发技术,管理技术,基本操作,索引概述,创建索引,查看索引,删除索引,维护索引,索引优化,全文索引,本章重点,索引的概念,索引的分类,索引的创建与删除,本章难点,索引概念的理解,索引的分类,索引的创建、删除等操作的,T-SQL,语法,第,8,章 索引及其应用,索引的概述,1,创建索引,2,管理和维护索引,3,8.1,索引的概述,?,?,?,在一大堆记录中要搜寻某条记录，,你是喜欢从头到尾一条一条地寻,找，还是使用比较有效率的方法呢？,在,SQL Server,中，我们可以针对一个或多个字段的数据做“索引”（,Index,），那么在寻找这些字段的数据时，就会比顺序搜寻要快很多。,索引是什么？是数据库有什么好处？,索引概述,将书籍分门别类,英文书籍使用字母顺序排列,中文书籍使用笔划多少来排列,管理员将这些数据记录在小卡片上整齐地放好，读者从这些整理好的小卡片中很快就能找到自己需要的书籍,这些小卡片就是整个图书管的索引,8.1,索引的概述,索引的结构,8.1,索引的概述,索引的意义,索引在数据库中的作用与目录在书籍中的作用类似，都用来提高查找信息的速度。索引使程序无需浏览整个表，就可以快速找到所需的数据。,使用索引的代价,为每一列都建立索引是不明智的，因为使用索引要付出一定的代价：,索引需要占用数据表以外的物理存储空间。,创建索引和维护索引要花费一定的时间。,当对表进行更新操作时，索引需要被重建，这样降低 了数据的维护速度,8.1,索引的概述,索引的分类,根据索引的存储结构不同将其分为两类：,聚集索引,聚集索引是指表中数据行的,物理存储顺序,与,索引顺序完全相同,。聚集索引由上下两层组成，上层为索引页，包含表中的索引页面，用于数据检索，下层为数据页。,非聚集索引,非聚集索引不改变表中数据行的物理存储位置，数据与索引分开存储，通过索引带有的指针与表中的数据发生联系。,8.1,索引的概述,聚集索引,索引页,数据页,根结点,8.1,索引的概述,例如，在图书馆中存放着很多书，这些书可以按照作者顺序存放，也可以按照书名顺序存放，还可以按照书的出版社排序存放。假设现在这些书是杂乱存放的，并且在书名列上建立了聚集索引，那么这些书就必须按照书名的顺序重新排放，使得数据,(,书,),按照索引排序。这就是聚集索引。,8.1,索引的概述,创建聚集索引应该注意以下事项：,每张表只能有一个聚集索引。,创建聚集索引时所需的可用空间应是数据库表中数据量的,120%,。这是因为在创建聚集索引时表中的数据将被复制以便进行排序，排序完成后，再将旧的未加索引的表删除，所以数据库必须有足够的用来复制的空间。,主键是聚集索引的良好候选者。,缺省设置是非聚集索引。,8.1,索引的概述,非聚集索引,系部代码,指针地址,01,8,02,2,03,5,04,4,05,1,06,7,07,6,08,3,系部代码,系部名称,系主任,05,社会科学部,刘克忠,02,经济管理系,刘国峰,08,建筑系,王未起,04,基础科学部,王彬,03,传播技术系,田建国,07,农林系,陈瑞修,06,机电工程系,王伟东,01,计算机系,刘海军,8.1,索引的概述,8.1,索引的概述,唯一索引,唯一索引不允许两行具有相同的索引值。如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。,8.1,索引的概述,主键索引,主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。主键索引为聚集索引，也是唯一索引。,8.1,索引的概述,【,提示,】,一般情况下，先创建聚集索引，后创建非聚集索引，因为创建聚集索引会改变表中的行的顺序，从而会影响到非聚集索引。,创建多少个非聚集索引，取决于用户执行的查询要求。,【,提示,】,在确定某一索引适合某一查询之后，可以选择最适合具体情况的索引类型。索引包含以下特性：,聚集还是非聚集。,唯一还是非唯一。,单列还是多列。,索引中的列是升序排序还是降序排序。新名称必须遵守标识符规则。,第,8,章 索引及其应用,索引的概述,1,创建索引,2,管理和维护索引,3,1,、系统自动创建索引,在创建或修改表时，如果添加了一个主键或惟一键约束，则系统将自动在该表上，以该键值作为索引列，创建一个惟一索引。该索引是聚集索引还是非聚集索引，要根据当前表中的索引状况和约束语句或命令而定。,8.2,创建索引,例,8.1,在,xsgl,数据库中创建,xs,表时，将学号字段设置为主键。使用存储过程,sp_helpindex,查看,xs,表的索引情况。,8.2,创建索引,EXEC,sp_helpindex,xs,2,、在管理工具下创建索引,例,8.2,在,xs,表上为“姓名”字段添加非唯一性的非聚集索引，将该索引命名为,IX_xm,。,8.2,创建索引,弹出“新建索引”窗口,8.2,创建索引,2,、使用,T-SQL,语句创建索引,CREATE,UNIQUE CLUSTERED|NONCLUSTRERED,INDEX,索引名,ON,表,|,视图,(,字段名,ASC|DESC,n),ON,文件组,指定索引类型,指明索引名，索引名在一个表中必须唯一,指定创建索引的表或视图的名称,指定建立索引的字段,指定保存索引文件的数据库文件组名称,8.2,创建索引,例,8.3,：使用,CREATE INDEX,语句，在,xs,表的“专业”列和“姓名”列上创建名为“,IX_zyxm,”,的非聚集、复合索引。,8.2,创建索引,CREATE,NONCLUSTERED,INDEX,IX_zyxm,ON,xs,(,专业,姓名,),例；将,Employees,表中男员工的数据存为一个新表，命名为“男员工表”，在“男员工表”中按姓名创建一个唯一性聚集索引，并注意查看索引创建前后数据排序的变化。,8.2,创建索引,例：,为,goods,表的“商品编号”和“商品名称”创建一个复合索引“,i_goods,”,设定各相应参数,。,8.2,创建索引,USE sales,GO,CREATE INDEX,i_,姓名,ON Employees(,姓名,DESC),例,8.3,：使用,CREATE INDEX,语句，在,xs,表的“专业”列和“姓名”列上创建名为“,IX_zyxm,”,的非聚集、复合索引。,8.2,创建索引,CREATE,UNIQUE INDEX,i_goods,ON goods,（商品编号,商品名称）,第,8,章 索引及其应用,索引的概述,1,创建索引,2,管理和维护索引,3,1,、查看和维护索引信息,8.3,管理和维护索引,EXEC,sp_helpindex,表名,2,、删除索引,8.3,管理和维护索引,DROP INDEX,表名,.,索引名,|,视图,.,索引名,n,索引列所在的表或视图,要除去的索引名称,例,8.4,：用,DROP INDEX,命令删除,xs,表中的“,IX_zyxm,”,索引。,8.3,管理和维护索引,DROP,INDEX,xs.IX_zyxm,GO,用,DROP INDEX,命令删除索引时，需要注意如下事项。,（,1,）不能用,DROP INDEX,命令删除由,PRIMARY KEY,约束或,UNIQUE,约束创建的索引。这些索引必须通过删除,PRIMARY KEY,约束或,UNIQUE,约束，由系统自动删除。,（,2,）在删除聚集索引时，表中的所有非聚集索引都将被重建。,8.3,管理和维护索引,3,、索引的分析与维护,（,1,）索引的分析,SQL Server,内部存在一个查询优化器，如,何进行数据查询是由它决定的。,查询优化器总能针对数据库的状态为每个查,询生成一个最佳的执行计划。,8.3,管理和维护索引,1,）显示查询计划,例,8.6,：执行学生成绩的查询，显示执行计划。,8.3,管理和维护索引,SELECT,*,FROM,xs,A,INNER JOIN,cj,B,ON,A.,学号,=B.,学号,GO,单击“查询”,|“,显示估计执行计划”命令，完成显,示执行计划的设置,8.3,管理和维护索引,显示执行计划的设置,8.3,管理和维护索引,以表格的形式显示计划：,8.3,管理和维护索引,set showplan_all,on|,off,set showplan_,text,on|,off,8.3,管理和维护索引,USE XSGL,GO,SET SHOWPLAN_ALL ON;,GO,SELECT,姓名,FROM,xs,WHERE,姓名,LIKE,林,%,GO,SET SHOWPLAN_ALL OFF;,GO,2,）数据,I/O,统计,数据查询语句所花费的磁盘活动量也是主要的性能之一，通过设置,STATISTICS IO,选项，可以使,SQL,显示磁盘,IO,信息,SET STATISTICS IO|OFF,8.3,管理和维护索引,例,8.8,：给出执行学生成绩查询的,I/O,统计。,SET STATISTICS IO ON -,打开,I/O,统计,GO,SELECT*FROM,xs,A INNER JOIN,cj,B ON A.,学号,=B.,学号,GO,SET STATISTICS IO OFF -,关闭,I/O,统计,（,2,）索引的维护,在创建索引后，为了得到最佳的性能，必须,对索引进行维护。因为随着时间的推移，用户需,要在数据库上进行插入、更新和删除等一系列操,作，这将使数据变得支离破碎，从而造成索引性,能的下降。,8.3,管理和维护索引,1,）统计信息更新,8.3,管理和维护索引,在“选项”中设置,8.3,管理和维护索引,使用,UPDATE STATISTICS,命令更新,用户应该避免频繁地进行索引统计的更新，特别是数据库操作比较集中的时间内统计更新,8.3,管理和维护索引,UPDATE STATISTICS,xs,PK_xs,2,）使用,DBCC SHOWCONTIG,语句扫描表,对表进行数据操作可能会导致表碎片，而表碎片会导致额外的页读取，从而造成数据库性能的降低，这时用户可以通过,DBCC SHOWCONTIG,语句来扫描表，并通过其返回值确定该表的索引页是否已经严重不连续。,扫描索引的碎片信息：,进行碎片整理：,8.3,管理和维护索引,DBCC SHOWCONTIG,(,xs,PK_xs,),DBCC INDEXDEFRAG,(,xsgl,xs,PK_xs,),本章小结,索引能加快检索表中的数据：,SQL Server,中的索引按组织方式可分为聚集,索引和非聚集索引,习题,用,T-SQL,语句完成下列操作（数据类型、为空性等属性自己视情况而定）：,建立“学生选课”数据库,建立学生、课程和选课表。,建立各表以主键为索引项的索引。,