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

先看下之前查询的code:

public PageModel 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 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 userList=new ArrayList();
	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();
	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 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返回的条数