mysql大表分页查询翻页优化方案

【虚拟资源买卖网www.cnused.com】源码虚拟资源交易平台,免费开通VIP商户!
吆喝资源网官方客服QQ:787749153官方唯一QQ号码
文字广告位招租      好位置不等人!200元/月

mysql分页查询是先查询出来所有数据,然后跳过offset,取limit条记录,造成了越往后的页数,查询时间越长

一般优化思路是转换offset,让offset尽可能的小,最好能每次查询都是第一页,也就是offset为0

查询按id排序的情况

一、如果查询是根据id排序的,并且id是连续的

这种网上介绍比较多,根据要查的页数直接算出来id的范围

比如offset=40, limit=10, 表示查询第5页数据,那么第5页开始的id是41,增加查询条件:id>40 limit 10

二、如果查询是根据id排序的,但是id不是连续的

通常翻页页数跳转都不会很大,那我们可以根据上一次查询的记录,算出来下一次分页查询对应的新的 offset和 limit,也就是离上一次查询记录的offset

分页查询一般会有两个参数:offset和limit,limit一般是固定,假设limit=10

那为了优化offset太大的情况,每次查询需要提供两个额外的参数

参数lastEndId: 上一次查询的最后一条记录的id

参数lastEndOffset: 上一次查询的最后一条记录对应的offset,也就是上一次查询的offset+limit

  1. 第一种情况(与第二种其实是一样):跳转到下一页,增加查询条件:id>lastEndId limit 10
  2. 第二种情况:往下翻页,跳转到下任意页,算出新的newOffset=offset-lastEndOffset,增加查询条件:id>lastEndId offset newOffset limit 10,但是如果newOffset也还是很大,比如,直接从第一页跳转到最后一页,这时候我们可以根据id逆序(如果原来id是正序的换成倒序,如果是倒序就换成正序)查询,根据总数量算出逆序查询对应的offset和limit,那么 newOffset = totalCount - offset - limit, 查询条件:id<lastEndId offset newOffset limit 10 ,然后再通过代码逆序,得到正确顺序的数据,注意:最后一页 offset + limit>=totalCount ,也就是算出来的newOffset 可能小于0, 所以最后一页的newOffset=0,limit = totalCount - offset
  3. 第三种情况:往上翻页,跳转到上任意页,根据id逆序 ,newOffset = lastEndOffset- offset - limit-1, 查询条件:id<lastEndId offset newOffset limit 10 ,然后再通过代码逆序,得到正确顺序的数据

三,如果查询是根据其他字段,比如一般使用的创建时间(createTime)排序

这种跟第二种情况差不多,区别是createTime不是唯一的,所以不能确定上一次最后一条记录对应的创建时间,哪些是下一页的,哪些是上一页的

这时候,增加一个请求参数lastEndCount:表示上一次查询最后一条记录对应的创建时间,有多少条是这同一时间的,这个根据上一次的数据统计

根据第二种情况下计算出来的newOffset加上lastEndCount,就是新的offset,其他的处理方式和第二种一致

java 示例:

/** * 如果是根据创建时间排序的分页,根据上一条记录的创建时间优化分布查询 * * @see 将会自动添加createTime排序 * @param lastEndCreateTime * 上一次查询的最后一条记录的创建时间 * @param lastEndCount 上一次查询的时间为lastEndCreateTime的数量 * @param lastEndOffset 上一次查询的最后一条记录对应的偏移量 offset+limit **/	public Page<T> page(QueryBuilder queryBuilder, Date lastEndCreateTime, Integer lastEndCount, Integer lastEndOffset,	int offset, int limit) {	FromBuilder fromBuilder = queryBuilder.from(getModelClass());	Page<T> page = new Page<>();	int count = dao.count(fromBuilder);	page.setTotal(count);	if (count == 0) {	return page;	}	if (offset == 0 || lastEndCreateTime == null || lastEndCount == null || lastEndOffset == null) {	List<T> list = dao.find(	SelectBuilder.selectFrom(fromBuilder.offsetLimit(offset, limit).order().desc("createTime").end()));	page.setData(list);	return page;	}	boolean isForward = offset >= lastEndOffset;	if (isForward) {	int calcOffset = offset - lastEndOffset + lastEndCount;	int calcOffsetFormEnd = count - offset - limit;	if (calcOffsetFormEnd <= calcOffset) {	isForward = false;	if (calcOffsetFormEnd > 0) {	fromBuilder.order().asc("createTime").end().offsetLimit(calcOffsetFormEnd, limit);	} else {	fromBuilder.order().asc("createTime").end().offsetLimit(0, calcOffsetFormEnd + limit);	}	} else {	fromBuilder.where().andLe("createTime", lastEndCreateTime).end().order().desc("createTime").end()	.offsetLimit(calcOffset, limit);	}	} else {	fromBuilder.where().andGe("createTime", lastEndCreateTime).end().order().asc("createTime").end()	.offsetLimit(lastEndOffset - offset - limit - 1 + lastEndCount, limit);	}	List<T> list = dao.find(SelectBuilder.selectFrom(fromBuilder));	if (!isForward) {	list.sort(new Comparator<T>() {	@Override	public int compare(T o1, T o2) {	return o1.getCreateTime().before(o2.getCreateTime()) ? 1 : -1;	}	});	}	page.setData(list);	return page;	}

前端js参数,基于bootstrap table

 this.lastEndCreateTime = null; this.currentEndCreateTime = null; this.isRefresh = false; this.currentEndOffset = 0; this.lastEndOffset = 0; this.lastEndCount = 0; this.currentEndCount = 0; $("#" + this.tableId).bootstrapTable({ url: url, method: 'get', contentType: "application/x-www-form-urlencoded",//请求数据内容格式 默认是 application/json 自己根据格式自行服务端处理 dataType:"json", dataField:"data", pagination: true, sidePagination: "server", // 服务端请求 pageList: [10, 25, 50, 100, 200], search: true, showRefresh: true, toolbar: "#" + tableId + "Toolbar", iconSize: "outline", icons: { refresh: "icon fa-refresh", }, queryParams: function(params){	if(params.offset == 0){	this.currentEndOffset = params.offset + params.limit;	}else{	if(params.offset + params.limit==this.currentEndOffset){	//刷新	this.isRefresh = true;	params.lastEndCreateTime = this.lastEndCreateTime;	params.lastEndOffset = this.lastEndOffset;	params.lastEndCount = this.lastEndCount;	}else{	console.log(this.currentEndCount);	//跳页	this.isRefresh = false;	params.lastEndCreateTime = this.currentEndCreateTime;	params.lastEndOffset = this.currentEndOffset;	params.lastEndCount = this.currentEndCount;	this.lastEndOffset = this.currentEndOffset;	this.currentEndOffset = params.offset + params.limit;	console.log(params.lastEndOffset+","+params.lastEndCreateTime);	}	}	return params; }, onSearch: function (text) { this.keyword = text; }, onPostBody : onPostBody, onLoadSuccess: function (resp) {	if(resp.code!=0){	alertUtils.error(resp.msg);	} var data = resp.data; var dateLength = data.length; if(dateLength==0){	return; } if(!this.isRefresh){ this.lastEndCreateTime = this.currentEndCreateTime; this.currentEndCreateTime = data[data.length-1].createTime; this.lastEndCount = this.currentEndCount; this.currentEndCount = 0; for (var i = 0; i < resp.data.length; i++) {	var item = resp.data[i];	if(item.createTime === this.currentEndCreateTime){	this.currentEndCount++;	}	} } } });

更多MySQL相关技术文章,请访问MySQL教程栏目进行学习!

以上就是mysql大表分页查询翻页优化方案的详细内容,更多请关注吆喝资源网其它相关文章!

本文转自PHP中文网,吆喝网(http://yaoohe.com)搜集整理,如有疑问请联系本站客服!

本文由 吆喝资源网 作者:shijian 发表,其版权均为 吆喝资源网 所有,文章内容系作者个人观点,不代表 吆喝资源网 对观点赞同或支持。如需转载,请注明文章来源。

2

发表评论