【原创】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>

 

 

iBATIS 2.x

<select id="queryAllUser" resultMap="user">
select *
  from (select row_.*, rownum rownum_
          from (SELECT * FROM user_tab) row_
         where rownum <= #_maxResult#)
where rownum_ > #_skipResult#
    </select>

iBATIS 3.0
iBatis3 Dialect 分页

 

大部分应用都会有安全控制系统，因此在获取到数据后一般会对数据进行一系列的安全过滤，去从Collection中删除用户没有权限查看有数据，这个时候的以上的分页方式便不再适用，因为数据总量，一页的数据都有可能会被过滤，就会比较乱。
所以通常会分两次查询，第一次只查询出所有符合条件的主键（通常为ID，会有索引，因此性能不会很差），这个时候的主键都已经被安全系统好，都是用户可以访问的记录。然后再对这些主键进行分页，之后将分页后的ID收集起来，再去执行“真正”的数据查询，取出符合条件的记录。
不知道大家对这种分页方式有没有什么改进的地方，欢迎大家讨论一下！

我觉得应该尽量避免分两次查询。原来用ACEGI的时候，也考虑过分页查询的时候先把数据查出来，然后再过滤，但感觉这种做法不好，还是应该想办法把权限的控制加入到sql的where条件里去。

oracle分页的时候就用这个 mysql用limit

通用性又不好保证了.
由于权限是个树...
如果用sql的话写起sql来很要命...

我也正在做这方面的东西， 想法和这差不多， 不知道有没更好的方法控制数据。

采用sql级别的分页查询比iBatis的分页性能好的多

需要哪些数据就查哪些才是效率提高的最重要的吧！

首先肯定一下楼主的钻研精神，哈哈。

推荐动态生成SQL（应用基础组件）、数据库端分页。它们是性能的保障!
动态生成SQL的通用性好；数据库端分页的通用性差。