第13章 规划索引.docx
- 文档编号:6470325
- 上传时间:2023-01-06
- 格式:DOCX
- 页数:21
- 大小:25.13KB
第13章 规划索引.docx
《第13章 规划索引.docx》由会员分享,可在线阅读,更多相关《第13章 规划索引.docx(21页珍藏版)》请在冰豆网上搜索。
第13章规划索引
第13章规划索引
本章概述
本章是理论性相当强的一章。
学生在本章中将全面了解索引对数据库性能提升的原理和作用,为后面章节的学习打下基础。
并为将来的工作中的高端任务——性能调整打下坚实的理论基础。
学生将学习不同的索引类型,并学习如何分析数据库现状并进行相应的索引规划达到优化应用的目的。
由于本章内容对于目前的学生来讲略显艰深,建议只要学生掌握初步分析方法即可。
数据库性能优化主要要靠经验,需要长期大量企业实践的积累才能做到掌握自如。
教学目标
●了解为何以及何时使用索引,索引的使用能够极大地提高企业数据库的性能
●了解SQLServer如何使用聚集索引和非聚集索引
●了解SQLServer索引架构如何使数据的检索更容易
●了解SQLServer如何维护索引和堆
●了解在决定对哪些列进行索引时,数据的选择性、密度和分布情况的重要性,这些数据对于数据库程序员在决定索引策略时有至关重要的作用。
教学重点
●决定需要索引的列
●索引的类型
●学习分析统计数据决定索引策略
教学难点
●理解SQLServer中索引的架构
●理解索引的实现原理
●理解索引对性能的影响,对数据库进行分析从而决定要索引的列和索引类型的方法
教学资源
课本
知识点
13.1索引介绍
13.2索引架构
13.3SQLServer检索存储的数据的方法
13.4SQLServer维护索引和堆结构的方法
13.5决定需要索引的列
13.6推荐操作
实验
实验13-1:
使用sp_help识别索引
实验13-2:
查看sysindexes表中的项
习题
习题1-对应13.3SQLServer检索存储的数据的方法
习题2-对应13.3SQLServer检索存储的数据的方法
习题3-对应13.5决定需要索引的列
习题4-对应13.4SQLServer维护索引和堆结构的方法
习题5-对应本章综合知识
习题6-对应13.3SQLServer检索存储的数据的方法
习题7-对应13.4SQLServer维护索引和堆结构的方法
习题8-对应13.3SQLServer检索存储的数据的方法
习题9-对应本章综合知识
习题10-对应本章综合知识
教师光盘
实验文件
Labfiles\allfiles.exe
习题解答
tPrep\answer.doc
教学指导手册包
新版幻灯片
\Powerpnt\PPT_2071_2073_13.ppt
多媒体视频
\Powerpnt\indexing.avi
先修知识
在正式开始学习本章内容以前,学生须具备下列知识基础。
知识基础
推荐补充
数据结构的基本知识
《数据结构》
建议学时
课堂教学(4课时)+实验教学(4课时)
教学过程
13.1索引简介
教学提示:
本节主要达到一个目的。
●了解使用索引的基本概念、索引的作用和使用索引的场合,这些经验对于刚刚接触索引概念的数据库程序员非常重要,在缺乏经验的情况下也能够对SQLServer性能作出好的调整。
(略讲)
内容
教学方法
提示
讲授:
数据库中的索引与书籍中的索引类似。
在一本书中,利用索引可以快速查找所需信息,无须阅读整本书。
在数据库中,索引使数据库程序无须对整个表进行扫描,就可以在其中找到所需数据。
书中的索引是一个词语列表,其中注明了包含各个词的页码。
而数据库中的索引是一个表中所包含的值的列表,其中注明了表中包含各个值的行所在的存储位置。
可以为表中的单个列建立索引,也可以为一组列建立索引;索引采用B树结构(B树即平衡树,它的特点是根节点到每个叶子节点的路径长度都相同,所以被形象的称作平衡树。
有关平衡树的更多资料,请参考《数据结构》)。
索引包含一个条目,该条目有来自表中每一行的一个或多个列(搜索关键字)。
B树按搜索关键字排序,可以在搜索关键字的任何子词条集合上进行高效搜索。
例如,对于一个A、B、C列上的索引,可以在A以及A、B和A、B、C上对其进行高效搜索。
大多数书中包含一个关于词汇、名称、地点等等的总索引。
数据库则包含分别关于所选类型或数据列的索引:
这好比在一本书中分别为人名和地名建立索引。
当创建数据库并优化其性能时,应该为数据查询所使用的列创建索引。
在随Microsoft®SQLServer™2000提供的pubs示例数据库中,employee表在emp_id列上有一个索引。
当SQLServer执行一个语句,在employee表中根据指定的emp_id值查找数据时,它能够识别emp_id列的索引,并使用该索引查找所需数据。
如果该索引不存在,它会从表的第一行开始,逐行搜索指定的emp_id值。
SQLServer为某些类型的约束(如PRIMARYKEY和UNIQUE约束)自动创建索引。
可以通过创建不依赖于约束的索引,进一步对表定义进行自定义。
不过,索引为性能所带来的好处却是有代价的。
带索引的表在数据库中会占据更多的空间。
另外,为了维护索引,对数据进行插入、更新、删除操作的命令所花费的时间会更长。
在设计和创建索引时,应确保对性能的提高程度大于在存储空间和处理资源方面的代价。
演示:
使用企业管理器查看pubs数据库中的索引
阅书:
13.1
幻灯:
第3-6页
●补充读物:
13.2索引架构
教学提示:
本节主要达到一个目的。
●了解索引的架构和两种类型:
聚集索引和非聚集索引。
(略讲)
内容
教学方法
提示
讲授:
聚集索引确定表中数据的物理顺序。
聚集索引类似于电话簿,后者按姓氏排列数据。
由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。
但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。
聚集索引对于那些经常要搜索范围值的列特别有效。
使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。
例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。
这样有助于提高此类查询的性能。
同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节省成本。
当索引值唯一时,使用聚集索引查找特定的行也很有效率。
例如,使用唯一雇员ID列emp_id查找特定雇员的最快速的方法,是在emp_id列上创建聚集索引或PRIMARYKEY约束。
如果该表上尚未创建聚集索引,且在创建PRIMARYKEY约束时未指定非聚集索引,PRIMARYKEY约束会自动创建聚集索引。
也可以在lname(姓氏)列和fname(名字)列上创建聚集索引,因为雇员记录常常是按姓名而不是按雇员ID分组和查询的。
注意事项:
定义聚集索引键时使用的列越少越好,这一点很重要。
如果定义了一个大型的聚集索引键,则同一个表上定义的任何非聚集索引都将增大许多,因为非聚集索引条目包含聚集键。
当把SQL脚本保存到可用空间不足的磁盘上时,索引优化向导不返回错误。
在分析过程中,索引优化向导会消耗相当多的CPU及内存资源。
最好在生产服务器(生产服务器即实际工作中使用的服务器,投入生产环境的服务器,比如公司的Email服务器就是生产服务器。
这些服务器要求非常稳定,所以我们不可以在生产服务器上作任何测试工作,必需在测试服务器上通过了严格测试以后才能发布到生产服务器上)的测试版上执行优化,而不要在生产服务器上执行。
此外,最好在另一台计算机上而非运行SQLServer的计算机上运行该向导。
该向导不能用于在SQLServer6.5版或更早版本的数据库中选择或创建索引及统计信息。
在创建聚集索引之前,应先了解您的数据是如何被访问的。
可考虑将聚集索引用于:
-包含大量非重复值的列。
-使用下列运算符返回一个范围值的查询:
BETWEEN、>、>=、<和<=。
-被连续访问的列。
-返回大型结果集的查询。
-经常被使用联接或GROUPBY子句的查询访问的列;一般来说,这些是外键列。
对ORDERBY或GROUPBY子句中指定的列进行索引,可以使SQLServer不必对数据进行排序,因为这些行已经排序。
这样可以提高查询性能。
-OLTP类型的应用程序,这些程序要求进行非常快速的单行查找(一般通过主键)。
应在主键上创建聚集索引。
聚集索引不适用于:
-频繁更改的列
这将导致整行移动(因为SQLServer必须按物理顺序保留行中的数据值)。
这一点要特别注意,因为在大数据量事务处理系统中数据是易失的。
-宽键
来自聚集索引的键值由所有非聚集索引作为查找键使用,因此存储在每个非聚集索引的叶条目内。
阅书:
13.2.1-13.2.2
幻灯:
第8-12页
讲授:
非聚集索引与课本中的索引类似。
数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置。
索引中的项目按索引键值的顺序存储,而表中的信息按另一种顺序存储(这可以由聚集索引规定)。
如果在表中未创建聚集索引,则无法保证这些行为具有任何特定的顺序。
其实,我们的汉语字典的正文本身就是一个聚集索引。
比如,我们要查“安”字,就会很自然地翻开字典的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的,那么“安”字就自然地排在字典的前部。
如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字;同样的,如果查“张”字,那您也会将您的字典翻到最后部分,因为“张”的拼音是“zhang”。
也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。
我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
如果您认识某个字,您可以快速地从自动中查到这个字。
但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。
但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。
很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。
我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。
我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。
与使用书中索引的方式相似,Microsoft®SQLServer™2000在搜索数据值时,先对非聚集索引进行搜索,找到数据值在表中的位置,然后从该位置直接检索数据。
这使非聚集索引成为精确匹配查询的最佳方法,因为索引包含描述查询所搜索的数据值在表中的精确位置的条目。
如果基础表使用聚集索引排序,则该位置为聚集键值;否则,该位置为包含行的文件号、页号和槽号的行ID(RID)。
例如,对于在emp_id列上有非聚集索引的表,如要搜索其雇员ID(emp_id),SQLServer会在索引中查找这样一个条目,该条目精确列出匹配的emp_id列在表中的页和行,然后直接转到该页该行。
多个非聚集索引:
有些书籍包含多个索引。
例如,一本介绍园艺的书可能会包含一个植物通俗名称索引,和一个植物学名索引,因为这是读者查找信息的两种最常用的方法。
对于非聚集索引也是如此。
可以为在表中查找数据时常用的每个列创建一个非聚集索引。
注意事项:
在创建非聚集索引之前,应先了解您的数据是如何被访问的。
可考虑将非聚集索引用于:
-包含大量非重复值的列,如姓氏和名字的组合(如果聚集索引用于其它列)。
如果只有很少的非重复值,如只有1和0,则大多数查询将不使用索引,因为此时表扫描通常更有效。
-不返回大型结果集的查询。
-返回精确匹配的查询的搜索条件(WHERE子句)中经常使用的列。
-经常需要联接和分组的决策支持系统应用程序。
应在联接和分组操作中使用的列上创建多个非聚集索引,在任何外键列上创建一个聚集索引。
-在特定的查询中覆盖一个表中的所有列。
这将完全消除对表或聚集索引的访问。
阅书:
13.2.3
幻灯:
第13-14页
●建议参考联机丛书中的内容作为补充。
同时参考13.1中的补充读物。
(这里不推荐使用非官方的参考资料,但是建议老师通过google查找一些经验文章)
13.3SQLServer检索存储的数据的方法
教学提示:
本节主要达到两个目的。
●了解sysindexes系统表的作用。
(略讲)
●了解不使用索引、使用非聚集索引、使用聚集索引和使用基于聚集索引的非聚集索引查找行时的不同过程。
(精讲)
内容
教学方法
提示
讲授:
Master数据库中的sysindexes表内的页指针定位表、索引和索引视图的所有页集合。
每个表和索引视图有一个数据页集合,以及其它一些实现为这个表或视图定义的各个索引的页集合。
每个表、索引和索引视图在sysindexes内有一行,由对象标识符(id)列和索引标识符(indid)列的组合唯一标识。
IAM页链(IAM是索引分配映射表的缩写,由于这些表采用页的结构进行存储,同时使用链表结构将其连接,所以称为IAM页链)管理分配给表、索引和索引视图的页。
sysindexes.FirstIAM列指向IAM页链的IAM首页,IAM页链管理分配给表、索引或索引视图的空间。
每个表在sysindexes内有一个行集:
-一个堆集在sysindexes内有一行,其indid=0。
FirstIAM列指向表的数据页集合的IAM链。
服务器使用IAM页查找数据页集合内的页,因为这些页不链接在一起。
-某个表或视图上的一个聚集索引在sysindexes内有一行,其indid=1。
root列指向聚集索引B树的顶端。
服务器使用索引B树查找数据页。
-为某个表或视图创建的每个非聚集索引在sysindexes内有一行。
每个非聚集索引的行内的indid值都从2到250。
root列指向非聚集索引B树的顶端。
-对于每个至少有一个text、ntext或image列的表,在sysindexes内也有一行,其indid=255。
FirstIAM列指向管理text、ntext和image页的IAM页链。
阅书:
13.3.1
幻灯:
第16-17页
●对于不太清楚的术语请查询联机帮助
讲授:
通过图13-2我们可以看到,不使用索引查找行的过程是简单而低效的。
阅书:
13.3.2
幻灯:
第18页
●结合图13-2解释过程
讲授:
非聚集索引与聚集索引一样有B树结构,但是有两个重大差别:
-数据行不按非聚集索引键的顺序排序和存储。
-非聚集索引的叶层不包含数据页。
相反,叶节点(叶节点就是树结构中没有子节点的节点,具体请参考《数据结构》)包含索引行。
每个索引行包含非聚集键值以及一个或多个行定位器,这些行定位器指向有该键值的数据行(如果索引不唯一,则可能是多行)。
非聚集索引可以在有聚集索引的表、堆集或索引视图上定义。
在Microsoft®SQLServer™2000中,非聚集索引中的行定位器有两种形式:
-如果表是堆集(堆是基本数据结构,通常来说它就是一块内存块)(没有聚集索引),行定位器就是指向行的指针。
该指针用文件标识符(ID)、页码和页上的行数生成。
整个指针称为行ID。
-如果表没有聚集索引,或者索引在索引视图上,则行定位器就是行的聚集索引键。
如果聚集索引不是唯一的索引,SQLServer2000将添加在内部生成的值以使重复的键唯一。
用户看不到这个值,它用于使非聚集索引内的键唯一。
SQLServer通过使用聚集索引键搜索聚集索引来检索数据行,而聚集索引键存储在非聚集索引的叶行内。
由于非聚集索引将聚集索引键作为其行指针存储,因此使聚集索引键尽可能小很重要。
如果表还有非聚集索引,请不要选择大的列作为聚集索引的键。
阅书:
13.3.3
幻灯:
第19-20页
●结合图13-3解释过程
讲授:
聚集索引在sysindexes内有一行,其indid=1。
数据链内的页和其内的行按聚集索引键值排序。
所有插入都在所插入行中的键值与排序顺序相匹配时执行。
Microsoft®SQLServer™2000将索引组织为B树。
索引内的每一页包含一个页首,页首后面跟着索引行。
每个索引行都包含一个键值以及一个指向较低级页或数据行的指针。
索引的每个页称为索引节点。
B树的顶端节点称为根节点(根节点是树的顶端节点,它没有父节点,具体请参照《数据结构》)。
索引的底层节点称为叶节点。
每级索引中的页链接在双向链接列表中。
在聚集索引内数据页组成叶节点。
根和叶之间的任何索引级统称为中间级。
对于聚集索引,sysindexes.root指向它的顶端。
SQLServer沿着聚集索引浏览以找到聚集索引键对应的行。
为找到键的范围,SQLServer浏览索引以找到这个范围的起始键值,然后用向前或向后指针扫描数据页。
为找到数据页链的首页,SQLServer从索引的根节点开始沿最左边的指针进行扫描。
阅书:
13.3.4
幻灯:
第21-22页
●结合图13-4解释过程
讲述:
下面我们看看如何在使用聚集索引和非聚集索引的情况下检索数据行。
阅书:
13.3.5
幻灯:
第23-24页
●结合图13-5解释过程
13.4SQLServer维护索引和堆结构的方法
教学提示:
本节主要达到一个目的。
●了解在插入、更新和删除行时,SQLServer如何维护索引和堆。
(精讲)
内容
教学方法
提示
讲述:
下面我们通过图13-6看一看索引中页拆分的过程。
讲授课本
阅书:
13.4.1
幻灯:
第26-28页
●结合图13-6进行讲解
讲述:
下面我们通过图13-7看一看堆中的转向指针的作用。
讲授课本
阅书:
13.4.2
幻灯:
第29-30页
●结合图13-7进行讲解
下面我们通过图13-9看一看SQL中行更新和删除时发生的事情。
讲授课本
阅书:
13.4.3,13.4.4
幻灯:
第31-32页
●结合图13-9进行讲解。
●补充阅读:
13.5决定需要索引的列
教学提示:
本节主要达到一个目的。
●掌握决定需要索引的列的方法。
(精讲)
内容
教学方法
提示
讲述:
不是所有的索引都会对数据库性能有所改善。
所以,我们需要学习设计有用的索引。
讲授课本
阅书:
13.5
幻灯:
第34-41页
补充阅读:
13.6推荐操作
教学提示:
本节主要达到一个目的。
●了解经验方法。
(略讲)
内容
教学方法
提示
讲授课本
阅书:
13.6
幻灯:
第43页
总结
经过本章的学习,我们了解了下列的知识和内容。
●为何以及何时使用索引
●SQLServer如何使用聚集索引和非聚集索引
●SQLServer索引架构如何使数据的检索更容易
●SQLServer如何维护索引和堆
●在决定对哪些列进行索引时,数据的选择性、密度和分布情况的重要性
本章是对索引理论基础的概述,在第十四章中,我们将具体学习索引的创建和维护相关操作。
随堂练习
1.在一个使用SQLServer2000创建的数据表Products中,ProductID列是主键,并且在ProductID列上创建了clustered索引。
大多数查询使用Catalog和Kind两列。
很少有一些产品的Kind相同,但是属于同一个Catalog的产品很多。
请问应当如何提高查询效率?
A.在Products表上创建视图
B.使用存储过程返回数据
C.在Kind和Catalog上创建nonclustered索引,将Kind作为索引的第一列
D.在Kind和Catalog上创建nonclustered索引,将Catalog作为索引的第一列
正确答案:
(C)
分析:
由于大多数查询使用Catalog和Kind两列,我们应当在这两列上创建索引包含着两列。
我们应当选择不同值较多的列作为索引,这样SQLServer会更快地找到正确的那一行数据。
在本题中,由于Catalog的不同数据较少,因此选择Kind作为第一列。
2.在数据库中一些存储过程包含复杂的查询,需要连接多个表,每个表包含至少一个索引,需要优化这些SQL存储过程,应当如何做?
A.查询分析器中执行存储过程,使用ShowExecutionPlan选项
B.查询分析器中执行存储过程,使用ShowServerTrace选项
C.使用索引调整向导处理查询分析器产生的跟踪文件
D.更新存储过程中使用到的索引数据
正确答案:
(B)
分析:
当存储过程更新后,该存储过程包含复杂的查询。
新的存储过程的性能比原来降低了。
我们使用SQL查询分析器分析和调整存储过程。
ShowServerTrace命令提供了检查查询在服务器产生的影响的访问方法。
3.在设计SQLServer2000数据库时,根据查询报告,设计人员设计创建了数据表的索引,为了测试设计的正确性,请问应如何做?
A.在查询分析器中执行所有查询,使用显示执行计划选项
B.针对负载文件运行索引调整向导
C.执行DBCCSHOW_STATISTICS语句
D.在查询分析器中执行所有查询,并使用SHOWPLAN_TEXT选项
正确答案:
(B)
4.在设计SQLServer2000数据库时,设计人员设计创建了访问已经添加索引的数据表的存储过程,为了保证设计不会对数据库产生负面影响。
应当如何优化?
A.为存储过程中的每个查询创建一个索引
B.为存储过程中的每个查询WHERE子句包含的列创建一个索引
C.使用索引调整向导的输出确定应当添加哪些索引
D.在所有查询列和WHERE子句中出现的列上执行CREATESTATISTICS
正确答案:
(C)
分析:
在本题中我们要达到两个目标:
1保证新的存储过程的性能被优化
2保证数据库日常的操作不会被影响
这两个目标是矛盾的
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第13章 规划索引 13 规划 索引
![提示](https://static.bdocx.com/images/bang_tan.gif)