[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();
for (var i = 0; i < 5; i++)
{
    list.Add(new UserX { Name = "name" + i });
}
list.Insert(true);

这是一个对IEnumerable的扩展方法，在支持批量插入的数据库上走批量插入流程，其它走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();
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(this IEnumerable list, Boolean? useTransition = null) where T : IEntity;
public static Int32 BatchUpdate(this IEnumerable list, IDataColumn[] columns = null, ICollection updateColumns = null, ICollection 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();
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();
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版]

  1. 。快速展现用法，代码配置连接字符串
  2. 。建立表格字段和索引，名字以及数据类型规范，推荐字段（时间，用户，IP）
  3. 。数据类业务类，泛型基类，接口
  4. 。连接字符串，调试开关，SQL日志，慢日志，参数化，执行超时。代码与配置文件设置，连接字符串局部设置
  5. 。自动建立数据库数据表
  6. 。InitData写入初始化数据
  7. 。重载拦截，自增字段，Valid验证，实体模型（时间，用户，IP）
  8. 。如何产生，怎么利用
  9. 。高并发统计
  10. 。单表和多表，不同连接，多种写法
  11. 。多表关联，Map映射
  12. 。复杂条件，分页，自定义扩展FieldItem，查总记录数，查汇总统计
  13. 。Sql缓存，更新机制
  14. 。全表整理缓存，更新机制
  15. 。字典缓存，适用用户等数据较多场景。
  16. 。字段精炼，索引完备，合理查询，充分利用缓存
  17. 。元数据，通用处理程序
  18. 。Membership
  19. 。Xml，Json，二进制，网络或文件
  20. 。常见拆分逻辑
  21. 。聚合统计，分组统计
  22. 。批量插入，批量Upsert，异步保存
  23. 。写入级缓存，提升性能。
  24. 。备份数据，恢复数据，同步数据
  25. 。提供RPC接口服务，远程执行查询，例如SQLite网络版
  26. 。ETL抽取，调度计算处理，结果持久化 
CXcode大数据.NETCore

相关

C# 十六进制八进制前缀或后缀

C#中的Attribute（特性）的使用

一、Asp.Net Core WebAPI——修改默认监听端口

c#小灶——9.算术运算符

c#小灶——4.注释和代码规范

c#小灶——3.输出语句

.net core 2.2 部署CentOS7（3）安装Xshell操控CentOS7

Xcode Build Settings

.net core 部署在Centos 7/8 的几个小坑

通俗易懂，什么是.NET?什么是.NET Framework？什么是.NET Core?

【Luogu3414】SAC#1 - 组合数

[C#] 使用递归将多条路径生成树

标签