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、指向下一页和上一页的指针等。

日志文件不包含页，而是包含一系列日志记录。默认情况下，数据和事务日志放在单磁盘系统的同一驱动器和路径上。 对于生产环境， 建议将数据和日志文件放在不同的磁盘上。