Ist es sinnvoll, für jedes Datum unterschiedliche Tabellen für dieselben Daten zu erstellen?

Question

Ich habe eine MYSQL InnoDB-Tabelletable mit den folgenden Spalten (Tabellen- und Spaltennamen geändert):

• Datum (PK, Datum)
• var_a (PK, FK, INT)
• var_b (PK, FK, INT)
• rel_ab(dezimal)

wobei rel_ab 是描述给定日期 2 个变量 var_a 和 var_b 之间关系的列。 （var_a 和 var_b sich auf verschiedene Tabellen bezieht)

Daten werden jeden Tag stapelweise hochgeladen, insgesamt etwa 7 Millionen Zeilen pro Tag. Das Problem bestand darin, dass das Hochladen jedes neuen täglichen Stapels bereits nach wenigen Wochen Stunden dauerte. Natürlich müssen wir unser Tischdesign verbessern. Hier finden Sie einige zusätzliche Details zu unserem Formular.

• Wir verwenden COMPRESSION="zlib".
• Zusätzlich zum zusammengesetzten Primärschlüssel erstellen wir auch den für den Fremdschlüssel erforderlichen Index in der Spalte var_a 和 var_b.
• Wenn wir Daten aus dieser Tabelle extrahieren, verwenden Sie sie immer für ein bestimmtes Datum 的查询 SELECT * FROM table WHERE date = . Die Auswahl dauert nur wenige Minuten.
• Wir werden (mit ziemlicher Sicherheit) nie einen Grund haben, einen Eintrag aus der Tabelle zu löschen, auf die var_a 和 var_b verweist.
• Die Daten werden von der Pandas-Funktion df.to_sql('temp', con, if_exists='replace', index=False, method='multi') hochgeladen, wobei wir „ignorieren“ einfügen temp< /code> auf df.to_sql('temp', con, if_exists='replace', index=False, method='multi') 上传，我们在其中插入忽略 < code>temp 到 table，然后删除 temp, dann temp löschen.

Also habe ich vor, mindestens eines der folgenden Dinge zu tun:

• Entfernen Sie die Fremdschlüsselbeschränkung für die Spalte var_a 和 var_b und verlassen Sie sich darauf, dass der Daten-Upload-Prozess alles richtig macht. Dies liegt daran, dass in unserem Anwendungsfall keiner der beiden Indizes die Abfragegeschwindigkeit tatsächlich verbessert.
• Teilen Sie die Tabelle für jedes Datum in verschiedene Tabellen auf. Ich habe zum Beispiel eine Kolumne namens table_230501 的表，其中包含 var_a、var_b、rel_ab. Dies liegt daran, dass wir jeweils nur ein Datum auswählen.

Ich weiß, dass die erste Lösung die Datenintegrität gefährden kann und die zweite Lösung unsere Architektur durcheinander bringen wird. Aufgrund meiner begrenzten Erfahrung habe ich auch noch nie von der zweiten Option gehört und kann online keine Beispiele für dieses Design finden. Sind diese Optionen eine sinnvolle Lösung? Beide erhöhen die Upload-Geschwindigkeit und reduzieren die Festplattennutzung, haben aber auch ihre Nachteile. Welche anderen Möglichkeiten gibt es sonst, die Upload-Geschwindigkeit zu erhöhen?

EDIT: Meins SHOW CREATE TABLE sollte so aussehen

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 倍收缩是典型的。

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