原创作品,允许转载,转载时请务必以超链接形式标明文章
原始出处 、作者信息和本声明。否则将追究法律责任。
http://boylook.blog.51cto.com/7934327/1308511
There is experimental support for fetching rows in batches
When using Connector/J 5.0.1 along with more recent builds of the MySQL server, you can add "useCursorFetch=true" to your JDBC url parameters, and the driver will fetch rows in batches of size setFetchSize() as defined in the JDBC API.
One could also argue that the behavior _does_ follow the JDBC API, quoting from the APIDOCS for Statement.setFetchSize():
"Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed. The number of rows specified affects only result sets created using this statement. If the value specified is zero, then the hint is ignored. The default value is zero."
It's only a _hint_. Driver vendors are allowed to ignore hints. The very reason that the wording is there is because there are quite a few vendors who can not adhere to this "contract" in all situations.
if (this.connection.versionMeetsMinimum(5, 0, 2)
&& this.connection.getUseCursorFetch()
&& isBinaryEncoded
&& callingStatement != null
&& callingStatement.getFetchSize() != 0
&& callingStatement.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY)
若OK,则RowData rows = new RowDataCursor
&& (this.resultSetConcurrency == java.sql.ResultSet.CONCUR_READ_ONLY) && (this.fetchSize == Integer.MIN_VALUE)
如果不满足则rowData = readSingleRowSet
3.否则rowData = new RowDataDynamic
这里主要关注RowDataCursor的next实现方式:next会去调用fetchMoreRows,
3.1 如果fetchsize==Integer.MIN_VALUE,则另fetchsize=1,类似stream 模式
3.2 否则回调fetchRowsViaCursor:
this.sharedSendPacket.writeByte((byte) MysqlDefs.COM_FETCH);
this.sharedSendPacket.writeLong(statementId);
this.sharedSendPacket.writeLong(fetchSize);
sendCommand(MysqlDefs.COM_FETCH, null, this.sharedSendPacket, true,
null);
while ((row = nextRow(columnTypes, columnTypes.length, true,
ResultSet.CONCUR_READ_ONLY, false, useBufferRowExplicit, false, null)) != null) {
fetchedRows.add(row);
}
当执行sendCommand后,看MySQL源码是如何处理的:
317 void Materialized_cursor::fetch(ulong num_rows)
318 {
319 THD *thd= table->in_use;
320
321 int res=0;
322 result->begin_dataset();
323 for(fetch_limit+= num_rows; fetch_count < fetch_limit; fetch_count++)
324 {
325 if((res= table->file->rnd_next(table->record[0])))
326 break;
327 /* Send data only if the read was successful. */
328 /*
329 If network write failed (i.e. due to a closed socked),
330 the error has already been set. Just return.
331 */
332 if(result->send_data(item_list))
333 return;
334 }
335
336 switch(res) {
337 case0:
338 thd->server_status|= SERVER_STATUS_CURSOR_EXISTS;
339 result->send_eof();
340 break;
341 case HA_ERR_END_OF_FILE:
342 thd->server_status|= SERVER_STATUS_LAST_ROW_SENT;
343 result->send_eof();
344 close();
345 break;
346 default:
347 table->file->print_error(res,MYF(0));
348 close();
349 break;
350 }
351 }
分析到这里基本上可以确定MySQL也是支持batch fetch的.