SQL数据库查询优化.docx
《SQL数据库查询优化.docx》由会员分享，可在线阅读，更多相关《SQL数据库查询优化.docx（20页珍藏版）》请在冰点文库上搜索。
SQL数据库查询优化
一、实验目的
1.熟悉查询查询处理的过程；
2.掌握查询优化的概念，理解查询优化的必要性；
3.了解数据库的查询计划；
4.掌握查询代价的分析方法，并且能通过配置参数或者修改SQL语句来降低查询代价。
二、实验环境
SQLServer
三、实验学时
2学时
四、实验要求
1）求选修了00002号课程的学生姓名。
用SQL表达：
SELECTStudent.Sname
FROMStudent，SC
WHEREStudent.Sno=SC.SnoANDSC.Cno=‘00002’
2）三种实现方法：
Q1=πSname（σStudent.Sno=SC.Sno∧Sc.Cno='2'（Student×SC））
Q2=πSname（σSc.Cno='2'（Student
SC））
Q3=πSname（Student
σSc.Cno='2'（SC））
3）要求：
本实验旨在说明查询优化的必要性，只要求把法一Q1与法二Q2和法三Q3比较，从而说明查询优化的重要性
五、实验内容及步骤
（一）实验数据的准备
--1.创建数据库
createdatabasestu_optimization
ON
（NAME=stu_opti,
FILENAME='E:
\stu_opti\stu_opti.mdf',
SIZE=100,
MAXSIZE=500,
FILEGROWTH=10）
LOGON
（NAME='stu_opti_log',
FILENAME='E:
\stu_opti\stu_opti_log.ldf',
SIZE=50MB,
MAXSIZE=250MB,
FILEGROWTH=5MB）
GO
--2.创建学生表
createtables（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
go
droptables
go
--3.为学生表输入数据
--输入30000个计科教育学生
declare@numint
declare@nint
set@num=30000
set@n=1
while@n<=@num
begin
insertintos（sno,sdept）
select'151031'+right（'00000'+cast（@nasvarchar（5））,5）,'计科教育'
set@n=@n+1
end
select*froms
--4.设置学生性别
--
（1）设置15000个学生的性别为女性
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop15000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--随机设置个学生的性别为女性
updatedbo.s
setssex='女'
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--
（2）设置其他学生的性别为男性
updatedbo.s
setssex='男'
wheressexisnull
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
select*fromsorderbysno
--5.设置学生年龄
--
（1）为5000个学生设置其年龄为21岁
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这个学生的年龄为21岁
updatedbo.s
setsage=21
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
--
（2）为5000个学生设置其年龄为22岁
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这个学生的年龄为22岁
updatedbo.s
setsage=22
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
--（3）为5000个学生设置其年龄为23岁
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这个学生的年龄为23岁
updatedbo.s
setsage=23
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
--（4）为5000个学生设置其年龄为20岁
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这个学生的年龄为20岁
updatedbo.s
setsage=20
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
--（5）为5000个学生设置其年龄为19岁
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这个学生的年龄为19岁
updatedbo.s
setsage=19
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
--（6）为5000个学生设置其年龄为18岁
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这个学生的年龄为18岁
updatedbo.s
setsage=18
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
updatessetsage=21wheresageisnull
select*fromsorderbysno
--6.设置学生姓名
--
（1）为5000个学生设置其姓名为李
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这些学生的姓名为李
updatedbo.s
setsname='李'
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
--
（2）为5000个学生设置其姓名为王
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这些学生的姓名为王
updatedbo.s
setsname='王'
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
--（3）为5000个学生设置其姓名为王
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这些学生的姓名为陈
updatedbo.s
setsname='陈'
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
--（4）为5000个学生设置其姓名为刘
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这些学生的姓名为刘
updatedbo.s
setsname='刘'
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
--（5）为5000个学生设置其姓名为张
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这些学生的姓名为张
updatedbo.s
setsname='张'
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
--（6）为5000个学生设置其姓名为邱
--创建临时表，其结构与学生表的结构一致
CREATETABLE#TEMP_s（
snochar（11）,
snamechar（10）,
ssexchar
（2）,
sagetinyint,
sdeptchar（10））
--从学生表中随机产生行数据插入到临时表中
INSERTINTO#TEMP_s
SELECTtop5000sno,sname,ssex,sage,sdept
FROMdbo.s
ORDERBYNEWID（）
--设置这些学生的姓名为邱
updatedbo.s
setsname='邱'
fromdbo.sinnerjoin#TEMP_s
ondbo.s.sno=#TEMP_s.sno
--删除临时表
TRUNCATETABLE#TEMP_s
DROPTABLE#TEMP_s
--7.创建课程表，录入课程
createtablec（
cnochar（5）,
cnamevarchar（20）,
cpnochar（5）,
ccredittinyint）
go
insertintodbo.c（cno,cname,cpno,ccredit）
values
--（'00001','计算机导论','',2）,
--（'00002','高级语言程序设计','',2）,
--（'00003','离算数学','',3）,
--（'00004','数据结构','00002',3）,
--（'00005','c#','00002',2）,
--（'00006','面向对象程序设计','00005',2）,
--（'00007','数据库原理','00004',3）,
--（'00008','操作系统','',3）,
--（'00009','计算机组成原理','',3）,
--（'00010','编译原理','',3）,
--（'00011','软件工程','',2）,
--（'00012','数字图像处理','',2）,
--（'00013','程序设计','',2）,
--（'00014','平面动画设计','',2）,
--（'00015','linux操作系统','00008',2）,
--（'00016','数据库新技术','00007',2）,
--（'00017','嵌入式技术','',2）,
--（'00018','算法设计与分析','00004',2）,
--（'00019','nosql','',2）,
（'00020','数据库实用技术','00007',2）
select*fromc
--8.创建学生成绩表，录入成绩
--
（1）创建学生成绩表sc
createtablesc（
snochar（11）notnull,
cnochar（5）notnull,
gradetinyint,
primarykey（sno,cno））
go
--
（2）录入学号和课程号
declare@snochar（11）
declares_cursorcursorlocalforselectsnofromdbo.s
opens_cursor
fetchs_cursorinto@sno
declare@s_rowint,@nint
set@s_row=30000
set@n=1
while@n<=@s_row
begin
insertdbo.sc（sno,cno）
select@sno,cnofromdbo.c
fetchs_cursorinto@sno
set@n=@n+1
end
closes_cursor
deallocates_cursor
--（3）为学生选课表生成成绩
--1）创建学生选课表sc12
createtablesc12（
snochar（11）,
cnochar（5）,
gradeint,
primarykey（sno,cno））
go
--2）将学生选课表sc中的选课信息插入到学生选课表sc12，并随机生成相应的选课成绩
declare@snochar（11）,@cnochar（5）
declare@grdaetinyint
declaresc_curcursorforselectsno,cnofromdbo.sc
opensc_cur
fetchnextfromsc_curinto@sno,@cno
set@grdae=cast（floor（rand（）*50）asint）+50
declare@nint,@sc_rowint
set@n=1
set@sc_row=600000
while@n<=@sc_row
begin
insertintodbo.sc12（sno,cno,grade）
values（@sno,@cno,@grdae）
fetchnextfromsc_curinto@sno,@cno
set@grdae=cast（floor（rand（）*50）asint）+50
set@n=@n+1
end
closesc_cur
deallocatesc_cur
说明：
上面是查询优化数据库的模板程序。
由于笛卡尔积计算量非常之大，上面的数据由普通的计算机作为数据服务器难以实现。
（二）实验实现：
本实验在sqlserver2000环境下实现
1.数据库：
stu_optimization
1）学生表：
s22（5000个元祖）
2）课程表：
c22（20个元祖）
3）学生选课表：
sc22（100000元祖）
2.三种方法的时间代价
1）法一Q1中只进行笛卡尔积的时间：
2）法二、法三的时间
这里只是从时间代价上说明查询优化的必要性。
通过简单的查询语句难以实现法二和法三在DBMS中的实现细节。
升级会员 