單表千萬級資料遷移實踐方案-乞丐版，不使用大資料平台

Posted on 2019-03-12 10:12 隻會一點java 閱讀(...) 評論(...) 編輯 收藏

一、現狀

mysql下，某business單表已近2000萬且還在持續增加中,存在多個索引，有較高的查詢壓力。現業務端使用guava cache攔了一道，還能頂得住，但是背景管理系統的全量資料的分頁排序查詢比較慢，且将來會越來越慢。

二、目标

 業務端+admin查詢都快。

三、解決方案

1.基于實際情況（大家一定要根據實際情況來），把資料庫拆為三個，如下：

1. 熱資料(老表)：提供CURD操作，事務加鎖等等，最大限度的不用更改原代碼。
2. 半年内資料（history）：隻提供查詢業務。
3. 半年之前資料(backup)：歸檔，不提供業務查詢，隻支援手動查庫(已跟産品溝通好)。

2.資料遷移，采用公司統一任務排程平台，注冊任務後排程執行，自帶WEB管理頁面。支援暫停、恢複、執行計劃、日志查詢。

3.由于曆史資料過千萬，需要上線前進行一次手動遷移，初始化資料

            1）history表儲存：7天~半年，非進行狀态的資料。

            2）backup表儲存：半年前的，非進行狀态的資料。

            3）删除business表中，7天前的，非進行狀态的資料。

4. 後續每天淩晨定時任務遷移資料（遷移時注意：保證ID一緻  ）

            business-->history      >7天，非進行狀态的資料。

            history-->backup   >半年，非進行狀态的資料。

5.admin切到從庫讀。主從分離,避免從庫讀全量資料，導緻業務端查詢緩慢。

四、采坑

1.千萬級帶索引删除記錄，記得不能一次性直接delete,可以根據建立時間來，一次删除百萬級資料，多分幾次删除。否則容易出現假死，慢查詢，kill不掉執行sql.

2.注意初始化資料時候，可能當天多次執行，是以加上修改時間在當天前的，這樣多次執行，不會出現資料重複。

3.寫批量插入sql時，

1）不要用函數：sql中使用函數極端消耗時間。

2）不要用#，要用$：避免再次編譯消耗時間，這裡不用怕什麼sql注入，内部接口。

1 <insert id="transferTradingOrderByIds">
 2     insert into ${toTableName} (<include refid="Base_Column_List"/>)
 3     select <include refid="Base_Column_List"/>
 4     from ${fromTableName}
 5     <where>
 6         id in
 7         <foreach collection="transferTradingOrderIds" item="id" separator="," open="(" close=")">
 8             ${id}
 9         </foreach>
10     </where>
11 </insert>      

五、結果

熱表資料:一百萬内，增删改查極快。

曆史資料:一千萬内，查詢快。

歸檔資料：千萬級以上，慢，但是業務不調用。