數(shù)據(jù)庫(kù)SQL SELECT查詢(xún)的工作原理

作為Web開(kāi)發(fā)人員，雖并非專(zhuān)業(yè)的DBA，但我們總是離不開(kāi)數(shù)據(jù)庫(kù)。一般開(kāi)發(fā)員只會(huì)應(yīng)用SQL的四條經(jīng)典語(yǔ)句:select，insert，delete，update。以至于從來(lái)沒(méi)有研究過(guò)它們的工作原理，在這里我們說(shuō)一說(shuō)select在數(shù)據(jù)庫(kù)中的工作原理。

B/S架構(gòu)中最經(jīng)典的話題無(wú)非于三層架構(gòu)，可以大概分為數(shù)據(jù)層，業(yè)務(wù)邏輯層和表示層，而數(shù)據(jù)層的作用一般都是和數(shù)據(jù)庫(kù)交互，例如查詢(xún)記錄。我們經(jīng)常是寫(xiě)好查詢(xún)SQL，然后調(diào)用程序執(zhí)行SQL。但是它內(nèi)部的工作流程是怎樣的呢？先做哪一步，然后做哪一步等，我想還有大部分朋友和我一樣都不一定清楚。

第一步：應(yīng)用程序把查詢(xún)SQL語(yǔ)句發(fā)給服務(wù)器端執(zhí)行

我們?cè)跀?shù)據(jù)層執(zhí)行SQL語(yǔ)句時(shí)，應(yīng)用程序會(huì)連接到相應(yīng)的數(shù)據(jù)庫(kù)服務(wù)器，把SQL語(yǔ)句發(fā)送給服務(wù)器處理。

第二步：服務(wù)器解析請(qǐng)求的SQL語(yǔ)句

1.SQL計(jì)劃緩存，經(jīng)常用查詢(xún)分析器的朋友大概都知道這樣一個(gè)事實(shí)，往往一個(gè)查詢(xún)語(yǔ)句在第一次運(yùn)行的時(shí)候需要執(zhí)行特別長(zhǎng)的時(shí)間，但是如果你馬上或者在一定時(shí)間內(nèi)運(yùn)行同樣的語(yǔ)句，會(huì)在很短的時(shí)間內(nèi)返回查詢(xún)結(jié)果。

原因：

• 服務(wù)器在接收到查詢(xún)請(qǐng)求后，并不會(huì)馬上去數(shù)據(jù)庫(kù)查詢(xún)，而是在數(shù)據(jù)庫(kù)中的計(jì)劃緩存中找是否有相對(duì)應(yīng)的執(zhí)行計(jì)劃，如果存在，就直接調(diào)用已經(jīng)編譯好的執(zhí)行計(jì)劃，節(jié)省了執(zhí)行計(jì)劃的編譯時(shí)間。
• 如果所查詢(xún)的行已經(jīng)存在于數(shù)據(jù)緩沖存儲(chǔ)區(qū)中，就不用查詢(xún)物理文件了，而是從緩存中取數(shù)據(jù)，這樣從內(nèi)存中取數(shù)據(jù)就會(huì)比從硬盤(pán)上讀取數(shù)據(jù)快很多，提高了查詢(xún)效率.數(shù)據(jù)緩沖存儲(chǔ)區(qū)會(huì)在后面提到。

2.如果在SQL計(jì)劃緩存中沒(méi)有對(duì)應(yīng)的執(zhí)行計(jì)劃，服務(wù)器首先會(huì)對(duì)用戶請(qǐng)求的SQL語(yǔ)句進(jìn)行語(yǔ)法效驗(yàn)，如果有語(yǔ)法錯(cuò)誤，服務(wù)器會(huì)結(jié)束查詢(xún)操作，并用返回相應(yīng)的錯(cuò)誤信息給調(diào)用它的應(yīng)用程序。

注意:此時(shí)返回的錯(cuò)誤信息中，只會(huì)包含基本的語(yǔ)法錯(cuò)誤信息，例如select寫(xiě)成selec等，錯(cuò)誤信息中如果包含一列表中本沒(méi)有的列，此時(shí)服務(wù)器是不會(huì)檢查出來(lái)的，因?yàn)橹皇钦Z(yǔ)法驗(yàn)證，語(yǔ)義是否正確放在下一步進(jìn)行。

3.語(yǔ)法符合后，就開(kāi)始驗(yàn)證它的語(yǔ)義是否正確，例如，表名，列名，存儲(chǔ)過(guò)程等等數(shù)據(jù)庫(kù)對(duì)象是否真正存在，如果發(fā)現(xiàn)有不存在的，就會(huì)報(bào)錯(cuò)給應(yīng)用程序，同時(shí)結(jié)束查詢(xún)。

4.接下來(lái)就是獲得對(duì)象的解析鎖，我們?cè)诓樵?xún)一個(gè)表時(shí)，首先服務(wù)器會(huì)對(duì)這個(gè)對(duì)象加鎖，這是為了保證數(shù)據(jù)的統(tǒng)一性，如果不加鎖，此時(shí)有數(shù)據(jù)插入，但因?yàn)闆](méi)有加鎖的原因，查詢(xún)已經(jīng)將這條記錄讀入，而有的插入會(huì)因?yàn)槭聞?wù)的失敗會(huì)回滾，就會(huì)形成臟讀的現(xiàn)象。

5.接下來(lái)就是對(duì)數(shù)據(jù)庫(kù)用戶權(quán)限的驗(yàn)證，SQL語(yǔ)句語(yǔ)法，語(yǔ)義都正確，此時(shí)并不一定能夠得到查詢(xún)結(jié)果，如果數(shù)據(jù)庫(kù)用戶沒(méi)有相應(yīng)的訪問(wèn)權(quán)限，服務(wù)器會(huì)報(bào)出權(quán)限不足的錯(cuò)誤給應(yīng)用程序，在稍大的項(xiàng)目中，往往一個(gè)項(xiàng)目里面會(huì)包含好幾個(gè)數(shù)據(jù)庫(kù)連接串，這些數(shù)據(jù)庫(kù)用戶具有不同的權(quán)限，有的是只讀權(quán)限，有的是只寫(xiě)權(quán)限，有的是可讀可寫(xiě)，根據(jù)不同的操作選取不同的用戶來(lái)執(zhí)行，稍微不注意，無(wú)論你的SQL語(yǔ)句寫(xiě)的多么完善，完美無(wú)缺都沒(méi)用。

6.解析的最后一步，就是確定最終的執(zhí)行計(jì)劃。當(dāng)語(yǔ)法，語(yǔ)義，權(quán)限都驗(yàn)證后，服務(wù)器并不會(huì)馬上給你返回結(jié)果，而是會(huì)針對(duì)你的SQL進(jìn)行優(yōu)化，選擇不同的查詢(xún)算法以最高效的形式返回給應(yīng)用程序。例如在做表聯(lián)合查詢(xún)時(shí)，服務(wù)器會(huì)根據(jù)開(kāi)銷(xiāo)成本來(lái)最終決定采用hashjoin，mergejoin，還是loopjoin，采用哪一個(gè)索引會(huì)更高效等等，不過(guò)它的自動(dòng)化優(yōu)化是有限的，要想寫(xiě)出高效的查詢(xún)SQL還是要優(yōu)化自己的SQL查詢(xún)語(yǔ)句。

當(dāng)確定好執(zhí)行計(jì)劃后，就會(huì)把這個(gè)執(zhí)行計(jì)劃保存到SQL計(jì)劃緩存中，下次在有相同的執(zhí)行請(qǐng)求時(shí)，就直接從計(jì)劃緩存中取，避免重新編譯執(zhí)行計(jì)劃。

第三步：語(yǔ)句執(zhí)行

服務(wù)器對(duì)SQL語(yǔ)句解析完成后，服務(wù)器才會(huì)知道這條語(yǔ)句到底代表了什么意思，接下來(lái)才會(huì)真正的執(zhí)行SQL語(yǔ)句。

這時(shí)分兩種情況:

• 如果查詢(xún)語(yǔ)句所包含的數(shù)據(jù)行已經(jīng)讀取到數(shù)據(jù)緩沖存儲(chǔ)區(qū)的話，服務(wù)器會(huì)直接從數(shù)據(jù)緩沖存儲(chǔ)區(qū)中讀取數(shù)據(jù)返回給應(yīng)用程序，避免了從物理文件中讀取，提高查詢(xún)速度。
• 如果數(shù)據(jù)行沒(méi)有在數(shù)據(jù)緩沖存儲(chǔ)區(qū)中，則會(huì)從物理文件中讀取記錄返回給應(yīng)用程序，同時(shí)把數(shù)據(jù)行寫(xiě)入數(shù)據(jù)緩沖存儲(chǔ)區(qū)中，供下次使用。

說(shuō)明:SQL緩存分好幾種，這里有興趣的朋友可以去搜索一下，有時(shí)因?yàn)榫彺娴拇嬖冢沟梦覀兒茈y馬上看出優(yōu)化的結(jié)果，因?yàn)榈诙螆?zhí)行因?yàn)橛芯彺娴拇嬖?，?huì)特別快速，所以一般都是先消除緩存，然后比較優(yōu)化前后的性能表現(xiàn)，這里有幾個(gè)常用的方法:

DBCC DROPCLEANBUFFERS

從緩沖池中刪除所有清除緩沖區(qū)。

DBCC FREEPROCCACHE

從過(guò)程緩存中刪除所有元素。

DBCC FREESYSTEMCACHE

從所有緩存中釋放所有未使用的緩存條目。

SQLServer2005數(shù)據(jù)庫(kù)引擎會(huì)事先在后臺(tái)清理未使用的緩存條目，以使內(nèi)存可用于當(dāng)前條目。但是，可以使用此命令從所有緩存中手動(dòng)刪除未使用的條目。

這只能基本消除SQL緩存的影響，目前好像沒(méi)有完全消除緩存的方案，如果大家有，請(qǐng)指教。

結(jié)論:只有知道了服務(wù)執(zhí)行應(yīng)用程序提交的SQL的操作流程才能很好的調(diào)試我們的應(yīng)用程序。

• 確保SQL語(yǔ)法正確;
• 確保SQL語(yǔ)義上的正確性，即對(duì)象是否存在;
• 數(shù)據(jù)庫(kù)用戶是否具有相應(yīng)的訪問(wèn)權(quán)限。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請(qǐng)查看下面相關(guān)鏈接