关闭

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

标签: 分页优化mysql
1787人阅读 评论(13) 收藏 举报
分类:


        今天将一个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 <> 'root' ")
				.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;
	}

        基本上跟以前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限制返回的条数




     


    

 

2
2
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

mysql大数据量分页查询优化技巧

PS:我们知道mysql使用Limit实现了分页查询,在我们平常的Web开发中常用一些通用的分页查询jar包,比如说有名的中国开发者实现的java Pagehelper(Github地址https:/...
  • u011687186
  • u011687186
  • 2017-04-17 16:23
  • 1257

MySQL详解(19)----------海量数据分页查询优化

分页的具体讲解讲解请看http://blog.csdn.net/u011225629/article/details/46775947 查看代码打印1 SELECT * FROM table ORD...
  • u011225629
  • u011225629
  • 2015-07-06 16:19
  • 7153

MySQL大数据量分页查询方法及其优化

原文地址:http://www.cnblogs.com/geningchao/p/6649907.html ---方法1: 直接使用数据库提供的SQL语句 ---语句样式: MySQL中,可用如下...
  • yalishadaa
  • yalishadaa
  • 2017-06-04 20:50
  • 921

MySQL大数据量分页查询方法及其优化 ---方法1: 直接使用数据库提供的SQL语句 ---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N ---适

MySQL大数据量分页查询方法及其优化 ---方法1: 直接使用数据库提供的SQL语句 ---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LI...
  • Lxj12306
  • Lxj12306
  • 2017-12-04 09:42
  • 289

SSH分页查询方法

  • 2014-03-11 23:39
  • 9KB
  • 下载

mvc ligerGrid 分页查询方法

$(f_initGrid); var manager, GridList; function f_initGrid() { GridList =...
  • lybwwp
  • lybwwp
  • 2014-03-27 14:04
  • 4604

sqlserver三种分页查询方法

假设有表student,每页显示10条记录,查询第5页的内容。 第一种方法:select top 10 * from student where id not in ( --40是这么计算出来的:1...
  • cuiyaoqiang
  • cuiyaoqiang
  • 2016-03-15 17:07
  • 372

不同数据库中的分页查询方法

在实际应用中,往往能碰到分页的需求。虽然有不少组件可以实现面向对象的分页,但是都是利用了缓存。很多时候,这些方法是不可用的。原因很简单,在一个数据量非常大的应用中,缓存全部数据无论是对网络传输还是对内...
  • lihb018
  • lihb018
  • 2014-04-15 10:10
  • 477

项目中不可缺少的分页查询方法

分页查询,每个项目里必不可少的,封装好的方法在网上也是很多的,不管是B/S还是C/S项目,底层的方法写的都是大同小异的,所以为了提高效率,我们总是将最快的实现方法拿过来。         因为公司之前...
  • u010785685
  • u010785685
  • 2015-10-28 19:38
  • 864

C# 实现分页查询方法实例

namespace ConsoleApplication { /// /// 定义查询类 /// public class Query { ...
  • dannywj1371
  • dannywj1371
  • 2013-06-07 16:34
  • 7435
    个人资料
    • 访问:942877次
    • 积分:21511
    • 等级:
    • 排名:第400名
    • 原创:528篇
    • 转载:13篇
    • 译文:7篇
    • 评论:5167条
    dig coin