[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。

先来看批量插入用户：

代码语言：javascript复制

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语句

代码语言：javascript复制

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扩展，允许指定要批量插入的列

代码语言：javascript复制

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语句

代码语言：javascript复制

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。

来看看批量更新的两个扩展

代码语言：javascript复制

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技术

代码语言：javascript复制

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技术

代码语言：javascript复制

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的技术

代码语言：javascript复制

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的技术

代码语言：javascript复制

// 先更新，根据更新结果影响的条目数判断是否需要插入
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;");

来个批量更新用户的例子：

代码语言：javascript复制

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上得到语句

代码语言：javascript复制

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语句。

批量删除用户的例子：

代码语言：javascript复制

var list = new List<UserX>();
for (var i = 0; i < 5; i  )
{
    list.Add(new UserX { ID = i   1, Name = "name"   i });
}
list.Delete();

得到语句

代码语言：javascript复制

Delete From User Where ID In(1,2,3,4,5)

最后再次提醒，批量操作不是万能灵药，一定要慎用！

oracle xcode 编程算法大数据批量计算

0 人点赞