解决hibernate对Sql Server分页慢的问题

时间：2023-03-08 23:38:00
解决hibernate对Sql Server分页慢的问题

一、hibernate分页 hibernate对MsSql的伪分页 
分页是web项目中比不可少的一个功能，数据量大的时候不能全部展示必然要用到分页技术。相信大家对hibernate中的分页都不陌生：

  1. public Query setMaxResults(int maxResults);
  2. public Query setFirstResult(int firstResult);

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

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

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

  1. StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
  2. if (hasOffset) {
  3. pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
  4. }
  5. else {
  6. pagingSelect.append("select * from ( ");
  7. }
  8. pagingSelect.append(sql);
  9. if (hasOffset) {
  10. pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
  11. }
  12. else {
  13. pagingSelect.append(" ) where rownum <= ?");
  14. }

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

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

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

  1. public class SQLServer2005Dialect extends SQLServerDialect {
  2. /**
  3. *
  4. * 是否需要绑定limit参数?
  5. *
  6. * 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数
  7. */
  8. private ThreadLocal<Boolean> supportsVariableLimit = new ThreadLocal<Boolean>();
  9. public SQLServer2005Dialect() {
  10. registerFunction("bitand", new BitAndFunction());
  11. registerFunction("bitxor", new BitXorFunction());
  12. registerFunction("bitor", new BitOrFunction());
  13. setSupportsVariableLimit(false);
  14. }
  15. /**
  16. *
  17. * <p>
  18. * 设置是否先绑定limit参数。
  19. * </p>
  20. *
  21. * @param first
  22. */
  23. private void setSupportsVariableLimit(boolean first) {
  24. this.supportsVariableLimit.set(Boolean.valueOf(first));
  25. }
  26. /**
  27. *
  28. * <p>
  29. * 获取sql中select子句位置。
  30. * </p>
  31. *
  32. * @param sql
  33. *
  34. * @return int
  35. */
  36. protected static int getSqlAfterSelectInsertPoint(String sql) {
  37. int selectIndex = sql.toLowerCase().indexOf("select");
  38. int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");
  39. return selectIndex + ((selectDistinctIndex == selectIndex) ? 15 : 6);
  40. }
  41. public boolean supportsLimitOffset() {
  42. return true;
  43. }
  44. /*
  45. * Hibernate在获得Limit String(已添加了limit子句)后，如果此方法返回true，
  46. *
  47. * 则会添加额外的参数值(ROW_NUMBER()范围)(策略可能是这样：有offset设置两个参数值，没有设置一个参数值)
  48. */
  49. public boolean supportsVariableLimit() {
  50. return ((Boolean) this.supportsVariableLimit.get()).booleanValue();
  51. }
  52. public boolean useMaxForLimit() {
  53. return true;
  54. }
  55. /**
  56. * 首页top，以后用ROW_NUMBER
  57. */
  58. public String getLimitString(String query, int offset, int limit) {
  59. setSupportsVariableLimit(offset > 0);
  60. if (offset == 0) {
  61. return new StringBuffer(query.length() + 8).append(query).insert(
  62. getSqlAfterSelectInsertPoint(query), " top " + limit)
  63. .toString();
  64. }
  65. return getLimitString(query, offset > 0);
  66. }
  67. public String getLimitString(String sql, boolean hasOffset) {
  68. int orderByIndex = sql.toLowerCase().lastIndexOf("order by");
  69. if (orderByIndex <= 0) {
  70. throw new UnsupportedOperationException(
  71. "must specify 'order by' statement to support limit operation with offset in sql server 2005");
  72. }
  73. String sqlOrderBy = sql.substring(orderByIndex + 8);
  74. String sqlRemoveOrderBy = sql.substring(0, orderByIndex);
  75. int insertPoint = getSqlAfterSelectInsertPoint(sql);
  76. return new StringBuffer(sql.length() + 100)
  77. .append("with tempPagination as(")
  78. .append(sqlRemoveOrderBy)
  79. .insert(
  80. insertPoint + 23,
  81. " ROW_NUMBER() OVER(ORDER BY " + sqlOrderBy
  82. + ") as RowNumber,")
  83. .append(
  84. ") select * from tempPagination where RowNumber>?  and RowNumber<=?")
  85. .toString();
  86. }
  87. }

最后在配置Hibernate的时候在 hibernate.cfg.xml 配置 dialect 使用自己的SQLServer2005Dialect类

<property name="dialect">
      com.common.SQLServer2005Dialect
</property>

或在与spring的集成配置文件中

<prop key="hibernate.dialect">com.common.SQLServer2005Dialect</prop>

在分页时，就可以达到很好的效率了。

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

相关文章