前言

對(duì)于一個(gè)只有幾千行甚至幾萬(wàn)行數(shù)據(jù)的查詢的小系統(tǒng)來(lái)說(shuō)，數(shù)據(jù)庫(kù)的查詢優(yōu)化作用不大，但對(duì)于大型的應(yīng)用系統(tǒng)，數(shù)據(jù)動(dòng)輒上百萬(wàn)，就需要了解DBMS對(duì)查詢語(yǔ)句的處理過(guò)程和優(yōu)化算法，更好的利用其優(yōu)化算法，以提高系統(tǒng)的性能。

查詢執(zhí)行過(guò)程

執(zhí)行一條查詢語(yǔ)句需要做查詢分析、查詢檢查、查詢優(yōu)化、查詢執(zhí)行等幾個(gè)關(guān)鍵步驟，具體描述如下：

查詢分析：對(duì)查詢語(yǔ)句進(jìn)行掃描、詞法分析和語(yǔ)法分析，從查詢語(yǔ)句中識(shí)別出語(yǔ)言符號(hào)，進(jìn)行語(yǔ)法檢查和語(yǔ)法分析

查詢檢查：根據(jù)數(shù)據(jù)字典對(duì)合法的查詢語(yǔ)句進(jìn)行語(yǔ)義檢查。包括對(duì)用戶的存取權(quán)限進(jìn)行檢查和完整性約束定義檢查；檢查通過(guò)后把SQL查詢語(yǔ)句轉(zhuǎn)換成等價(jià)的關(guān)系代數(shù)表達(dá)式，一般都用查詢樹(shù)(語(yǔ)法分析樹(shù))來(lái)表示關(guān)系代數(shù)表達(dá)式

查詢優(yōu)化：數(shù)據(jù)庫(kù)管理系統(tǒng)會(huì)自動(dòng)的選擇一個(gè)高效執(zhí)行的查詢處理策略。所謂的查詢優(yōu)化就是我們DBMS可選擇的高效查詢處理策略，提供條件，如：建立那種類(lèi)型的索引

查詢執(zhí)行：代碼生成器(code generator)生成執(zhí)行查詢計(jì)劃的代碼，執(zhí)行相應(yīng)指令。 查詢優(yōu)化是根據(jù)DBMS根據(jù)物理設(shè)計(jì)建立的存取路徑，選擇最優(yōu)的算法進(jìn)行優(yōu)化，DBMS查詢優(yōu)化包括代數(shù)優(yōu)化和物理優(yōu)化。

單表選擇操作常用算法

• 全表掃描：對(duì)查詢的基本表順序掃描，逐一檢查每個(gè)元組是否滿足選擇條件，把滿足條件的元組作為結(jié)果輸出。此種算法適合小表，不適合大表
• 索引掃描：通過(guò)索引先找到滿足條件的元組主碼或元組指針，再通過(guò)元組指針直接在查詢的基本表中找到元組，索引使用B+樹(shù)或hash散列，當(dāng)元組比較多時(shí)，速度比全表掃描快

在選擇操作上的啟發(fā)

1. 對(duì)于小關(guān)系，使用全表順序掃描，即使選擇列上有索引
2. 對(duì)于大關(guān)系，選擇條件是主碼＝值的查詢，結(jié)果只有一個(gè)元組，選擇主碼索引；
3. 對(duì)于大關(guān)系，非主屬性＝值的查詢，若選擇列上有索引，DBMS根據(jù)查詢結(jié)果的元組數(shù)目個(gè)數(shù)確定存取方法，若元組比例較小(<10%)使用索引掃描方法，否則還是使用全表順序掃描
4. 查詢條件包含AND連接的合取選擇條件，如果組合索引（如條件為學(xué)號(hào)和班級(jí)聯(lián)合索引），優(yōu)先采用組合索引掃描方法
5. 查詢條件包含AND連接的合取選擇條件，如果某些屬性上有一般的索引（如只有學(xué)號(hào)索引），則可以用索引掃描方法，沒(méi)有索引使用全表掃描
6. 查詢條件包含or、between、！=、<>、in、是否為空（null）的使用，將無(wú)法使用索引

結(jié)論：使用全表掃描還是索引掃描，是由DBMS根據(jù)表中記錄數(shù)多少和查找數(shù)據(jù)記錄數(shù)進(jìn)行選擇的。

一般情況下是選擇的記錄數(shù)大于整個(gè)表記錄總數(shù)的20%或者表中的記錄很少，使用全表掃描。因此如果表中記錄很少或者每次查詢選用的記錄很多，所建的索引不能提高性能。

連接操作的實(shí)現(xiàn)算法

以 SELECT * FROM student，sc WHERE student.sno=sc.sno為例，假設(shè)student表中1000條記錄，sc表中10000條記錄

1. 嵌套循環(huán)：對(duì)外層循環(huán)(student)的每一個(gè)元組(s)，檢索內(nèi)層循環(huán)(sc)中的每一個(gè)元組(sc)，檢查這兩個(gè)元組在連接屬性(sno)上是否相等，如果滿足連接條件，則串接后作為結(jié)果輸出，直到外層循環(huán)表中的元組處理完為止 。執(zhí)行次數(shù)為1000*10000=10 7 ^7 7次
2. 排序合并：先對(duì)student和sc表中的sno排序，取student表中第一個(gè)sno，依次掃描sc表中具有相同sno的元組，當(dāng)掃描到Sno不相同的第一個(gè)SC元組時(shí)，返回Student表掃描它的下一個(gè)元組，再掃描sc表中具有相同sno的元組，把它們連接起來(lái)， 直到student 表掃描完成。執(zhí)行次數(shù)為10000=10 4 ^4 4次，但需加排序消耗的時(shí)間
3. 索引連接：如果sc表上沒(méi)有屬性sno的索引，建立sno索引；對(duì)student中每一個(gè)元組，由sno值通過(guò)SC的索引查找相應(yīng)的SC元組；把這些SC元組和Student元組連接起來(lái)；直到Student表中的元組處理完為止。執(zhí)行連接次數(shù)為1000= 10 3 ^3 3次，但需要消耗索引的查找時(shí)間
4. Hash連接 ：把連接屬性作為hash碼，用同一個(gè)hash函數(shù)把R和S中的元組散列到同一個(gè)hash文件中。第一步取兩個(gè)表中較小元祖（student）的一個(gè)進(jìn)行一遍處理，把它的元組按hash函數(shù)分散到hash表的桶中，第二步，對(duì)另一個(gè)表(sc)進(jìn)行一遍處理，把S的元組散列到適當(dāng)?shù)膆ash桶中，把元組與桶中所有來(lái)自R并與之相匹配的元組連接起來(lái)

在連接操作上的啟發(fā)

• 如果2個(gè)表都已經(jīng)按照連接屬性排序，選用排序-合并方法
• 如果一個(gè)表在連接屬性上有索引，選用索引連接方法
• 如果上面2個(gè)規(guī)則都不適用，其中一個(gè)表較小，選用Hashjoin方法
• 可以選用嵌套循環(huán)方法，并選擇其中較小的表，確切地講是占用的塊數(shù)(b)較少的表，作為外表(外循環(huán)的表)

結(jié)論： 當(dāng)表中的元祖數(shù)比較大時(shí)，建議建立索引，這樣會(huì)大大的提高查找效率。但需要執(zhí)行索引查找需要通過(guò)索引的指針間接地獲取數(shù)據(jù)以及管理索引也需要成本，當(dāng)元祖數(shù)少的時(shí)候，DBMS不會(huì)選擇排序合并和索引連接。當(dāng)一個(gè)表元祖少，一個(gè)多，無(wú)索引時(shí)，DBMS可能會(huì)選擇Hash連接

基于關(guān)系代數(shù)優(yōu)化

集中式數(shù)據(jù)庫(kù)：總代價(jià)=I/O代價(jià)+CPU代價(jià)+內(nèi)存代價(jià)，主要考慮I/O代價(jià)；

分布式數(shù)據(jù)庫(kù)：總代價(jià)=I/O代價(jià)+CPU代價(jià)+內(nèi)存代價(jià)＋通信代價(jià)，主要考慮I/O代價(jià)和通訊代價(jià)

【示例】： 假定學(xué)生-課程數(shù)據(jù)庫(kù)中有1000個(gè)學(xué)生記錄，10000個(gè)選課記錄，其中選修2號(hào)課程的選課記錄為50個(gè)，使用不同關(guān)系代數(shù)計(jì)算此SQL表達(dá)式的代價(jià) SELECT Student.Sname FROM Student，SC WHERE Student.Sno=SC.Sno AND SC.Cno=‘2’； 設(shè)一個(gè)塊能裝10個(gè)Student元組或100個(gè)SC元組，在內(nèi)存中可以存放5塊Student元組和1塊SC元組。

1. πsname(σstudent.sno=sc.sno∧sc.cno=‘2’ (student×sc)) student和sc兩個(gè)關(guān)系做笛卡爾積，將拼接好的元祖移出內(nèi)存寫(xiě)到中間文件上，待拼接完成后，再讀入做選擇運(yùn)算，拼接元祖每塊10條元祖，每秒讀20塊，則需時(shí)間成本≈105+2×5×10 4 ^4 4≈10 5 ^5 5s=27.8小時(shí)
2. πsname(σsc.cno=‘2’ (student ? sc)) 即兩個(gè)關(guān)系先做自然連接，再做選擇和投影，這樣學(xué)生和課程匹配后最多有10000個(gè)元組，即SC元組個(gè)數(shù)。寫(xiě)入和讀取中間文件的成本大大縮小。同樣每塊10條元祖，每秒讀20塊，則需時(shí)間成本≈105+50+50≈205s
3. πsname(Student ? σsc.cno=‘2’(sc)) 先對(duì)sc表做選擇，選擇完成后sc最多符合條件的數(shù)據(jù)有50條，總的執(zhí)行時(shí)間≈5+5≈10s
4. 第三種情況，如果sc表cno有索引，則讀入索引塊數(shù)更少，約3·4塊，student上sno也有索引的話，沒(méi)有必要讀入所有的student元祖，時(shí)間更快，約1秒左右就可讀出。

代數(shù)優(yōu)化啟發(fā)，盡量減少I(mǎi)/O操作，減少寫(xiě)入讀入數(shù)據(jù)塊的次數(shù)，就可以提高性能，對(duì)我們有以下啟發(fā)

• 選擇運(yùn)算應(yīng)盡可能先做。在優(yōu)化策略中這是最重要、最基本的一條
• 把投影運(yùn)算和選擇運(yùn)算同時(shí)進(jìn)行，減少讀寫(xiě)中間文件次數(shù)
• 把投影同其前或其后的雙目運(yùn)算結(jié)合起來(lái)
• 減少掃描關(guān)系的次數(shù)
• 某些選擇同在它前面要執(zhí)行的笛卡爾積結(jié)合起來(lái)成為一個(gè)連接運(yùn)算
• 找出公共子表達(dá)式

總結(jié)

查詢優(yōu)化技術(shù)是每中類(lèi)型的數(shù)據(jù)庫(kù)管理系統(tǒng)中的關(guān)鍵技術(shù)，是由DBMS自動(dòng)完成的，但不要把優(yōu)化的任務(wù)全部放在RDBMS上，應(yīng)該找出RDBMS的優(yōu)化規(guī)律，以寫(xiě)出適合RDBMS自動(dòng)優(yōu)化的SQL語(yǔ)句。 對(duì)于RDBMS不能優(yōu)化的查詢需要重新規(guī)劃書(shū)寫(xiě)查詢語(yǔ)句，進(jìn)行手工調(diào)整以優(yōu)化查詢性能。

到此這篇關(guān)于數(shù)據(jù)庫(kù)sql查詢性能優(yōu)化詳解的文章就介紹到這了,更多相關(guān)數(shù)據(jù)庫(kù)查詢優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論