數(shù)據(jù)查詢分析實驗.doc_第1頁
數(shù)據(jù)查詢分析實驗.doc_第2頁
數(shù)據(jù)查詢分析實驗.doc_第3頁
數(shù)據(jù)查詢分析實驗.doc_第4頁
數(shù)據(jù)查詢分析實驗.doc_第5頁
已閱讀5頁,還剩11頁未讀 繼續(xù)免費閱讀

下載本文檔

版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進行舉報或認(rèn)領(lǐng)

文檔簡介

1. 實驗六 數(shù)據(jù)查詢分析實驗1.1 實驗?zāi)康?. 通過對不同情況下查詢語句的執(zhí)行分析,鞏固和加深對查詢和查詢優(yōu)化相關(guān)理論知識的理解,提高優(yōu)化數(shù)據(jù)庫系統(tǒng)的實踐能力;2. 熟悉了解Mysql中查詢分析器的使用,并進一步提高編寫復(fù)雜查詢的SQL 程序的能力。1.2 實驗內(nèi)容1.2.1 索引對查詢的影響(1) 對結(jié)果集只有一個元組的查詢分三種情況進行執(zhí)行(必如查詢一個具體學(xué)生的信息):不建立索引,(學(xué)號上)建立非聚集索引,(學(xué)號上)建立聚集索引。用查詢分析器的執(zhí)行步驟和結(jié)果對執(zhí)行進行分析比較。(2) 對結(jié)果集中有多個元組的查詢(例如查看某門成績的成績表)分類似(1)的三種情況進行執(zhí)行比較。(3) 對查詢條件為一個連續(xù)的范圍的查詢(例如查看學(xué)號在某個范圍內(nèi)的學(xué)生的選課情況)分類似(1)的三種情況進行執(zhí)行比較,注意系統(tǒng)處理的選擇。(4) 索引代價。在有索引和無索引的情況下插入數(shù)據(jù)(例如在選課情況表SC 上插入數(shù)據(jù)),比較插入的執(zhí)行效率。1.2.2 對相同查詢功能不同查詢語句的執(zhí)行比較分析(1) select avg(grade) from sc group by cno having cno =100;select avg(grade) from sc where cno = 100;有和沒有g(shù)roup by,比較其查詢效率,并分析。(2) select sno,sname from student s1 where sno =(select max(sno) from student s2 where s1.dept = s2.dept );另一個:create table student1 as(select max(sno) as maxsno ,deptfrom student group by dept);select sno, sname from student,student1where student.sno = student1.maxsno and student1.dept=student.dept;drop table student1;重寫后的查詢一定比原始查詢更優(yōu)嗎?通過執(zhí)行分析結(jié)果。(3) 對下面兩個查詢進行比較select sno,sname from student where dept != 電信 and sno all(select sno from student where dept = 電信 );另:select sname ,sno from student where dept != 電信 and sno ( select max(sno) from student where dept = 電信 );1.2.3 查詢優(yōu)化除了建立適當(dāng)索引,對SQL 語句重寫外,還有其他手段來進行查詢調(diào)優(yōu),例如調(diào)整緩沖區(qū)大小,事先建立視圖等。設(shè)計實現(xiàn)下列查詢,使之運行效率最高。寫出你的查詢形式,以及調(diào)優(yōu)過程;并說明最優(yōu)情況下的運行時間。(1)查找選修了每一門課的學(xué)生。(2)查找至少選修了課程數(shù)據(jù)庫原理和操作系統(tǒng)的學(xué)生的學(xué)號。1.3 實驗環(huán)境Window8操作系統(tǒng)Mysql 8.0版本數(shù)據(jù)庫Mysql workbench 8.0可視化工具Mysql命令行編輯器1.4 實驗步驟及結(jié)果分析索引對查詢的影響1.4.1 單元組查詢對結(jié)果集只有一個元組的查詢分三種情況進行執(zhí)行(必如查詢一個具體學(xué)生的信息):不建立索引,(學(xué)號上)建立非聚集索引,(學(xué)號上)建立聚集索引。用查詢分析器的執(zhí)行步驟和結(jié)果對執(zhí)行進行分析比較。1. 沒有建立索引的情況(查詢sno=30203的學(xué)生)2. 建立非聚集索引的情況(查詢sno=30203的學(xué)生)3. 建立聚集索引的情況(查詢sno=30203的學(xué)生)4. 查詢?nèi)N情況下的時間耗用但是發(fā)現(xiàn),profiles沒有記錄信息,在網(wǎng)上查閱后進行了profiling權(quán)限開啟查詢,發(fā)現(xiàn)profiling未開啟。 我們開啟之后再進行時間查詢:最終成功 5. 效率比較查詢方法時間(ms)比較不建立索引0.3365較短非聚集索引0.4315最長聚集索引0.32125最短由表的數(shù)據(jù)可看出,在查詢某個學(xué)生信息得過程中,聚集索引效率最高,無索引次之,非聚集索引最差。這樣的結(jié)果產(chǎn)生的原因可能有:1. 電腦的不穩(wěn)定性,2. 數(shù)據(jù)庫大小不合適,3. 非聚集索引查詢過程較復(fù)雜1.4.2 多元組查詢對結(jié)果集中有多個元組的查詢(例如查看某門成績的成績表)分類似1.4.1的三種情況進行執(zhí)行比較。1. 無索引查詢(查詢課程號為c01的情況)2. 非聚集索引查詢(查詢課程號為c01的情況)3. 聚集索引查詢(查詢課程號為c01的情況)4. 耗用時間展示5. 結(jié)果統(tǒng)計及比較查詢方法時間(ms)比較不建立索引0.382較短非聚集索引0.397最長聚集索引0.35575最短1.4.3 范圍查詢對查詢條件為一個連續(xù)的范圍的查詢(例如查看學(xué)號在某個范圍內(nèi)的學(xué)生的選課情況)分類似1.4.1的三種情況進行執(zhí)行比較,注意系統(tǒng)處理的選擇。1. 無索引查詢(查詢學(xué)號在30201到30203之間的學(xué)生)2. 非聚集索引 (查詢學(xué)號在30201到30203之間的學(xué)生)3. 聚集索引(查詢學(xué)號在30201到30203之間的學(xué)生)4. 耗用時間展示5. 效率統(tǒng)計比較查詢方法時間(ms)比較不建立索引0.4995最長非聚集索引0.46125較短聚集索引0.385最短由上述結(jié)果來看,在范圍查詢中,無索引查詢效率最低;索引查詢效率相對較高,這種比較在數(shù)據(jù)量大的時候更加明顯,其中聚集索引較非聚集索引效率率更高。因為聚集索引將sno直接排序查找,速度較快;而非聚集索引查找塊然后再進行細(xì)化數(shù)據(jù)查找,速度相對慢一些。1.4.4 索引代價索引代價。在有索引和無索引的情況下插入數(shù)據(jù)(例如在選課情況表SC 上插入數(shù)據(jù)),比較插入的執(zhí)行效率。(為保證每次插入的位置和數(shù)據(jù)相同,我們每次插入都要進行刪除重新插入,即控制變量保證一致)1. 無索引插入2. 非聚集索引3. 聚集索引4. 耗用時間展示5. 效率統(tǒng)計比較查詢方法時間(ms)比較不建立索引67.031最短非聚集索引119.127最長聚集索引95.419較短由數(shù)據(jù)來看,建立索引的導(dǎo)致查詢的效率提高,但同時也使得數(shù)據(jù)表的更新操作效率變低。因為對于索引來說,每次插入數(shù)據(jù)不僅要考慮數(shù)據(jù)插入的情況,還要考慮索引的變更。1.5 實驗步驟及結(jié)果分析對相同查詢功能不同查詢語句的執(zhí)行比較分析1.5.1 比較11. 命令功能:查找課程號cno=100的課程平均成績2. Mysql語句1:select avg(grade) from sc group by cno having cno =100;3. Mysql語句2:select avg(grade) from sc where cno = 100;4. 兩條語句執(zhí)行結(jié)果:5. 兩條語句用時展示6. 效率統(tǒng)計比較查詢方法時間(ms)比較用group by0.74825長不用group by0.57475短由于group需要將我們不需要的內(nèi)容進行分組,因此增加了是叫消耗,效率相對較低。但是對于大規(guī)模的分類處理來說,group by更加直觀方便。1.5.2 比較21. 命令功能:查詢各個dept中學(xué)號sno最大的學(xué)生的學(xué)號姓名2. Mysql語句1:(直接比較查詢)select sno,sname from student s1 where sno = (select max(sno) from student s2 where s1.dept = s2.dept );3. Mysql語句2:(先創(chuàng)建表格,再查詢)create table student1 as(select max(sno) as maxsno ,deptfrom student group by dept);select sno, sname from student,student1where student.sno = student1.maxsno and student1.dept=student.dept;4. 兩條語句耗時展示:5. 效率統(tǒng)計比較查詢方法時間(ms)比較不創(chuàng)表查詢2.6405較短創(chuàng)表耗時432.95925長創(chuàng)表查詢0.8195短由數(shù)據(jù)對比可得:創(chuàng)建表格查詢相比不創(chuàng)表查詢,速度有很大的提高,但是在創(chuàng)建表的過程中需要消耗大量的時間。因此,對以后的查詢處理要進行合適的選擇:對于查詢次數(shù)較少的情況,我們可以直接查詢,因為創(chuàng)表消耗太多。對于查詢次數(shù)較多,需要較長時間使用的情況,我們可以創(chuàng)建表,在以后都會使用。1.5.3 比較31. 命令功能:查詢所有非電信學(xué)院中,學(xué)號比所有電信學(xué)院學(xué)號都大的學(xué)生學(xué)號姓名。2. Mysql語句1:(使用all)select sno,sname from student where dept != 電信 and sno all(select sno from student where dept = 電信 );3. Mysql語句2:(使用max)select sname ,sno from student where dept != 電信 and sno ( select max(sno) from student where dept = 電信 );4. 兩條語句耗時展示:5. 效率統(tǒng)計比較查詢方法時間(ms)比較外部使用all0.42875短內(nèi)部使用max0.696長由數(shù)據(jù)可得:在此次比較中,在外部使用的all耗時較少,因為我們在內(nèi)部查詢出所有sno之后只需要在外部進行all所有的比較即可。而是用max,需要首先在內(nèi)部自行比較產(chǎn)生最大者,在讓最大者和外部比較,增多了比較次數(shù)1.6 實驗步驟及結(jié)果分析查詢優(yōu)化1.6.1 查找選修了每一門課的學(xué)生1. 直接用count查詢2. 嵌套查詢3. 創(chuàng)建表查詢4. 耗用時間展示5. 統(tǒng)計比較查詢方法時間(ms)比較count查詢0.8825較短嵌套查詢1.77525長表查詢0.4525短1.6.2 查找至少選修了課程數(shù)據(jù)庫原理和操作系統(tǒng)的學(xué)生的學(xué)號1. 思路1:使用嵌套查詢,在選擇數(shù)據(jù)庫原理的基礎(chǔ)上搜索選了操作系統(tǒng)的學(xué)生。2. 思路2:先將所有選了數(shù)據(jù)庫原理或操作系統(tǒng)的人選出來,然后挑選出里面選課數(shù)為2的學(xué)生。這樣就保證兩門課都選了。3. 思路3:使用視圖方法完成思路14. 思路4:使用視圖方法完成思路25. 耗用時間展示不使用視圖:使用視圖:6. 效率總結(jié)比較查詢方法時間(ms)比較思路11.06短思路20.5525最短思路3創(chuàng)建視圖118.23725思路3查詢11.6825最長思路4創(chuàng)建視圖355.128思路4查詢1.224425較長在不考慮電腦機器效率誤差的情況下,創(chuàng)建視圖使得查詢效率相對較慢,不如直接查詢快速。而采用嵌套一層一層查詢比直接group查詢更慢一些,因為嵌套會導(dǎo)致循環(huán)的增加。1.7 實驗總結(jié)1.7.1 實驗遇到的問題1. 本次實驗遇到的第一個問題就是如何建立索引的問題,在之前實驗的基礎(chǔ)上開始做,發(fā)現(xiàn)設(shè)置了主鍵之后,數(shù)據(jù)庫會默認(rèn)以主鍵為搜索碼建立聚集索引,而且無法刪除索引也不好更改,最后只能刪除主鍵來作為無索引、創(chuàng)建非聚集索引、添加主鍵 2. 實驗遇到的第二個問題是在進行第二步實驗的時候,原表格中沒有age這一屬性,因此實驗中我將ege改為了sno屬性進行查詢3. 采用控制變量法驗證各種優(yōu)化方式的

溫馨提示

  • 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫網(wǎng)僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。

最新文檔

評論

0/150

提交評論