ApsaraDB RDS：AliSQL大量資料刪除最佳實務

問題描述

在資料庫營運中，刪除表中部分資料、清空表或刪除整個表是常見的操作。對於小規模的資料集，這些操作對資料庫執行個體的影響可以忽略。然而，當涉及數百萬乃至上億行記錄，或者資料檔案達到幾百GB甚至數TB時，這類操作可能會引發如下嚴重問題。

執行個體卡頓或HA切換

• 問題1：Binlog提交階段，在單個大事務中刪除大量資料時，產生的臨時Binlog檔案可能達到數十GB甚至數百GB。提交這些Binlog時會阻塞其他寫事務，導致短暫卡頓或長時間延遲，嚴重時會因HA探測逾時引發主備切換。

• 問題2：AHI清理階段，InnoDB需要遍曆Buffer Pool清理相關AHI記錄，此過程需持有全域鎖dict_sys->mutex，耗時較長。詳情請參見MySQL官方說明（MySQL Bugs: #91977: Dropping Large Table Causes Semaphore Waits; No Other Work Possible）。

• 問題3：檔案刪除階段，大表檔案的刪除可能導致執行個體瞬間及後續一段時間內IO資源被完全佔用，影響正常請求的處理。

執行個體抖動

問題4：檔案刪除階段，InnoDB會遍曆Buffer Pool以清理該表的相關頁面。若buffer_pool_size非常大，清理過程將耗時較長，會長時間獨佔單個Buffer Pool的Mutex，阻礙其他事務正常讀寫頁面，進而引起執行個體效能波動。

解決方案

參數調整

刪除資料操作

刪除部分資料

分批操作，建議單次刪除的資料量不超過一萬行，不要一次性刪除大量資料。

刪除所有資料

• MySQL 5.7版本推薦使用RENAME + DROP操作

  未採用TRUNCATE是因為其在5.7中通過清空原資料表空間實現，而非重建新表。

  # 重建一張相同的表
  CREATE TABLE t1_new LIKE t1;
  # 檢查重建表的表定義是否符合預期
  SHOW CREATE TABLE t1_new;
  # 新表和老表交換
  RENAME TABLE t1 TO t1_bak, t1_new TO t1;
  # 刪除原表
  DROP TABLE t1_bak;
  # 檢查資料表空間檔案清理進展
  SELECT * FROM information_schema.innodb_purge_files;

  說明

  • 在MySQL 5.7版本中，DDL操作不具備原子性，執行個體崩潰可能導致檔案殘留。

  • RENAME過程可能觸發重新命名檔案失敗導致執行個體崩潰問題，詳情請參見MySQL官方說明（bug#108087 InnoDB：crash because of failure to rename file in DDL），此問題已在AliSQL 5.7 20220731及更高版本中修複。

• MySQL 8.0版本推薦使用RENAME + DROP或TRUNCATE操作

  • 使用RENAME + DROP參考如下SQL：

    # 重建一張相同的表
    CREATE TABLE t1_new LIKE t1;
    # 檢查重建表的表定義是否符合預期
    SHOW CREATE TABLE t1_new;
    # 提前鎖定新表和老表
    lock TABLE t1 write, t1_new write;
    # 新表和老表交換
    RENAME TABLE t1 TO t1_bak, t1_new TO t1;
    # 釋放表鎖
    unlock TABLE;
    # 刪除原表
    DROP TABLE t1_bak;
    # 檢查資料表空間檔案清理進展
    SELECT * FROM information_schema.innodb_purge_files;

    說明

    • RENAME過程可能觸發重新命名檔案失敗導致執行個體崩潰問題，詳情請參見MySQL官方說明（bug#108087 InnoDB：crash because of failure to rename file in DDL），此問題已在AliSQL 5.7 20220731及更高版本中修複。

    • MySQL 8.0增加了lock TABLE，可以選擇是否執行，具體參考8.0.13新增的能力8.0 rename table。

    • RENAME + DROP避免了TRUNCATE過程中潛在的卡頓情況。

  • 使用TRUNCATE，參考如下SQL：

    # truncate表
    TRUNCATE TABLE t1;
    # 檢查資料表空間檔案清理進展
    SELECT * FROM information_schema.innodb_purge_files;

刪除大表

直接使用DROP。

# 刪除表
DROP TABLE t1;
# 檢查資料表空間檔案清理進展
SELECT * FROM information_schema.innodb_purge_files;