優化mysql大表分頁查詢翻頁的具體方案

下文我給大家簡單講講關于優化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

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大表分頁查詢翻頁的具體方案這篇文章怎么樣，是否有所收獲。如果想要了解更多相關，可以繼續關注我們的行業資訊板塊。