如何在不鎖定 MySQL 的情況下更新（非常）大的表

我有一個大表（58+ 百萬條記錄），它代表第二個表中的兩條記錄（玩家和目標）之間的關係。

不幸的是，設計我們架構的人並沒有正確考慮問題，而是選擇使用使用者名來表示這種關係，而不是使用者記錄的數字 id。隨著事情的發展（就像他們通常做的那樣），我們的使用者名不再是一個有效的、唯一的玩家代表，所以我需要將這些關係轉換為使用數字 ID。

多虧了 Percona Toolkit，在沒有鎖定的情況下添加欄位很容易，它提供了可以在活動表上進行 ALTER 的 pt-online-schema-change。然而，填充表格可能會更棘手。

表格看起來像這樣（我已經剝離了不相關欄位的創建），兩個未填充的欄位是player_id和target_id：

CREATE TABLE `player_targets` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `player` varchar(20) NOT NULL,
 `player_id` int(10) unsigned DEFAULT NULL,
 `target` varchar(20) NOT NULL,
 `target_id` int(10) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=58000000 DEFAULT CHARSET=latin1;

CREATE TABLE 'player_uuids' (
 `id`int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(20) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=600000 DEFAUL CHARSET=latin1;

我打算用這樣的查詢填充這兩個新欄位：

UPDATE player_targets t
INNER JOIN player_uuids u1
 ON u1.username = t.player
INNER JOIN player_uuids u2
 ON u2.username = t.target
SET
 t.player_id = u1.id,
 t.target_id = u2.id
WHERE
 t.player_id IS NULL
 OR t.player_id IS NULL;

由於表儲存關係是 MyISAM，我對文件的解釋是 UPDATE 查詢將鎖定表，直到它完成所有行。由於表很大，這在實際環境中可能無法很好地工作。

最好的方法是什麼？編寫腳本來迭代批量關係？將表引擎更改為 InnoDB（表讀取量很大，我認為這是 MyISAM 的原因）？

您可以在循環語句中刪除或更新一行。在這種情況下，不會有阻塞，但它的工作速度會慢一些。在過程中使用限制 1 執行此操作：

 PROCEDURE myProcedure()
   BEGIN
   DECLARE c int; -- to calculated affected rows
   set c = 0;

     ml:LOOP
         UPDATE player_targets t
         INNER JOIN player_uuids u1
          ON u1.username = t.player
         INNER JOIN player_uuids u2
          ON u2.username = t.target
         SET
          t.player_id = u1.id,
          t.target_id = u2.id
         WHERE
          t.player_id IS NULL
          OR t.player_id IS NULL 
          LIMIT 1;

         -- check if the loop has completed    
         IF  ROW_COUNT() = 0 THEN
             LEAVE ml;
         END IF;    

         set c = c + 1;

         IF c MOD  100  = 0 THEN
             SELECT CONCAT(c, ' row(s) updated');
         END IF;
     END LOOP;  

     SELECT CONCAT(c, ' row(s) updated; The statement has completed');
   END

並呼叫程序：

Call myProcedure();

是的，迭代批次。 這個部落格提供了一些關於如何做的建議。

它體現在如何將 big DELETEs 分塊，但這些原則適用於 big UPDATEs，就像你的一樣。

它向您展示瞭如何使用（通常）PRIMARY KEY和使用LIMIT來決定下一個塊。沒有步驟必須掃描整個表，因為這會破壞分塊的目的。

引用自：https://dba.stackexchange.com/questions/90811