[NewLife.XCode]批量添删改操作（提升吞吐率）
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倍以上性能差异。
支持批量插入的数据库技术：
- MySql、SQLite，生成带有多组values的insert语句，例如
Insert Into table(column1, column2),(v11, v12) values(v21, v22) ... ,(vn1, vn2)
- Oracle，还是普通的Insert语句，参数化，但每个数值变量传入数组而不是单个数值，同时设置OracleCommand.ArrayBindCount为行数，在设置OracleCommand.BindByName为true；
- SqlServer，借助特有的SqlBatcher来实现
尽管各家技术截然不同，但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)最后再次提醒，批量操作不是万能灵药，一定要慎用！
![[NewLife.XCode]实体队列（多线程生产的大数据集中保存）](https://ask.qcloudimg.com/http-save/yehe-1142010/b5748399024a8facd73a1d5429238dc3.png)
![[NewLife.XCode]分表分库（百亿级大数据存储）](https://ask.qcloudimg.com/http-save/yehe-1142010/6gg2508swn.png)
![[NewLife.XCode]导入导出（实体对象百变魔君）](https://ask.qcloudimg.com/http-save/yehe-1142010/sa2tilkba1.png)
腾讯云开发者
