我有一個 MYSQL InnoDB 表table
,其中包含以下列(表格和列名稱已更改):
其中 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
引用的表中刪除條目。 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"
P粉7812356892023-09-10 13:05:25
有一些潛在的解決方案可以幫助您提高 MySQL 表的上傳速度:
刪除 var_a 和 var_b 上的索引:由於您沒有使用這些索引來加速查詢,因此刪除它們可以幫助加快上傳過程。但是,如果您使用外鍵約束,通常建議在屬於外鍵的欄位上保留索引。
按日期對錶進行分區:分區有助於提高查詢效能,因為它允許資料庫僅掃描給定查詢的相關分區。但是,它也會使維護和備份變得更加複雜,如果您的查詢已經表現良好,則可能沒有必要。
使用批次插入方法:您可以嘗試使用批次插入方法,例如 LOAD DATA INFILE 或 MySQL 批次插入 API,而不是使用 df.to_sql 插入單獨的行。這比單獨插入要快,特別是如果您可以批量上傳資料而不是一次一行。
使用不同的壓縮演算法:您目前正在使用 zlib 壓縮,但還有其他壓縮演算法可能對您的資料更快或更有效。您可以嘗試嘗試不同的壓縮選項,看看它們是否可以提高上傳速度。
增加伺服器資源:如果您有預算和資源,升級伺服器硬體或增加伺服器數量可能有助於提高上傳速度。這可能不是每個人都可行的選擇,但如果您已經用盡其他選擇,則值得考慮。
就您建議的選項而言,刪除外鍵約束可能會導致資料完整性問題,因此我不推薦這種方法。如果您的查詢已經遇到效能問題,則按日期分割可能是一個很好的解決方案,但如果您的查詢已經快速運行,則可能沒有必要。
P粉0989790482023-09-10 11:03:23
要加快上傳速度,請將其刪除。說真的,如果您所做的唯一一件事就是準確地獲取某個日期文件中的內容,為什麼要將資料放入表中? (您的評論指出單個文件實際上是幾個文件。首先將它們組合起來可能是一個好主意。)
如果您確實需要表中的數據,讓我們討論這些...
顯示建立表格
;您提供的內容可能遺漏了一些細微的內容。 載入資料
?希望不是一次插入一行。我不知道熊貓是如何運作的。 (也不知道其他 99 個「簡化」MySQL 存取的軟體包是如何運作的。)請了解它的幕後功能。您可能必須繞過 Pandas 才能獲得更好的性能。批次加載的速度至少是逐行加載的 10 倍。 FLOAT
? MEDIUMINT [UNSIGNED]
每天至少可以節省 7MB。 多個「相同」的表總是不明智的。一張桌子總是比較好。但是,正如上面所建議的,零表仍然更好。