springboot 线程池和数据库链接池配置以及多线程效率实测
使用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目录下）
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
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 ">
<!-- spring thread pool executor -->
<bean id="taskExecutor" class="org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor">
<!-- 线程池维护线程的最少数量 -->
<property name="corePoolSize" value="20" />
<!-- 允许的空闲时间 -->
<property name="keepAliveSeconds" value="200" />
<!-- 线程池维护线程的最大数量 -->
<property name="maxPoolSize" value="50" />
<!-- 缓存队列 -->
<property name="queueCapacity" value="100" />
<!-- 对拒绝task的处理策略 -->
<property name="rejectedExecutionHandler">
<bean class="java.util.concurrent.ThreadPoolExecutor$CallerRunsPolicy" />
</property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!-- 数据库连接驱动 -->
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<!-- 数据库连接url -->
<property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/sampledb?useSSL=false&characterEncoding=utf8"/>
<!-- 数据库连接用户名 -->
<property name="user" value="root"/>
<!-- 数据库连接密码 -->
<property name="password" value="root"/>
<property name="automaticTestTable" value="test_timeout"/>
<!-- 隔多少秒检查所有连接池中的空闲时间 -->
<property name="idleConnectionTestPeriod" value="60"/>
<!-- 最大空闲时间，超过空闲时间的连接将被丢弃 -->
<property name="maxIdleTime" value="900"/>
<!-- 初始化连接池数量 -->
<property name="initialPoolSize" value="10"/>
<!-- 最小连接池数量 -->
<property name="minPoolSize" value="10"/>
<!-- 最大连接池数量 -->
<property name="maxPoolSize" value="50"/>
<!-- 当连接池连接用完时，C3PO一次性创建新连接的数据 -->
<property name="acquireIncrement" value="10"/>
<!-- 数据源内加载的PreparedStatement数量 -->
<property name="maxStatements" value="200" />
<!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的
时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable
等方法来提升连接测试的性能。Default: false -->
<property name="testConnectionOnCheckout" value="false"/>
<!--如果设为true那么在取得连接的同时将校验连接的有效性。Default: false -->
<property name="testConnectionOnCheckin" value="true"/>
</bean>
</beans>
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方法里则可以省略。
}
