数据库中使用fatc size

JDBC批量读取优化-fetchSize

0、简单介绍

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

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

常规实现1：分页读取出来。缺点：需要排序后分页读取，性能低下。

常规实现2：一次性读取出来。缺点：需要很大内存，一般计算机不行。

非常规实现：建立长连接，利用服务端游标，一条一条流式返回给java端。

非常规实现优化：jdbc中有个重要的参数fetchSize（它对业务实现无影响，即不会限制读取条数等），优化后可显著提升性能。

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

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

1、MySQL中对fatch size的处理方式

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

1.MySQL版本在5.0以上，MySQL的JDBC驱动更新到最新版本(至少5.0以上)

2.Statement一定是TYPE_FORWARD_ONLY的，并发级别是CONCUR_READ_ONLY(即创建Statem ent的默认参数)

3.以下两句语句选一即可：

1).statement.setFetchSize(Integer.MIN_VALUE);

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

原文链接：https://blog.csdn.net/weixin_33045961/article/details/113135787

MySQL中使用fetchsize：https://www.cnblogs.com/arcticBoiledWater/p/14042912.html

Mysql默认是不支持fetchSize的，像Oracle和db2等商业数据库则支持，那么这么在jdbc连接mysql是使用这个参数呢，满足一下三个条件：

• 首先数据库版本要高于5.0.2，官方是这么说的

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

• 再就是设置了fetchSize，例如

  PreparedStatement pstmt = conn.prepareStatement(sql );
  

• 最最重要的是在数据库连接后加上，

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

完成上面的设置后，fetchSize就生效了，每次拉取fetchSize调数据，而不是默认的把所有数据一次拉取出来。

2、Oracle数据库对fatch size的使用方式：

上面已经讲过了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时，性能有提升。（看一些网友的数据，性能提升更多）

（三）原理分析

1、先在服务端执行查询后将数据缓存在服务端。（耗时相对较长）

2、java端获取数据时，利用服务端游标进行指针跳动，如果fetchSize为1000，则一次性跳动1000条，返回给java端缓存起来。（耗时较短，跳动次数为N/1000）

3、在调用next函数时，优先从缓存中取数，其次执行2过程。（内存读取，耗时可忽略）

题外话：spring的JdbcCursorItemReader是对fetchSize的良好应用。

参考链接：

https://www.cnblogs.com/baimingqian/p/11761942.html

3、测试MySQL

static final String DB_URL = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&&rewriteBatchedStatements=true&useCursorFetch=true";

	@Test
    public void testFetchSize(){
        try {
            long beginTime=System.currentTimeMillis();

            Connection connection = DriverManager.getConnection(DB_URL,"root","root");
            connection.setAutoCommit(false); //为了设置fetchSize,必须设置为false
            String sql = "select * from tb_user";
            PreparedStatement psst = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            psst.setFetchSize(FETCH_SIZE);
            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:"+FETCH_SIZE+";耗时:"+(endTime-beginTime)+"ms");  // 		totalCount:30000;fetchSize:100;耗时:326ms
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Test
    public void noUserFetchSize(){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String url =  "jdbc:mysql://localhost:3306/jdbc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&&rewriteBatchedStatements=true&useCursorFetch=true";
        try {
            conn= DriverManager.getConnection(url,"root","root");
            String sql = "select * from  tb_user ";
            stmt = conn.prepareStatement(sql);
            long start = System.currentTimeMillis();
            rs = stmt.executeQuery();
            int countSize = 0;
            while (rs.next()){
                countSize++;
            }
            long timeDifference = System.currentTimeMillis()-start;
            System.out.println("执行时长差为："+timeDifference+"对应的总数为："+countSize); // 43ms
        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
            try {
                // 数据进行回滚
                conn.rollback();
                System.out.println("执行数据库操作失败...... 回滚事务");
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        } finally {
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        System.out.println("Goodbye!");
    }

3.1、测试结果

在mysql中，当数据量是相同大时候，我设置的是3W。

在fectch size 发生不断变动的情况下，需要根据数据量来进行调整。调整的大的时候，速度比不使用快一点；小的时候慢一点。