印刻数据库

外观

SQLServer 逻辑架构 ​

SQL Server 逻辑架构描述了数据库引擎的组件和它们之间的交互关系，帮助 DBA 理解 SQL Server 如何处理用户请求、管理数据和保证系统可靠性。本文将详细介绍 SQL Server 的逻辑架构，包括核心组件、查询处理流程和关键服务。

核心逻辑组件 ​

SQL Server 逻辑架构主要由以下核心组件组成：

1. 数据库引擎 (Database Engine) ​

数据库引擎是 SQL Server 的核心组件，负责处理数据存储、检索和管理。它包含以下主要子组件：

1.1 关系引擎 (Relational Engine) ​

关系引擎也称为查询处理器，负责处理 SQL 查询的解析、优化和执行计划生成。

主要子组件：

查询解析器 (Query Parser) ​
  • 将 T-SQL 语句解析为抽象语法树 (AST)
  • 检查 SQL 语法错误
  • 验证对象名称和权限
查询优化器 (Query Optimizer) ​
  • 分析查询的各种执行计划
  • 基于成本模型选择最优执行计划
  • 考虑索引、统计信息和数据分布
  • 支持多种连接算法和操作符
查询执行器 (Query Executor) ​
  • 执行查询优化器生成的执行计划
  • 与存储引擎交互获取和修改数据
  • 管理事务和并发控制

1.2 存储引擎 (Storage Engine) ​

存储引擎负责物理数据的存储和检索，管理数据文件、索引和事务日志。

主要子组件：

访问方法 (Access Methods) ​
  • 处理数据的检索和修改请求
  • 管理索引访问和表扫描
  • 实现锁和并发控制
缓冲区管理器 (Buffer Manager) ​
  • 管理内存中的数据页缓存
  • 实现检查点机制
  • 管理脏页写入和预读
  • 优化 I/O 性能
事务管理器 (Transaction Manager) ​
  • 管理事务的开始、提交和回滚
  • 实现 ACID 事务特性
  • 管理事务日志
  • 实现锁定和隔离级别

2. SQLOS (SQL Operating System) ​

SQLOS 是 SQL Server 内置的操作系统，提供了以下核心服务：

  • 内存管理：管理 SQL Server 内存分配
  • 调度器：管理线程调度和并行查询执行
  • 同步原语：提供锁、信号量和事件等同步机制
  • 死锁检测：自动检测和解决死锁
  • 错误处理：管理异常和错误报告

3. 数据文件与文件组 ​

SQL Server 数据库由以下类型的文件组成：

  • 主要数据文件 (.mdf)：包含数据库的系统表和元数据
  • 次要数据文件 (.ndf)：可选，用于分散数据存储
  • 事务日志文件 (.ldf)：记录所有数据修改操作

文件组：

  • 主要文件组 (PRIMARY)：包含主要数据文件
  • 用户定义文件组：用于组织数据文件，优化 I/O 性能
  • 文件流文件组：用于存储 FILESTREAM 数据

4. 数据库对象 ​

SQL Server 数据库包含多种对象，用于组织和管理数据：

  • 表：存储数据的基本单位
  • 视图：虚拟表，基于一个或多个表的查询结果
  • 索引：提高数据检索性能
  • 存储过程：预编译的 T-SQL 代码块
  • 函数：返回值的预编译代码块
  • 触发器：响应数据修改事件的代码
  • 约束：保证数据完整性
  • 同义词：数据库对象的别名
  • 序列：生成连续数字值

查询处理流程 ​

SQL Server 处理查询的完整流程如下：

1. 客户端连接 ​

  • 客户端通过网络协议连接到 SQL Server
  • SQL Server 身份验证（Windows 或 SQL Server 身份验证）
  • 建立会话和执行上下文

2. 查询提交 ​

  • 客户端发送 T-SQL 查询到 SQL Server
  • 查询被发送到关系引擎

3. 查询解析 ​

  • 查询解析器将 T-SQL 解析为抽象语法树 (AST)
  • 检查语法错误和对象权限
  • 生成查询树

4. 查询优化 ​

  • 查询优化器分析查询树
  • 生成多个可能的执行计划
  • 基于成本模型评估每个计划
  • 选择最优执行计划

5. 执行计划生成 ​

  • 生成可执行的查询计划
  • 计划可以缓存供后续使用
  • 支持参数化查询和计划重用

6. 查询执行 ​

  • 查询执行器执行执行计划
  • 向存储引擎请求数据
  • 执行数据检索和修改操作
  • 管理事务和锁

7. 结果返回 ​

  • 查询结果被返回给客户端
  • 关闭游标和释放资源
  • 提交或回滚事务

事务处理机制 ​

SQL Server 实现了完整的 ACID 事务特性：

1. 原子性 (Atomicity) ​

  • 事务要么全部完成，要么全部回滚
  • 使用预写事务日志保证原子性
  • 支持显式和隐式事务

2. 一致性 (Consistency) ​

  • 事务执行前后数据库保持一致状态
  • 支持约束和触发器
  • 实现事务隔离级别

3. 隔离性 (Isolation) ​

  • 多个事务并发执行时相互隔离
  • 支持五种隔离级别：
    • 未提交读 (Read Uncommitted)
    • 已提交读 (Read Committed)
    • 可重复读 (Repeatable Read)
    • 快照隔离 (Snapshot)
    • 序列化 (Serializable)
  • 实现多版本并发控制 (MVCC)

4. 持久性 (Durability) ​

  • 事务提交后数据永久保存
  • 使用事务日志保证持久性
  • 实现检查点机制，定期将脏页写入磁盘

并发控制 ​

SQL Server 使用多种机制实现并发控制：

1. 锁定机制 ​

  • 共享锁 (S)：用于读取操作
  • 排他锁 (X)：用于修改操作
  • 更新锁 (U)：用于更新操作的准备阶段
  • 意向锁 (IS, IX, IU, SIU, SIX, UIX)：用于层级锁定
  • 架构锁 (Sch-M, Sch-S)：用于保护架构修改

2. 锁升级 ​

  • 将多个细粒度锁升级为粗粒度锁
  • 减少锁管理开销
  • 避免锁膨胀

3. 死锁管理 ​

  • 自动检测死锁情况
  • 选择牺牲者事务进行回滚
  • 提供死锁图和诊断信息

4. 乐观并发控制 ​

  • 基于行版本控制
  • 允许读取操作不阻塞写入操作
  • 写入操作不阻塞读取操作
  • 实现快照隔离级别

内存管理 ​

SQL Server 内存管理由 SQLOS 和缓冲区管理器共同实现：

1. 内存架构 ​

  • 缓冲池：存储数据页和索引页
  • 计划缓存：存储查询执行计划
  • 数据字典缓存：存储系统表和元数据
  • CLR 内存：用于 CLR 集成
  • 线程栈：用于线程执行
  • 连接内存：用于客户端连接

2. 内存分配策略 ​

  • 动态内存分配：根据系统负载调整内存使用
  • 最大和最小服务器内存设置
  • 内存授权：为查询分配内存
  • 内存压力检测和释放

3. 缓冲区管理 ​

  • 页面替换算法：LRU-K 算法
  • 脏页管理：定期写入磁盘
  • 预读机制：提前加载数据页
  • 检查点：将脏页写入磁盘，减少恢复时间

日志管理 ​

SQL Server 事务日志是保证数据完整性和可恢复性的关键组件：

1. 事务日志架构 ​

  • 预写日志 (WAL)：先写日志，后写数据
  • 日志记录类型：插入、更新、删除、提交、回滚等
  • 日志序列号 (LSN)：唯一标识日志记录

2. 日志写入机制 ​

  • 事务日志写入是同步的
  • 日志缓冲区刷新到磁盘
  • 支持批量写入优化

3. 恢复模式 ​

  • 简单恢复模式：自动截断事务日志，不支持 point-in-time 恢复
  • 完整恢复模式：保留所有事务日志，支持 point-in-time 恢复
  • 大容量日志恢复模式：批量操作使用最小日志记录，提高性能

版本差异 ​

SQL Server 不同版本在逻辑架构上的主要差异：

1. SQL Server 2012 及之前 ​

  • 传统查询优化器
  • 基本的行版本控制
  • 有限的内存中处理能力

2. SQL Server 2014 ​

  • 引入内存中 OLTP (Hekaton)
  • 增强的 Columnstore 索引
  • 改进的查询优化器

3. SQL Server 2016 ​

  • 增强的内存中 OLTP
  • 改进的 Query Store
  • 动态数据掩码和行级安全
  • 原生 JSON 支持

4. SQL Server 2017 ​

  • 跨平台支持
  • Python 集成
  • 自动数据库优化
  • 增强的图形数据库功能

5. SQL Server 2019 ​

  • Big Data Clusters
  • 增强的 PolyBase
  • 智能查询处理
  • 加速数据库恢复
  • 持久化内存支持

6. SQL Server 2022 ​

  • Azure Synapse Link 集成
  • 增强的 Always On 可用性组
  • 改进的智能查询处理
  • 支持 Azure Arc

性能监控与优化 ​

1. 关键性能指标 ​

  • 缓冲区命中率：衡量内存使用效率
  • 锁等待时间：衡量并发性能
  • 页面 life expectancy：衡量内存压力
  • 批处理请求数/秒：衡量系统吞吐量
  • 等待统计信息：识别性能瓶颈

2. 监控工具 ​

  • 动态管理视图 (DMVs)：sys.dm_exec_query_stats, sys.dm_os_wait_stats, sys.dm_db_index_physical_stats
  • Query Store：捕获查询计划和性能统计信息
  • Extended Events：低开销事件跟踪
  • Performance Monitor：系统级性能监控

3. 优化建议 ​

  • 合理设计索引，避免过度索引
  • 优化查询语句，避免复杂 JOIN 和子查询
  • 调整内存配置，确保足够的缓冲池大小
  • 优化事务设计，避免长事务
  • 定期更新统计信息
  • 监控和解决阻塞问题

常见问题 (FAQ) ​

Q: SQL Server 逻辑架构与物理架构有什么区别？ ​

A: 逻辑架构描述了 SQL Server 的组件和它们之间的逻辑关系，包括查询处理、事务管理和内存管理等；物理架构描述了数据在磁盘上的存储方式，包括数据文件、日志文件和文件组等。

Q: 查询优化器如何选择执行计划？ ​

A: 查询优化器基于成本模型选择执行计划，考虑以下因素：

  • 表和索引的统计信息
  • 数据分布和基数估计
  • 可用索引和索引选择性
  • 硬件资源和系统负载
  • 查询复杂度和操作符类型

Q: 什么是死锁，如何避免？ ​

A: 死锁是指两个或多个事务相互等待对方持有的资源，导致无法继续执行的情况。避免死锁的方法包括：

  • 保持事务简短，减少持有锁的时间
  • 按相同顺序访问资源
  • 使用较低的隔离级别
  • 避免在事务中使用用户交互
  • 使用快照隔离级别

Q: 缓冲区管理器的主要作用是什么？ ​

A: 缓冲区管理器的主要作用是：

  • 管理内存中的数据页缓存
  • 减少磁盘 I/O 操作
  • 实现检查点机制
  • 管理脏页写入和预读
  • 优化 I/O 性能

Q: SQL Server 如何保证事务的持久性？ ​

A: SQL Server 使用预写事务日志保证事务的持久性：

  • 事务提交前，所有修改操作的日志记录必须写入磁盘
  • 数据页可以异步写入磁盘
  • 检查点机制定期将脏页写入磁盘
  • 事务日志包含足够的信息用于恢复数据

Q: 什么是锁升级，为什么需要锁升级？ ​

A: 锁升级是将多个细粒度锁（如行锁）升级为粗粒度锁（如页锁或表锁）的过程。锁升级的目的是：

  • 减少锁管理的内存开销
  • 减少锁冲突的可能性
  • 避免锁膨胀导致的性能问题

总结 ​

SQL Server 逻辑架构是一个复杂而完善的系统，包含了关系引擎、存储引擎、SQLOS 等核心组件。了解 SQL Server 逻辑架构对于 DBA 来说至关重要，它有助于：

  • 理解查询处理流程，优化 SQL 查询
  • 设计合理的数据库架构和索引
  • 配置和优化内存、I/O 和并发设置
  • 诊断和解决性能问题
  • 确保数据库的高可用性和可靠性

通过深入理解 SQL Server 逻辑架构，DBA 可以更好地管理和优化数据库系统，确保业务应用的高效运行。随着 SQL Server 版本的不断更新，逻辑架构也在不断演进，引入了内存中 OLTP、智能查询处理等新特性，DBA 需要持续学习和适应这些变化，以充分利用新功能提升系统性能和可靠性。