实验数据存储：重复列值场景下的最优数据库结构选型

针对多配置组合、每个组合需存储10万条实验结果的场景，以下是三种数据库结构方案的优缺点分析：

1. 朴素实现 #

单表包含setting a、setting b、result of experiment列，每行存储一条实验结果，示例数据如下：

(_ROWID_), setting a, setting b, result
(0,) 1 0.1 res_1
(1,) 1 0.1 res_2
(2,) 1 0.1 res_3
... 
(n,) 2 0.1 res_n
(n+1,) 2 0.1 res_n+1
... 
(k,) 3 0.1 res_k
(k+1,) 3 0.1 res_k+1
... 
(l,) 1 0.01 res_l
(l+1,) 1 0.01 res_l+1
... etc.

• 优点：实现难度极低，无需复杂的表关联或索引配置，开发、调试和维护成本都很低；数据写入逻辑简单，支持单条或批量插入。
• 缺点：存储空间浪费严重，setting a和setting b会随着同配置的10万条实验结果大量重复存储；查询性能差，默认自增ROWID的表数据无序，针对特定配置组合的查询需要全表扫描，数据量越大性能衰减越明显。

2. 父表+子表结构 #

父表作为配置目录，存储setting a、setting b的唯一组合及对应子表的标识；每个配置组合对应一个独立子表（命名如table_a_b），子表仅存储result of experiment列，无需重复存储设置值。

• 优点：彻底消除设置值冗余，子表只存储实验结果，存储空间利用率达到最优。
• 缺点：查询复杂度高，查询特定配置的实验结果时，需要先从父表查询到对应子表，再对子表进行操作，多表跳转增加了查询逻辑的复杂度；扩展性与维护性差，若配置组合数量较多，会生成大量子表，数据库管理难度陡增，后续新增配置组合还需手动创建对应子表，不利于批量操作与统一管理。

3. 聚集索引（Clustered Indexes）方案 #

基于单表结构，通过在setting a和setting b上创建联合聚集索引，利用B树结构将相同配置组合的实验结果物理聚集存储在一起。

• 优点：兼顾空间效率与查询性能，无需拆分表，避免设置值冗余的同时，相同配置的数据物理连续存储，针对特定配置的查询可以快速定位数据范围，大幅提升查询效率；维护成本低，仅需维护单表和一个联合索引，相比多表结构更易管理；主流数据库兼容性较好（如MySQL InnoDB、SQL Server均支持）。
• 缺点：写入有一定性能开销，插入数据时需要维护B树索引结构，会带来轻微的写入性能损耗，但对于实验数据批量写入的场景，通过按配置组合有序插入可有效降低这种损耗；依赖数据库特性，部分数据库引擎（如MySQL MyISAM）不支持聚集索引，需结合实际选型确定可行性。

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