1. 什么是fetchsize？
1.1 Oracle中的fetchsize
先来简单解释一下，当我们执行一个SQL查询语句的时候，需要在客户端和服务器端都打开一个游标，并且分别申请一块内存空间，作为存放查询的数据的一个缓冲区。这块内存区，存放多少条数据就由fetchsize来决定，同时每次网络包会传送fetchsize条记录到客户端。应该很容易理解，如果fetchsize设置为20，当我们从服务器端查询数据往客户端传送时，每次可以传送20条数据，但是两端分别需要20条数据的内存空闲来保存这些数据。fetchsize决定了每批次可以传输的记录条数，但同时，也决定了内存的大小。这块内存，在oracle服务器端是动态分配的（大家可以想想为什么）。而在客户端（JBOSS），PS对象会存在一个缓冲中（LRU链表），也就是说，这块内存是事先配好的，应用端内存的分配在conn.prepareStatement(sql)或都conn.CreateStatement(sql)的时候完成。
在java程序中，我们会执行以下代码:
//打开游标，执行查询，但是并不获取任何的数据，网络上没有数据的传输。
rs = stmt.executeQuery();
//获取具体的数据，网络一般每次传输fetchsize条数据。
while (rs.next()){
}
1.1 MYSQL中的fetchsize
MYSQL的preparestament基本上不占用内存，为什么呢？因为MYSQL并不需要象oracle那样的一块内存来保存结果集缓冲区，为什么不需要缓冲区，其中根本的原因是由MYSQL的通讯方式决定的。
MYSQL 客户端/服务器协议是半双工的，即MYSQL只能在给定的时间，发送或接受数据，但不能同时发送和接收。所以，MYSQL在数据查询结果集传送的时候，需要一次性将数据全部传送到客户端，在客户数据接收完之后，释放相关的锁等资源。因为这种半双工的通讯方式，所以MYSQL不需要客户端的游标，但是客户端API通过把结果取到内存中，可以模拟游标的操作。所以，我们可以在JAVA程序中，可以象ORACLE那样来实现MYSQL的访问。
2. 如何设置fetchsize?
Fetchsize可以在任何一层进行设置 ，ORACLE JDBC驱动默认的FETCHSIZE为10。一般为了方便，我们会在数据源层面上来设置fetchsize。
2.1 语句级别的设置:
我们可以在jdbc中调用Preparedstatement .setFetchSize()的进行设置:
stmt = conn.prepareStatement(sql);
stmt.setFetchSize(50);
也可以在Ibatis, hibernate等框架上直接针对某个语句进行设置：
< select id="getAllProduct"> select * from employee < /select>
2.2 数据源中的全局设置
JBOSS连接中设置:
< connection-property name="defaultRowPrefetch">50</ connection-property>
2.3 Fetchsize的核心源码:
可以在JDBC驱动类Oracle.jdbc.driver.OracleStatment中找到这个方法， setPrefetchInternal方法中传入的默认值为0，伪代码如下:
void setPrefetchInternal(int paramInt){
if (paramInt < 0) {
DatabaseError.throwSqlException(68, "setFetchSize");
}
//获取连接池中的DefaultRowPrefetch属性
else if (paramInt == 0) {
paramInt = this.connection.getDefaultRowPrefetch();
}
if (paramInt == this.defaultRowPrefetch)
return;
this.defaultRowPrefetch = paramInt;
if ((this.currentResultSet == null) || (this.currentResultSet.closed)) {
this.rowPrefetchChanged = true;
}
}
3. Fetchsize对性能影响的测试:
3.1 空查询结果集的测试:
查询的表一共有300条记录，测试中查询的结果集为空，执行的是全表扫描。
SQL> select count(*) from test10000;
COUNT(*)
----------
300
SQL> select * from test10000 where col_a='test';
no rows selected
| 数据库 | 连接方式 | PSCACHE | fetchsize | 字段长度 | 网络距离 | 总记录数 | 返回记录 | 执行时间 (ms) |
| ORACLE | oci | 支持 | 1 | 10000 | 15KM | 300 | 0 | 1.5875 |
| ORACLE | oci | 支持 | 5 | 10000 | 15KM | 300 | 0 | 1.5828 |
| ORACLE | oci | 支持 | 10 | 10000 | 15KM | 300 | 0 | 1.7781 |
| ORACLE | oci | 支持 | 50 | 10000 | 15KM | 300 | 0 | 2.0468 |
| ORACLE | oci | 支持 | 100 | 10000 | 15KM | 300 | 0 | 2.6656 |
| ORACLE | oci | 支持 | 1 | 10000 | 本地 | 300 | 0 | 0.1646 |
| ORACLE | oci | 支持 | 5 | 10000 | 本地 | 300 | 0 | 0.1713 |
| ORACLE | oci | 支持 | 10 | 10000 | 本地 | 300 | 0 | 0.1898 |
| ORACLE | oci | 支持 | 50 | 10000 | 本地 | 300 | 0 | 0.3431 |
| ORACLE | oci | 支持 | 100 | 10000 | 本地 | 300 | 0 | 1.2609 |
| ORACLE | thin | 支持 | 1 | 10000 | 15KM | 300 | 0 | 1.6344 |
| ORACLE | thin | 支持 | 10 | 10000 | 15KM | 300 | 0 | 1.6687 |
| ORACLE | thin | 支持 | 100 | 10000 | 15KM | 300 | 0 | 1.6266 |
| MYSQL | jdbc | 支持 | 1 | 10000 | 15KM | 300 | 0 | 1.5187 |
| MYSQL | jdbc | 支持 | 10 | 10000 | 15KM | 300 | 0 | 1.6093 |
| MYSQL | jdbc | 支持 | 100 | 10000 | 15KM | 300 | 0 | 1.5906 |
从上面的测试中，可以得出如下结论：
- 在没有记录返回的情况下，OCI方式中fetchsize设置越大，对性能的影响越大。
- 在没有记录返回的情况下，THIN和mysql的方式中，fetchsize的大小，对于性能影响不大。
这是在空结果集情况下的影响，仅供参考，不应该作为我们考虑的情况。
3.2 非空查询结果集的测试:
| 数据库 | 连接方式 | PSCACHE | fetchsize | 字段长度 | 网络距离 | 总记录数 | 返回记录 | 执行时间 (ms) |
| ORACLE | oci | 支持 | 1 | 100 | 15KM | 300 | 300 | 226.9533 |
| ORACLE | oci | 支持 | 5 | 100 | 15KM | 300 | 300 | 86.44667 |
| ORACLE | oci | 支持 | 10 | 100 | 15KM | 300 | 300 | 43.74667 |
| ORACLE | oci | 支持 | 50 | 100 | 15KM | 300 | 300 | 10 |
| ORACLE | oci | 支持 | 100 | 100 | 15KM | 300 | 300 | 5.2 |
| ORACLE | oci | 支持 | 1 | 100 | 15KM | 300 | 10 | 8.44 |
| ORACLE | oci | 支持 | 5 | 100 | 15KM | 300 | 10 | 2.9 |
| ORACLE | oci | 支持 | 10 | 100 | 15KM | 300 | 10 | 1.56 |
| ORACLE | oci | 支持 | 50 | 100 | 15KM | 300 | 10 | 1.56 |
| ORACLE | oci | 支持 | 1 | 100 | 本地 | 300 | 300 | 12.773 |
| ORACLE | oci | 支持 | 5 | 100 | 本地 | 300 | 300 | 5.32 |
| ORACLE | oci | 支持 | 10 | 100 | 本地 | 300 | 300 | 2.9 |
从上面的测试中，可以得出如下结论：
- 当返回结果集较大时，设置较大的fetchsize，对性能会有很大的提升。
- Fetchsize设置大于返回的记录数时，对于性能的提升没有任何的意义，反而会增加内存的开销。
3.3 Fetchsize对性能和内存的影响，下面的图可以很好的说明他们的关系:
3.4 Fetchsize和网络的关系
当fetchsize设置到某一值时，便不会再有性能的提升，这不仅仅是因为结果集大小的原因，和操作系统或者ORACLE上的TCP读/写的缓冲区也有关系：
–操作系统上控制网络的读/写 buffer – net.core.rmem_default = 262144 – net.core.wmem_default = 262144 –数据库端控制，默认值为操作系统上的设置 : – RECV_BUF_SIZE=9375000 - SEND_BUF_SIZE=9375000
4. 总结：
fetchsize的设置，跟具体业务系统有关系，没有一个最好的值可以供各个应用都可以使用。一般OLTP的系统，fetchsize使用jdbc的默认值就可以了。我查看了下网络上的大部分文章，在某个特定的条件下测试的fetchsize，得出一个值，然后所有人都用这个值来设置自己的应用系统。一般情况下，这仅仅只是一些资源的浪费，但是，在某些情况下，如数据源拆分，读写分离架构中，当fetchsize设置的太大，有可能会导致性能的急剧下降，甚至会导致应用上可怕的JVM内存溢出，在不少公司发生过这种惨痛的教训。建议在设置这个值之前，先做一个JVM内存的DUMP，以便能够对内存的占用情况有一个清晰的了解。
发表评论