刪除大量行的最佳方法，知道欄位不刪除

來自 Rails 背景，我有一個大型生產數據庫，我有一個副本，我只需要來自 2000 多家公司中的 4 家的記錄。我正在嘗試刪除除屬於 4 的行之外的所有行，並且我知道我擁有它的方式不是最佳的。

DELETE FROM appointments 
WHERE (appointments.company_id NOT IN (6, 753, 785, 1611))

另一個範例是當我必須刪除 company_id 位於關聯表上的表上的記錄時：

DELETE FROM mappings 
WHERE mappings.id IN (SELECT mappings.id 
                     FROM code_mappings 
                     INNER JOIN codes ON codes.remote_id = mappings.code_remote_id 
                     WHERE (codes.company_id NOT IN (6, 753, 785, 1611)))

關於第一個表，appointments請確保您在company_id列上有索引。

關於mappings表，使用EXISTS而不是IN可能會產生更好的性能。您可以按如下方式重新編寫查詢：

DELETE FROM mappings AS m
WHERE EXISTS (  SELECT 1
               FROM code_mappings AS cm
                 INNER JOIN codes AS c
                   ON c.remote_id = cm.code_remote_id
               WHERE 
               (
               c.company_id NOT IN (6, 753, 785, 1611)
               AND cm.id = m.id
               )
)

在上述查詢中，您還將受益於mappings和code表的索引。

創建索引的文件是 @ https://www.postgresql.org/docs/current/static/sql-createindex.html。在您的情況下，您可以在相關表上創建索引，如下所示：

CREATE INDEX company_id_idx ON appointments (company_id);

CREATE INDEX remote_id_company_id_idx ON codes (remote_id, company_id);

CREATE INDEX code_remote_id_id_idx ON code_mappings (code_remote_id, id);

-- If you don't already have a primary key OR index on `id` column in the `mappings` table, then create one:

ALTER TABLE mappings ADD PRIMARY KEY (id);
-- Choose primary key, or index: CREATE INDEX id_idx ON mappings (id);

我覺得你很好，老實說。您可以隨時深入分析此處建議的各種選項的查詢計劃。

例如，EXISTS 謂詞（或 NOT EXISTS）的性能優勢來自於讓查詢優化器選擇如何使用索引，並且在子查詢最簡單時效果最佳：

EXISTS (select * from blah where outertable.key = key)

在這種情況下，您正在處理非常低的選擇性。您實際上是在說刪除所有內容！好吧，除了這四個，別管他們。

查詢優化器不會選擇使用您的索引，因為沒有任何意義。它將進行表掃描。如果它確實選擇了一個索引（它不會），它實際上會表現得更差（可能）。

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