是否有可能拥有超过一百万列的 SQL 表？

Question

我正在建立一个微阵列数据数据库。每个患者样本都有超过 1,000,000 个特征，我希望将患者样本作为行存储在 SQL 表中，每个特征作为列。

                 HuEX Microarray Data
+----+----------+----------+-----+------------------+
| ID | Feature1 | Feature2 | ... | Feature1,000,000 |
+----+----------+----------+-----+------------------+
| 1  |   2.3543 |  10.5454 | ... |          5.34333 |
| 2  |  13.4312 |   1.3432 | ... |         40.23422 |
+----+----------+----------+-----+------------------+

我知道大多数关系数据库系统对表中的列数都有限制。

+------------+-----------------+
|       DBMS | Max Table Col # | 
+------------+-----------------+
| SQL Server |  1,024 - 30,000 |
|      MySQL |    65,535 bytes |
| PostgreSQL |     250 - 1,600 |
|     Oracle |           1,000 | 
+------------+-----------------+

显然这些限制对于我的任务来说太低了。是否有办法增加 SQL 数据库表可以拥有的列数，或者是否有另一个 DBMS 可以处理如此多的表列？

更新

请注意，所有列都将具有所有行的值。

Answer 1

不。

即使你能让它发挥作用，它也会非常缓慢且笨拙。

相反，您应该创建一个单独的表，其中包含 PatientID、Feature 和 Value 列。
该表将为您建议的表中的每个单元格分配一行。

它还可以添加有关每个患者特征对的附加信息。

Answer 2

您通常会拆分（规范化）表：

Sample: ID, PatientID
Feature: ID, Name
SampleFeature: SampleID, FeatureID, value

SQL 数据库无法处理大量列，但它们可以处理大量行。

Answer 3

尝试重新排列您的表格：

CREATE TABLE MicroarrayData (
    SampleID  INTEGER,
    FeatureID INTEGER,
    Value     REAL,
    PRIMARY KEY (SampleID, FeatureID)
);

Answer 4

这实际上是实体属性值模型 (EAV) 的用例，并且实际上可能更适合某些激烈环境中的非 RDBMS/SQL 解决方案。 （尽管关系数据库是主力……不妨使用它，直到它被证明是不够的;-）

来自维基百科文章：

<块引用>

实体属性值模型（EAV）是一种描述实体的数据模型，其中可用于描述它们的属性（属性、参数）的数量可能很大，但实际适用于给定的属性的数量实体相对温和。在数学中，该模型称为稀疏矩阵。

快乐编码。

Answer 5

好吧，根据新信息，这是同质数字（双精度）值的密集数组，查询很重要（也就是说，我将忽略 blob/XML 的反规范化以及特殊 UDF 的使用），我建议如下：

将每个结果拆分为多个记录，其中每个记录的形式为：

ID, SEGMENT, IDx ... // where x is [0, q]

q 的值是任意的，但应根据特定的数据库实现来选择（例如尝试适应SQL 中的 8k 记录大小服务器）出于性能/效率原因。

然后，每个结果将被拆分为记录，以便 SEGMENT 引用该段。也就是说，给定特征的“绝对索引”为n = SEGMENT * q + x，并且特征n将在SEGMENT = n /的记录中找到q 。那么主键就是(ID, SEGMENT)。

因此，查询仍然很容易——唯一的变化是与段之间的转换——唯一的附加要求是SEGMENT（该列也可能参与索引）。

（可以使用单独的表将特征映射到SEGMENT/x或其他。通过这种方式，它类似于 EAV 模型。）

因此，虽然在某些方面与完全规范化的形式相似，但它采用利用初始矩阵的打包/同质/静态特征性质来显着减少记录数量——而 200 万条记录可以说是一个小表，2000 万条记录只是一个“中型”表，2 亿条记录（结果从 200 个芯片 x 每个芯片 100 万个功能（如果每个功能都创造一个记录）开始变得令人畏惧。尽管复杂性相同，q 为 200 会将记录数量减少到仅仅 1000 万条。 （每个压缩记录在数据/结构比率方面也更加更加高效。）

快乐的编码。

---

虽然以上是我的一个尝试性的“假设”建议，但我鼓励更多地探索这个问题——特别是所需的确切数据访问模式。我不确定这是标准 RDBMS 的“典型”用法，而且 RDBMS 甚至可能不是解决此问题的好方法。