天津理工大学 数据库实验四--查询优化(共13页)

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

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

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

4、写出存储过程），插入多条元组（1万条以上(yshng)），考虑不同的SQL操作，分析比较执行代价。存储过程：create procedure insert_stusexflag nvarchar=男, age int=0, dept char(8)=MA,i int=1aswhile i600000beginset age = 20if i%4 = 0 set age=17if i%4 = 1set age=18if i%4 = 2set age = 19if i%2 = 0set sexflag = 男elseset sexflag = 女set dept = MAif i%3 = 0set

5、 dept = CSif i%3 = 1 set dept = ISinsert into dbo.Student values(i, Name+cast(i as char), sexflag, age, dept)set i=i+1end create procedure insert_coui int = 1aswhile i1000begininsert into dbo.Course (Cno,Cname,Ccredit) values(i, Course+ cast(i as char), i%5)set i=i+1endUSE labGOSET ANSI_NULLS ONGOSE

6、T QUOTED_IDENTIFIER ONGOALTER procedure dbo.insert_s_ci int =1, j int =1aswhile i6000 beginset j=1while j= 20go set statistics io off set statistics time off (2)建立索引后，再查询：查询student表中年龄在20岁以上(yshng)的学生记录(3)表中元组数量少，查询结果(ji gu)所占比例大：查询student表中年龄在19岁以下(yxi)的学生(xu sheng)记录STU表中共1000条数据(4)表元组数量多，查询结果所占比例

7、小：查询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.Sno=SC.Sno and SC.Cno = Course.Cno

8、 and Cname=Course1 and Grade90go set statistics io off set statistics time off（2）嵌套查询(chxn)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 Cname = Courserse1) go set statistics io off set

9、statistics time off（3）建立(jinl)索引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 Grade90go set statistics io off set statistics time off（4）使用(sh

10、yng)游标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 Grade90open my_cursordeclare name sysnamefetch next from my_cursor into namewhile(fetch

11、_status=0)beginprint namefetch next from my_cursor into nameendclose my_cursordeallocate my_cursorgo set statistics io off set statistics time off分析(fnx)比较：3综合(zngh)练习(1) 对于(duy)student表，不按照姓名创建索引，查询某个姓名，所需要的时间。set statistics io on set statistics time on go select * from dbo.Student where Sname = Na

12、me1999go 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=Name1999go set statistics io off set statistics time off(3) 对于(duy)student表，不按照系别创建索引，查询

13、某个系所有学生，所需要的时间。set statistics io on set statistics time on go select * from dbo.Student where Sdept=CSgo set statistics io off set statistics time off(4) 对于student表，按照系别创建各种索引，查询某个(mu )系所有学生，所需要的时间。set statistics io on set statistics time on go create index Sdepton dbo.Student (Sdept)select * from d

14、bo.Student where Sdept=CSgo set statistics io off set statistics time off(5) 查询(chxn)sc表所需时间。set statistics io on set statistics time on go select * from dbo.SCgo set statistics io off set statistics time off(6) 将student和sc连接(linji)所需时间。set statistics io on set statistics time on go select * from db

15、o.Student,dbo.SC where Student.Sno=SC.Snogo set statistics io off set statistics time off(7) 将student和sc和course连接(linji)所需时间。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)查询(chxn)选修了“数据库”学生的学号姓名，分别用嵌套和连接的方法(fngf)，观察两种方法所用的时间。set statistics io on set statistics time on go select Sno,Sname from dbo.Student where Sno in(select sno from