【PGCCC】PostgreSQL 数据库设计中的文本标识符 | 翻译

无论是设计独立应用程序还是微服务，您都不可避免地会遇到共享标识符的话题。无论是网页的 URL、RESTful API 资源、JSON 文档、CSV 导出还是其他内容，特定资源的标识符都会被暴露。

/orders/123
/products/345/variants/1

虽然标识符只是一个数字，不带有任何负面含义，但您可能有充分的理由避免暴露它们。这些原因包括：

• 安全性与数据暴露：数值标识符具有顺序性和可预测性，能够暴露底层数据源的信息（例如数据量），为ID枚举提供依据。
• 隐私和保密性：人们可能会担心隐藏引用数据的数量。例如，客户、客户或订单的数量可能是企业希望保密的信息。
• 非描述性：整数作为标识符可能会引起混淆。像 123 这样的 ID 不会传达任何附加信息，这使得调试边缘情况更加困难。

这些原因和其他原因（如 SEO 优化）导致文本标识符的使用增加。它们的可读性和多功能性使其成为外部数据共享的理想选择。

然而，在数据库（或数据模型）设计中，文本标识符的优势往往被其带来的问题所掩盖。虽然文本标识符提高了互操作性，但它们往往伴随着性能和存储方面的权衡。相比之下，整数自然处理速度更快、效率更高，从而降低了存储要求，加快了索引、排序和搜索速度——而这些任务正是计算机所优化的。

在本文中，我们将探讨在数据库设计中直接使用文本标识符的场景，并讨论有效使用它们的策略。

什么让文本标识符如此有吸引力？

说实话，文本标识符之所以流行是有原因的。对于人类来说，它们更易于阅读，在某些情况下，还可以添加额外的上下文。（如果你不喜欢 Heroku 古怪而又令人难忘的名字，请举手！）如果选择得当，它们也更容易记住。

文本标识符可以嵌入其他上下文。以订单号 APAC-20241103-8237 为例，它同时编码了地区和订单日期。使用文本标识符的另一个常见原因是它们在分布式环境中的唯一性。

当人们需要直接与它们互动时，它们特别方便。例如，客户从电子邮件中复制订单号或支持团队讨论问题时，可读且有意义的标识符会大有裨益。它更简单、更直观，而且当有人试图回忆或分享它时，不太可能引起麻烦。

当标识符不再仅仅是标识符时

当文本标识符在数据或数据库模型中用作自然键时，就会出现问题。尽管文本标识符有诸多好处，但出于以下几个原因，它通常不适合用作主键：

上下文变化：文本标识符提供的附加上下文可能会发生变化，从而需要更新。尽管保证不会发生改变，但变化是不可避免的。
排序问题：对文本标识符进行排序可能很棘手，特别是基于语言环境的排序或当标识符中嵌入数字时（例如，order1434 与 order349）。
最终的问题是效率：

文本标识符通常比数字标识符需要更多的存储空间。文本字段中的每个字符比简单整数占用更多字节，这会导致数据库大小更大、性能更慢，尤其是在索引或处理大型数据集时。
数据库针对数字操作进行了优化，这使得文本字段的搜索、连接和索引本质上变慢。这种性能差距会严重影响大型数据集，从而影响应用程序效率。
文本标识符使管理表之间的关系变得复杂。额外的存储要求不仅影响源表，还影响所有引用实体。将增加的存储空间乘以引用表的数量，您就会了解整体影响。
随着数据库的增长，这些问题变得更加突出。存储需求的增加和操作速度的降低导致了数据库膨胀，从而降低了系统性能。此外，膨胀的索引可能会误导查询规划器做出次优选择，例如优先选择顺序扫描而不是索引扫描，从而使常规操作更加复杂。

UUID 不是解决所有这些问题的解决方案吗？

视情况而定。虽然 UUID 提供数字表示，并且非常适合在分布式系统中生成唯一密钥，但它们并不总是最佳选择：

与 BIGINT 相比，现实世界中很少有场景需要全范围的 UUID。对于大多数解决方案来说，过早优化通常是不值得的。
UUID 的 16 字节（128 位）存储大小可能不如许多文本标识符高效。即使是 8 字节（64 位）的 BIGINT 也更节省存储。这种低效率还延伸到索引、连接和其他操作。
个人观点：UUID 很丑陋。
此外，不同版本的 UUID 具有不同的优势。例如，UUIDv1 包含时间戳组件，使其具有一定程度的可排序性，而 UUIDv4 则完全是随机的。即使是可排序的 UUID 也可能无法比 BIGINT 或精心构造的文本标识符等更精简的选项提供显着的优势。

在大多数情况下，性能和可读性的权衡使得 UUID 不那么有吸引力，除非其全局唯一性在分布式系统中至关重要。

现实生活中的例子

让我们超越理论并探索实际的例子：

CREATE TABLE sessions (
    token TEXT PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(user_id),
    ...
);

CREATE TABLE products (
    sku TEXT PRIMARY KEY,
    label TEXT NOT NULL,
    ...
);

CREATE TABLE documents (
    document_id TEXT PRIMARY KEY,
    ...
);

在这些情况下，使用文本标识符作为主键似乎合乎逻辑，因为：

• 它们是实体的自然键。
• 它们很可能在服务范围之外传播，因此无论如何都需要存储。
• 预计他们不会改变。
• 对于单个表的存储影响似乎可以忽略不计。

然而，这种逻辑经不起推敲。文本标识符经常传播到服务范围之外，导致更新它们的压力。更新主键并非易事。考虑一下：

• 虽然 SKU 理想情况下永远不变，但现实世界中的场景（如品牌重塑、产品整合或供应商变更）可能需要更新。尽管 SKU
  很有吸引力，但它们并不是理想的主键候选者。
• 随机生成的文本标识符（如会话令牌）将…有待确定。

跨表引用这些标识符时会出现真正的问题：

CREATE TABLE session_logs (
    log_id BIGINT GENERATED ALWAYS AS IDENTITY,
    token TEXT NOT NULL REFERENCES sessions(token),
    ...
);

CREATE TABLE product_reviews (
    review_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_sku TEXT NOT NULL REFERENCES products(sku),
    ...
);

CREATE TABLE customer_orders (
    order_id TEXT PRIMARY KEY,
    customer_id TEXT NOT NULL REFERENCES customers(customer_id),
    ...
);

CREATE TABLE document_revisions (
    revision_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    document_id TEXT NOT NULL REFERENCES documents(document_id),
    ...
);

在这种情况下，文本标识符可能会成为问题。首先，您失去了修改它们的能力。其次，存储需求的增加变得显而易见。例如，一百万条记录中每个引用的额外 100 个字节会导致仅一个引用就需要额外的 100 MB 存储空间。

然而，存储并不是最大的问题；索引才是。在 PostgreSQL 中，索引文本字段（无论是作为主键还是外键）所需的空间比索引数字字段要多得多。这会导致索引臃肿、查找速度变慢以及操作更加零散，尤其是在依赖外键关系的查询中。因此，查询规划器可能会诉诸全表扫描而不是索引扫描，从而进一步降低性能。

这些性能问题通常在开发或测试环境中未被发现，但可能会导致生产严重中断。

有效地重新引入文本标识符

这篇文章的目的并不是完全阻止使用文本标识符，而是强调为什么它们不适合作为主键。以下是一些有效处理它们的策略：

1. 引入代理键
一个简单的解决方案是引入代理主键，替换对文本标识符的引用：

CREATE TABLE products (
    product_id INT GENERATED BY DEFAULT AS IDENTITY,
    sku TEXT NOT NULL,
    label TEXT NOT NULL,
    ...
);

CREATE INDEX products_by_sku ON products(sku);

CREATE TABLE product_reviews (
    review_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    ...
);

这种方法可以通过索引保持按 SKU 进行高效检索。

2. 使用映射表获得更大的灵活性
映射表允许您：

* 	Update identifiers without affecting the parent entity.
* 	Maintain a history of text identifiers linked to a specific entity.

CREATE TABLE products (
    product_id INT GENERATED BY DEFAULT AS IDENTITY,
    label TEXT NOT NULL,
    ...
);

CREATE TABLE product_skus (
    product_sku_id INT GENERATED BY DEFAULT AS IDENTITY,
    product_id INT REFERENCES products(product_id),
    sku TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ
);

CREATE UNIQUE INDEX unique_product_skus ON product_skus (product_id, sku) WHERE deleted_at IS NULL;

这种方法可以适应不断变化的 SKU，而不会损害数据完整性。相关用例可能是将映射表链接到产品变体：

CREATE TABLE product_variants (
    variant_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(product_id),
    sku TEXT NOT NULL,
    name TEXT NOT NULL,
    ...
);

3. 解析文本标识符的含义
文本标识符通常包含有意义的信息，例如地区、日期或类别。通过分解标识符，您可以单独存储这些上下文数据，从而提高灵活性和性能。

例如，您可以设计一个更为强大的模式，而不是直接存储像 ORD-EMEA-00789 这样的订单标识符：

CREATE TABLE orders (
    order_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    region_id INT NOT NULL REFERENCES regions(region_id),
    order_date DATE NOT NULL,
    ...
);

CREATE TABLE regions (
    region_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    code TEXT NOT NULL,
    ...
);

INSERT INTO regions (name, code) VALUES
('Europe, the Middle East, and Africa', 'EMEA'),
('Asia Pacific', 'APAC');

To generate a user-friendly order number, you can create a function:

CREATE OR REPLACE FUNCTION get_order_number(p_order_id INT)
RETURNS TEXT AS $$
DECLARE
    v_region_code TEXT;
    v_formatted_order_id TEXT;
    v_order_number TEXT;
BEGIN
    SELECT r.code INTO v_region_code
    FROM orders o
    JOIN regions r ON o.region_id = r.region_id
    WHERE o.order_id = p_order_id;

    IF NOT FOUND THEN
        RETURN NULL;
    END IF;

    v_formatted_order_id := TO_CHAR(p_order_id, 'FM00000');
    v_order_number := 'ORD-' || v_region_code || '-' || v_formatted_order_id;

    RETURN v_order_number;
END;
$$ LANGUAGE plpgsql;

此方法使您能够有效地存储和检索结构化数据，同时仍提供可供外部使用的可读且有意义的标识符。

4. 可逆文本 ID
对于需要增强安全性或隐私性的场景，标识符不应容易被枚举或可预测，您可以使用可逆的基于文本的 ID。这样，您可以向用户呈现看似随机的文本表示，同时在内部保持高效的数字存储。

Sqids是一个可以帮助实现这一目标的项目。它从数字生成 URL 友好的唯一标识符，并可以将多个数字标识符编码为单个字符串。以下是使用 Sqids 项目的示例：

[42] -> JgaEBgznCpUZo3Kk
[42, 430004] -> lTiYlvsGkh59m1PQ

生成的标识符在了解共享字母表的情况下是可逆的，允许您解码请求而无需访问数据库，这在高吞吐量环境中非常有用。此技术对于用户、帐户或会话标识符特别有用，可平衡安全性需求和运营效率。

然而，请务必记住，这不能替代强大的安全实践。适当的身份验证和授权机制仍然是保护应用程序安全的必要条件。

通过精心整合这些策略，您可以在适当的情况下利用文本标识符的优势，同时避免数据库设计中的常见陷阱。这种平衡可确保系统高效、可维护，同时满足技术和业务需求。

5. 利用 Generate 列
在需要保留嵌入上下文的文本标识符的极端情况下， PostgreSQL 中的生成列可能是一个有价值的功能。从版本 12 开始，PostgreSQL 允许定义其值自动从表中的其他列计算出来的列。这确保了一致性，无需人工干预。

例如，您可以定义一个函数来处理格式化逻辑：

CREATE OR REPLACE FUNCTION get_formatted_order_id(p_region_id INT, p_order_id INT)
RETURNS TEXT AS $$
DECLARE
    v_region_code TEXT;
BEGIN
    v_region_code := CASE p_region_id
        WHEN 1 THEN 'EMEA'
        WHEN 2 THEN 'APAC'
        ELSE 'OTHER'
    END;

    RETURN 'ORD-' || v_region_code || '-' || LPAD(p_order_id::TEXT, 5, '0');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

该函数被标记为 IMMUTABLE，因为 PostgreSQL 要求生成的列仅使用不可变函数 - 那些保证每次对相同的输入返回相同结果的函数。

CREATE TABLE orders (
    order_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    region_id INT NOT NULL,
    order_date DATE NOT NULL,
    formatted_order_id TEXT GENERATED ALWAYS AS (
        get_formatted_order_id(region_id, order_id)
    ) STORED
);

此设置可确保每当或发生更改formatted_order_id时都会自动更新。该列以物理方式存储，从而增强了频繁查询数据的读取性能。region_idorder_id

使用生成的列可以简化维护派生值（如格式化文本标识符）的一致性。但是，请注意它们的特征，例如：

• 自动更新：当引用的列发生变化时，系统会自动重新计算该列的值。
• 不变性要求：只能使用不可变函数，确保可靠且一致的计算。

总结

文本标识符将继续存在，这很棒。它们易于阅读、容易记住，并且可以将大量有意义的上下文打包到一个简单的字符串中。它们使外部交互更加顺畅，无论是客户引用订单号还是支持团队跟踪问题。它们甚至为原本枯燥的标识符增添了一点魅力和个性。

然而，控制它们的使用是很重要的。我听说的一条好规则是

• 在外部通信时使用文本标识符- 例如在 URL 或 API 响应中
• 在内部，始终依赖数字 ID - 必要时使用代理键，如 INT 或 BIGINT（如果您追求行星主宰，甚至是UUID），以保持数据库的效率和完整性。

这种方法可让您充分利用文本标识符的优势进行外部通信，同时保持数据库的性能和可扩展性优化。通过将文本标识符存储在专用字段中，并使用数字主键进行内部操作，您可以实现适当的平衡，并尽力保持系统性能。
#PG证书#PG考试#postgresql培训#postgresql考试#postgresql认证
原文链接： https://notso.boringsql.com/posts/text-identifier-in-db-design/
作者： Radim Marek