[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教程系列[2019版]

增删改查入门。快速展现用法，代码配置连接字符串
数据模型文件。建立表格字段和索引，名字以及数据类型规范，推荐字段（时间，用户，IP）
实体类详解。数据类业务类，泛型基类，接口
功能设置。连接字符串，调试开关，SQL日志，慢日志，参数化，执行超时。代码与配置文件设置，连接字符串局部设置
反向工程。自动建立数据库数据表
数据初始化。InitData写入初始化数据
高级增删改。重载拦截，自增字段，Valid验证，实体模型（时间，用户，IP）
脏数据。如何产生，怎么利用
增量累加。高并发统计
事务处理。单表和多表，不同连接，多种写法
扩展属性。多表关联，Map映射
高级查询。复杂条件，分页，自定义扩展FieldItem，查总记录数，查汇总统计
数据层缓存。Sql缓存，更新机制
实体缓存。全表整理缓存，更新机制
对象缓存。字典缓存，适用用户等数据较多场景。
百亿级性能。字段精炼，索引完备，合理查询，充分利用缓存
实体工厂。元数据，通用处理程序
角色权限。Membership
导入导出。Xml，Json，二进制，网络或文件
分表分库。常见拆分逻辑
高级统计。聚合统计，分组统计
批量写入。批量插入，批量Upsert，异步保存
实体队列。写入级缓存，提升性能。
备份同步。备份数据，恢复数据，同步数据
数据服务。提供RPC接口服务，远程执行查询，例如SQLite网络版
大数据分析。ETL抽取，调度计算处理，结果持久化

posted @ 2022-01-11 13:44 大石头阅读(868) 评论(1) 编辑收藏举报

刷新页面返回顶部

物联网、大数据

我不相信神话，我只相信汗水！我不相信命运，我只相信双手！https://newlifex.com
C#/.Net群：1600800 物联网/GD32/STM32群：1600838

[NewLife.XCode]批量添删改操作（提升吞吐率）

批量添加

批量更新

批量添加或更新

批量删除

系列教程

公告

物联网、大数据

我不相信神话，我只相信汗水！我不相信命运，我只相信双手！https://newlifex.com C#/.Net群：1600800 物联网/GD32/STM32群：1600838

[NewLife.XCode]批量添删改操作（提升吞吐率）

批量添加

批量更新

批量添加或更新

批量删除

系列教程

公告

我不相信神话，我只相信汗水！我不相信命运，我只相信双手！https://newlifex.com
C#/.Net群：1600800 物联网/GD32/STM32群：1600838