優(yōu)化mysql大表分頁(yè)查詢翻頁(yè)的具體方案

下文我給大家簡(jiǎn)單講講關(guān)于優(yōu)化mysql大表分頁(yè)查詢翻頁(yè)的具體方案，大家之前了解過相關(guān)類似主題內(nèi)容嗎？感興趣的話就一起來看看這篇文章吧，相信看完優(yōu)化mysql大表分頁(yè)查詢翻頁(yè)的具體方案對(duì)大家多少有點(diǎn)幫助吧。

mysql分頁(yè)查詢是先查詢出來所有數(shù)據(jù)，然后跳過offset，取limit條記錄，造成了越往后的頁(yè)數(shù)，查詢時(shí)間越長(zhǎng)

一般優(yōu)化思路是轉(zhuǎn)換offset，讓offset盡可能的小，最好能每次查詢都是第一頁(yè)，也就是offset為0

查詢按id排序的情況

一、如果查詢是根據(jù)id排序的，并且id是連續(xù)的

這種網(wǎng)上介紹比較多，根據(jù)要查的頁(yè)數(shù)直接算出來id的范圍

比如offset=40, limit=10, 表示查詢第5頁(yè)數(shù)據(jù)，那么第5頁(yè)開始的id是41，增加查詢條件：id>40  limit 10

二、如果查詢是根據(jù)id排序的，但是id不是連續(xù)的

通常翻頁(yè)頁(yè)數(shù)跳轉(zhuǎn)都不會(huì)很大，那我們可以根據(jù)上一次查詢的記錄，算出來下一次分頁(yè)查詢對(duì)應(yīng)的新的 offset和 limit，也就是離上一次查詢記錄的offset

分頁(yè)查詢一般會(huì)有兩個(gè)參數(shù)：offset和limit，limit一般是固定，假設(shè)limit=10

那為了優(yōu)化offset太大的情況，每次查詢需要提供兩個(gè)額外的參數(shù)

參數(shù)lastEndId： 上一次查詢的最后一條記錄的id

參數(shù)lastEndOffset： 上一次查詢的最后一條記錄對(duì)應(yīng)的offset，也就是上一次查詢的offset+limit

1. 第一種情況（與第二種其實(shí)是一樣）：跳轉(zhuǎn)到下一頁(yè)，增加查詢條件：id>lastEndId limit 10
2. 第二種情況：往下翻頁(yè)，跳轉(zhuǎn)到下任意頁(yè)，算出新的newOffset=offset-lastEndOffset,增加查詢條件：id>lastEndId offset newOffset limit 10，但是如果newOffset也還是很大，比如，直接從第一頁(yè)跳轉(zhuǎn)到最后一頁(yè)，這時(shí)候我們可以根據(jù)id逆序（如果原來id是正序的換成倒序，如果是倒序就換成正序）查詢，根據(jù)總數(shù)量算出逆序查詢對(duì)應(yīng)的offset和limit，那么 newOffset = totalCount - offset - limit， 查詢條件：id<lastEndId offset newOffset limit 10 ,然后再通過代碼逆序，得到正確順序的數(shù)據(jù)，注意：最后一頁(yè) offset + limit>=totalCount ，也就是算出來的newOffset 可能小于0, 所以最后一頁(yè)的newOffset=0，limit = totalCount - offset
3. 第三種情況：往上翻頁(yè)，跳轉(zhuǎn)到上任意頁(yè)，根據(jù)id逆序 ,newOffset = lastEndOffset- offset - limit-1， 查詢條件：id<lastEndId offset newOffset limit 10 ,然后再通過代碼逆序，得到正確順序的數(shù)據(jù)

三，如果查詢是根據(jù)其他字段，比如一般使用的創(chuàng)建時(shí)間（createTime）排序

這種跟第二種情況差不多，區(qū)別是createTime不是唯一的，所以不能確定上一次最后一條記錄對(duì)應(yīng)的創(chuàng)建時(shí)間，哪些是下一頁(yè)的，哪些是上一頁(yè)的

這時(shí)候，增加一個(gè)請(qǐng)求參數(shù)lastEndCount：表示上一次查詢最后一條記錄對(duì)應(yīng)的創(chuàng)建時(shí)間，有多少條是這同一時(shí)間的，這個(gè)根據(jù)上一次的數(shù)據(jù)統(tǒng)計(jì)

根據(jù)第二種情況下計(jì)算出來的newOffset加上lastEndCount，就是新的offset，其他的處理方式和第二種一致

java 示例：

/**
	 * 如果是根據(jù)創(chuàng)建時(shí)間排序的分頁(yè)，根據(jù)上一條記錄的創(chuàng)建時(shí)間優(yōu)化分布查詢
	 * 
	 * @see 將會(huì)自動(dòng)添加createTime排序
	 * @param lastEndCreateTime
	 *            上一次查詢的最后一條記錄的創(chuàng)建時(shí)間
	 * @param lastEndCount 上一次查詢的時(shí)間為lastEndCreateTime的數(shù)量
	 * @param lastEndOffset  上一次查詢的最后一條記錄對(duì)應(yīng)的偏移量     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參數(shù)，基于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",//請(qǐng)求數(shù)據(jù)內(nèi)容格式 默認(rèn)是 application/json 自己根據(jù)格式自行服務(wù)端處理
            dataType:"json",
            dataField:"data",
            pagination: true,
            sidePagination: "server", // 服務(wù)端請(qǐng)求
            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);
            			//跳頁(yè)
            			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++;
						}
					}
                }
                
            }
        });

大家覺得優(yōu)化mysql大表分頁(yè)查詢翻頁(yè)的具體方案這篇文章怎么樣，是否有所收獲。如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。