iBatis分页查询的性能有关问题分析
关于iBatis的分页性能问题,网上的讨论也很多,经过验证,我的结论是:只有在表的数据量很大,并且是从很后面的一个位置取一页数据的时候(比如从1000000条开始取100条),性能问题才比较明显。分析如下。
首先看一下iBatis的分页代码。iBatis中,具体负责执行sql的类是com.ibatis.sqlmap.engine.execution.SqlExecutor。负责分页查询的方法是executeQuery —>handleMultipleResults —> handleResults。handleResults方法的源码如下:
private void handleResults(RequestScope request, ResultSet rs, int skipResults, int maxResults, RowHandlerCallback callback) throws SQLException { try { request.setResultSet(rs); ResultMap resultMap = request.getResultMap(); if (resultMap != null) { // Skip Results if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) { if (skipResults > 0) { rs.absolute(skipResults); } } else { for (int i = 0; i < skipResults; i++) { if (!rs.next()) { return; } } } // Get Results int resultsFetched = 0; while ((maxResults == SqlExecutor.NO_MAXIMUM_RESULTS || resultsFetched < maxResults) && rs.next()) { Object[] columnValues = resultMap.resolveSubMap(request, rs).getResults(request, rs); callback.handleResultObject(request, columnValues, rs); resultsFetched++; } } } finally { request.setResultSet(null); } }
从代码中可以看出iBatis分页查询的逻辑是首先判断ResulteSet的类型,如果ResultSet的类型是ResultSet.TYPE_FORWARD_ONLY,则使用ResultSet对象的next()方法,一步一步地移动游标到要取的第一条记录的位置,然后再采用next()方法取出一页的数据;如果ResultSet的类型不是ResultSet.TYPE_FORWARD_ONLY,则采用ResultSet对象的absolute()方法,移动游标到要取的第一条记录的位置,然后再采用next()方法取出一页的数据。
ResultSet的类型,是在iBatis的配置文件中配置的,如:
<select id="queryAllUser" resultMap="user" resultSetType="FORWARD_ONLY">
select id,name from user_tab
</select>
其中resultSetType的可选值为FORWARD_ONLY | SCROLL_INSENSITIVE | SCROLL_SENSITIVE,如果没有配置,默认值为FORWARD_ONLY,FORWARD_ONLY类型的ResultSet 不支持absolute方法,所以是通过next方法定位的。一般情况下,我们都使用FORWARD_ONLY类型的ResultSet,SCROLL类型ResultSet的优点是可向前,向后滚动,并支持精确定位(absolute),但缺点是把结果集全部加载进缓存(如果查询是从1000000条开始取100条,会把前100万条数据也加载进缓存),容易造成内存溢出,性能也很差,除非必要,一般不使用。
可见,iBatis的分页完全依赖于JDBC ResultSet的next方法或absolute方法来实现,而Hibernate在分页查询方面,比iBatis要好很多,Hibernate可以根据不同的数据库,对sql做不同的优化加工,然后再执行优化后的sql。比如,对于Oracle数据库来说,原始sql为select * form user_tab, 从1000001条开始取100条,则hibernate加工后的sql为:
select *
from (select row_.*, rownum rownum_
from (SELECT * FROM user_tab) row_
where rownum <= 1000100)
where rownum_ > 1000000
写一个程序,对比一下两种方式下的查询效率。程序如下:
public class Test{ public static void main(String[] args) throws Exception { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:db", "db", "xwdb"); long a = System.currentTimeMillis(); testPageQuery1(conn); //testPageQuery2(conn); long b = System.currentTimeMillis(); System.out.println(b-a); } public static void testPageQuery1(Connection conn) throws Exception{ String sql = "SELECT * FROM user_tab "; Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery(sql); int j=0; //游标移动到1000001条数据的位置 while(rs.next() && j++<1000000){ } int i=0; //依次取出100条数据 while(rs.next() && i++<100){ } } public static void testPageQuery2(Connection conn) throws Exception{ String sql = "SELECT * FROM user_tab "; StringBuffer pagingSelect = new StringBuffer( sql.length()+100 ); pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( "); pagingSelect.append(sql); pagingSelect.append(" ) row_ where rownum <= 1000100) where rownum_ > 1000000"); Statement stmt = conn .createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery(pagingSelect.toString()); while(rs.next()){ } } }
testPageQuery1方法是用ibatis采用的分页方法查询,testPageQuery2是用Hibernate采用的分页方法查询,发现testPageQuery1需要执行十几秒,而testPageQuery2仅需要执行零点几秒,差异很大。而如果改成从1000条开始取100条,甚至更靠前,则2者的差别是非常小的。
综上所述,如果系统中查询的数据量很大,并且用户会选择查询非常靠后的数据,那么我们就应该替换iBatis的分页实现,如果不存在这种情况,那我们就不需要替换iBatis的分页实现,一般情况下,用户不可能去查询那么靠后的页,这也是iBatis一直不修改分页实现的原因吧。
如果我们选择替换的话,有三种办法,一种是自己写一个类,继承iBatis的SqlExecutor,然后把这个类注入到com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient中,由于SqlExecutor是ExtendedSqlMapClient的私有变量,没有public类型的set方法,所以需要采用reflect机制注入;第二种方法是在自己的工程里写一个和iBatis的SqlExecutor的包名和类名完全一样的类,web工程中,WEB-INF/classes下的java类,先于WEB-INF/lib下jar包的加载,所以就巧妙了覆盖了iBatis的SqlExecutor类;第三种办法是弃用iBatis的分页查询方法queryForList(String sql,Object obj,int maxResult,int skipResult),而用普通查询方法,queryForList(String sql,Object obj)。只不过把maxResult和skipResult都作为obj的变量传到sql里去。如下:
<select id="queryAllUser" resultMap="user">
select *
from (select row_.*, rownum rownum_
from (SELECT * FROM user_tab) row_
where rownum <= #_maxResult#)
where rownum_ > #_skipResult#
</select>
<p><select id="queryAllUser" resultMap="user"> <br>select *<br> from (select row_.*, rownum rownum_<br> from (SELECT * FROM user_tab) row_<br> where rownum <= #_maxResult#)<br> where rownum_ > #_skipResult#<br> </select><br><br><strong>iBATIS 3.0</strong><br><a href="http://melin.iteye.com/blog/491713">iBatis3 Dialect 分页</a></p>
<p> </p>
所以通常会分两次查询,第一次只查询出所有符合条件的主键(通常为ID,会有索引,因此性能不会很差),这个时候的主键都已经被安全系统好,都是用户可以访问的记录。然后再对这些主键进行分页,之后将分页后的ID收集起来,再去执行“真正”的数据查询,取出符合条件的记录。
不知道大家对这种分页方式有没有什么改进的地方,欢迎大家讨论一下!
所以通常会分两次查询,第一次只查询出所有符合条件的主键(通常为ID,会有索引,因此性能不会很差),这个时候的主键都已经被安全系统好,都是用户可以访问的记录。然后再对这些主键进行分页,之后将分页后的ID收集起来,再去执行“真正”的数据查询,取出符合条件的记录。
不知道大家对这种分页方式有没有什么改进的地方,欢迎大家讨论一下!
我觉得应该尽量避免分两次查询。原来用ACEGI的时候,也考虑过分页查询的时候先把数据查出来,然后再过滤,但感觉这种做法不好,还是应该想办法把权限的控制加入到sql的where条件里去。
<div class="quote_div">
<p><span style="font-weight: bold;">iBATIS 2.x</span></p>
<p><select id="queryAllUser" resultMap="user"> <br>select *<br> from (select row_.*, rownum rownum_<br> from (SELECT * FROM user_tab) row_<br> where rownum <= #_maxResult#)<br>where rownum_ > #_skipResult#<br> </select><br><br><strong>iBATIS 3.0</strong><br><a href="http://melin.iteye.com/blog/491713">iBatis3 Dialect 分页</a></p>
<p> </p>
</div>
<p><br>oracle分页的时候就用这个 mysql用limit</p>
所以通常会分两次查询,第一次只查询出所有符合条件的主键(通常为ID,会有索引,因此性能不会很差),这个时候的主键都已经被安全系统好,都是用户可以访问的记录。然后再对这些主键进行分页,之后将分页后的ID收集起来,再去执行“真正”的数据查询,取出符合条件的记录。
不知道大家对这种分页方式有没有什么改进的地方,欢迎大家讨论一下!
我觉得应该尽量避免分两次查询。原来用ACEGI的时候,也考虑过分页查询的时候先把数据查出来,然后再过滤,但感觉这种做法不好,还是应该想办法把权限的控制加入到sql的where条件里去。
通用性又不好保证了.
由于权限是个树...
如果用sql的话写起sql来很要命...
所以通常会分两次查询,第一次只查询出所有符合条件的主键(通常为ID,会有索引,因此性能不会很差),这个时候的主键都已经被安全系统好,都是用户可以访问的记录。然后再对这些主键进行分页,之后将分页后的ID收集起来,再去执行“真正”的数据查询,取出符合条件的记录。
不知道大家对这种分页方式有没有什么改进的地方,欢迎大家讨论一下!
我也正在做这方面的东西, 想法和这差不多, 不知道有没更好的方法控制数据。
推荐动态生成SQL(应用基础组件)、数据库端分页。它们是性能的保障!
动态生成SQL的通用性好;数据库端分页的通用性差。
推荐动态生成SQL(应用基础组件)、数据库端分页。它们是性能的保障!
动态生成SQL的通用性好;数据库端分页的通用性差。
没看懂什么意思,呵呵。到底推荐哪一个?
**ibatis原来是机械的next().之前还真没想过这样的问题,hibernate根据数据源做sql级别的优化看来是优越的,也许因为ibatis那些设计者想着能很灵活的支持sql,所以就懒得写了,呵呵。不过这种“偷懒”降低了移植性。
**至于ispring提出的安全控制,我怎么感觉说得跟这个是两码事了?
能讲述下你关于“配置工作相当繁重”的理解么?
<select id="queryAllUser" resultMap="user">
select *
from (select row_.*, rownum rownum_
from (SELECT * FROM user_tab) row_
where rownum <= #_maxResult#)
where rownum_ > #_skipResult#
</select>
-----------------------------------------
这才是正解。
鄙人一般都这样分页。
这样分页估计和hibenate的性能没什么区别,并且还可以手动优化。
貌似是parameterClass - -!!