sql - 查找表——自然键或代理键作为主键？

Question

我有一张记录许可证使用情况的表格。每个许可证使用都需要与用户和主机相关联。表定义如下所示。

create table if not exists  per_user_fact
(
    per_user_fact_id        int unsigned        not null    auto_increment,
    time_of_day             char(16)            not null,
    license_served_id       smallint unsigned   not null,
    license_hours           numeric(10,2)       not null,
    role_name               varchar(64)         null,
    user                    varchar(128)        not null,
    host                    varchar(128)        not null,
    primary key (per_user_fact_id),
    foreign key (license_served_id) references served_license(served_license_id),
    foreign key (user, host) references user_host(username, hostname)
);

我想规范化此表，以便将重复的用户/主机值移动到这样的新表中。

create table if not exists  user_host
(
    username                varchar(64)         not null,
    hostname                varchar(128)        not null,
    primary key (username, hostname)
);

对于 user_host 表，我应该选择哪种主键 - 自然主键还是代理主键？我可以想到以下控制因素。

1. 如果主键是自然主键，即用户名和主机名的组合，则父表 per_user_fact 将不需要额外的联接来查找用户名和主机名。
2. 如果主键是自然的，则会浪费存储空间，因为用户名和主机名值将在两个表中重复。
3. 如果主键是代理项，则父表需要额外的连接才能获取用户名和主机名的值。
4. 如果主键是代理项，则对 user_host 表的索引会更快。

请指教。

Answer 1

我非常喜欢使用代理主键，即使在这种情况下也是如此。当您连接到集群主键时，额外连接的成本可以忽略不计。

此外，假设 username 和 hostname (一起)长于四个左右的字符，代理键可以节省空间。事实上，您可能会发现代理键导致更快 查询，因为per_user_fact 中的数据较小。较小的表占用较少的数据页，从而导致较少的 I/O。

代理键的另一个优势是可以在修改任何其他表的情况下更改用户名和主机名。如果您使用数据字段进行连接，那么修改值需要更新多个表——这是一个更麻烦的操作。

我还喜欢代理标识/序列/自动递增键，因为它们还捕获表中的插入顺序。当然，还有其他方法(我的表通常有一个默认为插入时间的 CreatedAt 列)。但是，代理键也可以起到这个作用。

这些原因并不构成问题的“正确”答案。有不使用代理人的正当理由。不过，对我来说，我几乎所有的表都有这样的主键。