关系型数据库中用整数外键替换重复字符串列是否有性能优势？

问题背景 #

最初设计的单表结构（country字段存在大量重复值）：

CREATE TABLE test
(
    id      INT IDENTITY(1, 1),
    name    VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO test
VALUES      ('Amy', 'Mexico'),
            ('Tom', 'US'),
            ('Mark', 'Morocco'),
            ('Izzy', 'Mexico');
-- millions of other rows

拆分后的关联表结构：

CREATE TABLE countries
(
    id   INT IDENTITY(1, 1),
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE test
(
    id         INT IDENTITY(1, 1),
    name       VARCHAR(100) NOT NULL,
    country_id INT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(country_id) REFERENCES countries(id)
); 

提问：从性能和索引角度来看，第二种方案是否存在优势，还是仅仅增加了操作复杂度？（已知第一种方案未违反任何范式）

性能与索引层面的明确优势 #

1. 存储与IO优化 #

• 原表中country是VARCHAR(100)，每条记录都要存储完整的国家名称字符串，百万级数据下，重复存储会占用大量磁盘空间。拆分后test表用仅4字节的INT类型country_id代替最长100字节的字符串，能大幅压缩表的总大小，直接降低磁盘IO开销——不管是全表扫描还是定点读取，速度都会更快。
• 数据备份、恢复的效率也会同步提升，因为整体数据量变小了很多。

2. 索引效率提升 #

• 如果给原表的country字段建索引，索引条目是一个个字符串值，不仅占用更多存储空间，索引树的层级也会更高（单条索引记录体积大），查询时的索引查找速度会变慢。
• 拆分后给test.country_id建索引，INT类型的索引条目体积小，索引树更紧凑，查找、排序这类操作的效率会显著提升。另外countries表的主键本身就是聚集索引，查找国家名称的速度极快。

3. 查询性能优化 #

• 当需要按国家做分组、过滤、排序时，INT类型的country_id比字符串比较效率高得多：字符串比较需要逐字符匹配，而数值比较是直接的二进制匹配，速度差距会随着数据量增大而越发明显。
• 多表关联查询（比如test关联countries取国家名称），只要有合适的索引（比如test.country_id的非聚集索引），JOIN的开销几乎可以忽略，远小于直接处理大量重复字符串的开销。

关于操作复杂度的权衡 #

拆分方案确实会带来一点操作上的复杂度：

• 新增数据时，需要先确保countries表中存在对应国家的记录，可能要先做查询，或者用MERGE（SQL Server）这类语句来避免重复插入，再往test表插数据。
• 但换个角度，修改国家名称时，只需要在countries表中改一次即可，这比原表要修改百万条重复记录高效太多——这是原表完全不具备的优势。
• 查询时需要多表JOIN，但只要索引设置合理，这个额外开销微乎其微，完全不会抵消性能上的收益。

总结 #

在数据量较大（比如你提到的百万级）的场景下，第二种拆分方案从性能和索引角度看有明确的优势：既节省存储、提升IO和索引效率，还能简化批量修改国家信息的操作。虽然增加了一点关联操作的复杂度，但这种复杂度带来的性能收益远大于其成本，而且数据量越大，优势越显著。

内容的提问来源于stack exchange，提问作者adragomir