Paging performance exploration-mysql, paging exploration-mysql

Paging performance exploration-mysql, paging exploration-mysql

Paging technology is very common in development. I used paging technology for the two projects I just joined the company, so I thought about in-depth exploration of paging technology.

Several common paging methods:

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 * FROM LIST_TABLE WHERE id > offset_id LIMIT n;

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 struct FinanceDcPage {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;

SELECT COUNT(*) FROM my_table WHERE x = y ORDER BY id;

Then return the specified number of pages to the front-end:

SELECT * FROM my_table WHERE x = y ORDER BY date_col LIMIT (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 struct FinanceDcPage {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 ;.... Limit, 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 limit is used, 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.

Here is an article about the optimization of the elevator mode. Because my project has not yet risen to the point where I want to optimize it, let's go directly to his approach.

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:

Order by optimization

SELECT * FROM pa_dc_flow ORDER BY subject_code DESC LIMIT 100000,5

The order by keyword is used in this statement, so it is very important to sort the ORDER. If you sort the auto-increment id, then 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 * FROM pa_dc_flow INNER JOIN (SELECT id FROM pa_dc_flow ORDER BY subject_code DESC LIMIT 100000,5) AS pa_dc_flow_id USING(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 orcer by 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!

TableSubject_code is a common field with an index built above. id is the auto-incrementing primary key.

Select * from a order by subject_code // you cannot use the index select id from a order by subject_code // you can use the select subject_code from a order by subject_code // you can use the select * from where subject_code = XX order by subject_code // can be indexed

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!

For details, see order by keyword optimization.

The second SQL statement is clever use of the second method to use the index. Select id from a order by subject_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.

19:27:34

Brave, Happy, Thanksgiving!