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

This article mainly gives you a brief talk about several common paging methods and performance of mysql. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on several common paging methods and performance of mysql can bring you some practical help.

Several common paging methods:

1. Escalator mode

The escalator mode usually provides only the previous / next page in navigation, and some products do not even provide the previous page function, but only a "more / more" mode, and there are also more ways to drop down and load automatically, which can be technically summarized as escalator mode.

The escalator method is relatively simple and efficient in technical implementation, and one page can be obtained back according to the offset of the last item on the current page. Written as SQL may be similar

SELECT*FROMLIST_TABLEWHEREid > offset_id LIMIT n

1. Elevator mode

Another way of data acquisition is reflected in the product in the way of accurate page flipping, such as 1pm 2pm 3. N, at the same time, the navigation can also be entered by the user directly to n pages. The elevator mode is used in most domestic scenes, but the elevator mode is relatively expensive in terms of technical implementation.

In MySQL, the commonly mentioned b-tree is usually b+tree in the implementation of the storage engine.

When using the elevator mode, when the user specifies to turn to page n, there is no direct way to address the location, but needs to count,scan from the first floor to count*page one by one, the data acquisition really begins, so the efficiency is not high.

Traditional paging technology (elevator mode)

First of all, the front end needs to pass the paging entity to you, as well as the query conditions

/ / paging entity structFinanceDcPage {1:i32 pageSize,// page capacity 2:i32 pageIndex,// current page index}

Then you need to return the total number of queries to the front end.

SELECTCOUNT (*) FROMmy_tableWHEREx= y ORDERBYid

Then return the specified number of page noodles to the front end:

SELECT*FROMmy_tableWHEREx= y ORDERBYdate_colLIMIT (pageIndex-1) * pageSize, pageSize

The results queried by the above two sql statements need to be returned to the front-end paging entity and the single-page result set

/ / paging entity structFinanceDcPage {1:i32 pageSize,// page capacity 2:i32 pageIndex,// current page index 3:i32 pageTotal,// total number of 4:i32 totalRecod,// entries}

In the traditional query method, only the pageIndex value, that is, the offset of limit offset,num, is changed with each request.

Such as limit 0pl 10; limit 10pm 10; … . Limit10000,10

The above changes will lead to a deviation in the execution time of each query. The higher the offset value, the longer the time it takes. For example, limit10000,10 needs to read 10010 pieces of data to get the desired 10 pieces of data.

Optimization method

In traditional methods, we have learned that the key to efficiency is that the program traverses a lot of unneeded data, and finds the key point, so start here.

If we don't have to use elevators, we can use escalators to improve performance.

But in most cases, the elevator form can better meet the needs of users, so we need to find another way to optimize the elevator form.

Optimization based on traditional way

The optimization methods mentioned above are either difficult to meet the needs of users or too complex to implement, so if the amount of data is not particularly large, such as more than a million pieces of data, it is not necessary to use the above optimization method at all.

The traditional method is enough, but it may also need to be optimized. For example:

Orderby optimization

SELECT*FROMpa_dc_flowORDERBYsubject_codeDESCLIMIT100000,5

ORDERBY keyword is used in this statement, so what to sort is very important, if you are sorting self-increasing id, then this statement does not need to be optimized, if it is an index or even non-index, it needs to be optimized.

First of all, you have to make sure that it is an index, otherwise it will be really slow. Then if it is an index, but it is not as orderly as the self-incrementing id, then it should be rewritten as the following statement.

SELECT*FROMpa_dc_flowINNERJOIN (SELECTidFROMpa_dc_flowORDERBYsubject_codeDESCLIMIT100000,5) ASpa_dc_flow_idUSING (id)

Here is the EXPLAIN for two sql

As we can see from the figure, the second sql can scan a lot of pages less.

In fact, this involves the optimization of order by, and the subject_code index is not used in the first sql. If you change it to select subject_code... The index is used. Here are the optimizations for order by.

If you want to index the field after order by, you must establish a composite index with a field in the where condition! In other words, if the field after orcerby is to be sorted by the index, it can either establish a composite index with the fields in the where condition [when establishing a composite index here, you need to note that the column order of the composite index is (where field, order by field), so that it can meet the leftmost column principle. The reason may be that the order by field can be counted in the where query condition! Either it itself should be referenced in the where condition!

Table asubject_code is an ordinary field with an index on it, and id is a self-increasing primary key.

Select*fromaorderbysubject_code// does not need an index selectidfromaorderbysubject_code// can use an index selectsubject_codefromaorderbysubject_code// can use an index select*fromawheresubject_code= XX orderbysubject_code// can use an index

This means that order by should avoid using file system sorting by either putting the order by field after the select, or using the order by field in the where condition, or building a composite index between the order by field and the where conditional field!

The second sql is the ingenious use of the second way to use the index. Select id from an order bysubject_code, this way

Count optimization

When the amount of data is very large, you can actually output the approximate data of the total. Using the declare statement, he does not actually execute the sql, but makes the estimate.

Several common mysql paging methods and performance will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.