How MyBatis‑Plus Handles Slow Queries on Massive Datasets

Regular Query

By default MyBatisPlus stores the entire result set in memory. For a table with 1,000,000 rows a typical pagination query looks like this:

@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 result set is loaded all at once the JVM may run out of memory (OOM) and the query becomes very slow because the framework spends a lot of time mapping each row to an entity.

Stream Query

Stream query returns an Iterator instead of a collection, allowing the application to fetch one row at a time. This reduces memory usage dramatically. MyBatis keeps a long‑lived connection and a server‑side cursor; the developer must close the cursor after processing.

Example:

@Select("SELECT bds.* FROM big_data_search bds ${ew.customSqlSegment} ")
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000000)
void listData(@Param(Constants.WRAPPER) QueryWrapper<BigDataSearchEntity> queryWrapper,
              ResultHandler<BigDataSearchEntity> handler);

Key points:

The cursor stays open until all rows are consumed; the application must close it explicitly.

MySQL fetches rows lazily via ResultSet.next(), while Oracle can pull a batch defined by fetchSize.

If the network buffer fills, MySQL’s processing thread blocks, preventing the client memory from exploding.

Cursor Query

MyBatis provides org.apache.ibatis.cursor.Cursor, which extends java.io.Closeable and java.lang.Iterable. It 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.

Two usage patterns are shown:

Pattern 1 : fetch multiple rows per round (large fetchSize).

Pattern 2 : fetch a single row per round (small fetchSize).

@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);

@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);

Differences between non‑stream and stream queries:

Non‑stream query: memory grows almost linearly with the number of rows.

Stream query: memory stays stable; its size depends on the batch size ( BATCH_SIZE).

Practical advice: after processing each batch, clear temporary containers (e.g., gxids.clear()) and close the cursor to avoid leaks.

Overall, for massive data migrations, exports, or batch processing, using MyBatisPlus stream or cursor queries can prevent OOM and dramatically improve throughput compared with ordinary pagination.