數(shù)據(jù)查詢分析實驗.doc

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)化方式的