天津理工大學(xué) 數(shù)據(jù)庫實(shí)驗(yàn)四--查詢優(yōu)化(共13頁)

1、 實(shí)驗(yàn)報(bào)告學(xué)院(xuyun)（系）名稱：計(jì)算機(jī)與通信(tng xn)工程學(xué)院姓名Touchkiss學(xué)號(hào)20125577專業(yè)計(jì)算機(jī)科學(xué)與技術(shù)班級2班實(shí)驗(yàn)項(xiàng)目查詢優(yōu)化課程名稱數(shù)據(jù)庫系統(tǒng)課程代碼0668026實(shí)驗(yàn)時(shí)間2014/12/5實(shí)驗(yàn)地點(diǎn)7#215批改意見成績教師簽字：實(shí)驗(yàn)環(huán)境（軟、硬件環(huán)境）實(shí)驗(yàn)?zāi)康牧私鈹?shù)據(jù)庫查詢優(yōu)化方法和查詢計(jì)劃的概念學(xué)會(huì)分析查詢的代價(jià)，并通過建立索引或者修改SQL語句來降低查詢代價(jià)實(shí)驗(yàn)內(nèi)容與要求基于教材中的student、course、SC表，通過存儲(chǔ)過程（要求在報(bào)告中寫出存儲(chǔ)過程），插入多條元組（1萬條以上），考慮不同的SQL操作，分析比較執(zhí)行代價(jià)。單表查詢直接查詢：查詢

2、student表中年齡在20歲以上的學(xué)生記錄建立索引后，再查詢：查詢student表中年齡在20歲以上的學(xué)生記錄表中元組數(shù)量少，查詢結(jié)果所占比例大：查詢student表中年齡在20歲以上的學(xué)生記錄表元組數(shù)量多，查詢結(jié)果所占比例?。翰樵僺tudent表中年齡在20歲以上的學(xué)生記錄分析以上四種SQL查詢的執(zhí)行效率，并做總結(jié)：多表查詢：基于student、course、SC表，按照以下要求，實(shí)現(xiàn)多表查詢，并分析比較執(zhí)行效率。（自行設(shè)計(jì)查詢語句，要求針對同一查詢內(nèi)容，使用以下四種方式）多表連接查詢嵌套查詢建立索引使用游標(biāo)分析比較：3綜合練習(xí)(1) 對于student表，不按照姓名創(chuàng)建索引，查詢某個(gè)姓名

3、，所需要的時(shí)間。(2) 對于student表，按照姓名創(chuàng)建索引，查詢某個(gè)姓名，所需要的時(shí)間。(3) 對于student表，不按照系別創(chuàng)建索引，查詢某個(gè)系所有學(xué)生，所需要的時(shí)間。(4) 對于student表，按照系別創(chuàng)建各種索引，查詢某個(gè)系所有學(xué)生，所需要的時(shí)間。(5) 查詢sc表所需時(shí)間。(6) 將student和sc連接所需時(shí)間。(7) 將student和sc和course連接所需時(shí)間。(8)查詢選修了“數(shù)據(jù)庫”學(xué)生的學(xué)號(hào)姓名，分別用嵌套和連接的方法，觀察兩種方法所用的時(shí)間。本次實(shí)驗(yàn)總結(jié)： 基于教材中的student、course、SC表，通過(tnggu)存儲(chǔ)過程（要求(yoqi)在報(bào)告中

4、寫出存儲(chǔ)過程），插入多條元組（1萬條以上(yshng)），考慮不同的SQL操作，分析比較執(zhí)行代價(jià)。存儲(chǔ)過程：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)的學(xué)生記錄(3)表中元組數(shù)量少，查詢結(jié)果(ji gu)所占比例大：查詢student表中年齡在19歲以下(yxi)的學(xué)生(xu sheng)記錄STU表中共1000條數(shù)據(jù)(4)表元組數(shù)量多，查詢結(jié)果所占比例

7、?。翰樵僺tudent表中年齡在20歲以上的學(xué)生記錄Student表中有600000條數(shù)據(jù)分析以上四種SQL查詢的執(zhí)行效率，并做總結(jié)：2.多表查詢：基于student、course、SC表，按照以下要求，實(shí)現(xiàn)多表查詢，并分析比較執(zhí)行效率。（自行設(shè)計(jì)查詢語句，要求針對同一查詢內(nèi)容，使用以下四種方式）（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)游標(biāo)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)練習(xí)(1) 對于(duy)student表，不按照姓名創(chuàng)建索引，查詢某個(gè)姓名，所需要的時(shí)間。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表，按照姓名創(chuàng)建索引，查詢某個(gè)姓名，所需要的時(shí)間。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表，不按照系別創(chuàng)建索引，查詢

13、某個(gè)系所有學(xué)生，所需要的時(shí)間。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表，按照系別創(chuàng)建各種索引，查詢某個(gè)(mu )系所有學(xué)生，所需要的時(shí)間。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表所需時(shí)間。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)所需時(shí)間。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)所需時(shí)間。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)選修了“數(shù)據(jù)庫”學(xué)生的學(xué)號(hào)姓名，分別用嵌套和連接的方法(fngf)，觀察兩種方法所用的時(shí)間。set statistics io on set statistics time on go select Sno,Sname from dbo.Student where Sno in(select sno from