《大型数据库应用与维护》课件-第9讲 SQL Server对象管理

大型数据库应用与维护以SQLServer2012为例上讲内容回顾4.1数据库的创建与管理

4.1.1创建数据库 4.1.2管理数据库4.2数据表的创建与管理4.2.1创建数据表4.2.2修改数据表4.2.3删除数据表4.3数据库完整性

4.3.1完整性 4.3.2约束4.4

视图

4.4.1视图概述 4.4.2创建视图4.4.3利用视图管理数据4.4.4管理视图上讲内容提问视图是什么？有何特点或作用？视图是否可以与表同名？只能在当前数据库中创建视图吗？创建视图有几种方法，分别是？可以利用T-SQL语句创建几种类型的视图？什么情况下视图必须为列定义名称？当视图引用基表或视图被删除，该视图还可以被使用？

第四章SQLServer对象管理第四章SQLServer对象管理目的与要求：掌握SQLServer数据库的对象管理，包括创建数据库，创建数据表，数据完整性与约束，视图与索引等。重点与难点：创建数据库与数据表（重点）数据完整性与约束（难点）视图（重点）索引（难点）第9讲SQLServer对象管理44.5索引4.5.1索引概述4.5.2创建索引4.5.3管理索引★本章复习4.5索引4.5

索引索引概述创建索引管理索引4.5.1索引概述4.5.1

索引概述在应用系统中，尤其在联机事务处理系统中，对数据查询及处理速度已成为衡量应用系统成败的标准。

而采用索引来加快数据处理速度通常是最普遍采用的优化方法。4.5.1

索引概述数据库中的索引与书籍中的目录类似。在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。书中的目录是一个词语列表，其中注明了包含各个词的页码。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。而数据库中的索引是一个表中所包含的值的列表，其中注明了表中包含各个值的行所在的存储位置。4.5.1

索引概述

索引的作用通过创建唯一索引，可以增强数据记录的唯一性。可以大大加快数据检索速度。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。在使用ORDERBY和GROUPBY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。4.5.1

索引概述

索引的注意事项不过，索引为性能所带来的好处却是有代价的。带索引的表在数据库中会占据更多的空间。另外，为了维护索引，对数据进行插入、更新、删除操作的命令所花费的时间会更长。创建索引所需的工作空间约为数据库表的1.2倍，在建立索引时，数据被复制以便建立索引。索引建立后，旧的未加索引的表被删除，创建索引时使用的硬盘空间由系统自动收回。在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源方面的代价。时间和空间4.5.1

索引概述因创建索引要耗一定的系统性能，因此要考察对某列创建索引的必要性。定义有主关键字和外部关键字的列需在指定范围中快速或频繁查询的列需要按排序顺序快速或频繁检索的列在集合过程中需要快速或频繁组合到一起的列这些情况要考虑创建索引4.5.1

索引概述在查询中几乎不涉及的列很少有唯一值的列由text,ntext或image数据类型定义的列只有较少行数的表没必要建索引这些情况可不考虑创建索引4.5.1

索引概述SQLServer根据存储索引和数据的物理行的方式的不同，索引分为3种类型。即聚集索引（CLUSTEREDINDEX）、非聚集索引（NONCLUSTEREDINDEX），以及主XML索引和辅助XML索引。聚集索引非聚集索引4.5.1

索引概述1.聚集索引聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。当以某字段作为关键字建立聚集索引时，表中数据以该字段作为排序根据。因此，一个表只能建立一个聚集索引，但该索引可以包含多个列（组合索引）。4.5.1

索引概述1.聚集索引如果存在主键，那么主键索引就是聚集索引。如果不存在主键，将会使用第一个唯一(UNIQUE)、非空的索引作为聚集索引。如果表中既没有主键索引，又没有合适的唯一索引，那么系统会自动维护一个row_id(默认大小为6B)来作为隐藏的聚集索引。为什么聚集索引只能存在一个呢？是为了节省磁盘空间和保证数据的一致性。4.5.1

索引概述2.非聚集索引非聚集索引是相比较于聚集索引来说，它是把索引和行数据分开维护。非聚集索引完全独立于数据行的结构。数据存储在一个地方，索引存储在另一个地方。非聚集索引中的数据排列顺序并不是表格中数据的排列顺序。SQLServer默认情况下建立的索引是非聚集索引。一个表可以拥有多个非聚集索引，每个非聚集索引提供访问数据的不同排序顺序。4.5.1

索引概述聚集型索引和非聚集型索引的比较关于非聚集索引非聚簇索引提高的存取速度，但降低了表的更新的速度如果硬盘和内存空间有限，应限制非聚簇索引的使用修改一个表的数据时，同时要维护索引索引存取速度索引的数量所需空间聚集索引快一表一个少非聚集索引慢一表可以多个多4.5.1

索引概述建立聚集索引的必要性查询命令的回传结果是以该字段为排序条件需要回传局部范围的大量数据表格中某字段内容的重复性比较大要考虑建非聚集索引的情况查询所获数据量较少时某字段中的数据的唯一性比较高时4.5.2创建索引4.5.2

创建索引1.利用对象资源管理器创建索引2.利用T-SQL语句创建索引

4.5.2

创建索引1.利用对象资源管理器创建索引4.5.2

创建索引2.利用T-SQL语句创建索引Create[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX

index_nameONTABLE

(column[ASC|DESC][,…n])[with{ignore_dup_key|drop_existing|sort_in_tempdb}][ONFILEGROUP]参数说明如下：unique：该选项用于通知SQLServer索引中列出的列的值是每行唯一的。index_name：索引名。建议使用了“idx_表名_列名"的这种命名方法。ONtable：这是同索引相关联的表的名称。只能是一个表的名称。利用T-SQL语句可以创建索引，该命令的基本语法如下：4.5.2

创建索引2.利用T-SQL语句创建索引【例1】使用CREATEINDEX语句为“物料表”创建一个非聚集索引，索引字段为“物料名称”，索引名为idx_name。create

index

idx_nameon

物料表(物料名称)4.5.2

创建索引2.利用T-SQL语句创建索引【例2】根据“物料表”的物料代码和物料名称列创建索引idx_idname。create

index

idx_idnameon

物料表(物料代码,物料名称)4.5.2

创建索引2.利用T-SQL语句创建索引【例3】为“物料表”的物料代码列创建唯一非聚集索引。如果输入重复键值，将忽略该insert或update语句。create

unique

nonclustered

index

idx_物料代码_unique

on

物料表(物料代码)4.5.2

创建索引2.利用T-SQL语句创建索引【例4】为“物料表”的物料代码创建索引，使用降序排列，填满率为60％。create

index

idx_snoon

物料表(物料代码

desc)with

fillfactor

=60上机实验任务一实验4-4第一题：【创建索引】15分钟4.5.3管理索引4.5.3

管理索引重命名索引删除索引4.5.3

管理索引1.重命名索引可以通过存储过程sp_rename可以对索引名进行重命名。【例5】将“物料表”的索引idx_idname重命名为idx_text。exec

sp_rename

‘物料表.idx_idname','idx_text'goexec

sp_rename

‘表名.旧名’,‘新名'go4.5.3

管理索引2.删除索引【例6】删除“物料表”中的idx_name索引。DROP

INDEX

物料表.idx_name上机实验任务二实验4-4第二题：【管理索引】5分钟小结本讲小结4.5索引4.5.1索引概述4.5.2创建索引4.5.3管理索引小结第四章总结★4.1数据库的创建与管理4.1.1创建数据库4.1.2管理数据库4.2数据表的创建与管理4.2.1创建数据表4.2.2修改数据表4.2.3