Jdbc如何快速從PostgreSql擷取大量資料，記憶體不被撐破

標籤：

前言：

最近做資料同步，需要從PostgreSql擷取資料，發現一旦資料比較多，那麼讀取的速度非常慢，並且記憶體佔用特別多&GC不掉。

代碼範例：

為了方便講解，下面寫了案例代碼，從b2c_order擷取資料，這個資料表6G左右。

package com.synchro;import java.sql.*;/** * Created by qiu.li on 2015/10/16. */public class Test {    public static void main(String[] args) {        Connection conn = null;        try {            Class.forName("org.postgresql.Driver");            conn = DriverManager.getConnection("jdbc:postgresql://***.qunar.com:5432/database", "username", "password");            String sql = "select * from mirror.b2c_order";            PreparedStatement ps = conn.prepareStatement(sql);            ps.setMaxRows(1000);            ResultSet rs = ps.executeQuery();            int i = 0;            while (rs.next()) {                i++;                if (i % 100 == 0) {                    System.out.println(i);                }            }        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }    }}

現象：

在Idea執行代碼，發現卡死，並且佔用大量的記憶體

解決方案：

然後我決定開始逐步調試，跟蹤代碼：

第一步、我發現是在執行executeQuery方法的時候卡住的

第二步、是在執行AbstractJdbc2Statement.executeWithFlags方法卡住的

第三步、繼續跟蹤，並在網路上查看可能引起的原因是和設定fetchSize參數相關，所以繼續跟蹤

第四步、sendQuery，sendOneQuery方法，在這裡發現了問題，好在代碼不太多，我就都貼出來了：

        boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta && fetchSize > 0 && !describeOnly;// Work out how many rows to fetch in this pass.        int rows;        if (noResults)        {            rows = 1;             // We‘re discarding any results anyway, so limit data transfer to a minimum        }        else if (!usePortal)        {            rows = maxRows;       // Not using a portal -- fetchSize is irrelevant        }        else if (maxRows != 0 && fetchSize > maxRows)        {            rows = maxRows;       // fetchSize > maxRows, use maxRows (nb: fetchSize cannot be 0 if usePortal == true)        }        else        {            rows = fetchSize;     // maxRows > fetchSize        }

可見是usePortal參數在影響Jdbc從資料庫擷取資料的行數，具體的原因參考：http://m.blog.csdn.net/blog/itjin45/42004447#

boolean usePortal = (flags & QueryExecutor.QUERY_FORWARD_CURSOR) != 0 && !noResults && !noMeta && fetchSize > 0 && !describeOnly;這麼多條件，只要一個成立，fetchSize就失效了：!noResults表示這個SQL不需要返回任何結果，這個肯定等於true，因為所有的select都會要求返回結果!noMeta表示這個SQL不需要返回中繼資料，這個肯定等於true，因為select都要求返回中繼資料，供後續的resultSet.get使用!fetchSize大於0，這個不說了，自然是true!describeOnly,這個只有在desc table這樣的語句的時候，才會是false，對於select，也是true那麼，試下的唯一的可能導致usePortal為false的原因就是 flags & queryExecutor.QUERY_FORWARD_CURSOR這個值等於0了。。繼續往上翻，看看什麼時候才不會執行flags = flags |  QueryExecutor.QUERY_FORWARD_CURSOR 這個代碼了，因為只有這個代碼沒有被執行過，才會導致上面這個條件為false然後將代碼定位到了AbstractJdbc2Statement類的execute方法： // Enable cursor-based resultset if possible.        if (fetchSize > 0 && !wantsScrollableResultSet() && !connection.getAutoCommit() && !wantsHoldableResultSet())            flags |= QueryExecutor.QUERY_FORWARD_CURSOR;其中：wantsHoldableResultSet（）代碼直接返回的false，所以，不考慮這個，那麼，要麼wantsScrollableResultSet()返回true，或者connection.getAutoCommit()返回true，才會導致flags不包含QueryExecutor.QUERY_FORWARD_CURSOR，才會導致fetchSize失效wantsScrollableResultSet()這個方法的代碼為： protected boolean wantsScrollableResultSet() {        return resultsettype != ResultSet.TYPE_FORWARD_ONLY;    }至此，問題已經被最終定位到：1、如果connection是自動認可事務的，那麼，fetchSize將失效2、如果statement不是TYPE_FORWARD_ONLY的，那麼，fetchSize也將失效

結論

如果想fetchSize生效，必須保證connection是autocommit = false的，並且，statement為forward_only的：conn.setAutoCommit(false);final Statement statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.FETCH_FORWARD);另外，不帶參數的conn.createStatement()，其預設就是TYPE_FORWARD_ONLY所以，一般情況下，如果想fetchsize生效，必須設定autocommit為flase，也就是需要手工去管理事務。

那麼修改代碼如下：

package com.synchro;import java.sql.*;/** * Created by qiu.li on 2015/10/16. */public class Test {    public static void main(String[] args) {        Connection conn = null;        try {            Class.forName("org.postgresql.Driver");            conn = DriverManager.getConnection("jdbc:postgresql://l-tdata2.tkt.cn6.qunar.com:5432/log_analysis", "tkt_data_dev", "23eadc16-a4e4-418c-b18a-ccb2a6b9d587");            conn.setAutoCommit(false); //並不是所有資料庫都適用，比如hive就不支援，orcle不需要            String sql = "select * from mirror.b2c_order";            PreparedStatement ps = conn.prepareStatement(sql);            ps.setFetchSize(1000); //每次擷取1萬條記錄            //ps.setMaxRows(1000);            ResultSet rs = ps.executeQuery();            int i = 0;            while (rs.next()) {                i++;                if (i % 100 == 0) {                    System.out.println(i);                }            }        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }    }}

這次再一次執行，發現根本不卡。

參考文獻

https://jdbc.postgresql.org/documentation/head/query.html 

http://m.blog.csdn.net/blog/itjin45/42004447#

Jdbc如何快速從PostgreSql擷取大量資料，記憶體不被撐破