還用foreach在MyBatis批量插入資料,伺服器還沒崩?
導讀 |
近日,專案中有一個耗時較長的Job存在CPU佔用過高的問題,經排查發現,主要時間消耗在往MyBatis中批量插入資料。
|
近日,專案中有一個耗時較長的Job存在CPU佔用過高的問題,經排查發現,主要時間消耗在往MyBatis中批量插入資料。mapper configuration是用foreach迴圈做的,差不多是這樣。(由於專案保密,以下程式碼均為自己手寫的demo程式碼)
insert into USER (id, name) values(#{model.id}, #{model.name})
這個方法提升批量插入速度的原理是,將傳統的:
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2"); INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2"); INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2"); INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2"); INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
轉化為:
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2"), ("data1", "data2"), ("data1", "data2"), ("data1", "data2"), ("data1", "data2");
在MySql Docs中也提到過這個trick,如果要優化插入速度時,可以將許多小型操作組合到一個大型操作中。理想情況下,這樣可以在單個連線中一次性傳送許多新行的資料,並將所有索引更新和一致性檢查延遲到最後才進行。
乍看上去這個foreach沒有問題,但是經過專案實踐發現,當表的列數較多(20+),以及一次性插入的行數較多(5000+)時,整個插入的耗時十分漫長,達到了14分鐘,這是不能忍的。在資料中也提到了一句話:
Of course don't combine ALL of them, if the amount is HUGE. Say you have 1000 rows you need to insert, then don't do it one at a time. You shouldn't equally try to have all 1000 rows in a single query. Instead break it into smaller sizes.
它強調,當插入數量很多時,不能一次性全放在一條語句裡。可是為什麼不能放在同一條語句裡呢?這條語句為什麼會耗時這麼久呢?我查閱了資料發現:
Insert inside Mybatis foreach is not batch, this is a single (could become giant) SQL statement and that brings drawbacks:
- some database such as Oracle here does not support.
- in relevant cases: there will be a large number of records to insert and the database configured limit (by default around 2000 parameters per statement) will be hit, and eventually possibly DB stack error if the statement itself become too large.
Iteration over the collection must not be done in the mybatis XML. Just execute a simple Insertstatement in a Java Foreach loop. The most important thing is the session Executor type.
SqlSession session = sessionFactory.openSession(ExecutorType.BATCH); for (Model model : list) { session.insert("insertStatement", model); } session.flushStatements();
Unlike default ExecutorType.SIMPLE, the statement will be prepared once and executed for each record to insert.
從資料中可知,預設執行器型別為Simple,會為每個語句建立一個新的預處理語句,也就是建立一個PreparedStatement物件。在我們的專案中,會不停地使用批量插入這個方法,而因為MyBatis對於含有的語句,無法採用快取,那麼在每次呼叫方法時,都會重新解析sql語句。
Internally, it still generates the same single insert statement with many placeholders as the JDBC code above.
MyBatis has an ability to cache PreparedStatement, but this statement cannot be cached because it contains element and the statement varies depending on the parameters. As a result, MyBatis has to 1) evaluate the foreach part and 2) parse the statement string to build parameter mapping [1] on every execution of this statement.
And these steps are relatively costly process when the statement string is big and contains many placeholders.
[1] simply put, it is a mapping between placeholders and the parameters.
從上述資料可知,耗時就耗在,由於我foreach後有5000+個values,所以這個PreparedStatement特別長,包含了很多佔位符,對於佔位符和引數的對映尤其耗時。並且,查閱相關資料可知,values的增長與所需的解析時間,是呈指數型增長的。
所以,如果非要使用 foreach 的方式來進行批量插入的話,可以考慮減少一條 insert 語句中 values 的個數,最好能達到上面曲線的最底部的值,使速度最快。一般按經驗來說,一次性插20~50行數量是比較合適的,時間消耗也能接受。
重點來了。上面講的是,如果非要用的方式來插入,可以提升效能的方式。而實際上,MyBatis文件中寫批量插入的時候,是推薦使用另外一種方法。(可以看 中 Batch Insert Support 標題裡的內容)
SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH); try { SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class); Listrecords = getRecordsToInsert(); // not shown BatchInsertbatchInsert = insert(records) .into(simpleTable) .map(id).toProperty("id") .map(firstName).toProperty("firstName") .map(lastName).toProperty("lastName") .map(birthDate).toProperty("birthDate") .map(employed).toProperty("employed") .map(occupation).toProperty("occupation") .build() .render(RenderingStrategy.MYBATIS3); batchInsert.insertStatements().stream().forEach(mapper::insert); session.commit(); } finally { session.close(); }
即基本思想是將 MyBatis session 的 executor type 設為 Batch ,然後多次執行插入語句。就類似於JDBC的下面語句一樣。
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&rewriteBatchedStatements=true","root","root"); connection.setAutoCommit(false); PreparedStatement ps = connection.prepareStatement( "insert into tb_user (name) values(?)"); for (int i = 0; i < stuNum; i++) { ps.setString(1,name); ps.addBatch(); } ps.executeBatch(); connection.commit(); connection.close();
經過試驗,使用了 ExecutorType.BATCH 的插入方式,效能顯著提升,不到 2s 便能全部插入完成。
總結一下,如果MyBatis需要進行批量插入,推薦使用 ExecutorType.BATCH 的插入方式,如果非要使用 的插入的話,需要將每次插入的記錄控制在 20~50 左右。
原文來自:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69955379/viewspace-2845023/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MyBatis中批量插入資料,多重forEach迴圈MyBatis
- mybatis插入資料、批量插入資料MyBatis
- MyBatis 批量插入資料MyBatis
- MyBatis Plus 批量資料插入功能,yyds!MyBatis
- 還在使用SimpleDateFormat?你的專案崩沒?ORM
- mybatis中使用foreach構造多like查詢及批量插入MyBatis
- 使用Mybatis批量插入大量資料的實踐MyBatis
- Mybatis批量插入Oracle、MySQLMyBatisOracleMySql
- java中批量插入資料jdbc hibernate Mybatis使用方式JavaJDBCMyBatis
- 批量備份和還原資料庫資料庫
- oracle批量插入資料Oracle
- 伺服器重做raid,磁碟資料還在嗎伺服器AI
- MyBatis Batch Update Exception使用foreach批量update出錯MyBatisException
- 使用MySqlBulkLoader批量插入資料MySql
- 大表資料插入批量提交
- 海量資料處理_批量插入
- SQL語句批量插入資料SQL
- SqlBulkCopy 複製批量插入資料SQL
- Java通過Mybatis實現批量插入資料到Oracle中JavaMyBatisOracle
- 啟明星資料庫批量備份與還原工具資料庫
- 【MyBatis】幾種批量插入效率的比較MyBatis
- MySQL:JDBC批量插入資料的效率MySqlJDBC
- Mybatis批量插入,是否能夠返回id列表MyBatis
- Oracle批量插入資料insert all into用法Oracle
- 使用 【Ado.Net】 批量插入資料
- Redis從檔案中批量插入資料Redis
- MSSQL資料批量插入優化詳細SQL優化
- 向表中插入大批量資料
- 批量插入資料的儲存過程儲存過程
- Laravel 批量插入(如果資料存在刪除原資料)Laravel
- 插入大量資料速度慢的解決方法:批量插入
- foreach 實現 MyBatis 遍歷集合與批量運算元據MyBatis
- Java MyBatis 插入資料庫返回主鍵JavaMyBatis資料庫
- Mybatis:插入資料返回自增主鍵MyBatis
- 使用SqlBulkCopy批量插入或遷移資料(轉)SQL
- 【UNDO】還原資料
- 還原資料庫資料庫
- 資料庫還原資料庫