JBOSS连接池调优—合理的设置fetchsize

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

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