MySQL基本分頁查詢方法及其優化

MySQL基本分頁查詢方法及其優化

今天將一個oracle的數據庫生成到了mySQL,因為代碼比較原始，是JDBC訪問數據庫的，所以，對數據庫的分頁查詢一下子就查不出來了。小憂傷( ⊙ o ⊙ )啊！

先看下之前查詢的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限制返回的條數