今天将一个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 ?); 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 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限制返回的条数