2023學(xué)年完整公開課版查詢優(yōu)化

主講教師：魏巍巍北京信息職業(yè)技術(shù)學(xué)院數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用查詢優(yōu)化創(chuàng)建采集任務(wù)01查詢優(yōu)化實(shí)例0102目錄CONTENTS查詢優(yōu)化01查詢優(yōu)化在關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)中有著非常重要的地位。關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)和非過程化的SQL語(yǔ)言能夠取得巨大的成功，關(guān)鍵是得益于查詢優(yōu)化技術(shù)的發(fā)展。關(guān)系查詢優(yōu)化是影響RDBMS性能的關(guān)鍵因素。由于關(guān)系表達(dá)式的語(yǔ)義級(jí)別很高，使關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)可以從關(guān)系表達(dá)式中分析查詢語(yǔ)義，提供了執(zhí)行查詢優(yōu)化的可能性。這就為關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)在性能上接近甚至超過非關(guān)系系數(shù)據(jù)庫(kù)統(tǒng)提供了機(jī)遇。查詢優(yōu)化查詢優(yōu)化的優(yōu)點(diǎn)不僅在于用戶不必考慮如何最好地表達(dá)查詢以獲得較好的效率，而且在于系統(tǒng)可以比用戶程序的“優(yōu)化”做得更好。這是因?yàn)椋?/p>

1.優(yōu)化器可以從數(shù)據(jù)字典中獲取許多統(tǒng)計(jì)信息，例如關(guān)系中的元組數(shù)、關(guān)系中每個(gè)屬性值的分布情況等。優(yōu)化器可以根據(jù)這些信息選擇有效的執(zhí)行計(jì)劃，而用戶程序則難以獲得這些信息。查詢優(yōu)化2.如果數(shù)據(jù)庫(kù)的物理統(tǒng)計(jì)信息改變了，系統(tǒng)可以自動(dòng)對(duì)查詢進(jìn)行重新優(yōu)化以選擇相適應(yīng)的執(zhí)行計(jì)劃。在非關(guān)系系統(tǒng)中必須重寫程序，而重寫程序在實(shí)際應(yīng)用中往往是不太可能的。3.優(yōu)化器可以考慮數(shù)百種不同的執(zhí)行計(jì)劃，而程序員一般只能考慮有限的幾種可能性。4.優(yōu)化器中包括了很多復(fù)雜的優(yōu)化技術(shù)，這些優(yōu)化技術(shù)往往只有最好的程序員才能掌握。系統(tǒng)的自動(dòng)優(yōu)化相當(dāng)于使得所有人都擁有這些優(yōu)化技術(shù)。

查詢優(yōu)化查詢優(yōu)化一般來(lái)說，可以歸納為四個(gè)步驟：

將查詢轉(zhuǎn)換成某種內(nèi)部表示，通常是語(yǔ)法樹。

根據(jù)一定的等價(jià)變換規(guī)則把語(yǔ)法樹轉(zhuǎn)換成標(biāo)準(zhǔn)（優(yōu)化）形式。

選擇低層的操作算法。對(duì)于語(yǔ)法樹中的每一個(gè)操作需要根據(jù)存取路徑、數(shù)據(jù)的存儲(chǔ)分布、存儲(chǔ)數(shù)據(jù)的聚簇等信息來(lái)選擇具體的執(zhí)行算法。查詢優(yōu)化

生成查詢計(jì)劃。查詢計(jì)劃也稱查詢執(zhí)行方案，是由一系列內(nèi)部操作組成的。這些內(nèi)部操作按一定的次序構(gòu)成查詢的一個(gè)執(zhí)行方案。通常這樣的執(zhí)行方案有多個(gè)，需要對(duì)每個(gè)執(zhí)行計(jì)劃計(jì)算代價(jià)，從中選擇代價(jià)最小的一個(gè)。在集中式關(guān)系數(shù)據(jù)庫(kù)中，計(jì)算代價(jià)時(shí)主要考慮磁盤讀寫的I/O次數(shù)，也有一些系統(tǒng)還考慮了CPU的處理時(shí)間。

查詢優(yōu)化目前的商品化RDBMS大都采用基于代價(jià)的優(yōu)化算法。在集中式數(shù)據(jù)庫(kù)中，查詢的執(zhí)行開銷主要包括：

總代價(jià)=I/O代價(jià)+CPU代價(jià)在多用戶環(huán)境下，內(nèi)存在多個(gè)用戶間的分配情況會(huì)明顯地影響這些用戶查詢執(zhí)行的總體性能。因此，多用戶數(shù)據(jù)庫(kù)還應(yīng)考慮查詢的內(nèi)存開銷，即：

總代價(jià)=I/O代價(jià)+CPU代價(jià)+內(nèi)存代價(jià)查詢優(yōu)化實(shí)例02[例]求選修了1024號(hào)課程的學(xué)生姓名。用SQL語(yǔ)言表達(dá)：

SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno='1024';假定學(xué)生-課程數(shù)據(jù)庫(kù)中有l(wèi)000個(gè)學(xué)生記錄，l0000個(gè)選課記錄，其中選修1024號(hào)課程的選課記錄為50個(gè)。實(shí)例系統(tǒng)可以用多種等價(jià)的關(guān)系代數(shù)表達(dá)式來(lái)完成這一查詢Q1=πSname(Student.sno=Sc.So='1024' (Student×SC))Q2=πSname(o=’1024’(StudentSC))Q3=πSname(Student

o='1024'(SC))還可以寫出幾種等價(jià)的關(guān)系代數(shù)表達(dá)式，但分析這三種就足以說明問題了。后面將看到由于查詢執(zhí)行的策略不同，查詢時(shí)間相差很大。

實(shí)例第一種情況1.計(jì)算廣義笛卡爾積

把S和SC的每個(gè)元組連接起來(lái)。一般連接的做法是：在內(nèi)存中盡可能多地裝入某個(gè)表(如Student表)的若干塊元組，留出一塊存放另一個(gè)表(如SC表)的元組。然后把SC中的每個(gè)元組和Student中每個(gè)元組連接，連接后的元組裝滿一塊后就寫到中間文件上，再?gòu)腟C中讀入一塊和內(nèi)存中的S元組連接，直到SC表處理完。這時(shí)再一次讀入若干塊S元組，讀入一塊SC元組，重復(fù)上述處理過程，直到把S表處理完。實(shí)例設(shè)一個(gè)塊能裝l0個(gè)Student元組或l00個(gè)SC元組，在內(nèi)存中存放5塊Student元組和l塊SC元組，則讀取總塊數(shù)為：

1000/10+1000/10*5×10000/100=100+20*100=2100塊

其中讀Student表l00塊。讀SC表20遍，每遍讀l00塊。若每秒讀寫20塊，則總計(jì)要花105s。

連接后的元組數(shù)為l03×104=l07。設(shè)每塊能裝l0個(gè)元組，則寫出這些塊要用時(shí)l06/20=5×104s。實(shí)例2.作選擇操作

依次讀入連接后的元組，按照選擇條件選取滿足要求的記錄。假定內(nèi)存處理時(shí)間忽略。這一步讀取中間文件花費(fèi)的時(shí)間(同寫中間文件一樣)需5×104s。滿足條件的元組假設(shè)僅50個(gè)，均可放在內(nèi)存。3.作投影

把第2步的結(jié)果在Sname上作投影輸出，得到最終結(jié)果。因此第一種情況下執(zhí)行查詢的總時(shí)間≈l05+2×5×104≈l05s。這里，所有內(nèi)存處理時(shí)間均忽略不計(jì)。實(shí)例第二種情況l.計(jì)算自然連接

為了執(zhí)行自然連接，讀取Student和SC表的策略不變，總的讀取塊數(shù)仍為2?100塊花費(fèi)l05s。但自然連接的結(jié)果比第一種情況大大減少，為104個(gè)。因此寫出這些元組時(shí)間為104/10/20=50s，僅為第一種情況的千分之一。2.讀取中間文件塊，執(zhí)行選擇運(yùn)算，花費(fèi)時(shí)間也為50s.3.把第2步結(jié)果投影輸出。實(shí)例第三種情況1.先對(duì)SC表作選擇運(yùn)算，只需讀一遍SC表，存取l00塊花費(fèi)時(shí)間為5s，因?yàn)闈M足條件的元組僅50個(gè)，不必使用中間文件。2.讀取STUDENT表，把讀入的STUDENT元組和內(nèi)存中的SC元組作連接。也只需讀一遍STUDENT表共l00塊花費(fèi)時(shí)間為5s。實(shí)例3.把連接結(jié)果投影輸出。

第三種情況總的執(zhí)行時(shí)間≈5+5≈10s。

假如SC表的