如何半天迁移数据库10亿级别的数据

一、问题分析经过几分钟排查，数据库情况如下：数据库使用SQLserver2008R2，单台数据量table是21亿，没有水平或垂直分割，但使用了分区表。分区表策略按时间从大到小划分分区，近30个分区。正是因为有了分区表，系统才保证了到现在性能都不会太差。这张表除了聚簇索引外，没有其他索引，也没有主键（主键其实是用索引来快速查重）。因此，在频繁插入新数据的情况下，索引调整所消耗的性能是比较低的。至于聚簇索引和非聚簇索引的知识请自行google或百度。至于业务，它并不太复杂。咨询过相关人员，大约40%的请求是单Inserts，大约60%的请求是通过class_id和in_time（倒序）分页获取数据。select请求全部命中聚集索引，所以性能非常高。这也是聚簇索引设计的目的。2、解决问题由于单表数据量已经超过21亿，2017年之前的数据对业务几乎没有影响，所以决定将2017年之前（不含2017年）的数据迁移到新表中，只针对特殊情况在以后的业务查询中使用。经过查询，大约有9亿条数据量。数据迁移工作包括三个步骤：从源数据表中查询需要迁移的数据，将数据插入新表，删除旧表中的数据。因为你的内存不可能一次加载9亿条数据。1）通过分页的方式从源数据表中获取数据，具体的分页条目个数，太小会导致原表查询太频繁，太大则查询太慢。SQL语句类似于：SELECT*FROM(SELECT*,ROW_NUMBER()OVER(ORDERBYclass_id,in_time)pFROMtablexxWHEREin_time<'2017.1.1')tWHEREt.pBETWEEN1AND1002)将查询到的数据插入到目标数据表中。这里强调一下，不要使用Single插入策略一定要使用batch插入。3)删除数据。其实这里删除还是有一点难度的。该表没有标记的列。这里就不展开了，因为这不是本文的重点。如果你的数据量不大，上面的方法完全没问题，但是在9亿这个数字面前，上面的方法就显得绰绰有余了。一个字：慢，太慢，很慢。可以粗略计算一下，如果每秒可以迁移1000条数据，大概需要的时间是（单位：分钟）：900000000/1000/60=15000（分钟）大约需要10天^V^2，传统方法以上改进方法的缺点在哪里？查询一定要命中9亿条数据之前的索引，即使是非聚集索引我也不推荐，聚集索引是首选。如果了解索引的原理，就应该明白，当不断插入新数据时，索引会不断更新和调整，以保持树的平衡等特性。聚簇索引的影响尤其大，因为实际数据也需要移动。提取以上两点的共同元素，即聚簇索引。相应的解决方案应运而生：根据聚簇索引分页索引查询数据；批量插入数据迎合聚集索引，即：按照聚集索引的顺序批量插入；根据聚簇索引的顺序批量删除；由于是表分区，如果有的话，2017年之前的分区直接在物理磁盘层面与当前表分离，然后挂载到另一张表上，堪称神级操作。谁能指导一下，不胜感激~3.扩展阅读表的聚簇索引顺序就是实际数据文件的顺序，它们映射到磁盘上，本质上是位于同一个磁道上，因此磁头在操作时无需跳动即可操作。硬盘中存储的每个文件都可以分为两部分：文件头和存储数据的数据区。文件头用于记录文件名、文件属性、占用的簇号等信息，文件头以簇的形式存储并映射到FAT表（文件分配表）中。真正的数据存放在数据区。通常的删除其实就是修改文件头的前两个代码。该修改被映射到FAT表中，文件被标记为删除，并在FAT表中清除该文件占用的簇号注册项，意思是释放空间，这就是为什么通常删除文件后硬盘空间会增加的原因。但是真正的文件内容仍然保存在数据区，并没有被删除。需要等到以后的数据写入后，再覆盖这个数据区，才能彻底删除原来的数据。它不会从磁盘中删除，除非它被后来保存的数据覆盖。4.第一步实际运行代码：由于聚簇索引需要class_id，所以宁愿花2-4秒查询要操作的class_id（ORM是dapper），按升序排列：DateTimedtMax=DateTime。Parse("2017.1.1");varallClassId=DBProxy.GeSourcetLstClassId(dtMax)?.OrderBy(s=>s);按照第一步class_id列表的顺序查询数据，获取pages中的每一个class_id，然后插入目标表，然后删除源表对应class_id的数据。dintpageIndex=1;//页码intpageCount=20000;//每页数据数DataTabletempData=null;intsuccessCount=0;foreach(varclassIdinallClassId){tempData=null;pageIndex=1;while(true){intstartIndex=(pageIndex-1)*pageCount+1;intendIndex=pageIndex*pageCount;tempData=DBProxy.GetSourceDataByClassIdTable(dtMax,classId,startIndex,endIndex);if(tempData==null||tempData.Rows.Count==0){//结束一页面没有数据，删除源数据sourcedata跳出$"class:{classId}Completed,completed:{successCount}");}DBProxy完整代码:classDBProxy{//获取待迁移数据的所有classesidpublicstaticIEnumerableGeSourcetLstClassId(DateTimedtMax){varconnection=Config.GetConnection(Config.SourceDBStr);stringSql=@"SELECTclass_idFROMtablexxWHEREin_time<@dtMaxGROUPBYclass_id";using(connection){returnconnection.Query(Sql,new{dtMax=dtMax},commandType:System.Data.CommandType.Text);}}publicstaticDataTableGetSourceDataByClassIdTable(DateTimedtMax,intclassId,intstartIndex,intendIndex){varconnection=Config.GetConnection(Config.SourceDBStr);stringSql=@"SELECT*FROM(SELECT*,ROW_NUMBER()OVER(ORDERBYin_timedesc)pFROMtablexxWHEREin_time<@dtMaxANDclass_id=@classId)tWHEREt。pBETWEEN@startIndexAND@endIndex";using(connection){DataTabletable=newDataTable("MyTable");varreader=connection.ExecuteReader(Sql,new{dtMax=dtMax,classId=classId,startIndex=startIndex,endIndex=endIndex},commandType:System.Data.CommandType.Text);table.Load(reader);reader.Dispose();returntable;}}publicstaticintDeleteSourceClassData(DateTimedtMax,intclassId){varconnection=Config.GetConnection(Config.SourceDBStr);stringSql=@"deletefromtablexxWHEREin_time<@dtMaxANDclass_id=@classId";using(connection){returnconnection.Execute(Sql,new{dtMax=dtMax,classId=classId},commandType:System.Data.CommandType.Text);}}//SqlBulkCopy批量添加数据publicstaticintAddTargetData(数据表数据）{varconnection=Config.GetConnection(Config.TargetDBStr);using(varsbc=newSqlBulkCopy(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);}}return1;}}运行报告：程序在本地运行，开启一个virtualprivate网络连接远程DB服务器运行1分钟，迁移数据量为1915560条，约30000条数据/秒1915560/60=31926条/秒CPU情况（不高）：磁盘队列情况（不高）：5、写入以后，以下情况会提高速度：源数据库和目标数据库的硬盘是ssd，并且是不同的服务器；迁移程序与数据库在同一个局域网内，保证数据传输时带宽不会成为瓶颈；SqlBulkCopy参数的合理设置；在我们的大部分场景下，每批插入的数据量并没有达到设定值，因为有的class_id对应的数据量只有几十条，甚至几条，打开和关闭都需要时间数据库连接；简单的批量添加或批量删除操作。