SQL_SERVER_2005之9索引和查询优化.ppt_第1页
SQL_SERVER_2005之9索引和查询优化.ppt_第2页
SQL_SERVER_2005之9索引和查询优化.ppt_第3页
SQL_SERVER_2005之9索引和查询优化.ppt_第4页
SQL_SERVER_2005之9索引和查询优化.ppt_第5页
已阅读5页,还剩40页未读, 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

1、2020年7月31日,第1页,第9章索引和查询优化,学习目标本章重点介绍本章,2020年7月31日,第2页,学习目标,数据库管理系统通常使用索引技术来加快表中数据的检索。索引类似于图书目录。目录允许用户根据页数快速找到他们想要的东西,而无需浏览整本书。在数据库中,索引还允许数据库应用程序快速查找表中的特定数据,而无需扫描整个数据库。在书籍中,目录是内容和相应页码的列表。在数据库中,索引是表中的数据列表和相应的存储位置。本章将详细研究索引和查询优化的内容。2020年7月31日,第3页,本章的重点,索引的特征,索引的类型,创建索引,优化索引,优化查询,2020年7月31日,第4页,本章的内容,9.

2、1概述9.2索引的类型和特征9.3创建索引9.4索引维护9.5查询优化9.6实践练习9.7练习,2020年7月31日,第5页,本章的内容, 9.1概述9.2索引的类型和特征9.3创建索引9.4索引维护9.5查询优化9.6实践练习9.7,2020年7月31日,第6页,9.1概述在微软的SQL Server系统中,最小的可管理空间是页面。 页面是8KB字节的物理空间。插入数据时,数据将根据插入时间顺序放置在数据页上。通常,放置数据的顺序和数据本身的逻辑关系之间没有联系。因此,就数据之间的逻辑关系而言,数据堆积得一塌糊涂。这种堆叠数据的方式称为堆。当一个数据页上的数据堆积起来时,数据必须堆积在另一个

3、数据页上,这称为页分解。2020年7月31日,第7页,为什么要创建索引?索引是与表或视图相关联的物理结构,可用于加速从表或视图中检索数据行。为什么要创建索引?这是因为创建索引可以极大地提高系统的性能。首先,通过创建唯一的索引,可以保证每行数据的唯一性。其次,它可以大大加快数据的检索速度,这也是索引的主要原因。第三,它可以加速表之间的连接,特别是在实现数据的引用完整性方面。第四,当使用ORDER BY和GROUP BY子句进行数据检索时,它还可以显著减少查询中分组和排序的时间。第五,通过使用索引,可以在查询过程中使用优化的隐藏设备,提高系统的性能。正是由于这些原因,索引应该添加到表中。2020年

4、7月31日,第8页,为什么不为表中的每一列创建一个索引?有些人可能会问为什么添加索引有这么多优点,那么为什么不为表中的每一列创建一个索引呢?虽然索引有很多优点,但是在表中的每一列都添加索引是不明智的。这是因为添加索引也有其缺点。首先,创建和维护索引需要时间。其次,索引需要占用物理空间。除了数据表,每个索引还占用一定的物理空间。如果要建立聚集索引,需要更多空间。第三,在添加、删除和修改表中的数据时,需要动态维护索引,降低了数据维护的速度。2020年7月31日,第9页,索引建立在列的顶部,索引创建在经常需要搜索的列上;在主键上创建索引;在经常用于联接的列上创建索引,即在外键上创建索引;在经常需要按

5、范围搜索的列上创建索引(因为索引已经排序,所以它的指定范围是连续的);在需要频繁排序的列上创建索引(因为索引已经排序,所以查询可以使用索引的排序来加快排序查询时间);在WHERE子句中经常使用的列上创建索引。,2020年7月31日,第10页,本章内容,9.1概述9.2索引的类型和特征9.3创建索引9.4维护索引9.5优化查询9.6练习9.7练习,2020年7月31日,第11页,9.2索引的类型和特征在Microsoft SQL Server 2005系统中有两种基本的索引类型:聚集索引和非聚集索引。此外,还有唯一索引、包含列索引、索引视图、全文索引和XML索引等。在这些索引类型中,聚集索引和非

6、聚集索引是数据库引擎中的基本索引类型,是理解唯一索引、包含列索引和索引视图的基础。本节主要研究这两种索引类型。此外,为了更好地理解索引结构,有必要了解堆结构。最后,简要介绍了系统的数据访问。2020年7月31日,第12页,堆,堆是一个没有聚集索引的表,表中的数据没有任何顺序。堆信息记录在sys.partitions目录视图中。每个堆可以有多个不同的分区,每个分区都有一个堆结构,每个分区在sys.partitions目录视图中都有一行,index_id=0。也就是说,每个堆可以有多个堆结构。2020年7月31日,第13页,堆结构示意图,2020年7月31日,第14页,聚集索引是指具有相同物理顺序

7、和数据表索引顺序的索引,而非聚集索引是指具有不同物理顺序和数据表索引顺序的索引。聚集指数的叶层和非叶层构成了一种特殊的B树结构。B树结构中的每一页都称为一个索引节点。索引的最低级别节点是叶级节点。在聚集索引中,表的数据页是叶级的,叶级以上的索引页是非叶级的。在聚集索引中,页面的顺序是有序的。应该在表中经常搜索的列或顺序访问的列上创建聚集索引。用于指定聚集索引第一页地址信息的Root_page来自sys。系统内部分配单位系统视图。聚集索引的结构图如图9-2所示。2020年7月31日,第15页,聚集索引的示意图,2020年7月31日,第16页,非聚集索引具有与聚集索引相同的B树结构,但是在非聚集索

8、引中,基础表的数据行没有按非聚集键的顺序进行排序和存储,并且非聚集索引的叶级由索引页而不是数据页组成。非聚集索引可以在表或视图的聚集索引上定义,也可以在表或视图的堆上定义。非聚集索引中的每个索引行都由一个非聚集键值和一个行定位器组成,行定位器指向聚集索引或包含键值的堆中的数据行。如果表或视图中没有聚集索引(堆),行定位器是指向行的指针RID,RID由文件标识符标识、页码和页面上的行数生成。如果表或视图上有聚集索引,则行定位器是该行的聚集索引键。非聚集索引的结构图如图9-3所示。2020年7月31日,第17页,非聚集索引示意图,2020年7月31日,第18页,其他类型的索引,除了聚集索引和非聚集

9、索引之外,Microsoft SQL Server 2005系统还提供了一些其他类型的索引或索引表达式,包括唯一索引、包含列索引、索引视图、全文索引和XML索引。创建聚集索引或非聚集索引时,键可以不同或包含重复值。如果希望索引键不同,必须创建一个唯一的索引。当然,在创建聚集索引或非聚集索引时,可以指定索引是唯一的。这种唯一性与上面提到的主键约束有关。在某种程度上,可以说主键约束等于唯一聚集索引。如果多列的总字节数大于900字节,并且您希望将这些列包含在索引中,则可以使用包含列索引。如果希望提高视图的查询效率,可以对视图进行物理索引,也就是说,将结果集永久存储在索引中。全文索引是一种基于标记的特

10、殊类型的索引,由微软的全文引擎服务创建、使用和维护。其目的是为用户提供对字符串数据中复杂单词的高效搜索。此索引的结构不同于数据库引擎使用的聚集索引或非聚集索引的B树结构。XML索引是一种与XML数据相关联的索引形式,它是XML二进制BLOB的分离持久化表示。XML索引可以分为主索引和辅助索引。2020年7月31日,第19页,访问数据的方式。当访问数据库中的数据时,可以使用两种方法,即表扫描和索引搜索。第一种方法是表扫描,即系统将指针放在表的头数据所在的数据页上,然后按照数据页的排列顺序,从前到后逐页扫描表数据占用的所有数据页,直到扫描完表中的所有记录。扫描时,如果找到符合查询条件的记录,则选择

11、该记录。最后,显示所有符合查询条件的选定记录。第二种方法是使用索引搜索。索引是一种树形结构,其中存储了关键字和指向包含关键字所在记录的数据页的指针。使用索引搜索时,系统会根据索引中的关键字和指针,沿着索引的树形结构查找满足查询条件的记录。最后,显示所有找到的满足查询语句条件的记录。当系统沿着索引值搜索时,它使用搜索值与索引值进行比较。这种比较判断一直持续到满足以下两个条件:搜索值不大于或等于索引值。搜索值大于或等于索引页上的最后一个值。2020年7月31日,第20页,本章内容,9.1概述9.2索引的类型和特征,9.3创建索引,9.4维护索引,9.5优化查询,9.6练习,2020年7月31日,第

12、21页,9.3创建索引,在微软的SQL Server 2005系统中,可以直接或间接创建索引。直接创建索引时,可以使用CREATE INDEX语句或图形工具。2020年7月31日,第22页,直接法和间接法,后者可分为直接法和间接法。直接创建索引的方法是使用命令和工具直接创建索引。间接创建索引意味着通过创建其他对象来创建附加索引,例如在表中定义主键约束或唯一性约束,同时创建索引。虽然这两种方法可以创建索引,但是它们的具体内容是不同的。使用create index语句或CREATE INDEX向导创建索引,这是创建索引最基本的方法,而且这种方法最灵活,可以自定义和创建一个符合自己需要的索引。也可以

13、通过定义主键约束或唯一性约束来间接创建索引。2020年7月31日,第23页,CREATE INDEX语句,在table _ or _ view _ name(列ASC | desc,n ) INCLUDE (column_name,n)WITH(PAD _ INDEX=ON | OFF | fill factor=fill factor | SORT _ IN _ TEMPDB=ON | OFF | IGNORE _ DUP _ KEY=ON | OFF | STATISTICS _ norecute=ON | OFF | DROP _ EXISTING=ON | OFF | ONLINE=O

14、N | OFF | ALLOW _ ROW _ lock=ON | OFF | ALLOW _ PAGE _ lock=ON | OFF | max DOP=max _ degree _ parallelism),ON partition _ schema _ name(column _ name)| file group _ name | default,2001年7月31日第24页,示例9-1使用CREATE INDEX语句创建唯一聚集索引,2020年7月31日,第25页,示例9-2使用CREATE INDEX语句创建唯一非聚集索引,2020年7月31日,第26页,示例9-3使用CREAT

15、E INDEX语句创建包含列索引,2020年7月31日,第27页,示例9-4使用DROP INDEX语句删除指定索引,2020年7月31日,第28页数据库引擎优化顾问数据库引擎优化顾问分析一个或多个数据库的工作负载和实现,其中工作负载是在一个或多个要优化的数据库上执行的一组Transact-SQL语句。数据库引擎优化顾问的输入是由SQL Server Profiler生成的跟踪文件、指定的跟踪表或工作负载。数据库引擎优化顾问的输出是修改数据库的物理设计结构的建议,包括聚集索引、非聚集索引、索引视图、分区等。2020年7月31日,第29页,示例9-5使用数据库引擎优化顾问,2020年7月31日,

16、第30页,示例9-5,2020年7月31日,第31页,查看索引信息。在Microsoft SQL Server 2005系统中,您可以使用一些目录视图和系统函数来查看有关索引的信息。这些目录视图和系统函数的描述如表9-1所示。2020年7月31日,第32页,示例9-6查看索引信息,2020年7月31日,第33页,本章内容,9.1概述9.2索引类型和特征9.3索引创建9.4索引维护9.5查询优化9.6机器练习9.7练习,2020年7月31日,第34页,9.4这些维护包括检查碎片信息、维护统计信息、分析索引性能、删除和重建索引等。在2020年7月31日第35页,检查索引统计信息,这是查询优化器用来分析和评估查询并确定最佳查询计划的基本数据。一般来说,用户可以通过普通方式访问指定索引的统计信息。一种方法是使用DBCC显示统计命令,另一种方法是使用图形工具。DBCC SHOW_STATISTICS命令可用于返回指定表或视图的特定对象的统计信息,如索引和列。2020年7月31日,第36页,示例9-7,和2020年7月31日,第37页,要查看索引的碎片信息,有两种方法可以查看索引的碎片信息,使用sys.dm_db_index_physical_stats系统函数和使用图形工具。请注意,sys.dm_db_index

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论