Microsoft SQL Server 資料庫體系結構圖解

大家好，我是只談技術不剪發的 Tony 老師，

Microsoft SQL Server 是微軟公司開發的一款關系型資料庫管理系統，支持 Windows、Linux 以及容器部署，Microsoft SQL Server 采用標準的客戶端-服務器體系結構，客戶端發送請求到服務端，服務端處理完成之后回傳結果到客戶端，本文給大家詳細介紹一下 Microsoft SQL Server 資料庫的體系結構以及各個組件的功能，

如果覺得文章有用，歡迎評論📝、點贊👍、推薦🎁

整體結構

Microsoft SQL Server 服務由一個實體（Instance）和多個資料庫（Databases）組成，實體包含了后臺執行緒和占用的記憶體，默認的系統資料庫包括 master、model、msdb、Resource 以及 tempdb，

Microsoft SQL Server 的整體系統結構如下：

主要包含以下三個組件：

• 協議層（Protocol Layer），主要負責客戶端的連接請求和資料通信，
• 關系引擎（Relational Engine），主要負責 SQL 陳述句的決議、優化和執行，
• 存盤引擎（Storage Engine），主要負責資料和日志的存盤和訪問、記憶體和快取管理、事務和鎖管理，

協議層

客戶端應用首先需要通過 SNI 網路介面（SQL Server Network Interface）與服務器建立連接，Microsoft SQL Server 提供了以下三種協議方式：

• TCP/IP 協議；
• 共享記憶體（Shared Memory）協議；
• 命名管道（Named Pipes）協議，

TCP/IP 協議

TCP/IP 是遠程客戶端連接 Microsoft SQL Server 服務器默認使用的方式，在 SSMS 工具中通過 TCP/IP 連接資料庫的配置如下：

其中，192.168.56.104 是服務器的地址；Microsoft SQL Server 默認監聽的 TCP/IP 埠為 1433，

共享記憶體協議

當客戶端應用和 Microsoft SQL Server 服務位于同一臺機器時，可以使用共享記憶體協議進行通信，使用 SSMS 連接本地資料庫時的配置如下：

其中，Server name 選項可以設定為以下內容之一：

• .
• localhost
• 127.0.0.1
• computer name\instance name

命名管道協議

命名管道是一種實作行程間通信的 FIFO 機制，兩個行程可以通過管道的名字打開、讀寫管道，如果客戶端應用和 Microsoft SQL Server 服務位于同一局域網內或者同一臺機器時，可以使用命名管道協議進行通信，在 SSMS 工具中通過命名管道連接資料庫的配置如下：

默認情況下 Microsoft SQL Server 服務器沒有啟用命名管道協議，可以通過 SQL Server Configuration Manager 工具進行配置，對于本地資料庫，默認創建的命名管道為 \\.\pipe\sql\query，

TDS 協議

客戶端連接建立后，可以發送查詢陳述句給服務器，服務器執行完成之后再將結果回傳給客戶端，Microsoft SQL Server 在應用層使用 TDS（ Tabular Data Stream、表格資料流）協議實作客戶端和服務器之間的資料傳輸請求和回應，

TDS 會話直接與底層的傳輸級會話系結，意味著在建立傳輸級連接之后，服務器接收到建立 TDS 連接的請求時建立 TDS 會話，TDS 會話一直持續到傳輸級連接終止（例如關閉 TCP 套接字時），

TDS 包含了用于身份驗證和標識、通道加密協商、SQL 批處理命令提交、存盤程序呼叫、資料回傳以及事務管理器請求等功能，回傳的資料具有自描述性并且是面向記錄的，資料流包含了要回傳的資料行名稱、型別以及可選的描述，下圖描繪了 TDS 協議中一個典型的（簡化的）通信流程：

關系引擎

Microsoft SQL Server 協議層接收到客戶端的請求并處理之后，將陳述句傳遞給關系引擎進行處理，關系引擎也成為查詢處理器（Query Processor），關系引擎決定了查詢需要執行的操作以及如何最好地實作該操作，負責請求存盤引擎獲取用戶所需的資料并且對結果進行處理，然后通過協議層將結果回傳給客戶端，

關系引擎包含了以下三個主要部分：

• 命令決議器（CMD Parser）；
• 查詢優化器（Query Optimizer）；
• 查詢執行器（Query Executor），

命令決議器

命令決議器主要的作用是檢查 T-SQL 陳述句的語法和語意錯誤，并創建一個內部的查詢樹（Query Tree），

Microsoft SQL Server 和其他編程語言一樣預定義了很多關鍵字，同時具有自己的語法格式，例如，SELECT、INSERT、UPDATE、CREATE、DROP 等都屬于預定義的關鍵字，命令決議器首先對輸入的陳述句進行語法檢查，如果違反了語法規則，將會回傳一個錯誤，例如：

SELECR * FROM employee;
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.

其中，SELECT 錯寫成了 SELECR，所以回傳了一個語法錯誤，

接下來是語意檢查，包括表名和欄位名是否存在，存在的話將其系結（Binding）到該查詢，如果查詢涉及了視圖，還會使用視圖的定義進行陳述句替換，例如：

SELECT * FROM no_table;
Msg 208, Level 16, State 1, Line 1
Invalid object name 'no_table'.

由于當前模式中不存在表 no_table，查詢回傳了物件名無效的錯誤，

完成檢查之后，命令決議器為 T-SQL 陳述句創建一個查詢樹，然后傳遞給查詢優化器，

查詢優化器

查詢優化器的作用是創建一個執行計劃，也就是執行查詢陳述句的具體操作，需要注意的是，并非所有的查詢都會進行優化，DML 命令（例如 SELECT、INSERT、DELETE 以及 UPDATE 等）會發送給優化器；DDL 命令（例如 CREATE、ALTER 等）不會進行優化，而是直接編譯成內部格式，

查詢優化器的輸入包括查詢陳述句、資料庫模式（表和索引的定義）以及資料庫統計資訊， 查詢優化器的輸出稱為“查詢執行計劃”，有時也稱為“查詢計劃”或為“執行計劃”，在優化單個 SELECT 陳述句期間查詢優化器的輸入和輸出如下圖中所示：

Microsoft SQL Server 查詢優化器是基于成本的優化器，基于輸入引數和各種因素，例如所需的 CPU 使用率、記憶體以及 I/O 等，對查詢成本進行計算，然后找出最佳（而不是成本最低）的執行計劃，執行計劃包含了從每個表提取資料的方法（表掃描或者索引訪問）、多個表的訪問順序、執行計算的方法以及對每個表中的資料進行篩選、聚合和排序的方法，

查詢執行器

查詢執行器負責呼叫存盤引擎執行具體的計劃，存盤引擎提供了獲取資料的訪問方法（Access Method），查詢執行器將存盤引擎回傳的資料處理成為結果集定義的格式后，通過協議層將結果集回傳客戶端，

存盤引擎

存盤引擎負責存盤系統（例如磁盤或者 SAN）中的資料存盤和檢索，存盤引擎包含了 3 個組件：

• 訪問方法（Access Method）；
• 緩沖管理器（Buffer Manager）；
• 事務管理器（Transaction Manager），

訪問方法

訪問方法是查詢執行器和緩沖管理器/事務日志之間的一個介面，首先，它會判斷查詢的型別是 SELECT 陳述句還是 DDL/DML 陳述句；如果是 SELECT 陳述句，則將其傳遞給緩沖管理器進行處理；如果是 DDL 或者 DML 陳述句（例如 UPDATE），則將其傳遞給事務管理器進行處理，

緩沖管理器

緩沖管理器實作了以下核心功能模塊：

• 執行計劃快取（Plan Cache）；
• 資料決議（Data Parsing）：緩沖區快取（Buffer cache）和資料存盤（Data storage）；
• 臟頁（Dirty Page），

第一次生成查詢計劃時，如果計劃比較復雜，緩沖管理器會將該查詢和相應的執行計劃存盤到快取中，快取管理器對每次查詢進行檢查，如果服務器接收到相同的查詢，可以重用快取的查詢計劃和資料，

快取管理器還提供了資料的訪問操作，如果資料已經位于資料快取（Data cache）中，直接通過快取回傳資料；這種方式減少了磁盤 I/O，提高了資料訪問的性能，被稱為資料的軟決議（Soft Parsing），

如果所需的資料不在資料快取中，通過磁盤 I/O 訪問資料存盤設備中的檔案，同時將資料存盤到快取中，這種方式被稱為資料的硬決議（Hard Parsing），

臟頁是只記憶體中被修改過但還沒有寫入磁盤的資料，它由事務管理器產生，具體參考下文中的事務管理器部分，

事務管理器

如果查詢屬于修改物件或者資料的陳述句，需要呼叫事務管理器，事務管理器包括日志管理器（Log Manager）和鎖管理器（Log Manager），

日志管理器利用事務日志（Transaction Log）中的日志項記錄了系統的所有更新操作，每條日志記錄由一個日志序列號（LSN）標識，同時包含了事務 ID 和資料修改記錄， Microsoft SQL Server 使用預寫日志 (Write-ahead Logging) ，可以確保在將相關日志記錄寫入磁盤后再將資料修改寫入磁盤，維護了事務的 ACID 屬性，如果系統出現故障，則可能需要使用事務日志將資料庫恢復到一致狀態，

鎖管理器用于在事務處理期間管理事務對所依賴的資源（如行、頁或表）上請求的鎖， 鎖可以阻止其他事務以某種可能會導致事務請求鎖出錯的方式修改資源，實作事務的隔離性和一致性，

資料庫事務的處理流程如下：

• 日志管理器開始記錄日志，同時鎖定管理器鎖定相關的資料，
• 在緩沖區快取中維護資料的副本；
• 在日志緩沖區中記錄被修改資料的前后鏡像，并且更新資料緩沖區中的資料副本，此時也就產生了資料的臟頁；
• 檢查點執行緒（Checkpoint）定期將資料臟頁和緩沖日志寫入磁盤，
• SQL Server 通過 Lazy Writer 執行緒使用 LRU（Least recently Used）演算法將資料臟頁重繪到磁盤檔案，

另外，Microsoft SQL Server 還提供了基于行版本控制的隔離，資料庫引擎將會維護修改的每一行的版本， 應用程式可以指定事務使用行版本查看事務或查詢開始時存在的資料，而不是使用鎖保護所有讀取， 通過使用行版本控制，讀取操作阻止其他事務的可能性將大大降低，

資料庫檔案

每個 SQL Server 資料庫至少具有兩個作業系統檔案：一個資料檔案和一個日志檔案， 資料檔案包含資料和物件，例如表、索引、存盤程序和視圖， 日志檔案包含恢復資料庫中的所有事務所需的資訊， 為了便于分配和管理，可以將資料檔案集合起來，放到檔案組中，

SQL Server 資料庫具有三種型別的檔案：

• 主要資料檔案（Primary file），包含資料庫的啟動資訊，并指向資料庫中的其他檔案， 每個資料庫有一個主要資料檔案， 主要資料檔案的建議檔案擴展名是 .mdf，
• 輔助資料檔案（Secondary file），用戶定義的可選資料檔案， 通過將每個檔案放在不同的磁盤驅動器上，可將資料分散到多個磁盤中，次要資料檔案的建議檔案擴展名是 .ndf，
• 事務日志檔案（Log file），此日志包含用于恢復資料庫的資訊， 每個資料庫必須至少有一個日志檔案， 事務日志的建議檔案擴展名是
  .ldf，

資料檔案是存盤資料的物理檔案，由多個資料頁（data page）組成； 資料頁是磁盤 I/O 操作的基本單位，每個資料頁大小為 8KB，是 Microsoft SQL Server 中最小的存盤單元，8 個物理上連續的資料頁組成一個區（extent），區是管理空間的基本單位，

每頁的開頭是 96 位元組的標頭（Page Header），用于存盤有關頁的系統資訊，包括頁碼（Page Number）、頁型別（Page Type）、頁的可用空間以及擁有該頁的物件的分配單元 ID、指向下一頁和上一頁的指標等，

日志檔案不包含頁，而是包含一系列日志記錄，默認情況下，資料和事務日志放在單磁盤系統的同一驅動器和路徑上， 對于生產環境， 建議將資料和日志檔案放在不同的磁盤上，