Parallel Pagination Query Interceptor for MyBatis-Plus in Spring Boot

Applicable scenarios include pagination queries on large single tables and complex multi‑table joins with GROUP BY or DISTINCT that make the traditional COUNT + data query sequential execution a performance bottleneck.

Technology stack: Spring Boot + MyBatis‑Plus, Java 8+ CompletableFuture, MySQL 8.0, and custom ThreadPoolTaskExecutor for parallel tasks.

Implementation idea: The interceptor replaces the default MyBatis‑Plus pagination behavior by disabling the built‑in COUNT optimization, running the COUNT query asynchronously in a separate thread, and then executing the data query, finally merging the total count into the Page object.

Key steps:

Use MyBatis Interceptor to intercept Executor#query and extract the IPage parameter.

Submit a CompletableFuture that builds a COUNT‑SQL (removing ORDER BY, handling GROUP BY/DISTINCT) and executes it in a dedicated thread pool.

Execute the original data query synchronously, record its duration, and set the total count from the future result.

Thread‑pool configuration (example):

@Configuration
public class ThreadPoolTaskExecutorConfig {
    public static final Integer CORE_POOL_SIZE = 20;
    public static final Integer MAX_POOL_SIZE = 40;
    public static final Integer QUEUE_CAPACITY = 200;
    public static final Integer KEEP_ALIVE_SECONDS = 60;

    @Bean("threadPoolTaskExecutor")
    public ThreadPoolTaskExecutor getThreadPoolTaskExecutor() {
        ThreadPoolTaskExecutor threadPoolTaskExecutor = new ThreadPoolTaskExecutor();
        threadPoolTaskExecutor.setCorePoolSize(CORE_POOL_SIZE);
        threadPoolTaskExecutor.setMaxPoolSize(MAX_POOL_SIZE);
        threadPoolTaskExecutor.setQueueCapacity(QUEUE_CAPACITY);
        threadPoolTaskExecutor.setKeepAliveSeconds(KEEP_ALIVE_SECONDS);
        threadPoolTaskExecutor.setThreadNamePrefix("commonTask-");
        threadPoolTaskExecutor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        threadPoolTaskExecutor.initialize();
        return threadPoolTaskExecutor;
    }

    @Bean("countAsyncThreadPool")
    public ThreadPoolTaskExecutor getCountAsyncThreadPool() {
        ThreadPoolTaskExecutor threadPoolTaskExecutor = new ThreadPoolTaskExecutor();
        threadPoolTaskExecutor.setCorePoolSize(6);
        threadPoolTaskExecutor.setMaxPoolSize(12);
        threadPoolTaskExecutor.setQueueCapacity(2);
        threadPoolTaskExecutor.setKeepAliveSeconds(KEEP_ALIVE_SECONDS);
        threadPoolTaskExecutor.setThreadNamePrefix("countAsync-");
        threadPoolTaskExecutor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        threadPoolTaskExecutor.initialize();
        return threadPoolTaskExecutor;
    }
}

MyBatis‑Plus configuration to register the interceptor:

@Configuration
@MapperScan("com.xxx.mapper")
public class MybatisPlusConfig {
    @Resource
    ThreadPoolTaskExecutor countAsyncThreadPool;
    @Resource
    ApplicationContext applicationContext;

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

    @Bean
    public PageParallelQueryInterceptor pageParallelQueryInterceptor() {
        PageParallelQueryInterceptor pageParallelQueryInterceptor = new PageParallelQueryInterceptor();
        pageParallelQueryInterceptor.setCountAsyncThreadPool(countAsyncThreadPool);
        pageParallelQueryInterceptor.setApplicationContext(applicationContext);
        return pageParallelQueryInterceptor;
    }
}

Core interceptor implementation (simplified):

@Intercepts({
    @Signature(type = Executor.class, method = "query",
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
    @Signature(type = Executor.class, method = "query",
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})
})
@Slf4j
public class PageParallelQueryInterceptor implements Interceptor {
    private ThreadPoolTaskExecutor countAsyncThreadPool;
    private ApplicationContext applicationContext;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        Object parameter = args[1];
        Page
page = getPageParameter(parameter);
        if (page == null || page.getSize() <= 0 || !page.searchCount() || page.getTotal() == 0) {
            return invocation.proceed();
        }
        page.setSearchCount(false);
        page.setTotal(0);
        args[2] = RowBounds.DEFAULT;
        String originalSql = ms.getBoundSql(parameter).getSql();
        CompletableFuture
countFuture = resolveCountCompletableFuture(invocation, originalSql);
        long startTime = System.currentTimeMillis();
        Object proceed = invocation.proceed();
        log.info("Data query time={}", System.currentTimeMillis() - startTime);
        page.setTotal(countFuture.get());
        return proceed;
    }

    // Additional helper methods: resolveCountCompletableFuture, executeCountQuery, doCountQuery, getNewMappedStatement, getPageParameter, etc.
}

Notes:

The parallel approach does not guarantee strict consistency between COUNT and data queries under high concurrency, similar to the default MyBatis‑Plus behavior.

JOIN‑based COUNT optimizations are disabled, which may affect accuracy in one‑to‑many scenarios.

Thread‑pool sizes should be tuned according to CPU‑bound or I/O‑bound workloads, following common formulas such as threads = CPU * (1 + wait/compute) .