資料庫sql select查詢的工作原理

我並非專業DBA，但身為B/S架構的開發人員，總是離不開資料庫。一般開發者只會應用SQL的四個經典語句:select，insert，delete，update。但是我從來沒有研究過它們的工作原理，這篇我想說一說select在資料庫中的工作原理。

B/S架構中最經典的話題無非於三層架構，可以大概分為資料層，業務邏輯層和表示層，而資料層的作用一般都是和資料庫交互，例如查詢記錄。我們常常是寫好查詢SQL，然後呼叫程式執行SQL。但是它內部的工作流程是怎麼樣的呢？先做哪一步，然後做哪一步等，我想還有大部分朋友跟我一樣都不一定清楚。

第一步：應用程式把查詢SQL語句發給伺服器端執行

我們在資料層執行SQL語句時，應用程式會連接到對應的資料庫伺服器，把SQL語句傳送給伺服器處理。

第二步：伺服器解析請求的SQL語句

1.SQL計劃緩存，經常用查詢分析器的朋友大概都知道這樣一個事實，往往一個查詢語句在第一次運行的時候需要執行特別長的時間，但是如果你馬上或在一定時間內運行同樣的語句，會在很短的時間內回傳查詢結果。

原因：

伺服器在接收到查詢請求後，並不會馬上去資料庫查詢，而是在資料庫中的計劃快取中找是否有相對應的執行計劃，如果存在，就直接呼叫已經編譯好的執行計劃，節省了執行計劃的編譯時間。

如果所查詢的行已經存在於數據緩衝存儲區中，就不用查詢物理文件了，而是從緩存中取數據，這樣從內存中取數據就會比從硬碟上讀取數據快很多，提高了查詢效率.資料緩衝儲存區會在後面提到。

2.如果在SQL計劃快取中沒有對應的執行計劃，伺服器首先會對使用者要求的SQL語句進行語法效驗，如果有語法錯誤，伺服器會結束查詢操作，並用傳回對應的錯誤訊息給呼叫它的應用程式.

注意:此時傳回的錯誤訊息中，只會包含基本的語法錯誤訊息，例如select寫成selec等，錯誤訊息中如果包含一列表中本沒有的列，此時伺服器是不會檢查出來的，因為只是語法驗證，語意是否正確放在下一步進行。

3.語法符合後，就開始驗證它的語義是否正確，例如，表名，列名，存儲過程等等數據庫對像是否真正存在，如果發現有不存在的，就會報錯給應用程序，同時結束查詢。

4.接下來就是獲得物件的解析鎖，我們在查詢一個表格時，首先伺服器會對這個物件加鎖，這是為了保證資料的統一性，如果不加鎖，此時有資料插入，但因為沒有加鎖的原因，查詢已經將這條記錄讀入，而有的插入會因為事務的失敗會回滾，就會形成髒讀的現象。

5.接下來就是對資料庫使用者權限的驗證，SQL語句語法，語意都正確，此時不一定能夠得到查詢結果，如果資料庫使用者沒有對應的存取權限，伺服器會報出權限不足的錯誤給應用程序，在稍大的項目中，往往一個項目裡面會包含好幾個數據庫連接串，這些數據庫用戶具有不同的權限，有的是只讀權限，有的是只寫權限，有的是可讀可寫，根據不同的操作選取不同的使用者來執行，稍微不注意，無論你的SQL語句寫的多麼完善，完美無缺都沒用。

6.解析的最後一步，就是確定最終的執行計劃。當語法，語義，權限都驗證後，伺服器並不會馬上給你回傳結果，而是會針對你的SQL進行最佳化，選擇不同的查詢演算法以最高效的形式傳回給應用程式。例如在做表聯合查詢時，伺服器會根據開銷成本來最終決定採用hashjoin，mergejoin，還是loopjoin，採用哪一個索引會更有效率等等，不過它的自動化優化是有限的，要寫出高效的查詢SQL還是要最佳化自己的SQL查詢語句。

當確定好執行計劃後，就會把這個執行計劃保存到SQL計劃緩存中，下次在有相同的執行請求時，就直接從計劃緩存中取，避免重新編譯執行計劃。

第三步：語句執行

伺服器對SQL語句解析完成後，伺服器才會知道這語句到底代表了什麼意思，接下來才會真正的執行SQL語句。

這時分兩種情況:

如果查詢語句所包含的資料行已經讀取到資料緩衝儲存區的話，伺服器會直接從資料緩衝儲存區中讀取資料傳回應用程序，避免了從實體文件中讀取，提高查詢速度。

如果資料行沒有在資料緩衝儲存區中，則會從實體檔案中讀取記錄傳回給應用程序，同時把資料行寫入資料緩衝儲存區中，以供下次使用。

說明:SQL快取分好幾種，這裡有興趣的朋友可以去搜尋一下，有時因為快取的存在，使得我們很難馬上看出優化的結果，因為第二次執行因為有快取的存在，會特別快速，所以一般都是先消除緩存，然後比較優化前後的性能表現，這裡有幾個常用的方法:

DBCCDROPCLEANBUFFERS

從緩衝池中刪除所有清除緩衝區。

DBCCFREEPROCCACHE

從過程快取中刪除所有元素。

DBCCFREESYSTEMCACHE

從所有快取中釋放所有未使用的快取條目。 SQLServer2005資料庫引擎會事先在背景清理未使用的快取項目，以使記憶體可用於目前條目。但是，可以使用此命令從所有快取中手動刪除未使用的條目。

這只能基本消除SQL快取的影響，目前好像沒有完全消除快取的方案，如果大家有，請指教。

結論:只有知道了服務執行應用程式提交的SQL的操作流程才能很好的調試我們的應用程式。

確保SQL語法正確;

確保SQL語意上的正確性，即物件是否存在;

資料庫使用者是否具有對應的存取權。