菜菜呀, 咱们业务 BJKJ 有个表数据需要做迁移
程序员主力 Y 总
现在有多少数据?
菜菜
大约 21 亿吧, 2017 年以前的数据没有业务意义了, 给你半天时间把这个事搞定, 绩效给你 A
程序员主力 Y 总
有绩效奖金吗?
菜菜
钱的事你去问 X 总, 我当家不管钱
程序员主力 Y 总
...........
菜菜
问题分析
经过几分钟的排查, 数据库情况如下:
1. 数据库采用 Sqlserver 2008 R2, 单表数据量 21 亿
2. 无水平或者垂直切分, 但是采用了分区表. 分区表策略是按时间降序分的区, 将近 30 个分区. 正因为分区表的原因, 系统才保证了在性能不是太差的情况下坚持至今.
3. 此表除聚集索引之外, 无其他索引, 无主键(主键其实是利用索引来快速查重的). 所以在频繁插入新数据的情况下, 索引调整所耗费的性能比较低.
至于聚集索引和非聚集索引等知识, 请各位移步 google 或者百度.
至于业务, 不是太复杂. 经过相关人员咨询, 大约 40% 的请求为单条 Insert, 大约 60% 的请求为按 class_id 和 in_time(倒序)分页获取数据. Select 请求全部命中聚集索引, 所以性能非常高. 这也是聚集索引之所以这样设计的目的.
解决问题
由于单表数据量已经超过 21 亿, 并且 2017 年以前的数据几乎不影响业务, 所以决定把 2017 年以前 (不包括 2017 年) 的数据迁移到新表, 仅供以后特殊业务查询使用. 经过查询大约有 9 亿数据量.
数据迁移工作包括三个个步骤:
1. 从源数据表查询出要迁移的数据
2. 把数据插入新表
3. 把旧表的数据删除
传统做法
这里申明一点, 就算是传统的做法也需要分页获取源数据, 因为你的内存一次性装载不下 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 ^
改进做法
以上的传统做法弊端在哪里呢?
1. 在 9 亿数据前查询必须命中索引, 就算是非聚集索引菜菜也不推荐, 首推聚集索引.
2. 如果你了解索引的原理, 你应该明白, 不停的插入新数据的时候, 索引在不停的更新, 调整, 以保持树的平衡等特性. 尤其是聚集索引影响甚大, 因为还需要移动实际的数据.
提取以上两点共同的要素, 那就是聚集索引. 相应的解决方案也就应运而生:
1. 按照聚集索分页引查询数据
2. 批量插入数据迎合聚集索引, 即: 按照聚集索引的顺序批量插入.
3. 按照聚集索引顺序批量删除
由于做了表分区, 如果有一种方式把 2017 年以前的分区直接在磁盘物理层面从当前表剥离, 然后挂载到另外一个表, 可算是神级操作. 有谁能指导一下菜菜, 感激不尽
- DateTime dtMax = DateTime.Parse("2017.1.1");
- var allClassId = DBProxy.GeSourcetLstClassId(dtMax)?.OrderBy(s=>s);
- 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}个");
- }
- 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;
- }
- }
来源: https://www.cnblogs.com/zhanlang/p/10403332.html