【数据库】聊聊数据库中的 fetchsize 参数

聊聊数据库中的 fetchsize 参数

1.介绍
2.案例
3.MySQL 中的 fetchsize
4.Oracle 中的 fetchsize

1.介绍

在使用查询语句的时候，经常需要根据条件来进行查询得到最终的总记录条数，然后得到结果之后需要来进行处理。

场景：Java 端从数据库读取 100W 数据进行后台业务处理。

常规实现 1：分页读取出来。缺点：需要排序后分页读取，性能低下。
常规实现 2：一次性读取出来。缺点：需要很大内存，一般计算机不行。
非常规实现：建立长连接，利用服务端游标，一条一条流式返回给 Java 端。
非常规实现优化：JDBC 中有个重要的参数 fetchSize（它对业务实现无影响，即不会限制读取条数等），优化后可显著提升性能。

缺省时，驱动程序一次从查询里获取所有的结果。这样可能对于大的数据集来说是不方便的，因此 JDBC 驱动提供了一个用于设置从一个数据库游标抽取若干行的 ResultSet 的方法。在连接的客户端这边缓冲了一小部分数据行，并且在用尽之后，则通过重定位游标检索下一个数据行块。

setFetchSize 最主要是为了减少网络交互次数设计的。访问 ResultSet 时，如果它每次只从服务器上取一行数据，则会产生大量的开销。setFetchSize 的意思是当调用 rs.next 时，ResultSet 会一次性从服务器上取得多少行数据回来，这样在下次 rs.next 时，它可以直接从内存中获取出数据而不需要网络交互，提高了效率。这个设置可能会被某些 JDBC 驱动忽略的，而且设置过大也会造成内存的上升。

2.案例

MySQL 中的 fetchsize 是指从结果集中获取行数据的大小。fetchsize 的大小直接影响到程序的性能和响应时间，因此是值得注意的。

在 MySQL 中，当执行 SELECT 语句时，会返回一定数量的数据行。fetchsize 就是表示从结果集中获取多少行数据的大小。

int fetchSize = 100;  //设置fetchsize的大小为100
Statement stmt = conn.createStatement();
stmt.setFetchSize(fetchSize); //将fetchsize的大小设置为100
ResultSet rs = stmt.executeQuery("SELECT * FROM users");

上面的代码中，fetchsize 的大小被设置为 100。当 ResultSet 对象被创建后，只会获取到前 100 条数据。当我们遍历 ResultSet 对象时，例如使用 rs.next()，在获取到第 100 条记录时，会自动从数据库中获取接下来的数据。由此，我们可以看出，fetchsize 对程序的性能和响应时间起到非常重要的作用。

当 fetchsize 设置的太小，会导致程序频繁地访问数据库，从而影响程序的性能；当 fetchsize 设置的太大，可能会导致内存不足，从而影响程序的响应时间。

因此，在设置 fetchsize 时，需要结合程序的具体需求和硬件设备的配置进行合理的调整。如果不确定 fetchsize 的大小该如何设置，可以先进行测试，不断调整 fetchsize 的大小，直到找到合适的配置。

3.MySQL 中的 fetchsize

默认情况下，MySQL 的 JDBC 驱动会一下子把所有 row 都读取下来，这在一般情况下是最优的，因为可以减少 Client - Server 的通信开销。但是这样也有一个问题，当数据库查询结果很大时，特别当不能全部放进内存时，就会产生性能问题。本来，JDBC API 里在 Connection、Statement 和 ResultSet 上都有设置 fetchSize 的方法，但是 MySQL 的 JDBC 驱动都不支持，无论你怎么设 fetchSize，ResultSet 都会一次性从 Server 读取数据。在 MySQL 的官方论坛上也有多个这样的问题，总结一下解决办法如下：

MySQL 版本在 5.0 以上，MySQL 的 JDBC 驱动更新到最新版本（至少 5.0 以上）
Statement 一定是 TYPE_FORWARD_ONLY 的，并发级别是 CONCUR_READ_ONLY（即创建 Statement 的默认参数）
以下两句语句选一即可：
- statement.setFetchSize(Integer.MIN_VALUE);
- ((com.mysql.jdbc.Statement)stat).enableStreamingResults();

这样会一行一行地从 Server 读取数据，因此通信开销很大，但内存问题可以解决。官方的说法是不支持 fetchSize 不是 MySQL 的 JDBC 驱动的问题，而是 MySQL 本身就不支持。而商用数据库 Oracle 或 DB2 都是支持 fetchSize 的，从这里也可以看出两者的考量不同。

MySQL 默认是不支持 fetchSize 的，像 Oracle 和 DB2 等商业数据库则支持，那么这么在 JDBC 连接 MySQL 是使用这个参数呢，满足三个条件，，官方是这么说的

param	explain	value
useCursorFetch	If connected to MySQL > 5.0.2, and setFetchSize() > 0 on a statement, should that statement use cursor-based fetching to retrieve rows?	false

首先数据库版本要高于 5.0.2
再就是设置了 fetchSize
最最重要的是在数据库连接后加上，useCursorFetch=true

url=jdbc:mysql://192.168.1.3:3306/cuishou3?useCursorFetch=true

4.Oracle 中的 fetchsize

上面已经讲过了 Oracle 是默认支持的。

public static void main(String[] args) throws SQLException {
    getAll(1);
    getAll(10);
    getAll(100);
    getAll(1000);
}

public static void getAll(int fetchSize) {
    try {
        long beginTime = System.currentTimeMillis();

        Connection connection = DriverManager.getConnection(MYSQL_URL);
        connection.setAutoCommit(false); //为了设置fetchSize,必须设置为false
        String sql = "select * from test";
        PreparedStatement psst = connection.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        psst.setFetchSize(fetchSize);
        ResultSet rs = psst.executeQuery();

        int totalCount = 0;
        // 从内存中来进行读取
        while (rs.next()) {
                totalCount++;
        }
        rs.close();
        psst.close();
        connection.close();

        long endTime = System.currentTimeMillis();
        System.out.println("totalCount:"+totalCount+";fetchSize:"+fetchSize+";耗时:"+(endTime-beginTime)+"ms");
    } catch (SQLException e) {
        e.printStackTrace();
    } 
}

执行结果如下所示：

totalCount:3185194;fetchSize:1;耗时:23770ms
totalCount:3185194;fetchSize:10;耗时:23253ms
totalCount:3185194;fetchSize:100;耗时:21890ms
totalCount:3185194;fetchSize:1000;耗时:20985ms

可以看到，当 fetchSize 为 1000 时，性能有提升（看一些网友的数据，性能提升更多）。

先在服务端执行查询后将数据缓存在服务端（耗时相对较长）。
Java 端获取数据时，利用服务端游标进行指针跳动，如果 fetchSize 为 1000，则一次性跳动 1000 条，返回给 Java 端缓存起来（耗时较短，跳动次数为 $N /1000$ ）。
在调用 next 函数时，优先从缓存中取数，其次执行上一步过程（内存读取，耗时可忽略）。

在这里插入图片描述