歡迎光臨
每天分享高質量文章

程式員過關斬將–快速遷移10億級資料

菜菜呀,咱們業務BJKJ有個表資料需要做遷移

程式員主力 Y總

現在有多少資料?

菜菜

大約21億吧,2017年以前的資料沒有業務意義了,給你半天時間把這個事搞定,績效給你A

程式員主力 Y總

有績效獎金嗎?

菜菜

錢的事你去問X總,我當家不管錢

程式員主力 Y總

………..

菜菜
問題分析
經過幾分鐘的排查,資料庫情況如下:

1.  資料庫採用Sqlserver 2008 R2,單表資料量21億

 

2. 無水平或者垂直切分,但是採用了分割槽表。分割槽表策略是按時間降序分的區,將近30個分割槽。正因為分割槽表的原因,系統才保證了在性能不是太差的情況下堅持至今。

3. 此表除聚集索引之外,無其他索引,無主鍵(主鍵其實是利用索引來快速查重的)。所以在頻繁插入新資料的情況下,索引調整所耗費的性能比較低。

至於聚集索引和非聚集索引等知識,請各位移步google或者百度。
        至於業務,不是太複雜。經過相關人員咨詢,大約40%的請求為單條Insert,大約60%的請求為按class_id 和in_time(倒序)分頁獲取資料。Select請求全部命中聚集索引,所以性能非常高。這也是聚集索引之所以這樣設計的目的。

解決問題
        由於單表資料量已經超過21億,並且2017年以前的資料幾乎不影響業務,所以決定把2017年以前(不包括2017年)的資料遷移到新表,僅供以後特殊業務查詢使用。經過查詢大約有9億資料量。

資料遷移工作包括三個個步驟:

1.  從源資料表查詢出要遷移的資料

2.  把資料插入新表

3.  把舊表的資料刪除

傳統做法

        這裡申明一點,就算是傳統的做法也需要分頁獲取源資料,因為你的記憶體一次性裝載不下9億條資料。

1.  從源資料表分頁獲取資料,具體分頁條數,太少則查詢原表太頻繁,太多則查詢太慢。

SQL陳述句類似於

SELECT * FROM (
SELECT *,ROW_NUMBER() OVER(ORDER BY class_id,in_time) p FROM  tablexx WHERE in_time <'2017.1.1'  
) t WHERE t.p BETWEEN 1 AND 100
 

2.  把查詢出來的資料插入標的資料表,這裡強調一點,一定不要用單條插入策略,必須用批量插入。

3.  把資料刪除,其實這裡刪除還是有一個小難點,表沒有標示列。這裡不展開,因為這不是菜菜要說的重點。

        如果你的資料量不大,以上方法完全沒有問題,但是在9億這個數字前面,以上方法顯得心有餘而力不足。一個字:慢,太慢,非常慢。

可以大體算一下,假如每秒可以遷移1000條資料,大約需要的時間為(單位:分)

900000000/1000/60=15000(分鐘)
大約需要10天^ V ^

改進做法
以上的傳統做法弊端在哪裡呢?

1.  在9億資料前查詢必須命中索引,就算是非聚集索引菜菜也不推薦,首推聚集索引。

2.  如果你瞭解索引的原理,你應該明白,不停的插入新資料的時候,索引在不停的更新,調整,以保持樹的平衡等特性。尤其是聚集索引影響甚大,因為還需要移動實際的資料。

 

提取以上兩點共同的要素,那就是聚集索引。相應的解決方案也就應運而生:

1.  按照聚集索分頁引查詢資料

2 批量插入資料迎合聚集索引,即:按照聚集索引的順序批量插入。

3. 按照聚集索引順序批量刪除

由於做了表分割槽,如果有一種方式把2017年以前的分割槽直接在磁盤物理層面從當前表剝離,然後掛載到另外一個表,可算是神級操作。有誰能指導一下菜菜,感激不盡

擴展閱讀
1.  一個表的聚集索引的順序就是實際資料檔案的順序,映射到磁盤上,本質上位於同一個磁道上,所以操作的時候磁盤的磁頭不必跳躍著去操作。

2.  儲存在硬碟中的每個檔案都可分為兩部分:檔案頭和儲存資料的資料區。檔案頭用來記錄檔案名、檔案屬性、占用簇號等信息,檔案頭儲存在一個簇並映射在FAT表(檔案分配表)中。而真實的資料則是儲存在資料區當中的。平常所做的刪除,其實是修改檔案頭的前2個代碼,這種修改映射在FAT表中,就為檔案作了刪除標記,並將檔案所占簇號在FAT表中的登記項清零,表示釋放空間,這也就是平常刪除檔案後,硬碟空間增大的原因。而真正的檔案內容仍儲存在資料區中,並未得以刪除。要等到以後的資料寫入,把此資料區改寫掉,這樣才算是徹底把原來的資料刪除。如果不被後來儲存的資料改寫,它就不會從磁盤上抹掉。

NetCore 代碼(實際運行代碼)
1.  第一步:由於聚集索引需要class_id ,所以寧可花2-4秒時間把要操作的class_id查詢出來(ORM為dapper),並且升序排列

   DateTime dtMax = DateTime.Parse("2017.1.1");
   var allClassId = DBProxy.GeSourcetLstClassId(dtMax)?.OrderBy(s=>s);
2.  按照第一步class_id 串列順序查詢資料,每個class_id 分頁獲取,然後插入標的表,全部完成然後刪除源表相應class_id的資料。(全部命中聚集索引)

   D int pageIndex = 1//頁碼
            int pageCount = 20000;//每頁的資料條數
            DataTable tempData =null;
            int successCount = 0;
            foreach (var classId in allClassId)
            {
                tempData = null;
                pageIndex = 1;
                while (true)
                {
                    int startIndex = (pageIndex - 1) * pageCount+1;
                    int endIndex = pageIndex * pageCount;

                    tempData = DBProxy.GetSourceDataByClassIdTable(dtMax, classId, startIndex, endIndex);
                    if (tempData == null || tempData.Rows.Count==0)
                    {
                        //最後一頁無資料了,刪除源資料源資料然後跳出
                         DBProxy.DeleteSourceClassData(dtMax, classId);
                        break;
                    }
                    else
                    {
                        DBProxy.AddTargetData(tempData);
                    }
                    pageIndex++;
                }
                successCount++;
                Console.WriteLine($"班級:{classId} 完成,已經完成:{successCount}個");
            }
 

DBProxy 完整代碼:

class DBProxy
    {
        //獲取要遷移的資料所有班級id
        public static IEnumerable<intGeSourcetLstClassId(DateTime dtMax)
        {
            var connection = Config.GetConnection(Config.SourceDBStr);
            string Sql = @"SELECT class_id FROM  tablexx WHERE in_time ;
            using (connection)
            {
                return connection.Query<int>(Sql, new { dtMax = dtMax }, commandType: System.Data.CommandType.Text);

            }
        }

        public static DataTable GetSourceDataByClassIdTable(DateTime dtMax, int classId, int startIndex, int endIndex)
        {
            var connection = Config.GetConnection(Config.SourceDBStr);
            string Sql = @" SELECT * FROM (
                        SELECT *,ROW_NUMBER() OVER(ORDER BY in_time desc) p FROM  tablexx WHERE in_time                         ) t WHERE t.p BETWEEN @startIndex AND @endIndex ";
            using (connection)
            {
                DataTable table = new DataTable("MyTable");
                var reader = connection.ExecuteReader(Sql, new { dtMax = dtMax, classId = classId, startIndex = startIndex, endIndex = endIndex }, commandType: System.Data.CommandType.Text);
                table.Load(reader);
                reader.Dispose();
                return table;
            }
        }
         public static int DeleteSourceClassData(DateTime dtMax, int classId)
        {
            var connection = Config.GetConnection(Config.SourceDBStr);
            string Sql = @" delete from  tablexx WHERE in_time ;
            using (connection)
            {
                return connection.Execute(Sql, new { dtMax = dtMax, classId = classId }, commandType: System.Data.CommandType.Text);

            }
        }
        //SqlBulkCopy 批量添加資料
        public static int AddTargetData(DataTable data)
        {
            var connection = Config.GetConnection(Config.TargetDBStr);
            using (var sbc = new SqlBulkCopy(connection))
            {
                sbc.DestinationTableName = "tablexx_2017";               
                sbc.ColumnMappings.Add("class_id""class_id");
                sbc.ColumnMappings.Add("in_time""in_time");
                .
                .
                .
                using (connection)
                {
                    connection.Open();
                    sbc.WriteToServer(data);
                }               
            }
            return 1;
        }

    }
運行報告:

        程式本機運行,開vpn連接遠程DB服務器,運行1分鐘,遷移的資料資料量為 1915560,每秒約3萬條資料

 1915560 / 60=31926 條/秒

 cpu情況(不高):

 

磁盤佇列情況(不高):

寫在最後
在以下情況下速度還將提高

1. 源資料庫和標的資料庫硬碟為ssd,並且分別為不同的服務器

2. 遷移程式和資料庫在同一個局域網,保障資料傳輸時候帶寬不會成為瓶頸

3. 合理的設置SqlBulkCopy引數

4. 菜菜的場景大多數場景下每次批量插入的資料量達不到設置的值,因為有的class_id 對應的資料量就幾十條,甚至幾條而已,打開關閉資料庫連接也是需要耗時的

5. 單純的批量添加或者批量刪除操作

 

程式員修仙之路–把用戶訪問記錄優化到極致

程式員修仙之路–把用戶訪問記錄優化到極致

程式員修仙之路–設計一個實用的執行緒池
程式員修仙之路–資料結構之CXO讓我做一個計算器
程式猿修仙之路–資料結構之設計高性能訪客記錄系統
程式猿修仙之路–演算法之快速排序到底有多快
程式猿修仙之路–資料結構之你是否真的懂陣列?

互聯網之路,菜菜與君一同成長

長按識別二維碼關註

 

    赞(0)

    分享創造快樂