在講SQLSERVER內部原理的之前我覺得非常有必要向大家介紹一下SQLSERVER的歷史
讓我們站在年看看計算機數據庫業界到底處於什麼狀態
年Oracle已經於年月發布了Oracle i(可能中文版在年才來到中國)Oracle i支持用JAVA編寫存儲過程支持XML支持Linux
年月SQLSERVER正式發布SQLSERVER重構了整個數據庫引擎(相當於重寫了SQLSERVER)SQLSERVER第一次完整性的支持了行鎖(有沒有搞錯過去人是怎麼使用數據庫產品的年Oracle就支持行鎖另外年Oracle就開始研發ERP產品誰說Oracle是ERP門外漢可以參考這個)
看看他們倆的前一個版本如果你入行比較晚(年以後)可能對以下文字更感到驚訝
年Oracle發布有了存儲過程觸發器引用完整性校驗分布式事務處理(天哪Oracle才有了這些東西)
年SQLSERVER發布SQLSERVER是微軟真正意義上的第一個數據庫產品(真是爆料大家沒想到SQLSERVER才是微軟第一個數據庫產品那版本之前的是怎麼度過的)因為年微軟和Sybase掰了(Sybase是第一個運行於PC上的C/S數據庫產品)微軟為了進入數據庫產品領域自己又沒有經驗於是和Sybase一起合作(當時微軟是全世界第一大軟件公司微軟年上市Sybase有產品缺錢微軟缺產品有錢於是一拍即合)直到年微軟也不需要Sybase了(已經學會了數據庫技術)Sybase也感覺微軟太狼子野心於是合作分裂微軟開始自己做自己的數據庫
歷史說完我們言歸正傳
很多入門級做管理軟件的SQL語句玩的熟練從子查詢到Having到交叉表統計SQL都能做出來甚至存儲過程能寫多行游標自定義函數觸發器約束用的眼花缭亂再入點門在SQL查詢器中可以使用SQL分析優化索引用SQL Profile可以跟蹤SQL甚至在性能查看器中監測SQLSERVER內存CPU線程I/O的運行狀態甚至為自己會使用DBCC而沾沾自喜
你是如此熟悉SQLSERVER又是對SQLSERVER如此陌生
我今天就用架構的角度來給大家分析一下SQLSERVER架構和原理短短一篇博文肯定只能面上的多一些深一層的可能需要連載數篇文章甚至一塊大磚頭書才能講完整不過我希望我的博文能夠拋磚引玉使大家能從一個過去沒有想過的角度去看SQLSERVER
SQLSERVER作為一個數據庫產品我個人認為最重要的就是兩大塊存儲引擎和查詢引擎
其他的日志事務鎖索引等等都是圍繞他們來工作的
SQLSERVER是C/S產品所以一條SQL語句要讓SQLSERVER執行必須要傳輸到SQLSERVER服務器端傳輸我們當然知道需要NetBEUITCP/IP等等網絡傳輸協議但是光有這些還不行客戶端如何發服務器端如何收如何確認發的和收的正確完整如何確實發的和收的已經結束如何發和收能跨越各種網絡協議(如UNIX和WINDOWS和NOVELL通訊)如何保證數據安全校驗如何保證數據收發是同步還是異步就需要在網絡傳輸協議之上再構造一層協議SQLSERVER既支持IPC機制也支持RPC機制你想想你的管理軟件開發平台是否有這一層當然現在的消息服務器已經專業的提供了這一機理可靠的安全的高效的異步的消息壓縮消息拆分智能路由集群跨越不同的操作系統不同的編程語言不同的通訊協議不同的硬件平台的消息數據傳輸可能你過去不了解消息中間件通過這一案例可以知道消息中間件的用途
SQL語句被可靠無誤的發送到了服務器端SQLSERVER引擎中第一個模塊就來接待這個SQL數據這個模塊的名字叫Open Data Services它監聽新的連接;清除失敗連接;將結果集消息和狀態返回給客戶端
SQLSERVER客戶端和服務器端之間傳輸數據數據包是有格式的在SQLSERVER中被稱為tabular data stream這個數據流是令牌控制客戶端和服務器端對話(否則客戶端說了N句話服務器端返回N句話沒有令牌就混在一起了不知道哪個回答是對應哪個請求的)我們往往不能直接和Open Data Services打交道把數據放進來而是我們必須通過ODBCADO或DBLibrary來發送tabular data stream而SQLSERVER返回的數據結果也是通過這些ODBC之類發回tabular data stream你看看SQLSERVER設計的多巧妙一個通用數據訪問接口屏蔽了你和SQLSERVER之間就如同WINDOWS API屏蔽了內核讓你無法訪問就如同DirectX屏蔽了UI和外設的操控
SQL語句ODBC編碼成tabular data streamIPC或RPC網絡協議IPC或RPC解碼tabular data streamODBCOpen 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要保證高速處理必須預先預測好哪些數據頁和索引頁要處理不能人家緩沖管理器已經處理完你訪問方法管理器才計算下一步將要處理的頁面要知道這些管理器可是不分哪個用戶來處理的如果接受來自多個並發的用戶發來各種各樣的數據處理請求你怎麼能預測到哪些數據頁和索引頁要處理呢?這就需要一個統一的調度而且這個統一的調度也影響著緩沖區管理器你不能請求一個大數據緩沖區管理器這才火燒屁股才擴大緩沖區然後裝載數據那樣流水線就停下了緩沖區管理器必須預先知道將在不久要有一個大數據所以在並行運算的時候就有獨立線程來擴展了緩沖區因為擴大緩沖區還和操作系統有關你要擴大緩沖區正好遇到WINDOWS頁面失效就涉及到你的虛擬文件的變化而頁面失效又會影響CPU和I/O所以頁面失效是一個性能影響很大的問題而提高命中率是我們性能優化一直努力的重點如果數據長時間不用緩沖區管理器就要讓這塊內存數據過期可以被新的數據覆蓋否則緩沖區老加載不卸載也不行再說有些數據已經被更新了你數據老化了不重新讀入你的數據就引起讀錯誤了
我們知道數據頁包含數據行索引頁包含索引行數據行就由行管理器來控制而索引行由索引管理器來負責
而單行上的檢索修改執行又被事務管理器和鎖管理器影響著事務有顯性事務和隱性事務兩種而鎖又有共享鎖排它鎖更新鎖意向鎖而鎖還分為行鎖頁鎖表鎖數據庫鎖而鎖又有死鎖的可能性鎖的不同加上事務的影響這個行是否能讀能修改能怎樣的讀(讀一致還是髒讀)是等待事務和鎖還是可以進行就受了很多影響因為一張數據頁上放的行是有限的尤其還有填充度的影響(如填充度為%就這個數據頁面只能填充%就必須分頁以防以後有數據插入的時候就非常影響數據插頁這也是性能影響比較大尤其在插入數據比較多的情況下)SQLSERVER的一張數據頁默認是K除去填充度和數據頭也沒有多少可存儲的數據了這就是為了關系型數據庫都勸阻大家要小表大數據也就是說列要少列要短頻繁訪問的列要在前數據可以海量如果行長了你想要檢索和更新多少數據頁這需要多少頁面調度面臨著頁面失效和鎖機制的影響而且大文本和可變行都是指針存儲需要跳轉查找更浪費了不少時間
而索引管理器最主要在維護著索引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數據庫(mastermsdbtempdbmodel)
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22318.html
