商城轉pg 有個索引的功能模組，

之前在oracle 端的做法是根據rownum 分頁讀取的，每次10w 筆記錄。

轉到pg後，也是採用了這個方式，似乎就不太湊效了。

如果不用order by 排序，直接使用limit m,n 每次返回10w筆記錄，似乎會有重複的資料。

第一次返回的部分記錄會在第二次返回的記錄集中出現。導致索引不太準確。於是加了order by 來排序
然後用limit m,n 返回記錄。結果加了這個排序後，效率低的嚇人，第一個10w筆還是可以接受，之後的，就實在是無法忍受了。

於是建議開發同事用遊標的方式來提取資料，這樣就不用排序了，也不用多次執行sql語句了。

開發同事做了實驗：
[code]
Connection conn = ((DataSource) EnvUtils.getEnv().getApplicationContext().getBean("dataSource")).getConnection();
PreparedStatement pst = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
pst.setFetchSize(10000);

java.lang.OutOfMemoryError: Java heap space

[/code]
記憶體溢位了，雖然我們設定了每次fetch 1w筆記錄，似乎是把整個資料庫裡的記錄全部都取出來了。

原因在哪裡呢，於是去專門看了pg的jdbc 驅動的doc。

問題終於找到了。

pg 裡如果要使用cursor 功能來批次處理資料，需要開啟事務功能。
pg 的jdbc 的doc 裡給了一個例子：
[code]

ExampleÂ 5.2.Â Setting fetch size to turn cursors on and off.

Changing code to cursor mode is as simple as setting the fetch size of the Statement to the appropriate size. Setting the fetch size back to 0 will cause all rows to be cached (the default behaviour).

// make sure autocommit is off
conn.setAutoCommit(false);
Statement st = conn.createStatement();

// Turn use of the cursor on.
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
   System.out.print("a row was returned.");
}
rs.close();

// Turn the cursor off.
st.setFetchSize(0);
rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
   System.out.print("many rows were returned.");
}
rs.close();

// Close the statement.
st.close();

[/code] 

按此方法請開發同事測試了下，果然給力。 

於是動手改造了索引的這部分程式碼，90W 記錄，完成索引大約10分鐘。 

而之前order by limit m,n 版本的 在測試環境跑了一個下午。 差距很大。 

記於此，為後來者指路。

PG jdbc 驅動的cursor 的使用

相關文章