天津理工大学 数据库实验四--查询优化

1、 实验报告学院（系）名称：计算机与通信工程学院姓名Touchkiss学号20125577专业计算机科学与技术班级2班实验项目查询优化课程名称数据库系统课程代码0668026实验时间2014/12/5实验地点7#215批改意见成绩教师签字：实验环境（软、硬件环境）一、 实验目的1. 了解数据库查询优化方法和查询计划的概念2. 学会分析查询的代价，并通过建立索引或者修改SQL语句来降低查询代价二、 实验内容与要求基于教材中的student、course、SC表，通过存储过程（要求在报告中写出存储过程），插入多条元组（1万条以上），考虑不同的SQL操作，分析比较执行代价。1 单表查询（1） 直接查询

2、：查询student表中年龄在20岁以上的学生记录（2） 建立索引后，再查询：查询student表中年龄在20岁以上的学生记录（3） 表中元组数量少，查询结果所占比例大：查询student表中年龄在20岁以上的学生记录（4） 表元组数量多，查询结果所占比例小：查询student表中年龄在20岁以上的学生记录分析以上四种SQL查询的执行效率，并做总结：2. 多表查询：基于student、course、SC表，按照以下要求，实现多表查询，并分析比较执行效率。（自行设计查询语句，要求针对同一查询内容，使用以下四种方式）（1） 多表连接查询（2） 嵌套查询（3） 建立索引（4） 使用游标分析比较：3综

3、合练习(1) 对于student表，不按照姓名创建索引，查询某个姓名，所需要的时间。(2) 对于student表，按照姓名创建索引，查询某个姓名，所需要的时间。(3) 对于student表，不按照系别创建索引，查询某个系所有学生，所需要的时间。(4) 对于student表，按照系别创建各种索引，查询某个系所有学生，所需要的时间。(5) 查询sc表所需时间。(6) 将student和sc连接所需时间。(7) 将student和sc和course连接所需时间。(8)查询选修了“数据库”学生的学号姓名，分别用嵌套和连接的方法，观察两种方法所用的时间。三、 本次实验总结： 基于教材中的student、

4、course、SC表，通过存储过程（要求在报告中写出存储过程），插入多条元组（1万条以上），考虑不同的SQL操作，分析比较执行代价。存储过程：create procedure insert_stusexflag nvarchar='男', age int=0, dept char(8)='MA',i int=1aswhile i<600000beginset age = 20if i%4 = 0 set age=17if i%4 = 1set age=18if i%4 = 2set age = 19if i%2 = 0set sexflag = '

5、男'elseset sexflag = '女'set dept = 'MA'if i%3 = 0set dept = 'CS'if i%3 = 1 set dept = 'IS'insert into dbo.Student values(i, 'Name'+cast(i as char), sexflag, age, dept)set i=i+1end create procedure insert_coui int = 1aswhile i<1000begininsert into dbo.Cou

6、rse (Cno,Cname,Ccredit) values(i, 'Course'+ cast(i as char), i%5)set i=i+1endUSE labGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure dbo.insert_s_ci int =1, j int =1aswhile i<6000 beginset j=1while j<1000begininsert into dbo.SC values(i,j,(i*j)%100)set j=j+1endset i=i+1en

7、d1.单表查询(1)直接查询：查询student表中年龄在20岁以上的学生记录dbcc dropcleanbuffersdbcc freeproccacheset statistics io on set statistics time on go select * from dbo.Student where Sage >= 20go set statistics io off set statistics time off (2)建立索引后，再查询：查询student表中年龄在20岁以上的学生记录(3)表中元组数量少，查询结果所占比例大：查询student表中年龄在19岁以下的学生记

8、录STU表中共1000条数据(4)表元组数量多，查询结果所占比例小：查询student表中年龄在20岁以上的学生记录Student表中有600000条数据分析以上四种SQL查询的执行效率，并做总结：2.多表查询：基于student、course、SC表，按照以下要求，实现多表查询，并分析比较执行效率。（自行设计查询语句，要求针对同一查询内容，使用以下四种方式）（1）多表连接查询set statistics io on set statistics time on go select Sname from dbo.Student,dbo.Course,dbo.SCwhere Student.Sn

9、o=SC.Sno and SC.Cno = Course.Cno and Cname='Course1' and Grade>90go set statistics io off set statistics time off（2）嵌套查询set statistics io on set statistics time on go select Sname from Student where Sno in (select Sno from SC where Grade > 90 and Cno =(select Cno from Course where Cnam

10、e = 'Courserse1') go set statistics io off set statistics time off（3）建立索引set statistics io on set statistics time on go create index Studentgra1 on SC(Grade)select Sname from dbo.Student,dbo.Course,dbo.SCwhere Student.Sno=SC.Sno and SC.Cno = Course.Cno and Cname='Courserse1' and Grad

11、e>90go set statistics io off set statistics time off（4）使用游标set statistics io on set statistics time on go declare my_cursor cursor scroll dynamicforselect Sname from dbo.Student,dbo.Course,dbo.SCwhere Student.Sno=SC.Sno and SC.Cno = Course.Cno and Cname='Course1' and Grade>90open my_cu

12、rsordeclare name sysnamefetch next from my_cursor into namewhile(fetch_status=0)beginprint namefetch next from my_cursor into nameendclose my_cursordeallocate my_cursorgo set statistics io off set statistics time off分析比较：3综合练习(1) 对于student表，不按照姓名创建索引，查询某个姓名，所需要的时间。set statistics io on set statistics

13、 time on go select * from dbo.Student where Sname = 'Name1999'go set statistics io off set statistics time off(2) 对于student表，按照姓名创建索引，查询某个姓名，所需要的时间。set statistics io on set statistics time on go create index Snameon Student(Sname)select * from dbo.Student where Sname='Name1999'go set

14、 statistics io off set statistics time off(3) 对于student表，不按照系别创建索引，查询某个系所有学生，所需要的时间。set statistics io on set statistics time on go select * from dbo.Student where Sdept='CS'go set statistics io off set statistics time off(4) 对于student表，按照系别创建各种索引，查询某个系所有学生，所需要的时间。set statistics io on set sta

15、tistics time on go create index Sdepton dbo.Student (Sdept)select * from dbo.Student where Sdept='CS'go set statistics io off set statistics time off(5) 查询sc表所需时间。set statistics io on set statistics time on go select * from dbo.SCgo set statistics io off set statistics time off(6) 将student和s

16、c连接所需时间。set statistics io on set statistics time on go select * from dbo.Student,dbo.SC where Student.Sno=SC.Snogo set statistics io off set statistics time off(7) 将student和sc和course连接所需时间。set statistics io on set statistics time on go select * from dbo.Student,dbo.SC,dbo.Coursewhere Student.Sno=SC.Sno and SC.Cno=Course.Cnogo set statistics io off set statistics time off(8)查询选修了“数据库”学生的学号姓名，分别用嵌套和连接的方法，