&emsp;&emsp;當前我們組管理著一套審核系統，除了數據源是服務端提供的，其余后臺管理都是由我們組在維護。 &emsp;&emsp;這個系統就是將APP中的各類社交信息送到后臺，然后有專門的審核人員來判斷信息是否合規，當然在送到后臺之前已經讓機器審核了一遍。 &emsp;&emsp;在去年8月份上線后，日積月累，有張數據表變得比較龐大，截止到目前將近5800W條，數據容量31.21G，每條記錄大概是582B。 &emsp;&emsp;由于數據量龐大，在檢索時也將模糊查詢撤掉，并且為了便于查詢，還加了很多索引，目前的索引容量都達到了12.2G，審核人員也經常反饋系統使用起來很卡。 ## 一、制訂優化方案 &emsp;&emsp;在了解到他們的訴求后，我們也展開了優化方案。 **1）分表** &emsp;&emsp;首先想到的分表，第一種橫向分表，也就是將數據以日或月為單位，目前一天的量在20W條左右，一個月的話在600W條左右。 &emsp;&emsp;但是這么分的話，在維護上就比較復雜，例如查詢時，假設正好遇到跨天或跨月的條件，那么數據組織就比較繁瑣了。 &emsp;&emsp;第二種分表是利用MySQL的語法：分區表，就是讓MySQL來做分表這個粗活，對我們這些使用者來說該怎么查還是怎么查。 &emsp;&emsp;工作量都移交給了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) ); ~~~ &emsp;&emsp;但是有網友說，隨著數據量的增加，分區表也會有性能問題，具體達到多少量會有顯著的性能問題，我沒有深入研究，但是量上去了，總歸還是會有點問題的。 &emsp;&emsp;分表的方案就這么廢棄了，然后想到將數據同步到 ElasticSearch 中，這樣的話，檢索就毫無壓力了，不過數據是需要頻繁的更新的，不知道會不會影響ES的性能。 &emsp;&emsp;并且改造成本也是巨大的，要改動很多地方，而目前最緊缺的就是人力資源了，即使我們花大力氣改造好了，當前測試組也抽不出人手做質量保障。 &emsp;&emsp;匆忙上線勢必會影響審核人員的使用，雖然系統有這個那個的小毛病，但至少還能穩定的在運行中，也就作罷了。 **2）MySQL歸檔** &emsp;&emsp;在仔細思考后，又想到了另一個改造成本最小的方案：MySQL歸檔。所謂歸檔就是將大表中的一組數據遷移到另一張表中。 &emsp;&emsp;與審核人員一對一溝通后，了解到，其實他們會用到的數據也就是半個月內的，半個月之前的數據很少會用到。 &emsp;&emsp;也就是說表中存在很明顯地冷熱數據，并且冷數據被操作的概率非常低，幾乎不會訪問。 &emsp;&emsp;那我只要每天將這部分冷數據遷移出去，就能保障審核記錄表的容量，也就能避免性能問題。 ## 二、實踐 &emsp;&emsp;我需要編寫一個定時任務，在每天下午兩點運行，之所以在白天運行是為了遇到問題時，能第一時間響應。 &emsp;&emsp;數據歸檔簡單地說，就是先從源表中查詢數據，再將數據插入到存量表中，最后刪除源表中的數據。 &emsp;&emsp;為了保證數據不會誤刪和遺漏，并且還要保證SQL讀寫的性能，在編寫代碼時比較謹慎，預演了多種場景。 **1）批量插入** &emsp;&emsp;根據審核人員反饋的情況，我會保留記錄表兩個月的數據，兩個月之前的數據全部遷移。 &emsp;&emsp;每天的數據量是20W左右，每小時的數據量在1.5W左右，根據這個信息，我會每次取半個小時的數據，批量添加到另一張存量表中。 &emsp;&emsp;我采用的ORM系統是[Sequelize](https://sequelize.org/)，其批量添加的語法采用的是 INSERT INTO VALUES，就是將多條 INSERT 語句合并成一條，我還特地將數據有序排列，提升插入性能。 ~~~ INSERT INTO `demo_table` (`id`, `uid`, `content`) VALUES ('1', '1001', 'content0'), ('2', '1002', 'content1'); ~~~ &emsp;&emsp;有網友做過[實驗](https://www.cnblogs.com/myseries/p/11191134.html)，批量插入的效率比單條插入高的多，100W的數據量要快21倍左右，1000W的數據要快56倍左右。 &emsp;&emsp;還有另一種批量插入的語法是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'; ~~~ &emsp;&emsp;在搜索文檔時有個網友訴說了這種插入方式引起了一個[嚴重的事故](https://juejin.cn/post/6844904086173646862)，以上面的SQL為例，由于沒有為 create\_time 配置索引，發生了全表掃描。 &emsp;&emsp;當數據量巨大時，數據庫就掛起了，無法讀寫。 **2）Sequelize的時間** &emsp;&emsp;如果要每次取半個小時的數據，那么就得有一對起始和結束時間，這個好弄，用[moment](http://momentjs.cn/)庫算一下就好了。 &emsp;&emsp;但是在使用時發現了問題，下面是采用Sequelize查詢方法（find）時打印出的時間范圍。 ~~~ `create_time` >= '2020-08-06 04:00' AND `create_time` < '2020-08-06 05:00' ~~~ &emsp;&emsp;然后是在調用刪除方法（destroy）時打印出的時間范圍，可以明顯的看出兩個時間相差8個小時，也就是存在時區的問題。 ~~~ `create_time` >= '2020-08-06 12:00' AND `create_time` < '2020-08-06 13:00' ~~~ &emsp;&emsp;查找相關資料后才得知，Sequelize 為了達到最好的兼容性，其 timezone（時區）默認是 +00:00，在將時間插入到數據庫中時都會轉換成UTC時間。 &emsp;&emsp;上海所在的地區是東八區，所以得到的UTC時間需要減去8小時。那按理說數據庫中保存的時間都會減8小時，但是每次在數據庫中查詢時，顯示的時間又是正確的。 &emsp;&emsp;這是因為表中的日期字段類型是 TIMESTAMP，它會自動轉換成數據庫時區的時間，而 DATETIME相當于一個常量，不會做自動轉換。 &emsp;&emsp;繼續回到剛剛的問題，下面是我的查詢條件，在調用 find() 時會自動減去8，而 destroy() 就沒有這步轉換，就會導致查詢出來的數據和刪除的數據不匹配，出現誤刪的問題。 ~~~ const where = { create_time: { $gte: '2020-08-06 12:00', $lt: '2020-08-06 13:00' } }; ~~~ &emsp;&emsp;想到一個辦法，那就是取當前時間段的最后一條記錄，并且將其ID值作為刪除條件，即刪除條件改成小于等于指定的ID，但在后面的實踐中發現一個隱患。 &emsp;&emsp;那就是當ID大的一個記錄，如果它的時間比較小，那么就會被誤刪。延續最后一條記錄的思路，將其創建時間作為刪除條件，就能讓兩者匹配了。 &emsp;&emsp;順便說下，為什么不用 ID 來作為區間，主要擔心的一個問題是類型溢出。 &emsp;&emsp;下面的兩條數字，第一條是調用Number.MAX\_SAFE\_INTEGER，而第二條是MySQL的bigint類型，兩者都是所能表示的最大數據范圍。 ~~~ 9007199254740991 9223372036854775807 ~~~ &emsp;&emsp;后者要比前者多了三位，那么在Node中做簡單的累加時，有可能出現溢出。順便說一句，Sequelize在從數據庫中讀取到ID后，會將其作為字符串返回。 **3）事務** &emsp;&emsp;為了保證先插入，后刪除的順序，引入了[事務](https://www.sequelize.com.cn/other-topics/transactions)，保持原子性，一旦出現問題，就回滾。 &emsp;&emsp;Sequelize 提供的事務分為托管和非托管，就是手動調用 commit() 和 rollback() 的區別，我采用了非托管。 &emsp;&emsp;此處又遇到一個問題，在阿里云上做遷移數據表，運維說需要放到另一個庫中，因為兩者表名要相同，而sequelize的事務需要由數據庫實例調用。 &emsp;&emsp;也就是說在完成插入和刪除時需要分別創建兩個不同的事務，兩次commit()。 ~~~ try { await t1.commit(); await t2.commit(); } catch (error) { await t1.rollback(); await t2.rollback(); } ~~~ &emsp;&emsp;在上面的代碼中，假設 t1完成了提交，t2在提交時發生了問題，進入了 catch() 分支內，那么此處直接調用 t1.rollback() 很可能會報下面的錯誤。 ~~~ Error: Transaction cannot be rolled back because it has been finished with state: commit ~~~ &emsp;&emsp;目前的做法是保證插入一定要成功，也就是保留一個事務，若刪除失敗，那么就發告警，先手動處理，但感覺這種情況應該也不多。 ~~~ 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）造數據** &emsp;&emsp;為了能模擬數據的批量插入和刪除，記錄表需要包含充足的數據，所以得寫腳本實現。 &emsp;&emsp;本來的設想是塞入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); } ~~~ &emsp;&emsp;運行時就報棧溢出，只得溫柔一點，降低數據量，只賽了150W條數據，每小時加1.5W條，這下終于可以了，可以繼續后面的測試了。 ~~~ FATAL ERROR: Reached heap limit Allocation failed - JavaScript heap out of memory ~~~ &emsp;&emsp;為了能保障質量，還特地將遷移邏輯包裝成一個接口，讓QA人員測試。 **5）數據清理** &emsp;&emsp;在執行定時任務之前，我還會將原表中的數據只保留一個月，并且將表中原有的數據整體遷移至一張備份表中。 &emsp;&emsp;在通過 DELETE 命令清理數據時，發生了意外，我本來打算直接刪除5000多W條數據，但是直接卡住沒有反應，還把表給鎖住了。 &emsp;&emsp;網上的方案基本都是將需要的數據移到臨時表，然后再刪除原表，最后修改臨時表的名稱，但是我的表不能刪除，因為數據再不斷的插入。 &emsp;&emsp;后面改成1000W一個批次，情況也不理想，再縮小，改成500W一批次，現在可以運行了，但是執行了將近半小時。 &emsp;&emsp;再縮小范圍，改成100W一次刪除，就能5分鐘完成。 ***** > 原文出處： [博客園-Node.js躬行記](https://www.cnblogs.com/strick/category/1688575.html) [知乎專欄-Node.js躬行記](https://zhuanlan.zhihu.com/pwnode) 已建立一個微信前端交流群，如要進群，請先加微信號freedom20180706或掃描下面的二維碼，請求中需注明“看云加群”，在通過請求后就會把你拉進來。還搜集整理了一套[面試資料](https://github.com/pwstrick/daily)，歡迎閱讀。  推薦一款前端監控腳本：[shin-monitor](https://github.com/pwstrick/shin-monitor)，不僅能監控前端的錯誤、通信、打印等行為，還能計算各類性能參數，包括 FMP、LCP、FP 等。