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)

作者: Tsung

對新奇的事物都很有興趣, 喜歡簡單的東西, 過簡單的生活.

發表迴響

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料