第7章 使用视图和索引优化查询

,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,单击此处编辑母版标题样式,第,7,章 使用视图和索引优化查询,复习,:,数据库的基本关系运算,投影,(,查询指定的列,),连接,(,查询多个表,),选择,(,查询符合条件的行,),基本查询,对一张表的查询,对表中的原始数据的查询,分别用什么子句实现,?,select,列表名,from,表名,n,where,条件,例：,select,*,from,xsqk,where,出生日期,75,by,学号,order by,学号,复习,:,连接查询：同时查询,多个,表中的信息。,内连接：将多表中,满足条件,的行连接起来。,select,xsqk.,学号,姓名,课程号,成绩,from,xsqk,xs_kc,where,xsqk.,学号,=xs_kc.,学号,and,成绩,60,select,课程名称,授课教师,from,kc,where,课程号,=ANY,(,select,课程号,from,xs_kc,where,成绩,=all(,select,成绩,from,xs_kc),三、修改,视图,语法格式：,alter view,视图,(,列名表,),with,encryption,as,select,查询语句,with check option,通过对象资源管理器修改视图,三、修改,视图,实例,4,：在“,v1”,的视图中增加两列：专业名和所在系。,alter view,v1,(,学生学号,男生姓名,生日,专业,系,),as,select,学号,姓名,出生日期,专业名,所在系,from,xsqk,where,专业名,=,计算机网络,and,性别,=1,三、修改,视图,实例,5,：修改名为“,v,综合信息”的视图，取消其加密属性，增加选项检查属性。,alter view,V,综合信息,as,select,xsqk.,学号,姓名,专业名,课程名,成绩,from,xsqk,xs_kc,kc,where,xsqk.,学号,=xs_kc.,学号,and,xs_kc.,课程号,=kc.,课程号,with check option,四、删除,视图,语句格式,drop,view,视图名,n,五、使用视图管理数据表中的数据,说明：,视图可以代替表完成从表中查询、插入、更新和删除数据的操作。,五、使用视图管理数据表中的数据,-1.,插入数据,实例,6,：向“,V1”,视图中添加两条记录。,insert into,v1,values,(,020106,李好,1981-04-22,计算机网络,计算机,),insert into,v1,values,(,020107,周权,1981-07-08,信息安全,计算机,),select,*,from,xsqk,select,*,from,v1,请思考,:V1,视图中为什么不能看到,周权,的记录,?,因为,周权,的记录不满足视图的定义,即视图中只显示,“,计算机网络,”,专业的学生信息，所以,周权,的信息只能在基表中看到,而不能在视图中看到。,五、使用视图管理数据表中的数据,-1.,插入数据,插入数据的注意事项,可通过视图向基表中插入数据，但,插入的数据,实际上,存放在基表中,，而不是存放在视图中。,如果视图引用了多个表，使用,insert,语句插入的列必须属于同一个表。,若创建视图时定义了,“,with check option,”,选项，则使用视图向基表中插入数据时，必须保证插入后的数据,满足,定义视图的,限制条件,。,说明：如果不想让,不满足,视图定义的数据插入到基表中，可以在定义视图时，加上“,with check option”,。,五、使用视图管理数据表中的数据,-1.,插入数据,实例,7,：假设“,V1”,视图已添加了“,with check option,”,属性，通过视图向基表中添加两条记录。,insert into,v1,values,(,020108,赵伟,1980-05-07,计算机网络,计算机,),insert into,v1,values,(,020109,刘齐,1981-12-02,信息安全,计算机,),请思考：这两条记录能否插入到基表中？能否在视图中能否显示？,“,赵伟,”,的信息既能插入到基表中，也能在视图中看到。但,“,刘齐,”,的信息都不能，且系统会提示错误。,五、使用视图管理数据表中的数据,-2.,删除,数据,语句格式：,delete from,视图名,where,条件,实例,8,：删除“,V1”,视图中学号为,020108,的记录。,delete from,V1,where,学号,=020108,注意事项：,要删除的数据必须包含在视图的结果集中。,如果视图引用了多个表时，无法用,delete,命令删除数据。,引,:,请思考,:,在一个数据表中检索数据时是如何进行的,?,如果采取逐行扫描的方式进行检索,可行吗,?,有什么方法可提高检索效率？,对于小表来说，是可行的。但对于有成千上万行的表来说，其搜索的效率非常低。,建立索引。,索引,学习目标,认知目标：,掌握索引的概念,理解创建索引的必要性,掌握几种索引类型的特点和它们适合的场合,能力目标,:,掌握用命令方式创建索引的方法,(,重点、难点,),掌握查看、删除索引的方法,(,重点,),一、索引的基本概念,1.,索引的含义,索引：是对表或视图中的一列或多个列的值进行排序的一种物理结构，它由该表或视图中的一列或若干列值，以及这些列值的记录在数据表中存储位置的物理地址所组成。,索引如同书的目录,索引信息存放在,索引页,中，表中的数据存放在,数据页,中。,一、索引的基本概念,2.,建立索引的优缺点,建立索引的优点,建立了索引的列作为查询条件时，数据的检索速度能大大地提高。,增加索引的不利方面,创建索引也要花费时间和占用物理空间。,虽然索引加快了检索速度，但减慢了数据修改的速度,(,因为每执行一次数据修改，就需要对索引进行维护,),。,一、索引的基本概念,3.,使用索引的准则,何时创建索引？,在主键上,在用于连接的列,(,外键,),上,在经常用作查询条件的列上,在经常要排序的列上,一、索引的基本概念,3.,使用索引的准则,何时可不考虑建立索引？,很少或从来不作为查询条件的列,在小表中的任何列,属于,text,、,image,、,长度较大的,char,、,varchar,、,binary,等数据类型的列,当修改的性能需求远大于查询的性能需求时，不要创建索引,一、索引的基本概念,4.,索引的类型,按数据的物理存放顺序：,聚集索引,:,会改变表记录的物理存储顺序，使之与索引列的顺序完全相同（适用于范围搜索）。,非聚集索引,:,不改变表记录的存放顺序（适用于直接匹配单个条件的查询）。,按索引列取值的唯一性：,唯一索引,:,要求被索引列不能有重复值，也不能有两个,NULL,。,非唯一索引,:,不存在这一限制。,按索引列的个数：,单索引、复合索引,二、创建索引,1.,创建索引的方法,系统自动建立索引,人工创建索引,T-SQL,命令方式（,掌握,）,对象资源管理器方式,二、创建索引,2.,系统自动建立索引,添加主键约束时,系统会自动在表中生成一个聚集惟一索引。,在,xsqk,表的“学号”列上已创建了聚集惟一索引,(,名为,PK_xsqk),在,xs_kc,表的“学号和课程号”两列上已创建了聚集惟一索引,在,kc,表中的“课程号”列上已创建了聚集惟一索引,添加惟一性约束时,系统会自动在表中生成一个非聚集惟一索引。,二、创建索引,2.,系统自动建立索引,实例,1,：创建一个名为,test,的表，其结构定义如下。,use,xscj,create table,test,(,课程号,smallint constraint,pk_test,primary key,课程名,char,(14),constraint,uq_test,unique,授课教师,char,(10),),生成惟一性聚集索引,生成惟一性非聚集索引,二、创建索引,3.,手工建立索引,(1),表索引,命令格式,create,unique,clustered,|,nonclustered,index,索引名,on,表名,|,视图名,(,列名,asc,|,desc,.,n,),说明：,一个表中只能创建,1,个聚集索引,。,由于系统已自动在主键上创建了聚集索引，所以用户不能再创建，除非先删除已有的。,一个表中可以创建,若干个非聚集索引,。,默认值,二、创建索引,3.,手工建立索引,(1),表索引,实例,2,：在,kc,表的“课程名”列上创建一个非聚集索引，索引名为“,ix_kcm”,，并降序排列。,实例,3,：在,kc,表中，重新创建名为“,ix_kcm”,的索引，使其成为惟一性的非聚集索引。,create unique index,ix_kcm,on,kc,(,课程名,desc,),with drop_existing,create index,ix_kcm,on,kc,(,课程名,desc,),删除同名的原索引，重建新索引。,二、创建索引,3.,手工建立索引,(1),表索引,实例,4,：已知,xs_kc,表中的“学号”和“课程号”两列已设置为主键，主键约束名为“,PK_xs_kc”,，请重新创建这两列的组合索引，使学号降序排列，使索引页填满,60%,后就换新页进行填充。,create unique clustered index,pk_xs_kc,on,xs_kc(,学号,desc,课程号,),with pad_index,fillfactor,=60,drop_existing,中间级索引页也按“填充因子”指定的填充度填充,叶级索引页按,60%,进行填充,二、创建索引,3.,手工建立索引,(1),表索引,课堂练习：已知,xsqk,表中的“学号”列已设置为主键，请在“姓名”和“所在系”两列上创建名为“,ix_name_xi”,的索引，按姓名降序排列，并使索引页填满,60%,后就换新页进行填充。,请考虑：,是否要创建惟一性索引？能否创建聚集索引？,create index,ix_name_xi,on,xsqk,(,姓名,desc,所在系,),with pad_index,fillfactor,=60,三、操作索引,1.,查看索引,命令格式：,exec,sp_helpindex,表名,|,视图名,用对象资源管理器方式,三、操作索引,2.,删除索引,命令格式：,drop index,表名,.,|,视图名,.,索引名,n,实例,6,：删除表中的索引。,drop index,kc.,ix_kcm,xsqk.ix_name_xi,注意：,SQL Server,自动建立的索引不能用,drop index,删除，只能用,alter table,语句中的,drop constraint,子句来解除加在该字段上的主键约束或惟一性约束，这些约束一解除，相关的索引也就被删除了。,本课小结,使用视图的目的,创建视图：,create view,修改视图：,alter view,查看视图：,select,*,from,视图名,通过视图管理数据表中的数据,插入数据,删除数据,索引的作用,提高检索数据的速度,使用索引的准则,索引的类型,聚集和非聚集索引,惟一和非惟一索引,单和复合索引,创建索引的方法,系统自动创建索引,添加主键约束时,添加惟一性约束时,人工创建索引,命令方式,对象资源管理器,删除索引的方法,drop index,索引名,alter table,表名,drop constraint,约束名,