Mysql
從 mysql innodb 中刪除大量數據
我需要刪除我的生產數據庫中的大量數據,該數據庫的大小約為 100GB。如果可能的話,我想盡量減少停機時間。
我的刪除選擇標準很可能是
從 USER.ID=5 且 UPDATED_AT<100 的文章中刪除 *
刪除它的最佳方法是什麼?
- 建立索引?
- 編寫一個順序腳本,通過一次分頁 1000 行來刪除?
您可以通過防止表被鎖定來最小化幾乎所有的停機時間。
使用 a
SELECT INSERT
將您想要的數據遷移到臨時表。重命名表。
您需要在執行
INSERTS
期間考慮任何SELECT INSERT
問題。最後,刪除包含不需要的數據的表。
請注意,如果您沒有
innodb_file_per_table
在配置中啟用分配的磁碟空間將不會被釋放。此外,您的應用程序和具體情況需要特別考慮。
這是我使用這種方法編寫的程序..
# Temp table, recreating forty_first_transaction_ # CREATE TABLE working_table_temp_ LIKE working_table_; # Increment if there's an auto_increment field. # ALTER TABLE working_table_temp_ AUTO_INCREMENT = 15000000; # Testing inserts and select while this query was running resulted success. # Verified no active lock. # INSERT INTO working_table_temp_ SELECT * FROM working_table_ WHERE id > $NUM; RENAME TABLE working_table_ TO working_table_old_; RENAME TABLE working_table_temp_ TO working_table_; # Verify that all rows were caught # # The last row < 15000000 in both tables should be identical. # SELECT * FROM working_table_ where id < 15000000 order by id desc limit 5; SELECT * FROM working_table_old_ where id < 15000000 order by id desc limit 5; # If not, we need to move them ! # # This query will need to be modified. # INSERT INTO working_table_ SELECT * FROM working_table_old_ WHERE id > 138376577; # Verify application functionality, if necessary. # # LAST CHANCE FOR BACKOUT !!! # # Once verified, get rid of the old data to free up some space. # DROP TABLE working_table_old_; ^D df -h ## BACKOUT ## RENAME TABLE working_table_ TO working_table_new_; RENAME TABLE working_table_old_ TO working_table_;