NewLife.XCode是一個有15年歷史的開源數據中間件，支援netcore/net45/net40，由新生命團隊(2002~2020)開發完成並維護至今，以下簡稱XCode。

整個系列教程會大量結合示例程式碼和運行日誌來進行深入分析，蘊含多年開發經驗於其中，代表作有百億級大數據實時計算項目。

開源地址：//github.com/NewLifeX/X （求star, 1067+）

 

在大數據分析處理中，需要對海量數據進行添刪改操作，常規單行操作難以滿足要求，批量操作勢在必行！

飛仙（//feixian.newlifex.com/）有收藏各種資料庫批量插入數據的性能排行榜，其中MySql冠軍是60萬tps，SQLite冠軍是56.6萬tps！

！！閱讀本文之前，建議閱讀

 

 

批量添加

常規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)

 

最後再次提醒，批量操作不是萬能靈藥，一定要慎用！