jdbc 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

SELECT FOUND_ROWS(); #返回的第二个结果集为如果没有limit限制返回的条数