How to Speed Up Large-Scale Queries with MyBatisPlus

Regular Query

By default MyBatisPlus retrieves the entire result set into memory. For a table with 1 million rows this usually means paging the data, but if pagination is not optimized the database server may become overloaded and the query can take minutes or even hours.

Example mapper :

@Mapper
public interface BigDataSearchMapper extends BaseMapper<BigDataSearchEntity> {
    @Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
    Page<BigDataSearchEntity> pageList(@Param("page") Page<BigDataSearchEntity> page,
                                         @Param(Constants.WRAPPER) QueryWrapper<BigDataSearchEntity> queryWrapper);
}

When the LIMIT deep‑pagination is not considered, the database may be stressed for a very long time.

Stream Query

A stream query returns an Iterator instead of a full collection, allowing the application to fetch one row at a time and keep memory usage low.

The database connection stays open for the whole stream, so the application must close the connection after processing.

All rows must be read (or the result set closed) before issuing another query, otherwise an exception is thrown.

MyBatis provides org.apache.ibatis.cursor.Cursor, which extends java.io.Closeable and java.lang.Iterable. The cursor offers three useful methods: isOpen() – checks whether the cursor is still open. isConsumed() – indicates whether all rows have been read. getCurrentIndex() – returns the number of rows already fetched.

When processing large result sets, the batch size ( BATCH_SIZE) determines how much memory the stream consumes; a larger batch size uses more memory.

Cursor Query

Cursor queries also avoid OOM by fetching rows in batches controlled by the fetchSize parameter. Two annotation styles are shown:

@Mapper
public interface BigDataSearchMapper extends BaseMapper<BigDataSearchEntity> {
    // Method 1 – multiple rows per fetch
    @Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000000)
    Page<BigDataSearchEntity> pageList(@Param("page") Page<BigDataSearchEntity> page,
                                         @Param(Constants.WRAPPER) QueryWrapper<BigDataSearchEntity> queryWrapper);

    // Method 2 – one row per fetch
    @Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 100000)
    @ResultType(BigDataSearchEntity.class)
    void listData(@Param(Constants.WRAPPER) QueryWrapper<BigDataSearchEntity> queryWrapper,
                  ResultHandler<BigDataSearchEntity> handler);
}

Key points:

Method 1 returns multiple rows per fetch; Method 2 returns a single row per fetch.

Oracle pulls the configured fetchSize rows to the client before processing; MySQL streams rows via ResultSet.next(), flushing buffers as it reads.

Non‑streaming queries cause memory to grow linearly with the number of rows, while stream queries keep memory stable, limited mainly by the batch size.

After processing each batch, temporary containers (e.g., gxids.clear()) should be cleared to release memory.

By choosing the appropriate query mode and tuning fetchSize, developers can handle million‑row datasets efficiently without exhausting JVM memory.