為每個日期的相同資料建立不同的表格是否明智？

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

多個「相同」的表總是不明智的。一張桌子總是比較好。但是，正如上面所建議的，零表仍然更好。