--- theme: qklhk-chocolate highlight: a11y-dark --- 在老东家工作 3 年了，公司的业务和技术栈相对熟练得差不多了。领导觉得我能够委以重任，便把一个新项目交给我负责，另外指派一名同事协助我。项目的重点在于数据的交互比较多，以及每天大量的数据同步和批量操作，不能出错。队友建议以短、平、快为主，能够使用已有现成的技术就用现成的技术。直接面向过程开发是人们最为舒适，是人为本能的习惯。由于他有这一种能够处理好的决心，便把数据批量操作这块委托于他。查看了以往公司现成一些写法，一部分是直接面向 SQL 写法批量插入，面对增量同步则先查出，存在的更新，不存在的插入。一部分是通过 Kafka 和后台任务原子操作。理论上这么操作结果也能成，但是看到修改记录，我就知道面临的需求变了很多变化很快，导致大量的更改。私底下询问负责人也了解出了太多问题，原本一劳永逸赶紧写完结果反而投入了更多的精力和时间。出于预防心理，也对那位同事进行了提醒并且加以思考再下手。不到一个月，我们就把项目上线了，并且没有出现数据上的错误，得到了领导的表扬。我们也提前收场，做一些小的优化，其余时间在摸鱼。一段时间之后，麻烦便接踵而至，其一就是开始数据量暴增，那位同事在做增量同步时进行了锁表操作，批量操作需要一些时间，在前台读取时出现响应超时。其二就是增量同步要调整，以主库或第三方来源库为主，出现数据更新和删除的需要同步操作。同事目前的主力放在了新项目上，把一些零散的时间用来调整需求和 bug，结果越处理，bug 出现的越多，不是数量过多卡死就是变量不对导致数据处理不对。于是到了某一时刻终于爆发，领导找到我俩，被痛批一顿，工作这么久就只会 CRUD 操作，来的实习生都会干的活，还养你们干什么。当然，要复盘的话当然有迹可循。我想碰见这种情况还真不少，首次开发项目时一鼓作气，以“短、平、快” 战术面向过程开发，短时间内上线。但是，一个软件的生命周期可不止步于上线，还要过程运维以及面对变化。导致在二次开发的时候就脱节了，要么当时写法不符合现有业务，要么改动太多动不动就割到了大动脉大出血，要么人跑了... 所以我们会采用面向对象，抽象化编程，就是用来保稳定，预留一部分来应付变化，避免牵一发而动全身。挨完骂，也要开始收拾烂摊子。于是我打算重新组装一个通用的方法，打算一劳永逸。首先我们定义一个接口通用思维 IDbAsyncBulk。由于源码已经发布到了github，所以一些注释写成了英文，大致也能看出蹩脚英文的注释。 ``` public interface IDbAsyncBulk { ///

/// default init. /// use reflect to auto init all type, to lower case database fileds,and default basic type. /// if ignore some fileds,please use DbBulk,Ignore property to remarkable fileds. /// if other operating,need user-defined to init operate. ///

/// Corresponding type Task InitDefaultMappings(); ///

/// batch operating ///

/// will operate object entity type. /// database connecting string. /// target table name. /// will operate data list. Task CopyToServer(string connection_string, string targetTable, List list); ///

/// batch operating ///

/// will operate object entity type. /// database connecting string.need to check database connecting is openning. /// if nothing other follow-up operate, shouldn't cover this connecting. /// target table name. /// will operate data list. Task CopyToServer(DbConnection connection, string targetTable, List list); ///

/// renew as it exists，insert as it not exists. /// follow up : /// 1.create temporary table /// 2.put data into temporary table. /// 3.merge data to target table. ///

/// data type /// connecting string /// mapping orignal table and target table fileds,need primary key and data only,if not will throw error. /// target table name. /// will operate data list. /// put data into temporary table,default name as 'target table name + # or _temp' /// need to insert column，if is null,just use Mapping fileds，in order to avoid auto-create column /// need to modify column，if is null,just use Mapping fileds Task MergeToServer(string connection_string, List keys, string targetTable, List list, string tempTable = null, List insertmapping = null, List updatemapping = null); ///

/// renew as it exists，insert as it not exists. /// follow up : /// 1.create temporary table /// 2.put data into temporary table. /// 3.merge data to target table. ///

/// data type /// database connecting string.need to check database connecting is openning. /// mapping orignal table and target table fileds,need primary key and data only,if not will throw error. /// target table name. /// will operate data list. /// put data into temporary table,default name as 'target table name + # or _temp' /// need to insert column，if is null,just use Mapping fileds，in order to avoid auto-create column /// need to modify column，if is null,just use Mapping fileds Task MergeToServer(DbConnection connection, List keys, string targetTable, List list, string tempTable = null, List insertmapping = null, List updatemapping = null); ///

/// batch update operating。 /// 1.create temporary table /// 2.put data into temporary table. /// 3.merge data to target table. ///

/// data type /// connecting string /// matching 'where' compare fileds. /// need to update fileds. /// target table name /// will operate data list. /// put data into temporary table,default name as 'target table name + # or _temp' Task UpdateToServer(string connection_string, List where_name, List update_name, string targetTable, List list, string tempTable = null); ///

/// batch update operating。 /// 1.create temporary table /// 2.put data into temporary table. /// 3.merge data to target table. ///

/// data type /// connecting string /// matching 'where' compare fileds. /// need to update fileds. /// target table name /// will operate data list. /// put data into temporary table,default name as 'target table name + # or _temp' /// create temporary table or not Task UpdateToServer(DbConnection connection, List where_name, List update_name, string targetTable, List list, string tempTable = null, bool createtemp = true); ///

/// renew as it exists，insert as it not exists.original table not exist and target table exist will remove. /// 1.create temporary table /// 2.put data into temporary table. /// 3.merge data to target table. /// 4.will remove data that temporary data not exist and target table exist. ///

/// data type /// connecting string /// mapping orignal table and target table fileds,need primary key and data only,if not will throw error. /// target table name /// will operate data list. /// put data into temporary table,default name as 'target table name + # or _temp' /// need to insert column，if is null,just use Mapping fileds，in order to avoid auto-create column /// need to modify column，if is null,just use Mapping fileds Task MergeAndDeleteToServer(string connection_string, List keys, string targetTable, List list, string tempTable = null, List insertmapping = null, List updatemapping = null); ///

/// data type /// database connecting string.need to check database connecting is openning. /// mapping orignal table and target table fileds,need primary key and data only,if not will throw error. /// target table name /// will operate data list. /// put data into temporary table,default name as 'target table name + # or _temp' /// need to insert column，if is null,just use Mapping fileds，in order to avoid auto-create column /// need to modify column，if is null,just use Mapping fileds Task MergeAndDeleteToServer(DbConnection connection, List keys, string targetTable, List list, string tempTable = null, List insertmapping = null, List updatemapping = null); ///

/// create temporary table ///

/// create temporary table name /// rarget table name /// database connecting Task CreateTempTable(string tempTable, string targetTable, DbConnection connection); } ``` 解释几个方法的作用： > InitDefaultMappings：初始化映射，将目标表的字段映射到实体，在批量操作时候会根据反射进行一一匹配表字段； > > > > > CopyToServer：批量新增，在符合数据表结构时批量复制到目标表，采用官方 SqlBulkCopy 类结合实体简化操作。 > > > > > MergeToServer：增量同步，需指定唯一键，存在即更新，不存在则插入。支持指定更新字段，指定插入字段。 > > > > > UpdateToServer：批量更新，需指定 where 条件，以及更新的字段。 > > > > > MergeAndDeleteToServer：增量同步，以数据源和目标表进行匹配，目标表存在的则更新，不存在的则插入，目标表存在，数据源不存在则目标表移除。 > > > > > CreateTempTable：创建临时表。 > > > 增加实体属性标记，用来标记列名是否忽略同步数据，以及消除数据库别名，大小写的差异。 ``` ///

/// 数据库批量操作标记，用于标记对象属性。 ///

public class DbBulkAttribute : Attribute { ///

/// 是否忽略。忽略则其余属性不需要设置，不忽略则必须设置Type。 ///

public bool Ignore { get; set; } ///

/// 列名，不设置则默认为实体字段名小写 ///

public string ColumnName { get; set; } } ``` 实现类，目前仅支持 SqlServer 数据库，正在更新 MySql 和 PGSql 中。然后需要定义BatchSize(default 10000)、BulkCopyTimeout (default 300)、ColumnMappings，分别是每批次大小，允许超时时间和映射的字段。 ``` ///

/// sql server batch ///

public class SqlServerAsyncBulk : IDbAsyncBulk { ///

/// log recoding ///

private ILogger _log; ///

///batch insert size（handle a batch every time ）。default 10000。 ///

public int BatchSize { get; set; } ///

/// overtime,default 300 ///

public int BulkCopyTimeout { get; set; } ///

/// columns mapping ///

public Dictionary ColumnMappings { get; set; } ///

/// structure function ///

/// public SqlServerAsyncBulk(ILogger log) { _log = log; BatchSize = 10000; BulkCopyTimeout = 300; } //...to do ``` 使用上也非常的简便，直接在服务里注册单例模式，使用的时候直接依赖注入。 ``` //if you use SqlServer database, config SqlServerAsyncBulk service. services.AddSingleton(); ``` ``` public class BatchOperate { private readonly IDbAsyncBulk _bulk; public BatchOperate(IDbAsyncBulk bulk) { _bulk = bulk; } } ``` 以 user_base 表举两个实例,目前测试几十万数据也才零点几秒。 ``` public async Task CopyToServerTest() { var connectStr = @"Data Source=KF009\SQLEXPRESS;Initial Catalog=MockData;User ID=xxx;Password=xxx"; await _bulk.InitDefaultMappings(); var mock_list = new List(); for (var i = 0; i < 1000; i++) { mock_list.Add(new UserBaseModel { age = i, birthday = DateTime.Now.AddMonths(-i).Date, education = "本科", email = "xiaoyu@163.com", name = $"小榆{i}", nation = "汉", nationality="中国" }); } await _bulk.CopyToServer(connectStr, "user_base", mock_list); } ``` ``` public async Task MergeToServerTest() { var connectStr = @"Data Source=KF009\SQLEXPRESS;Initial Catalog=MockData;User ID=sa;Password=root"; await _bulk.InitDefaultMappings(); var mock_list = new List(); for (var i = 0; i < 1000; i++) { mock_list.Add(new UserBaseModel { age = i, birthday = DateTime.Now.AddMonths(-i).Date, education = "本科", email = "mock@163.com", name = $"小榆{i}", nation = "汉", nationality = "中国" }); } var insertMapping = new List { "birthday", "education", "age", "email", "name", "nation", "nationality" }; var updateMapping = new List { "birthday", "education", "age", "email"}; await _bulk.MergeToServer(connectStr,new List {"id"}, "user_base", mock_list,null, insertMapping, updateMapping); ``` 到这里，也已经完成了批量数据操作啦，不用再面对大量的sql操作啦。面向 sql 开发一时确实爽，但是面临变化或者别人接手的时候，是很痛苦的。具体实现细节内容过多，篇幅有限暂时不全部展示，有兴趣或者尝试的伙伴可以进 github 进行参考。 > github👉：https://github.com/sangxiaoyu/MergeServer 💖