第7章-索引与视图

索引的优点和缺点聚集索引和非聚集索引的特点索引的类型视图的概念、特点和类型使用CREATEVIEW语句创建视图通过视图修改基表中的数据通过创建和设计良好的索引进行数据查询，可以显著提高数据库查询和应用程序的性能，减少磁盘I/O操作，降低系统资源的消耗。数据库系统用下列两种方法之一来访问数据：（1）表扫描，就是指系统将指针放在该表的表头数据所在的数据页上，然后按照数据页的排列顺序，逐页地从前向后扫描该表数据所占有的全部数据页，直至扫描完表中的全部记录。在扫描时，如果找到符合查询条件的记录，那么就将这条记录挑选出来。最后，将全部挑选出来符合查询语句条件的记录显示出来。（2）使用索引查找。索引是一种树状结构，其中存储了关键字和指向包含关键字所在记录的数据页的指针。当使用索引查找时系统将沿着索引的树状结构，根据索引中关键字和指针找到符合查询条件的记录。最后将全部查找到的符合查询语句条件的记录显示出来。索引是一种与表或视图关联的物理结构，能提供一种以一列或多列的值为基础迅速查找表中行的能力，用来加快从表或视图中检索数据行的速度。为什么要创建索引呢？这是因为创建索引可以大大提高系统的性能：（1）通过创建唯一性索引，可以保证每一行数据的唯一性。（2）可以大大加快数据的检索速度，这也是索引的最主要的原因。（3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。（4）在使用ORDERBY和GROUPBY子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。（5）通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。既然增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？虽然索引有许多优点，但是为表中的每一个列都增加索引是非常不明智的做法。这是因为增加索引也有缺点：（1）创建索引和维护索引要耗费时间。（2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚集索引，那么需要的空间就会更大。（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。因为上述这些原因，建立索引需要花费一定的时间和存储空间，而且使用INSERT和UPDATE对数据进行插入和更新操作时，维护索引在时间和空间上也需要一定的开销。因此没有必要对表中所有的列建立索引，而应该根据实际情况来创建索引。在MicrosoftSQLServer2008R2系统中有两种基本的索引类型：聚集索引和非聚集索引。除此之外，还有唯一性索引、包含性列索引、索引视图、全文索引及XML索引等。（1）堆文件堆是不含聚集索引的表，表中的数据没有任何的顺序。堆结构中数据按照插入的先后次序存放，堆文件的数据页面不一定在物理上相邻。堆文件执行插入操作很容易，但是效率不高。（2）B+树的总体结构B+树（Balance树，平衡树）是目前广泛采用的动态文件结构.在B+树索引中，索引分级组织成一棵树。最上层的索引节点称为根节点，中间节点按照索引数据量分成不同的层次。最下层的索引节点称为叶节点。聚集索引是一种数据表的物理顺序与索引顺序相同的索引。建立索引时，系统将对表的物理数据页中的数据按列进行排列，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的。聚集索引的叶级和非叶级构成了一个特殊类型的B+树结构.聚集索引的特点如下：（1）表的数据按照索引的数据顺序排列。（2）每个数据表只能建立一个聚集索引，并且会在第一个建立，常常会在主键所在的列或者最常查询的列上建立聚集索引。（3）索引将占用用户数据库的空间。（4）适合范围查询。非聚集索引是一种数据表的物理顺序与索引顺序不相同的索引。非聚集索引与聚集索引具有相同的B树结构，但是在非聚集索引中，基础表的数据行不是按照非聚集键的顺序排序和存储，且非聚集索引的叶级是由索引页而不是由数据页组成。非聚集索引既可以定义在表或视图的聚集索引上，也可以定义在表或视图的堆上。创建一个非聚集索引时，应该注意下列事项：（1）如果没有指定索引类型，那么默认的类型是非聚集索引。（2）索引页的叶级只包含索引的关键字，不包含实际的数据（3）每个表最多可以创建249个非聚集索引。（4）聚集索引应在非聚集索引被创建之前创建。（5）唯一性是由叶级维护的。（6）以下情况发生时，SQLServer会自动重建现有的非聚集索引：删除现有的聚集索引时创建聚集索引时使用DROP_EXISTING选项来改变聚集索引列的定义时唯一索引是指索引值必须是唯一的，不允许数据表中具有两行相同的索引值。聚集索引和非聚集索引是从索引数据存储的角度来区分的；而唯一索引和非唯一索引是从索引值来区分的，所以唯一索引和非唯一索引既可以是聚集索引，也可以是非聚集索引，只要列中的数据是唯一的，就可以在一张表中创建一个唯一索引和多个非聚集索引。7.2.1创建索引创建索引的方式可以分为直接方法和间接方法。◦直接创建索引的方法就是使用命令和工具直接创建索引。◦间接创建索引就是通过创建其他对象而附加创建了索引。主键约束或唯一性约束创建的索引的优先级高于使用CREATEINDEX语句创建的索引。当在表上定义主键或唯一性约束时，如果表中已经有了使用CREATEINDEX语句创建的标准索引时，那么主键约束或唯一性约束创建的索引覆盖以前创建的标准索引。（1）从“开始”菜单上选择“程序”|MicrosoftSQLServer2008R2|SQLServerManagementStudio命令，使用Windows或SQLServer身份验证建立连接。（2）在“对象资源管理器”窗口中，打开要建立索引的表“业务记录”表，单击前面的“+”，展开选项，右键单击“索引”项，选择“新建索引”命令，打开“新建索引”对话框（3）在“选择页”窗格中选择“常规”项，输入索引名称“入住时间_index”，选择非聚集索引类型，单击“添加”按钮，打开“选择列”对话框，如图7-5所示。在该窗体中选择“入住时间”字段作为索引列，单击“确定”按钮，返回“新建索引”对话框。（4）在“选项”页可设置索引的填充因子等参数，填充因子指示索引页的填满程度。在“包含性列”页中，设置索引页还可以包含的其他非键列。在“存储”页，可以对指定的文件组或分区方案建立索引。在“空间”页可以设置空间索引的空间属性。在“筛选器”页可以定义筛选索引的筛选表达式。（5）设置完成后，单击“确定”按钮，则该索引创建完成。CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameONtable_or_view_name(column[ASC|DESC][,...n])[INCLUDE(column_name[,…n])][WITH(PAD_INDEX={ON|OFF}|FILLFACTOR=fillfactor|SORT_IN_TEMPDB={ON|OFF}|IGNORE_DUP_KEY={ON|OFF}|STATISTICS_NORECOMPUTE={ON|OFF}|DROP_EXISTING={ON|OFF}|ONLINE={ON|OFF}|ALLOW_ROW_LOCKS={ON|OFF}|ALLOW_PAGE_LOCKS={ON|OFF}|MAXDOP=max_degree_of_parallelism)[,…n]])ON{partition_schema_name(column_name)|filegroup_name|default}UNIOUE选项表示创建唯一性的索引，这时在索引列中不能有相同的两个列值存在。CLUSTERED选项表示创建聚集索引。NONCLUSTERED选项表示创建非聚集索引，非聚集索引是CREATEINDEX语句的默认值。第一个ON关键字表示索引所属的表或视图，这里用于指定表或视图的名称和相应的列名称。列名称后面可以使用ASC或DESC关键字，指定升序排列或降序排列，其默认值是ASC。第二个ON关键字用于指定该索引所在的分区方案或文件组名称。INCLUDE子句用于指定将要包含到非聚集索引的页级中的非键列。PAD_INDEX选项用于指定索引的中间页级，也就是说为非叶级索引页指定填充度。这时的填充度由FILLFACTOR选项指定。FILLFACTOR选项用于指定叶级索引页的填充度。SORT_IN_TEMPDB选项为ON时，用于指定创建索引时产生的中间结果，在tempdb数据库中进行排序。该选项为OFF时，在当前数据库中排序。IGNORE_DUP_KEY选项用于指定唯一性索引键冗余数据的系统行为。当为ON时，系统发出警告信息，只有违反唯一性行的数据插入失败。该选项为OFF时，取消整个INSERT语句并且发出错误信息。STATISTICS_NORECOMPUTE选项用于指定是否重新计算分发统计信息。为ON时，不自动计算过期的索引统计信息。为OFF时，启动自动计算功能。DROP_EXISTING选项用于是否可以删除指定的索引并且重建该索引。为ON时，可以删除并且重建已有的索引。为OFF时，不能删除重建。ONLINE选项用于指定索引操作期间基础表和关联索引是否可用于查询。为ON时，不持有表锁，允许用于查询。为OFF时，持有表锁，索引操作期间不能执行查询。ALLOW_ROW_LOCKS选项用于指定是否使用行锁，如果为ON，表示使用行锁。ALLOW_PAGE_LOCKS选项用于指定是否使用页锁，如果为ON，表示使用页锁。MAXDOP选项用于指定索引操作期间覆盖最大并行度的配置选项，主要目的是限制执行并行计划过程中使用的处理器数量。例7-1例7-2例7-3例7-4例7-5例7-61.直接使用MicrosoftSQLServerManagementStudio查看索引（1）在“对象资源管理器”窗口中，打开要查看索引的表“客房信息”表，单击右键，在弹出菜单中，选择“设计”，进入表设计器。（2）在“表设计器”中，打开“索引/键”窗口。如图7-6所示。可以查看表的所有索引，选中某个索引后还可以查询该索引的名称和列字段等属性。2.使用系统视图查看索引信息（1）系统视图sys.indexes◦sys.indexs用于显示数据库元数据中存储的索引类型/文件组或分区方案ID和索引选项的当前设置。例7-7（2）系统视图sys.index_columns◦sys.index_columns可以查看索引的列ID、索引内的位置、索引列的排序顺序等。例7-83.使用系统函数查看索引信息（1）系统函数sys.dm_db_index_usage_stats◦sys.dm_db_index_usage_stats用于显示不同类型索引操作的计数以及上次执行每种操作的时间。例7-9（2）系统函数sys.dm_db_index_operational_stats◦sys.dm_db_index_operational_stats用于显示数据库中表或索引的每个分区的当前低级I/O、锁定、闩锁和访问方法活动。例7-10【例7-10】查看数据库Hotel中“客房信息”表的所有索引和分区的信息。DECLARE@db_idsmallintDECLARE@object_idintSET@db_id=DB_ID(N'Hotel')SET@object_id=OBJECT_ID(N'Hotel.dbo.客房信息')IF@db_idISNULLBEGINPRINTN'Invaliddatabase'ENDELSEIF@object_idISNULLBEGINPRINTN'Invalidobject'ENDELSEBEGINSELECT*FROMsys.dm_db_index_operational_stats(@db_id,@object_id,NULL,NULL)ENDGO1.直接使用Microsoft