




已閱讀5頁(yè),還剩11頁(yè)未讀, 繼續(xù)免費(fèi)閱讀
版權(quán)說(shuō)明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)
文檔簡(jiǎn)介
1. 實(shí)驗(yàn)六 數(shù)據(jù)查詢分析實(shí)驗(yàn)1.1 實(shí)驗(yàn)?zāi)康?. 通過(guò)對(duì)不同情況下查詢語(yǔ)句的執(zhí)行分析,鞏固和加深對(duì)查詢和查詢優(yōu)化相關(guān)理論知識(shí)的理解,提高優(yōu)化數(shù)據(jù)庫(kù)系統(tǒng)的實(shí)踐能力;2. 熟悉了解Mysql中查詢分析器的使用,并進(jìn)一步提高編寫(xiě)復(fù)雜查詢的SQL 程序的能力。1.2 實(shí)驗(yàn)內(nèi)容1.2.1 索引對(duì)查詢的影響(1) 對(duì)結(jié)果集只有一個(gè)元組的查詢分三種情況進(jìn)行執(zhí)行(必如查詢一個(gè)具體學(xué)生的信息):不建立索引,(學(xué)號(hào)上)建立非聚集索引,(學(xué)號(hào)上)建立聚集索引。用查詢分析器的執(zhí)行步驟和結(jié)果對(duì)執(zhí)行進(jìn)行分析比較。(2) 對(duì)結(jié)果集中有多個(gè)元組的查詢(例如查看某門成績(jī)的成績(jī)表)分類似(1)的三種情況進(jìn)行執(zhí)行比較。(3) 對(duì)查詢條件為一個(gè)連續(xù)的范圍的查詢(例如查看學(xué)號(hào)在某個(gè)范圍內(nèi)的學(xué)生的選課情況)分類似(1)的三種情況進(jìn)行執(zhí)行比較,注意系統(tǒng)處理的選擇。(4) 索引代價(jià)。在有索引和無(wú)索引的情況下插入數(shù)據(jù)(例如在選課情況表SC 上插入數(shù)據(jù)),比較插入的執(zhí)行效率。1.2.2 對(duì)相同查詢功能不同查詢語(yǔ)句的執(zhí)行比較分析(1) select avg(grade) from sc group by cno having cno =100;select avg(grade) from sc where cno = 100;有和沒(méi)有g(shù)roup by,比較其查詢效率,并分析。(2) select sno,sname from student s1 where sno =(select max(sno) from student s2 where s1.dept = s2.dept );另一個(gè):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;重寫(xiě)后的查詢一定比原始查詢更優(yōu)嗎?通過(guò)執(zhí)行分析結(jié)果。(3) 對(duì)下面兩個(gè)查詢進(jìn)行比較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)索引,對(duì)SQL 語(yǔ)句重寫(xiě)外,還有其他手段來(lái)進(jìn)行查詢調(diào)優(yōu),例如調(diào)整緩沖區(qū)大小,事先建立視圖等。設(shè)計(jì)實(shí)現(xiàn)下列查詢,使之運(yùn)行效率最高。寫(xiě)出你的查詢形式,以及調(diào)優(yōu)過(guò)程;并說(shuō)明最優(yōu)情況下的運(yùn)行時(shí)間。(1)查找選修了每一門課的學(xué)生。(2)查找至少選修了課程數(shù)據(jù)庫(kù)原理和操作系統(tǒng)的學(xué)生的學(xué)號(hào)。1.3 實(shí)驗(yàn)環(huán)境Window8操作系統(tǒng)Mysql 8.0版本數(shù)據(jù)庫(kù)Mysql workbench 8.0可視化工具M(jìn)ysql命令行編輯器1.4 實(shí)驗(yàn)步驟及結(jié)果分析索引對(duì)查詢的影響1.4.1 單元組查詢對(duì)結(jié)果集只有一個(gè)元組的查詢分三種情況進(jìn)行執(zhí)行(必如查詢一個(gè)具體學(xué)生的信息):不建立索引,(學(xué)號(hào)上)建立非聚集索引,(學(xué)號(hào)上)建立聚集索引。用查詢分析器的執(zhí)行步驟和結(jié)果對(duì)執(zhí)行進(jìn)行分析比較。1. 沒(méi)有建立索引的情況(查詢sno=30203的學(xué)生)2. 建立非聚集索引的情況(查詢sno=30203的學(xué)生)3. 建立聚集索引的情況(查詢sno=30203的學(xué)生)4. 查詢?nèi)N情況下的時(shí)間耗用但是發(fā)現(xiàn),profiles沒(méi)有記錄信息,在網(wǎng)上查閱后進(jìn)行了profiling權(quán)限開(kāi)啟查詢,發(fā)現(xiàn)profiling未開(kāi)啟。 我們開(kāi)啟之后再進(jìn)行時(shí)間查詢:最終成功 5. 效率比較查詢方法時(shí)間(ms)比較不建立索引0.3365較短非聚集索引0.4315最長(zhǎng)聚集索引0.32125最短由表的數(shù)據(jù)可看出,在查詢某個(gè)學(xué)生信息得過(guò)程中,聚集索引效率最高,無(wú)索引次之,非聚集索引最差。這樣的結(jié)果產(chǎn)生的原因可能有:1. 電腦的不穩(wěn)定性,2. 數(shù)據(jù)庫(kù)大小不合適,3. 非聚集索引查詢過(guò)程較復(fù)雜1.4.2 多元組查詢對(duì)結(jié)果集中有多個(gè)元組的查詢(例如查看某門成績(jī)的成績(jī)表)分類似1.4.1的三種情況進(jìn)行執(zhí)行比較。1. 無(wú)索引查詢(查詢課程號(hào)為c01的情況)2. 非聚集索引查詢(查詢課程號(hào)為c01的情況)3. 聚集索引查詢(查詢課程號(hào)為c01的情況)4. 耗用時(shí)間展示5. 結(jié)果統(tǒng)計(jì)及比較查詢方法時(shí)間(ms)比較不建立索引0.382較短非聚集索引0.397最長(zhǎng)聚集索引0.35575最短1.4.3 范圍查詢對(duì)查詢條件為一個(gè)連續(xù)的范圍的查詢(例如查看學(xué)號(hào)在某個(gè)范圍內(nèi)的學(xué)生的選課情況)分類似1.4.1的三種情況進(jìn)行執(zhí)行比較,注意系統(tǒng)處理的選擇。1. 無(wú)索引查詢(查詢學(xué)號(hào)在30201到30203之間的學(xué)生)2. 非聚集索引 (查詢學(xué)號(hào)在30201到30203之間的學(xué)生)3. 聚集索引(查詢學(xué)號(hào)在30201到30203之間的學(xué)生)4. 耗用時(shí)間展示5. 效率統(tǒng)計(jì)比較查詢方法時(shí)間(ms)比較不建立索引0.4995最長(zhǎng)非聚集索引0.46125較短聚集索引0.385最短由上述結(jié)果來(lái)看,在范圍查詢中,無(wú)索引查詢效率最低;索引查詢效率相對(duì)較高,這種比較在數(shù)據(jù)量大的時(shí)候更加明顯,其中聚集索引較非聚集索引效率率更高。因?yàn)榫奂饕龑no直接排序查找,速度較快;而非聚集索引查找塊然后再進(jìn)行細(xì)化數(shù)據(jù)查找,速度相對(duì)慢一些。1.4.4 索引代價(jià)索引代價(jià)。在有索引和無(wú)索引的情況下插入數(shù)據(jù)(例如在選課情況表SC 上插入數(shù)據(jù)),比較插入的執(zhí)行效率。(為保證每次插入的位置和數(shù)據(jù)相同,我們每次插入都要進(jìn)行刪除重新插入,即控制變量保證一致)1. 無(wú)索引插入2. 非聚集索引3. 聚集索引4. 耗用時(shí)間展示5. 效率統(tǒng)計(jì)比較查詢方法時(shí)間(ms)比較不建立索引67.031最短非聚集索引119.127最長(zhǎng)聚集索引95.419較短由數(shù)據(jù)來(lái)看,建立索引的導(dǎo)致查詢的效率提高,但同時(shí)也使得數(shù)據(jù)表的更新操作效率變低。因?yàn)閷?duì)于索引來(lái)說(shuō),每次插入數(shù)據(jù)不僅要考慮數(shù)據(jù)插入的情況,還要考慮索引的變更。1.5 實(shí)驗(yàn)步驟及結(jié)果分析對(duì)相同查詢功能不同查詢語(yǔ)句的執(zhí)行比較分析1.5.1 比較11. 命令功能:查找課程號(hào)cno=100的課程平均成績(jī)2. Mysql語(yǔ)句1:select avg(grade) from sc group by cno having cno =100;3. Mysql語(yǔ)句2:select avg(grade) from sc where cno = 100;4. 兩條語(yǔ)句執(zhí)行結(jié)果:5. 兩條語(yǔ)句用時(shí)展示6. 效率統(tǒng)計(jì)比較查詢方法時(shí)間(ms)比較用group by0.74825長(zhǎng)不用group by0.57475短由于group需要將我們不需要的內(nèi)容進(jìn)行分組,因此增加了是叫消耗,效率相對(duì)較低。但是對(duì)于大規(guī)模的分類處理來(lái)說(shuō),group by更加直觀方便。1.5.2 比較21. 命令功能:查詢各個(gè)dept中學(xué)號(hào)sno最大的學(xué)生的學(xué)號(hào)姓名2. Mysql語(yǔ)句1:(直接比較查詢)select sno,sname from student s1 where sno = (select max(sno) from student s2 where s1.dept = s2.dept );3. Mysql語(yǔ)句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. 兩條語(yǔ)句耗時(shí)展示:5. 效率統(tǒng)計(jì)比較查詢方法時(shí)間(ms)比較不創(chuàng)表查詢2.6405較短創(chuàng)表耗時(shí)432.95925長(zhǎng)創(chuàng)表查詢0.8195短由數(shù)據(jù)對(duì)比可得:創(chuàng)建表格查詢相比不創(chuàng)表查詢,速度有很大的提高,但是在創(chuàng)建表的過(guò)程中需要消耗大量的時(shí)間。因此,對(duì)以后的查詢處理要進(jìn)行合適的選擇:對(duì)于查詢次數(shù)較少的情況,我們可以直接查詢,因?yàn)閯?chuàng)表消耗太多。對(duì)于查詢次數(shù)較多,需要較長(zhǎng)時(shí)間使用的情況,我們可以創(chuàng)建表,在以后都會(huì)使用。1.5.3 比較31. 命令功能:查詢所有非電信學(xué)院中,學(xué)號(hào)比所有電信學(xué)院學(xué)號(hào)都大的學(xué)生學(xué)號(hào)姓名。2. Mysql語(yǔ)句1:(使用all)select sno,sname from student where dept != 電信 and sno all(select sno from student where dept = 電信 );3. Mysql語(yǔ)句2:(使用max)select sname ,sno from student where dept != 電信 and sno ( select max(sno) from student where dept = 電信 );4. 兩條語(yǔ)句耗時(shí)展示:5. 效率統(tǒng)計(jì)比較查詢方法時(shí)間(ms)比較外部使用all0.42875短內(nèi)部使用max0.696長(zhǎng)由數(shù)據(jù)可得:在此次比較中,在外部使用的all耗時(shí)較少,因?yàn)槲覀冊(cè)趦?nèi)部查詢出所有sno之后只需要在外部進(jìn)行all所有的比較即可。而是用max,需要首先在內(nèi)部自行比較產(chǎn)生最大者,在讓最大者和外部比較,增多了比較次數(shù)1.6 實(shí)驗(yàn)步驟及結(jié)果分析查詢優(yōu)化1.6.1 查找選修了每一門課的學(xué)生1. 直接用count查詢2. 嵌套查詢3. 創(chuàng)建表查詢4. 耗用時(shí)間展示5. 統(tǒng)計(jì)比較查詢方法時(shí)間(ms)比較count查詢0.8825較短嵌套查詢1.77525長(zhǎng)表查詢0.4525短1.6.2 查找至少選修了課程數(shù)據(jù)庫(kù)原理和操作系統(tǒng)的學(xué)生的學(xué)號(hào)1. 思路1:使用嵌套查詢,在選擇數(shù)據(jù)庫(kù)原理的基礎(chǔ)上搜索選了操作系統(tǒng)的學(xué)生。2. 思路2:先將所有選了數(shù)據(jù)庫(kù)原理或操作系統(tǒng)的人選出來(lái),然后挑選出里面選課數(shù)為2的學(xué)生。這樣就保證兩門課都選了。3. 思路3:使用視圖方法完成思路14. 思路4:使用視圖方法完成思路25. 耗用時(shí)間展示不使用視圖:使用視圖:6. 效率總結(jié)比較查詢方法時(shí)間(ms)比較思路11.06短思路20.5525最短思路3創(chuàng)建視圖118.23725思路3查詢11.6825最長(zhǎng)思路4創(chuàng)建視圖355.128思路4查詢1.224425較長(zhǎng)在不考慮電腦機(jī)器效率誤差的情況下,創(chuàng)建視圖使得查詢效率相對(duì)較慢,不如直接查詢快速。而采用嵌套一層一層查詢比直接group查詢更慢一些,因?yàn)榍短讜?huì)導(dǎo)致循環(huán)的增加。1.7 實(shí)驗(yàn)總結(jié)1.7.1 實(shí)驗(yàn)遇到的問(wèn)題1. 本次實(shí)驗(yàn)遇到的第一個(gè)問(wèn)題就是如何建立索引的問(wèn)題,在之前實(shí)驗(yàn)的基礎(chǔ)上開(kāi)始做,發(fā)現(xiàn)設(shè)置了主鍵之后,數(shù)據(jù)庫(kù)會(huì)默認(rèn)以主鍵為搜索碼建立聚集索引,而且無(wú)法刪除索引也不好更改,最后只能刪除主鍵來(lái)作為無(wú)索引、創(chuàng)建非聚集索引、添加主鍵 2. 實(shí)驗(yàn)遇到的第二個(gè)問(wèn)題是在進(jìn)行第二步實(shí)驗(yàn)的時(shí)候,原表格中沒(méi)有age這一屬性,因此實(shí)驗(yàn)中我將ege改為了sno屬性進(jìn)行查詢3. 采用控制變量法驗(yàn)證各種優(yōu)化方式的
溫馨提示
- 1. 本站所有資源如無(wú)特殊說(shuō)明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。
最新文檔
- 單位車輛分級(jí)管理制度
- 工廠停工現(xiàn)場(chǎng)管理制度
- 醫(yī)院病床衛(wèi)生管理制度
- 醫(yī)院窗口輔料管理制度
- 醫(yī)療器械文件管理制度
- 八一鋼鐵考勤管理制度
- 曹妃甸職業(yè)技術(shù)學(xué)院《好萊塢電影研究起》2023-2024學(xué)年第二學(xué)期期末試卷
- 渤海石油職業(yè)學(xué)院《醫(yī)學(xué)影像診斷學(xué)實(shí)驗(yàn)》2023-2024學(xué)年第二學(xué)期期末試卷
- 亳州學(xué)院《統(tǒng)計(jì)建模與數(shù)據(jù)分析》2023-2024學(xué)年第二學(xué)期期末試卷
- 北京中醫(yī)藥大學(xué)東方學(xué)院《國(guó)際市場(chǎng)營(yíng)銷W》2023-2024學(xué)年第二學(xué)期期末試卷
- DB36_T 420-2019 江西省工業(yè)企業(yè)主要產(chǎn)品用水定額(高清無(wú)水印-可復(fù)制)
- 車間精益改善總結(jié)報(bào)告課件(PPT 19頁(yè))
- 中小學(xué)教育懲戒規(guī)則(試行)全文解讀ppt課件
- TCECS 850-2021 住宅廚房空氣污染控制通風(fēng)設(shè)計(jì)標(biāo)準(zhǔn)
- 《冬病夏治工作指南》
- 布魯克納操作手冊(cè)
- 印度尼西亞煤炭購(gòu)銷合同
- GB∕T 25119-2021 軌道交通 機(jī)車車輛電子裝置
- 2022年國(guó)網(wǎng)輸變電工程質(zhì)量通病防治工作要求及技術(shù)措施[1]
- 三年級(jí)美術(shù)下冊(cè)16奇石教學(xué)設(shè)計(jì)1浙美版
- 支氣管分段亞段及及支氣管鏡檢查
評(píng)論
0/150
提交評(píng)論