mysql平滑关闭_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) fromtgp_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 liketest_db.test_track_log;alter table test_db.test_track_log rename totest_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 liketest.test_track_log;

mysql> RENAME TABLE test.test_track_log TOtest_db_arch.test_track_log_bak,

test_db_arch.test_track_logTOtest.test_track_log,

test_db_arch.test_track_log_bakTOtest_db_arch.test_track_log;

Query OK,0 rows affected (0.02 sec)

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

轉自

MySQL平滑刪除數據的小技巧 https://www.toutiao.com/a6643787647217041924/?tt_from=mobile_qq&utm_campaign=client_share&timestamp=1547273084&app=news_article&utm_source=mobile_qq&iid=26112390770&utm_medium=toutiao_ios&group_id=6643787647217041924