One, foreword

When the program accesses the MySQL database, if the amount of data queried is very large, the database driver loads all the loaded data into the memory, which may cause memory overflow (OOM).

In fact, MySQL database provides streaming query, allows to meet the conditions of the data in batches part of the load into memory, can effectively avoid OOM; This article focuses on how to use streaming queries and compare them to normal queries for performance testing.

 

Second, JDBC to achieve streaming query

Using JDBC PreparedStatement/Statement setFetchSize method is set to an Integer. The MIN_VALUE or use method Statement. EnableStreamingResults () You can implement streaming queries that are returned one by one through the database connection while executing the resultSet.next () method without consuming a lot of client memory.

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

        // Execute the query to obtain the result
        rs = stmt.executeQuery();
        // Iterate over the result
        while(rs.next()){
            System.out.println(rs.getString(1)); count++; }}catch (SQLException e) {
        e.printStackTrace();
    } finally {
        close(stmt, rs, conn);
    }
    return count;
}
Copy the code

PS: In the above example, the isStreamQuery parameter is used to switch the streaming query from the normal query, which is used for the following test comparison.

 

Three, performance test

A test table my_test was created for testing, with a total of 27W data. The following four test cases were used for testing:

  1. Large amount of data common query (27W items)
  2. Large data volume streaming query (27W items)
  3. Small amount of data common query (10 items)
  4. Small data volume streaming query (10 items)

 

3.1. Test common queries with large amounts of data

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

3.1.1. Query Time

The 27W data took 38 seconds

3.1.2. Memory usage

It uses nearly 1 gigabyte of memory

 

3.2. Test large data volume streaming queries

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

3.2.1. Query Time

The 27W data took 37 seconds

3.2.2. Memory usage

Due to batch acquisition, there is 30-270m fluctuation

 

3.3. Test common queries with small data volumes

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

3.3.1. Query Time

The 10 data volumes took 1 second

 

3.4. Test small data volume streaming queries

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

3.4.1. Query Time

The 10 data volumes took 1 second

 

Four,

The optimization of MySQL streaming query for memory usage is relatively obvious, but it has little impact on the query speed. It is mainly used to solve the scenario of large data query with large memory usage.

DEMO address: github.com/zlt2000/mys…