生成有效的表

8 分钟

有效的表设计构成了任何数据库的基础。表格结构化数据，并决定您的查询在访问和修改信息时的效率。

设计和创建表

表是关系数据库的基本构建基块，将数据组织成表示实体及其属性的行和列。在关系系统中，表定义用于存储事务数据的结构，通过外键强制执行关系，并为查询和报告提供基础。

对于多维分析，表充当 事实数据表 ，用于存储可度量的事件和 维度表 ，提供分析上下文。在创建表时做出的设计决策（数据类型、列大小调整、约束和关系）直接影响到作和分析工作负荷的存储效率、查询性能、数据完整性和可伸缩性。

选择适当的数据类型

数据类型是影响数据库的基本决策。错误的选择可能会导致存储浪费、性能不佳、数据丢失或应用程序错误。与可以轻松重构的应用程序代码不同，更改生产数据库中的列数据类型通常需要重新生成表，这可能意味着大型表的停机时间数小时。

在设计初始架构时选择适当的数据类型，因为这是获得正确架构的最简单时间。此外，请在以下情况下仔细考虑数据类型：

您正在存储需要高精度的数据。
处理存储成本会成倍增加的大容量表
你正在定义频繁查询的列，这些列使用较小的类型执行速度更快

浏览常见数据类型

适当的数据类型会影响存储、性能和操作。

类型类别	数据类型	存储大小	使用准则	Example
数值	`INT`、`BIGINT`、`DECIMAL`、`FLOAT`	4 个字节，8 个字节，变化	根据范围和精度需求进行选择	`Quantity INT`、`Revenue DECIMAL(10,2)`、`Population BIGINT`
字符串	`VARCHAR`、`CHAR`、`NVARCHAR`	1 字节/字符，固定，2 个字节/字符	使用 `VARCHAR` 表示可变长度的数据，`CHAR` 表示固定长度的数据，`NVARCHAR` 表示 Unicode 数据。	`Email VARCHAR(100)`、`CountryCode CHAR(2)`、`ProductName NVARCHAR(100)`
日期/时间	`DATE`、`DATETIME2`、`DATETIMEOFFSET`	3 个字节，6-8 个字节，10 个字节	`DATETIME2` 提供比 `DATETIME` 更高的精度	`BirthDate DATE`、`OrderTimestamp DATETIME2`、`EventTime DATETIMEOFFSET`
二进制	`VARBINARY`、`IMAGE`	varies	用于存储图像或文档等二进制数据	`ProfilePhoto VARBINARY(MAX)`、`DocumentContent VARBINARY(MAX)`
特殊	`UNIQUEIDENTIFIER`、`XML`、`JSON`	16 字节，可变，原生二进制	GUID 适用 `UNIQUEIDENTIFIER`、XML 文档适用 `XML`、JSON 文档（原生二进制格式）适用 `JSON` (SQL 2025+)	`RowGUID UNIQUEIDENTIFIER`、`Config XML`、`Settings JSON`

数据类型细微差别需要仔细注意。例如，使用 FLOAT 财务数据，而不是 DECIMAL 引入舍入错误，无需重新计算每个依赖值即可修复这些错误。当 INT 足够时，使用 UNIQUEIDENTIFIER 主键会使索引大小增加两倍，并减慢每个 JOIN 操作。其中大多数决策会影响数据库的性能，并且可以确定查询是以毫秒为单位还是以分钟为单位运行。

估计表大小要求

表大小不仅关乎存储成本，还直接影响数据库操作。表大小会影响备份和还原时间、索引重新生成持续时间和查询性能。

重要

一个设计不佳的表如果每行存储 200 个字节而非 100 字节，将使您的存储需求、备份时间和 I/O 要求翻倍。

另一种计划表大小调整的方案是在计算云数据库的存储成本、设计有限的磁盘空间或规划存档策略时。这些情景都需要准确的大小估算，以便做出关于资源和操作的明智决策。

例如，一家零售公司每天存储 1 亿个交易记录，如果每行数据多出 50 字节，将浪费 5GB 的存储空间。这意味着每年将有 1.8TB 的不必要存储，此外，备份时间和成本也会成比例增加。

以下示例演示如何估计表的大小 Employee ：

-- Estimate row size for a table
-- Fixed-length columns: sum of column sizes
-- Variable-length: estimate average size
-- Example row calculation:
CREATE TABLE Employee (
    EmployeeID INT,              -- 4 bytes
    FirstName NVARCHAR(50),      -- ~2-100 bytes (avg 40)
    LastName NVARCHAR(50),       -- ~2-100 bytes (avg 40)
    HireDate DATE,               -- 3 bytes
    Salary DECIMAL(10,2)         -- 5 bytes
);  
-- Estimated row size: 4 + 40 + 40 + 3 + 5 = ~92 bytes
-- Plus row overhead (~7 bytes) = ~99 bytes per row
-- 1 million rows ≈ 94 MB

小窍门

可以使用 Copilot 来帮助生成表大小估计。

设计有效列

以下示例演示了一个设计 Product 良好的表，该表应用本单元中介绍的原则：

CREATE TABLE Product (
    ProductID INT PRIMARY KEY IDENTITY(1,1),       -- Auto-incrementing surrogate key (4 bytes)
    ProductName NVARCHAR(100) NOT NULL,             -- Unicode support, appropriate length, enforced
    Category NVARCHAR(50) NOT NULL,                 -- Smaller than ProductName (categorization needs less space)
    Price DECIMAL(10,2) NOT NULL,                   -- Exact precision for financial data
    StockQuantity INT NOT NULL DEFAULT 0,           -- Integer sufficient for inventory, default prevents nulls
    LastRestocked DATETIME2 DEFAULT GETUTCDATE()    -- Modern date type with automatic timestamp
);

以下内容演示了几种最佳做法：

适当的数据类型: INT 适用于主键（小于 BIGINT 或 UNIQUEIDENTIFIER）， DECIMAL(10,2) 用于精确的财务计算而不是 FLOAT， DATETIME2 以获得比遗留系统DATETIME更好的精度。
大小合适的列：根据预期数据长度，产品名称适用 NVARCHAR(100)，类别适用 NVARCHAR(50)
约束： NOT NULL 通过防止缺少关键值来确保数据质量
默认值：（0）和StockQuantity（当前 UTC 时间）的自动值LastRestocked可降低应用程序代码复杂性
高效的主键：生成顺序键，可以高效地进行群集，并使用最小的存储空间（4字节，而非GUID的16字节）

注释

此示例使用 NVARCHAR （每个字符 2 个字节）来支持 Unicode。如果数据仅为ASCII格式，则 VARCHAR 可以将字符串储存减少一半（每个字符1字节）。对于 30 个字符的名称，ProductName VARCHAR(100) 使用约 30 字节，而 NVARCHAR(100) 使用约 60 字节。在 1000 万行中，这将节省大约 300 MB。 NVARCHAR用于国际数据;当存储效率很重要时使用VARCHAR，并且数据将仅保留 ASCII。

设计最佳做法

在设计和实现表时应用以下关键原则，以确保性能和可维护性：

使用适当的数据类型 - 较小的数据类型可减少存储和提高性能
提前考虑表大小 - 估计行大小和表大小总计以规划存储和索引
实现有意义的约束 - 确保数据库级别的数据质量
规划增长 - 设计表以处理将来的数据量
战略性地编制索引 - 在WHERE、JOIN和ORDER BY子句中使用的索引列
选择用于分析的列存储 - 对具有分析查询的大型表使用列存储索引
适当时规范化 - 平衡规范化与查询性能需求
监视行和页面压缩 - 为大型表启用压缩以保存存储

大多数数据库性能问题源于开发初期做出的设计决策不佳。超大数据类型会浪费存储空间，并导致查询速度变慢。缺少或错误的索引类型会创建资源升级无法解决的瓶颈。在创建或修改表之前，在适当的对象设计中投入时间来防止这些问题。在设计过程中做出的决策（选择适当的数据类型、估计表大小、选择正确的索引类型）对长期性能和成本的影响远大于以后可以应用的任何优化。

反馈

此页面是否有帮助？