Home >Database >Mysql Tutorial >MySQL基本分页查询方法及其优化_MySQL

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

WBOY
WBOYOriginal
2016-05-27 13:46:35987browse

今天将一个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 <> &#39;root&#39; ")
				.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<&#39;root&#39; limit 1;
	SELECT FOUND_ROWS();  #返回的第二个结果集为如果没有limit限制返回的条数
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn