[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抽取，調度計算處理，結果持久化
浙公網安備 33010602011771號