數(shù)據(jù)查詢分析實(shí)驗(yàn).doc

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