索引及其应用

1、第第7 7章章 索引及其应用索引及其应用7.1 索引的基础知识n索引是数据库的一种对象，它保存数索引是数据库的一种对象，它保存数 据库据库表中一列或多列组合的排序。表中一列或多列组合的排序。n增加索引可以提高数据的检索效率。增加索引可以提高数据的检索效率。7.1.1 数据存储n数据存储的基本单位：页； 大小：8kb； 作用：存储系统信息； sql数据库的数据文件包含8种页类型。7.1.2 7.1.2 索引索引1. 1. 索引的概念索引的概念2. 2. 使用索引的意义使用索引的意义3. 3. 使用索引的代价使用索引的代价4. 4. 建立索引的原则建立索引的原则 可以利用索引快速访问数据库表中的特

2、定信息。索引是对数据库表中一个或多个列（例如，学生表的姓名 (lname) 列）的值进行排序的结构。如果想按特定学生的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。 索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。系部系部代码代码系部系部名称名称系主任系主任05社会科学部社会科学部刘克思刘克思02经济管理系经济管理系刘国峰刘国峰08建筑系建筑系王未起王未起04基础科学部基础科学部王彬王彬03传播技术系传播技术系田建国田建国07农林系农林系陈瑞

3、修陈瑞修06机电工程系机电工程系王伟东王伟东01计算机系计算机系刘海军刘海军82547系部代码系部代码指针地址指针地址 0102030405106076083系部代码系部代码”索引索引 “ “系部系部”表表说明： 在数据库关系图中，可以为选定的表创建、编辑或删除索引/键属性页中的每个索引类型。当保存附加在此索引上的表或包含此表的数据库关系图时，索引同时被保存。有关详细信息，请参见创建索引。 通常情况下，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引将占用磁盘空间，并且降低添加、删除和更新行的速度。不过在多数情况下，索引所带来的数据检索速度的优势大大超过它的不足之处。然而，如果应用程

4、序非常频繁地更新数据，或磁盘空间有限，那么最好限制索引的数量建立索引的原则建立索引的原则: 1. 1. 主键列上一定要建立索引；主键列上一定要建立索引；2. 2. 外键列可以建立索引；外键列可以建立索引；3. 3. 在经常查询的字段上最好建立索引；在经常查询的字段上最好建立索引；4. 4. 在查询中很少涉及并且重复值比在查询中很少涉及并且重复值比 较多的列不要建立索引。较多的列不要建立索引。7.2 索引的分类索引的分类7.2.1 7.2.1 聚集索引聚集索引 聚集索引确定表中数据的物理顺序（物理聚集索引确定表中数据的物理顺序（物理存储顺序与索引顺序完全相同）。聚集索引类似存储顺序与索引顺序完全

5、相同）。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组（组合索引），就像电话簿按姓氏和名字进行组织一样。织一样。 当索引值唯一时，使用聚集索引查找特定当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一学号的行也很有效率。例如，使用唯一学号 ID ID 列列 emp_idemp_id 查找特定学生的最快速的方法，是在查找特定

6、学生的最快速的方法，是在 emp_idemp_id 列上创建聚集索引或列上创建聚集索引或 PRIMARY KEY PRIMARY KEY 约约束（系统自动建立创建聚集索引）。束（系统自动建立创建聚集索引）。说明： 定义聚集索引键时使用的列越少越好，这定义聚集索引键时使用的列越少越好，这一点很重要。一点很重要。 在创建聚集索引之前，应先了解您的数据在创建聚集索引之前，应先了解您的数据是如何被访问的。可考虑将聚集索引用于：是如何被访问的。可考虑将聚集索引用于： 1.1.包含大量非重复值的列。包含大量非重复值的列。 2.2.被连续访问的列。被连续访问的列。聚集索引不适用于：聚集索引不适用于： 频繁更

7、改的列，这将导致整行移动（因为频繁更改的列，这将导致整行移动（因为 SQL Server SQL Server 必须按物理顺序保留行中的数据必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。处理系统中数据是易失的。 7.2.2 7.2.2 非聚集索引非聚集索引 非聚集索引与课本中的索引类似。数据存非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索有指针指向数据的存储位置。索引中的项目按索引键值的顺序

8、存储，而表中的信息按另一种顺序引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。如果在表中未存储（这可以由聚集索引规定）。如果在表中未创建聚集索引，则无法保证这些行具有任何特定创建聚集索引，则无法保证这些行具有任何特定的顺序。的顺序。 例如，对于在例如，对于在 emp_idemp_id 列上有非聚集索引列上有非聚集索引的表，如要搜索其学生的表，如要搜索其学生 ID (emp_idID (emp_id) )，SQL SQL Server Server 会在索引中查找这样一个条目，该条目会在索引中查找这样一个条目，该条目精确列出匹配的精确列出匹配的 emp_idemp_id

9、列在表中的页和行，列在表中的页和行，然后直接转到该页该行。然后直接转到该页该行。7.3 索引的操作索引的操作方法：方法：（1 1）利用企业管理器中的索引向导创建索引。）利用企业管理器中的索引向导创建索引。（2 2）利用企业管理器直接创建索引。）利用企业管理器直接创建索引。（3 3）利用）利用Transact-SQLTransact-SQL语句中的语句中的CREATE INDEXCREATE INDEX命命 令创建索引。令创建索引。（4 4）利用企业管理器中的索引优化向导创建索引。）利用企业管理器中的索引优化向导创建索引。 7.3.1 7.3.1 创建索引创建索引利用利用Transact-SQL

10、Transact-SQL语句中的语句中的CREATE INDEXCREATE INDEX命令创建索引。命令创建索引。 语法格式：语法格式：CREATE UNIQUE CLUSTERED | CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX NONCLUSTERED INDEX index_nameindex_name ON ON tabletable ( ( column column ASC | DESC ASC | DESC ,. ,.n n ) ) 说明：n UNIQUE CLUSTERED | UNIQUE CLUSTERED | NONCLUS

11、TERED NONCLUSTERED ：指定创建索引的类型，：指定创建索引的类型，其依次为惟一索引、聚集索引和非聚集索其依次为惟一索引、聚集索引和非聚集索引。引。n当省略当省略UNIQUEUNIQUE选项时，建立的是非惟一索选项时，建立的是非惟一索引，省略引，省略 CLUSTERED | NONCLUSTERED CLUSTERED | NONCLUSTERED ，创建非聚集索引。创建非聚集索引。n例例7-17-1：n为班级表中创建基于为班级表中创建基于“系部代码系部代码”列的非列的非聚集索引聚集索引aaaa。代码：代码：use stuuse stugogocreate index aacre

12、ate index aa on on 班级表（系部代码）班级表（系部代码）gogon课堂练习：课堂练习：1. 1. 为学生表中的姓名一列创建非聚集索为学生表中的姓名一列创建非聚集索 引，名字自定。引，名字自定。2. 2. 为学生表中的学号一列创建聚集索引。为学生表中的学号一列创建聚集索引。7.3.2 查询索引信息查询索引信息语法格式：语法格式：exec sp_helpindexexec sp_helpindex 表名表名例例7-27-2：查看数据库中：查看数据库中“教师表教师表”的索的索 引信息。引信息。use stuuse stugogoexec sp_helpindexexec sp_he

13、lpindex 教师表教师表gogo7.3.3 7.3.3 索引更名索引更名 更改当前数据库中用户创建对象更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。（如表、列或用户定义数据类型）的名称。语法格式：语法格式：sp_rename sp_rename objname objname= = object_nameobject_name , , newname newname = = new_namenew_name , objtype , objtype = = object_typeobject_type 说明：nobject_nameobject_name是对象的原名。是对

14、象的原名。nnew_namenew_name是对象更改后的名称。是对象更改后的名称。 object_typeobject_type对象的类型。对象的类型。n例例7-37-3：将将stustu数据库中学生表的数据库中学生表的aaaa索引名更改为索引名更改为bbbb。use stuuse stugogoexec sp_rename aaexec sp_rename aa , bb , bbgogo7.3.4 7.3.4 删除索引删除索引语法格式：语法格式：DROP INDEX DROP INDEX 表名表名. .索引名索引名例例7-47-4： 删除学生表的删除学生表的bbbb索引。索引。drop

15、index drop index 学生表学生表.bb.bbgogo课堂练习：课堂练习：1. 1. 为教学计划表创建一个基于为教学计划表创建一个基于“课程号、课程号、 专业代码专业代码”组合列的惟一、聚集、复组合列的惟一、聚集、复合合 索引索引aa_indexaa_index； 2. 2. 查看这个索引的定义；查看这个索引的定义；3. 3. 把把aa_indexaa_index索引更名为索引更名为bb_indexbb_index索引；索引；4. 4. 删除删除bb_indexbb_index索引。索引。课后思考：nP170-1.2.3.4题小结： 聚集索引对表的物理数据页中的数据按列聚集索引对表

16、的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索进行排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的，它的叶节点中存储的是引与数据是混为一体的，它的叶节点中存储的是实际的数据。实际的数据。 非聚集索引具有完全独立于数据行的结构，非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引的叶节点存储了组成非聚集索排序。非聚集索引的叶节点存储了组成非聚集索引的关键字值和行定位器。引的关键字值和行定位器。 7.4 设置索引的选项设置索引的选项语法语法: :CREATE UNIQUE CLUST

17、ERED | NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,.n ) WITH PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | PAD_INDEXPAD_INDEX 指定索引中间级中每个页（节点）上保持开放的空间。PAD_INDEX 选项只有在指定了 FILLFACTOR 时才有用，因为 PAD_INDEX 使用由 FILLFACTOR 所指定的百分比。默认情况下，给定中间级页上的键集，SQL Server 将确保每个索引页上的可用空间至少可以容

18、纳一个索引允许的最大行。如果为 FILLFACTOR 指定的百分比不够大，无法容纳一行，SQL Server 将在内部使用允许的最小值替代该百分比。 FILLFACTOR = fillfactorFILLFACTOR = fillfactor 指定在 SQL Server 创建索引的过程中，各索引页叶级的填满程度。如果某个索引页填满，SQL Server 就必须花时间拆分该索引页，以便为新行腾出空间，这需要很大的开销。对于更新频繁的表，选择合适的 FILLFACTOR 值将比选择不合适的 FILLFACTOR 值获得更好的更新性能。 如果指定了 FILLFACTOR，SQL Server 会向

19、上舍入每页要放置的行数。例如，发出 CREATE CLUSTERED INDEX .FILLFACTOR = 33 将创建一个 FILLFACTOR 为 33% 的聚集索引。假设 SQL Server 计算出每页空间的 33% 为 5.2 行。SQL Server 将其向上舍入，这样，每页就放置 6 行。 用户指定的 FILLFACTOR 值可以从 1 到 100。如果没有指定值，默认值为 0。如果 FILLFACTOR 设置为 0，则只填满叶级页。 可以通过执行 sp_configuresp_configure 更改默认的 FILLFACTOR 设置。如果 FILLFACTOR 值较小（0

20、除外），就会使 SQL Server 创建叶级页不完全填充的新索引。FILLFACTOR 为 10 会是合理的选择。FILLFACTOR 值较小还会使索引占用较多的存储空间。IGNORE_DUP_KEYIGNORE_DUP_KEY 控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。如果为索引指定了 IGNORE_DUP_KEY，并且执行了创建重复键的 INSERT 语句，SQL Server 将发出警告消息并忽略重复的行。例子: 为班级表创建基于“系部代码”列的非聚集索引aa，其fillfactor和pad_index选项值均为60use stugocreate index aa

21、on 班级表（系部代码） with pad_index,fillfactor=60go课堂练习： 为课程注册表创建一个基于“课程号，专业代码”组合列的惟一、非聚集、复合索引aa，其fillfactor和pad_index选项值均为60.7.5 索引的分析与维护索引的分析与维护7.5.1 7.5.1 索引的分析索引的分析1. showplan1. showplan 语法格式：set showplan_allon/off 和set showplan_text on/off 当 SET SHOWPLAN_ALL 为 ON 时，SQL Server 返回每个语句的执行信息但不执行语句，而且 Trans

22、act-SQL 语句将不执行，直到将该选项设置为 OFF 为止(off_显示索引后的记录顺序)。 SET SHOWPLAN_ALL 将信息作为行集返回，行集形成一个层次结构树，用以表示 SQL Server 查询处理器在执行每个语句时所采取的步骤。 例子：查询学生表上所有男生的姓名use stugoset showplan_all on(该为off再查看结果)goselect 姓名from 学生表where 性别=男 下表显示输出中包含的列。 列名描述NodeId 当前查询内的节点 ID。 StmtId 当前批处理中的语句数。PhysicalOp节点的物理实现算法。仅限于 PLAN_ROWS

23、类型的行。EstimateCPU该运算符的预计 CPU 成本。仅限于 PLAN_ROWS 类型的行。例：USE stuGOSET SHOWPLAN_ALL ONGO- First query.select 姓名from 学生表where 性别=男 GO- Second query.select 姓名from 学生表WHERE 姓名 LIKE 张GOSET SHOWPLAN_ALL OFFGO2. statistics io2. statistics io 显示关于 Transact-SQL 语句生成的磁盘活动量的信息。语法语法: : SET STATISTICS IO ON | OFF SET

24、 STATISTICS IO ON | OFF 当 STATISTICS IO 为 ON 时，显示统计信息。为 OFF 时，不显示统计信息。将该选项设置为 ON 后，所有的后续 Transact-SQL 语句将返回统计信息，直到将该选项设置为 OFF 为止。输出项输出项含义含义Table表的名称scan count执行的扫描次数logical reads从数据高速缓存读取的页数physical reads从磁盘读取的页数read-ahead reads为查询放入高速缓存的页数。有五个输出项例子：查询学生表上所有男生的姓名use stugoset showplan_all ongoSET STA

25、TISTICS IO ONSET STATISTICS IO ON go select 姓名from 学生表where 性别=男7.5.2 7.5.2 索引的维护索引的维护1. DBCC SHOWCONTIG ( table_name | table_id 1. DBCC SHOWCONTIG ( table_name | table_id |view_name | view_id |view_name | view_id , index_name | index_id , index_name | index_id ) ) table_name | table_id | view_name

26、| view_idtable_name | table_id | view_name | view_id: : 是要对其碎片信息进行检查的表或视图。如果是要对其碎片信息进行检查的表或视图。如果未指定，则对当前数据库中的所有表和索引视图进未指定，则对当前数据库中的所有表和索引视图进行检查。行检查。index_name | index_idindex_name | index_id是要对其碎片信息是要对其碎片信息进行检查的索引。进行检查的索引。 DBCC SHOWCONTIG 确定表是否高度碎片化。在对表进行数据修改（INSERT、UPDATE 和 DELETE 语句）的过程中会出现表碎片现象。由于这些修改通常并不在表的行中进行平均分布，所以每页的填满状态会随时间而改变。对于扫描部分或全部表的查询，这些表碎片会导致额外的页读取，这将妨碍数据的并行扫描。 当索引的碎片非常多时，有两种减少碎片的方法： 1.除去并重新创建聚集索引。 2.使用 DBCC INDEXDEFRAG 按逻辑顺序重新排序索引的叶级页。例1:查看学生表的碎片信息DB