为每个日期的相同数据创建不同的表是否明智？

Question

我有一个 MYSQL InnoDB 表table，其中包含以下列（表和列名称已更改）：

• 日期（PK、日期）
• var_a（PK、FK、INT）
• var_b（PK、FK、INT）
• rel_ab（十进制）

其中 rel_ab 是描述给定日期 2 个变量 var_a 和 var_b 之间关系的列。 （var_a 和 var_b 引用不同的表）

数据每天批量上传，每天总计约 700 万行。问题是，仅仅几周后，上传每个新的每日批次就开始需要几个小时。显然我们需要改进我们的表格设计。以下是我们表格的一些其他详细信息。

• 我们使用COMPRESSION="zlib"。
• 除了复合主键之外，我们还在 var_a 和 var_b 列上建立了外键所需的索引。
• 当我们从此表中提取数据时，始终使用针对给定日期 的查询 SELECT * FROM table WHERE date = 。选择仅需几分钟。
• 我们（几乎可以肯定）永远不会有理由从 var_a 和 var_b 引用的表中删除条目。
• 数据由 pandas 函数 df.to_sql('temp', con, if_exists='replace', index=False, method='multi') 上传，我们在其中插入忽略 < code>temp 到 table，然后删除 temp。

因此，我计划至少执行以下操作之一：

• 删除列 var_a 和 var_b 上的外键约束，并依靠数据上传过程来正确完成所有操作。这是因为在我们的用例中，这两个索引实际上都没有提高查询速度。
• 将表格划分为每个日期都有不同的表格。例如，我有一个名为 table_230501 的表，其中包含 var_a、var_b、rel_ab 列。这是因为我们一次只选择一个日期。

我知道第一个解决方案可能会威胁数据完整性，而第二个解决方案会使我们的架构变得混乱。以我有限的经验，我也从未听说过第二种选择，也无法在网上找到这种设计的任何示例。这些选项中的任何一个都是明智的解决方案吗？两者都会提高上传速度并减少磁盘使用，但也都有其缺点。否则，还有哪些其他方法可以提高上传速度？

编辑：我的SHOW CREATE TABLE应该看起来像

CREATE TABLE table (
  date date NOT NULL,
  var_a int NOT NULL,
  var_b int NOT NULL,
  rel_ab decimal(19,16) NOT NULL,
  PRIMARY KEY (date,`var_a`,`var_b`),
  KEY a_idx (var_a),
  KEY b_idx (var_b),
  CONSTRAINT a FOREIGN KEY (var_a) REFERENCES other_table_a (var_a) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT b FOREIGN KEY (var_b) REFERENCES other_table_b (var_b) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION="zlib"

Answer 1

有一些潜在的解决方案可以帮助您提高 MySQL 表的上传速度：

删除 var_a 和 var_b 上的索引：由于您没有使用这些索引来加速查询，因此删除它们可以帮助加快上传过程。但是，如果您使用外键约束，通常建议在属于外键的列上保留索引。

按日期对表进行分区：分区有助于提高查询性能，因为它允许数据库仅扫描给定查询的相关分区。但是，它也会使维护和备份变得更加复杂，如果您的查询已经表现良好，则可能没有必要。

使用批量插入方法：您可以尝试使用批量插入方法，例如 LOAD DATA INFILE 或 MySQL 批量插入 API，而不是使用 df.to_sql 插入单独的行。这比单独插入要快，特别是如果您可以批量上传数据而不是一次一行。

使用不同的压缩算法：您当前正在使用 zlib 压缩，但还有其他压缩算法可能对您的数据更快或更有效。您可以尝试尝试不同的压缩选项，看看它们是否可以提高上传速度。

增加服务器资源：如果您有预算和资源，升级服务器硬件或增加服务器数量可能有助于提高上传速度。这可能不是每个人都可行的选择，但如果您已经用尽其他选择，则值得考虑。

就您建议的选项而言，删除外键约束可能会导致数据完整性问题，因此我不推荐这种方法。如果您的查询已经遇到性能问题，则按日期分区可能是一个很好的解决方案，但如果您的查询已经快速运行，则可能没有必要。

Answer 2

要加快上传速度，请将其删除。说真的，如果您所做的唯一一件事就是准确获取某个日期文件中的内容，为什么要将数据放入表中呢？ （您的评论指出单个文件实际上是几个文件。首先将它们组合起来可能是一个好主意。）

如果您确实需要表中的数据，让我们讨论这些...

• 在确定索引之前，我们必须查看所有主要查询。
• PK 中的列顺序对于加载和查询都很重要。
• 分区可能有助于加载，但不太可能有助于查询。例外：您会删除“旧”数据吗？
• 请提供显示创建表；您提供的内容可能遗漏了一些细微的内容。
• 加载是如何完成的？一个巨大的加载数据？希望不是一次插入一行。我不知道熊猫是如何工作的。 （也不知道其他 99 个“简化”MySQL 访问的软件包是如何工作的。）请了解它的幕后功能。您可能必须绕过 Pandas 才能获得更好的性能。批量加载的速度至少是逐行加载的 10 倍。
• 我在加载时还没有看到需要临时表。也许。如果您删除 FK（如您所建议的），您可以执行查询来验证其他表中 var_a 和 var_b 的存在。那就是“模拟FK”。
• 如果可行，请根据 PK 对传入数据进行排序。 （这可能是经济增长缓慢的根源。）
• 有辅助键吗？它们会影响加载速度。
• 我认为您的 FK 暗示了其他表的索引。
• 您是否正在向其他表添加新行？
• “rel_ab (DECIMAL)”——多少位小数？确切的声明是什么？如果是某种测量，您是否考虑过FLOAT？
• 现在其他表中有很多行。也就是说，您真的需要一个 4 字节的 INT 来引用它们吗？切换到 3 字节 MEDIUMINT [UNSIGNED] 每天至少可以节省 7MB。
• 您如何处理该 SELECT 中的 700 万行？
• 无压缩。在InnoDB中效率很低。 4 列中只有一列可能是可压缩的。压缩需要额外的buffer_pool_space。压缩会占用大量CPU。对于 InnoDB，2 倍收缩是典型的。

多个“相同”的表总是不明智的。一张桌子总是更好。但是，正如上面所建议的，零表仍然更好。