错误代码1117列太多。表的MySQL列限制

#1 楼

为什么您需要创建一个甚至只有20列的表，更不用说2000了？授予的，非规范化的数据可以避免必须执行JOIN来检索多列数据。但是，如果列数超过10，则应该停下来考虑一下数据检索期间的实际情况。

如果2000列表经过SELECT * FROM ... WHERE，则会生成在处理过程中使用大的临时表，获取不必要的列，并创建许多方案，在这些情况下，通信包（max_allowed_pa​​cket）会在每个查询中都处于边缘。

在我作为开发人员的早期，我早在1995年就曾在一家公司工作，DB2是主要的RDBMS。该公司有一个包含270列，数十个索引的表，并且在检索数据时遇到性能问题。他们联系了IBM，并请顾问检查了他们系统的体系结构，包括一张整体表。该公司被告知：“如果您在未来两年内不对该表进行规范化，那么DB2将在执行Stage2处理的查询上失败（任何需要对未索引列进行排序的查询）。”有人告诉一家价值数万亿美元的公司，以规范化270列表。 2000列表的数量还要多。在这种情况下，这些选项将是


max_allowed_pa​​cket
tmp_table_size
max_tmp_tables
max_heap_table_size
max_length_for_sort_data
max_sort_length
sort_buffer_size
myisam_max_sort_file_size
myisam_sort_buffer_size

如果您有TB的RAM，请对这些设置进行每周处理以弥补存在数十列（更不用说数百列）的情况了。

如果您使用InnoDB，此问题会成倍增加，因为您将不得不处理MVCC（多版本并发控制），试图通过事务隔离保护每个SELECT，UPDATE和DELETE的大量列。 >
没有替代品或创可贴可以弥补糟糕的设计。请，为了您日后的理智，今天将这张桌子归一化！！！

#2 楼

我无法想象在适当的规范化表中数据模型可以合法包含2000列的任何事物。

我的猜测是，您可能正在做某种“填补空白”的非规范化模式，实际上是将所有不同类型的数据存储在一个表中，而不是将数据分解为单独的表并建立关系，而是拥有多个字段来记录给定数据中存储的数据的“类型”行，并且您90％的字段为NULL。尽管如此，即使那样，要达到2000列... yikes。如果您要存储大量与给定记录关联的键/值数据，为什么不这样建模呢？这样的事情：

CREATE TABLE master (
    id INT PRIMARY KEY AUTO_INCREMENT,
    <fields that really do relate to the
    master records on a 1-to-1 basis>
);

CREATE TABLE sensor_readings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    master_id INT NOT NULL,   -- The id of the record in the
                              -- master table this field belongs to
    sensor_id INT NOT NULL,
    value VARCHAR(255)
);

CREATE TABLE sensors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    <fields relating to sensors>
);

然后要获取与给定“主”记录关联的所有传感器条目，您只需SELECT sensor_id,value FROM sensor_readings WHERE master_id=<some master ID>即可。如果需要获取master表中记录的数据以及该记录的所有传感器数据，则可以使用联接：

SELECT master.*,sensor_readings.sensor_id,sensor_readings.value
FROM master INNER JOIN sensor_readings on master.id=sensor_readings.master_id
WHERE master.id=<some ID>

，然后如果您需要每个传感器的详细信息，可以进一步加入。

#3 楼

这是一个具有2000个传感器的测量系统


忽略有关规范化的所有评论-您要的是合理的数据库设计（在理想的世界中）并且完美无缺标准化，这是非常不寻常的，而且正如其他地方所指出的那样，RDBMS通常根本不是为这么多列而设计的。链接可能阻止您进一步前进

如其他人所建议的，您可以通过使用带有id, sensor_id, sensor_value的子表来解决此限制，或者更简单地，您可以创建第二个表以仅包含那些将不适合第一个（并使用相同的PK）

#4 楼

MySQL 5.0列数限制（添加了重点）：每个表有4096个列的硬限制，但是对于给定的表，有效最大值可能会更少。确切的限制取决于几个相互作用的因素。




每个表（无论存储引擎如何）的最大行大小为65,535字节。存储引擎可能会对此限制施加其他限制，从而减小有效的最大行大小。

最大行大小限制了列的数量（可能还有大小），因为所有列的总长度不能超过此大小。


...

各个存储引擎可能会施加限制表列计数的其他限制。示例：


InnoDB最多允许1000列。

#5 楼

首先是更多的燃烧，然后是一个真正的解决方案...

我大多同意已经向您抛出的火焰。

我不同意键值归一化。查询最终变得可怕。性能甚至更差。

一种避免当前问题（列数限制）的“简单”方法是“垂直分区”数据。假设有5个表，每个表有400列。它们都将具有相同的主键，只是一个主键可能是AUTO_INCREMENT。

最好将最重要的十二个字段决定下来，然后将它们放入“ main”表中。然后以某种合乎逻辑的方式将传感器分组，然后将它们放入几个并行表中。使用正确的分组，您可能不必一直都联接所有表。

您是否为任何值编制索引？您需要搜索它们吗？

是否需要对日期时间进行搜索？

如果需要索引很多列-平底锅。

如果需要索引一些列-将它们放入“主表”中。

这是真正的解决方案（如果适用）...

如果您不需要索引的大量传感器，则不要创建列！是的，你听到了我的声音。相反，将它们收集到JSON中，压缩JSON，然后将其存储到BLOB字段中。您将节省大量空间；您将只有一个表，没有列限制问题；等等。您的应用程序将解压缩，然后使用JSON作为结构。你猜怎么了？您可以拥有结构-可以将传感器分组为数组，多级内容等，就像您的应用程序希望的那样。另一个“功能”-它是开放式的。如果添加更多传感器，则无需更改表。 JSON（如果这样灵活）。

（压缩是可选的；如果您的数据集很大，它将有助于磁盘空间，从而提高整体性能。）

#6 楼

我认为这是大数据世界中的一种可能方案，在这种情况下，您可能不会执行传统的select *类型的查询。我们在客户级别的预测建模世界中处理此问题，我们在数千个维度上对客户进行建模（所有维度的值为0或1）。当您在同一行中同时拥有风险因素和同一行中的结果标志时，这种存储方式使下游模型构建活动等更加容易。这可以从具有父子结构的存储立场上标准化，但是下游的预测模型将需要将其转换回平面模式。我们使用redshift进行列存储，因此在加载数据时，您的1000多个列实际上以列格式存储...

这种设计存在时间和地点。绝对。规范化不是解决每个问题的方法。