1、第8章 索引及其应用,索引的概述,1,创建索引,2,管理和维护索引,3,本章重点,索引的概念 索引的分类 索引的创建与删除,本章难点,索引概念的理解 索引的分类 索引的创建、删除等操作的T-SQL语法,第8章 索引及其应用,索引的概述,1,创建索引,2,管理和维护索引,3,8.1 索引的概述,在一大堆记录中要搜寻某条记录， 你是喜欢从头到尾一条一条地寻找，还是使用比较有效率的方法呢？在SQL Server中，我们可以针对一个或多个字段的数据做“索引”（Index），那么在寻找这些字段的数据时，就会比顺序搜寻要快很多。索引是什么？是数据库有什么好处？,索引概述,将书籍分门别类 英文书籍使用字母顺
2、序排列 中文书籍使用笔划多少来排列 管理员将这些数据记录在小卡片上整齐地放好，读者从这些整理好的小卡片中很快就能找到自己需要的书籍 这些小卡片就是整个图书管的索引,8.1 索引的概述,索引的结构,8.1 索引的概述,索引的意义索引在数据库中的作用与目录在书籍中的作用类似，都用来提高查找信息的速度。索引使程序无需浏览整个表，就可以快速找到所需的数据。 使用索引的代价 为每一列都建立索引是不明智的，因为使用索引要付出一定的代价： 索引需要占用数据表以外的物理存储空间。 创建索引和维护索引要花费一定的时间。 当对表进行更新操作时，索引需要被重建，这样降低 了数据的维护速度,8.1 索引的概述,索引的
3、分类 根据索引的存储结构不同将其分为两类： 聚集索引聚集索引是指表中数据行的物理存储顺序与索引顺序完全相同。聚集索引由上下两层组成，上层为索引页，包含表中的索引页面，用于数据检索，下层为数据页。 非聚集索引 非聚集索引不改变表中数据行的物理存储位置，数据与索引分开存储，通过索引带有的指针与表中的数据发生联系。,8.1 索引的概述,聚集索引,8.1 索引的概述,例如，在图书馆中存放着很多书，这些书可以按照作者顺序存放，也可以按照书名顺序存放，还可以按照书的出版社排序存放。假设现在这些书是杂乱存放的，并且在书名列上建立了聚集索引，那么这些书就必须按照书名的顺序重新排放，使得数据(书)按照索引排序。
4、这就是聚集索引。,8.1 索引的概述,创建聚集索引应该注意以下事项： 每张表只能有一个聚集索引。 创建聚集索引时所需的可用空间应是数据库表中数据量的120%。这是因为在创建聚集索引时表中的数据将被复制以便进行排序，排序完成后，再将旧的未加索引的表删除，所以数据库必须有足够的用来复制的空间。 主键是聚集索引的良好候选者。 缺省设置是非聚集索引。,8.1 索引的概述,非聚集索引,8.1 索引的概述,8.1 索引的概述,唯一索引唯一索引不允许两行具有相同的索引值。如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。,8.1 索引的概述,主键索引 主键索引要求主键中的每
5、个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。主键索引为聚集索引，也是唯一索引。,8.1 索引的概述,【提示】 一般情况下，先创建聚集索引，后创建非聚集索引，因为创建聚集索引会改变表中的行的顺序，从而会影响到非聚集索引。 创建多少个非聚集索引，取决于用户执行的查询要求。 【提示】 在确定某一索引适合某一查询之后，可以选择最适合具体情况的索引类型。索引包含以下特性： 聚集还是非聚集。 唯一还是非唯一。 单列还是多列。 索引中的列是升序排序还是降序排序。新名称必须遵守标识符规则。,第8章 索引及其应用,索引的概述,1,创建索引,2,管理和维护索引,3,1、系统自动创建索引在创建或修
6、改表时，如果添加了一个主键或惟一键约束，则系统将自动在该表上，以该键值作为索引列，创建一个惟一索引。该索引是聚集索引还是非聚集索引，要根据当前表中的索引状况和约束语句或命令而定。,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 U
7、NIQUE 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、男员工表”，在“男员工表”中按姓名创建一个唯一性聚集索引，并注意查看索引创建前后数据排序的变化。,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_goodsON goods（商品编号,商品名称）,第8章 索引及其
9、应用,索引的概述,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约束创建的索引。这些索引必须通过
10、删除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,单击“查询”|“显示估计执行计划”命令，完成显 示执行计
11、划的设置,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 xsWHERE 姓名 LIKE 林% GO SET SHOWPLAN_ALL OFF; GO,2）数据I/O统计数据查询语句所花费的磁盘活动量也是主要的性能之一，通过设置STATISTICS IO 选项，可以使SQL显示磁盘IO信息 SET STAT
12、ISTICS 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 管理和维护索引,在
13、“选项”中设置,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语句完成下列操作（数据类型、为空性等属性自己视情况而定）： 建立“学生选课”数据库 建立学生、课程和选课表。 建立各表以主键为索引项的索引。,
