使用spring多线程往mysql数据库插入100万条数据效率对比,结果如下:
a) 20个线程*100000条/线程 = 200万条数据, 用时7分43秒(同样情况跑了2次,第一次是7分42秒,第二次是7分44秒)
b)1个线程*2000000条/线程 = 200万条数据,用时11分27秒。两者差别不大,重新调试参数,应该会有更快的结果
直接上代码:
配置文件:threadPoolConfig.xml (放在resource/META-INF目录下)
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:cache="http://www.springframework.org/schema/cache"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:c="http://www.springframework.org/schema/c"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/cache
http://www.springframework.org/schema/cache/spring-cache.xsd ">
1 OperationService.java
@Service
@ContextConfiguration(classes = Config.class)
public class OperationService {
@Autowired
private JdbcTemplate jdbcTemplate;
private Logger logger = LoggerFactory.getLogger(OperationService.class);
/**
* 每个线程根据自己的线程号插入相应的一段数据。例如:线程5插入id为501~600之间的数据
*/
public void dataInsert(int from, int to){
String insertSql = "insert into t_user_test values (?, 'john', '123', ?)";
LocalDateTime start = LocalDateTime.now();
for (int i = from; i <= to; i++) {
jdbcTemplate.update(insertSql, new Object[]{i, LocalDateTime.now()});
}
LocalDateTime end = LocalDateTime.now();
logger.info("开始时间:" + start + ", 结束时间:" + end);
}
}
2 MyThread.java
public class MyThread implements Runnable {
private OperationService operationService;
private int from;
private int to;
public MyThread(){
}
public MyThread(OperationService operationService, int from, int to){
this.operationService = operationService;
this.from = from;
this.to = to;
}
@Override
public void run() {
operationService.dataInsert(from, to);
}
}
3 Config.java
@Configuration
@ComponentScan(basePackages = { "common.use.multiThread" })
@ImportResource(value = {"classpath:META-INF/threadPoolConfig.xml" })
@EnableScheduling
public class Config {
}
4 测试类 MyTest.java
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = Config.class)
public class MyTest {
@Autowired
private ThreadPoolTaskExecutor taskExecutor;
@Autowired
private OperationService operationService;
@Test
public void test(){
for (int i = 1; i <= 20; i++) {
int unit = 100000;
int from = (i-1)*unit;
int to = i*unit - 1;
taskExecutor.execute(new MyThread(operationService, from, to));
System.out.println("int i is " + i + ", now threadpool active threads totalnum is " + taskExecutor.getActiveCount());
}
try {
System.in.read();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
//注意:System.in.read()要保留,如果不保留,测试类主线程执行完,直接关闭jvm,不等待子线程执行完,这是坑。放在main方法里则可以省略。
}