MySQL 線上數據庫清理數據的方法

Posted on by

01 場景分析

 今天下午，開發的同事提來一個需求，需要在線上要刪除一些數據記錄，簡單看瞭看數據的分佈，大概是要刪除數據表中的兩千七百多萬條記錄，數據表的總記錄是兩千八百多萬，也就是說，要刪除的記錄占瞭總記錄的絕大部分比重，兩千七百多萬的數據記錄，要是刪除的話，使用的時間是相當長的，對線上的業務肯定會造成影響。這裡將實際的應用案例簡單重構為以下方法：

mysql> select date,count(*) from test.tbl_a  group by date;
+----------+----------+
|   date | count(*) |
+----------+----------+
| |  63103 |
| 20190118 | |
| |  125916 |
| 20190120 |   |
| |  129198 |
| 20190122 |  |
| | 5191247 |
+----------+----------+
 rows in set (13.21 sec)

上面就是重構之後的表的結構，我們可以看到，test數據庫中的表tbl_b按照date分組之後，每個組的數據量都不小，而我們的需求是將date為20190118和20190123的記錄刪除，可以看到這兩種記錄總計有兩千多萬條，占瞭表中數據的絕大部分，如果直接刪除的話，線上的業務肯定會受到阻塞。

以下是操作方法，需要註意的是，應用下面的操作方法的前提是：

要刪除的數據占瞭數據表中的絕大部分。

02 操作辦法

 通過分析，知道剩餘的表數據對於全表來說是很小的一部分，這個操作我們分為4步：

1.我們先把剩餘的數據存入到另外一個數據庫test1中：

create table test1.tbl_b_new as 
select * from test.tbl_b 
where date in(,,,,);

這個操作的時間隻有3s左右；

2.我們在test1數據庫中創建一個同名的表tbl_b,它的結構和test數據庫中的tbl_b數據結構一致：

create table test1.tbl_b like test.tbl_b;

3.緊接著，我們使用數據庫中的rename操作將表test中的表tbl_b和test1中的表tbl_b進行交換，等價於將test數據庫中的表所有數據清除。

RENAME TABLE test.tbl_b  TO test1.tbl_b_bak,
         test1.tbl_b  TO test.tbl_b,
         test1.tbl_b_bak TO test1.tbl_b;

4.再講第一步保存的剩餘數據填充到新表中來，如下：

insert into test.tbl_b select * from  test1.tbl_b_new;

03 結果分析

 看上去我們好像把問題搞復雜瞭，直接刪除的事情，被我們搞的多瞭好幾個步驟，但是實際上不是這樣的，這一套操作可以幫我們節省好幾分鐘時間，對線上業務的影響也更小，原因如下：

  1. 我們使用create table as的方法創建剩餘的數據表，這種方法使我們僅對數據表中的少部分數據進行瞭操作。避免瞭我們對過多的數據進行掃描。
  2. mysql中對大表進行rename的操作，rename命令會直接修改底層的.frm文件，所以它的速度是相當之快的。

   第二個特點給我們提供瞭一種思路，在一個很著急的業務中，要使用一個表的時候，往往不給我們留充足的時間備份表，如果我們想要刪除一個大表裡面的數據，而且需要進行相關備份，我們可以通過rename操作迅速處理，然後再想辦法去備份rename之後的表。

    上面的例子中使用create table as 和create table like兩種克隆表的方式，有幾點需要註意：

create table like方式會完整地克隆表結構，但不會插入數據，需要單獨使用insert into或load data方式加載數據
create table as  方式會部分克隆表結構，完整保留數據

    如果有興趣，可以做做試驗進行驗證。

以上就是MySQL 線上數據庫清理數據的方法的詳細內容，更多關於MySQL 線上數據庫清理數據的資料請關註WalkonNet其它相關文章！

推薦閱讀：

    None Found