时间：   |   分类：     |   阅读： 3349 字 ~7分钟   |   访问： 0

上篇文章介绍了一个方法，解决了导出Excel过程中内存溢出的问题。但是有些问题还没有搞明白，那就是为什么要这样打开数据库游标？为什么fetchSize必须为：-2147483648？这里面涉及到数据库游标的更多信息，这一篇文章将深入介绍这方面内容。

在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。

MySQL数据库游标通常有两种形式：Client Side Cursor（客户端游标）和Server Side Cursor（服务器端游标）。默认情况下，客户端游标会把整个结果集获取到客户端内存中，如果结果集太大，就会引发Out Of Memory错误；而服务器端游标会将结果集缓存在服务器端，客户端从服务器端分批获得结果集。

MySQL默认是使用客户端游标的，因为通常情况下，程序处理的结果集不会特别大，对小结果集使用客户端游标效率更高：结果集一次性传输到客户端，客户端可以自行处理，服务器端也可以为其他客户端提供服务。但是针对大结果集，默认的客户端游标处理方式满足不了要求，针对这种情况，MySQL的Java客户端做了一个特殊的处理。下面参照官方帮助看一下。

1. 官方帮助说明

1.1 客户端游标

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate and, due to the design of the MySQL network protocol, is easier to implement. If you are working with ResultSets that have a large number of rows or large values and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time. To enable this functionality, create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE);

 1>The combination of a forward-only, read-only result set, with a fetch size of Integer.`MIN_VALUE` serves as a signal to the driver to stream result sets row-by-row. After this, any result sets created with the statement will be retrieved row-by-row.
 2
 3简单翻译一下：
 4> 缺省情况下，结果集会被整个的拉取并保存到客户端内存中。通常情况下，这是这是最有效的方式，可以与MySQL的网络协议完美配合。如果你需要处理一个大数量级的结果集或者你无法在你的JVM中申请足够的Heap内存，你可以告诉驱动使用流方式返回结果集，一次返回一条记录。
 5
 6> 要开启这个功能，可以按照下面的代码方式创建结果Statement：
 7
 8> ```java
 9stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
10       java.sql.ResultSet.CONCUR_READ_ONLY);
11stmt.setFetchSize(Integer.MIN_VALUE);

使用TYPE_FORWARD_ONLY，CONCUR_READER_ONLY的组合创建一个Statement，并且指定fetchSize为Integer.MIN_VALUE，这会告诉MySQL的驱动使用流方式处理结果集，一次返回一条记录。

1.2 -2147483648的来历

那么Integer.MIN_VALUE是多少呢？看一下定义：

1@Native public static final int   MIN_VALUE = 0x80000000;

计算一下，正好是：-2147483648。这就是之前提到的这个数字的来历，MySQL驱动需要这个数字来明确开启流处理方式。回忆一下之前博文说道的修改方式，其中的fetchSize, resultType都是按照上面帮助中提到的要求进行设置的：

 1<select id="exportByExample" fetchSize="-2147483648" parameterType="com.eveus.admin.po.logs.BankcardAuthNoOTPLogPOExample" resultSetType="FORWARD_ONLY" resultMap="BaseResultMap">
 2  select
 3  <include refid="Export_Column_List" />
 4  from UID_BANKCARD_AUTH_NO_OTP_LOG 
 5  <if test="_parameter != null">
 6    <include refid="Example_Where_Clause" />
 7  </if>
 8  <if test="orderByClause != null">
 9    order by ${orderByClause}
10  </if>
11</select>

1.3 客户端游标的限制

客户端游标在使用中还是有一些限制或者不足的地方的。我们继续看官方帮助文档：

There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.

The earliest the locks these statements hold can be released (whether they be MyISAM table-level locks or row-level locks in some other storage engine such as InnoDB) is when the statement completes.

If the statement is within scope of a transaction, then locks are released when the transaction completes (which implies that the statement needs to complete first). As with most other databases, statements are not complete until all the results pending on the statement are read or the active result set for the statement is closed.

Therefore, if using streaming results, process them as quickly as possible if you want to maintain concurrent access to the tables referenced by the statement producing the result set.

翻译一下：

使用这种机制有一些事情需要注意：使用同一个数据库链接，你必须读取结果集中的全部记录（或者关掉结果集）才能够执行新的查询语句，否则会有异常抛出。 只有当Statement执行完毕的时候，其持有的锁才能够被释放（无论是MyISAM的表级锁还是其他存储引擎，例如InnoDB，的行级锁）。 如果该Statement位于一个事务当中，只有当事务完成的时候，Statement持有的锁才会被释放。这同时意味着Statement必须先于事务完成。与大多数数据库一样，只有读取了Statement上的所有结果或关闭了语句的活动结果集，Statement才会关闭。 因此，如果要使用流方式处理结果集，一定要记住尽可能快的处理结果集，以保证对产生结果集的表的并发访问能力。

总结一下就是使用流方式的时候，结果集没有处理完或者Statement没有关闭的时候，是有锁存在的，因此处理速度一定要快，否则会影响并发性能。

1.3 服务器端游标

Another alternative is to use cursor-based streaming to retrieve a set number of rows each time. This can be done by setting the connection property useCursorFetch to true, and then calling setFetchSize(int) with int being the desired number of rows to be fetched each time: 另一个可行的方法是使用基于游标的流处理方式来每次获取指定数量的记录。可以通过在MySQL链接字符串中设置useCursorFetch=true这种参数，然后调用setFetchSize(int)函数来设置期望的每次返回的记录条数。

1conn = DriverManager.getConnection("jdbc:mysql://localhost/?useCursorFetch=true", "user", "s3cr3t");
2stmt = conn.createStatement();
3stmt.setFetchSize(100);
4rs = stmt.executeQuery("SELECT * FROM your_table_here");

这就是服务器端游标的使用方法：在MySQL连接字符串中增加useCursorFetch=true参数，然后在Statement上执行setFetchSize设置期望每次处理的记录数。就这么简单。使用MyBatis的话，还是需要在Mapper XML文件中使用fetchSize参数来设置期望的记录条数。例如：

1<select id="exportByExample" fetchSize="100" parameterType="com.eveus.admin.po.logs.BankcardAuthNoOTPLogPOExample" resultMap="BaseResultMap">

2. 性能测试

看过官方文档，针对大结果集，实际上有两种方式可以用来解决JVM内存溢出的问题：

  1. 使用客户端游标方式的流处理方式；
  2. 使用服务器端游标方式处理。

那究竟哪种方式效率更高，速度更快呢？写个测试跑一下就知道了：

 1Date d1, d2, d3;
 2
 3BankcardAuthNoOTPLogPOExample example = new BankcardAuthNoOTPLogPOExample();
 4BankcardAuthNoOTPLogPOExample.Criteria criteria = example.createCriteria();
 5criteria.andIdGreaterThan(0L);
 6Map<String, Object> readerParams = new HashMap<String, Object>();
 7readerParams.put("oredCriteria", example.getOredCriteria());
 8
 9MyBatisCursorItemReader<BankcardAuthNoOTPLogPO> reader = new MyBatisCursorItemReader<>();
10reader.setSqlSessionFactory(sqlSessionFactory);
11reader.setQueryId("com.eveus.admin.mapper.logs.BankcardAuthNoOTPLogPOCustMapper.exportByExample");
12reader.setParameterValues(readerParams);
13
14d1 = new Date();
15reader.open(new ExecutionContext());    // 打开游标
16d2 = new Date();
17BankcardAuthNoOTPLogPO logPO = null;
18int rows = 0;
19while (( logPO = reader.read()) != null) {
20    rows++;
21    if (rows%10000==0) {
22        System.out.println(rows);
23    }
24}
25d3 = new Date();
26System.out.printf("Test Result:\n Open Cursor: %d, Read all records: %d\n", d2.getTime()-d1.getTime(), d3.getTime()-d2.getTime());
27System.out.println("Done");

上面的例子，分别记录了游标打开时间，所有记录全部遍历一遍的时间。下面是测试结果：

编号 fetchSize useCursorFetch 打开游标时间（ms) 遍历结果集时间(ms) 内存占用(G)
1 -2147483648 - 82 46466 1.53
2 - - 51641 24835 3.12
3 2000 - 51193 29519 3.09
4 - true 48955 23013 2.88
5 -2147483648 true 90 43334 1.37
6 1000 true 13162 65851 1.49

注1：以上结果基于一个总记录数为2954671的表进行测试所得； 注2：fetchSize参数在MyBatis Mapper文件中设置； 注3：useCursorFetch参数在MySQL链接字符串中设置；

通过结果可以简单分析出来：

  1. fetchSize如果是Integer.MIN_VALUE，则强制进行客户端游标的流处理模式（无论是否指定useCursorFetch参数）；
  2. 要使用客户端游标的流处理模式，则fetchSize必须为Integer.MIN_VALUE；
  3. 要使用服务器端游标，则必须启用useCursorFetch参数，并指定fetchSize。

3. 如何选择？

测试结果分析： 根据结果，我们实际上可以将六种测试场景情形归结成三种模式（猜测性质）：

a. 客户端游标的流处理模式，对应测试用例1，5； b. 服务器端游标处理模式，对应测试用例6； c. 传统客户端游标处理模式，对应其他三种测试用例。

游标打开速度：客户端游标的流处理模式耗时最短，不到100ms；其次是服务器端游标，10秒+左右；而对传统的客户端游标处理方式，需要把整个结果集缓冲到客户端，不只是容易引起内存溢出，首次打开游标的时间也非常长，50+秒左右。

记录集遍历速度：传统模式c速度最快，在20-30秒之间；而模式a次之，在40+秒左右；而服务器端游标模式b最慢，65秒了。

内存耗用：内存好用则是模式a、b差不太多，模式c好用内存太多。

所以最终的结论是什么呢？还真是没有完美的方案，最好还是根据自己的场景进行抉择了。另外的方式就是自己也写个测试程序多测测，结合自己的场景才能够选择合适的方案了。

对认证云的问题来说（导出Excel），最终选择的是模式a（客户端游标的流处理方式）：其总体处理速度最快（打开游标和记录遍历时间相加之和最小），占用内存也不大，对我来说是最恰当的方案了。

附录、参考资料

0%