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://newlifex.com/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)


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