MySQL根本分页查询方法及其优化

        今天将一个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;	}

        基本上跟以前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 u1JOIN (	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限制返回的条数

1楼liyuanjinglyj昨天 20:37

来，把这个文章帮我推一下http://blog.csdn.net/liyuanjinglyj/article/details/47281057