MySQL 普通查询、流式查询、游标查询以及使用 mybatis 如何实现

MySQL 普通查询、流式查询、游标查询以及使用 mybatis 如何实现

JDBC 查询的大体流程是：JDBC 向 Mysql 数据库请求数据，数据库查询后通过网络将数据发送给我们本机，数据先存储在 socket buffer 中，然后才会将数据读入到 JVM 中（存在堆中）。也就是说数据存储在 socket buffer 中的时候是不消耗堆的空间的。

假设我们要查询 10 条数据：select id, name from user limit 10，各种查询的处理过程如下：

普通查询：就是我们常用的查询，Mysql 会将 10 条数据全部发送到本机，将 10 条数据先存储在 socket buffer 中，然后将 10 条数据全部读到堆中。

流式查询：Mysql 会将数据源源不断的发送到本机，存储在 socket buffer 中，每次调用ResultSet.next()方法时只会读取 1 条数据到堆中（不需要数据全部发送完毕就能读取）。

游标查询：Mysql 每次返回的数据条数取决于fetchSize 参数，假设 fetchSize = 3，那么 Mysql 每次只会返回 3 条数据，存储在 socket buffer 中，然后将 3 条数据都读入到堆中，调用ResultSet.next() 时堆有数据就直接返回，没数据会再向 Mysql 请求 3 条数据，这样循环往复（3、3、3、1），直到 10 条数据都读完。

普通查询

public static void main(String[] args) throws SQLException {
    String sql = "select id, name from user limit 10";
    Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
    Statement st = conn.createStatement();
    ResultSet rs = st.executeQuery(sql);

    while (rs.next()) {
        System.out.println(rs.getInt("id") + " " + rs.getString("name"));
    }
}

在 rs.next() 打断点可以进入 com.mysql.cj.jdbc.result.ResultSetImpl.next()，可以看到 rowData 的实现类是 ResultsetRowsStatic，且 szie 是 10，这说明将 10 条记录到加载进了堆中。

ResultsetRowsStatic.next() 方法很简单，先 index++，再 List.get(index)。

流式查询

上图是流式查询的官方文档，大意是要使用流式查询，在创建 Statement 时必须传参 java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY，且必须把 fetchSize 设置为 Integer.MIN_VALUE。

有个注意事项：必须读完或关闭 ResultSet，才能在同一个 Connection 上再执行其他操作，否则会抛异常。换句话说就是一个流式查询会独占一个数据库连接，所以使用流式查询时应尽快读取结果集，避免将连接耗尽的情况出现。

public static void main(String[] args) throws SQLException {
    String sql = "select id, name from user limit 10";
    Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
    // 重点 TYPE_FORWARD_ONLY、CONCUR_READ_ONLY、fetchSize = Integer.MIN_VALUE
    Statement st = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
    st.setFetchSize(Integer.MIN_VALUE);

    ResultSet rs = st.executeQuery(sql);
    while (rs.next()) {
        System.out.println(rs.getInt("id") + " " + rs.getString("name"));
    }
}

rowData 的实现类为 ResultsetRowsStreaming，头部的注释与官方文档相同的意思。

read() 最终会走到 ResultsetRowReader.read()

游标查询

要使用游标查询，必须在设置连接属性 useCursorFetch=true 和 fetchSize。

public static void main(String[] args) throws SQLException {
    String sql = "select id, name from user limit 10";
    // 连接属性 useCursorFetch=true
    Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?useCursorFetch=true", "root", "root");
    Statement st = conn.createStatement();
    // fetchSize
    st.setFetchSize(3);

    ResultSet rs = st.executeQuery(sql);
    while (rs.next()) {
        System.out.println(rs.getInt("id") + " " + rs.getString("name"));
    }
}

rowData 的实现类是 ResultsetRowsCursor。

mybatis 如何实现

com.mysql.cj.jdbc.ConnectionImpl 的 createStatement()、prepareStatement(String sql) 方法的默认值就是使用流式查询所需要的值。

private static final int DEFAULT_RESULT_SET_TYPE = ResultSet.TYPE_FORWARD_ONLY;

private static final int DEFAULT_RESULT_SET_CONCURRENCY = ResultSet.CONCUR_READ_ONLY;

@Override
public java.sql.Statement createStatement() throws SQLException {
    return createStatement(DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY);
}

@Override
public java.sql.PreparedStatement prepareStatement(String sql) throws SQLException {
    return prepareStatement(sql, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY);
}

mybatis 创建 Statement 是 BaseStatementHandler 的 prepare 方法

@Override
public Statement prepare(Connection connection, Integer transactionTimeout) throws SQLException {
  ErrorContext.instance().sql(boundSql.getSql());
  Statement statement = null;
  try {
    // 创建 statement
    statement = instantiateStatement(connection);
    setStatementTimeout(statement, transactionTimeout);
    // 设置 fetchSize
    setFetchSize(statement);
    return statement;
  } catch (SQLException e) {
    closeStatement(statement);
    throw e;
  } catch (Exception e) {
    closeStatement(statement);
    throw new ExecutorException("Error preparing statement.  Cause: " + e, e);
  }
}

instantiateStatement 方法有三个实现：

• CallableStatementHandler：存储过程相关的

• SimpleStatementHandler：返回 Statement

  @Override
  protected Statement instantiateStatement(Connection connection) throws SQLException {
  if (mappedStatement.getResultSetType() == ResultSetType.DEFAULT) {
    return connection.createStatement();
  } else {
    return connection.createStatement(mappedStatement.getResultSetType().getValue(), ResultSet.CONCUR_READ_ONLY);
  }
  

• PreparedStatementHandler：返回 PreparedStatement

  @Override
  protected Statement instantiateStatement(Connection connection) throws SQLException {
  String sql = boundSql.getSql();
  if (mappedStatement.getKeyGenerator() instanceof Jdbc3KeyGenerator) {
    String[] keyColumnNames = mappedStatement.getKeyColumns();
    if (keyColumnNames == null) {
      return connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
    } else {
      return connection.prepareStatement(sql, keyColumnNames);
    }
  } else if (mappedStatement.getResultSetType() == ResultSetType.DEFAULT) {
    return connection.prepareStatement(sql);
  } else {
    return connection.prepareStatement(sql, mappedStatement.getResultSetType().getValue(), ResultSet.CONCUR_READ_ONLY);
  }
  

分析之后发现 mybatis 实现流式查询、游标查询很简单，第一需要设置连接属性 useCursorFetch=true

jdbc:mysql://127.0.0.1:3306/test?useCursorFetch=true

第二设置 fetchSize，流式查询设置成 Integer.MIN_VALUE 即 -2147483648；游标查询设置成我们想要的值：

<!-- 流式查询 -->
<select id="streamingQuery" fetchSize="-2147483648" resultMap="BaseResultMap">
  select id, name from user limit 10
</select>

<!-- 游标查询 -->
<select id="cursorQuery" fetchSize="3" resultMap="BaseResultMap">
  select id, name from user limit 10
</select>

第三 Mapper 需要返回 Cursor<User>：

Cursor<User> streamingQuery();

Cursor<User> cursorQuery();

第四 for 循环获取数据：

@RestController
public class TestController {

    private final UserMapper userMapper;

    public TestController(UserMapper userMapper) {
        this.userMapper = userMapper;
    }

    // 注意点：获取数据时要保证数据库连接没有被回收，这里使用了 @Transactional 保证
    @Transactional
    @GetMapping("streaming")
    public List<User> streamingQuery() {
        Cursor<User> cursor = userMapper.streamingQuery();
        return getData(cursor);
    }

    @Transactional
    @GetMapping("cursor")
    public List<User> cursorQuery() {
        Cursor<User> cursor = userMapper.cursorQuery();
        return getData(cursor);
    }

    public List<User> getData(Cursor<User> cursor) {
        List<User> list = new LinkedList<>();
        cursor.forEach(list::add);
        return list;
    }
}

可以在 com.mysql.cj.jdbc.result.ResultSetImpl.next() 打断点验证一下 rowData 的类型。

参考

• MySQL :: MySQL Connector/J 8.0 Developer Guide :: 6.4 JDBC API Implementation Notes