獲取多個表上的插入值/唯一鍵/ MySQL

由於一些糟糕的設計決策，我必須確保隨機字元串（無序列！）不能在三個不同的 MySQL 表（TableA、TableB、TableC）中出現兩次。因此，如果 TableB 或 TableC 中已經存在值“ABCD1234”，則不得將其插入 TableC。現在的任務是在其中一個表中插入幾個（1-100.000）個新鍵。

旁注：這些表具有project分隔不同邏輯分區的欄位。密鑰在每個分區/項目中必須“僅”是唯一的。如果我在下面寫下記錄號，那總是在一個分區/項目中。

第一次嘗試延遲錯誤

我曾經在客戶端（PHP）上檢索鍵的聯合列表以在插入之前檢查重複項。使用雜湊表，這非常有效，但是當我們達到 >2 mio 時。記錄，我們遇到了記憶體問題。由於我們有多個執行緒同時執行，PHP 程序的 RAM 使用量 (256 MB) 比 MySQL 數據庫 (4+ GB) 更受限制。

第二次試錯

我對子查詢（完全關閉性能）和 JOIN 進行了很多嘗試。

INSERT INTO TableA (`code`, `project`)
SELECT TemporaryTable t
LEFT JOIN TableA r1 ON ((t.`code` = r1.`code`) AND (r1.project = ...))
LEFT JOIN TableB r2 ON ((t.`code` = r2.`code`) AND (r2.project = ...))
LEFT JOIN TableB r3 ON ((t.`code` = r3.`code`) AND (r2.project = ...))
WHERE (r1.`code` IS NULL) AND (r2.`code` IS NULL) AND (r3.`code` IS NULL)

所有這些試驗都以必須將 100k 新密鑰與 2m 現有密鑰進行比較的設置結束……這非常非常慢。當使用 LEFT JOIN 過濾現有值時，我們正在討論這樣一個查詢的幾分鐘。

第三次試錯

所以下一個試驗是使用我無論如何都需要的臨時表，因為我需要支持 MySQL 5.7 而這不支持VALUES ROW()語法。

所以我創建了臨時表（在 上有一個索引code），插入了新聞值（要插入的候選人），然後執行了三個DELETE FROM TABLE查詢來刪除重複項：

DELETE FROM TemporaryTable 
WHERE `code` IN (
   SELECT r.`code` FROM TableA r WHERE (r.`project` = ...)
)

我該說什麼……它仍然非常緩慢。帶有子查詢的 DELETE 命令似乎效率低下。

下一次審判

唯一真正快速工作的是唯一索引(code, project)和 INSERT IGNORE。MySQL 似乎有一些神奇的優化，它比任何顯式 JOIN 至少快 10k 倍。

當然，重點是您只能在單個表中創建這樣的唯一索引。因此，本地結果是創建第四個TableX處理唯一性的表。

因此，新值將被插入（INSERT IGNORE）到 TableX 中，並且只有那些成功的值才會被插入到 TableA、TableB 或 TableC 中。

據我所知，這也是唯一不會失敗的解決方案，如果有多個這樣的插入同時執行（執行緒安全）。

這個解決方案仍然讓我有些頭疼：

1. 我如何知道哪些值已被接受（插入）並且現在可以用於 TableA？可以添加插入查詢的序列號，但這會在 TableX 中添加大量原本無用的數據。
2. 當從 TableA、TableB、TableC 中刪除數據時，是否有任何優雅的解決方案，使用 MySQL 外鍵等來避免 TableX 中的偽影？

而且，當然，我歡迎任何關於如何解決unique-over-multiple-tables 問題的替代建議。我們還考慮更改最初導致問題的糟糕設計決策……但這當然會與許多其他更改有關。而且我無法相信你不能使用 MySQL 的超高性能 UNIQUE + INSERT IGNORE 機制。最終是相同的任務。

很多表的唯一性。

這可以通過額外的表 + 觸發器來解決。像

CREATE TABLE tableABC (id VARCHAR(255) UNIQUE);
CREATE TRIGGER tr_bi_a BEFORE INSERT ON tableA 
FOR EACH ROW INSERT INTO tableABC VALUES (NEW.id);

[**DEMO**](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3a25e4e7128d385f1bb6a3a3e5278cd4)

如果可以更改此列值，則必鬚根據 BEFORE UPDATE 觸發器（刪除OLD.id和插入NEW.id）創建。如果可以從表中刪除行，那麼您必鬚根據 BEFORE DELETE 觸發器創建（刪除OLD.id）。

---

或者，您可以創建 BEFORE INSERT 觸發器，該觸發器將檢查NEW.id表中不存在的內容 - 但這可能不是並發安全的。

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