Hibernate的优化：分页

有同学强调我对sql server的分页不了解，在mssql 2000之前只能通过top进行分页。我没有说top分页不对，我是说在hibernate的sql server方言里面，针对sql server 的top分页是不对的。至少在我使用的版本里是不对的。而且有很多同学也遇到和我类似的问题。请大家打开hibernate的showsql，看看生成的sql 语句再来判断正确还是错误，谢谢！
提到Hibernate，好多人都用“垃圾”，“效率太低”这样的字眼评价。其实任何一个框架的产生都是有原因的，这些为企业级开发产生的框架最初只是为了简化开发，或许会有一些影响性能的地方。但是通过配置和巧妙的办法，完全可以规避这些问题，发挥最大的效果。至少目前我的项目，日均pv在500w左右目前还没发现什么问题。
一、hibernate分页 hibernate对MsSql的伪分页
分页是web项目中比不可少的一个功能，数据量大的时候不能全部展示必然要用到分页技术。相信大家对hibernate中的分页都不陌生：

public Query setMaxResults(int maxResults);
public Query setFirstResult(int firstResult);

只要调用了这两个方法并设置好参数，hibernate自动分页完全屏蔽了底层数据库分页技术，这也是众多开发者喜欢hibernate的原因之一。
项目开发中遇到一个奇怪的问题。数据库采用的是Sql Server 2005，也设置了上面两个参数，可是每次发送到数据库端的SQL语句都是select top ....语句。即便是查询第10w条，也只有一个select top 语句，不免引起对hibernate实现sql server分页的怀疑。hibernate针对不同数据库实现的分页方法封装在对应数据库的方言里，通过getLimitString方法转化成对应数据库的分页算法。
以常见的Mysql数据库的方言MySQLDialect为例：

public String getLimitString(String sql, boolean hasOffset) {
		return new StringBuffer( sql.length() + 20 )
				.append( sql )
				.append( hasOffset ? " limit ?, ?" : " limit ?" )
				.toString();
	}

采用了大家熟悉的mysql的limit进行分页。
Oracle数据库的方言Oracle9iDialect：

StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
		if (hasOffset) {
			pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
		}
		else {
			pagingSelect.append("select * from ( ");
		}
		pagingSelect.append(sql);
		if (hasOffset) {
			pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
		}
		else {
			pagingSelect.append(" ) where rownum <= ?");
		}

利用Oracle的rownum 结合三层嵌套查询完成分页。这个三层是Oracle最经典高效的分页算法。
可是针对Sql Server的方言SQLServerDialect：

public String getLimitString(String querySelect, int offset, int limit) {
		if ( offset > 0 ) {
			throw new UnsupportedOperationException( "query result offset is not supported" );
		}
		return new StringBuffer( querySelect.length() + 8 )
				.append( querySelect )
				.insert( getAfterSelectInsertPoint( querySelect ), " top " + limit )
				.toString();
	}

揉揉眼睛、再揉揉，没错，只出现了一个top语句。这就意味着如果查询第10w页的数据，需要把前10w页数据全部提取出来。hibernate针对sql server的分页是伪分页，所以随着数据量日益增加用户抱怨系统速度慢，程序员抱怨hibernate性能低，dba抱怨开发人员sql功底太浅。
不知道hibernate开发组，出于什么目前或情况没有真正提供sql server的分页技术，那我们自己来实现。
方言类：

public class SQLServer2005Dialect extends SQLServerDialect {

	/**
	 * 
	 * 是否需要绑定limit参数?
	 * 
	 * 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数
	 */

	private ThreadLocal<Boolean> supportsVariableLimit = new ThreadLocal<Boolean>();
	public SQLServer2005Dialect() {
		registerFunction("bitand", new BitAndFunction());
		registerFunction("bitxor", new BitXorFunction());
		registerFunction("bitor", new BitOrFunction());
		setSupportsVariableLimit(false);
	}

	/**
	 * 
	 * <p>
	 * 设置是否先绑定limit参数。
	 * </p>
	 * 
	 * @param first
	 */

	private void setSupportsVariableLimit(boolean first) {
		this.supportsVariableLimit.set(Boolean.valueOf(first));
	}

	/**
	 * 
	 * <p>
	 * 获取sql中select子句位置。
	 * </p>
	 * 
	 * @param sql
	 * 
	 * @return int
	 */
	protected static int getSqlAfterSelectInsertPoint(String sql) {
		int selectIndex = sql.toLowerCase().indexOf("select");

		int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");

		return selectIndex + ((selectDistinctIndex == selectIndex) ? 15 : 6);
	}

	public boolean supportsLimitOffset() {
		return true;
	}

	/*
	 * Hibernate在获得Limit String(已添加了limit子句)后，如果此方法返回true，
	 * 
	 * 则会添加额外的参数值(ROW_NUMBER()范围)(策略可能是这样：有offset设置两个参数值，没有设置一个参数值)
	 */
	public boolean supportsVariableLimit() {
		return ((Boolean) this.supportsVariableLimit.get()).booleanValue();
	}

	public boolean useMaxForLimit() {
		return true;
	}
	/**
	 * 首页top，以后用ROW_NUMBER
	 */
	public String getLimitString(String query, int offset, int limit) {
		setSupportsVariableLimit(offset > 0);

		if (offset == 0) {
			return new StringBuffer(query.length() + 8).append(query).insert(
					getSqlAfterSelectInsertPoint(query), " top " + limit)
					.toString();
		}

		return getLimitString(query, offset > 0);
	}
	
	public String getLimitString(String sql, boolean hasOffset) {
		int orderByIndex = sql.toLowerCase().lastIndexOf("order by");

		if (orderByIndex <= 0) {
			throw new UnsupportedOperationException(
					"must specify 'order by' statement to support limit operation with offset in sql server 2005");
		}

		String sqlOrderBy = sql.substring(orderByIndex + 8);

		String sqlRemoveOrderBy = sql.substring(0, orderByIndex);

		int insertPoint = getSqlAfterSelectInsertPoint(sql);
		return new StringBuffer(sql.length() + 100)
				.append("with tempPagination as(")
				.append(sqlRemoveOrderBy)
				.insert(
						insertPoint + 23,
						" ROW_NUMBER() OVER(ORDER BY " + sqlOrderBy
								+ ") as RowNumber,")
				.append(
						") select * from tempPagination where RowNumber>?  and RowNumber<=?")
				.toString();
	}
}

函数：

public class BitAndFunction implements SQLFunction {
	public Type getReturnType(Type type, Mapping mapping) {
		return Hibernate.INTEGER;
	}

	public boolean hasArguments() {
		return true;
	}

	public boolean hasParenthesesIfNoArguments() {
		return true;
	}

	public String render(List args, SessionFactoryImplementor factory)
			throws QueryException {
		if (args.size() != 2) {
			throw new IllegalArgumentException(
					"BitAndFunction requires 2 arguments!");
		}
		return args.get(0).toString() + " & " + args.get(1).toString();
	}
}

public class BitOrFunction implements SQLFunction {
	public Type getReturnType(Type type, Mapping mapping) {
		return Hibernate.INTEGER;
	}
	public boolean hasArguments() {
		return true;
	}
	public boolean hasParenthesesIfNoArguments() {
		return true;
	}
	public String render(List args, SessionFactoryImplementor factory)
			throws QueryException {
		if (args.size() != 2) {
			throw new IllegalArgumentException(
					"BitOrFunction requires 2 arguments!");
		}
		return args.get(0).toString() + " | " + args.get(1).toString();
	}
}

public Type getReturnType(Type type, Mapping mapping) {
		return Hibernate.INTEGER;
	}
	public boolean hasArguments() {
		return true;
	}
	public boolean hasParenthesesIfNoArguments() {
		return true;
	}
	public String render(List args, SessionFactoryImplementor factory)
			throws QueryException {
		if (args.size() != 2) {
			throw new IllegalArgumentException(
					"BitXorFunction requires 2 arguments!");
		}
		return args.get(0).toString() + " ^ " + args.get(1).toString();
	}

打包测试，一切正常。第一页采用top分页，以后采用ROW_NUMBER分页第一页以上查询要求sql中必须含有排序子句。
这只是在项目过程中采用的sql server遇到的该问题，如果使用mysql，oracle则不会遇到这个问题。
至此，项目中第一个问题解决！

评论

48 楼 yangguo 2012-09-03  

还不错，但是实在不想每条语句都去冗余增加个order by。楼主有办法解决吗。

47 楼 beautyOfProgram 2011-01-13  

遇到的同样的问题，不过我看我的Hibernate 版本是 3.0 看样子 先升级看看

46 楼 lai555 2010-12-22  

SQL2000不知楼主能否提供下解决方案

45 楼 银色幻想 2010-12-21  

mssqlserver 是从 sybase 偷学的技术

44 楼 zjriso 2010-12-07  

LZ 认识蛮深刻的！

43 楼 wenxiang_tune 2010-12-04  

我使用的分页主要是用JS控制的，jsp页面拿到结果集然后JS控制分页，对于一些实时性要求不高的场景比较适合，hibernate的自带分页我觉得还是很实用的，其实我想为了效率可以缓存结果集再根据本身需要调整结果数，毕竟，一个分页不一定适合每一个场景。我的分页过2天分享到博客

42 楼 cuishen 2010-11-19  

row_number() over 分页方式应对小数据量（如几千，几万）分页还行，到十万级以上可能就会有性能瓶颈了！

41 楼 myreligion 2010-10-28  

楼主看下这个帖子的分页算法是否更全面。

http://webeasymail.iteye.com/blog/247881

40 楼 hcjhuanghe 2010-10-28  

mark一下，有时间仔细学习一下

39 楼 scwwc55555 2010-10-22  

不是很懂。。。

38 楼 jordan_micle 2010-10-18  

还是mysql好，还是做小数据量的项目好

37 楼 jqyp324 2010-09-07  

我们也是这么封装的

36 楼 my_corner 2010-08-27  

Ashfrog 写道

昨日我恰好用了hibernate对sql server分页···
结果是对的···
但是生成的sql看不懂···
拿到查询分析器里面运行···
也和实际效果不一样···
简直不懂了···

没太明白。你是说我的代码不对？

35 楼 Ashfrog 2010-08-18  

昨日我恰好用了hibernate对sql server分页···
结果是对的···
但是生成的sql看不懂···
拿到查询分析器里面运行···
也和实际效果不一样···
简直不懂了···

34 楼 icefishc 2010-08-17  

my_corner 写道

sckiller 写道

请问sybase１５的分页是不是可以仿照第一页ms sql的方法 top 实现?

sysbase15是否可以嵌套使用top,这个还真不太清楚

不行

33 楼 my_corner 2010-08-17  

sckiller 写道

请问sybase１５的分页是不是可以仿照第一页ms sql的方法 top 实现?

sysbase15是否可以嵌套使用top,这个还真不太清楚

32 楼 sckiller 2010-08-13  

请问sybase１５的分页是不是可以仿照第一页ms sql的方法 top 实现?

31 楼 my_corner 2010-07-29  

分离的北极熊 写道

你别管HIBERNATE 是如何实现的，如果自己写SQL的话，把分页语句实现……

除了LS有说到的 row_number() over外，不用top，LZ可以尝试写一个分页来体验一下

ls是有说过用 rownumber分页的，而且我也已经写在方言里了。我也没说分页不能用top，我是说hibernate里的top分页是不对的。
下次请您看清帖子的内容，再回复，谢谢！

30 楼 my_corner 2010-07-29  

分离的北极熊 写道

LZ好像对SQL SERVER 的分页语句不是很熟悉啊

mysql 是 limit
Oracle 是你文中所描述的 3层嵌套查询
而sql server 从久远的历史来看，就是用top来分页的， 你所看到的东西没什么不对的地方

兄弟，sql server 2000只能top 分页不假，可是hibernate里的sql server是错误的。并不是top的嵌套查询，只是简单的top查询。然后在内存中进行的分页。你可以自己写个例子，打开sql语句验证一下。楼上有位同学也遇到过这个问题，自己去试试吧。

29 楼 my_corner 2010-07-29  

zeroxy 写道

我记得Hibernate对Mssql的分页是这样处理的。比如说每页10条，取第10页。

select top 10
from (select Top 100 from Table_A order by id) a
order by a.id desc

确实是只用了top，但也不是全取出来。

我明白你的意思，不过你的写错了。
select top 10 from (select Top 100 from Table_A order by id asc) a order by a.id desc

我不知道你的hibernate是什么版本的，我的项目里确实是只有一条top，你可以打开sql 语句看看。每次翻页都是类似这种语句：select top 10000 id from table
是把说有数据载入内存，进行假分页。