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

实验报告
学院（系）名称：计算机与通信工程学院
姓名
Touchkiss
学号
20125577
专业
计算机科学与技术
班级
2班
实验项目
查询优化
课程名称
数据库系统
课程代码
0668026
实验时间
2014/12/5
实验地点
7#215
批改意见
成绩

教师签字：

实验环境（软、硬件环境）
一、 实验目的
1. 了解数据库查询优化方法和查询计划的概念
2. 学会分析查询的代价，并通过建立索引或者修改SQL语句来降低查询代价
二、 实验内容与要求
基于教材中的student、course、SC表，通过存储过程（要求在报告中写出存储过程），插入多条元组（1万条以上），考虑不同的SQL操作，分析比较执行代价。
1． 单表查询
（1） 直接查询：查询student表中年龄在20岁以上的学生记录
（2） 建立索引后，再查询：查询student表中年龄在20岁以上的学生记录
（3） 表中元组数量少，查询结果所占比例大：查询student表中年龄在20岁以上的学生记录
（4） 表元组数量多，查询结果所占比例小：查询student表中年龄在20岁以上的学生记录
分析以上四种SQL查询的执行效率，并做总结：

2. 多表查询：基于student、course、SC表，按照以下要求，实现多表查询，并分析比较执行效率。（自行设计查询语句，要求针对同一查询内容，使用以下四种方式）
（1） 多表连接查询
（2） 嵌套查询
（3） 建立索引
（4） 使用游标
分析比较：
3．综合练习
(1) 对于student表，不按照姓名创建索引，查询某个姓名，所需要的时间。
(2) 对于student表，按照姓名创建索引，查询某个姓名，所需要的时间。
(3) 对于student表，不按照系别创建索引，查询某个系所有学生，所需要的时间。
(4) 对于student表，按照系别创建各种索引，查询某个系所有学生，所需要的时间。
(5) 查询sc表所需时间。
(6) 将student和sc连接所需时间。
(7) 将student和sc和course连接所需时间。
(8)查询选修了“数据库”学生的学号姓名，分别用嵌套和连接的方法，观察两种方法所用的时间。
三、 本次实验总结：
基于教材中的student、course、SC表，通过存储过程（要求在报告中写出存储过程），插入多条元组（1万条以上），考虑不同的SQL操作，分析比较执行代价。
存储过程：
create procedure insert_stu
@sexflag nvarchar='男', @age int=0, @dept char(8)='MA',@i int=1
as
while @i<600000
begin
set @age = 20
if @i%4 = 0
set @age=17

if @i%4 = 1
set @age=18

if @i%4 = 2
set @age = 19

if @i%2 = 0
set @sexflag = '男'
else
set @sexflag = '女'

set @dept = 'MA'

if @i%3 = 0
set @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+1
end

create procedure insert_cou
@i int = 1
as
while @i<1000
begin
insert into dbo.Course (Cno,Cname,Ccredit) values(@i, 'Course'+ cast(@i as char), @i%5)
set @i=@i+1
end
USE [lab]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[insert_s_c]
@i int =1, @j int =1
as

while @i<6000
begin
set @j=1
while @j<1000
begin
insert into dbo.SC values(@i,@j,(@i*@j)%100)
set @j=@j+1
end
set @i=@i+1
end
1.单表查询
(1)直接查询：查询student表中年龄在20岁以上的学生记录
dbcc dropcleanbuffers
dbcc freeproccache

set statistics io on
set statistics time on
go
select * from dbo.Student where Sage >= 20
go
set statistics io off
set statistics time off

(2)建立索引后，再查询：查询student表中年龄在20岁以上的学生记录

(3)表中元组数量少，查询结果所占比例大：查询student表中年龄在19岁以下的学生记录

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.SC
where Student.Sno=SC.Sno and SC.Cno = Course.Cno and Cname='Course1' and Grade>90
go
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 Cname = '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.SC
where Student.Sno=SC.Sno and SC.Cno = Course.Cno and Cname='Courserse1' and Grade>90
go
set statistics io off
set statistics time off

（4）使用游标
set statistics io on
set statistics time on
go
declare my_cursor cursor scroll dynamic
for
select Sname from dbo.Student,dbo.Course,dbo.SC
where Student.Sno=SC.Sno and SC.Cno = Course.Cno and Cname='Course1' and Grade>90
open my_cursor
declare @name sysname
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
print @name
fetch next from my_cursor into @name
end
close my_cursor
deallocate my_cursor
go
set statistics io off
set statistics time off

分析比较：
3．综合练习
(1) 对于student表，不按照姓名创建索引，查询某个姓名，所需要的时间。
set statistics io on
set statistics 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 Sname
on Student(Sname)
select * from dbo.Student where Sname='Name1999'
go
set 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 statistics time on
go
create index Sdept
on 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.SC
go
set statistics io off
set statistics time off

(6) 将student和sc连接所需时间。
set statistics io on
set statistics time on
go
select * from dbo.Student,dbo.SC where Student.Sno=SC.Sno
go
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.Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno
go
set statistics io off
set statistics time off

(8)查询选修了“数据库”学生的学号姓名，分别用嵌套和连接的方法，观察两种方法所用的时间。
set statistics io on
set statistics time on
go
select Sno,Sname from dbo.Student where Sno in
(select sno from dbo.SC where Cno in
(select cno from dbo.Course where Cname = 'Course999'))
go
set statistics io off
set statistics time off

本次实验总结：