SQL数据库查询优化.docx
- 文档编号:30757932
- 上传时间:2023-08-20
- 格式:DOCX
- 页数:20
- 大小:187.07KB
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中的实现细节。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 数据库 查询 优化