• 欢迎访问web前端中文站,JavaScript,CSS3,HTML5,web前端demo
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏web前端中文站吧

程序员过关斩将–数据库快速迁移10亿级数据

JavaScript web前端中文站 1年前 (2019-02-23) 1057次浏览 已收录 0个评论

菜菜呀,咱们业务 BJKJ 有个表数据需要做迁移,现在有多少数据?

更多精彩内容请看 web 前端中文站
http://www.lisa33xiaoq.net 可按 Ctrl + D 进行收藏

大约 21 亿吧,2017 年以前的数据没有业务意义了,给你半天时间把这个事搞定,绩效给

问题分析
经过几分钟的排查,数据库情况如下:

程序员过关斩将--数据库快速迁移 10 亿级数据1.? 数据库采用 Sqlserver 2008 R2,单表数据量 21 亿

程序员过关斩将--数据库快速迁移 10 亿级数据2. 无水平或者垂直切分,但是采用了分区表。分区表策略是按时间降序分的区,将近 30 个分区。正因为分区表的原因,系统才保证了在性能不是太差的情况下坚持至今。

程序员过关斩将--数据库快速迁移 10 亿级数据3. 此表除聚集索引之外,无其他索引,无主键(主键其实是利用索引来快速查重的)。所以在频繁插入新数据的情况下,索引调整所耗费的性能比较低。

程序员过关斩将--数据库快速迁移 10 亿级数据
至于聚集索引和非聚集索引等知识,请各位移步 google 或者百度。
程序员过关斩将--数据库快速迁移 10 亿级数据
????????至于业务,不是太复杂。经过相关人员咨询,大约 40%的请求为单条 Insert,大约 60%的请求为按 class_id 和 in_time(倒序)分页获取数据。Select 请求全部命中聚集索引,所以性能非常高。这也是聚集索引之所以这样设计的目的。

解决问题
????????由于单表数据量已经超过 21 亿,并且 2017 年以前的数据几乎不影响业务,所以决定把 2017 年以前(不包括 2017 年)的数据迁移到新表,仅供以后特殊业务查询使用。经过查询大约有 9 亿数据量。

数据迁移工作包括三个个步骤:

1.? 从源数据表查询出要迁移的数据

2.? 把数据插入新表

3.? 把旧表的数据删除

传统做法

????????这里申明一点,就算是传统的做法也需要分页获取源数据,因为你的内存一次性装载不下 9 亿条数据。

程序员过关斩将--数据库快速迁移 10 亿级数据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
 

程序员过关斩将--数据库快速迁移 10 亿级数据2.? 把查询出来的数据插入目标数据表,这里强调一点,一定不要用单条插入策略,必须用批量插入。

程序员过关斩将--数据库快速迁移 10 亿级数据3.? 把数据删除,其实这里删除还是有一个小难点,表没有标示列。这里不展开,因为这不是菜菜要说的重点。

????????如果你的数据量不大,以上方法完全没有问题,但是在 9 亿这个数字前面,以上方法显得心有余而力不足。一个字:慢,太慢,非常慢。

可以大体算一下,假如每秒可以迁移1000 条数据,大约需要的时间为(单位:分)

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

改进做法
以上的传统做法弊端在哪里呢?

1.? 在 9 亿数据前查询必须命中索引,就算是非聚集索引菜菜也不推荐,首推聚集索引。

2.??如果你了解索引的原理,你应该明白,不停的插入新数据的时候,索引在不停的更新,调整,以保持树的平衡等特性。尤其是聚集索引影响甚大,因为还需要移动实际的数据。

 

提取以上两点共同的要素,那就是聚集索引。相应的解决方案也就应运而生:

1.??按照聚集索分页查询数据

2.? 批量插入数据迎合聚集索引,即:按照聚集索引的顺序批量插入。

3. 按照聚集索引顺序批量删除

由于做了表分区,如果有一种方式把 2017 年以前的分区直接在磁盘物理层面从当前表剥离,然后挂载到另外一个表,可算是神级操作。有谁能指导一下菜菜,感激不尽

扩展阅读
程序员过关斩将--数据库快速迁移 10 亿级数据1.? 一个表的聚集索引的顺序就是实际数据文件的顺序,映射到磁盘上,本质上位于同一个磁道上,所以操作的时候磁盘的磁头不必跳跃着去操作。

程序员过关斩将--数据库快速迁移 10 亿级数据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<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;
}

}

运行报告:

????????程序本机运行,开 vpn 连接远程 DB 服务器,运行 1 分钟,迁移的数据数据量为 1915560,每秒约 3 万条数据

?1915560 / 60=31926 条/秒

?cpu 情况(不高):

磁盘队列情况(不高):

写在最后
在以下情况下速度还将提高

1. 源数据库和目标数据库硬盘为 ssd,并且分别为不同的服务器

2. 迁移程序和数据库在同一个局域网,保障数据传输时候带宽不会成为瓶颈

3. 合理的设置 SqlBulkCopy 参数

4. 菜菜的场景大多数场景下每次批量插入的数据量达不到设置的值,因为有的 class_id 对应的数据量就几十条,甚至几条而已,打开关闭数据库连接也是需要耗时的

5. 单纯的批量添加或者批量删除操作

【注:本文源自网络文章资源,由站长整理发布】


web 前端中文站 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:程序员过关斩将–数据库快速迁移 10 亿级数据
喜欢 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址