MySQL streaming query

Is it easy to query a large amount of data? Try MySQL streaming query

1, Foreword

When the program accesses the MySQL database, when the amount of data queried is particularly large, the database driver loads all the loaded data into the memory, It may lead to memory overflow (OOM). In fact, a streaming query is provided in MySQL database, which allows qualified data to be loaded into memory in batches and in part, which can effectively avoid oom. This paper mainly introduces how to use streaming query and compare it with ordinary query for performance test.

2, JDBC implements streaming query

Use JDBC to set the setFetchSize method of PreparedStatement/Statement to integer MIN_ Value or use the method statement Enablestreamingresults() can implement streaming queries when executing resultset When using the next () method, it will return one by one through the database connection, which will not occupy a lot of memory on the client.

public int execute(String sql, boolean isStreamQuery) throws SQLException {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    int count = 0;
    try {
        //Get database connection
        conn = getConnection();
        if (isStreamQuery) {
            //Set streaming query parameters
            stmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(Integer.MIN_VALUE);
        } else {
            //General query
            stmt = conn.prepareStatement(sql);
        }

        //Execute query to get results
        rs = stmt.executeQuery();
        //Traversal result
        while(rs.next()){
            System.out.println(rs.getString(1));
            count++;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        close(stmt, rs, conn);
    }
    return count;
}

PS: in the above example, the parameter istreamquery is used to switch between streaming query and ordinary query, which is used for the following test comparison.

3, Performance test

Create a test table my_test with a total data volume of 27w. The following four test cases are used for testing:

General query of large amount of data (27w pieces)
Large data streaming query (27w pieces)
Ordinary query of small amount of data (10 items)
Small data streaming query (10 pieces)

3.1. Test large data ordinary query

@Test
public void testCommonBigData() throws SQLException {
    String sql = "select * from my_test";
    testExecute(sql, false);
}

3.1. 1. Query time

27w data volume: 38 seconds

3.1. 2. Memory usage

Use nearly 1G of memory

3.2. Test large data streaming query

@Test
public void testStreamBigData() throws SQLException {
    String sql = "select * from my_test";
    testExecute(sql, true);
}

3.2. 1. Query time

27w data volume takes 37 seconds

3.2. 2. Memory usage

Since it is obtained in batches, the memory fluctuates between 30-270m

3.3. Test common query with small amount of data

@Test
public void testCommonSmallData() throws SQLException {
    String sql = "select * from my_test limit 100000, 10";
    testExecute(sql, false);
}

3.3. 1. Query time

10 pieces of data take 1 second

3.4. Test small data streaming query

@Test
public void testStreamSmallData() throws SQLException {
    String sql = "select * from my_test limit 100000, 10";
    testExecute(sql, true);
}

3.4. 1. Query time