从MySQL innodb中删除大量的数据

我需要在我的生产数据库中删除大量的数据,这个数据库大小为100GB。 如果可能的话,我想尽量减less我的宕机时间。

我的删除select标准可能是

DELETE * FROM POSTING WHERE USER.ID = 5 AND UPDATED_AT <100

什么是删除它的最好方法?

  • build立一个索引?
  • 写一个连续的脚本,通过每次行1000分页删除?

您可以通过防止表被locking来将几乎所有停机时间降到最低。

使用SELECT INSERT将所需数据迁移到临时表。

重命名表。

SELECT INSERT运行期间,您将需要考虑任何INSERTS

最后,删除包含您不想要的数据的表。

请注意,如果您的configuration中未启用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_;