数据库实验5ppt课件

实验5索引和优化,1,5.1创建索引,（1）利用Transact-SQL语句中的CREATEINDEX命令创建索引。语法格式：CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEX索引名ON数据表名|视图名(字段名ASC|DESC,.n)WITHPAD_INDEX,FILLFACTOR=填充因子,IGNORE_DUP_KEY,DROP_EXISTING,STATISTICS_NORECOMPUTE,SORT_IN_TEMPDBON文件组名,2,CREATEINDEX命令,参数说明：（1）UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。（2）CLUSTERED：用于指定创建的索引为聚集索引。（3）NONCLUSTERED：用于指定创建的索引为非聚集索引。（4）ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。（5）PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。必须和填充因子同时使用。（6）FILLFACTOR=填充因子：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。,3,4,CREATEINDEX命令,参数说明：（7）IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQLServer所作的反应。当使用该选项，表示当插入或更新记录时，忽略重复键值。（8）DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。（9）STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。（10）SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在tempdb数据库中。,5,1、利用对象资源管理器直接创建索引,例1为数据库“教学管理”中的学生数据表关于学生表.学号建立聚集索引，关于学生表.姓名建立非聚集索引。操作步骤如下：在对象资源管理器中，展开“教学管理”数据库，选择数据表学生表节点单击右键，在快捷菜单中选择“修改”。在设计器窗口中单击右键，在快捷菜单中选择“索引/键”，则出现新建索引对话框。单击“添加”按钮，系统自动给出新索引名（可以在“标识”里重新命名），然后再在“常规”中选择要索引的列“姓名”。单击【关闭】按纽，索引创建完毕。创建过程如下图所示。,6,7,2、利用SQL中的CREATEINDEX命令创建索引,USE教学管理GOCREATEUNIQUECLUSTEREDINDEXIX_学号ON学生表(学号)WITH(pad_index=ON,fillfactor=100)上述命令关于学生表.学号建立了升序惟一性聚集索引，索引名为IX_学号，填充因子为100。如果学生表表已经有聚集索引，则会出现下列错误信息：服务器：消息1902，级别16，状态3，行1不能在表学生表上创建多个聚集索引。请在创建新聚集索引前除去现有的聚集索引PK_学生表。,8,例2为数据库“教学管理”中数据表关于课程表.课名降序建立惟一索引IX_课程表_课名。方法一：使用对象资源管理器创建过程请参照上例，见上图创建索引窗口。方法二：使用SQL命令USE教学管理GOCREATEUNIQUEINDEXIX_课程表_课名ON课程表(课名DESC),9,例3为数据库“教学管理”中数据表关于教师表.姓名升序建立非聚集和非惟一索引IX_教师表_姓名。方法一：使用对象资源管理器创建过程请参照上例，见前图创建索引窗口。方法二：使用SQL命令USE教学管理GOCREATEINDEXIX_教师表_姓名ON教师表(姓名ASC),10,例4为数据库“教学管理”中数据表关于选课表.学号降序，选课表.开课号升序建立组合惟一索引IX_学号_开课号，填充因子为90，在插入数据时，可以忽略重复的值。如果已经存在IX_学号_开课号索引，则先删除后重建。USE教学管理GOCREATEUNIQUEINDEXIX_学号_开课号ON选课表(学号DESC,开课号ASC)WITH(PAD_INDEX=ON,-保持索引开放的空间FILLFACTOR=90,-填充因子90IGNORE_DUP_KEY=ON,-忽略重复键值DROP_EXISTING=ON)-如果存在IX_学号_开课号索引-则删除,如果不存在，则提示错误-中断索引创建,11,例5为数据库“教学管理”中数据表关于选课表.成绩降序建立非聚集索引IX_选课表_成绩。方法一：使用对象资源管理器创建过程请参照上例，见前图创建索引窗口。方法二：使用SQL命令USE教学管理GOIFEXISTS(SELECTnameFROMsysindexesWHEREname=IX_选课表_成绩)DROPINDEX选课表.IX_选课表_成绩-如果存在IX_选课表_成绩索引删除CREATENONCLUSTEREDINDEXIX_选课表_成绩ON选课表(成绩DESC),12,5.2删除索引,1删除索引的方法SQLServer删除索引的主要方法有：利用对象资源管理器删除索引；利用SQL语句中的DROPINDEX命令删除索引。（1）利用对象资源管理器删除索引其具体步骤如下：在对象资源管理器中，展开指定的服务器和数据库，选择要删除索引的表，用右键单击该表，从弹出的快捷菜单中选择所有任务项的管理索引选项，就会出现管理索引对话框，在该对话框中，可以选择要处理的数据库和表。选择要删除的索引，单击“删除”按钮。（2）利用SQL中的DROPINDEX命令删除索引其语法形式如下：DROPINDEX索引名，n,13,5.2删除索引,2删除索引实例分析方法一：使用对象资源管理器在对象资源管理器中，展开“教学管理”数据库，找到“选课表”。在“选课表”下选择“索引”。在管理索引窗口，在索引列表中选择要删除的索引IX_选课表_成绩，单击右键，出现快捷菜单，选择“删除”完成索引的删除工作。方法二：使用SQL命令USE教学管理GODROPINDEX选课表.IX_选课表_成绩,14,5.2查询中的执行计划,5.2.1查看查询执行计划5.2.2索引和未索引执行计划的比较,15,5.2.1查看查询执行计划,执行SELECT学号,姓名FROM学生表ORDERBY姓名按Ctrl+L或者在“查询”菜单上选择“显示估计的执行计划”显示这个查询的执行计划。,16,5.2.2索引和未索引执行计划的比较,1.检验堆结构先创建一个学生表_备份表。再将学生表里数据拷贝过来，则学生表_备份表因为没有定义主键，也没有索引，所以是一个堆结构。在执行之前按“Ctrl+M”或在“查询”菜单中选择“包括实际的执行计划”来包括实际的执行计划。然后执行查询学生表_备份表。,17,5.2.2索引和未索引执行计划的比较,2.检验聚集索引CREATEUNIQUECLUSTEREDINDEXCLIDX_学生表_备份_IDON学生表_备份(学号)再执行前面执行过的SELECT语句来检验区别。可以看出，这里的SQLServer不再使用表扫描。,18,5.2.2索引和未索引执行计划的比较,3.检验非聚集索引l堆如果表没有聚集索引，SQLServer将在非聚集索引的叶子级存储一个指向物理行的指针(文件id、页id合页中的行id)。在这种情况下，SQLServer通过查询索引进而依据指针指向来获取行的方式查找一个特定的行。l聚集索引当一个聚集索引存在的时候，SQLServer会在非聚集索引的叶子级将此行的聚集索引的键存储为指针。如果SQLServer要根据非聚集索引获取一行，会在非聚集索引中进行查找，找出合适的聚集键，然后再通过聚集索引来获取行。,19,5.2.2索引和未索引执行计划的比较,3.检验非聚集索引执行以下查询：SELECT*FROM学生表_备份orderby身份证号由于身份证号列没有索引，因此SQLServer执行了一次聚集索引操作，主要操作用到了排序上。,20,5.5.2索引和未索引执行计划的比较,3.检验非聚集索引为了加速这个查询，SQLServer需要身份证号列有一个索引。由于在学生表_备份表上已经定义了一个聚集索引，因此必须使用非聚集索引。CREATEINDEXCLIDX_学生表_备份_身份ON学生表_备份(身份证号)再执行前一个SELECT语句并按“Ctrl+L”来显示估计的执行计划。,21,5.3索引使用中的维护,5.3.1维护索引的统计信息5.3.2维护索引碎片,22,5.3.1维护索引的统计信息,例7显示指定索引的统计信息USE教学管理GODBCCSHOW_STATISTICS(学生表_备份,CLIDX_学生表_备份_身份)-显示学生表_备份上CLIDX_学生表_备份_身份索引的统计信息GO例8更新指定表的索引统计信息USE教学管理GOUPDATESTATISTICS学生表-更新学生表student的所有索引的统计GO例9对指定数据库中所有表的索引统计进行更新USE教学管理GOEXECUTEsp_updatestats,23,5.3.2维护索引碎片,1、索引碎片有两类：内部碎片和外部碎片。内部碎片：当索引页里还有空间可利用时，出现的碎片是内部碎片