How to Efficiently Process Millions of Rows in MySQL with Streaming and Cursor Queries

When dealing with large‑scale data operations such as migration, export, or batch processing, loading millions of rows into JVM memory can cause OOM and slow queries.

Example scenario: reading 1,000,000 rows from a MySQL table for processing.

Regular Query

By default MySQL returns the full result set to memory. A common approach is pagination, but without deep‑page optimization it can be extremely slow.

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

This method is simple but may take minutes or hours for a million rows.

Streaming Query

Streaming returns an iterator instead of a full collection, reducing memory usage. MyBatis provides org.apache.ibatis.cursor.Cursor, which extends java.io.Closeable and java.lang.Iterable.

Cursor must be closed by the application after use.

All rows must be read (or the result set closed) before issuing another query.

Cursor Query

Cursor or fetchSize‑based queries fetch a configurable number of rows per round, allowing processing of millions of records without loading them all at once.

@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 options: ResultSet.FORWARD_ONLY: cursor moves only forward. ResultSet.SCROLL_INSENSITIVE: cursor can scroll but does not reflect DB changes. ResultSet.SCROLL_SENSITIVE: cursor reflects DB changes. fetchSize: number of rows fetched per round.

When using cursor queries, remember to clear temporary containers (e.g., gxids.clear()) after each batch.