MySQL 如何 Delete 大量資料

MySQL 若有大量的資料需要 DELETE，要怎麼做會比較快呢？

MySQL 如何 Delete 大量資料

MySQL 在砍大量資料的時候，要注意是使用 InnoDB 還是 MYISAM，相關設定和操作不一樣。

可以先參考這幾份官方文件

• MySQL :: 13.2.2 DELETE Statement
• MySQL :: 8.2.4.3 Optimizing DELETE Statements
  • The time required to delete individual rows in a MyISAM table is exactly proportional to the number of indexes. To delete rows more quickly, you can increase the size of the key cache by increasing the key_buffer_size system variable.
  • To delete all rows from a MyISAM table, TRUNCATE TABLE tbl_name is faster than DELETE FROM tbl_name
• MySQL :: Re: Faster/more reliable way to delete millions of rows?
  • was increasing the innodb_buffer_pool_size to around 2GB (I have 8GB RAM), or '2155872256'as the value
• 官方文件有說明幾個重點：
  • InnoDB：設定檔加大 innodb_buffer_pool_size 的值
  • MyISAM：設定檔加大 key_buffer_size 的值
  • 若要將 Table 的資料清空，使用 TRUNCATE TABLE table_name 會比 DELETE FROM table_name 還快

這篇有更多的技巧：MySQL Big DELETEs - 有空建議仔細研讀此篇

• 此篇是用 DELETE FROM tbl WHERE ts < CURRENT_DATE() - INTERVAL 30 DAY 這個語法當案例
• 若要 By 時間刪除，可以考慮使用 MySQL Partitioning：MySQL :: 22 Partitioning
• 一次要砍多筆資料 (一次 1000筆，用 range 的方式刪除)
  • Have a 'reasonable' size for innodb_log_file_size.
  • Use AUTOCOMMIT=1 for the session doing the deletions.
  • Pick about 1000 rows for the chunk size.
• 砍完資料後，要 Release 硬碟空間，需要做 OPTIMIZE TABLE
  • 若設定 innodb_file_per_table = 0，：dump ALL tables, remove ibdata, restart, and reload
  • 若設定 innodb_file_per_table = 1，OPTIMIZE TABLE 要給硬碟2倍的空間
    • 可以參考 pt-online-schema-change
    • Percona's package to do big deletes, etc: pt-archiver

我自己刪除是對要刪除的資料先先排序過，在做下述：

• 一次刪除 1000筆 (沒辦法 by range 的話，就用 IN (PK by 順序排列，寫入是照順序寫的，希望硬碟尋找速度也比較快)
• 另一隻對同樣 WHERE 語法先做一次 SELECT (Cache warm up)