MySQL平滑删除資料的小技巧【轉】

今天接到一位開發同學的資料操作需求，需求看似很簡單，需要執行下面的SQL語句：

delete from test_track_log where log_time < '2019-1-7 00:00:00';      

看需求描述是因為查詢統計較差，希望删除一些曆史資料。

帶着疑問我看下了表結構：

CREATE TABLE `test_track_log` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',

`uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '使用者ID',

...

`log_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '記錄時間',

PRIMARY KEY (`id`),

KEY `idx_uid_fsm_log` (`uid`,`fsm_id`,`log_time`)

) ENGINE=InnoDB AUTO_INCREMENT=125082604 DEFAULT CHARSET=utf8 COMMENT='記錄測試賬号的任務軌迹'      

看自增列的情況，這個表的資料量有近1億條記錄了，暫且不說資料量帶來的額外影響，單說這個需求，你會發現這是一個隕石坑。

簡單驗證了下，資料量确實在億級别。

select count(id) from tgp_db.tgp_track_log

+-----------+

| 125082603 |

+-----------+

1 row in set (1 min 26.63 sec)      

如果老老實實執行了，估計我下午就不用幹别的了。

顯然這個需求是一個模糊需求，業務方希望清理資料，但是實作方式缺不合理。

如果我們使用truncate的操作，這樣看來目前是比較合适的。

同時在做資料清理的時候，勢必要考慮備份資料，而和業務方确認，資料可以不用備份，但是從資料庫層面來說，是需要的。

在操作前進行細緻的溝通，發現業務方還是會希望參考近些天來的資料，尤其是當天的資料，是以這個操作還是需要謹慎。

這裡有兩個坑：

第一是業務方再三确認不需要備份，但是如果删除了資料之後，發生了意料之外的故障，需要恢複資料，而DBA沒法恢複，那麼這個鍋我們背不住。

第二是業務方再三确認删除的邏輯是正确的，但是他們不負責資料操作的性能問題，我們如果不去稽核而為了執行而執行，那麼造成性能故障之後，很容易造成需求的分歧。

是以這件事情的本質很簡單，清理資料，對業務影響最小，保留指定範圍的資料。

這種情況下單純的DML語句是搞不定了，我們需要想一些辦法，這裡有一個技巧，也是我非常喜歡MySQL的一個亮點特性，即MySQL可以很輕松的把一個庫的表遷移到另外一個資料庫，這種操作的代價就好像把一個檔案從檔案夾1拷貝到檔案夾2。

一個初版的實作如下：

create table test_db.test_track_log_tmp like test_db.test_track_log;

alter table test_db.test_track_log rename to test_db_arch.test_track_log;

alter table test_db.test_track_log_tmp rename to test_db.test_track_log;      

這種操作看起來很簡單，但是也存在一些問題，一個是在切換的過程中，如果寫入資料是會丢失資料的，即資料已經入庫，這裡通過rename丢失資料。

第二個是這個操作不夠簡潔。怎麼改進呢，我們可以把rename的操作玩得更溜。

mysql> create table test_db_arch.test_track_log like test.test_track_log;

 

mysql> RENAME TABLE test.test_track_log TO test_db_arch.test_track_log_bak,

test_db_arch.test_track_log TO test.test_track_log,

test_db_arch.test_track_log_bak TO test_db_arch.test_track_log;

Query OK, 0 rows affected (0.02 sec)      

整個過程持續0.02秒，億級資料的切換，整體來說效果還是很明顯的，也推薦大家在工作中根據适合的場景來應用。