Java thread pool multi thread query database to improve query efficiency
demand
Company data statistics report query, several large table Association query, including cross database query, data associated query, data filtering, data statistics.
problem
Main table data 1
Main table data 2
...
Statistics 1
Statistics 2
...
data
data
data
data
The report structure is main table data + statistical data. It is very fast to only query the data in the main table. The statistical data needs cross database joint query, and the amount of table data is large, which slows down the speed.
At the beginning, each paging request first obtains the main table list, then foreach the main table list, query the statistical data according to the main table fields and calculate them, and then merge them into the main table to return. The query speed is not ideal.
Response time of paging query =1 main table query time + 10 statistical data query time
thinking
Complex report data statistics should not be solved entirely at the DB level, but SQL is only responsible for data filtering and returning the fields required for statistics. SQL should be as simple and efficient as possible. The service layer gets the result set returned by dB and carries out more complex data consolidation and statistics at the code level.
One page is always given to the front end, so the optimization is based on the page. There are 10 pages. The thread pool opens 10 threads to conduct parallel queries, and the total is returned to the front end.
Response time of paging query =1 main table query time + 1 statistical data query time (the slowest of 10)
code
Thread pool tool class
package com.youxue.weliao.utils;
import com.google.common.util.concurrent.ThreadFactoryBuilder;
import lombok.extern.slf4j.Slf4j;
import java.util.concurrent.Future;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
/**
* @Author lhy
* @Date 2022/5/13
*/
@Slf4j
public class ThreadUtil {
private volatile static ThreadUtil threadUtil;
private ThreadPoolExecutor executor;
/**
* Single case
*/
private ThreadUtil() {
}
public static ThreadUtil getThreadUtilInstance() {
if (null == threadUtil) {
synchronized (ThreadUtil.class) {
if (null == threadUtil) {
threadUtil = new ThreadUtil();
}
}
}
return threadUtil;
}
/**
* Submit task
*
* @param task
*/
public Future<?> submit(Runnable task) {
if (executor == null) {
// Initialize thread pool
executor = initialize();
}
// Execution thread
return executor.submit(task);
}
/**
* Initialize thread pool
*
* @return
*/
private synchronized ThreadPoolExecutor initialize() {
ThreadPoolExecutor executor = new ThreadPoolExecutor(10, 10, 0L,
TimeUnit.MILLISECONDS,
new LinkedBlockingQueue<>(1024),
new ThreadFactoryBuilder()
.setNameFormat("task-admin-getlist--%d")
.build(),
new ThreadPoolExecutor.CallerRunsPolicy());
log.info("===================>ThreadUtil Thread pool initialization");
return executor;
}
/**
* Close thread pool
*/
public void shutdown() {
if (executor != null) {
executor.shutdown();
}
}
}
The service layer is actually called. I won't release all the business codes here. It is simplified as follows
@Override
public IPage<ManageDto> getManageDto(IPage<ManageDto> page, String str, String end, List<Integer> taskAdminIds) {
// Main table query
IPage<ManageDto> manageDtos = baseMapper.getManageDto(page, str, end, taskAdminIds);
if (manageDtos.getTotal() > 0) {
// Main table query result set
List<ManageDto> manageDtoList = manageDtos.getRecords();
// Asynchronous thread Future collection
List<Future<?>> futures = new ArrayList<>();
for (ManageDto manageDto : manageDtoList) {
// If there are several pieces of data on this page, start several threads to query statistical data
futures.add(ThreadUtil.getThreadUtilInstance().submit(() -> {
// Statistical data query
List<ManageVo> manageVos = groupCyberArmyService.getManageVos(manageDto.getProcessAdminUserId(), manageDto.getTaskAdminId());
// After business logic operation, it is merged into the main table Dto
}))
}
this.waitFinish(futures);
manageDtos.setRecords(manageDtoList);
return manageDtos;
}
return null;
}
/**
* Whether all threads in the thread pool have finished execution
*
* @param futures Asynchronous thread Future collection
*/
@SneakyThrows
private void waitFinish(List<Future<?>> futures) {
for (Future<?> future : futures) {
future.get();
}
}
analysis
future.get method
Thread pool threads are submitted asynchronously, but the paging results returned need to be returned synchronously. Future get is a blocking method. Only after all the tasks are completed can we use get to return results in order of task submission and call future The get () method checks whether all methods in the thread pool have been executed, so as to achieve the effect of asynchronous submission of threads and synchronous return of result sets.
Thread pool tool class
There is no more explanation for the single example. Here we use the DCL single example. The thread pool configures parameters such as core threads according to its own needs.
First of all, I would like to introduce myself. I graduated from Jiaotong University in 13 years. I once worked in a small company, went to large factories such as Huawei OPPO, and joined Alibaba in 18 years, until now. I know that most junior and intermediate Java engineers who want to improve their skills often need to explore and grow by themselves or sign up for classes, but there is a lot of pressure on training institutions to pay nearly 10000 yuan in tuition fees. The self-study efficiency of their own fragmentation is very low and long, and it is easy to encounter the ceiling technology to stop. Therefore, I collected a "full set of learning materials for java development" and gave it to you. The original intention is also very simple. I hope to help friends who want to learn by themselves and don't know where to start, and reduce everyone's burden at the same time. Add the business card below to get a full set of learning materials