人人都是 DBA（I）SQL Server 體繫結構

標籤：style   blog   http   io   ar   color   os   使用   sp   

在瞭解 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) SQL（Structured Query Language）的內部表現形式。
• 資料操縱語言 DML（Data Manipulation Language）是 SQL 語言的子集，包括 INSERT, UPDATE, DELETE 三種核心指令。
• 資料定義語言 (Data Definition Language) 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），可以根據需求不同選擇不同類型的儲存引擎實現。

Feature	InnoDB	MyISAM	Memory
Storage Limits	64TB	256TB	RAM
Transactions	Yes	No	No
Locking Granularity	Row	Table	Table
B-Tree Indexes	Yes	Yes	Yes
Compressed Data	Yes	Yes	No
Encrypted Data	Yes	Yes	Yes
Full-Text Search Indexes	Yes	Yes	No
Foreign Key Support	Yes	No	No

甚至在同一個資料庫執行個體中，每張資料表都可以指定使用哪種儲存引擎。

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

 

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

人人都是 DBA（I）SQL Server 體繫結構