Jdbc如何從PostgreSql讀取海量資料？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參數相關，所以我設定了fetchSize，奇葩的是沒有生效

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

        boolean usePortal = (flags & 8) != 0 && !noResults && !noMeta && fetchSize > 0 && !describeOnly;        boolean oneShot = (flags & 1) != 0 && !usePortal;        int rows;        if(noResults) {            rows = 1;        } else if(!usePortal) {            rows = maxRows;        } else if(maxRows != 0 && fetchSize > maxRows) {            rows = maxRows;        } else {            rows = fetchSize;        }

可見是usePortal是true，那麼fetchSize才會生效。

boolean usePortal = (flags & 8) != 0 && !noResults && !noMeta && fetchSize > 0 && !describeOnly;

那麼咱們逐一看一下這些條件：

• !noResults表示這個SQL不需要返回任何結果，這個肯定等於true，因為所有的select都會要求返回結果
• !noMeta表示這個SQL不需要返回中繼資料，這個肯定等於true，因為select都要求返回中繼資料，供後續的resultSet.get使用
• !fetchSize大於0，這個不說了，自然是true
• !describeOnly，這個只有在desc table這樣的語句的時候，才會是false，對於select，也是true

那麼，試下的唯一的可能導致usePortal為true的原因就是 flags & 8這個值是true。。（我想說這種寫法很別緻，tmd，設定flags的時候肯定是flags=flag|8，後來發現新的驅動修改了這種寫法）

繼續往上翻，看看什麼時候才會執行flags = flags | 8 這個代碼了，因為只有這個代碼被執行過，才會導致上面這個條件為true

        if(this.fetchSize > 0 && !this.wantsScrollableResultSet() && !this.connection.getAutoCommit() && !this.wantsHoldableResultSet()) {            flags |= 8;        }

其中：wantsHoldableResultSet()代碼直接返回的false，所以，不考慮這個。

那麼，wantsScrollableResultSet()返回false，並且connection.getAutoCommit()返回false，才會導致fetchSize生效。wantsScrollableResultSet()這個方法的代碼為：

protected boolean wantsScrollableResultSet() {        return resultsettype != 1003; //老代碼，看到這裡我真想死，1003是啥？好在偶然的機會看見了新的Postgresql驅動，使用ResultSet.TYPE_FORWARD_ONLY表示1003
}

至此，問題終於被定位：

1、如果connection不是自動認可事務的，那麼，fetchSize將生效（非預設）

2、如果statement是TYPE_FORWARD_ONLY的，那麼，fetchSize也將生效（預設）

結論

如果想fetchSize生效，必須保證connection是autocommit = false的，並且，statement為1003（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，也就是需要手工去管理事務。預設的原始碼如下：

    public Statement createStatement() throws SQLException {        return this.createStatement(1003, 1007); //有興趣的同學可以繼續跟蹤看看，1003就是resultsettype    }

代碼：

那麼修改代碼如下：

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/datasource", "username", "password");            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讀取海量資料？PostgreSql原始碼分析紀錄