前言

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

查詢執(zhí)行過程

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

查詢分析：對查詢語句進行掃描、詞法分析和語法分析，從查詢語句中識別出語言符號，進行語法檢查和語法分析

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

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

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

單表選擇操作常用算法

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

總結(jié)

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

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

最新評論