SQL Server 基础知识

目录

什么是 SQL Server？

安装

关键概念

系统数据库（System Databases）

登录名与数据库用户（Login and user）

身份验证模式（Authentication Mode）

包含数据库（Contained Database）

总体架构

协议层（Protocol Layer）

关系引擎（Relational Engine）

存储引擎（Storage Engine）

数据文件架构

数据文件

附录 - SQL Server 工具集

参考文档

什么是 SQL Server？

SQL Server 是由微软公司（Microsoft）开发的关系型数据库（RDBMS）。RDBMS 是 SQL 以及所有现代数据库系统的基础，比如 MS SQL Server，IBM DB2，Oracle，MySQL 以及微软的 Microsoft Access。

SQL Server 主要是和 MySQL 和 Oracle 二者竞争。

这里简答提一下 SQL 的历史：SQL 由 IBM 在 19 世纪 70 年代提出，在 1986 年成为 ANSI（American National Standards Insittute） 标准，在 1987 年成为 ISO（International Organization for Standardization） 标准。

SQL Server 支持 ANSI SQL 标准。但是 SQL Server 设计了自己的 SQL 语言，称之为 T-SQL（Transactional-SQL）。类似于 Oracle 的 PL/SQL。

Microsoft 和 Sybase** 在 1989 年发布 SQL Server 1.0 版本。在 19 世纪 90 年代二者合作关系结束之后微软保持了 SQL Server 的所有权。此后，微软陆续发布了 SQL Server 2000， 2005， 2008， 2012，2014， 2016， 2017， 以及 2019。从 SQL Server 2017 开始，SQL Server** 开始支持 Linux。在 2009 年微软宣布发行 Microsoft Azure SQL 数据库并在 2010 年正式发布。

安装

安装分为三个步骤：

1. 安装前 - 仔细阅读安装要求
2. 安装 - 选择一种安装方式安装 SQL Server
3. 安装后 - 使用 SQL Server 实用工具配置 SQL Server

安装前需要考虑安装的平台，比如是 Microsoft Server 的什么版本，还是Linux 平台；同时需要考虑磁盘空间，处理器（类型，速度），内存，以及存储系统，比如存储架构（NAS，DAS，SAN），磁盘类型（SCSI，SSD，...），RAID类型（RAID0，RAID1，...）。

还有重要的一点是需要选择合适的 Collation（有人把 Collation 翻译为排序规则其实是不严谨的，因为 Collation 除了影响排序之外还代表了字符在数据集里的呈现形式）。Collation 主要影响两个方面：

• 排序规则（Sorting rules）
• 文本数据的语义：字符大小写（Case Sensitivity），声调是否敏感（Accent Sensitivity。比如，法语里的 é 和 e）

比如下面这个例子，不同的 Collation 导致了完全不同的排序结果。

collation1.png collation2.png

SQL Server 支持在单一数据库中存储具有不同 Collation 的对象。

安装时有四种安装方式可以选择：

• 通过 SQL Server 安装向导安装 - 一步一步的可视化安装
• 通过 SQL Server 命令提示符安装 - 完全或简单静默安装。可以指定要安装的功能以及如何配置这些功能。 还可以指定与安装用户界面是进行静默交互、基本交互还是完全交互。
• 使用 SQL Server 配置文件安装 - 可以使用配置文件在整个企业中部署具有相同配置的 SQL Server 。
• 使用 SQL Server SysPrep 安装 - 在计算机上准备 SQL Server 的独立实例，以后再完成配置。

安装后验证安装是否成功。一般可以检查：

• SQL Server 的服务是否存在 - services 工具页面。如果没有特地设置实例名（instance name），默认为 SQL Server （MSSQLSERVER）。

• 相关安装文件夹是否存在 - 确认配置的默认文件和 temp 文件夹存在，且这些文件夹里包含所期望的文件，比如系统数据库的 .mdf，.ldf。

• 连接到 SQL Server 服务器验证 - 这是最简单的方法。只需要在 CMD 窗口输入 OSQL -E。如果连接成功，则会在窗口显示 1>。

  verifyinst.png

关键概念

系统数据库（System Databases）

SQL Server 有五个系统数据库：

• master - 记录 SQL Server实例的所有系统级信息。
• msdb - 用于 SQL Server 代理计划警报和作业。
• model - 用作 SQL Server实例上创建的所有数据库的模板。 对 model 数据库进行的修改（如数据库大小、排序规则、恢复模式和其他数据库选项）将应用于以后创建的所有数据库。
• resource - 一个只读数据库，包含 SQL Server包括的系统对象。 系统对象在物理上保留在 Resource 数据库中，但在逻辑上显示在每个数据库的 sys 架构中。
• tempdb - 一个工作空间，用于保存临时对象或中间结果集。注意，tempdb 会在 SQL Server 每次重启时重新创建。

这里只是简单介绍一下每个系统数据库的功能。如果需要了解详细信息，可以参考微软官方文档。

登录名与数据库用户（Login and user）

• 登录名 - 登录名是用于登录到 SQL Server 数据库引擎的单个用户帐户。 SQL Server 和 SQL 数据库 支持基于 Windows 身份验证的登录名和基于 SQL Server 身份验证的登录名。
• 数据库用户 -
  • 通过在数据库中创建数据库用户并将该数据库用户映射到登录名来授予登录名对数据库的访问权限。
  • 每个数据库用户均映射到单个登录名。
  • 也可以创建不具有相应登录名的数据库用户。 这些数据库用户称为“包含的数据库用户”

身份验证模式（Authentication Mode）

SQL Server 提供两种身份验证模式：

• Windows 验证模式（Windows Authentication mode）- 该模式下

  • 支持：Windows 身份验证
  • 不支持：SQL Server 身份验证

• 混合模式（Mixed mode）- 该模式下

  • 支持：Windows 身份验证
  • 支持：SQL Server 身份验证

  在 SSMS 里右击实例名称，选择属性（Properties），浏览到安全（Security）选项卡页面，可以修改身份认证模式。

包含数据库（Contained Database）

从 SQL Server 2012 开始 SQL Server 引进了包含数据库（Contained Database）。包含数据库独立于其他数据库和它所属的实例。

它的主要优势是：

• 许多描述数据库的元数据都在该数据库本身之中维护
• 允许数据库自己部分包含之前存储在数据库之外的数据。换句话说就是，一旦启用了包含数据库特性，就可以创建能够把用户信息（身份验证信息）存储在数据库自己内部的数据库而不是把这些信息存储在 master 数据库。

启用包含数据库特性必须在数据库的实例上进行，然后才可以创建包含数据库。

登录包含数据库时，需要在选项>>（Options>>）里指定要连接到的数据库，否则会出错。

contaieddb.png

总体架构

SQL Server 有三个主要组件：

• 协议层（Protocol Layer）
• 关系引擎（Relational Engine）
• 存储引擎（Storage Engine）

协议层（Protocol Layer）

• 共享内存（Shared Memory）- 客户端和 SQL Server 运行在同一台机器上时，可通过该协议通讯。

  • 使用 SSMS 连接到本地 SQL Server 时，SSMS 登录窗口中的服务器名称（Server Name）可以为：
    • . - 这里的点号 . 表示 SQL Server为本地安装
    • localhost - 顾名思义，指本地安装
    • 127.0.0.1 - 同上
    • <Machine>\<Instance> - 服务名称加上实例名称

• TCP/IP - 客户端和 SQL Server 不运行在同一台机器上时，或者可理解为处在网络环境时，可采取此种方式通讯。

  • 使用 SSMS 连接到远端时，SSMS 登录窗口中的服务器名称（Server Name）只能是：<Machine><Instance>

• 命名管道（Named Pipe）- 为局域网（LAN）开发的协议。默认是关闭的，需要通过 SQL 配置管理器（SQL Configuration Manager）开启。

架构图中的 TDS 是 Tabular Data Stream 的缩写。

• TDS 最初由 Sybase 开发，现在为微软所有。
• TDS 是包装的网络包。
• 三种协议均使用 TDS 包。

关系引擎（Relational Engine）

关系引擎（Relational Engine）又被称为查询处理器（Query Processor）。它包含能够决定执行什么样的查询以及如何做到最优查询。它负责从存储引擎（Storage Engine）请求数据并处理返回的数据。

• 命令解析器（CMD Parser）
  • 从协议层（Protocol Layer）传入的数据进入关系引擎。命名解析器首先接收到查询数据。它的主要职责是：
    • 语法检查(Syntatitic) -> 语义检查(Semantic) -> 创建查询树(Query Tree)
  • 解释：
    • 语法检查(Syntatitic) - SQL Server 预定义了很多关键字（SELECT, INSERT, UPDATE 等等）和语法。如果查询数据没有遵守这些规则，则会返回错误。
    • 语义检查(Semantic) - 由规范器（Normalizer）执行。比如，检查表名、列名是否存在于架构（Schema）里。如果存在，则将其绑定（Bind）到查询。
    • 创建查询树(Query Tree) - 产生查询在里面运行的不同的执行树。当然，所有的执行树均返回同样的预期结果。
• 优化器（Optimizer）
  • 优化器（Optimizer）为查询创建执行计划，该执行计划决定查询如何执行。
  • 并非所有的查询都会被优化。优化只针对 DML 命令，比如 SELECT，INSERT，DELETE 和 UPDATE。这些查询会首先被标记然后发送到优化器。DDL 命令，如 CREATE 和 ALTER 不会被优化，而是被编译成内部形式。
  • 查询成本（Query cost）的计算基于多种因素，如 处理器使用情况（CPU usage），内存使用情况（Memory usage）以及输入输出需求（Input/Output needs）。
  • 优化器（Optimizer）的目的是找到最省的执行计划，而不是最好的、最有效的执行计划。
  • 优化器（Optimizer）寻找成本最低的执行计划过程涵盖下面三个阶段：
    • 微计划（Trivial Plan）-> 事务执行计划（Transaction Processing Plan）-> 并行执行和优化（Parallel Processing and Optimization）
  • 解释
    • 微计划（Trivial Plan）- 又称为预优化阶段（Pre-optimization Stage）。有些情况下，可能只有一种方便可用的计划，这就是微计划。此时，无需创建优化计划。如果没有找到微计划，则进入下一阶段事务执行计划。
    • 事务执行计划（Transaction Processing Plan）- 包括简单计划（Simple Plan）和复杂计划（Complex Plan）的搜寻。在简单计划搜寻中，查询中涉及的索引和列将被用于数据分析（Statistical Analysis）；通常以一个表一个索引的方式执行。如果无法找到简单计划，则会进一步搜索复杂计划；此时会包含一个表的多个索引。
    • 并行执行和优化（Parallel Processing and Optimization）- 如果上述两个策略都没有奏效，优化器会搜寻并行执行的可能性。这取决于服务器的处理能力和配置。如果这也无法做到，优化器会尝试找到所有可能的执行计划，此称之为最终优化（Final Optimization）。最终优化的算法是微软的专利。
• 查询执行器（Query Executor）- 查询执行器调用通道方法（Access Method）。它为执行计划提供获取数据的逻辑。一旦从存储引擎获得数据，结果将发布到协议层（Protocol Layer）；最后，返回数据给用户。

存储引擎（Storage Engine）

存储引擎把数据存储在存储系统，如硬盘或 SAN，并在需要时返回数据。

• 通道方法（Access Method）- 为查询执行器和缓冲管理器以及事务管理器之间的接口。它判断一个查询是否是：

  • Select 语句 - 这种情况下，查询会被传递到缓冲管理器做进一步处理

  • 非 Select 语句 - 这种情况下，查询被被传递到事务管理器。通常是 UPDATE 语句。

    access method.png

• 缓冲管理器（Buffer Manager）

• 缓冲管理器管理如下三个模块功能：

  • 计划缓存（Plan Cache）
    • 已有查询计划（Existing Query Plan）- 缓冲管理器检查是否存在已存储的计划缓存，如果有，则使用该查询计划缓存及其关联的数据缓存。
    • 首次查询计划（First time Cache Plan）- 如果是查询执行计划首次执行，则将其存储到计划缓存。这样当下次执行同样的查询时可以更快的获取到执行计划。
  • 数据解析（Data Parsing）：缓冲缓存(Buffer Cache)和数据存储（Data Storage）
    • 缓冲缓存（Buffer Cache）- 软解析（Soft Parsing），缓冲管理器在缓冲区里寻找数据，如果有，则返回给查询执行器（Query Executor）。这有助于减少 I/O 操作。
    • 数据存储（Data Storage）- 硬解析（Hard Parsing），如果数据不在缓冲区，则数据将从数据存储搜索。数据同时会被缓存在数据缓存（Data Cache）里供将来使用。
  • 脏页（Dirty page）

• 事务管理器（Transaction Manager）- 通道方法（Access Method）判断查询是一个非 SELECT 语句时，事务管理器将被调用。

数据文件架构

每个 SQL Server 数据库至少具有两个操作系统文件：一个数据文件和一个日志文件。

数据文件

• 数据文件（Data Files）

  • 包含数据和对象，比如表，索引，存储索引和视图
  • 可以组织在文件组（Filegroup）里

• 日志文件（Log files）

  • 日志文件包含恢复数据库中的所有事务所需的信息。
  • 每个数据库至少有一个日志文件。
  • 日志文件是可复写文件（wrap-around file）。

• 数据库文件类型

  数据库文件类型主要有三种

  • 主要 （Primary）- 主要数据文件包含数据库的启动信息，并指向数据库中的其他文件。每个数据库有一个主要数据库文件。建议文件后缀为 .mdf。
  • 辅助副本（Secondary） - 可选，由用户定义。建议文件后缀为 .ndf 。
  • 事务日志 （Transaction Log）- 保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。建议文件后缀为 .ldf。

• 文件组（Filegroups）

  • 每个数据库有一个主要文件组。
  • 此文件组包含主要数据文件和未放入其他文件组的所有次要文件。
  • 文件组类型有：主文件组，内存优化文件组，Filestream，用户定义的文件组

• 页和区（Pages & Extents）

  • 页（Pages）
    • 页的大小为 8KB。
    • SQL Server 中数据存储的基本单位是页。
    • 磁盘 I/O 操作在页级执行。 也就是说，SQL Server 读取或写入所有数据页。
    • 页的类型有：数据（Data），索引（Index）等等。
  • 区 (Extents)
    • 区是管理空间的基本单位。
    • 一个区是 8 个物理上连续的页（即 64 KB）
    • 区有两种类型：混合区和统一区（从 SQL Server 2016 (13.x) 开始，数据库中所有分配的默认值都是统一区）

附录 - SQL Server 工具集

• SQL Server Management Studio - SQL Server 主要客户端
• SQL Server Configuration Manager - 所有 SQL Server 服务的集中配置工具
• Performance tools - 开始>所有程序>Microsoft SQL
  Server xxxx>性能工具
  • SQL Server Profiler
  • DatabaseEngine Tuning Advisor
• SQL Server Business Intelligent Development Studio - Microsfot Visual Studio 的插件集
• 第三方工具
  • –Embarcadero Technologies - ER/Studio
  • Computer Associates - ERWin
  • Red Gate Software - SQL Compare and SQL Data Compare Utilities
  • Quest Software - TOAD

参考文档

1. https://www.w3schools.com/sql/sql_intro.asp
2. https://en.wikipedia.org/wiki/Sybase
3. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-overview?view=sql-server-2017
4. https://en.wikipedia.org/wiki/Microsoft_Azure_SQL_Database
5. https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017#Collation_Defn
6. https://docs.microsoft.com/zh-cn/sql/relational-databases/security/authentication-access/getting-started-with-database-engine-permissions?view=sql-server-2017
7. https://docs.microsoft.com/zh-cn/sql/relational-databases/security/choose-an-authentication-mode?view=sql-server-2017
8. https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/migrate-to-a-partially-contained-database?view=sql-server-2017#enable
9. https://www.guru99.com/sql-server-introduction.html
10. https://docs.microsoft.com/zh-cn/sql/database-engine/configure-windows/buffer-pool-extension?view=sql-server-2017
11. https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017
12. https://docs.microsoft.com/zh-cn/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-2017