NewLife.XCode是一个有15年历史的开源数据中间件，支持netcore/net45/net40，由新生命团队(2002~2020)开发完成并维护至今，以下简称XCode。
整个系列教程会大量结合示例代码和运行日志来进行深入分析，蕴含多年开发经验于其中，代表作有百亿级大数据实时计算项目。
开源地址：https://github.com/NewLifeX/X （求star, 1067+）
在大数据分析处理中，需要对海量数据进行添删改操作，常规单行操作难以满足要求，批量操作势在必行！
飞仙（http://feixian.newlifex.com/）有收藏各种数据库批量插入数据的性能排行榜，其中MySql冠军是60万tps，SQLite冠军是56.6万tps！
！！阅读本文之前，建议阅读：https://www.yuque.com/smartstone/xcode/curd
常规MySql数据库的单行添加性能只有3000tps左右，而使用批量添加以后可轻松增加到20000tps。
先来看批量插入用户：
var list = new List<UserX>(); for (var i = 0; i < 5; i++) { list.Add(new UserX { Name = "name" + i }); } list.Insert(true);
这是一个对IEnumerable<TEntity>的扩展方法，在支持批量插入的数据库上走批量插入流程，其它走for循环插入。参数true表示启用事务保护，早期不支持批量插入的SQLite版本，事务插入特别重要，100倍以上性能差异。
支持批量插入的数据库技术：
Insert Into table(column1, column2),(v11, v12) values(v21, v22) ... ,(vn1, vn2)
尽管各家技术截然不同，但XCode做了很好的封装，可以无视底层差别。
PostgreSQL其实也支持MySql那样的批量插入，但是XCode用户极少用PostgreSQL，因此没有封装。
上面批量插入用户代码，在SQLite上得到的SQL语句
Insert Into User(Name,Password,DisplayName,Sex,Mail,Mobile,Code,Avatar,RoleID,RoleIDs,DepartmentID,Online,Enable,Logins,LastLogin,LastLoginIP,RegisterTime,RegisterIP,Ex1,Ex2,Ex3,Ex4,Ex5,Ex6,UpdateUser,UpdateUserID,UpdateIP,UpdateTime,Remark) Values('name0',null,null,0,null,null,null,null,0,null,0,0,0,0,null,null,null,null,0,0,0,null,null,null,null,0,null,'0001-01-01 00:00:00',null), ('name1',null,null,0,null,null,null,null,0,null,0,0,0,0,null,null,null,null,0,0,0,null,null,null,null,0,null,'0001-01-01 00:00:00',null), ('name2',null,null,0,null,null,null,null,0,null,0,0,0,0,null,null,null,null,0,0,0,null,null,null,null,0,null,'0001-01-01 00:00:00',null), ('name3',null,null,0,null,null,null,null,0,null,0,0,0,0,null,null,null,null,0,0,0,null,null,null,null,0,null,'0001-01-01 00:00:00',null), ('name4',null,null,0,null,null,null,null,0,null,0,0,0,0,null,null,null,null,0,0,0,null,null,null,null,0,null,'0001-01-01 00:00:00',null)
此外，还有一个BatchInsert扩展，允许指定要批量插入的列
var list = new List<UserX>(); for (var i = 0; i < 5; i++) { list.Add(new UserX { Name = "name" + i }); } var columns = UserX.Meta.Table.DataTable.Columns.Where(e => e.Name == "Name").ToArray(); list.BatchInsert(columns);
得到的SQL语句
Insert Into User(Name) Values('name0'),('name1'),('name2'),('name3'),('name4')
虽然批量插入性能很高，但并不是越多越好，根据经验，尽量把每一批待插入数据控制在一万行以内，再多的话，生成的Insert语句过长，也是够吃力的。
显而易见，MySql/SQLite的技术通用性强，但是开发者拼接比较吃力；Oracle的批操作技术更灵活，SqlServer需要引入专用依赖，限制有些大。如果各家ADO.Net都能像Oracle这样统一支持批量操作就好了。
在XCode中，强烈建议仅在百万级以上数据表中使用批量插入技术，不建议几十几百行的表也使用，因为它有一些缺点，譬如插入后无法得到自增ID，跟普通循环逐行插入的行为不同。
只有Oracle支持批量更新，具体技术跟批量插入一样，因为它是由ADO.Net驱动提供支持。
SqlServer理论上也支持，但没有经过测试。
MySql有Replace之类的操作，但它毕竟不是批量Update。
来看看批量更新的两个扩展
public static Int32 Update<T>(this IEnumerable<T> list, Boolean? useTransition = null) where T : IEntity; public static Int32 BatchUpdate<T>(this IEnumerable<T> list, IDataColumn[] columns = null, ICollection<String> updateColumns = null, ICollection<String> addColumns = null) where T : IEntity;
对于非Oracle数据库，Update扩展将会走for循环逐行更新。
BatchUpdate支持指定要覆盖更新或者累加更新的字段。
小数据量建议循环更新而不是批量更新！
批量Upsert，这是一个丝毫不逊色于批量Insert的大杀器。
在多节点多线程的大数据分析中，很可能多线程都需要修改同一张表，譬如写入统计数据。传统的查找并决定插入或更新很容易带来多线程冲突问题，并且在大表中性能很差。如果能够让数据库决定有则更新无则插入就好了，那就是Upsert，并且是批量Upsert。
MySql的Upsert技术
insert into stat (siteid,statdate,`count`,cost,createtime,updatetime) values (1,'2018-08-11 09:34:00',1,123,now(),now()), (2,'2018-08-11 09:34:00',1,456,now(),now()), (3,'2018-08-11 09:34:00',1,789,now(),now()), (2,'2018-08-11 09:34:00',1,456,now(),now()) on duplicate key update `count`=`count`+values(`count`),cost=cost+values(cost), updatetime=values(updatetime);
SQLite的Upsert技术
insert into stat (siteid,statdate,`count`,cost,createtime,updatetime) values (1,'2018-08-11 09:34:00',1,123,now(),now()), (2,'2018-08-11 09:34:00',1,456,now(),now()), (3,'2018-08-11 09:34:00',1,789,now(),now()), (2,'2018-08-11 09:34:00',1,456,now(),now()) On Conflict(siteid,statdate) Do Update Set count=count+excluded.count,cost=cost+excluded.cost, updatetime=excluded.updatetime;
跟MySql很像，但是要指定一个唯一索引的字段，很不方便。
Oracle的技术
var sb = Pool.StringBuilder.Get(); sb.AppendLine("BEGIN"); sb.AppendLine(insert + ";"); sb.AppendLine("EXCEPTION"); // 没有更新时，直接返回，可用于批量插入且其中部分有冲突需要忽略的场景 if (!update.IsNullOrEmpty()) { sb.AppendLine("WHEN DUP_VAL_ON_INDEX THEN"); sb.AppendLine(update + ";"); } else { //sb.AppendLine("WHEN OTHERS THEN"); sb.AppendLine("WHEN DUP_VAL_ON_INDEX THEN"); sb.AppendLine("RETURN;"); } sb.AppendLine("END;");
SqlServer的技术
// 先更新，根据更新结果影响的条目数判断是否需要插入 var sb = Pool.StringBuilder.Get(); sb.Append(update); sb.AppendLine(";"); sb.AppendLine("IF(@@ROWCOUNT = 0)"); sb.AppendLine("BEGIN"); sb.Append(insert); sb.AppendLine(";"); sb.AppendLine("END;");
来个批量更新用户的例子：
var list = new List<UserX>(); for (var i = 0; i < 5; i++) { list.Add(new UserX { ID = i + 1, Name = "name" + i }); } list.Upsert();
在SQLite上得到语句
Insert Into User(Name,Password,DisplayName,Sex,Mail,Mobile,Code,Avatar,RoleID,RoleIDs,DepartmentID,Online,Enable,Logins,LastLogin,LastLoginIP,RegisterTime,RegisterIP,Ex1,Ex2,Ex3,Ex4,Ex5,Ex6,UpdateUser,UpdateUserID,UpdateIP,UpdateTime,Remark) Values('name0',null,null,0,null,null,null,null,0,null,0,0,0,0,null,null,null,null,0,0,0,null,null,null,null,0,null,'0001-01-01 00:00:00',null), ('name1',null,null,0,null,null,null,null,0,null,0,0,0,0,null,null,null,null,0,0,0,null,null,null,null,0,null,'0001-01-01 00:00:00',null), ('name2',null,null,0,null,null,null,null,0,null,0,0,0,0,null,null,null,null,0,0,0,null,null,null,null,0,null,'0001-01-01 00:00:00',null), ('name3',null,null,0,null,null,null,null,0,null,0,0,0,0,null,null,null,null,0,0,0,null,null,null,null,0,null,'0001-01-01 00:00:00',null), ('name4',null,null,0,null,null,null,null,0,null,0,0,0,0,null,null,null,null,0,0,0,null,null,null,null,0,null,'0001-01-01 00:00:00',null) On Conflict(Name) Do Update Set Name=excluded.Name,Logins=Logins+excluded.Logins
这样表有个唯一索引Name字段，同时Logins打开了累加，因此生成的语句也有所不同。
实体列表的批量删除扩展并非数据库功能，而是由XCode检测主键，构造in操作的delete语句。
批量删除用户的例子：
var list = new List<UserX>(); for (var i = 0; i < 5; i++) { list.Add(new UserX { ID = i + 1, Name = "name" + i }); } list.Delete();
得到语句
Delete From User Where ID In(1,2,3,4,5)
最后再次提醒，批量操作不是万能灵药，一定要慎用！
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。