主页 / dba / 问题 / 3972
Accepted
Mokus
Asked: 2011-07-20 23:24:46 +0800 CST

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

  • 772

我有一个包含 1699 列的表，当我尝试插入更多列时，我得到了，

错误代码：1117。列太多

在这张表中，我只有 1000 行。对我来说，最重要的是列数。桌子上有什么限制吗？我想创建 2000 列。那可能吗？

mysql table

6 个回答

  1. Best Answer

    为什么你需要创建一个有 20 列的表，更不用说 2000 了？？？

    当然，非规范化数据可以避免必须执行 JOIN 来检索许多数据列。但是，如果您有超过 10 列，您应该停下来想一想在数据检索期间会发生什么。

    如果一个 2000 列的表经历 SELECT * FROM ... WHERE，您将在处理过程中生成大型临时表，获取不必要的列，并创建许多场景，其中通信数据包 ( max_allowed_pa​​cket ) 在每次查询时都会被推到边缘。

    在我早期作为开发人员的日子里，我在 1995 年曾在一家公司工作，当时 DB2 是主要的 RDBMS。该公司有一个包含 270 列、数十个索引的表，并且在检索数据时存在性能问题。他们联系了 IBM，并让顾问检查了他们系统的架构，包括这张单片机。该公司被告知“如果您在未来 2 年内不规范化该表，DB2 将在执行 Stage2 处理的查询（任何需要对非索引列进行排序的查询）上失败。” 这被告知一家价值数万亿美元的公司，以规范一个 270 列的表。2000 列的表格更是如此。

    就 mysql 而言，您必须通过设置与 DB2 Stage2 处理相当的选项来弥补这种糟糕的设计。在这种情况下，这些选项将是

    如果您有 TB 的 RAM，则调整这些设置以弥补数十个（更不用说数百个）列的存在效果很好。

    如果您使用 InnoDB，这个问题会成倍增加，因为您将不得不处理MVCC（多版本并发控制），试图通过事务隔离来保护每个 SELECT、UPDATE 和 DELETE 的大量列。

    结论

    没有替代品或创可贴可以弥补糟糕的设计。请，为了您将来的理智，今天规范化该表！

    • 40

  2. 我无法想象数据模型可以在正确规范化的表中合法包含 2000 列的任何内容。

    我的猜测是，您可能正在做某种“填空”非规范化模式，您实际上是在一个表中存储所有不同类型的数据，而不是将数据分解成单独的表并建立关系，您有各种字段来记录给定行中存储的数据“类型”，并且 90% 的字段为 NULL。即便如此，想要达到 2000 列……哎呀。

    您的问题的解决方案是重新考虑您的数据模型。如果您要存储大量与给定记录相关的键/值数据，为什么不这样建模呢？就像是：

    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>

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

    • 25

  3. 这是一个包含 2000 个传感器的测量系统

    忽略所有关于规范化的评论-您所要求的可能是明智的数据库设计（在理想世界中）并且完全规范化，这是非常不寻常的，并且正如在其他地方指出的那样，RDBMS 通常根本不是为这么多列设计的.

    尽管您没有达到 MySQL硬限制，但链接中提到的其他因素之一可能会阻止您走得更高

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

    • 21

  4. MySQL 5.0 列数限制（已添加重点）：

    每个表有 4096 列的硬性限制，但给定表的有效最大值可能会更少。确切的限制取决于几个相互作用的因素。

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

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

    ...

    单个存储引擎可能会施加额外的限制来限制表列数。例子：

    • InnoDB 最多允许 1000 列。
    • 16

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

    我基本上同意已经向你投掷的火焰。

    我不同意键值规范化。查询最终变得可怕。性能更差。

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

    也许更好的是决定最重要的十几个字段，将它们放入“主”表中。然后以某种逻辑方式对传感器进行分组，并将它们放入多个并行表中。通过适当的分组，您可能不必一直加入所有表。

    您是否为任何值编制索引？你需要搜索它们吗？可能您搜索日期时间？

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

    如果你需要索引一些——把它们放到'主表中。

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

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

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

    • 9

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

    这个设计有时间和地点。绝对地。规范化并不是所有问题的解决方案。

    • 4

相关问题