项目场景:
往mysql表中批量插入数据,表存在唯一索引,当插入数据发生唯一索引冲突时,则更新对应数据记录的字段值。
问题描述
提示:这里描述项目中遇到的问题:
往指定的mysql表指定列插入一批数据时,如果采用逐条插入的方式,性能不佳。因此考虑使用批量插入的方式。
public void writeDataToCpUserTagTable(ManualTagFieldMapEntity entity, List<Map<String, Object>> batchData) {
//批量插入多条数据,唯一索引是uid
String insertSql = "INSERT INTO " + entity.getTableName() + " (uid, " + entity.getFieldName() + ") VALUES (:uid, :tagValue) "
+ "ON DUPLICATE KEY UPDATE " + entity.getFieldName() + " = :tagValue";
for (Map<String, Object> rowData : batchData) {
String uid = (String) rowData.get("uid");
String tagValue = (String) rowData.get("tag_value");
Map<String, Object> paramMap = new HashMap<>();
paramMap.put("uid", uid);
paramMap.put("tagValue", tagValue);
namedParameterJdbcTemplate.update(insertSql, paramMap);
}
}
原因分析:
在循环体中调用insert 语句,每次执行insert 语句, 都需要从数据库连接池获取一个链接
,然后执行完sql后再释放链接
,因此整个过程耗时较长。
解决方案:
将多条数据的insert sql 合并成一条。
例如:
insert into tableA (uk_column_a, column_b) values (1, 'a') ON DUPLICATE KEY UPDATE column_b = values(column_b);
insert into tableA (uk_column_a, column_b) values (2, 'b') ON DUPLICATE KEY UPDATE column_b = values(column_b);
insert into tableA (uk_column_a, column_b) values (3, 'c') ON DUPLICATE KEY UPDATE column_b = values(column_b);
可以替换成一条sql:
insert into tableA (column_a, column_b) values
(1, 'a'),
(2, 'b'),
(3, 'c')
ON DUPLICATE KEY UPDATE column_b = values(column_b);
;
这种方式只会获取一次数据库链接,然后执行一条sql
,因此避免了多次获取数据库链接、释放数据库链接的时间开销。
因此上述对应的代码可以调整为:
public void writeDataToCpUserTagTable(ManualTagFieldMapEntity entity, List<Map<String, Object>> batchData) {
//批量插入多条数据,唯一索引是uid
String insertSql = "INSERT INTO " + entity.getTableName() + " (uid, " + entity.getFieldName() + ") VALUES "
+ buildMergedSql(batchData.size())
+ " ON DUPLICATE KEY UPDATE " + entity.getFieldName() + " = values (" + entity.getFieldName() + ")";
Map<String, Object> paramMap = new HashMap<>();
for (int i = 0; i < batchData.size(); i++) {
Map<String, Object> rowData = batchData.get(i);
String uid = (String) rowData.get("uid");
String tagValue = (String) rowData.get("tag_value");
paramMap.put("uid" + i, uid);
paramMap.put("tagValue" + i, tagValue);
}
namedParameterJdbcTemplate.update(insertSql, paramMap);
}
/**
* 构建批量插入sql,并分配好参数变量名.
*
* @param size
* @return
*/
String buildMergedSql(int size) {
List<String> rowSqlList = new ArrayList<>();
String rowSqlFormat = "(:uid%d, :tagValue%d)";
for (int i = 0; i < size; i++) {
rowSqlList.add(String.format(rowSqlFormat, i, i));
}
return String.join(",", rowSqlList);
}