Mysql large table paging query page turning optimization solution

Mysql paging query first queries out all the data, then skips the offset and takes the limit records, which results in the longer the number of pages, the longer the query time

The general optimization idea is to convert the offset to make the offset as small as possible. It is best to query the first page every time, that is, the offset is 0

The query is sorted by id

1. If the query is sorted according to id, and the id is continuous

There are many online introductions to this, and the id can be calculated directly based on the number of pages to be checked. The range

For example, offset=40, limit=10, means querying the data on page 5, then the id starting from page 5 is 41, add query conditions: id>40 limit 10

2. If the query is sorted according to id, but the id is not continuous

Usually the number of page jumps will not be very large, then we can based on the above For the records of a query, calculate the new offset and limit corresponding to the next paging query, which is the offset

from the previous query record. Paging queries generally have two parameters: offset and limit. Limit is usually Fixed, assuming limit=10

In order to optimize the situation where the offset is too large, two additional parameters need to be provided for each query

Parameter lastEndId: The id of the last record of the previous query

Parameter lastEndOffset: The offset corresponding to the last record of the last query, which is the offset limit of the last query

1. The first case (with the second The two are actually the same): jump to the next page, add query conditions: id>lastEndId limit 10
2. Second case: Turn the page down, jump to any next page , calculate the new newOffset=offset-lastEndOffset,Add query conditions: id>lastEndId offset newOffset limit 10, but if newOffset is still very large, for example, jump directly from the first page Go to the last page. At this time, we can query according to the reverse order of the id (if the original id is in positive order, change it to reverse order, if it is reverse order, change it to positive order), and calculate the offset and limit corresponding to the reverse order query based on the total number, then newOffset = totalCount - offset - limit, query condition: id=totalCount, that is, calculated The newOffset may be less than 0, so the newOffset=0 of the last page, limit = totalCount - offset
3. The third case: page up, jump Go to any page above, reverse order according to id, newOffset = lastEndOffset- offset - limit-1, query condition: id

Three, If the query is sorted based on other fields, such as the generally used creation time (createTime)

This is the same as the second situation Almost the same, the difference is that createTime is not unique, so we cannot determine the creation time corresponding to the last record, which ones are from the next page, and which ones are from the previous page

At this time, add a request parameter lastEndCount: Indicates the creation time corresponding to the last record in the last query, and how many records are at the same time. This is based on the last data statistics

Added the newOffset calculated in the second caselastEndCount is the new offset. The other processing methods are the same as the second one.

java Example:

/**
	 * 如果是根据创建时间排序的分页，根据上一条记录的创建时间优化分布查询
	 * 
	 * @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;
	}<p> </p>
<p>Front-end js parameters, based on bootstrap table</p>
<pre class="brush:php;toolbar:false">    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++;
						}
					}
                }
                
            }
        });

For more MySQL-related technical articles, please visit the MySQL Tutorial column to learn!

The above is the detailed content of Mysql large table paging query page turning optimization solution. For more information, please follow other related articles on the PHP Chinese website!