SQLServer 数据库与表

数据库与表是 SQL Server 中存储数据的基本单位，合理的数据库和表设计对于确保数据完整性、提高查询性能和简化维护工作至关重要。本文将详细介绍 SQL Server 数据库与表的设计原则、最佳实践和常见问题，帮助 DBA 设计高效、可靠的数据库架构。

数据库设计

1. 数据库类型

SQL Server 支持多种数据库类型，以满足不同的业务需求：

1.1 用户数据库

• 用于存储用户业务数据的数据库
• 每个实例可以创建多个用户数据库
• 支持自定义表、索引和存储过程
• 适合存储业务应用数据

1.2 系统数据库

SQL Server 内置的系统数据库，用于管理和支持数据库引擎：

master 数据库

• 存储 SQL Server 实例的元数据
• 包含系统表、登录信息和实例配置
• 数据库引擎启动时必须可用

tempdb 数据库

• 用于存储临时对象，如临时表和表变量
• 每次 SQL Server 重启时自动重建
• 所有数据库共享同一个 tempdb
• 性能对系统整体性能影响较大

model 数据库

• 新数据库的模板
• 新创建的数据库继承 model 数据库的配置和对象
• 用于设置所有新数据库的默认配置

msdb 数据库

• 用于 SQL Server Agent 存储作业、警报和操作员信息
• 存储备份和还原历史记录
• 存储 Database Mail 配置

resource 数据库

• 只读数据库，包含 SQL Server 的系统对象
• 不直接可见，通过系统视图访问
• 包含系统存储过程、视图和函数

2. 数据库创建与配置

2.1 数据库创建

创建数据库时需要考虑以下因素：

• 数据库名称：使用有意义的名称，符合命名规范
• 文件位置：将数据文件和日志文件放在不同的物理磁盘上
• 初始大小：根据预期数据量设置合适的初始大小，避免频繁自动增长
• 增长增量：设置合理的自动增长增量，避免过度碎片化
• 文件组：考虑使用多个文件组，分离不同类型的数据
• 恢复模式：根据业务需求选择合适的恢复模式（完整、简单、大容量日志）

2.2 数据库配置选项

重要的数据库配置选项包括：

• AUTO_CREATE_STATISTICS：自动创建列统计信息，提高查询优化器性能
• AUTO_UPDATE_STATISTICS：自动更新过期的统计信息
• AUTO_SHRINK：自动收缩数据库文件（不推荐启用，可能导致性能问题）
• AUTO_CLOSE：数据库空闲时自动关闭（不推荐启用，可能导致性能问题）
• PAGE_VERIFY：页面完整性检查，推荐使用 CHECKSUM
• READ_COMMITTED_SNAPSHOT：启用已提交读快照隔离级别，减少锁竞争

3. 数据库文件管理

3.1 数据文件

• 主要数据文件：包含数据库的系统表和元数据，扩展名为 .mdf
• 次要数据文件：存储用户数据，扩展名为 .ndf
• 文件组：用于组织数据文件，优化 I/O 性能
• 文件大小管理：监控文件增长，避免过度碎片化

3.2 事务日志文件

• 记录所有数据修改操作，用于恢复和回滚事务
• 扩展名为 .ldf
• 推荐放在单独的物理磁盘上
• 大小管理：避免频繁自动增长，设置合适的初始大小和增长增量

表设计

1. 表结构设计原则

1.1 规范化设计

• 第一范式 (1NF)：确保每列都是原子的，不可再分
• 第二范式 (2NF)：满足 1NF，且所有非主键列完全依赖于主键
• 第三范式 (3NF)：满足 2NF，且所有非主键列不传递依赖于主键
• BCNF (Boyce-Codd 范式)：比 3NF 更严格，消除所有非平凡函数依赖

1.2 反规范化设计

在某些情况下，为了提高查询性能，可以考虑反规范化设计：

• 添加冗余列：减少 JOIN 操作
• 创建汇总表：提前计算汇总数据
• 使用 denormalized views：创建预计算视图
• 考虑使用 Columnstore 索引：适合数据仓库场景

1.3 表命名规范

• 使用有意义的表名，避免缩写
• 使用一致的命名约定（如 PascalCase 或 snake_case）
• 表名应反映表的内容或用途
• 避免使用 SQL Server 保留字

2. 数据类型选择

选择合适的数据类型对于提高性能和确保数据完整性至关重要：

2.1 数值类型

数据类型	存储大小	范围	适用场景
tinyint	1 字节	0 到 255	小范围整数，如状态码、标志位
smallint	2 字节	-32,768 到 32,767	中等范围整数
int	4 字节	-2,147,483,648 到 2,147,483,647	常用整数类型，适合大多数整数场景
bigint	8 字节	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807	大范围整数，适合 ID 或计数器
decimal/numeric	5-17 字节	可变	精确数值，适合财务数据
float	4 或 8 字节	可变	近似数值，适合科学计算
real	4 字节	-3.40E+38 到 3.40E+38	近似数值，适合科学计算

2.2 字符类型

数据类型	存储大小	适用场景
char(n)	n 字节	固定长度字符串，如身份证号、邮政编码
varchar(n)	实际长度 + 2 字节	可变长度字符串，长度不超过 8,000 字节
varchar(max)	实际长度 + 2 字节	可变长度字符串，长度可达 2^31-1 字节
nchar(n)	2n 字节	固定长度 Unicode 字符串
nvarchar(n)	实际长度 * 2 + 2 字节	可变长度 Unicode 字符串，长度不超过 4,000 字符
nvarchar(max)	实际长度 * 2 + 2 字节	可变长度 Unicode 字符串，长度可达 2^30-1 字符
text/ntext	可变	已弃用，使用 varchar(max)/nvarchar(max) 替代

2.3 日期和时间类型

数据类型	存储大小	范围	精度	适用场景
date	3 字节	0001-01-01 到 9999-12-31	天	仅需要日期
time	3-5 字节	00:00:00.0000000 到 23:59:59.9999999	100 纳秒	仅需要时间
datetime	8 字节	1753-01-01 到 9999-12-31	3.33 毫秒	传统日期时间类型
datetime2	6-8 字节	0001-01-01 到 9999-12-31	100 纳秒	高精度日期时间
datetimeoffset	8-10 字节	0001-01-01 到 9999-12-31	100 纳秒	带时区偏移的日期时间
smalldatetime	4 字节	1900-01-01 到 2079-06-06	1 分钟	低精度日期时间

2.4 二进制类型

数据类型	存储大小	适用场景
binary(n)	n 字节	固定长度二进制数据
varbinary(n)	实际长度 + 2 字节	可变长度二进制数据，长度不超过 8,000 字节
varbinary(max)	实际长度 + 2 字节	可变长度二进制数据，长度可达 2^31-1 字节
image	可变	已弃用，使用 varbinary(max) 替代

2.5 特殊数据类型

• xml：存储 XML 数据，支持 XML 索引和查询
• json：SQL Server 2016+ 支持，存储 JSON 数据，支持 JSON 函数
• geography：存储地理空间数据（如经纬度）
• geometry：存储平面空间数据
• hierarchyid：存储层次结构数据
• rowversion/timestamp：自动生成的二进制数字，用于版本控制
• uniqueidentifier：全局唯一标识符 (GUID)

3. 约束管理

约束用于确保数据完整性，SQL Server 支持多种约束类型：

3.1 主键约束 (PRIMARY KEY)

• 唯一标识表中的每一行
• 不允许 NULL 值
• 每个表只能有一个主键
• 自动创建聚集索引（除非指定非聚集）
• 推荐使用整数类型作为主键，提高性能

3.2 外键约束 (FOREIGN KEY)

• 确保引用完整性，关联两个表
• 可以级联更新或删除
• 提高查询性能，支持索引
• 可以延迟约束检查

3.3 唯一约束 (UNIQUE)

• 确保列或列组合的值唯一
• 允许 NULL 值（最多一个）
• 自动创建唯一索引
• 可以作为外键引用

3.4 检查约束 (CHECK)

• 限制列的值范围
• 支持复杂的条件表达式
• 可以引用同一表中的其他列
• 不支持引用其他表

3.5 默认约束 (DEFAULT)

• 为列提供默认值
• 可以使用常量、函数或表达式
• 插入数据时，如果未指定值，则使用默认值
• 可以用于时间戳列，自动记录插入时间

3.6 NOT NULL 约束

• 确保列不接受 NULL 值
• 提高查询性能，减少 NULL 检查
• 确保数据完整性
• 可以在创建表或修改表时添加

4. 表优化技术

4.1 表分区

• 将大表分解为多个小分区，提高查询性能和管理效率
• 支持按范围、列表或哈希分区
• 可以在不同的文件组上存储不同的分区
• 适合大型表和数据归档

4.2 列存储索引

• 列式存储格式，适合数据仓库和分析工作负载
• 提供极高的数据压缩率
• 支持批量更新和实时操作
• 可以与行存储索引并存

4.3 内存优化表

• 数据完全存储在内存中，减少磁盘 I/O
• 使用无锁数据结构，提高并发性能
• 适合高吞吐量、低延迟的 OLTP 工作负载
• 支持 ACID 事务

4.4 稀疏列

• 用于存储大量 NULL 值的列
• 减少存储空间，提高性能
• 适合数据仓库和维度表
• 支持列集，简化查询

4.5 计算列

• 基于其他列的表达式计算得出
• 可以是持久化的或非持久化的
• 持久化计算列可以创建索引
• 减少数据冗余，确保数据一致性

版本差异

SQL Server 不同版本在数据库与表方面的主要差异：

1. SQL Server 2012 及之前

• 基本的表和数据库功能
• 有限的 JSON 支持
• 没有内存优化表
• 基本的分区表支持

2. SQL Server 2014

• 引入内存优化表
• 增强的列存储索引
• 改进的分区表支持

3. SQL Server 2016

• 原生 JSON 支持
• 增强的内存优化表
• 改进的列存储索引
• 引入 temporal tables（系统版本化临时表）

4. SQL Server 2017

• 跨平台支持，在 Linux 上创建和管理数据库
• 增强的 JSON 功能
• 改进的列存储索引性能

5. SQL Server 2019

• 增强的内存优化表
• 改进的列存储索引
• 引入持久化内存支持
• 增强的分区表功能

6. SQL Server 2022

• 增强的 JSON 支持
• 改进的列存储索引性能
• 增强的分区表功能
• 引入 ledger tables（ ledger 表，用于不可篡改的数据）

最佳实践

1. 数据库设计

• 分离数据和日志文件：将数据文件和日志文件放在不同的物理磁盘上
• 使用多个数据文件：将大型表分布到多个数据文件，提高并行 I/O 性能
• 使用合适的恢复模式：根据业务需求选择完整、简单或大容量日志恢复模式
• 设置合适的自动增长：避免频繁的自动增长操作，设置合理的初始大小和增长增量
• 定期备份数据库：确保数据安全，支持恢复

2. 表设计

• 选择合适的数据类型：使用最小的合适数据类型，提高性能和减少存储空间
• 设计合理的主键：使用整数类型作为主键，提高查询性能
• 使用约束确保数据完整性：避免在应用程序中实现数据完整性检查
• 考虑表分区：对于大型表，考虑使用分区表提高性能和管理效率
• 避免过度规范化：根据查询模式，适当反规范化以提高性能

3. 索引设计

• 为外键创建索引：提高 JOIN 性能，支持级联操作
• 为常用查询创建索引：分析查询模式，创建合适的索引
• 避免过度索引：每个索引都会增加写操作的开销
• 定期维护索引：重建或重组碎片化索引

4. 性能监控与优化

• 监控表大小和增长趋势：使用 DMV 和性能计数器监控表大小
• 分析查询性能：使用 Query Store 和执行计划分析查询性能
• 识别热点表：监控表的读写频率，优化热点表
• 优化插入操作：使用批量插入，减少日志生成
• 考虑使用列式存储：对于分析工作负载，考虑使用 Columnstore 索引

常见问题 (FAQ)

Q: 如何选择合适的主键类型？

A: 选择主键类型时应考虑：

• 整数类型：推荐使用，如 int 或 bigint，性能最好
• GUID 类型：适合分布式系统，但可能导致索引碎片
• 复合主键：尽量避免，会增加索引大小和复杂度
• 自然键 vs 代理键：优先使用代理键，避免业务逻辑变化影响主键

Q: 如何优化表的插入性能？

A: 优化插入性能的方法包括：

• 使用批量插入操作（如 BULK INSERT 或 SqlBulkCopy）
• 禁用索引和约束，插入完成后重新启用
• 使用最小的事务，避免长事务
• 考虑使用内存优化表
• 调整数据库恢复模式（如使用大容量日志恢复模式）

Q: 如何处理大型表？

A: 处理大型表的方法包括：

• 使用表分区，将表分解为多个小分区
• 考虑使用 Columnstore 索引，提高查询性能
• 归档旧数据，减少表大小
• 使用水平分片，将数据分布到多个数据库
• 优化查询，避免全表扫描

Q: 如何选择合适的数据类型？

A: 选择数据类型时应考虑：

• 数据范围：选择能容纳所有可能值的最小数据类型
• 性能：优先使用整数类型，避免大文本类型
• 存储空间：使用最小的合适数据类型，减少存储空间
• 查询模式：根据查询类型选择合适的数据类型
• 兼容性：考虑应用程序兼容性和未来扩展性

Q: 如何设计有效的外键关系？

A: 设计外键关系时应考虑：

• 确保引用完整性，避免孤儿记录
• 使用合适的级联操作，如级联删除或更新
• 为外键创建索引，提高 JOIN 性能
• 考虑使用延迟约束检查，提高插入性能
• 避免循环引用，简化数据模型

Q: 如何使用计算列优化查询？

A: 使用计算列优化查询的方法包括：

• 对于频繁使用的计算表达式，创建持久化计算列
• 为计算列创建索引，提高查询性能
• 避免复杂的计算表达式，影响插入和更新性能
• 考虑使用视图，替代非持久化计算列

总结

数据库与表设计是 SQL Server 运维的基础，合理的设计对于确保数据完整性、提高查询性能和简化维护工作至关重要。DBA 应该根据业务需求、查询模式和性能要求，设计高效、可靠的数据库架构。

通过本文的介绍，相信读者已经对 SQL Server 数据库与表的设计原则、最佳实践和常见问题有了深入的了解。在实际生产环境中，DBA 应该结合具体的业务场景，灵活运用这些知识，不断优化数据库设计，提高系统性能和可靠性。