北郵數(shù)據(jù)庫(kù)實(shí)驗(yàn)六數(shù)據(jù)查詢分析實(shí)驗(yàn)精要

1、實(shí)驗(yàn)六 數(shù)據(jù)查詢分析實(shí)驗(yàn)1、實(shí)驗(yàn)?zāi)康耐ㄟ^(guò)對(duì)不同情況下查詢語(yǔ)句的執(zhí)行分析，穩(wěn)固和加深對(duì)查詢和查詢優(yōu)化相關(guān)理論知識(shí)的理解，提高優(yōu)化數(shù)據(jù)庫(kù)系統(tǒng)的實(shí)踐能力，熟悉了解kingbase中查詢分析器的使用，并進(jìn)一步提高編寫復(fù)雜查詢的SQL 程序的能力。2、實(shí)驗(yàn)環(huán)境操作系統(tǒng)：MicrosoftWindows7旗艦版32位。數(shù)據(jù)庫(kù)版本：MySQL6.23、實(shí)驗(yàn)內(nèi)容3.1 索引對(duì)查詢的影響對(duì)結(jié)果集只有一個(gè)元組的查詢分三種情況進(jìn)行執(zhí)行比方查詢一個(gè)具體學(xué)生的信息：不建立索引學(xué)號(hào)上建立非聚集索引學(xué)號(hào)上建立聚集索引用查詢分析器的執(zhí)行步驟和結(jié)果對(duì)執(zhí)行進(jìn)行分析比擬。對(duì)結(jié)果集中有多個(gè)元組的查詢例如查看某門成績(jī)的成績(jī)表分類似1的

2、三種情況進(jìn)行執(zhí)行比擬。對(duì)查詢條件為一個(gè)連續(xù)的范圍的查詢例如查看學(xué)號(hào)在某個(gè)范圍內(nèi)的學(xué)生的選課情況分類似1的三種情況進(jìn)行執(zhí)行比擬，注意系統(tǒng)處理的選擇。索引代價(jià)。在有索引和無(wú)索引的情況下插入數(shù)據(jù)例如在選課情況表SC 上插入數(shù)據(jù)，比擬插入的執(zhí)行效率。3.2對(duì)相同查詢功能不同查詢語(yǔ)句的執(zhí)行比擬分析1有和沒(méi)有g(shù)roup by，比擬其查詢效率，并分析。group byselect avg(grade)from scgroup by cnohaving cno =100select avg(grade)from scwhere cno = 1002重寫后的查詢一定比原始查詢更優(yōu)嗎？通過(guò)執(zhí)行分析結(jié)果。selec

3、t sno，sname，agefrom student s1where age =(select max(age)from student s2where s1.dept = s2.dept)select dept ，max(age) as maxAge into tmpfrom studentgroup by dept;select sno, sname , agefrom student，tmpwhere student.age = tmp.maxAge and tmp.dept=student.deptdrop table tmp;3對(duì)下面兩個(gè)查詢進(jìn)行比擬select sname，age

4、from studentwhere dept != 10 and age all(select agefrom studentwhere dept = 10)select sname ,agefrom studentwhere dept != 10 and age (select max(age)from studentwhere dept = 10)3.3 查詢優(yōu)化除了建立適當(dāng)索引，對(duì)SQL語(yǔ)句重寫外，還有其他手段來(lái)進(jìn)行查詢調(diào)優(yōu)，例如調(diào)整緩沖區(qū)大小，事先建立視圖等。設(shè)計(jì)實(shí)現(xiàn)以下查詢，使之運(yùn)行效率最高。寫出你的查詢形式，以及調(diào)優(yōu)過(guò)程；并說(shuō)明最優(yōu)情況下的運(yùn)行時(shí)間。1查找選修了每一門課的學(xué)生。2查

5、找至少選修了課程數(shù)據(jù)庫(kù)原理和操作系統(tǒng)的學(xué)生的學(xué)號(hào)。4、實(shí)驗(yàn)過(guò)程4.1 索引對(duì)查詢的影響對(duì)結(jié)果集只有一個(gè)元組的查詢分三種情況進(jìn)行執(zhí)行比方查詢一個(gè)具體學(xué)生的信息：不建立索引在創(chuàng)立表是由于設(shè)置了primary key， mySQL自動(dòng)生成主鍵索引即聚集索引，為了測(cè)試不建立索引的情況下查詢學(xué)生信息的運(yùn)行時(shí)間，首先將主鍵索引刪除。查找學(xué)號(hào)為sno=31406的學(xué)生信息，結(jié)果為空集。查找語(yǔ)句的執(zhí)行時(shí)間為：學(xué)號(hào)上建立非聚集索引同樣先建立非聚集索引即普通索引，然后進(jìn)行同樣的查詢操作。查找學(xué)號(hào)為sno=31406的學(xué)生信息，結(jié)果為空集。查找語(yǔ)句的執(zhí)行時(shí)間為：學(xué)號(hào)上建立聚集索引重新設(shè)置該表的主鍵，會(huì)自動(dòng)生成主鍵索

6、引，即聚集索引，然后進(jìn)行同樣的查詢操作。查找學(xué)號(hào)為sno=31406的學(xué)生信息，結(jié)果為空集。查找語(yǔ)句的執(zhí)行時(shí)間為：執(zhí)行步驟及運(yùn)行時(shí)間結(jié)果如下，首先刪除student表的主鍵，在無(wú)索引的情況下查找學(xué)生信息；之后設(shè)立主鍵為sno學(xué)號(hào)，生成sno學(xué)號(hào)的聚集索引，查找學(xué)生信息；最后刪除主鍵，建立index_sno非聚集索引，查找學(xué)生信息。三種查詢語(yǔ)句的時(shí)間比照方下：查詢方法時(shí)間比擬不建立索引0.08761150時(shí)間最長(zhǎng)學(xué)號(hào)上建立非聚集索引0.00050150時(shí)間較短學(xué)號(hào)上建立聚集索引0.00038750時(shí)間最短對(duì)結(jié)果集中有多個(gè)元組的查詢例如查看某門成績(jī)的成績(jī)表分類似1的三種情況進(jìn)行執(zhí)行比擬。不建立索引

7、學(xué)號(hào)上、課程號(hào)上建立非聚集索引學(xué)號(hào)上、課程號(hào)上建立聚集索引執(zhí)行步驟及運(yùn)行時(shí)間結(jié)果如下，首先刪除sc表的主鍵，在無(wú)索引的情況下查找c01課程的成績(jī)表；之后在課程號(hào)和學(xué)號(hào)上建立非聚集索引，查詢c01課程的成績(jī)表，并刪除該索引；最后設(shè)立主鍵為cno課程號(hào)，sno學(xué)號(hào)，生成學(xué)號(hào)和課程號(hào)上的聚集索引，查找c01課程的成績(jī)表。三種查詢語(yǔ)句的時(shí)間比照方下：查詢方法時(shí)間比擬不建立索引0.00167800時(shí)間最長(zhǎng)建立非聚集索引0.00082550時(shí)間較短建立聚集索引0.00060400時(shí)間最短對(duì)查詢條件為一個(gè)連續(xù)的范圍的查詢例如查看學(xué)號(hào)在某個(gè)范圍內(nèi)的學(xué)生的選課情況分類似1的三種情況進(jìn)行執(zhí)行比擬，注意系統(tǒng)處理的選

8、擇。不建立索引建立非聚集索引建立聚集索引執(zhí)行步驟及運(yùn)行時(shí)間結(jié)果如下，首先刪除sc表的主鍵，在無(wú)索引的情況下查找學(xué)號(hào)為30201-30210之間的學(xué)生選課信息；之后在課程號(hào)和學(xué)號(hào)上建立非聚集索引，查找學(xué)號(hào)為30201-30210之間的學(xué)生選課信息，并刪除該索引；最后設(shè)立主鍵為cno課程號(hào)，sno學(xué)號(hào)，生成學(xué)號(hào)和課程號(hào)上的聚集索引，查找學(xué)號(hào)為30201-30210之間的學(xué)生選課信息。三種查詢語(yǔ)句的時(shí)間比照方下：查詢方法時(shí)間比擬不建立索引0.00090150時(shí)間最長(zhǎng)建立非聚集索引0.00041675時(shí)間最短建立聚集索引0.00055325時(shí)間較短索引代價(jià)。在有索引和無(wú)索引的情況下插入數(shù)據(jù)例如在選課情

9、況表SC上插入數(shù)據(jù)，比擬插入的執(zhí)行效率。不建立索引在學(xué)號(hào)，課程號(hào)上建立非聚集索引在學(xué)號(hào)，課程號(hào)上建立聚集索引執(zhí)行步驟及運(yùn)行時(shí)間結(jié)果如下，首先刪除sc表的主鍵，在無(wú)索引的情況下插入一行學(xué)生成績(jī)；之后在課程號(hào)和學(xué)號(hào)上建立非聚集索引，插入一行學(xué)生成績(jī)，并刪除該索引；最后設(shè)立主鍵為cno課程號(hào)，sno學(xué)號(hào)，生成學(xué)號(hào)和課程號(hào)上的聚集索引，插入一行學(xué)生成績(jī)。三種查詢語(yǔ)句的時(shí)間比照方下：查詢方法時(shí)間比擬不建立索引0.09039100時(shí)間最長(zhǎng)建立非聚集索引0.05449350時(shí)間較短建立聚集索引0.00133125時(shí)間最短4.2對(duì)相同查詢功能不同查詢語(yǔ)句的執(zhí)行比擬分析4.2.1 有和沒(méi)有g(shù)roup by，比擬

10、其查詢效率，并分析。1select avg(grade)from scgroup by cnohaving cno =C012select avg(grade)from scwhere cno = C01兩條語(yǔ)句的運(yùn)行結(jié)果是相同的，但是時(shí)間相差一個(gè)數(shù)量級(jí)。如以下圖。比擬兩條語(yǔ)句的運(yùn)行時(shí)間，有g(shù)roup by的語(yǔ)句總時(shí)間為0.00139200；沒(méi)有g(shù)roup by的語(yǔ)句總時(shí)間為0.00067600；沒(méi)有g(shù)roup by的語(yǔ)句運(yùn)行時(shí)間明顯要比另一條時(shí)間短。查詢語(yǔ)句時(shí)間比擬有 group by0.00139200長(zhǎng)沒(méi)有 group by0.00067600短 重寫后的查詢一定比原始查詢更優(yōu)嗎？通過(guò)執(zhí)

11、行分析結(jié)果。(1) select sno,sname,bdatefrom student as s1where bdate =(select max(bdate)from student as s2where s1.dept = s2.dept)(2) create table tmp as (select dept,max(bdate) as maxBdatefrom student group by dept);select sno, sname , bdatefrom student,tmpwhere student.bdate = tmp.maxBdate and tmp.dept=s

12、tudent.dept;drop table tmp;兩條語(yǔ)句的運(yùn)行結(jié)果是相同的，如以下圖：第一種查詢方法只包含一條語(yǔ)句，運(yùn)行時(shí)間為0.00292650s；第二種查詢方法包含三條語(yǔ)句，運(yùn)行時(shí)間分別為0.07077225s，0.00039900s，0.03445975s。其中，單純的查詢時(shí)間為0.00039900s。重寫后，單純的查詢會(huì)比沒(méi)重寫的查詢用時(shí)要少很多。甚至差了一個(gè)數(shù)量級(jí)。但是，重寫后的查詢需要建表和刪除表，如果沒(méi)有對(duì)這個(gè)新的表進(jìn)行屢次相關(guān)的查詢的話，那么建表時(shí)間會(huì)使得這個(gè)優(yōu)化得不償失。查詢語(yǔ)句時(shí)間比擬直接查詢0.00292650s長(zhǎng)事先建表查詢0.07077225s建表 單純的查詢時(shí)

13、間要比直接查詢少一個(gè)數(shù)量級(jí)，但是建表和刪表花費(fèi)了更多的時(shí)間。0.00039900s查詢0.03445975s刪表 對(duì)下面兩個(gè)查詢進(jìn)行比擬(1) select sname,bdatefrom student where dept != 計(jì)算機(jī) and bdate all( select bdate from student where dept = 計(jì)算機(jī) );(2) select sname, bdate from student where dept != 計(jì)算機(jī) and bdate (select max(bdate) from student where dept=計(jì)算機(jī));兩個(gè)語(yǔ)句的運(yùn)

14、行結(jié)果相同，且查詢結(jié)果為空集。比擬兩個(gè)語(yǔ)句的執(zhí)行時(shí)間， 第二條語(yǔ)句的執(zhí)行時(shí)間少于第一條語(yǔ)句。查詢語(yǔ)句時(shí)間比擬直接比擬0.00090500長(zhǎng)選擇比擬0.00072375短4.3 查詢優(yōu)化除了建立適當(dāng)索引，對(duì)SQL語(yǔ)句重寫外，還有其他手段來(lái)進(jìn)行查詢調(diào)優(yōu)，例如調(diào)整緩沖區(qū)大小，事先建立視圖等。設(shè)計(jì)實(shí)現(xiàn)以下查詢，使之運(yùn)行效率最高。寫出你的查詢形式，以及調(diào)優(yōu)過(guò)程；并說(shuō)明最優(yōu)情況下的運(yùn)行時(shí)間。查找選修了每一門課的學(xué)生。1方法一：直接查詢select sname from student where sno in(select sno from sc group by snohaving count(*) =

15、 (select count(*) from course);運(yùn)行時(shí)間：2方法二：事先創(chuàng)立tablecreate table student1 as (select distinct sno as sno1,count(cno) as accountfrom scgroup by sno);select sname from student1,studentwhere student1.account=5 and student1.sno1=student.sno ;drop table student1;運(yùn)行時(shí)間：3方法三：事先創(chuàng)立viewcreate view stu1 as(select

16、 distinct sno as sno1,count(cno) as accountfrom scgroup by sno);select sname from stu1, studentwhere stu1.account =5 and stu1.sno1=student.sno;drop view stu1;運(yùn)行時(shí)間：4嵌套子查詢select sname from studentwhere NOT exists (select * from coursewhere NOT exists (select * from SC whereSno = student.sno and cno =

17、Course o)運(yùn)行時(shí)間：方法直接查詢Create tableCreate view嵌套子查詢時(shí)間0.001257000.104937250.009923500.00309200結(jié)論最短最長(zhǎng)第三第二結(jié)論是，在建表，視圖和嵌套子查詢?nèi)N選擇下，嵌套子查詢的時(shí)間最少，但直接查詢更加快捷。查找至少選修了課程數(shù)據(jù)庫(kù)原理和操作系統(tǒng)的學(xué)生的學(xué)號(hào)。1方法一：事先創(chuàng)立tablecreate table tmp as(select sc.sno as sno,cnamefrom sc,coursewhere sc o=course o and (course ame=數(shù)據(jù)庫(kù)原理 or course ame=操作系統(tǒng));select snofrom tmp;drop table tmp;運(yùn)行時(shí)間：2方法二：事先創(chuàng)立viewcreate view tmpv as (select sc.sno as sno