SQLServer 核心概念

基本概念

实例 (Instance)

定义：SQLServer 实例是 SQLServer 数据库引擎的一个运行实例，包含一组系统数据库和用户数据库。

特点：

• 每个实例有独立的配置、内存空间和进程
• 可以在同一台服务器上运行多个实例
• 每个实例有独立的端口号（默认 1433）
• 实例名格式：计算机名\实例名（默认实例为 计算机名）

类型：

• 默认实例：在一台服务器上只能有一个默认实例
• 命名实例：在一台服务器上可以有多个命名实例

数据库 (Database)

定义：数据库是存储和管理数据的容器，包含表、视图、存储过程等数据库对象。

类型：

• 系统数据库：SQLServer 自带的数据库，用于管理 SQLServer 本身

  • master：存储系统级信息，如实例配置、数据库信息、登录账户等
  • model：所有新数据库的模板
  • msdb：用于 SQL Server Agent 作业调度、备份和恢复等
  • tempdb：临时数据库，用于存储临时表、临时结果集等
  • resource：只读数据库，包含系统对象

• 用户数据库：用户创建的数据库，用于存储应用程序数据

数据库文件：

• 数据文件 (.mdf)：主要数据文件，包含数据库的系统表和用户表
• 次要数据文件 (.ndf)：可选的数据文件，用于分散数据存储
• 事务日志文件 (.ldf)：包含事务日志记录，用于恢复数据库

文件组 (Filegroup)

定义：文件组是数据库文件的逻辑分组，用于管理和组织数据库文件。

类型：

• 主文件组：包含主要数据文件和未指定文件组的次要数据文件
• 用户定义文件组：用户创建的文件组
• 事务日志文件组：事务日志文件不属于任何文件组

用途：

• 提高 I/O 性能：将不同文件组放在不同磁盘上
• 便于管理：按功能或应用模块组织数据
• 支持分区表：将分区存储在不同文件组中

数据库对象

表 (Table)

定义：表是数据库中存储数据的基本单位，由行和列组成。

组成：

• 列：表中的字段，包含数据类型和约束
• 行：表中的记录，包含一条完整的数据
• 约束：确保数据完整性的规则，如主键约束、外键约束、唯一约束、检查约束等

类型：

• 堆表：没有聚集索引的表
• 聚集表：有聚集索引的表
• 内存优化表：存储在内存中的表，用于高性能应用

视图 (View)

定义：视图是基于表或其他视图的虚拟表，不存储实际数据，只存储查询定义。

类型：

• 标准视图：基于表或其他视图的查询
• 索引视图：创建了索引的视图，存储预计算结果
• 分区视图：将多个表的数据组合成一个视图

用途：

• 简化复杂查询
• 限制数据访问：只显示用户需要的数据
• 提供数据安全性：隐藏敏感数据
• 提高查询性能：索引视图可以提高查询性能

索引 (Index)

定义：索引是提高查询性能的数据结构，通过创建指向表中数据的指针来加速数据检索。

类型：

• 聚集索引：决定表中数据的物理存储顺序，每个表只能有一个聚集索引
• 非聚集索引：创建独立的数据结构，包含索引列和指向表中数据的指针，每个表可以有多个非聚集索引
• 唯一索引：确保索引列的值唯一
• 复合索引：使用多个列创建的索引
• 包含列索引：在索引中包含非索引列，减少回表查询
• 筛选索引：只包含满足特定条件的数据
• 全文索引：用于全文搜索
• 空间索引：用于空间数据查询
• Columnstore 索引：针对数据仓库和分析场景的列存储索引

存储过程 (Stored Procedure)

定义：存储过程是预编译的 T-SQL 语句集合，可以接受参数并返回结果。

类型：

• 系统存储过程：SQLServer 自带的存储过程，以 sp_ 或 xp_ 开头
• 用户定义存储过程：用户创建的存储过程
• 扩展存储过程：使用外部编程语言（如 C++）编写的存储过程

优点：

• 提高性能：预编译，减少网络流量
• 增强安全性：可以限制直接访问表
• 提高代码复用性：可以被多个应用程序调用
• 便于维护：集中管理和更新

函数 (Function)

定义：函数是返回值的预编译 T-SQL 语句集合。

类型：

• 标量函数：返回单个值
• 表值函数：返回表
  • 内联表值函数：返回一个表，类似于视图
  • 多语句表值函数：返回一个表，包含多个语句
• 系统函数：SQLServer 自带的函数

限制：

• 不能修改数据库状态
• 不能使用临时表（某些函数除外）
• 必须返回值

触发器 (Trigger)

定义：触发器是在表或视图上执行特定操作（如 INSERT、UPDATE、DELETE）时自动执行的存储过程。

类型：

• DML 触发器：在数据操作语言（DML）事件上触发
  • AFTER 触发器：在 DML 操作完成后触发
  • INSTEAD OF 触发器：替代 DML 操作执行
• DDL 触发器：在数据定义语言（DDL）事件上触发
• 登录触发器：在用户登录时触发

用途：

• 维护数据完整性
• 审计数据更改
• 实现复杂的业务规则
• 同步数据

约束 (Constraint)

定义：约束是确保数据完整性的规则。

类型：

• 主键约束 (PRIMARY KEY)：唯一标识表中的行，不允许 NULL 值
• 外键约束 (FOREIGN KEY)：确保引用完整性，关联两个表
• 唯一约束 (UNIQUE)：确保列值唯一，允许 NULL 值
• 检查约束 (CHECK)：确保列值满足特定条件
• 默认约束 (DEFAULT)：为列提供默认值
• 非空约束 (NOT NULL)：确保列值不为 NULL

事务与并发

事务 (Transaction)

定义：事务是一组原子性的 SQL 语句，要么全部执行成功，要么全部失败。

ACID 特性：

• 原子性 (Atomicity)：事务中的所有操作要么全部成功，要么全部失败
• 一致性 (Consistency)：事务执行前后，数据库状态保持一致
• 隔离性 (Isolation)：多个事务并发执行时，彼此之间互不影响
• 持久性 (Durability)：事务提交后，数据更改永久保存

事务控制语句：

• BEGIN TRANSACTION：开始事务
• COMMIT TRANSACTION：提交事务
• ROLLBACK TRANSACTION：回滚事务
• SAVE TRANSACTION：创建保存点

隔离级别 (Isolation Level)

定义：隔离级别决定了事务之间的隔离程度，影响并发性能和数据一致性。

SQLServer 支持的隔离级别：

• READ UNCOMMITTED：允许读取未提交的数据（脏读）
• READ COMMITTED：只能读取已提交的数据（默认隔离级别）
• REPEATABLE READ：确保在事务期间多次读取同一行时，结果一致
• SNAPSHOT：使用行版本控制，读取事务开始时的数据版本
• SERIALIZABLE：最高隔离级别，确保事务串行执行

锁 (Lock)

定义：锁是 SQLServer 用于控制并发访问的机制，防止多个事务同时修改同一数据导致数据不一致。

类型：

• 共享锁 (S)：用于读取操作，允许多个事务同时读取同一数据
• 排他锁 (X)：用于修改操作，防止其他事务读取或修改同一数据
• 更新锁 (U)：用于更新操作的初始阶段，防止死锁
• 意向锁：用于表示事务对某个资源的锁定意图
• 架构锁：用于保护数据库架构不被修改
• 批量更新锁：用于批量操作

粒度：

• 行级锁：锁定单行数据
• 页级锁：锁定数据页
• 表级锁：锁定整个表
• 数据库级锁：锁定整个数据库

死锁 (Deadlock)

定义：死锁是指两个或多个事务互相等待对方释放锁，导致所有事务无法继续执行。

预防措施：

• 按相同顺序访问资源
• 避免长时间持有锁
• 使用较低的隔离级别
• 使用索引减少锁的范围
• 启用死锁检测

性能相关概念

执行计划 (Execution Plan)

定义：执行计划是 SQLServer 执行查询的步骤和方法，包括如何访问表、如何使用索引、如何连接表等。

类型：

• 预估执行计划：SQLServer 基于统计信息生成的执行计划，不实际执行查询
• 实际执行计划：SQLServer 实际执行查询时生成的执行计划，包含实际的执行统计信息

运算符：

• 扫描运算符：全表扫描、索引扫描
• 查找运算符：索引查找、键查找
• 连接运算符：嵌套循环、哈希连接、合并连接
• 聚合运算符：流聚合、哈希聚合
• 排序运算符：排序

查询存储 (Query Store)

定义：查询存储是 SQLServer 2016 引入的功能，用于捕获和分析查询执行计划和性能统计信息。

功能：

• 自动捕获查询执行计划
• 跟踪查询性能变化
• 强制使用特定执行计划
• 分析查询性能退化

统计信息 (Statistics)

定义：统计信息是 SQLServer 用于优化查询执行计划的元数据，包含表或索引中数据的分布信息。

类型：

• 列统计信息：关于单个列数据分布的统计信息
• 索引统计信息：关于索引键列数据分布的统计信息

自动更新统计信息：

• SQLServer 会根据数据变化自动更新统计信息
• 可以手动更新统计信息：UPDATE STATISTICS table_name

安全相关概念

登录账户 (Login)

定义：登录账户是用于连接到 SQLServer 实例的账户。

类型：

• Windows 登录：使用 Windows 身份验证
• SQL Server 登录：使用 SQL Server 身份验证
• Azure AD 登录：使用 Azure Active Directory 身份验证

数据库用户 (User)

定义：数据库用户是登录账户在特定数据库中的映射，用于访问数据库对象。

类型：

• Windows 用户：映射到 Windows 登录
• SQL Server 用户：映射到 SQL Server 登录
• Azure AD 用户：映射到 Azure AD 登录
• 应用程序角色：用于应用程序访问数据库

角色 (Role)

定义：角色是一组权限的集合，用于简化权限管理。

类型：

• 服务器角色：作用于服务器级别，如 sysadmin、serveradmin 等
• 数据库角色：作用于数据库级别，如 db_owner、db_datareader、db_datawriter 等
• 应用程序角色：用于应用程序访问数据库
• 用户定义角色：用户创建的角色

权限 (Permission)

定义：权限是允许用户或角色执行特定操作的权限。

类型：

• 服务器权限：服务器级别的权限，如 CREATE DATABASE、ALTER ANY LOGIN 等
• 数据库权限：数据库级别的权限，如 CREATE TABLE、SELECT、INSERT 等
• 对象权限：针对特定对象的权限，如 SELECT ON table_name、EXECUTE ON procedure_name 等

权限控制语句：

• GRANT：授予权限
• DENY：拒绝权限
• REVOKE：撤销权限

常见问题 (FAQ)

Q: 实例和数据库有什么区别？

A: 实例是 SQLServer 数据库引擎的运行实例，包含一组系统数据库和用户数据库；数据库是存储和管理数据的容器，包含表、视图、存储过程等数据库对象。一个实例可以包含多个数据库，一个数据库属于一个实例。

Q: 聚集索引和非聚集索引有什么区别？

A: 聚集索引决定表中数据的物理存储顺序，每个表只能有一个聚集索引；非聚集索引创建独立的数据结构，包含索引列和指向表中数据的指针，每个表可以有多个非聚集索引。

Q: 什么是事务的 ACID 特性？

A: ACID 是事务的四个特性：

• 原子性 (Atomicity)：事务中的所有操作要么全部成功，要么全部失败
• 一致性 (Consistency)：事务执行前后，数据库状态保持一致
• 隔离性 (Isolation)：多个事务并发执行时，彼此之间互不影响
• 持久性 (Durability)：事务提交后，数据更改永久保存

Q: 什么是死锁？如何预防死锁？

A: 死锁是指两个或多个事务互相等待对方释放锁，导致所有事务无法继续执行。预防死锁的措施包括：

• 按相同顺序访问资源
• 避免长时间持有锁
• 使用较低的隔离级别
• 使用索引减少锁的范围
• 启用死锁检测

Q: 什么是执行计划？如何查看执行计划？

A: 执行计划是 SQLServer 执行查询的步骤和方法。可以使用以下方法查看执行计划：

• 在 SSMS 中，点击 "显示预估执行计划" 或 "包括实际执行计划"
• 使用 SET SHOWPLAN_XML ON 命令查看预估执行计划
• 使用 SET STATISTICS XML ON 命令查看实际执行计划

Q: 什么是统计信息？为什么需要更新统计信息？

A: 统计信息是 SQLServer 用于优化查询执行计划的元数据，包含表或索引中数据的分布信息。更新统计信息可以确保 SQLServer 生成准确的执行计划，提高查询性能。

总结

SQLServer 核心概念包括实例、数据库、表、索引、事务、锁等，这些概念是理解和使用 SQLServer 的基础。掌握这些核心概念有助于设计合理的数据库架构、优化查询性能、确保数据安全和高可用性。在开发和管理 SQLServer 数据库时，需要深入理解这些概念，并根据实际需求灵活应用。