搜尋

首頁  >  問答  >  主體

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

我有一個 MYSQL InnoDB 表table,其中包含以下列(表格和列名稱已更改):

其中 rel_ab 是描述給定日期 2 個變數 var_avar_b 之間關係的欄位。 (var_avar_b 引用不同的表)

資料每天批次上傳,每天總計約 700 萬行。問題是,僅僅幾週後,上傳每個新的每日批次就開始需要幾個小時。顯然我們需要改進我們的表格設計。以下是我們表格的一些其他詳細資訊。

因此,我計劃至少執行以下操作之一:

我知道第一個解決方案可能會威脅資料完整性,而第二個解決方案會使我們的架構變得混亂。以我有限的經驗,我也從未聽說過第二種選擇,也無法在網路上找到這種設計的任何範例。這些選項中的任何一個都是明智的解決方案嗎?兩者都會提高上傳速度並減少磁碟使用,但也都有其缺點。否則,還有哪些方法可以提高上傳速度?

編輯:我的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"

P粉665679053P粉665679053442 天前505

全部回覆(2)我來回復

  • P粉781235689

    P粉7812356892023-09-10 13:05:25

    有一些潛在的解決方案可以幫助您提高 MySQL 表的上傳速度:

    刪除 var_a 和 var_b 上的索引:由於您沒有使用這些索引來加速查詢,因此刪除它們可以幫助加快上傳過程。但是,如果您使用外鍵約束,通常建議在屬於外鍵的欄位上保留索引。

    按日期對錶進行分區:分區有助於提高查詢效能,因為它允許資料庫僅掃描給定查詢的相關分區。但是,它也會使維護和備份變得更加複雜,如果您的查詢已經表現良好,則可能沒有必要。

    使用批次插入方法:您可以嘗試使用批次插入方法,例如 LOAD DATA INFILE 或 MySQL 批次插入 API,而不是使用 df.to_sql 插入單獨的行。這比單獨插入要快,特別是如果您可以批量上傳資料而不是一次一行。

    使用不同的壓縮演算法:您目前正在使用 zlib 壓縮,但還有其他壓縮演算法可能對您的資料更快或更有效。您可以嘗試嘗試不同的壓縮選項,看看它們是否可以提高上傳速度。

    增加伺服器資源:如果您有預算和資源,升級伺服器硬體或增加伺服器數量可能有助於提高上傳速度。這可能不是每個人都可行的選擇,但如果您已經用盡其他選擇,則值得考慮。

    就您建議的選項而言,刪除外鍵約束可能會導致資料完整性問題,因此我不推薦這種方法。如果您的查詢已經遇到效能問題,則按日期分割可能是一個很好的解決方案,但如果您的查詢已經快速運行,則可能沒有必要。

    回覆
    0
  • P粉098979048

    P粉0989790482023-09-10 11:03:23

    要加快上傳速度,請將其刪除。說真的,如果您所做的唯一一件事就是準確地獲取某個日期文件中的內容,為什麼要將資料放入表中? (您的評論指出單個文件實際上是幾個文件。首先將它們組合起來可能是一個好主意。)

    如果您確實需要表中的數據,讓我們討論這些...

    • 在確定索引之前,我們必須查看所有主要查詢。
    • 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 倍收縮是典型的。

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

    回覆
    0
  • 取消回覆