MySQL 快速刪除大量數據（千萬級別）的幾種實踐方案

 

 筆者最近工作中遇見一個性能瓶頸問題，MySQL表，每天大概新增776萬條記錄，存儲周期為7天，超過7天的數據需要在新增記錄前老化。連續運行9天以後，刪除一天的數據大概需要3個半小時（環境：128G, 32核，4T硬盤），而這是不能接受的。當然如果要整個表刪除，毋庸置疑，用

TRUNCATE TABLE就好。

最初的方案（因為未預料到刪除會如此慢），代碼如下（最簡單和樸素的方法）:

delete from table_name where cnt_date <= target_date

 

 後經過研究，最終實現了飛一般(1秒左右)的速度刪除770多萬條數據，單張表總數據量在4600萬上下，優化過程的方案層層遞進，詳細記錄如下：

  • 批量刪除（每次限定一定數量），然後循環刪除直到全部數據刪除完畢；同時key_buffer_size 由默認的8M提高到512M

 運行效果：刪除時間大概從3個半小時提高到了3小時

（1）通過limit(具體size 請酌情設置）限制一次刪除的數據量，然後判斷數據是否刪除完，附源碼如下（Python實現）：

def delete_expired_data(mysqlconn, day):
    mysqlcur = mysqlconn.cursor()
    delete_sql = "DELETE from table_name where cnt_date<='%s' limit 50000" % day
    query_sql = "select srcip from table_name  where cnt_date <= '%s' limit 1" % day
    try: 
        df = pd.read_sql(query_sql, mysqlconn)
        while True:
            if df is None or df.empty:
                break
            mysqlcur.execute(delete_sql)
            mysqlconn.commit()

            df = pd.read_sql(query_sql, mysqlconn)
    except:
       mysqlconn.rollback()

 

（2）增加key_buffer_size

mysqlcur.execute("SET GLOBAL key_buffer_size = 536870912")

key_buffer_size是global變量，詳情參見Mysql官方文檔： //dev.mysql.com/doc/refman/5.7/en/server-configuration.html

  • DELETE QUICK + OPTIMIZE TABLE

 適用場景：MyISAM Tables

 Why: MyISAM刪除的數據維護在一個鏈表中，這些空間和行的位置接下來會被Insert的數據復用。 直接的delete後，mysql會合併索引塊，涉及大量內存的拷貝移動；而OPTIMIZE TABLE直接重建索引，即直接把數據塊情況，再重新搞一份（聯想JVM垃圾回收算法）。

運行效果：刪除時間大3個半小時提高到了1小時40分

具體代碼如下：

def delete_expired_data(mysqlconn, day):
    mysqlcur = mysqlconn.cursor()
    delete_sql = "DELETE QUICK from table_name where cnt_date<='%s' limit 50000" % day
    query_sql = "select srcip from table_name where cnt_date <= '%s' limit 1" % day
    optimize_sql = "OPTIMIZE TABLE g_visit_relation_asset"
    try: 
        df = pd.read_sql(query_sql, mysqlconn)
        while True:
            if df is None or df.empty:
                break
            mysqlcur.execute(delete_sql)
            mysqlconn.commit()

            df = pd.read_sql(query_sql, mysqlconn)
        mysqlcur.execute(optimize_sql)
        mysqlconn.commit()
    except:
       mysqlconn.rollback()
  • 表分區，刪除直接刪除過期日期所在的分區（最終方案—秒殺）

 MySQL表分區有幾種方式，包括RANGE、KEY、LIST、HASH，具體參見官方文檔。因為這裡的應用場景日期在變化，所以不適合用RANGE設置固定的分區名稱，HASH分區更適應此處場景

（1）分區表定義，SQL語句如下：

ALTER TABLE table_name PARTITION BY HASH(TO_DAYS(cnt_date)) PARTITIONS 7;

TO_DAYS將日期（必須為日期類型，否則會報錯:Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed）轉換為天數（按一年的天數計算），然後HASH；分區建立7個。實際上，就是 days MOD 7 。


（2）查詢出需要老化的日期所在的分區,SQL語句如下：

"explain partitions select * from g_visit_relation_asset where cnt_date = '%s'" % expired_day

（3）OPTIMIZE or REBUILD partition,SQL語句如下：

"ALTER TABLE g_visit_relation_asset OPTIMIZE PARTITION '%s'" % partition

完整代碼如下【Python實現】，循環刪除小於指定日期的數據：

def clear_partition_data(mysqlconn, day):
    mysqlcur = mysqlconn.cursor()
    expired_day = day
    query_partition_sql = "explain partitions select * from table_name where cnt_date = '%s'" % expired_day
    # OPTIMIZE or REBUILD after truncate partition
    try: 
        while True:
            df = pd.read_sql(query_partition_sql, mysqlconn)
            if df is None or df.empty:
                break
            partition = df.loc[0, 'partitions']
            if partition is not None:
                clear_partition_sql = "alter table table_name TRUNCATE PARTITION %s" % partition
                mysqlcur.execute(clear_partition_sql)
                mysqlconn.commit()

                optimize_partition_sql = "ALTER TABLE table_name OPTIMIZE PARTITION %s" % partition
                mysqlcur.execute(optimize_partition_sql)
                mysqlconn.commit()
            
            expired_day = (expired_day - timedelta(days = 1)).strftime("%Y-%m-%d")
            df = pd.read_sql(query_partition_sql, mysqlconn)
    except:
       mysqlconn.rollback()

 

  •  其它

 如果刪除的數據超過表數據的百分之50，建議拷貝所需數據到臨時表，然後刪除原表，再重命名臨時表為原表，附MySQL如下：

   INSERT INTO New
      SELECT * FROM Main
         WHERE ...;  -- just the rows you want to keep
   RENAME TABLE main TO Old, New TO Main;
   DROP TABLE Old;   -- Space freed up here

參考：

1）//dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html 具體分區說明

2）//mysql.rjweb.org/doc.php/deletebig#solutions   刪除大數據的解決方案

  本文版權歸作者和博客園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，否則保留追究法律責任的權利。

************************************************************************

精力有限，想法太多，專註做好一件事就行

  • 我只是一個程序猿。5年內把代碼寫好，技術博客字字推敲，堅持零拷貝和原創
  • 寫博客的意義在於打磨文筆，訓練邏輯條理性，加深對知識的系統性理解；如果恰好又對別人有點幫助，那真是一件令人開心的事

************************************************************************

VirMach 便宜 VPS

QNews

QNews