下文我給大家簡單講講關于優化mysql大表分頁查詢翻頁的具體方案，大家之前了解過相關類似主題內容嗎？感興趣的話就一起來看看這篇文章吧，相信看完優化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
三，如果查詢是根據其他字段，比如一般使用的創建時間（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大表分頁查詢翻頁的具體方案這篇文章怎么樣，是否有所收獲。如果想要了解更多相關，可以繼續關注我們的行業資訊板塊。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
