商城转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 版本的 在测试环境跑了一个下午。 差距很大。 

记于此，为后来者指路。

ITPub博客

PG jdbc 驱动的cursor 的使用

最新文章