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 流式查询,需要以下满足三个条件:
resultSetType == ResultSet.TYPE_FORWARD_ONLY
fetchSize == Integer.MIN_VALUE
resultSetConcurrency == java.sql.ResultSet.CONCUR_READ_ONLY
除此之外,我们还应注意netTimeoutForStreamingResults
的值,避免操作数据时发生超时。