[[258747]]

一、問(wèn)題分析

經(jīng)過(guò)幾分鐘的排查，數(shù)據(jù)庫(kù)情況如下：

數(shù)據(jù)庫(kù)采用SQLserver 2008 R2，單表數(shù)據(jù)量21億

無(wú)水平或者垂直切分，但是采用了分區(qū)表。分區(qū)表策略是按時(shí)間降序分的區(qū)，將近30個(gè)分區(qū)。正因?yàn)榉謪^(qū)表的原因，系統(tǒng)才保證了在性能不是太差的情況下堅(jiān)持至今。

此表除聚集索引之外，無(wú)其他索引，無(wú)主鍵(主鍵其實(shí)是利用索引來(lái)快速查重的)。所以在頻繁插入新數(shù)據(jù)的情況下，索引調(diào)整所耗費(fèi)的性能比較低。

至于聚集索引和非聚集索引等知識(shí)，請(qǐng)各位移步google或者百度。

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

二、解決問(wèn)題

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

數(shù)據(jù)遷移工作包括三個(gè)個(gè)步驟：

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

1、傳統(tǒng)做法

這里申明一點(diǎn)，就算是傳統(tǒng)的做法也需要分頁(yè)獲取源數(shù)據(jù)，因?yàn)槟愕膬?nèi)存一次性裝載不下9億條數(shù)據(jù)。

1)從源數(shù)據(jù)表分頁(yè)獲取數(shù)據(jù)，具體分頁(yè)條數(shù)，太少則查詢?cè)硖l繁，太多則查詢太慢。

SQL語(yǔ)句類(lèi)似于：

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)把查詢出來(lái)的數(shù)據(jù)插入目標(biāo)數(shù)據(jù)表，這里強(qiáng)調(diào)一點(diǎn)，一定不要用單條插入策略，必須用批量插入。

3)把數(shù)據(jù)刪除，其實(shí)這里刪除還是有一個(gè)小難點(diǎn)，表沒(méi)有標(biāo)示列。這里不展開(kāi)，因?yàn)檫@不是本文要說(shuō)的重點(diǎn)。

如果你的數(shù)據(jù)量不大，以上方法完全沒(méi)有問(wèn)題，但是在9億這個(gè)數(shù)字前面，以上方法顯得心有余而力不足。一個(gè)字：慢，太慢，非常慢。

可以大體算一下，假如每秒可以遷移1000條數(shù)據(jù)，大約需要的時(shí)間為(單位：分)：

900000000/1000/60=15000(分鐘) 

大約需要10天^ V ^

2、改進(jìn)做法

以上的傳統(tǒng)做法弊端在哪里呢?

• 在9億數(shù)據(jù)前查詢必須命中索引，就算是非聚集索引我也不推薦，首推聚集索引。
• 如果你了解索引的原理，你應(yīng)該明白，不停的插入新數(shù)據(jù)的時(shí)候，索引在不停的更新，調(diào)整，以保持樹(shù)的平衡等特性。尤其是聚集索引影響甚大，因?yàn)檫€需要移動(dòng)實(shí)際的數(shù)據(jù)。

提取以上兩點(diǎn)共同的要素，那就是聚集索引。相應(yīng)的解決方案也就應(yīng)運(yùn)而生：

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

由于做了表分區(qū)，如果有一種方式把2017年以前的分區(qū)直接在磁盤(pán)物理層面從當(dāng)前表剝離，然后掛載到另外一個(gè)表，可算是神級(jí)操作。有誰(shuí)能指導(dǎo)一下，不勝感激~

三、擴(kuò)展閱讀

一個(gè)表的聚集索引的順序就是實(shí)際數(shù)據(jù)文件的順序，映射到磁盤(pán)上，本質(zhì)上位于同一個(gè)磁道上，所以操作的時(shí)候磁盤(pán)的磁頭不必跳躍著去操作。

存儲(chǔ)在硬盤(pán)中的每個(gè)文件都可分為兩部分：文件頭和存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)區(qū)。文件頭用來(lái)記錄文件名、文件屬性、占用簇號(hào)等信息，文件頭保存在一個(gè)簇并映射在FAT表(文件分配表)中。

而真實(shí)的數(shù)據(jù)則是保存在數(shù)據(jù)區(qū)當(dāng)中的。

平常所做的刪除，其實(shí)是修改文件頭的前2個(gè)代碼，這種修改映射在FAT表中，就為文件作了刪除標(biāo)記，并將文件所占簇號(hào)在FAT表中的登記項(xiàng)清零，表示釋放空間，這也就是平常刪除文件后，硬盤(pán)空間增大的原因。

而真正的文件內(nèi)容仍保存在數(shù)據(jù)區(qū)中，并未得以刪除。要等到以后的數(shù)據(jù)寫(xiě)入，把此數(shù)據(jù)區(qū)覆蓋掉，這樣才算是徹底把原來(lái)的數(shù)據(jù)刪除。如果不被后來(lái)保存的數(shù)據(jù)覆蓋，它就不會(huì)從磁盤(pán)上抹掉。

四、實(shí)際運(yùn)行代碼

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

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

按照第一步class_id列表順序查詢數(shù)據(jù)，每個(gè)class_id分頁(yè)獲取，然后插入目標(biāo)表，全部完成然后刪除源表相應(yīng)class_id的數(shù)據(jù)。

D int pageIndex = 1; //頁(yè)碼 
           int pageCount = 20000;//每頁(yè)的數(shù)據(jù)條數(shù) 
           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) 
                   { 
                       //末尾一頁(yè)無(wú)數(shù)據(jù)了，刪除源數(shù)據(jù)源數(shù)據(jù)然后跳出 
                        DBProxy.DeleteSourceClassData(dtMax, classId); 
                       break; 
                   } 
                   else 
                   { 
                       DBProxy.AddTargetData(tempData); 
                   } 
                   pageIndex++; 
               } 
               successCount++; 
               Console.WriteLine($"班級(jí):{classId} 完成,已經(jīng)完成：{successCount}個(gè)"); 
           } 

DBProxy完整代碼：

class DBProxy 
    { 
        //獲取要遷移的數(shù)據(jù)所有班級(jí)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 批量添加數(shù)據(jù) 
        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; 
        } 
    } 

運(yùn)行報(bào)告：

程序本機(jī)運(yùn)行，開(kāi)虛擬專(zhuān)用網(wǎng)絡(luò)連接遠(yuǎn)程DB服務(wù)器，運(yùn)行1分鐘，遷移的數(shù)據(jù)數(shù)據(jù)量為1915560，每秒約3萬(wàn)條數(shù)據(jù)。

1915560 / 60=31926 條/秒

CPU情況(不高)：

磁盤(pán)隊(duì)列情況(不高)：

五、寫(xiě)在后面

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

• 源數(shù)據(jù)庫(kù)和目標(biāo)數(shù)據(jù)庫(kù)硬盤(pán)為ssd，并且分別為不同的服務(wù)器；
• 遷移程序和數(shù)據(jù)庫(kù)在同一個(gè)局域網(wǎng)，保障數(shù)據(jù)傳輸時(shí)候帶寬不會(huì)成為瓶頸；
• 合理的設(shè)置SqlBulkCopy參數(shù)；
• 我們的場(chǎng)景大多數(shù)場(chǎng)景下每次批量插入的數(shù)據(jù)量達(dá)不到設(shè)置的值，因?yàn)橛械腸lass_id 對(duì)應(yīng)的數(shù)據(jù)量就幾十條，甚至幾條而已，打開(kāi)關(guān)閉數(shù)據(jù)庫(kù)連接也是需要耗時(shí)的；
• 單純的批量添加或者批量刪除操作。