mysql高效分页计算（mysql大表分页查询翻页优化方案）
mysql高效分页计算（mysql大表分页查询翻页优化方案）分页查询一般会有两个参数：offset和limit，limit一般是固定，假设limit=10通常翻页页数跳转都不会很大，那我们可以根据上一次查询的记录，算出来下一次分页查询对应的新的 offset和 limit，也就是离上一次查询记录的offset这种网上介绍比较多，根据要查的页数直接算出来id的范围比如offset=40 limit=10 表示查询第5页数据，那么第5页开始的id是41，增加查询条件：id>40 limit 10二、如果查询是根据id排序的，但是id不是连续的
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
- 第一种情况（与第二种其实是一样）：跳转到下一页，增加查询条件：id>lastEndId limit 10
- 第二种情况：往下翻页，跳转到下任意页，算出新的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
- 第三种情况：往上翻页，跳转到上任意页，根据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大表分页查询翻页优化方案的详细内容，更多请关注其它相关文章！
更多技巧请《转发 关注》哦！
