MyBatis 批量操作的五個坑，千萬不要踩了！
大家好，我是君哥。
在日常開發(fā)中，為了提高操作數(shù)據(jù)庫效率，我們往往會選擇批量操作，比如批量插入、批量更新，這樣可以減少程序和數(shù)據(jù)庫的交互，減少執(zhí)行時間。但是批量操作往往隱藏著一些坑，使用不當(dāng)，很可能會造成生產(chǎn)事故。
今天來分享使用 MyBatis 批量操作可能會遇到的一些坑。下面我們以一張員工信息表為例進行講解，建表 SQL 如下(MySQL)：
CREATE TABLE `staff` (
`staff_id` tinyint(3) NOT NULL COMMENT '員工編號',
`name` varchar(20) DEFAULT NULL COMMENT '員工姓名',
`age` tinyint(3) DEFAULT NULL COMMENT '年齡',
`sex` tinyint(1) DEFAULT '0' COMMENT '性別，0:男 1:女',
`address` varchar(300) DEFAULT NULL COMMENT '家庭住址',
`email` varchar(200) DEFAULT NULL COMMENT '郵件地址',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf81.查詢條數(shù)
<select id="getStaffList" parameterType="int" resultType="Admin">
select * from staff limit #{offset},50000
</select>對應(yīng) Java 代碼如下：
public List<Staff> processStaffList(){
int offset = 0;
List<Staff> staffList = staffDao.getStaffList(offset);
while(true){
//...處理邏輯
if(staffList.size() < 10000){
break;
}
offset += 10000;
staffList = staffDao.getStaffList(offset);
}
}上面的查詢想一次想查回 50000 條數(shù)據(jù)，很有可能數(shù)據(jù)庫不能返回 50000 條。一般數(shù)據(jù)庫都有查詢結(jié)果集限制，比如 MySQL 會受兩個參數(shù)的限制：
- max_allowed_packet，返回結(jié)果集大小，默認 4M，超過這個大小結(jié)果集就會被截斷；
- max_execution_time，一次查詢執(zhí)行時間，默認值是 0 表示沒有限制，如果超過這個時間，MySQL 會終止查詢，返回結(jié)果。
所以，如果結(jié)果集太大不能全部返回，而我們在代碼中每次傳入的 offset 都是基于上次的 offset 加 50000，那必定會漏掉部分數(shù)據(jù)。
2.分頁問題
<select id="getStaffList" resultType="Staff">
select * from staff limit #{offset},1000
</select>如果單表數(shù)據(jù)量非常大，offset 會很大造成深度分頁問題，查詢效率低下。我們可以通過傳入一個起始的 staffId 來解決深度分頁問題。
我們修改一下 xml 中的代碼：
<select id="getStaffList" resultType="Staff">
select * from staff
<if test="staffId != null">
WHERE staff_id > #{staffId}
</if>
order by staff_id limit 1000
</select>對應(yīng) Java 代碼如下：
public List<Staff> processStaffList(){
List<Staff> staffList = staffDao.getStaffList(null);
while(true){
//...處理邏輯
if(staffList.size() < 1000){
break;
}
Staff lastStaffInPage = staffList.get(staffList.size() - 1);
staffList = staffDao.getStaffList(lastStaffInPage.getStaffId());
}
}3.參數(shù)數(shù)量
下面看一下這一條插入 SQL：
<insert id="batchInsertStaff">
INSERT INTO staff (`staff_id`, `name`, `age`, `sex`, `address`, `email`) VALUES
<foreach collection="staffList" index="" item="item" separator=",">
(#{item.staffId,}, #{item.name},#{item.age},#{item.sex},#{item.address},#{item.email})
</foreach>
</insert>上面的代碼如果 staffList 數(shù)量太大，會導(dǎo)致整條語句參數(shù)過多。如果使用 Oracle 數(shù)據(jù)庫，參數(shù)數(shù)量超過 65535，會報 ORA-7445([opiaba]when using more than 65535 bind variables) 的錯誤，導(dǎo)致數(shù)據(jù)庫奔潰。一定要對參數(shù)數(shù)量進行限制。參數(shù)太多，也可能會拋出下面異常。
4.參數(shù)類型
上一節(jié)的代碼中，插入語句并沒有指定參數(shù)類型。這樣會有一個問題，雖然一個字段我們定義成可以為空，但是通過參數(shù)傳進來的這個字段值是空，就會拋出下面異常導(dǎo)致插入失敗。
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping:
ParameterMapping{property='_frch_item_50.name', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null',
expressinotallow='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #5 with JdbcType OTHER .
Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 無效的列類型: 1111要保證程序健壯性，就要給插入語句中參數(shù)指定類型，上面代碼優(yōu)化后如下：
<insert id="batchInsertStaff">
INSERT INTO staff (`staff_id`, `name`, `age`, `sex`, `address`, `email`) VALUES
<foreach collection="staffList" index="" item="item" separator=",">
(#{item.staffId,jdbcType=TINYINT}, #{item.name,jdbcType=VARCHAR},#{item.age,jdbcType=TINYINT},#{item.sex,jdbcType=TINYINT},#{item.address,jdbcType=VARCHAR},#{item.email,jdbcType=VARCHAR})
</foreach>
</insert>5.批量條數(shù)
批量操作是為了減少應(yīng)用和數(shù)據(jù)庫的交互，提高操作效率。但是如果對插入、更新這些批量操作不做條數(shù)限制，很可能會導(dǎo)致操作效率低下甚至數(shù)據(jù)庫 hang 住。我們可以通過分頁操作對批量條數(shù)做一些限制，看下面示例代碼：
public List<Staff> processStaffList(){
List<Staff> staffList = ...;
int pageSize = 500;
int pageCount = staffList / pageSize;
for(int i = 0; i < pageCount + 1; i++){
List<Staff> subList = (i == pageCount)? staffList.subList(i * pageSize, staffList.size()) :
staffList.subList(i * pageSize, (i + 1) * pageSize);
staffDao.batchInsertStaff(subList);
}
}總結(jié)
作為一個 orm 框架，無論我們選擇 JDBC、MyBatis 還是 MyBatis-Plus，批量操作最終都是要操作底層數(shù)據(jù)庫，批次性能怎么樣、會不會出問題，主要還得參考底層數(shù)據(jù)庫的能力。因此，想用好批量，首先要了解數(shù)據(jù)庫的特性。
