Node.js躬行記（13）——MySQL歸檔

• 2021 年 11 月 1 日
• 筆記
• Node.js躬行記

　　當前我們組管理著一套審核系統，除了數據源是服務端提供的，其餘後台管理都是由我們組在維護。

　　這個系統就是將APP中的各類社交資訊送到後台，然後有專門的審核人員來判斷資訊是否合規，當然在送到後台之前已經讓機器審核了一遍。

　　在去年8月份上線後，日積月累，有張數據表變得比較龐大，截止到目前將近5800W條，數據容量31.21G，每條記錄大概是582B。

　　由於數據量龐大，在檢索時也將模糊查詢撤掉，並且為了便於查詢，還加了很多索引，目前的索引容量都達到了12.2G，審核人員也經常回饋系統使用起來很卡。

一、制訂優化方案

　　在了解到他們的訴求後，我們也展開了優化方案。

1）分表

　　首先想到的分表，第一種橫向分表，也就是將數據以日或月為單位，目前一天的量在20W條左右，一個月的話在600W條左右。

　　但是這麼分的話，在維護上就比較複雜，例如查詢時，假設正好遇到跨天或跨月的條件，那麼數據組織就比較繁瑣了。

　　第二種分表是利用MySQL的語法：分區表，就是讓MySQL來做分表這個粗活，對我們這些使用者來說該怎麼查還是怎麼查。

　　工作量都移交給了MySQL，聽上去很不錯，而且網路上教程一堆，下面是一種範圍分區。

CREATE TABLE partition_table (
    id INT,
    date DATETIME
) 
PARTITION BY RANGE (TO_DAYS(date) ) (
    PARTITION p1 VALUES LESS THAN ( TO_DAYS('20211001') ),
    PARTITION p2 VALUES LESS THAN ( TO_DAYS('20211002') ),
    PARTITION p3 VALUES LESS THAN ( TO_DAYS('20211003') ),
    PARTITION p4 VALUES LESS THAN ( TO_DAYS('20211004') ),
    PARTITION p5 VALUES LESS THAN (MAXVALUE) 
);

　　但是有網友說，隨著數據量的增加，分區表也會有性能問題，具體達到多少量會有顯著的性能問題，我沒有深入研究，但是量上去了，總歸還是會有點問題的。

　　分表的方案就這麼廢棄了，然後想到將數據同步到 ElasticSearch 中，這樣的話，檢索就毫無壓力了，不過數據是需要頻繁的更新的，不知道會不會影響ES的性能。

　　並且改造成本也是巨大的，要改動很多地方，而目前最緊缺的就是人力資源了，即使我們花大力氣改造好了，當前測試組也抽不出人手做品質保障。

　　匆忙上線勢必會影響審核人員的使用，雖然系統有這個那個的小毛病，但至少還能穩定的在運行中，也就作罷了。

2）MySQL歸檔

　　在仔細思考後，又想到了另一個改造成本最小的方案：MySQL歸檔。所謂歸檔就是將大表中的一組數據遷移到另一張表中。

　　與審核人員一對一溝通後，了解到，其實他們會用到的數據也就是半個月內的，半個月之前的數據很少會用到。

　　也就是說表中存在很明顯地冷熱數據，並且冷數據被操作的概率非常低，幾乎不會訪問。

　　那我只要每天將這部分冷數據遷移出去，就能保障審核記錄表的容量，也就能避免性能問題。

二、實踐

　　我需要編寫一個定時任務，在每天下午兩點運行，之所以在白天運行是為了遇到問題時，能第一時間響應。

　　數據歸檔簡單地說，就是先從源表中查詢數據，再將數據插入到存量表中，最後刪除源表中的數據。

　　為了保證數據不會誤刪和遺漏，並且還要保證SQL讀寫的性能，在編寫程式碼時比較謹慎，預演了多種場景。

1）批量插入

　　根據審核人員回饋的情況，我會保留記錄表兩個月的數據，兩個月之前的數據全部遷移。

　　每天的數據量是20W左右，每小時的數據量在1.5W左右，根據這個資訊，我會每次取半個小時的數據，批量添加到另一張存量表中。

　　我採用的ORM系統是 Sequelize，其批量添加的語法採用的是 INSERT INTO VALUES，就是將多條 INSERT 語句合併成一條，我還特地將數據有序排列，提升插入性能。

INSERT INTO `demo_table` (`id`, `uid`, `content`) VALUES 
('1', '1001', 'content0'), 
('2', '1002', 'content1');

　　有網友做過實驗，批量插入的效率比單條插入高的多，100W的數據量要快21倍左右，1000W的數據要快56倍左右。

　　還有另一種批量插入的語法是INSERT INTO SELECT，將查詢表的結果複製到另一張表中，目標表中任何已存在的行都不會受影響。

insert into `demo_table` 
select * from `record` where create_time between '2020-08-01 00:00:00' and '2020-08-31 23:59:59';

　　在搜索文檔時有個網友訴說了這種插入方式引起了一個嚴重的事故，以上面的SQL為例，由於沒有為 create_time 配置索引，發生了全表掃描。

　　當數據量巨大時，資料庫就掛起了，無法讀寫。

2）Sequelize的時間

　　如果要每次取半個小時的數據，那麼就得有一對起始和結束時間，這個好弄，用 moment 庫算一下就好了。

　　但是在使用時發現了問題，下面是採用Sequelize查詢方法（find）時列印出的時間範圍。

`create_time` >= '2020-08-06 04:00' AND `create_time` < '2020-08-06 05:00'

　　然後是在調用刪除方法（destroy）時列印出的時間範圍，可以明顯的看出兩個時間相差8個小時，也就是存在時區的問題。

`create_time` >= '2020-08-06 12:00' AND `create_time` < '2020-08-06 13:00'

　　查找相關資料後才得知，Sequelize 為了達到最好的兼容性，其 timezone（時區）默認是 +00:00，在將時間插入到資料庫中時都會轉換成UTC時間。

　　上海所在的地區是東八區，所以得到的UTC時間需要減去8小時。那按理說資料庫中保存的時間都會減8小時，但是每次在資料庫中查詢時，顯示的時間又是正確的。

　　這是因為表中的日期欄位類型是 TIMESTAMP，它會自動轉換成資料庫時區的時間，而 DATETIME相當於一個常量，不會做自動轉換。

　　繼續回到剛剛的問題，下面是我的查詢條件，在調用 find() 時會自動減去8，而 destroy() 就沒有這步轉換，就會導致查詢出來的數據和刪除的數據不匹配，出現誤刪的問題。

const where = {
  create_time: {
    $gte: '2020-08-06 12:00',
    $lt: '2020-08-06 13:00'
  }
};

　　想到一個辦法，那就是取當前時間段的最後一條記錄，並且將其ID值作為刪除條件，即刪除條件改成小於等於指定的ID，但在後面的實踐中發現一個隱患。

　　那就是當ID大的一個記錄，如果它的時間比較小，那麼就會被誤刪。延續最後一條記錄的思路，將其創建時間作為刪除條件，就能讓兩者匹配了。

　　順便說下，為什麼不用 ID 來作為區間，主要擔心的一個問題是類型溢出。

　　下面的兩條數字，第一條是調用Number.MAX_SAFE_INTEGER，而第二條是MySQL的bigint類型，兩者都是所能表示的最大數據範圍。

9007199254740991
9223372036854775807

　　後者要比前者多了三位，那麼在Node中做簡單的累加時，有可能出現溢出。順便說一句，Sequelize在從資料庫中讀取到ID後，會將其作為字元串返回。

3）事務

　　為了保證先插入，後刪除的順序，引入了事務，保持原子性，一旦出現問題，就回滾。

　　Sequelize 提供的事務分為託管和非託管，就是手動調用 commit() 和 rollback() 的區別，我採用了非託管。

　　此處又遇到一個問題，在阿里雲上做遷移數據表，運維說需要放到另一個庫中，因為兩者表名要相同，而sequelize的事務需要由資料庫實例調用。

　　也就是說在完成插入和刪除時需要分別創建兩個不同的事務，兩次commit()。

try {
  await t1.commit();
  await t2.commit();
} catch (error) {
  await t1.rollback();
  await t2.rollback();
}

　　在上面的程式碼中，假設 t1完成了提交，t2在提交時發生了問題，進入了 catch() 分支內，那麼此處直接調用 t1.rollback() 很可能會報下面的錯誤。

Error: Transaction cannot be rolled back because it has been finished with state: commit

　　目前的做法是保證插入一定要成功，也就是保留一個事務，若刪除失敗，那麼就發告警，先手動處理，但感覺這種情況應該也不多。

try {
  const t1 = await mysql.backend.transaction();
  //將數據添批量加進備份表  INSERT INTO VALUES
  await services.report.insert(list, { transaction: t1 });
  const { createTime } = list[list.length - 1];
  //刪除原表數據
  await services.report.del({
    createTime: {
      $lte: createTime
    }
  });
  await t1.commit();
} catch (error) {
  // 回滾
  await t1.rollback();
  console.log(error);
  // 發送警告 TODO
}

4）造數據

　　為了能模擬數據的批量插入和刪除，記錄表需要包含充足的數據，所以得寫腳本實現。

　　本來的設想是塞入1000W條數據，每小時加2W條，如下所示，簡單粗暴。

for (let i = 0; i < 500; i++) {
  const list = [];
  for (let j = 0; j < 20000; j++) {
    list.push({
      createTime: moment("2020-10-01 00:00").add(i, "hours")
    });
  }
  await services.report.savePatch(list);
}

　　運行時就報棧溢出，只得溫柔一點，降低數據量，只賽了150W條數據，每小時加1.5W條，這下終於可以了，可以繼續後面的測試了。

FATAL ERROR: Reached heap limit Allocation failed - JavaScript heap out of memory

　　為了能保障品質，還特地將遷移邏輯包裝成一個介面，讓QA人員測試。

5）數據清理

　　在執行定時任務之前，我還會將原表中的數據只保留一個月，並且將表中原有的數據整體遷移至一張備份表中。

　　在通過 DELETE 命令清理數據時，發生了意外，我本來打算直接刪除5000多W條數據，但是直接卡住沒有反應，還把表給鎖住了。

　　網上的方案基本都是將需要的數據移到臨時表，然後再刪除原表，最後修改臨時表的名稱，但是我的表不能刪除，因為數據再不斷的插入。

　　後面改成1000W一個批次，情況也不理想，再縮小，改成500W一批次，現在可以運行了，但是執行了將近半小時。

　　再縮小範圍，改成100W一次刪除，就能5分鐘完成。

參考資料：

MySQL 最佳實踐 · 分區表基本類型

為什麼MySQL不建議使用delete刪除數據？

數據歸檔二三事兒

Insert into select語句引發的生產事故

大批量數據高效插入資料庫表

mysql千萬級數據分表遷移方案板

關於「時間」的一次探索