優化大記錄刪除程序

我正在創建一個數據庫，它反映了我們的生產數據庫，但更輕巧且匿名 - 用於本地開發目的。

確保我們有足夠的數據作為工程團隊進行操作，我將刪除所有updated_at日期設置為一年多以前的客戶。簡單的過程是保留新使用者，但 bin 舊使用者或非活動使用者。

為此，我創建了一個儲存過程。

DELIMITER //
CREATE PROCEDURE delete_old_customers()
BEGIN
   SET @increment = 0;
   customer_loop: LOOP

       DELETE FROM customers 
       WHERE id BETWEEN @increment AND @increment+999
       AND updated_at < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR);

       IF @increment > (SELECT MAX(id) FROM customers) THEN
           LEAVE customer_loop;
       END IF;

       SET @increment = @increment + 1000;

   END LOOP customer_loop;
END //
DELIMITER ;

CALL delete_old_customers();

DROP PROCEDURE delete_old_customers;

因此，此程序將刪除分成 1000 個組，並執行直到沒有更多客戶需要處理。

我執行這樣的程序：

mysql "$MYSQLOPTS" devdb < ./queries/customer.sql

其中$MYSQLOPTS指的是具有以下選項的 my.cnf 文件：

[mysqld]
innodb_buffer_pool_size = 6G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_flush_log_at_trx_commit = 0
query_cache_size = 0

問題在於，由於此表具有 FK 和引用，此過程可能需要長達 3 小時才能刪除約 80 萬使用者；當然，隨著時間的推移，這只會增長。

這是在四核、8GB RAM、Digital Ocean Droplet 上執行的；所以我只有有限的工作方式。

因此，鑑於此，我很想有機會開始優化此過程以提高其速度，但我不確定從哪裡開始。我也對實現相同目標的替代方法持開放態度。

我更喜歡下一個策略：在每個插入記錄上填滿表的儲存常式也會刪除一些過期的記錄。這看起來像這樣：

BEGIN
-- lot of code --
INSERT INTO table ...
-- lot of code --
DELETE FROM table AS w WHERE w.expire < NOW() LIMIT 3;
END

插入/刪除比率設置為 1:3 只是為了確保即使在傳入數據速率由於每日/每週/每月波動而變低時也能獲得合理的刪除率。對於過期記錄計數低的已建立基地是可以接受的。如果要執行初始清理，則必須將 設置為LIMIT不會影響伺服器性能的值。

如果您的傳入數據速率較低，則可以創建特殊的常式 ad hoc：

CREATE PROCEDURE table_cleanup()    
BEGIN
main: REPEAT
 DELETE FROM table AS w WHERE w.expire < NOW() LIMIT 1000;
 UNTIL row_count() = 0 END REPEAT main;
END

HugeDELETE會被拆分成一時間鎖不了表的一系列小的。

1. DATABASE用相同的表建立一個新的。
2. 關閉 FK 檢查。
3. 複製 200K 行。（考慮以 1K 為單位進行。）
4. 複製那些 200K 所需的行。（也可能受益於分塊。）
5. 打開 FK 檢查。

注意：每個塊都應該是COMMITted你去的。（或者，有autocommit=1和不要打擾BEGINand COMMIT。）如果有任何崩潰，放棄新的DATABASE並重新開始。

這些步驟可以在目標開發機器（或第三台機器）上執行，以從生產機器中提取數據。

我同意您提供的分塊程式碼。這是另一個版本（雖然針對（DELETEing`）：http: //mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

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