筆者最近工作中遇見一個性能瓶頸問題，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官方文檔： https://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)https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html 具體分區說明
2)http://mysql.rjweb.org/doc.php/deletebig#solutions 删除大資料的解決方案