我有一張看起來像這樣的表：
------------- -------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
------------- -------------- ------ ----- --------- -------
| name | varchar(255) | NO | PRI | NULL | |
| timestamp1 | int | NO | | NULL | |
| timestamp2 | int | NO | | NULL | |
------------- -------------- ------ ----- --------- -------
該表中有大約 2.5 億行。我每天收到一次 csv，其中僅包含一個名稱列的大約 2.25 億行。我每天得到的 csv 中 99% 的名稱已經在資料庫中。所以我想要做的是對于所有已經存在的人，我將他們的timestamp1專欄更新為UNIX_TIMESTAMP(NOW()). 然后所有不在原始表中但在我添加到原始表的 csv 中的名稱。現在這就是我這樣做的方式：
DROP TEMPORARY TABLE IF EXISTS tmp_import;
CREATE TEMPORARY TABLE tmp_import (name VARCHAR(255), primary_key(name));
LOAD DATA LOCAL INFILE 'path.csv' INTO TABLE tmp_import LINES TERMINATED BY '\n';
UPDATE og_table tb SET timestamp1 = UNIX_TIMESTAMP(NOW()) WHERE og.name IN (SELECT tmp.name FROM tmp_import tmp);
DELETE FROM tmp_import WHERE name in (SELECT og.name FROM og_table og);
INSERT INTO og_table SELECT name, UNIX_TIMESTAMP(NOW()) AS timestamp1, UNIX_TIMESTAMP(NOW()) AS timestamp2 FROM tmp_import;
有人可能會猜測更新行需要很長時間，超過 6 小時或拋出錯誤。讀取資料需要 40 分鐘以上。我知道這主要是因為它正在創建一個索引，name當我沒有將它設定為主鍵時，讀取資料只需要 9 分鐘，但我認為擁有索引會加快操作速度。我已經嘗試了幾種不同的更新方式。我所擁有的以及以下內容：
UPDATE og_table og SET timestamp1 = UNIX_TIMESTAMP(NOW()) WHERE EXISTS (SELECT tmp.name FROM tmp_import tmp where tmp.name = og.name);
UPDATE og_table og inner join tmp_import tmp on og.name=tmp.name SET og.timestamp1 = UNIX_TIMESTAMP(NOW());
這兩種嘗試都沒有奏效。通常需要幾個小時，然后結束：
ERROR 1206 (HY000): 鎖總數超過鎖表大小
我正在使用InnoDB這些表，但沒有必要的外鍵，并且不一定需要該引擎的好處，所以我愿意嘗試不同的存盤引擎。
我已經瀏覽了很多帖子，但還沒有找到對我的情況有幫助的東西。如果我錯過了一個帖子，我道歉。
uj5u.com熱心網友回復：
如果name值相當長，您可以通過使用散列函式大大提高性能，例如MD5orSHA-1和 store&index only the hash。您可能甚至不需要所有 128 或 160 位。80 位部分應該足夠好，碰撞的可能性非常低。看到這個。
您可能要檢查的另一件事是您是否有足夠的 RAM。你的桌子有多大，你有多少記憶體？此外，這不僅僅是關于機器上有多少 RAM，而是有多少可用于 MySQL/InnoDB 的緩沖區快取。
你用的是什么盤？如果您使用的是旋轉磁盤 (HDD)，如果 InnoDB 需要不斷進行分散讀取，這可能是一個巨大的瓶頸。
還有許多其他事情可能會有所幫助，但我需要更多詳細資訊。例如，如果 CSV 中的名稱未排序，并且您的緩沖區快取大約是表大小的 10-20%，則通過分批拆分作業，您可能會獲得巨大的性能提升，以便每個批次中的名稱都接近足夠了（例如，首先處理所有以 'A' 開頭的名稱，然后處理以 'B' 開頭的名稱，等等）。為什么會有幫助？在不適合緩沖區快取的大索引（在 InnoDB 表中也實作為索引）中，如果您在索引周圍進行數百萬次讀取，則 DB 將需要不斷從磁盤讀取。但是，如果您在較小的區域上作業，則資料塊（頁面）只會被讀取一次，然后它們將保留在 RAM 中以供后續讀取，直到您完成該區域。 根據您的情況，可以輕松地將性能提高 1 或 2 個數量級。
uj5u.com熱心網友回復：
大更新（正如 Barmar 指出的那樣）需要很長時間。讓我們通過構建一個新表，然后將其交換到位來避免它。
首先，讓我澄清一下并提供一個最小的例子。
您不會洗掉任何行，對嗎？只是添加或更新行？
你有（在og_table）：
A 88 123
B 99 234
每日負荷 ( tmp_import) 表示
B
C
你要
A 88 123
B NOW() 234
C NOW() NULL
那是對的嗎？現在的代碼：
加載夜間資料并構建合并表：
LOAD DATA ... (name) -- into TEMPORARY tmp_import CREATE TABLE merge LIKE og_table; -- not TEMPORARY使用合并在一起的資料填充新表
INSERT INTO merge -- B and C (from the example): ( SELECT ti.name, FROM_UNIXTIME(NOW()), og.timestamp2 FROM tmp_import AS ti LEFT JOIN og_table AS USING(name) ) UNION ALL -- A: ( SELECT og.name, og.timestamp1, og.timestamp2 FROM og_table AS og LEFT JOIN tmp_import AS ti USING(name) WHERE ti.name IS NULL -- (that is, missing from csv) );將其交換到位
RENAME TABLE og_table TO x, merge TO og_table; DROP TABLE x;
獎勵： og_table只是非常短暫地“下降”（在 期間RENAME）。
一個可能的加速：name 在加載之前對 CSV 檔案進行排序。（如果這需要額外的步驟，那么該步驟的成本可能比沒有對資料進行排序的成本更糟糕。沒有足夠的資訊來預測。）
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/342625.html