根據不同的表格「過濾」巨大的 MariaDB/Mysql 表

Question

在我的 mariaDB 資料庫中處理大型資料集。我有兩個表，其中表 A 包含 5700 萬行，表 B 包含大約 500 行。表 B 是與表 A 中的列相關的 ids 的子集。我想刪除 A 中沒有相應 ID 的所有行表B.

範例表 A：

classification_id	姓名
20	梅賽德斯
30	川崎
80	徠茲
70	惠普

範例表B：

classification_id	類型
20	汽車
30	自行車
40	公車
50	船

因此，在此範例中，表 A 中的最後兩行將被刪除（或將建立僅包含前兩行的鏡像表，這也可以）。

我嘗試使用內部聯接執行第二個查詢，但此查詢花了幾分鐘才給出記憶體不足異常。

關於如何解決這個問題有什麼建議嗎？

Answer 1

由於您說過濾表包含相對較少的行數，因此最好的選擇是創建一個單獨的表，其中包含與原始表A 相同的列以及與您的匹配的行criteria ，然後替換原始表並刪除它。另外，對於如此數量的 ID，您可能想要使用 WHERE IN () 而不是聯接 - 只要您在那裡使用的欄位已建立索引，它通常會方式 更快。將所有內容放在一起：

CREATE TABLE new_A AS
  SELECT A.* FROM A 
  WHERE classification_id IN (SELECT classification_id FROM B);
RENAME TABLE A TO old_A, new_A to A;
DROP TABLE old_A;

需要注意的事項：

• 備份您的資料！並在執行 DROP TABLE 之前徹底測試查詢。您不想因為 StackOverflow 上的隨機答案而丟失 57M 行資料。
• 如果 A 有任何索引或外鍵，這些將不會被複製 - 因此您必須手動重新建立它們。我建議先執行 SHOW CREATE TABLE A 並記下其結構。或者，您可以考慮使用SHOW CREATE TABLE A 的輸出作為範本明確建立表格new_A，然後執行INSERT INTO new_A SELECT ... 而不是CREATE TABLE new_A AS SELECT ... 並在此之後使用相同的查詢。

Answer 2

試試這個：

delete from "table A" where classification_id not in (select classification_id from "table B");