MySQL DELETE 因大量行而變得異常緩慢

當對 Prices 表中的大量行執行 DELETE 時，DELETE 會逐漸變慢。如果刪除 15,000 行，它將在大約 15 秒內執行。20K 行需要 3 或 4 分鐘。40,000 行需要 15 分鐘，100,000 行執行一個多小時。

下面的 After DELETE 觸發器使用可用價格類型的計數更新 Items 表。這用於計算價格以加快生產過程。

兩個表都是InnoDB，我把innodb_buffer_pool_size更新為4G，沒有效果。

我已經驗證所有 SQL 語句都在使用索引。在 Prices 表上有一個 PriceType + ItemID 索引，ItemID 是 Items 表的主鍵。

不幸的是，這個模式是由另一個應用程序決定的，我不能修改表結構。我可以修改觸發器、索引等。無法更改該應用程序以直接更新 Items 表中的計數。

BEGIN

   DECLARE iPriceTypeA INT;
   DECLARE iPriceTypeB INT;
   DECLARE iPriceTypeC INT;

   SET iPriceTypeA = (SELECT COUNT(*) FROM Prices WHERE PriceType='A' AND ItemID=OLD.ItemID),
       iPriceTypeB = (SELECT COUNT(*) FROM Prices WHERE PriceType='B' AND ItemID=OLD.ItemID),
       iPriceTypeC = (SELECT COUNT(*) FROM Prices WHERE PriceType='C' AND ItemID=OLD.ItemID);

   UPDATE Items 
       SET PriceTypeA = iPriceTypeA,
           PriceTypeB = iPriceTypeB,
           PriceTypeC = iPriceTypeC,
       WHERE ItemID = OLD.ItemID;

END

這似乎不會造成問題，但是當刪除 120K 行時，伺服器會在幾個小時內無法使用。為什麼這個查詢會隨著更多的行而變得指數級地變慢？

編輯：使用價格表架構更新

ItemCode varchar(30)
PriceType char(1)
Method char(1)
Factor decimal(7,2)

編輯：

我仍然很困惑為什麼這個觸發器會導致刪除越慢記錄數越多。正如我所說，如果我刪除觸發器呼叫，刪除速度非常快。我真的很想深入了解性能問題。

為了讓事情現在正常工作，我的解決方案是切換到 MyISAM，因為該表僅用於我們應用程序中的選擇。

使用 InnoDB，必須保留已刪除的行，以防出現崩潰或其他原因回滾操作。如您所見，這是昂貴的，並且可能會隨著計數的增加而變得更加昂貴。

計劃 A：在較小的塊中和COMMIT每個塊之後刪除。（類似地，分塊應該應用於更新。）

計劃 B、C、D、… 查看更多提示

如果沒有在哪裡

沒有WHERE？最好創建一個新表，然後用RENAME TABLE原子方式將新表交換到位。並以DROP TABLE. 沒有停機時間；沒有代價高昂的刪除。

查詢重寫

如果您執行的是 5.7，那裡有一個新功能可以讓您“重寫”查詢以適應各種討厭的問題。 更多討論。您可能可以將沒有 where 的刪除轉換為DROPand CREATE。（這不如之前的建議那麼好。）

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