资源简介

(共26张PPT)
优化查询学生信息管理数据库
任务7-1 使用索引优化查询性能
目录
Contents
任务7-2 使用视图优化查询性能
实训 创建与管理索引和视图
任务7-1 使用索引优化查询性能
PART 1
任务
assignments
（一）索引概述
（二）索引的类型
（三）索引的设计原则
（四）创建索引
（五）删除索引
使用索引优化查询性能
任务7-1
任务1-1 数据处理
【任务分析】
如何合理地设计数据库的索引，以提高数据的查询速度和效率，提高系统的性能。
【课堂任务】
理解索引的概念及作用。
索引的概念及类型
索引的创建和管理
在关系型数据库中，索引是一种可以加快检索数据的数据库结构，主要用于提高查询性能。因为索引可以从大量的数据中迅速找到需要的数据，不需要检索整个数据库，从而大大提高了检索的效率。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
（一）索引概述
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的存储结构。索引是依赖于表建立的，提供了数据库中编排表中数据的内部方法。表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面。索引就存放在索引页面上。通常，索引页面相对于数据页面小得多。检索数据时，系统先搜索索引页面，从中找到所需数据的指针，再直接通过指针从数据页面中读取数据。从某种程度上，可以把数据库看作一本书，把索引看作书的目录，通过目录查找书的内容，显然比不通过目录查找要方便、快捷。
索引一旦创建，就由数据库自动管理和维护。例如，向表插入、更新和删除一条记录时，数据库会自动在索引中做出相应的修改。在编写SQL查询语句时，具有索引的表与不具有索引的表没有任何区别，索引只是提供一种快速访问指定记录的方法。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
1．索引可以提高数据的访问速度
只要为适当的字段建立索引，就能大幅度提高下列操作的速度。
（1）查询操作中WHERE子句的数据提取。
（2）查询操作中ORDER BY子句的数据排序。
（3）查询操作中GROUP BY子句的数据分组。
（4）更新和删除数据记录。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
2．索引可以确保数据的唯一性
创建唯一性索引可以保证表中数据记录不重复。
虽然索引具有诸多优点，但是仍要注意避免在一个表上创建大量的索引，因为这样不但会影响插入、删除、更新数据的性能，还会在更改表中的数据时，增加调整所有索引的操作，降低系统的维护速度。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
（二）索引的类型
SQL Server 2016的索引有聚集索引（CLUSTERED INDEX）、非聚集索引（NONCLUSTERED INDEX）、唯一索引（UNIQUE INDEX）、XML索引（XML INDEX）、空间索引（SPATIAL INDEX）等类型。本书重点介绍前三种。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
1．聚集索引
除了个别表之外，每个表都应该有聚集索引。聚集索引除了可以提高查询性能之外，还可以按需重新生成或重新组织表碎片，也可以对视图创建聚集索引。
在聚集索引中，行的物理存储顺序与索引顺序完全相同，即索引的顺序决定了表中行的存储顺序。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。
在创建PRIMARY KEY约束时，如果该表的聚集索引不存在且未指定唯一非聚集索引，则将自动对一列或多列创建唯一聚集索引。主键列不允许空值。。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
2．非聚集索引
非聚集索引是一种与存储在表中的数据相分离的索引结构，可对一个或多个选定列重新排序。非聚集索引通常可以比搜索基础表更快的速度查找数据；有时可以完全由非聚集索引中的数据完成查询，或非聚集索引可将数据库引擎指向基础表中的行。
一般来说，创建非聚集索引是为了提高聚集索引不涵盖的，又频繁使用的这类查询的性能，或在没有聚集索引的表（称为堆）中查找行。可以对表或索引视图创建多个非聚集索引。
在创建UNIQUE约束时，默认情况下将创建唯一非聚集索引，以便强制UNIQUE约束。 如果不存在该表的聚集索引，则可以指定唯一聚集索引。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
3．唯一索引
唯一索引能够保证索引键中不包含重复的值，从而使表中的每一行从某种方式上具有唯一性。 只有当唯一性是数据本身的特征时，指定唯一索引才有意义。
聚集索引和非聚集索引都可以是唯一的。只要列中的数据是唯一的，就可以为同一个表创建一个唯一聚集索引和多个唯一非聚集索引。
创建PRIMARY KEY或UNIQUE约束会自动为指定的列创建唯一索引。创建UNIQUE约束和创建独立于约束的唯一索引没有明显区别。数据验证的方式是相同的，而且查询优化器不会区分唯一索引是由约束创建的还是手动创建的。但是，如果目的是要实现数据完整性，则应为列创建UNIQUE或PRIMARY KEY约束，这样才能使索引的目标明确。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
（三）索引的设计原则
索引设计不佳和缺少索引都会影响数据库的应用性能。设计高效的索引对于获得良好的数据库和应用程序性能极为重要。设计索引，应该遵循以下原则。
1．索引并非越多越好
对表编制大量索引会影响INSERT、UPDATE、DELETE等语句的性能，因为当表中的数据更改时，所有索引都必须适当调整。例如，在多个索引中使用了某列，并且执行了修改该列数据的UPDATE语句时，必须更新包含该列的每个索引以及基表中的该列。
避免对经常更新的表创造过多的索引，并且索引应保持较窄，就是说，列要尽可能少。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
2．数据量小的表最好不要使用索引
对小表进行索引可能不会产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长。因此，小表的索引可能从来不用，但仍必须在表中的数据更改时维护。
3．在不同值少的列上不要建立索引
在条件表达式中经常用到在不同值较多的列上建立的索引，在不同值少的列上不要建立索引。例如，学生表的“性别”字段只有“男”和“女”两个不同值，因此无需建立索引。如果建立索引，则不但不会提高查询效率，反而会严重降低更新速度。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
4．指定唯一索引是由某种数据本身的特征来决定的
当唯一性是某种数据本身的特征时，指定唯一索引。例如，学生表中的“学号”字段就具有唯一性，对该字段建立唯一索引可以快速确定某个学生的信息。使用唯一索引需能确保列的数据完整性，以提高查询速度。
5．为经常需要排序、分组和联合操作的字段建立索引
在频繁进行排序或分组的列上建立索引，如果待排序的列有多个，则可以在这些列上建立组合索引。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
（四）创建索引
创建索引是指在某个表的一列或多列上建立一个索引，以提高访问表的速度。在实际创建索引之前，要注意如下事项。
（1）当给表创建PRIMARY或UNIQUE约束时，SQL Server会自动创建索引。
（2）索引的名称必须符合SQL Server的命名规则，且必须是表中唯一的。
（3）可以在创建表时创建索引，或是给现存表创建索引。
（4）只有表的所有者才能给表创建索引。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
1．使用SSMS创建索引
下面为grademanager数据库中的student表创建一个非聚集索引index_sname，操作步骤如下。
（1）启动SSMS，在【对象资源管理器】窗格中，连接到数据库引擎的实例。
（2）展开【数据库】|grademanager|【表】|student节点，用鼠标右键单击【索引】节点，在弹出的快捷菜单中选择【新建索引】命令，在下一级菜单中选择【非聚集索引】命令，如图7.1所示。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
使用索引优化查询性能
任务7-1
任务1-1 数据处理
（3）打开【新建索引】窗口，在【选择页】列表中选择【常规】选项，输入索引名称，选择索引类型、是否唯一索引等，如图7.2所示。
（4）单击【添加】按钮，打开【从“dbo.student”中选择列】对话框，在【选择要添加到索引的表列：】列表框中选中sname复选框，如图7.3所示。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
使用索引优化查询性能
任务7-1
任务1-1 数据处理
（5）单击【确定】按钮，返回【新建索引】窗口，单击【确定】按钮，在【索引】节点下生成一个名为“index_sname(不唯一，非聚性)”的索引，说明该索引创建成功，如图7.4所示。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
2．使用SQL语句创建索引
可以用CREATE INDEX语句在一个已经存在的表上创建索引，CREATE INDEX语句的格式如下。
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name
ON {table|view} (column[ASC|DESC][,...n])
[ON filegroup]
使用索引优化查询性能
任务7-1
任务1-1 数据处理
参数说明如下。
（1）UNIQUE、CLUSTERED和NONCLUSTERED选项：指定所创建索引的类型分别为唯一性索引、聚集索引和非聚集索引。省略UNIQUE时，SQL Server创建的是非唯一性索引；省略CLUSTERED|NONCLUSTERED选项时，SQL Server所创建的是非聚集索引。
（2）index_name：说明所创建索引的名称，索引名称应遵守SQL Server标识符命名规则。
（3）table|view：指定要创建索引的表或视图。
（4）column：指定索引的键列，可以是一列或多列。
（5）ASC|DESC：指定索引列的排序方式是升序还是降序，默认为升序ASC。
（6）ON filegroup子句：指定保存索引文件的数据库文件组名称。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
【例7.1】 为student表的sno列创建一个唯一性聚集索引，索引排列顺序为降序。
CREATE UNIQUE CLUSTEREDINDEX sno_student ON student(sno DESC)
【例7.2】 为student表的sname列创建一个非聚集索引，索引排列顺序为降序。
CREATE NONCLUSTEREDINDEX sname_student ON student(sname DESC)
使用索引优化查询性能
任务7-1
任务1-1 数据处理
（五）删除索引
当索引不再需要时，可以使用SSMS和DROP INDEX语句删除索引。
1．使用SSMS删除索引
（1）启动SSMS，在【对象资源管理器】窗格中，连接到数据库引擎的实例。
（2）展开实例服务器下的【数据库】|grademanager|【表】|student|【索引】节点，用鼠标右键单击要删除的索引，在弹出的快捷菜单中选择【删除】命令。
（3）在打开的【删除对象】页面中，单击【确定】按钮即可。
使用索引优化查询性能
任务7-1
任务1-1 数据处理
2．使用DROP INDEX语句删除索引
使用DROP INDEX语句删除索引的语句格式如下。
DROP INDEX <表名.索引名>
或
DROP INDEX <索引名> ON <表名>
例如，删除student表的sname_student索引。
DROP INDEX student.sname_student
或
DROP INDEX sname_student ON student

展开更多......

收起↑