IT工程師數位筆記本

在了解 SQL Server 數據庫時，可以先從數據庫的體系結構來觀察。SQL Server 的體系結構中包含 4 個主要組成部分：

• 協議層（Protocols）
• 關系引擎（Relational Engine），也稱為查詢處理器（Query Processor）
• 存儲引擎（Storage Engine）
• SQLOS

協議層（Protocols）

當應用程序與 SQL Server 數據庫通信時，首先需要通過 SNI（SQL Server Network Interface）網絡接口選擇建立通信連接的協議。可以使用以下協議：

• TCP/IP：應用最廣泛的協議；
• Named Pipes：僅為局域網（LAN）提供服務；
• Shared Memory：僅支持在同一臺機器上；
• VIA（Virtual Interface Adapter）：僅支持高性能 VIA 硬件；（該協議已棄用）

可以對 SQL Server 進行配置，使其可以同時支持多種協議。各種協議在不同的環境中有著不同的性能表現，需要根據性能需求選擇合適的協議。如果客戶端并未指定使用哪種協議，則可配置逐個地嘗試各種協議。

連接建立后，應用程序即可與數據庫進行直接的通信。當應用程序準備使用 T-SQL 語句 "select * from TableA" 向數據庫查詢數據時，查詢請求在應用程序側首先被翻譯成 TDS 協議包（TDS：Tabular Data Stream 即表格格式數據流協議），然后通過連接的通信協議信道發送至數據庫一端。

SQL Server 協議層接收到請求，并將請求轉換成關系引擎（Relational Engine）可以處理的形式。

關系引擎（Relational Engine）

關系引擎（Relational Engine）也稱為查詢處理器（Query Processor），主要包含 3 個部分：

• 命令解析器（Command Parser）
• 查詢優化器（Query Optimizer）
• 查詢執行器（Query Executor）

協議層將接收到的 TDS 消息解析回 T-SQL 語句，首先傳遞給命令解析器（Command Parser）。

命令解析器（Command Parser）檢查 T-SQL 語法的正確性，并將 T-SQL 語句轉換成可以進行操作的內部格式，即查詢樹（Query Tree）。

• 查詢樹（Query Tree）是結構化查詢語言 SQL（Structured Query Language）的內部表現形式。
• 數據操縱語言 DML（Data Manipulation Language）是 SQL 語言的子集，包括 INSERT, UPDATE, DELETE 三種核心指令。
• 數據定義語言 DDL（Data Definition Language）管理表和索引結構，包括 CREATE, DROP, ALTER, TRUNCATE 等命令。
• 數據控制語言 DCL（Data Control Language）負責授權用戶訪問和處理數據，包括 GRANT, REVOKE 等命名。
• T-SQL 即 Transact-SQL 則是在 SQL 基礎上擴展了過程化編程語言的功能，如流程控制等。
• SQLCLR（SQL Server Common Language Runtime）使用 .NET 程序集來擴展功能。

查詢優化器（Query Optimizer）從命令解析器處得到查詢樹（Query Tree），判斷查詢樹是否可被優化，然后將從許多可能的方式中確定一種最佳方式，對查詢樹進行優化。

• 無法優化的語句，包括控制流和 DDL 等，將被編譯成內部形式。
• 可優化的語句，例如 DML 等，將被做上標記等待優化。

優化步驟首先進行規范查詢（Normalize Query），可以將單個查詢分解成多個細粒度的查詢，并對細粒度的查詢進行優化，這意味著它將為執行查詢確定計劃，所以查詢優化器的結果是產生一個執行計劃（Execution Plan）。

查詢優化是基于成本的（Cost-based）考量的，也就是說，選擇成本效益最高的計劃。查詢優化器需要根據內部記錄的性能指標選擇消耗最少的計劃。這些內部性能指標包括：Memory 需求、CPU 利用率和 I/O 操作數量等。同時，查詢優化還使用啟發式算法（Pruning Heuristics），以確保評估優化及查詢的時間消耗不會比直接執行未優化查詢的時間更長。

在完成查詢的規范化和最優化之后，這些過程產生的結果將被編譯成執行計劃（Execution Plan）數據結構。執行計劃中包括查詢哪張表、使用哪個索引、檢查何種安全性以及哪些條件為何值等信息。

查詢執行器（Query Executor）運行查詢優化器（Query Optimizer）產生的執行計劃，在執行計劃中充當所有命令的調度程序，并跟蹤每個命令執行的過程。大多數命令需要與存儲引擎（Storage Engine）進行交互，以檢索或修改數據等。

存儲引擎（Storage Engine）

SQL Server 存儲引擎中包含負責訪問和管理數據的組件，主要包括：

• 訪問方法（Access Methods）
• 鎖管理器（Lock Manager）
• 事務服務（Transaction Services）
• 實用工具（Controlling Utilities）

訪問方法（Access Methods）包含創建、更新和查詢數據的具體操作，下面列出了一些訪問方法類型：

• 行和索引操作（Row and Index Operations）：負責操作和維護磁盤上的數據結構，也就是數據行和 B 樹索引。
• 頁分配操作（Page Allocation Operations）：每個數據庫都是 8KB 磁盤頁的集合，這些磁盤頁分布在多個物理文件中。SQL Server 使用 13 種磁盤頁面結構，包括數據頁面、索引頁面等。
• 版本操作（Versioning Operations）：用于維護行變化的版本，以支持快照隔離（Snapshot Isolation）功能等。

訪問方法并不直接檢索頁面，它向緩沖區管理器（Buffer Manager）發送請求，緩沖區管理器在其管理的緩存中掃描頁面，或者將頁面從磁盤讀取到緩存中。在掃描啟動時，會使用預測先行（Look-ahead Mechanism）機制對頁面中的行或索引進行驗證。

鎖管理器（Lock Manager）用于控制表、頁面、行和系統數據的鎖定，負責在多用戶環境下解決沖突問題，管理不同類型鎖的兼容性，解決死鎖問題，以及根據需要提升鎖（Escalate Locks）的功能。

事務服務（Transaction Services）用于提供事務的 ACID 屬性支持。ACID 屬性包括：

• 原子性（Atomicity）
• 一致性（Consistency）
• 隔離性（Isolation）
• 持久性（Durability）

預寫日志（Write-ahead Logging）功能確保在真正發生變化的數據頁寫入磁盤前，始終先在磁盤中寫入日志記錄，使得任務回滾成為可能。寫入事務日志是同步的，即 SQL Server 必須等它完成。但寫入數據頁可以是異步的，所以可以在緩存中組織需要寫入的數據頁進行批量寫入，以提高寫入性能。

SQL Server 支持兩種并發模型來保證事務的 ACID 屬性：

• 悲觀并發（Pessimistic Concurrency）假設沖突始終會發生，通過鎖定數據來確保正確性和并發性。
• 樂觀并發（Optimistic Concurrency）假設不會發生沖突，在碰到沖突再進行處理。

在樂觀并發模型中，用戶讀數據時不鎖定數據。在執行更新時，系統進行檢查，查看另一個用戶讀過數據后是否更改了數據。如果另一個用戶更改了數據，則產生一個錯誤，接收錯誤信息的用戶將回滾事務。該模型主要用在數據爭奪少的環境中，以及鎖定數據的成本超過回滾事務的成本時。

SQL Server 提供了 5 中隔離級別（Isolation Level），在處理多用戶并發時可以支持不同的并發模型。

• Read Uncommitted：僅支持悲觀并發；
• Repeatable Read：僅支持悲觀并發；
• Serializable：僅支持悲觀并發；
• Snapshot: 支持樂觀并發；
• Read Committed：默認隔離級別，依據配置既可支持悲觀并發也可支持樂觀并發。

實用工具（Controlling Utilities）中包含用于控制存儲引擎的工具，如批量加載（Bulk-load）、DBCC 命令、全文本索引管理（Full-text Index Management）、備份和還原命令等。

SQLOS

SQLOS 是一個單獨的應用層，位于 SQL Server 引擎的最低層。SQLOS 的主要功能包括：

• 調度（Scheduling）
• 內存管理（Memory Management）
• 同步（Synchronization）：提供 Spinlock, Mutex, ReaderWriterLock 等鎖機制。
• 內存代理（Memory Broker）：提供 Memory Distribution 而不是 Memory Allocation。
• 錯誤處理（Exception Handling）
• 死鎖檢測（Deadlock Detection）
• 擴展事件（Extended Events）
• 異步 I/O（Asynchronous IO）

數據庫體系結構對比

實際上，如果從體系結構的整體上來比較，各種常見的關系型數據庫的體系結構都是差不多的。這也使得我們在了解一種數據庫后，可以大體的猜測和快速理解另一種數據庫。

下面是 Oracle 數據庫的架構圖：

下面是 MySQL 數據庫的結構圖：

MySQL 數據庫在存儲引擎部分實現了可插拔式設計（Pluggable Storage Engines），可以根據需求不同選擇不同類型的存儲引擎實現。

甚至在同一個數據庫實例中，每張數據表都可以指定使用哪種存儲引擎。

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;

《人人都是 DBA》系列文章索引：

本系列文章《人人都是 DBA》由 Dennis Gao 發表自博客園個人技術博客，未經作者本人同意禁止任何形式的轉載，任何自動或人為的爬蟲轉載或抄襲行為均為耍流氓。