今天将一个oracle的数据库生成到了mySQL,因为代码比较原始,是JDBC访问数据库的,所以,对数据库的分页查询一下子就查不出来了。小忧伤( ⊙ o ⊙ )啊!
先看下之前查询的code:
public PageModel<user> findUserList(int pageNo,int pageSize) { StringBuffer sbSql=new StringBuffer(); sbSql.append("Select user_id,user_name,password,contact_tel,email,create_date ") .append("From") .append("(") .append("Select rownum rn,user_id,user_name,password,contact_tel,email,create_date ") .append("From") .append("(") .append("Select user_id,user_name,password,contact_tel,email,create_date from t_user where user_id <> 'root' ") .append(" order by user_id") .append(" )where rownum <=?") .append(") where rn>?"); Connection conn=null; PreparedStatement pstmt=null; ResultSet rs=null; PageModel<user> pageModel=null; try{ conn=DbUtil.getConnnection(); pstmt=conn.prepareStatement(sbSql.toString()); pstmt.setInt(1, pageNo*pageSize); pstmt.setInt(2, (pageNo-1)*pageSize); rs=pstmt.executeQuery(); List<user> userList=new ArrayList<user>(); while(rs.next()){ User user=new User(); user.setUserId(rs.getString("user_id")); user.setUserName(rs.getString("user_name")); user.setPassword(rs.getString("password")); user.setContactTel(rs.getString("contact_tel")); user.setEmail(rs.getString("email")); user.setCreateDate(rs.getTimestamp("create_date")); userList.add(user); } pageModel=new PageModel<user>(); pageModel.setList(userList); pageModel.setTotalRecords(getTotalRecords(conn)); pageModel.setPageNo(pageNo); pageModel.setPageSize(pageSize); }catch(SQLException e){ DbUtil.close(rs); DbUtil.close(pstmt); DbUtil.close(conn); } return pageModel; }</user></user></user></user></user>
基本上跟以前sql server数据库的rownum方式差不多。但是mysql这样子就不行了,要使用limit来进行分页。
先来看下我的表结构:
PS:我在user_id上面加了个索引。
然后,使用没有经过优化的limit进行查询:
#create INDEX rowindex on t_user(user_id) SELECT * from t_user ORDER BY USER_ID DESC limit 0,2
然后我们对此进行优化查询:
1,使用子查询方式进行优化查询
SELECT * FROM t_user WHERE USER_ID < =( SELECT USER_ID FROM t_user ORDER BY USER_ID DESC LIMIT ($page-1)*$pagesize.", 1), 1 ) ORDER BY USER_ID DESC LIMIT $pagesize 例如: SELECT * FROM t_user WHERE USER_ID < =( SELECT USER_ID FROM t_user ORDER BY USER_ID DESC LIMIT 3, 1 ) ORDER BY USER_ID DESC LIMIT 3
二,使用join方式进行优化
SELECT * FROM t_user AS u1 JOIN ( SELECT user_id FROM t_user ORDER BY USER_ID DESC LIMIT ($page-1)*$pagesize.", 1), 1 ) AS u2 示例: SELECT * FROM t_user AS u1 JOIN ( SELECT user_id FROM t_user ORDER BY USER_ID DESC LIMIT 0, 1 ) AS u2
三,对返回的数据总条数查询的优化
通常在代码里面,我要分页的话,需要返回的结果集中,包含数据总条数,这样我才能够根据当前的pageSize来在页面上显示数据一共有多少页。
而对这个数据总条数的查询,我们通常使用count(*) 或者count(0),然而在mysql里面,提供了内置的函数,来对这一查询进行优化:
SELECT SQL_CALC_FOUND_ROWS * from t_user where USER_ID<'root' limit 1; SELECT FOUND_ROWS(); #返回的第二个结果集为如果没有limit限制返回的条数