第八章--SQL-Server数据库应用开发技术

第8章索引与视图知识技能目标：1．理解索引的概念、索引种类和创建索引的优缺点2．熟练掌握索引的创建和删除3．理解视图的概念及使用视图的优缺点4．熟练掌握视图的创建、查看、修改、删除、更名5．熟练掌握通过视图查询和更新数据方法第8章索引与视图引导案例：前面的章节中已经创建了教务管理数据库，输入了相应的数据。数据库建立起来后，就可以操作数据库中的数据了，其中最主要的操作就是查询操作。为了提高查询的效率，就需要在相应的表上创建索引，为了增强查询的灵活性，就需要在表的基础上创建视图，满足用户复杂的查询需要。本章介绍这两方面的技术。第8章索引与视图8.1索引概念8.2索引的创建与管理8.3索引的维护8.4视图概念8.5视图的创建与管理8.6通过视图查询和更新数据小结8.1索引概念8.1.1索引的概念用户对数据库最频繁的操作是数据查询，一般情况下，执行查询操作时，需要对整个表进行数据搜索。当表中的数据很多时，搜索就需要很长的时间，为了提高检索数据的效率，数据库引入了索引机制。在数据库中，使用索引无须对整个表扫描，就可以找到所需要的数据。索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。可以在表或视图的一列或多列上创建索引，基于两列或多列上建立的索引称为复合索引。如果表中任意两行被索引的列值不允许出现重复值，那么这种索引称为唯一（UNIQUE）索引。返回目录8.1索引概念8.1.2聚簇索引和非聚簇索引按照索引的组织方式，可以将索引分为聚集索引和非聚集索引。1．聚簇索引（ClusteredIndex）对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚簇索引与数据是混为一体的，它的叶节点中存储的是实际的数据。可以在表或视图的一列或多列上的组合上创建索引，在实际开发中，一般在定义成主键的列建立聚集索引，且创建主键约束时，若表中没有聚集索引，则SQLServer将主键列作为聚集索引列。在每个表中只能创建一个聚集索引。返回目录8.1索引概念8.1.2聚簇索引和非聚簇索引2．非聚簇索引（NonclusteredIndex）非聚集索引与书中内容的组织方式类似，数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，索引中的项目按键值的顺序存储，而表中的数据按另一种顺序存储，具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。在表中，最多可以建立250个非聚集索引，或者249个非聚集索引和1个聚集索引。返回目录8.1索引概念8.1.3创建索引的优缺点1．创建索引的优点•通过创建唯一索引，可以保证数据记录的唯一性。•可以大大加快数据检索速度。•可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。•在使用ORDERBY和GROUPBY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。•使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。返回目录8.1索引概念8.1.3创建索引的优缺点2．创建索引的缺点•创建索引要花费时间和占用存储空间。•建立索引加快了数据检索速度，却减慢了数据修改速度。返回目录8.1索引概念8.1.4考虑建索引的列和不考虑建索引的列1．考虑建索引的列如果在一个列上创建索引，该列就称为索引列。索引列中的值称为关键字值。考虑建索引的列有以下这些列：（1）主键：通常检索、存取表是通过主键来进行的。因此，应该考虑在主键上建立索引。（2）连接中频繁使用的列：用于连接的列若按顺序存放，系统可以很快地执行连接。如外键，除用于实现参照完整性外，还经常用于进行表的连接。（3）在某一范围内频繁搜索的列和按排序顺序频繁检索的列。返回目录8.1索引概念8.1.4考虑建索引的列和不考虑建索引的列2．不考虑建索引的列建立索引需要产生一定的存储开销，在进行插入和更新数据的操作时，维护索引也要花费时间和空间。一般来说，如下一些列不考虑建立索引：（1）很少或从来不在查询中引用的列，因为系统很少或从来不根据这个列的值去查找数据行。（2）只有两个或很少几个值的列（如性别，只有两个值“男”或“女”），以这样的列创建索引并不能实现建立索引的好处。（3）以bit、text、image数据类型定义的列。（4）数据行数很少的小表一般也没有必要创建索引。返回目录8.2索引的创建与管理8.2.1用T-SQL语句创建索引用T-SQL语句创建索引，通过使用CREATEINDEX语句来实现。语法格式：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON{表名|视图名}(列名[ASC|DESC][,...n])[WITH[PAD_INDEX][[,]FILLFACTOR=填充因子][[,]DROP_EXISTING]]返回目录8.2索引的创建与管理8.2.1用T-SQL语句创建索引说明：UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。CLUSTERED：用于指定创建的索引为聚集索引。NONCLUSTERED：用于指定创建的索引为非聚集索引。PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。FILLFACTOR=填充因子：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，填充因子的值为1到100。DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。返回目录8.2索引的创建与管理8.2.1用T-SQL语句创建索引【例8.1】使用Ｔ-SQL语句在“教务管理数据库”数据库的“学生表”上创建一个名“XS_XM_INDEX”的非唯一性非聚簇索引，索引关键字为“姓名”，升序，填充因子50%。【例8.2】使用Ｔ-SQL语句在“教务管理数据库”的“学生表”上创建一个名为“XS_XH_INDEX”的唯一性聚集索引，索引关键字为“学号”，按降序排列，填充因子70%。【例8.3】使用Ｔ-SQL语句在“教务管理数据库”的“学生表”上创建一名为“XS_XH_XM_INDEX”的唯一性聚集索引，索引关键字为“学号”和“姓名”，均按降序排序，删除并重新创建同名的索引文件。返回目录8.2索引的创建与管理8.2.2用T-SQL语句管理索引当索引创建之后，可以使用T-SQL语句查看、更名和删除索引。1．查看索引在创建索引之前或在创建索引之后，可以用sp_helpindex系统存储过程查看表的所有索引信息。语法格式：SH_HELPINDEX表名【例８.4】用系统存储过程sp_helpindex查看教务管理数据库中“学生表”的索引信息。返回目录8.2索引的创建与管理8.2.2用T-SQL语句管理索引2．重命名索引创建索引后，可以用sp_rename系统存储过程来更改索引的名称。语法格式：SP_RENAME表名.原索引名,新索引名【例８.5】用系统存储过程sp_rename将“学生表”的索引XS_XM_INDEX重新命名为XS_XM_INDEX1。返回目录8.2索引的创建与管理8.2.2用T-SQL语句管理索引3．删除索引在创建索引之后，如果该索引不再需要，使用DROPINDEX命令可以删除一个或者多个当前数据库中的索引。语法格式：DROPINDEX表名.索引名[,...n]【例８.6】用DROP语句将“学生表”的索引XS_XM_INDEX1删除。注意：删除索引时要注意，如果索引是在CREATETABLE语句中创建的，只能用ALTERTABLE命令来删除索引，而不能用DROPINDEX来删除。返回目录8.2索引的创建与管理8.2.3用SSMS创建与管理索引1．使用SQLServerManagementStudio创建索引1）打开SQLServerManagementStudio。2）展开要创建索引的数据库，再展开“表”结点。3）展开要建索引的表。4）在“索引”结点上单击右键，选择“新建索引”。5）在“索引名称”文本框中，输入“ZYB__ZYH_INDEX”，在“索引类型”列表框中选择索引类型为“非聚集”，“唯一”索引。6）单击“添加”按钮，弹出对话框，选择要在其上创建索引的列。7）单击“确定”按钮，回到“新建索引”对话框，其中，“排序”列用于设置索引的排列顺序。如“升序”。8）单击“确定”按钮，即完成了索引的创建过程。返回目录8.2索引的创建与管理8.2.3用SSMS创建与管理索引2．使用SQLServerManagementStudio查看索引1）打开SQLServerManagementStudio。2）在对象资源管理器中展开要查看索引的数据库，再展开“表”结点3）展开要查看索引的表。4）展开“索引”结点，则会出现表中已存在的索引列表。5）双击某一索引名称，则出现索引属性对话框，用户可以查看索引的信息。返回目录8.2索引的创建与管理8.2.3用SSMS创建与管理索引3．使用SQLServerManagementStudio删除索引1）打开SQLServerManagementStudio。2）展开要查删除索引的数据库，再展开“表”结点。3）展开要删除索引的表。4）展开择“索引”结点，则会出现表中已存在的索引列表。5）选择某个需要删除的索引，如“ZYB__ZYH_INDEX”索引，单击鼠标右键，在弹出的菜单中单击“删除”菜单项，则打开删除索引对话框。6）单击“确定”按钮后，完成删除操作。返回目录8.3索引的维护在创建索引后，为了得到最佳的性能，必须对索引进行维护。因为随着时间的推移，用户频繁执行插入、修改和删除等一系列操作，这些操作会使数据变得支离破碎，从而造成索引性能的下降。SQLServer提供了多种方法来维护索引。1．更新索引的统计信息在创建索引时，SQLServer会自动存储有关的统计信息，查询优化器会利用索引的统计信息来估算使用该索引查询数据的成本。但随着用户频繁执行插入、修改和删除等一系列操作后，这些统计信息可能已经过时了，因此有必要对索引的这些统计信息进行更新。返回目录8.3索引的维护1．更新索引的统计信息可以使用UPDATESTATISTICS命令来更新索引的统计信息。语法格式：UPDATESTATISTICS表名索引名【例8.7】使用UPDATESTATISTICS命令，更新在“教务管理数据库”数据库的“学生表”的“XS_XH_INDEX”索引的统计信息。返回目录8.3索引的维护2．使用DDCCSHOWCONTIG命令扫描表对表的频繁操作可能会导致表碎片，而表碎片会造成数据库查询性能的降底，用户可以使用DBCCSHOWCONTIG来扫描表，并通过返回值确定该表的索引页是否已经严重不连续。语法格式：DBCCSHOWCONTIG(表名，索引名)【例8.8】使用DBCCSHOWCONTIG命令获取“教务管理数据库”数据库的“学生表”的“XS_XH_INDEX”索引碎片信息返回目录8.3索引的维护3．使用DDCCINDEXDEFRAG命令整理碎片可以使用DBCCINDEXDEFRAG命令对聚集索引和非聚集索引页上的碎片进行整理。语法格式：DBCCINDEXDEFRAG（数据库名,表名,索引名）【例8.9】使用DBCCINDEXDEFRAG命令对“教务管理数据库”数据库的“学生表”的“XS_XH_INDEX”索引进行碎片整理。返回目录8.4视图概念8.4.1视图的概念视图是用户查询表中数据的一种方式，用户通过它可以浏览表中的部分或全部数据，而数据的物理存放位置仍存储在表中，这些表称为视图的基表。视图不是真实存在的数据表，而是一个虚拟表，它仅是一个查询语句的定义，视图中的数据可以来自于一个或多个基表，也可以来自视图。视图可以使用户集中在他们关心的数据上，不必考虑那些不必要的数据。这样，用户只能看到视图中显示的数据。而不能看到视图所引用的基表中的其他数据。在一定程度上保证了数据的安全。返回目录8.4视图概念8.4.2使用视图的优缺点1．视图的优点•限定特定的数据•简化数据操作•安全保护2．视图的缺点•降低性能•限制修改返回目录8.5视图的创建与管