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

第一种情况（与第二种其实是一样）：跳转到下一页，增加查询条件：id>lastEndId limit 10第二种情况：往下翻页，跳转到下任意页，算出新的newOffset=offset-lastEndOffset,增加查询条件：id>lastEndId offset newOffset limit 10，但是如果newOffset也还是很大，比如，直接从第一页跳转到最后一页，这时候我们可以根据id逆序（如果原来id是正序的换成倒序，如果是倒序就换成正序）查询，根据总数量算出逆序查询对应的offset和limit，那么 newOffset = totalCount - offset - limit， 查询条件：id=totalCount ，也就是算出来的newOffset 可能小于0, 所以最后一页的newOffset=0，limit = totalCount - offset第三种情况：往上翻页，跳转到上任意页，根据id逆序 ,newOffset = lastEndOffset- offset - limit-1， 查询条件：id= lastEndOffset;

if (isForward) {

int calcOffset = offset - lastEndOffset + lastEndCount;

int calcOffsetFormEnd = count - offset - limit;

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 list = dao.find(SelectBuilder.selectFrom(fromBuilder));

if (!isForward) {

list.sort(new Comparator() {

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

更多技巧请《转发 + 关注》哦！