[轉] SQL Server 原理

在講SQLSERVER內部原理的之前，我覺得非常有必要向大家介紹一下SQLSERVER的歷史。
讓我們站在1999年，看看計算機資料庫業界到底處於什麼狀態。

1999年，Oracle已經於1998年9月釋出了Oracle 8i（可能中文版在1999年才來到中國）。Oracle 8i支援用JAVA編寫儲存過程，支援XML，支援Linux。
1999年1月，SQLSERVER7正式釋出。SQLSERVER7重構了整個資料庫引擎（相當於重寫了SQLSERVER）。SQLSERVER第一次完整性的支援了行鎖（有沒有搞錯，過去人是怎麼使用資料庫產品的。1988年，Oracle6就支援行鎖。另外1988年，Oracle就開始研發ERP產品。誰說Oracle是ERP門外漢，可以參考這個）。

看看他們倆的前一個版本。如果你入行比較晚（2000年以後），可能對以下文字更感到驚訝。

1992年，Oracle7釋出。有了儲存過程、觸發器、引用完整性校驗、分散式事務處理。（天哪，Oracle7才有了這些東西）。
1995年，SQLSERVER6釋出。SQLSERVER6是微軟真正意義上的第一個資料庫產品（真是爆料，大家沒想到SQLSERVER6才是微軟第一個資料庫產品，那版本6之前的5、4、3、2、1是怎麼度過的）。因為1994年，微軟和Sybase掰了（Sybase是第一個執行於PC上的C/S資料庫產品）。微軟為了進入資料庫產品領域，自己又沒有經驗，於是和Sybase一起合作（當時微軟是全世界第一大軟體公司，微軟1986年上市。Sybase有產品，缺錢。微軟缺產品，有錢。於是一拍即合）。直到1994年，微軟也不需要Sybase了（已經學會了資料庫技術），Sybase也感覺微軟太狼子野心，於是合作分裂。微軟開始自己做自己的資料庫。

歷史說完。我們言歸正傳。
很多入門級做管理軟體的，SQL語句玩的熟練，從子查詢到Having到交叉表統計SQL都能做出來，甚至儲存過程能寫2000多行，遊標、自定義函式、觸發器、約束用的眼花繚亂。再入點門，在SQL查詢器中可以使用SQL分析最佳化索引，用SQL Profile可以跟蹤SQL，甚至在效能檢視器中監測SQLSERVER記憶體、CPU、執行緒、I/O的執行狀態，甚至為自己會使用DBCC而沾沾自喜。

你是如此熟悉SQLSERVER，又是對SQLSERVER如此陌生。

我今天就用架構的角度來給大家分析一下SQLSERVER架構和原理。短短一篇博文肯定只能面上的多一些，深一層的可能需要連載數篇文章甚至一塊大磚頭書才能講完整。不過，我希望我的博文能夠拋磚引玉，使大家能從一個過去沒有想過的角度去看SQLSERVER。

SQLSERVER，作為一個資料庫產品，我個人認為，最重要的就是兩大塊：儲存引擎和查詢引擎。

其他的日誌、事務、鎖、索引等等都是圍繞他們來工作的。

SQLSERVER是C/S產品，所以一條SQL語句要讓SQLSERVER執行，必須要傳輸到SQLSERVER伺服器端。傳輸，我們當然知道需要NetBEUI、TCP/IP等等網路傳輸協議。但是光有這些還不行。客戶端如何發，伺服器端如何收，如何確認發的和收的正確完整，如何確實發的和收的已經結束，如何發和收能跨越各種網路協議（如UNIX和WINDOWS和NOVELL通訊），如何保證資料安全校驗，如何保證資料收發是同步還是非同步，就需要在網路傳輸協議之上再構造一層協議。SQLSERVER既支援IPC機制，也支援RPC機制。你想想你的管理軟體開發平臺是否有這一層。當然，現在的訊息伺服器已經專業的提供了這一機理，可靠的、安全的、高效的、非同步的、訊息壓縮、訊息拆分、智慧路由、叢集，跨越不同的作業系統、不同的程式語言、不同的通訊協議、不同的硬體平臺的訊息資料傳輸。可能你過去不了解訊息中介軟體，透過這一案例可以知道訊息中介軟體的用途。

SQL語句被可靠無誤的傳送到了伺服器端，SQLSERVER引擎中第一個模組就來接待這個SQL資料。這個模組的名字叫：Open Data Services。它監聽新的連線；清除失敗連線；將結果集、訊息和狀態返回給客戶端。

SQLSERVER客戶端和伺服器端之間傳輸資料，資料包是有格式的。在SQLSERVER中被稱為tabular data stream。這個資料流是令牌控制客戶端和伺服器端對話（否則，客戶端說了N句話，伺服器端返回N句話，沒有令牌就混在一起了，不知道哪個回答是對應哪個請求的）。我們往往不能直接和Open Data Services打交道，把資料放進來。而是我們必須透過ODBC、ADO或DB-Library來傳送tabular data stream。而SQLSERVER返回的資料結果，也是透過這些ODBC之類發回tabular data stream。你看看SQLSERVER設計的多巧妙，一個通用資料訪問介面遮蔽了你和SQLSERVER之間，就如同WINDOWS API遮蔽了核心讓你無法訪問，就如同DirectX遮蔽了UI和外設的操控。

SQL語句-ODBC-編碼成tabular data stream-IPC或RPC-網路協議-IPC或RPC-解碼tabular data stream-ODBC-Open Data Services。

Open Data Services監測客戶端連線。如果併發太多，它會建立連線，如果服務完，它會自己維護連線歸入池中。在池中保留一段生命期，它會自己釋放連線。如果有的客戶端連線中途突然斷掉（如客戶端重啟了），它在偵聽後無回應，它也會自己整理自己的連線的。我們在SQLSERVER執行緒中看到的連線，就是Open Data Services建立的。

Open Data Services有了連線（可能是建立的可能是從池裡拿出來的，池化、建立、銷燬都是非常講究技能的。池化多少，上下文資源如何保留，池化多長時間，什麼時候該銷燬，排程不當就會嚴重消耗資源），就把SQL接住。這時，是接到了Open Data Services的讀緩衝區裡面。這個緩衝區為高效能處理資料的SQLSERVER帶來一絲喘息機會，而就這一絲喘息機會，讓SQLSERVER可以遊刃有餘（你的設計有嗎？）。而Open Data Services有一個寫緩衝區。SQLSERVER把檢索到的資料，檢索出來就立即放進寫緩衝區，寫緩衝區一滿就立即被Open Data Service發走。當我過去研究SQLSERVER原理的時候，我常常讚歎，一個小小的SQLSERVER外圍模組都設計如此精妙，實在讓人佩服。我們經常在追求海量資料儲存和Cache架構，我們卻無視我們手邊的SQLSERVER。

SQL語句放到讀緩衝區，SQLSERVER的關係引擎就開始工作了。它總是在偵聽這個讀緩衝區。

SQL語句遇到的關係引擎的第一個模組就是命令分析器。我們在SQL查詢分析器中看到的查詢分析結果就是它的輸出傑作。它來構造查詢樹。首先是將你的SQL語句規範化（你想想你寫的軟體程式碼，輸入資料來了什麼都不管就直接處理，連輸入資料校驗都沒有，怎能穩定），否則以後的步驟將不好操作，如果你的SQL語句有語法錯誤，這個查詢樹的構造就無法完成，於是中斷。而要規範一個SQL語句，首先要從SQL語法庫中抽取SQLSERVER現有支援的各種語法和函式。

一旦構造成功，關係引擎的第二個模組就是命令最佳化器，來裁剪這棵樹。一個SQL語句可以生成多種執行和最佳化的方案（如果你使用過那種SQL最佳化工具的話，你就能理解），SQLSERVER會選擇最節省記憶體、CPU利用率、I/O次數（I/O是效能最佳化最要命的地方，往往效能就瓶頸在I/O上）的那一種方案。最佳化器會根據每張表的資料統計（有時候你為了效能最佳化，必須定時期同步更新一下統計，否則最佳化就會有誤差）。而且最佳化器也會根據查詢樹去選擇合適的索引（如果使用索引代價大，它會自動選擇全表掃描），最佳化器也會根據查詢樹知道先取哪些表的資料，然後再記憶體中如何合併資料，以得到你想要的結果（有時候想想最佳化器真偉大，你一個SQL過去，它需要在極短的時間內做多少事啊，為了能在極短時間內確定一個相對最佳化的方案，它也不可能窮舉所有可能的方案，所以我們做海量資料最佳化的時候，往往評估多種方案，然後修改自己的SQL語句以符合產生最優的方案）。

規範化、最佳化完SQL語句，就要產生執行計劃了。SQL管理器負責執行計劃的產生。因為你發過來的SQL語句可能是一個SELECT，也可能是一個INSERT或UPDATE。即使SELECT，也面臨著使用者許可權的限制（你如果設定過某一個SQLSERVER使用者的物件許可權和列許可權，你就會明白）。而INSERT之類更新語句，又會涉及到許可權、預設值、約束、表示式、主外來鍵、觸發器。一個最佳化完的SQL，具體要真正讓SQLSERVER從記憶體或硬碟上把資料找出來或者更新回去，需要很多細節的步驟。

查詢執行器來負責SQL的執行。因為SQL的執行要涉及到事務、鎖、等待、CPU排程，記憶體頁失效影響、I/O存取影響，所以查詢執行器會協調很多其他模組，但各個模組來負責處理，而查詢執行器並不真正全部包辦，否則讓事務管理器、鎖管理器、索引管理器、頁面檔案管理器、緩衝管理器、行管理器、日誌管理器幹嗎去。

查詢執行器是查詢引擎的最後一個模組，接下來的模組都屬於儲存引擎的範疇。所以，從上看，查詢引擎最主要是構造SQL查詢樹、最佳化裁剪SQL查詢樹，根據查詢樹產生執行計劃，然後協調執行查詢樹，把結果返回去。

而真正要把資料取出來或存進去，就需要儲存引擎來工作了。

首先根據執行計劃，要存取哪些資料頁和索引頁。這就是訪問方法管理器（access methods manager）要做的事情。但其實真要開啟這些頁，還不是訪問方法管理器自己要親手乾的。

親手幹這個活的是一個叫“緩衝區管理器”的模組。因為在硬碟上的資料是不可能計算處理的，必須要在記憶體中才能讓CPU來計算。所以要存取那些資料頁和索引頁，就通知讓緩衝區管理器來做。如果資料沒有在記憶體中，就讓緩衝區管理器來讀入，如果資料已經在記憶體中了，緩衝區管理器只有返回即可。這個過程是被緩衝區管理器來遮蔽的，對於訪問方法管理器是透明的。大家可不要以為訪問方法管理器啥事不做，只是一個釋出排程命令的。這可錯怪了它。因為SQLSERVER要保證高速處理，必須預先預測好哪些資料頁和索引頁要處理。不能人家緩衝管理器已經處理完，你訪問方法管理器才計算下一步將要處理的頁面。要知道，這些管理器可是不分哪個使用者來處理的。如果接受來自100多個併發的使用者，發來各種各樣的資料處理請求，你怎麼能預測到哪些資料頁和索引頁要處理呢？這就需要一個統一的排程。而且這個統一的排程也影響著緩衝區管理器。你不能請求一個大資料，緩衝區管理器這才火燒屁股才擴大緩衝區，然後裝載資料，那樣流水線就停下了。緩衝區管理器必須預先知道將在不久要有一個大資料，所以在並行運算的時候就有獨立執行緒來擴充套件了緩衝區。因為擴大緩衝區還和作業系統有關。你要擴大緩衝區，正好遇到WINDOWS頁面失效，就涉及到你的虛擬檔案的變化。而頁面失效又會影響CPU和I/O。所以頁面失效是一個效能影響很大的問題。而提高命中率是我們效能最佳化一直努力的重點。如果資料長時間不用，緩衝區管理器就要讓這塊記憶體資料過期，可以被新的資料覆蓋。否則緩衝區老載入不解除安裝也不行。再說，有些資料已經被更新了，你資料老化了，不重新讀入，你的資料就引起讀錯誤了。

我們知道，資料頁包含資料行。索引頁包含索引行。資料行就由行管理器來控制。而索引行，由索引管理器來負責。

而單行上的檢索、修改、執行，又被事務管理器和鎖管理器影響著。事務，有顯性事務和隱性事務兩種。而鎖，又有共享鎖、排它鎖、更新鎖、意向鎖。而鎖，還分為行鎖、頁鎖、表鎖、資料庫鎖。而鎖，又有死鎖的可能性。鎖的不同，加上事務的影響，這個行是否能讀、能修改，能怎樣的讀（讀一致還是髒讀），是等待事務和鎖，還是可以進行，就受了很多影響。因為一張資料頁上放的行是有限的，尤其還有填充度的影響（如填充度為80%，就這個資料頁面只能填充80%就必須分頁，以防以後有資料插入的時候，就非常影響資料插頁，這也是效能影響比較大，尤其在插入資料比較多的情況下）。SQLSERVER的一張資料頁預設是64K，除去填充度和資料頭，也沒有多少可儲存的資料了。這就是為了關係型資料庫都勸阻大家要小表大資料。也就是說，列要少，列要短，頻繁訪問的列要在前。資料可以海量。如果行長了，你想要檢索和更新多少資料頁，這需要多少頁面排程，面臨著頁面失效和鎖機制的影響。而且，大文字和可變行，都是指標儲存，需要跳轉查詢，更浪費了不少時間。

而索引管理器，最主要在維護著索引B樹。沒有索引頁，我們就要做全表掃描了，那需要載入多少資料頁，而且還要逐行掃描，如果遇上事務和更新鎖，就更有問題。所以，索引是非常重要的。而一個表，可以建立很多索引。索引，能直接找到所需要的行，而無須全表掃描。但是，你的索引如果僅僅是男女，或者你的索引涉及到可變行，都對索引不利。索引，不宜建立多。否則維護索引頁的成本和消耗也非常多。索引頁更要涉及到插頁、拆頁，頻繁改動涉及到索引的欄位，會讓索引頁劇烈變動，尤其資料量越大影響越大。我就不在這裡講解如何利用索引最佳化SQL了，否則一本書也講不完。

資料不斷存取，資料不斷被維護，載入記憶體或從記憶體中寫入硬碟。其實都是惰性寫入器在照顧。惰性寫入器來定期掃描老化資料，讓硬碟和記憶體中的資料是一致的。有這個惰性寫入器，就有了記憶體和硬碟的差異時間窗。就有可能出現異常。一旦伺服器突然斷電，沒有來得及寫會磁碟的怎麼辦。也也涉及到另一個模組：日誌管理器。日誌管理器利用檢查點的機制維護著日誌檔案。在伺服器重新啟動的時候，重寫載入日誌來把資料恢復到一致性。寫日誌，當然要比寫資料要容易的多，快的多。因為寫資料要操控記憶體和硬碟，還要注意許可權、鎖、事務，所以突然斷電，你還沒反應就來不及了。所以日誌這種輕量級的方法，就可以在恢復一致性上有很好的幫助（當然，也丟失資料。日誌頁也沒來得及寫入硬碟）。

講到這裡，就剩下事務管理器、鎖管理器。這兩個管理器和顯性事務、隱性事務、顯性鎖、隱性鎖、事務隔離級別、鎖級別、行管理器、索引管理器都有很多關係。微軟有WINDOWS優勢，又有Jim Gray這樣的巨師坐鎮（Jim Gray是圖靈獎獲得者，就是此爺提出了資料庫事務這一概念。蓋茨為了讓此爺為微軟工作，而此爺不喜歡雷德蒙天天下雨的天氣，於是在加州陽光中給此爺單獨建了一座研究院）。所以，在效能上，我個人認為SQLSERVER的效能是非常優秀的（你想想，一個資料庫產品的效能受什麼方面的影響）。至於業界老稱SQLSERVER無法管理海量資料，效能不佳，我個人感覺都是業界在以訛傳訛。而尤其中國內地IT業界，大部分都是入門級在跟帖嘈雜，尤其還有一批更不懂技術的媒體記者或寫手。

如果真要去說SQLSERVER不行，大型海量資料管理必須用某某資料庫產品，我建議從內部原理、內部架構、內部實現三個層次諸多方面來剖析到底在不在理。

最後就是I/O管理器了。我一直不認同SQLSERVER核心中有I/O管理器。因為SQLSERVER使用的是和WINDOWS同樣的頁面排程和頁面分配方法。何必要自己另創一套呢。就如同SQLSERVER把頁面、硬碟、記憶體、執行緒、CPU交給了WINDOWS一樣。SQLSERVER作為WINDOWS上的一個應用軟體，應該和WINDOWS上的其他軟體一樣被WINDOWS管理。SQLSERVER又不跨平臺，無須自己管理。

除了SQLSERVER這些核心涉及精妙以外，SQLSERVER的外圍工具也設計的相當好。如SQLSERVER的使用者安全性管理方法、物件分類（表、列、約束、預設、索引、觸發器、儲存過程、檢視、主鍵）、物件許可權方法、後設資料自管理方法、SQL語言、SQL查詢分析器、SQL跟蹤器、SQL效能分析器、SQL資料庫（master\msdb\tempdb\model）。

垃圾廣告.com/blog/182083