Understanding and Optimizing MyBatis PageHelper Pagination Performance

The project originally used PageHelper for pagination, which worked fine with small data volumes but showed severe performance degradation as the dataset grew to hundreds of thousands or millions of rows. By tracing the PageHelper source code, the article reveals how pagination is implemented via LIMIT clauses appended to the original SQL.

Key code snippets illustrate the creation of a Page object, the interception process, and the generation of the final paginated SQL:

public ResultContent select(Integer id) {
    Page
blogPage = PageHelper.startPage(1,3).doSelectPage(() -> testDao.select(id));
    List
test = (List
)blogPage.getResult();
    return new ResultContent(0, "success", test);
}

public static
Page
startPage(int pageNum, int pageSize) {
    return startPage(pageNum, pageSize, DEFAULT_COUNT);
}

Further inspection shows how MyBatis' MapperProxy and PageInterceptor invoke the original SQL, construct a CacheKey , and eventually call ExecutorUtil.pageQuery to apply the pagination logic.

The article then demonstrates MySQL's LIMIT offset, count performance issue: small offsets ( LIMIT 1,10 ) execute quickly, while large offsets ( LIMIT 100000,10 ) cause seconds of delay. To mitigate this, a sub‑query that first selects primary keys with the desired offset and then joins back to the full table is proposed, reducing the execution time from several seconds to under one second.

SELECT a.* FROM USER a
INNER JOIN (
    SELECT id FROM USER WHERE age = 10 LIMIT 100000,10
) b ON a.id = b.id;

The solution relies on indexing the WHERE condition and leveraging the primary‑key index to avoid full table scans. Finally, the article suggests that optimizing PageHelper itself would require refactoring the SQL‑building part of its interceptor, and provides the GitHub repository link for reference.

GitHub: https://github.com/pagehelper/Mybatis-PageHelper/