Explore mysql paging performance and mysql Paging

Explore mysql paging performance and mysql Paging

Several common paging methods:

1. Escalator Mode

The escalator mode generally only provides the previous or next page mode in navigation. Some products do not even provide the previous page function, but only provide the "more/more" mode, there are also more ways to automatically load pull-down, which can be summarized as escalator in technology.
The escalator method is relatively simple and efficient in technical implementation. You can obtain a page based on the offset of the last line on the current page. Writing SQL statements may be similar

SELECT*FROMLIST_TABLEWHEREid> offset_id LIMIT n;

1. Elevator Mode

Another data acquisition method is available on the product for accurate page turning, such as 1, 2, 3 ...... N. You can also enter n pages in the navigation bar. Most scenarios in China adopt the elevator mode, but the technical implementation of the elevator mode is relatively costly.

In MySQL, the B-tree mentioned in general is generally B + tree in the implementation of the storage engine.

When using the elevator mode, when the user specifies to flip to page n, it does not directly address the location, but needs to count one by one on the first floor and scan to count * page, the data is actually retrieved, so the efficiency is not high.

Traditional paging technology (ELEVATOR Mode)

First, the front-end needs to pass your paging object and query Conditions

// Paginated object 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 pages to the front-end:

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

The results obtained from the preceding two SQL statements must be returned to the front-end paging entities and single-page result sets.

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

In traditional query methods, only the pageIndex value is changed for each request, that is, the offset of limit offset and num.

Such as limit ;.... Limit10000, 10;

The preceding changes may lead to deviations in the execution time of each query. The larger the offset value, the longer the query time. For example, if the value is limit10000, 10 must read 10010 pieces of data to obtain the expected 10 pieces of data.

Optimization Method

In the traditional method, we learned that the key to affecting efficiency is that the program traverses a lot of unnecessary data. If we find the key point, we should start from here.

If there is no need to use the elevator, we can use the escalator to improve performance.

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

Optimization Based on traditional methods

The optimization method mentioned above is either difficult to meet the user's needs or is too complicated to implement. If the data volume is not particularly large, there are hundreds of thousands of data entries, in fact, there is no need to use the above optimization method.

Traditional methods are enough, but they may need to be optimized. For example:

Orderby Optimization

SELECT*FROMpa_dc_flowORDERBYsubject_codeDESCLIMIT100000,5

The ORDERBY keyword is used in this statement, so sorting what is very important. If you sort the auto-increment id, this statement does not need to be optimized, if it is an index or even a non-index, it needs to be optimized.

First, make sure it is an index, otherwise it will be very slow. Then, if it is an index, but it is not as orderly as the auto-increment id, it should be rewritten into the following statement.

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

The following is an explanation of the two SQL statements.

From the figure, we can see that the second SQL can reduce the number of pages.

In fact, this involves order by optimization. The subject_code index is not used in the first SQL statement. If you change to select subject_code... The index is used. The following is the optimization of order.

If you want to index the field after order by, you must create a composite index with a field in the where condition !! In other words, if the field after orcerby needs to be sorted by index, it must either establish a composite index with the field in the where condition [when a composite index is created here, note that the column order of the composite index is (where field, order by field), so that the leftmost column principle can be met, probably because the order by field can be included in the where query condition! ], Or it must be referenced in the where condition!

Table asubject_code is a common field with an index built on it. id is the auto-incrementing primary key.

Select * fromaorderbysubject_code // you cannot use the index selectidfromaorderbysubject_code // you can use the index select * fromawheresubject_code = XX orderbysubject_code

This means that order by should not be sorted by the file system. Either the order by field appears after select or the order by field appears in the where condition, you can create a composite index between the order by field and the where condition field!

The second SQL statement is clever use of the second method to use the index. Select id from a order bysubject_code, this method

Count Optimization

When the data volume is very large, the approximate data of the total number can be output. Using the explain statement, he does not actually execute the SQL statement, but estimates it.

Summary

The above is a small series of mysql paging performance exploration, I hope to help you, if you have any questions, please leave a message, the small series will reply to you in a timely manner. Thank you very much for your support for the help House website!