Java thread pool multi thread query database to improve query efficiency

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

Tags: Front-end Android Back-end Interview

Posted by ryadex on Tue, 09 Aug 2022 22:06:04 +0300