Shulou(Shulou.com)06/01 Report--

Below, I would like to tell you briefly about the specific plan to optimize the paging query of mysql large table. Have you known about similar topics before? If you are interested, let's take a look at this article. I believe it is more or less helpful for everyone to read the specific scheme of optimizing mysql paging query.

Mysql paging query is to query all the data first, then skip offset and take limit records, resulting in the later 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 have the first page of each query, that is, the offset is 0.

Query the sort by id

If the query is sorted by id and the id is continuous

There are many introductions on the Internet, and the range of id can be calculated directly according to the number of pages to be checked.

For example, offset=40, limit=10, means to query the data on page 5, then the id at the beginning of page 5 is 41, and the query condition is added: id > 40 limit 10

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

Usually the number of page turns is not very large, so we can calculate the new offset and limit corresponding to the next paging query based on the records of the previous query, that is, the offset from the previous query record.

Paging queries generally have two parameters: offset and limit,limit are generally fixed, assuming limit=10

In order to optimize the situation where the offset is too large, each query needs to provide two additional parameters.

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

Parameter lastEndOffset: the offset corresponding to the last record of the last query, that is, the offset+limit of the previous query

The first case (the same as the second): jump to the next page and add query conditions: id > lastEndId limit 10. Second case: turn the page down, jump to any page, calculate the new newOffset=offset-lastEndOffset, add the query condition: id > lastEndId offset newOffset limit 10, but if the newOffset is still very large, for example, jump directly from the first page to the last page, then we can reverse the order according to the id (if the original id is in reverse order If it is in reverse order, change to positive order) query, calculate the corresponding offset and limit according to the total number of queries, then newOffset= totalCount-offset-limit, query condition: id=totalCount, that is, the calculated newOffset may be less than 0, so the third case of the last page: newOffset=0,limit = totalCount-offset: turn up the page, jump to any page, according to id reverse order, newOffset= lastEndOffset- offset-limit-1, query condition: id= lastEndOffset If (isForward) {int calcOffset = offset-lastEndOffset + lastEndCount; int calcOffsetFormEnd = count-offset-limit; 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 list = dao.find (SelectBuilder.selectFrom (fromBuilder)) If (! isForward) {list.sort (new Comparator () {@ Override public int compare (To1, T2) {return o1.getCreateTime () .before (o2.getCreateTime ())? 1:-1 }});} page.setData (list); return page;}

Front-end js parameters, based on 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", / / the request data content format defaults to application/json 's own server processing according to the format: dataType: "json", dataField: "data", pagination: true SidePagination: "server", / / Server request 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) {/ / Refresh this.isRefresh = true; params.lastEndCreateTime = this.lastEndCreateTime; params.lastEndOffset = this.lastEndOffset Params.lastEndCount = this.lastEndCount;} else {console.log (this.currentEndCount); / / Page hopping 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.codekeeper 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; iTunes +) {var item = resp.data [I]; if (item.createTime = this.currentEndCreateTime) {this.currentEndCount++ })

What do you think of the specific plan to optimize the paging query of mysql large table? how about this article, whether there is anything to gain. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.