[[258747]]

一、問題分析

經過幾分鐘的排查，數據庫情況如下：

數據庫采用SQLserver 2008 R2，單表數據量21億

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

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

至于聚集索引和非聚集索引等知識，請各位移步google或者百度。

至于業務，不是太復雜。經過相關人員咨詢，大約40%的請求為單條Insert，大約60%的請求為按class_id 和in_time(倒序)分頁獲取數據。Select請求全部命中聚集索引，所以性能非常高。這也是聚集索引這樣設計的目的。

二、解決問題

由于單表數據量已經超過21億，并且2017年以前的數據幾乎不影響業務，所以決定把2017年以前(不包括2017年)的數據遷移到新表，僅供以后特殊業務查詢使用。經過查詢大約有9億數據量。

數據遷移工作包括三個個步驟：

• 從源數據表查詢出要遷移的數據
• 把數據插入新表
• 把舊表的數據刪除

1、傳統做法

這里申明一點，就算是傳統的做法也需要分頁獲取源數據，因為你的內存一次性裝載不下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 ^

2、改進做法

以上的傳統做法弊端在哪里呢?

• 在9億數據前查詢必須命中索引，就算是非聚集索引我也不推薦，首推聚集索引。
• 如果你了解索引的原理，你應該明白，不停的插入新數據的時候，索引在不停的更新，調整，以保持樹的平衡等特性。尤其是聚集索引影響甚大，因為還需要移動實際的數據。

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

• 按照聚集索分頁引查詢數據;
• 批量插入數據迎合聚集索引，即：按照聚集索引的順序批量插入;
• 按照聚集索引順序批量刪除;

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

三、擴展閱讀

一個表的聚集索引的順序就是實際數據文件的順序，映射到磁盤上，本質上位于同一個磁道上，所以操作的時候磁盤的磁頭不必跳躍著去操作。

存儲在硬盤中的每個文件都可分為兩部分：文件頭和存儲數據的數據區。文件頭用來記錄文件名、文件屬性、占用簇號等信息，文件頭保存在一個簇并映射在FAT表(文件分配表)中。

而真實的數據則是保存在數據區當中的。

平常所做的刪除，其實是修改文件頭的前2個代碼，這種修改映射在FAT表中，就為文件作了刪除標記，并將文件所占簇號在FAT表中的登記項清零，表示釋放空間，這也就是平常刪除文件后，硬盤空間增大的原因。

而真正的文件內容仍保存在數據區中，并未得以刪除。要等到以后的數據寫入，把此數據區覆蓋掉，這樣才算是徹底把原來的數據刪除。如果不被后來保存的數據覆蓋，它就不會從磁盤上抹掉。

四、實際運行代碼

第一步：由于聚集索引需要class_id，所以寧可花2-4秒時間把要操作的class_id查詢出來(ORM為dapper)，并且升序排列：

DateTime dtMax = DateTime.Parse("2017.1.1"); 
 var allClassId = DBProxy.GeSourcetLstClassId(dtMax)?.OrderBy(s=>s); 

按照第一步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<int> GeSourcetLstClassId(DateTime dtMax) 
        { 
            var connection = Config.GetConnection(Config.SourceDBStr); 
            string Sql = @"SELECT class_id FROM  tablexx WHERE in_time <@dtMax GROUP BY class_id "; 
            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 <@dtMax  AND class_id=@classId 
                        ) 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 <@dtMax  AND class_id=@classId "; 
            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; 
        } 
    } 

運行報告：

程序本機運行，開虛擬專用網絡連接遠程DB服務器，運行1分鐘，遷移的數據數據量為1915560，每秒約3萬條數據。

1915560 / 60=31926 條/秒

CPU情況(不高)：

磁盤隊列情況(不高)：

五、寫在后面

在以下情況下速度還將提高：

• 源數據庫和目標數據庫硬盤為ssd，并且分別為不同的服務器；
• 遷移程序和數據庫在同一個局域網，保障數據傳輸時候帶寬不會成為瓶頸；
• 合理的設置SqlBulkCopy參數；
• 我們的場景大多數場景下每次批量插入的數據量達不到設置的值，因為有的class_id 對應的數據量就幾十條，甚至幾條而已，打開關閉數據庫連接也是需要耗時的；
• 單純的批量添加或者批量刪除操作。