模块八++索引

2020年3月1日第1页教学目标理解索引的优点和缺点理解堆的结构特点理解聚集索引和非聚集索引的特点理解索引的类型使用CREATEINDEX语句创建索引的方式理解索引统计信息的特点和获得方式理解查询优化的方式模块八索引及其应用2020年3月1日第2页教学过程8.1综述8.2索引的操作8.3设置索引的选项8.4索引的分析与维护8.5索引视图2020年3月1日第3页8.1索引综述在MicrosoftSQLServer系统中，可管理的最小空间是页。一个页是8KB字节的物理空间。插入数据的时候，数据就按照插入的时间顺序被放置在数据页上。一般地，放置数据的顺序与数据本身的逻辑关系之间是没有任何联系的。因此，从数据之间的逻辑关系方面来讲，数据是乱七八糟堆放在一起的。数据的这种堆放方式称为堆。当一个数据页上的数据堆放满之后，数据就得堆放在另外一个数据页上，这时就称为页分解。索引是一种与表或视图关联的物理结构，可以用来加快从表或视图中检索数据行的速度。2020年3月1日第4页SQLServer2008支持在表中任何列（包括计算列）上定义索引。按索引结构的组织方式不同，可以将索引分为聚集索引和非聚集索引。1）聚集索引聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，当建立主键约束时，如果表中没有聚集索引，SQLSWERVER2008会用主键列作为聚集索引键。2）非聚集索引非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针,最多建立250个非聚集索引或249个非聚集索引和1个聚集索引。3)唯一索引要求索引中的字段值不能重复,可以建立唯一索引，也可以对多个字段的组合创建索引。索引是以表列为基础的数据库对象，它保存着表中排序的索引列，并记录了索引列在数据表中的物理存储位置，实现了表中数据的逻辑排序。2020年3月1日第5页使用索引的意义和代价第一种方法是表扫描：就是指系统将指针放在该表的表头数据所在的数据页上，然后按照数据页的排列顺序，一页一页地从前向后扫描该表数据所占有的全部数据页，直至扫描完表中的全部记录。第二种方法是使用索引查找。代价：(1)索引需占用数据表以外的物理存储空间(2)创建索引和维护需要花费时间(3)对表进行更新时，索引需重建，降低了数据的维护速度。2020年3月1日第6页建立索引的原则对经常用来检索的字段建立索引；对数据表中的主键一定建立索引；对数据表中的外键可以建立索引；对于经常用于连接的字段建立索引；查询中很少涉及的列、重复值比较多的列不要建索引；定义为text,image和bit数据类型的列不要建立索引。2020年3月1日第7页8.2索引的操作在MicrosoftSQLServer2008系统中，既可以直接创建索引，也可以间接创建索引。当直接创建索引时，既可以使用CREATEINDEX语句，也可以使用图形工具。可以把创建索引的方式分为直接方法和间接方法。直接创建索引的方法就是使用命令和工具直接创建索引。间接创建索引就是通过创建其他对象而附加创建了索引。例如：在表中定义主键约束或惟一性约束时，同时也创建了索引。8.2.1创建索引的操作2020年3月1日第8页创建索引应注意的几点：只有表或视图的所有者才能创建索引，并随时创建。创建索引时，对表进行复制，对数据进行排序，然后删除原始的表，因此数据库必须有足够的空间容纳数据的复本。使用CREATEINDEX创建索引时，必须指定索引名称、表及索引所应用的列的名称。一个表最多创建249个非聚集索引。复合索引的列的最大数目为16，各列组合的最大长度900B。2020年3月1日第9页1、使用资源管理器创建索引（在当前数据库下表设计器中创建索引）新建索引对话框2020年3月1日第10页2、使用CREATEINDEX语句创建索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON{表名|视图名}(字段名[ASC|DESC][,...n])[INCLUDE(column_name[,...n])][WITH(索引选项[,...n])][ON文件组]索引选项包含以下几项：(1)DROP_EXISTING：表示先删除存在的索引。(2)IGNORE_DUP_KEY：控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。(3)FILLFACTOR：填充因子。指定SQLServer2008创建索引的过程中，各索引页叶级的填满程度，取值1-100。(4)PAD_INDEX:索引中间级中每个页（节点）保持开放的空间。2020年3月1日第11页例：在表student中创建名为“IX_telephone”的唯一索引，该索引基于列“telephone”。createuniqueindexIX_telephoneonstudent(telephone)如果在此字段上创建聚集索引，结果如何？createclusteredindexpk_telephoneonstudent(telephone)错误信息：消息1902，级别16，状态3，第1行无法对表'student'创建多个聚集索引。请在创建新聚集索引前删除现有的聚集索引'PK_student'。CREATEINDEX索引名ON表名(字段名[ASC|DESC][,...n])表示创建不唯一，非聚集索引。2020年3月1日第12页3、修改表的结构时添加索引例：在student表中为sname字段添加唯一键同时添加了唯一非聚集索引。AltertablestudentAddunique(sname)Go如果表中已有数据，则在创建唯一索引时，SQLServer2008自动检验是否存在重复值，如果存在，则不能创建唯一索引。2020年3月1日第13页8.2.2查看索引信息1、在MicrosoftSQLServer2008系统中，可以使用资源管理器查看索引信息。2、使用存储过程sp_helpindex查看有关索引的信息。例：查看表student的索引信息。execsp_helpindexstudent2020年3月1日第14页1、使用SQL资源管理器2、使用系统存储过程Sp_rename不提倡使用该系统存储过程对索引进行命名,因为会破坏脚本语言。8.2.3索引更名2020年3月1日第15页8.2.4删除索引1、在资源管理器中删除2、使用DROPindex语句删除由createindex创建的索引。例：DROPindexstudent.IX_telephone注意：1）无法使用DROPindex删除由主键约束或唯一键约束创建的索引。这些索引必须通过删除主键约束或唯一键约束，由系统自动删除。2）在删除聚集索引时，表中的所有非聚集索引都将被重建。2020年3月1日第16页8.3设置索引的选项1、设置FILLFACTOR选项时考虑的因素：（1）填充因子的值从1到100之间的整数（2）值为100表示页将填满，留出的存储空间量最少。（3）值越小则数据页的空闲空间越大。（4）只有在创建或重新生成了索引后，才会应用填充因子。例题：在’student’表中基于’sname’字段创建非聚集索引sh_sn_index,其填充因子为70。Use学生库GoCreateindexsh_sn_indexonstudent(sname)WITHFILLFACTOR=70go2020年3月1日第17页2、设置PAD_INDEX选项FILFACTOR用来指定各索引页叶级的填满程度，对于非叶级索引页需要使用PAD_INDEX选项设置其预留空间的大小。例题：在’student’表中基于’sname’字段创建非聚集索引sh_sn_index,其FILLFACTOR,PAD_INDEX均为70。Use学生库GoCreateindexsh_sn_indexonstudent(sname)WITHPAD_INDEX,FILLFACTOR=70go2020年3月1日第18页8.4索引的分析和维护1、SHOWPLAN显示查询语句的执行信息例：在查询设计器下执行以下查询，显示执行计划。setSHOWPLAN_textongoselect学生表.学号,学生表.姓名,选修表.课程号,选修表.成绩from学生表,选修表where学生表.学号=选修表.学号gosetSHOWPLAN_textoffgo2020年3月1日第19页2、STATISTICSIO显示执行数据检索语句所花费的磁盘活动量信息例：在查询设计器下执行以下查询，显示数据I/O统计。setSTATISTICSIOon–打开I/O统计goselect学生表.学号,学生表.姓名,选修表.课程号,选修表.成绩from学生表,选修表where学生表.学号=选修表.学号gosetSTATISTICSIOOFF–关闭I/O统计go2020年3月1日第20页8.5索引的维护索引在创建之后，由于数据的增加、删除、更新等操作使得索引页发生碎块，因此为了提高系统的性能，必须对索引进行维护。这些维护包括查看碎块信息、维护统计信息、分析索引性能、删除重建索引等。2020年3月1日第21页1、查看索引碎片信息可以使用两种方式查看有关索引的碎片信息，使用DBCCSHOWCONTIG命令和使用图形化工具。例：利用DBCCSHOWCONTIG获取表“学生表”主键索引的碎片信息。setSHOWPLAN_textoffGODBCCSHOWCONTIG(学生表,pk_学生表)注意：如果扫描密度百分比较低（理想数为100%），就需要清理表上的碎片了。2020年3月1日第22页2、碎片整理使用DBCCINDEXDEFRAG语句进行碎片整理。例：使用DBCCINDEXDEFRAG语句对学生库数据库中的表“学生表”进行碎片整理。setSHOWPLAN_textoffGODBCCINDEXDEFRAG(学生库,学生表)2020年3月1日第23页3、维护索引统计信息设置统计信息自动更新：右击“数据库名”-”属性”-”选项”-”自动创建统计信息“设置为”TRUE”例：使用UPDATESTATISTICS命令更新表“学生表”主键索引的统计信息。UPDATESTATISTICS学生表pk_学生表2020年3月1日第24页创建索引视图注意以下几点：（1）索引视图只能引用基表，不能引用其他视图；（2）索引视图基表必须和视图位于同一数据库；（3）索引视图基表名称由两部分构成；（4）创建时要使用SCHEMABINDING选项；（5）若视图定义使用聚合函数，SELECT列表必须包括COUNT_BIG(*)；（6）视图中的表达式引用的所有函数必须是确定的。8.6索引视图2020年3月1日第25页问题：创建索引视图v_S1选课门数。使用该视图查询每个学生选修课程的数量。该视图包含“学号”，“姓名”，“选修门数”。Use学生库GoIFEXISTS(SELECT*FROMsysobjectsWHEREname='v_s1')DROPVIEWv_s1CREATEVIEWdbo.v_s1WITHSCHEMABINDINGASSELECTdbo.学生表.学号,dbo.学生表.姓名,COUNT_BIG(*)AS选修科目个数FROMdbo.选修表,dbo.学生表Wheredbo.学生表.学号=dbo.选修表.学号GROUPBYdbo.学生表.学号,dbo.学生表.姓名2020年3月1日第26页/*创建索引视图，形成唯一性索引*/Createuniqueclusteredindexxhxmondbo.v_s1(学号,姓名)