一个主键会扩散到整个模式
主键(Primary Key)从来不只是一张表的事。所有引用它的外键列都会采用相同的类型,这个值还会出现在索引、连接、API 地址和日志中。因此,主键类型是事后最难更改的设计决策之一。
实践中最常见的候选有三种:
- bigint — 由数据库按 1、2、3… 顺序分配的整数
- UUID v4 — 128 位中有 122 位是随机数的标识符
- UUID v7 — 开头存放生成时间(毫秒),因此按时间排序的 UUID
2025 年 9 月 25 日正式发布的 PostgreSQL 18 加入了 uuidv7() 函数,第三种方案无需扩展即可使用。下面整理各自的优缺点和选择方法。
bigint:小而快,但编号由数据库分配
在 PostgreSQL 中用 GENERATED ALWAYS AS IDENTITY 声明。它比旧的 serial 更符合 SQL 标准。
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE
);
优点
- 只有 8 字节,是 UUID(16 字节)的一半。主键索引以及引用它的外键索引都会更小。
- 新值总是追加在索引最右端,写入效率高。
- 便于人阅读和口头沟通("1042 号订单")。
缺点
- 编号由数据库分配,保存之前无法知道 ID。对于离线应用,或多个服务各自创建记录、之后再合并的架构来说很不方便。
- 直接暴露在 URL 中时,下一个编号很容易被猜到,总数和增长速度也会暴露。
- 合并多个数据库的数据时编号会冲突。
UUID v4:随处可生成,但在索引中很分散
CREATE TABLE customers (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- 与 PostgreSQL 18 的 uuidv4() 是同一个函数
email text NOT NULL UNIQUE
);
优点
- 应用、客户端或其他服务都能预先生成,几乎不会冲突。
- 无法猜测,即使暴露在 URL 中也难以推断其他记录。
缺点
- 由于值是随机的,新行会插入 B-tree 索引的任意位置。在写入频繁的表中,索引页会频繁分裂,最近写入的数据也无法集中在内存缓存里,磁盘 I/O 因此增加。
- 16 字节,索引比 bigint 大。外键越多,差距越大。
在 MySQL(InnoDB)这类按主键顺序存储表本身(聚簇索引)的引擎中,随机插入的影响会波及整张表。
UUID v7:按时间排序的 UUID
UUID v7 是 2024 年 5 月发布的 RFC 9562 所定义的格式。128 位中前 48 位存放 Unix 时间(毫秒),除版本位和变体位外,其余部分用随机值填充。RFC 9562 建议在可能的情况下用 UUIDv7 代替 UUIDv1 和 v6(SHOULD)。
例如在 019535d9-3df7-79fb-b466-fa907fa17f9e 中,前 12 位十六进制数(019535d9-3df7)是生成时间,紧接着的 7 是版本号。
因为时间在前,后生成的值通常更大。所以新行会像 bigint 一样集中在索引最右端,UUID v4 的随机插入问题大大减轻。同时保留了 UUID 的优点:随处可生成,随机部分使其难以猜测。
注意事项
- 只看 ID 就能 知道生成时间。 RFC 9562 在安全考虑中也写道,内嵌的时间戳构成非常小的攻击面。如果生成时间属于敏感信息(例如用户注册时间),请为对外展示另设 ID。
- 大小与 v4 相同,都是 16 字节。
在 PostgreSQL 18 中使用
从 PostgreSQL 18 起无需扩展即可直接使用。
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT uuidv7(),
customer_id uuid NOT NULL REFERENCES customers(id),
total_amount numeric(12, 2) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
-- 查看 ID 中的生成时间和版本
SELECT id,
uuid_extract_timestamp(id) AS id_created_at,
uuid_extract_version(id) AS version
FROM orders
ORDER BY id DESC
LIMIT 10;
PostgreSQL 18 中与 UUID 相关的函数如下:
| 函数 | 说明 |
|---|---|
uuidv7() | 生成按时间排序的 UUID v7。uuidv7(interval) 可将记录的时间偏移相应的间隔 |
uuidv4() | 生成随机 v4,与 gen_random_uuid() 相同(标明版本的别名) |
uuid_extract_timestamp(uuid) | 从 v1、v7 中提取生成时间(其他版本返回 NULL) |
uuid_extract_version(uuid) | 查看版本号 |
不要因为有了 uuid_extract_timestamp() 就删掉 created_at 列。ID 可能在应用中预先生成,也可能以偏移后的时间生成,不一定与业务所需的创建时间一致。
在 MySQL 中
根据 MySQL 8.4 文档,UUID() 生成的是 版本 1 的 UUID,没有生成 UUID v7 的内置函数(MySQL 缺陷跟踪系统中已有原生支持的请求)。实际可行的方法有两种:
- 在应用中生成 UUID v7,存入
BINARY(16)列 - 如果使用
UUID(),用UUID_TO_BIN(UUID(), 1)存储 — 第二个参数1会调换时间部分的顺序,使插入的值更接近顺序递增
无论哪种方式,以字符串(CHAR(36))存储都会使大小增加一倍以上,建议以二进制存储。
按场景选择
| 场景 | 推荐 |
|---|---|
| 单个数据库,ID 不对外展示 | bigint identity |
| 客户端或多个服务需要预先生成 ID,或需要合并数据 | UUID v7 |
| ID 会出现在 URL 或 API 中,但暴露生成时间无妨 | UUID v7 |
| 连生成时间也需要隐藏的值(邀请链接、密码重置令牌等) | UUID v4 或单独的随机令牌 |
如果已经在用 UUID v4 运行,很多时候只需从新表开始使用 v7 就足够了。更改现有主键意味着要同时修改所有引用它的外键,请先权衡收益与成本。
绘制 ERD 时要检查的事项
- 外键类型必须与其引用的主键相同。
uuid与text、bigint与integer混用时,每次连接都会发生类型转换,甚至无法创建约束。 - 与其为每张表单独决定,不如为 整个模式确定一条主键规则,这样能减少错误。例外情况(如对外公开的令牌)请放在单独的列中。
- 除主键索引外,也要为经常查询的外键建立索引。如果键是 16 字节,还需要考虑索引大小。
先确定键的类型再绘制关系,就能及早发现外键列的类型不一致。更多内容请参阅彻底掌握外键与表关系和数据库索引完全指南。