應用程式的SQL查詢原理

作為B/S架構的開發人員,總是離不開資料庫,一般開發員只會應用SQL的四條經典語句:select ,insert,delete,update。但是我從來沒有研究過它們的工作原理,這篇我想說一說select在資料庫中的工作原理。B/S架構中最經典的話題無非於三層架構,可以大概分為資料層,商務邏輯層和展示層,而資料層的作用一般都是和資料庫互動,例如查詢記錄。
我們經常是寫好查詢SQL,然後調用程式執行SQL。但是它內部的工作流程是怎樣的呢？先做哪一步，然後做哪一步等,我想還有大部分朋友和我一樣都不一定清楚。　

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

我們在資料層執行SQL語句時,應用程式會串連到相應的資料庫伺服器,把SQL語句發送給伺服器處理。

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

1:SQL計畫快取,經常用查詢分析器的朋友大概都知道這樣一個事實,往往一個查詢語句在第一次啟動並執行時候需要執行特別長的時間,但是如果你馬上或者在一定時間內運行同樣的語句,會在很短的時間內返回查詢結果。

原因:

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

2):如果所查詢的行已經存在於資料緩衝儲存區中,就不用查詢物理檔案了,而是從緩衝中取資料,這樣從記憶體中取資料就會比從硬碟上讀取資料快很多,提高了查詢效率.資料緩衝儲存區會在後面提到。

2:如果在SQL計畫快取中沒有對應的執行計畫,伺服器首先會對使用者請求的SQL語句進行文法效驗,如果有語法錯誤,伺服器會結束查詢操作,並用返回相應的錯誤資訊給調用它的應用程式。

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

3:文法符合後,就開始驗證它的語義是否正確,例如,表名,列名,預存程序等等資料庫物件是否真正存在,如果發現有不存在的,就會報錯給應用程式,同時結束查詢。

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

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

6:解析的最後一步,就是確定最終的執行計畫。當文法,語義,許可權都驗證後,伺服器並不會馬上給你返回結果,而是會針對你的SQL進行最佳化,選擇不同的查詢演算法以最高效的形式返回給應用程式。例如在做表聯集查詢時,伺服器會根據開銷成本來最終決定採用hash join,merge join ,還是loop join,採用哪一個索引會更高效等等,不過它的自動化最佳化是有限的,要想寫出高效的查詢SQL還是要最佳化自己的SQL查詢語句。
當確定好執行計畫後,就會把這個執行計畫儲存到SQL計畫快取中,下次在有相同的執行請求時,就直接從計畫快取中取,避免重新編譯執行計畫。

第三步：語句執行。

伺服器對SQL語句解析完成後,伺服器才會知道這條語句到底表態了什麼意思,接下來才會真正的執行SQL語句。
些時分兩種情況:

1):如果查詢語句所包含的資料行已經讀取到資料緩衝儲存區的話,伺服器會直接從資料緩衝儲存區中讀取資料返回給應用程式,避免了從物理檔案中讀取,提高查詢速度。

2):如果資料行沒有在資料緩衝儲存區中,則會從物理檔案中讀取記錄返回給應用程式,同時把資料行寫入資料緩衝儲存區中,供下次使用。

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

DBCC DROPCLEANBUFFERS

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

DBCC FREEPROCCACHE

從過程緩衝中刪除所有元素。

DBCC FREESYSTEMCACHE

　　從所有緩衝中釋放所有未使用的緩衝條目。SQL Server 2005 資料庫引擎會事先在後台清理未使用的緩衝條目，以使記憶體可用於當前條目。但是，可以使用此命令從所有緩衝中手動刪除未使用的條目。

這隻能基本消除SQL緩衝的影響,目前好像沒有完全消除緩衝的方案,如果大家有,請指教。

結論:只有知道了服務執行應用程式提交的SQL的操作流程才能很好的調試我們的應用程式。
1:確保SQL文法正確;
2:確保SQL語義上的正確性,即對象是否存在;
3:資料庫使用者是否具有相應的存取權限。

   註：該文轉載