【Mybatis】打开 MySQL 流式查询的大门之原理篇

K	V
mysql-connector-java	5.1.46

随着大数据的火爆全网，流式查询的地位变得越来越重要。
而应用开发的同学，需要对千万级别甚至上亿数据量的数据库进行批处理时，也需要用到流式查询的特性，避免操作大规模数据时引发OOM。
今天，我们一起来探索 MySQL 流式查询的原理。

com.mysql.jdbc.Statement

public interface Statement extends java.sql.Statement, Wrapper {

    /**
     * Workaround for containers that 'check' for sane values of
     * Statement.setFetchSize() so that applications can use
     * the Java variant of libmysql's mysql_use_result() behavior.
     * 
     * @throws SQLException
     */
    public abstract void enableStreamingResults() throws SQLException;

    /**
     * Resets this statements fetch size and result set type to the values
     * they had before enableStreamingResults() was called.
     * 
     * @throws SQLException
     */
    public abstract void disableStreamingResults() throws SQLException;
}

com.mysql.jdbc.StatementImpl

public class StatementImpl implements Statement {

    /*
     * (non-Javadoc)
     * 
     * @see com.mysql.jdbc.IStatement#enableStreamingResults()
     */
    public void enableStreamingResults() throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            this.originalResultSetType = this.resultSetType;
            this.originalFetchSize = this.fetchSize;

            setFetchSize(Integer.MIN_VALUE);
            setResultSetType(ResultSet.TYPE_FORWARD_ONLY);
        }
    }

    public void disableStreamingResults() throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            if (this.fetchSize == Integer.MIN_VALUE && this.resultSetType == ResultSet.TYPE_FORWARD_ONLY) {
                setFetchSize(this.originalFetchSize);
                setResultSetType(this.originalResultSetType);
            }
        }
    }

    /**
     * We only stream result sets when they are forward-only, read-only, and the
     * fetch size has been set to Integer.MIN_VALUE
     * 
     * @return true if this result set should be streamed row at-a-time, rather
     *         than read all at once.
     */
    protected boolean createStreamingResultSet() {
        return ((this.resultSetType == java.sql.ResultSet.TYPE_FORWARD_ONLY) && (this.resultSetConcurrency == java.sql.ResultSet.CONCUR_READ_ONLY)
                && (this.fetchSize == Integer.MIN_VALUE));
    }

    /**
     * Adjust net_write_timeout to a higher value if we're streaming result sets. More often than not, someone runs into
     * an issue where they blow net_write_timeout when using this feature, and if they're willing to hold a result set open
     * for 30 seconds or more, one more round-trip isn't going to hurt.
     *
     * This is reset by RowDataDynamic.close().
     */
    protected void setupStreamingTimeout(MySQLConnection con) throws SQLException {
        if (createStreamingResultSet() && con.getNetTimeoutForStreamingResults() > 0) {
            executeSimpleNonQuery(con, "SET net_write_timeout=" + con.getNetTimeoutForStreamingResults());
        }
    }
}

com.mysql.jdbc.ConnectionProperties

public interface ConnectionProperties {

    public int getNetTimeoutForStreamingResults();

    public void setNetTimeoutForStreamingResults(int value) throws SQLException;

}

com.mysql.jdbc.ConnectionPropertiesImpl

Represents configurable properties for Connections and DataSources. Can also expose properties as JDBC DriverPropertyInfo if required as well.

public class ConnectionPropertiesImpl implements Serializable, ConnectionProperties {

    private IntegerConnectionProperty netTimeoutForStreamingResults = new IntegerConnectionProperty("netTimeoutForStreamingResults", 600, 0, Integer.MAX_VALUE,
            Messages.getString("ConnectionProperties.netTimeoutForStreamingResults"), "5.1.0", MISC_CATEGORY, Integer.MIN_VALUE);


    public int getNetTimeoutForStreamingResults() {
        return this.netTimeoutForStreamingResults.getValueAsInt();
    }

    public void setNetTimeoutForStreamingResults(int value) throws SQLException {
        this.netTimeoutForStreamingResults.setValue(value, getExceptionInterceptor());
    }
}

结论

开启 MySQL 流式查询，需要以下满足三个条件：

1. resultSetType == ResultSet.TYPE_FORWARD_ONLY
2. fetchSize == Integer.MIN_VALUE
3. resultSetConcurrency == java.sql.ResultSet.CONCUR_READ_ONLY

除此之外，我们还应注意netTimeoutForStreamingResults的值，避免操作数据时发生超时。