虚拟主机管理电脑清理优化大师

背景

六月某日上线了一个日报表任务，因是第一次上线，故需要为历史所有日期都初始化一次报表数据
在执行过程中发现新增特别的慢：插入十万条左右的数据，SQL执行耗费高达三分多钟

因很早就听闻过mybatis-plus的[伪]批量新增的问题，很快锁定问题并进行修复，下面细节描述多种批量新增方案的具体性能表现

# 测试表结构
DROP TABLE IF EXISTS `biz_batch_insert_test`;
CREATE TABLE `biz_batch_insert_test` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`name` varchar(600) DEFAULT NULL,`age` tinyint(4) unsigned DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;# name字段给的长度大了些，模拟生产实际表结构占用
# 测试库版本：5.7.5

方案一：传统for循环

@Test
public void testUserInsert() {long l = System.currentTimeMillis();for (int i = 0; i < 100000; i++) {TestUser testUser = new TestUser();testUser.setName("中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国");testUser.setAge(20);testUserService.save(testUser);}System.out.println("毫秒==>" + (System.currentTimeMillis() - l));
}# 插入10万条耗时：238040ms，大约4分钟

方案二：使用Mybatis-Plus的saveBatch

select * from oss_group where id_path like ‘0.1.%’;

@Test
public void testUserInsert() {long l = System.currentTimeMillis();List<TestUser> list = new ArrayList<>();for (int i = 0; i < 100000; i++) {TestUser testUser = new TestUser();testUser.setName("中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国");testUser.setAge(20);list.add(testUser);}testUserService.saveBatch(list);System.out.println("毫秒==>" + (System.currentTimeMillis() - l));
}# 插入耗时：62180ms，大约1分钟

这里先留下一张saveBatch的SQL日志截图，这里的日志是一个insert into语句，下面带了一千条数据(MP默认一千条一个批次)，使用Mybatis Log插件查看还是单条的SQL

方案三：在方案二的基础上修改MySQL连接参数：rewriteBatchedStatements=true

# 与方案二测试代码相同
# 插入耗时：35260ms，大约半分钟

其SQL日志也如上方案二所示，MySQL Jdbc驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，直接造成较低的性能
Mysql连接配置链接

方案四：使用Mybatis-Plus提供的扩展插件：InsertBatchSomeColumn

@Test
public void testUserInsert() {long l = System.currentTimeMillis();List<TestUser> list = new ArrayList<>();for (int i = 0; i < 10000; i++) {TestUser testUser = new TestUser();testUser.setName("中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国中国");testUser.setAge(20);list.add(testUser);// 因为mysql的参数max_allowed_packet限制，所以这里程序改成单批1000条if(list.size() == 1000){testUserMapper.insertBatchSomeColumn(list);list.clear();}}System.out.println("毫秒==>" + (System.currentTimeMillis() - l));
}# 插入耗时：24410ms，大约24秒

再来看看此时控制台的SQL，与方案二的SQL有着明显的区别，这里是将批次插入的数据拼接在同一条SQL中，对于MySQL处理来说，这是真的批量新增

配置方式

// 1. 自定义SQL注入器
public class BatchSaveSqlInjector extends DefaultSqlInjector {@Overridepublic List<AbstractMethod> getMethodList(Class<?> mapperClass) {// 注意：保留mybatis-plus的自带方法List<AbstractMethod> methodList = super.getMethodList(mapperClass);methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));return methodList;}
}// 2. 实现自定义baseMapper
public interface BatchSaveBaseMapper<T> extends BaseMapper<T> {/*** 批量插入 仅适用于mysql** @param entityList*            实体列表* @return 影响行数*/Integer insertBatchSomeColumn(Collection<T> entityList);
}// 3. 注入插件
// 方式一
@Configuration
public class MybatisPlusConfig {/*** 分页插件*/@Beanpublic PaginationInterceptor paginationInterceptor() {PaginationInterceptor paginationInterceptor = new PaginationInterceptor();paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));return paginationInterceptor;}/*** SQL注入器*/@Beanpublic BatchSaveSqlInjector easySqlInjector() {return new BatchSaveSqlInjector();}
}// 方式二
// 若项目有自定义SqlSessionFactory，也可在初始化时将自定义SQL注入器植入，参考下图MybatisPlusAutoConfiguration.sqlSessionFactory的做法

总结

	插入10万数据耗时（秒）
mybatis-plus：save	238
mybatis-plus的saveBatch：rewriteBatchedStatements=false	62
mybatis-plus的saveBatch：rewriteBatchedStatements=true	35
mybatis-plus扩展插件：InsertBatchSomeColumn	24