mysql大表分页查询翻页优化方案
![](/uploads/allimg/200930/5d11d55049f2f81312882.jpg)
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<lastEndId offset newOffset limit 10 ,然后再通过代码逆序,得到准确次序的数据,留意:最后一页 offset + limit>=totalCount ,也就是算出来的newOffset 大概小于0, 所以最后一页的newOffset=0,limit = totalCount - offset
- 第三种状况:往上翻页,跳转到上任意页,按照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相关技术文章,请拜访MySQL教程栏目停止学习!
以上就是mysql大表分页查询翻页优化方案的具体内容,更多请关注百分百源码网其它相关文章!