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

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大表分页查询翻页优化方案的详细内容，更多请关注名铺123其它相关文章！