第7章 索引与视图课件.ppt

《第7章 索引与视图课件.ppt》由会员分享，可在线阅读，更多相关《第7章 索引与视图课件.ppt（49页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、第7章 索引与视图本章的学习目标：n索引的优点和缺点n聚集索引和非聚集索引的特点n索引的类型n视图的概念、特点和类型n使用CREATE VIEW语句创建视图n通过视图修改基表中的数据7.1 索引概述 n通过创建和设计良好的索引进行数据查询，可以显著提高数据库查询和应用程序的性能，减少磁盘I/O操作，降低系统资源的消耗。 7.1.1 索引的基本概念 n数据库系统用下列两种方法之一来访问数据：（1）表扫描，就是指系统将指针放在该表的表头数据所在的数据页上，然后按照数据页的排列顺序，逐页地从前向后扫描该表数据所占有的全部数据页，直至扫描完表中的全部记录。在扫描时，如果找到符合查询条件的记录，那么就将

2、这条记录挑选出来。最后，将全部挑选出来符合查询语句条件的记录显示出来。（2）使用索引查找。索引是一种树状结构，其中存储了关键字和指向包含关键字所在记录的数据页的指针。当使用索引查找时系统将沿着索引的树状结构，根据索引中关键字和指针找到符合查询条件的记录。最后将全部查找到的符合查询语句条件的记录显示出来。n索引是一种与表或视图关联的物理结构，能提供一种以一列或多列的值为基础迅速查找表中行的能力，用来加快从表或视图中检索数据行的速度 7.1.2 索引的分类 n在Microsoft SQL Server 2008 R2系统中有两种基本的索引类型：聚集索引和非聚集索引。除此之外，还有唯一性索引、包含性

3、列索引、索引视图、全文索引及XML索引等。 1.索引文件架构（1）堆文件n堆是不含聚集索引的表，表中的数据没有任何的顺序。堆结构中数据按照插入的先后次序存放，堆文件的数据页面不一定在物理上相邻。堆文件执行插入操作很容易，但是效率不高。 （2） B+树的总体结构nB+树（Balance树，平衡树）是目前广泛采用的动态文件结构.n在B+树索引中，索引分级组织成一棵树。最上层的索引节点称为根节点，中间节点按照索引数据量分成不同的层次。最下层的索引节点称为叶节点。 .聚集索引n聚集索引是一种数据表的物理顺序与索引顺序相同的索引。n建立索引时，系统将对表的物理数据页中的数据按列进行排列，然后再重新存储到

4、磁盘上，即聚集索引与数据是混为一体的。聚集索引的叶级和非叶级构成了一个特殊类型的B+树结构.n聚集索引的特点如下：n（1）表的数据按照索引的数据顺序排列。n（2）每个数据表只能建立一个聚集索引，并且会在第一个建立，常常会在主键所在的列或者最常查询的列上建立聚集索引。n（3）索引将占用用户数据库的空间。n（4）适合范围查询。非聚集索引 n非聚集索引是一种数据表的物理顺序与索引顺序不相同的索引。非聚集索引与聚集索引具有相同的B树结构，但是在非聚集索引中，基础表的数据行不是按照非聚集键的顺序排序和存储，且非聚集索引的叶级是由索引页而不是由数据页组成。n非聚集索引既可以定义在表或视图的聚集索引上，也可

5、以定义在表或视图的堆上。 n创建一个非聚集索引时，应该注意下列事项：（1）如果没有指定索引类型，那么默认的类型是非聚集索引。（2）索引页的叶级只包含索引的关键字，不包含实际的数据（3）每个表最多可以创建249个非聚集索引。（4）聚集索引应在非聚集索引被创建之前创建。（5）唯一性是由叶级维护的。（6）以下情况发生时，SQL Server会自动重建现有的非聚集索引：删除现有的聚集索引时创建聚集索引时使用DROP_EXISTING选项来改变聚集索引列的定义时聚集索引上的非聚集索引 n当在一个聚集索引上构建非聚集索引时，索引中的指针值就是数据行的聚集索引键值。一旦定位到索引值，SQL Server就使

6、用聚集键导航聚集索引来检索所有需要的列。唯一索引 n唯一索引是指索引值必须是唯一的，不允许数据表中具有两行相同的索引值。n聚集索引和非聚集索引是从索引数据存储的角度来区分的；而唯一索引和非唯一索引是从索引值来区分的，所以唯一索引和非唯一索引既可以是聚集索引，也可以是非聚集索引，只要列中的数据是唯一的，就可以在一张表中创建一个唯一索引和多个非聚集索引。7.2 索引的操作 7.2.1 创建索引 n创建索引的方式可以分为直接方法和间接方法。n直接创建索引的方法就是使用命令和工具直接创建索引。n间接创建索引就是通过创建其他对象而附加创建了索引。 n主键约束或唯一性约束创建的索引的优先级高于使用CREA

7、TE INDEX语句创建的索引。当在表上定义主键或唯一性约束时，如果表中已经有了使用CREATE INDEX语句创建的标准索引时，那么主键约束或唯一性约束创建的索引覆盖以前创建的标准索引。1. 直接使用Microsoft SQL Server Management Studio创建索引。n（1）从“开始”菜单上选择“程序”| Microsoft SQL Server 2008 R2| SQL Server Management Studio命令，使用Windows或SQL Server身份验证建立连接。n（2）在“对象资源管理器”窗口中，打开要建立索引的表“业务记录”表，单击前面的“+”，展开

8、选项，右键单击“索引”项，选择“新建索引”命令，打开“新建索引”对话框 （3）在“选择页”窗格中选择“常规”项，输入索引名称“入住时间_index”，选择非聚集索引类型，单击“添加”按钮，打开“选择列”对话框，如图7-5所示。在该窗体中选择“入住时间”字段作为索引列，单击“确定”按钮，返回“新建索引”对话框。（4）在“选项”页可设置索引的填充因子等参数，填充因子指示索引页的填满程度。在“包含性列”页中，设置索引页还可以包含的其他非键列。在“存储”页，可以对指定的文件组或分区方案建立索引。在“空间”页可以设置空间索引的空间属性。在“筛选器”页可以定义筛选索引的筛选表达式。（5）设置完成后，单击“

9、确定”按钮，则该索引创建完成。2. 使用Transact-SQL语句创建索引nCREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name nON table_or_view_name ( column ASC | DESC ,.n ) nINCLUDE (column_name, n)n WITH n( PAD_INDEX = ON | OFFn | FILLFACTOR = fillfactorn | SORT_IN_TEMPDB = ON | OFFn | IGNORE_DUP_KEY = ON | OFFn | STATISTICS_N

10、ORECOMPUTE = ON | OFFn | DROP_EXISTING = ON | OFFn | ONLINE = ON | OFFn | ALLOW_ROW_LOCKS = ON | OFFn | ALLOW_PAGE_LOCKS = ON | OFFn | MAXDOP = max_degree_of_parallelism), n )nON partition_schema_name (column_name) | filegroup_name | defaultnUNIOUE选项表示创建唯一性的索引，这时在索引列中不能有相同的两个列值存在。CLUSTERED选项表示创建聚集索引

11、。NONCLUSTERED选项表示创建非聚集索引，非聚集索引是CREATE INDEX语句的默认值。n第一个ON关键字表示索引所属的表或视图，这里用于指定表或视图的名称和相应的列名称。列名称后面可以使用ASC或DESC关键字，指定升序排列或降序排列，其默认值是ASC。第二个ON关键字用于指定该索引所在的分区方案或文件组名称。nINCLUDE子句用于指定将要包含到非聚集索引的页级中的非键列。nPAD_INDEX选项用于指定索引的中间页级，也就是说为非叶级索引页指定填充度。这时的填充度由FILLFACTOR选项指定。nFILLFACTOR选项用于指定叶级索引页的填充度。nSORT_IN_TEMPD

12、B选项为ON时，用于指定创建索引时产生的中间结果，在tempdb数据库中进行排序。该选项为OFF时，在当前数据库中排序。nIGNORE_DUP_KEY选项用于指定唯一性索引键冗余数据的系统行为。当为ON时，系统发出警告信息，只有违反唯一性行的数据插入失败。该选项为OFF时，取消整个INSERT语句并且发出错误信息。nSTATISTICS_NORECOMPUTE选项用于指定是否重新计算分发统计信息。为ON时，不自动计算过期的索引统计信息。为OFF时，启动自动计算功能。nDROP_EXISTING选项用于是否可以删除指定的索引并且重建该索引。为ON时，可以删除并且重建已有的索引。为OFF时，不能删

13、除重建。nONLINE选项用于指定索引操作期间基础表和关联索引是否可用于查询。为ON时，不持有表锁，允许用于查询。为OFF时，持有表锁，索引操作期间不能执行查询。nALLOW_ROW_LOCKS选项用于指定是否使用行锁，如果为ON，表示使用行锁。nALLOW_PAGE_LOCKS选项用于指定是否使用页锁，如果为ON，表示使用页锁。nMAXDOP选项用于指定索引操作期间覆盖最大并行度的配置选项，主要目的是限制执行并行计划过程中使用的处理器数量。n例7-1 n例7-2n例7-3n例7-4n例7-5n例7-6 7.2.2 查看索引 n1.直接使用Microsoft SQL Server Manage

14、ment Studio查看索引（1）在“对象资源管理器”窗口中，打开要查看索引的表“客房信息”表，单击右键，在弹出菜单中，选择“设计”，进入表设计器。（2）在“表设计器”中，打开“索引/键”窗口。如图7-6所示。可以查看表的所有索引，选中某个索引后还可以查询该索引的名称和列字段等属性。2.使用系统视图查看索引信息（1）系统视图sys.indexesnsys.indexs用于显示数据库元数据中存储的索引类型/文件组或分区方案ID和索引选项的当前设置。n例7-7 （2）系统视图sys.index_columnsnsys.index_columns可以查看索引的列ID、索引内的位置、索引列的排序顺序

15、等。n例7-8 3.使用系统函数查看索引信息（1）系统函数sys.dm_db_index_usage_statsnsys.dm_db_index_usage_stats用于显示不同类型索引操作的计数以及上次执行每种操作的时间。n例7-9 （2）系统函数sys.dm_db_index_operational_statsnsys.dm_db_index_operational_stats用于显示数据库中表或索引的每个分区的当前低级I/O、锁定、闩锁和访问方法活动。n例7-10 维护索引 1. 直接使用Microsoft SQL Server Management Studio修改与维护索引 n（1

16、）从“开始”菜单上选择“程序”| Microsoft SQL Server 2008 R2| SQL Server Management Studio命令，使用Windows或SQL Server身份验证建立连接。n（2）进入对象资源管理器，展开指定的服务器和数据库项，并展开要查看的表，从选项中选择“索引”选项，则会出现表中已存在的索引列表。双击某一索引名称，则会出现索引属性对话框，在“索引属性-常规”标签页上可以修改索引类型、索引键列等。如图7-7所示。n（3）在“索引属性-选项”标签页中可以勾选是否重新计算索引统计信息。索引统计信息对维护索引的性能具有指导作用。还可以设置填充因子。 n（4

17、）在“索引属性-存储”标签页中可以修改索引的文件组和分区属性。n（5）在“索引属性-碎片”标签页中。n（6）在“索引属性-扩展属性”标签页中主要包含数据库名称、排序规则等。 2. 使用Transact-SQL语句修改与维护索引（1）使用系统存储过程查看索引信息语法格式：EXEC sp_helpindex table_namen例7-11 （2）查看索引属性nSQL Server提供的Transact-SQL函数INDEXPROPERTY能够在给定表标识号/索引名称及属性名称的前提下，返回指定查看的索引属性值。其语法如下：NDEXPROPERTY (table_id, index, proper

18、ty)n其中：table_id为预为其提供索引属性信息的表的标识号。index是索引的名称，将为该索引返回属性信息。property包含将要返回的索引属性的名称。n例7-12 （2）查看索引属性nSQL Server提供的Transact-SQL函数INDEXPROPERTY能够在给定表标识号/索引名称及属性名称的前提下，返回指定查看的索引属性值。其语法如下：NDEXPROPERTY (table_id, index, property)n其中：table_id为预为其提供索引属性信息的表的标识号。index是索引的名称，将为该索引返回属性信息。property包含将要返回的索引属性的名称。n

19、例7-12 7.2.4 更名与删除索引 1.使用系统存储过程给索引更名n使用系统存储过程给索引更名的语法格式：sp_rename objname = object_name , newname = new_name , objtype = object_type n例7-13 7.3 视图概述视图概述n视图是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。和真实的表一样，视图也包括几个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的表。因此，视图不是真实存在的基础表，而是一个虚拟表，视图中所显示的数据并不以视图结构存储在数据库中，而是存储在

20、视图所引用的表中。 7.3.1 视图的概念 n视图是查看数据库表中数据的一种方式。视图提供了存储预定义的查询语句作为数据库中的对象以备以后使用的能力。视图是一种逻辑对象，是一种虚拟表。n视图的内容包括如下：（1）基表的列的子集或行的子集，也就是说视图可以是基表的其中一部分。（2）两个或多个基表的联合，也就是说视图是对多个基表进行联合运算检索的SELECT语句。（3）两个或多个基表的连接，也就是说视图是通过对若干个基表的连接生成的。（4）基表的统计汇总，也就是说视图不仅是基表的投影，还可以是经过对基表的各种复杂运算的结果。（5）另外一个视图的子集，也就是说视图既可以基于表，也可以基于另外一个视图

21、。（6）视图和基表的混合，在视图的定义中，视图和基表可以起到同样的作用。7.3.2 视图的作用 n视图结合了基本表和查询两者的特性：用户可以使用视图从一个或多个相关的基表中提取一个数据集（查询特性）；用户能运用视图去更新视图中的信息，并且持久地存储到磁盘（表特性）。n视图的优点主要表现在以下几点：（1）数据集中显示（2）简化对数据的操作（3）自定义数据（4）合并分割数据（5）安全机制7.3.3 视图的限制 n 当更新视图中的数据时，实际上是对数据表的数据进行更新。事实上，当从视图中插入或者删除数据时，情况也相同。n某些视图是不能更新数据的，这些视图有如下特征：（1）有UNION等集合操作符的视

22、图。（2）有GROUP BY子句的视图。（3）有诸如AVG、SUM、或者MAX等函数的视图。（4）使用DISTINCT短语的视图。（5）连接表的视图（其中有一些例外）。7.4 视图的操作视图的操作 7.4.1 创建视图 n在SQL Server中创建视图时需要遵循下列规则： （1）只能在当前数据库中创建视图。（2）如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。（3）如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。（4）不能在视图上创建索引，不能在规则、默认、触发器的定义中引用视图。（5）当通过视图查询数据时，SQL Se

23、rver要检查以确保语句中涉及的所有数据库对象存在，而且数据修改语句不能违反数据完整性规则。n1. 直接使用Microsoft SQL Server Management Studio创建视图（1）从“开始”菜单上选择“程序”| Microsoft SQL Server 2008 R2| SQL Server Management Studio命令，使用Windows或SQL Server身份验证建立连接。（2）在“对象资源管理器”中展开服务器，然后展开“数据库”节点，双击“Hotel”数据库将其展开。选择“视图”项，单击右键，从快捷菜单中选择“新建视图”命令，将在主菜单上添加“查询设计器”菜

24、单，并打开设计器窗格 （3）在关系图窗格中单击右键，从快捷菜单中选择“添加表”命令，（右击“视图”节点，从弹出菜单中选择“新建视图”命令，）打开“添加表”对话框 （4）选择将要定义的视图的基表（可以是表、视图、函数、同义词或者它们的组合）。（5）假定这里选择“客房信息”表，单击“添加”按钮，把它添加到“视图”选项，选择完成后，单击“关闭”按钮 （6）在“视图”页面，完成视图的定义操作。（7）单击“保存”按钮，在弹出对话框中输入视图名称“v_客房位置”，然后单击确定按钮，保存视图 n2. 使用Transact-SQL语句创建视图n使用Transact-SQL语句中的CREATE VIEW命令创建

25、视图的语法形式如下：CREATE VIEW view_name ( column ,.n ) WITH ,.n ASselect_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA n例7-15 n例7-16n例7-17n例7-18n 7.4.2 查看视图n创建了一个视图后，如果视图定义没有加密，可以获取该视图定义的有关信息。（1）使用系统存储过程语句sp_helptext查看视图、触发器、存储过程在系统表中的定义文本， 其语法形式如下：sp_helptext objname = namen参数ob

26、jname = name为对象的名称，对象必须在当前数据库中。n例7-19 n（2）使用系统存储过程sp_depends返回系统表中存储的任何信息，该系统表指出数据库对象所依赖的对象。其语法形式如下：sp_depends object = objectn参数 object = object为被检查的数据库对象。对象可以是表、视图、存储过程或触发器。n例7-20 n（3）使用系统存储过程sp_help来返回有关数据库对象的详细信息，如果不针对某一特定对象，则返回数据库中所有对象信息。其语法形式如下：sp_help object = namen参数object = name是系统对象表中的任意对象

27、的名称，或者是系统类型表中任何用户定义数据类型的名称。n例7-21 修改视图 n1. 直接使用Microsoft SQL Server Management Studio修改视图（1）从“开始”菜单上选择“程序”| Microsoft SQL Server 2008 | SQL Server Management Studio命令，使用Windows或SQL Server身份验证建立连接。（2）在“对象资源管理器”中展开服务器，然后展开“数据库”节点，双击“Hotel”数据库将其展开。（3）双击“视图”将其展开，右键单击要修改的视图，从弹出的快捷菜单中选择“设计”命令。（4）打开“视图设计器”

28、对话框，可以在其中按照创建视图的方法对视图定义进行各种修改。n2. 使用Transact-SQL语句修改视图n使用Transact-SQL语句中的ALTER VIEW语句修改对视图的定义。语法形式如下： ALTER VIEW view_name (column,.n) WITH ENCRYPTION ASselect_statement WITH CHECK OPTION n例7-22 7.4.4 删除视图 n1. 直接使用Microsoft SQL Server Management Studio删除视图n可以在企业管理器中删除视图：右击要删除的视图，在弹出的快捷菜单中选择“删除”命令，打开

29、“除去对象”对话框，如图7-16所示，单击“全部除去”按钮则将视图删除。 n2. 使用Transact-SQL语句修改视图使用Transact- SQL语句DROP VIEW删除视图的语法形式如下：DROP VIEW view_name ,nn例7-23 通过视图修改数据n通过视图可以修改数据，但是无论在什么时候修改视图的数据，实际上都是在修改视图的基表中的数据。在满足一定的限制条件下，可以通过视图自由地插入、删除和更新基表中的数据。n在修改视图时，要注意下列一些条件：n不能同时影响两个或两个以上的基表。可以修改由两个或两个以上的基表得到的视图，但是每一次修改的数据只能影响一个基表。n某些列不

30、能修改。这些不能修改的列包括通过计算得到值的列、有内置函数的列或有合计函数的列等。n如果影响到表中那些没有缺省值的列，那么可能引起错误。例如，如果使用INSERT语句向视图中插入数据，且该视图的基表有一个没有缺省值的列或有一个不允许空的列且该列没有出现在视图的定义中，那么就会产生一个错误消息。n如果在视图定义中指定了WITH CHECK OPTION选项，那么系统验证所修改的数据。WITH CHECK OPTION选项强制对视图的所有修改语句必须满足定义视图使用的SELECT语句的标准。如果这种修改超出了视图定义的范围，那么系统将拒绝这种修改。1. 通过视图插入数据n例 7-242. 通过视图修改数据n例 7-253. 通过视图删除数据n例 7-26