如何半天搞定數(shù)據(jù)庫遷移10億級數(shù)據(jù)
一、問題分析
經(jīng)過幾分鐘的排查，數(shù)據(jù)庫情況如下：
數(shù)據(jù)庫采用SQLserver 2008 R2，單表數(shù)據(jù)量21億
無水平或者垂直切分，但是采用了分區(qū)表。分區(qū)表策略是按時(shí)間降序分的區(qū)，將近30個(gè)分區(qū)。正因?yàn)榉謪^(qū)表的原因，系統(tǒng)才保證了在性能不是太差的情況下堅(jiān)持至今。
此表除聚集索引之外，無其他索引，無主鍵(主鍵其實(shí)是利用索引來快速查重的)。所以在頻繁插入新數(shù)據(jù)的情況下，索引調(diào)整所耗費(fèi)的性能比較低。
至于聚集索引和非聚集索引等知識(shí)，請各位移步google或者百度。
至于業(yè)務(wù)，不是太復(fù)雜。經(jīng)過相關(guān)人員咨詢，大約40%的請求為單條Insert，大約60%的請求為按class_id 和in_time(倒序)分頁獲取數(shù)據(jù)。Select請求全部命中聚集索引，所以性能非常高。這也是聚集索引這樣設(shè)計(jì)的目的。
二、解決問題
由于單表數(shù)據(jù)量已經(jīng)超過21億，并且2017年以前的數(shù)據(jù)幾乎不影響業(yè)務(wù)，所以決定把2017年以前(不包括2017年)的數(shù)據(jù)遷移到新表，僅供以后特殊業(yè)務(wù)查詢使用。經(jīng)過查詢大約有9億數(shù)據(jù)量。
數(shù)據(jù)遷移工作包括三個(gè)個(gè)步驟：
- 從源數(shù)據(jù)表查詢出要遷移的數(shù)據(jù)
- 把數(shù)據(jù)插入新表
- 把舊表的數(shù)據(jù)刪除
1、傳統(tǒng)做法
這里申明一點(diǎn)，就算是傳統(tǒng)的做法也需要分頁獲取源數(shù)據(jù)，因?yàn)槟愕膬?nèi)存一次性裝載不下9億條數(shù)據(jù)。
1)從源數(shù)據(jù)表分頁獲取數(shù)據(jù)，具體分頁條數(shù)，太少則查詢原表太頻繁，太多則查詢太慢。
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)把查詢出來的數(shù)據(jù)插入目標(biāo)數(shù)據(jù)表，這里強(qiáng)調(diào)一點(diǎn)，一定不要用單條插入策略，必須用批量插入。
3)把數(shù)據(jù)刪除，其實(shí)這里刪除還是有一個(gè)小難點(diǎn)，表沒有標(biāo)示列。這里不展開，因?yàn)檫@不是本文要說的重點(diǎn)。
如果你的數(shù)據(jù)量不大，以上方法完全沒有問題，但是在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)整，以保持樹的平衡等特性。尤其是聚集索引影響甚大，因?yàn)檫€需要移動(dòng)實(shí)際的數(shù)據(jù)。
提取以上兩點(diǎn)共同的要素，那就是聚集索引。相應(yīng)的解決方案也就應(yīng)運(yùn)而生：
- 按照聚集索分頁引查詢數(shù)據(jù);
- 批量插入數(shù)據(jù)迎合聚集索引，即：按照聚集索引的順序批量插入;
- 按照聚集索引順序批量刪除;
由于做了表分區(qū)，如果有一種方式把2017年以前的分區(qū)直接在磁盤物理層面從當(dāng)前表剝離，然后掛載到另外一個(gè)表，可算是神級操作。有誰能指導(dǎo)一下，不勝感激~
三、擴(kuò)展閱讀
一個(gè)表的聚集索引的順序就是實(shí)際數(shù)據(jù)文件的順序，映射到磁盤上，本質(zhì)上位于同一個(gè)磁道上，所以操作的時(shí)候磁盤的磁頭不必跳躍著去操作。
存儲(chǔ)在硬盤中的每個(gè)文件都可分為兩部分：文件頭和存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)區(qū)。文件頭用來記錄文件名、文件屬性、占用簇號(hào)等信息，文件頭保存在一個(gè)簇并映射在FAT表(文件分配表)中。
而真實(shí)的數(shù)據(jù)則是保存在數(shù)據(jù)區(qū)當(dāng)中的。
平常所做的刪除，其實(shí)是修改文件頭的前2個(gè)代碼，這種修改映射在FAT表中，就為文件作了刪除標(biāo)記，并將文件所占簇號(hào)在FAT表中的登記項(xiàng)清零，表示釋放空間，這也就是平常刪除文件后，硬盤空間增大的原因。
而真正的文件內(nèi)容仍保存在數(shù)據(jù)區(qū)中，并未得以刪除。要等到以后的數(shù)據(jù)寫入，把此數(shù)據(jù)區(qū)覆蓋掉，這樣才算是徹底把原來的數(shù)據(jù)刪除。如果不被后來保存的數(shù)據(jù)覆蓋，它就不會(huì)從磁盤上抹掉。
四、實(shí)際運(yùn)行代碼
第一步：由于聚集索引需要class_id，所以寧可花2-4秒時(shí)間把要操作的class_id查詢出來(ORM為dapper)，并且升序排列：
- DateTime dtMax = DateTime.Parse("2017.1.1");
- var allClassId = DBProxy.GeSourcetLstClassId(dtMax)?.OrderBy(s=>s);
按照第一步class_id列表順序查詢數(shù)據(jù)，每個(gè)class_id分頁獲取，然后插入目標(biāo)表，全部完成然后刪除源表相應(yīng)class_id的數(shù)據(jù)。
- D int pageIndex = 1; //頁碼
- int pageCount = 20000;//每頁的數(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)
- {
- //末尾一頁無數(shù)據(jù)了，刪除源數(shù)據(jù)源數(shù)據(jù)然后跳出
- DBProxy.DeleteSourceClassData(dtMax, classId);
- break;
- }
- else
- {
- DBProxy.AddTargetData(tempData);
- }
- pageIndex++;
- }
- successCount++;
- Console.WriteLine($"班級:{classId} 完成,已經(jīng)完成：{successCount}個(gè)");
- }
DBProxy完整代碼：
- class DBProxy
- {
- //獲取要遷移的數(shù)據(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)行，開虛擬專用網(wǎng)絡(luò)連接遠(yuǎn)程DB服務(wù)器，運(yùn)行1分鐘，遷移的數(shù)據(jù)數(shù)據(jù)量為1915560，每秒約3萬條數(shù)據(jù)。
1915560 / 60=31926 條/秒
CPU情況(不高)：
磁盤隊(duì)列情況(不高)：
五、寫在后面
在以下情況下速度還將提高：
- 源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫硬盤為ssd，并且分別為不同的服務(wù)器；
- 遷移程序和數(shù)據(jù)庫在同一個(gè)局域網(wǎng)，保障數(shù)據(jù)傳輸時(shí)候帶寬不會(huì)成為瓶頸；
- 合理的設(shè)置SqlBulkCopy參數(shù)；
- 我們的場景大多數(shù)場景下每次批量插入的數(shù)據(jù)量達(dá)不到設(shè)置的值，因?yàn)橛械腸lass_id 對應(yīng)的數(shù)據(jù)量就幾十條，甚至幾條而已，打開關(guān)閉數(shù)據(jù)庫連接也是需要耗時(shí)的；
- 單純的批量添加或者批量刪除操作。
