北郵大三數(shù)據(jù)庫實驗六數(shù)據(jù)查詢分析實驗

1、實驗六 數(shù)據(jù)查詢分析實驗實驗目的通過對不同情況下查詢語句的執(zhí)行分析，鞏固和加深對查詢和查詢優(yōu)化相關理論知識的理解，提高優(yōu)化數(shù)據(jù)庫系統(tǒng)的實踐能力，熟悉了解Sybase中查詢分析器的使用，并進一步提高編寫復雜查詢的SQL 程序的能力。實驗內容1.索引對查詢的影響（1）對結果集只有一個元組的查詢分三種情況進行執(zhí)行（必如查詢一個具體學生的信息）：不建立索引，（學號上）建立非聚集索引，（學號上）建立聚集索引。建立聚集索引：create clustered index studenton student(student_id)go建立非聚集索引：create nonclustered index stud

2、ent_indexon student(student_id)go用查詢分析器的執(zhí)行步驟和結果對執(zhí)行進行分析比較。select * from student where student_id='30201'不建立索引建立聚集索引建立非聚集索引（2）對結果集中有多個元組的查詢（例如查看某門成績的成績表）分類似（1）的三種情況進行執(zhí)行比較。select * from student where student_id>'30401'不建立索引：建立聚集索引：建立非聚集索引：（3）對查詢條件為一個連續(xù)的范圍的查詢（例如查看學號在某個范圍內的學生的選課情況）分類似（

3、1）的三種情況進行執(zhí)行比較，注意系統(tǒng)處理的選擇。select * from student where student_id between '31201' and '31415'不建立索引：建立聚集索引：建立非聚集索引：（4）索引代價。在有索引和無索引的情況下插入數(shù)據(jù)（例如在選課情況表SC 上插入數(shù)據(jù)），比較插入的執(zhí)行效率。insert into student values('31710','張攀','男','1993-1-1 00:00:00','計算機','3146&

4、#39;)delete from student where student_id ='31710'無索引：建立聚集索引：建立非聚集索引：2、對相同查詢功能不同查詢語句的執(zhí)行比較分析（1） group byselect avg(score)from scgroup by course_idhaving course_id='C01'select avg(score)from scwhere course_id='C01'比較其查詢效率可知，沒有group by的查詢時間比較短，查詢效率較高（2）select student_id,student_

5、name,birthdayfrom student s1where birthday=(select max(birthday)from student s2where s1.department = s2.department）另一個：select department,max(birthday) as maxAge into tmpfrom studentgroup by department;select student_id,student_name,birthdayfrom student,tmpwhere student.birthday = tmp.maxAge and tmp.

6、department=student.departmentdrop table tmp查詢結果來看，重寫的執(zhí)行時間要快一些，但相差不多，如果數(shù)據(jù)庫比較大的話，執(zhí)行效果也許更明顯（3）對下面兩個查詢進行比較select student_name,birthdayfrom studentwhere department!='電信' and birthday> all(select birthdayfrom studentwhere department ='電信'）另：select student_name,birthdayfrom studentwhere

7、department!='電信' and birthday> (select max(birthday)from studentwhere department ='電信'）3、查詢優(yōu)化除了建立適當索引，對SQL 語句重寫外，還有其他手段來進行查詢調優(yōu)，例如調整緩沖區(qū)大小，事先建立視圖等。設計實現(xiàn)下列查詢，使之運行效率最高。寫出你的查詢形式，以及調優(yōu)過程；并說明最優(yōu)情況下的運行時間。（1） 查找選修了每一門課的學生。方法一：with student1(num,account) as(select distinct student_id, count(cou

8、rse_id)from scgroup by student_id)select student_namefrom student1,studentwhere student1.account>=5 and student1.num=student.student_id方法二：select student_namefrom studentwhere not exists(select * from coursewhere not exists(select *from scwhere sc.student_id=student.student_id and course.course_i

9、d=sc.course_id)嘗試了兩種方法，相比較而言，第二種方法的執(zhí)行速度更快。（2） 查找至少選修了課程數(shù)據(jù)庫原理和操作系統(tǒng)的學生的學號。方法一：(select student_idfrom studentviewwhere course_name='操作系統(tǒng)')intersect(select student_idfrom studentview where course_name='數(shù)據(jù)庫原理')方法二：(select student_idfrom sc,coursewhere course.course_name='操作系統(tǒng)' and sc.course_id= course.course_id)intersect(select student_idfrom sc, coursewhere course.course_name='數(shù)據(jù)庫原理' and sc. course_id = course. course_id)相比較而言不使用視圖的方法查詢速度較快實驗總結：1、 本次實驗遇到的第一個問題就是如何建立索引的問題，在之前實驗的基礎上開始做，發(fā)現(xiàn)設置了主鍵之后，數(shù)據(jù)庫會默認以主鍵為搜索碼建立聚集索引，而且無法刪除索引也不好更改，所以只好重新建表格進行實驗，自己來