MySQL paging performance exploration

Several common paging methods:

1. Escalator method

The escalator method usually only provides two modes of navigation: previous page/next page. Some products do not even provide the previous page function, but only provide a "more/more" method. There is also a pull-down method to automatically load more, which can all be technically summarized as the escalator method.
The escalator method is relatively simple and efficient in technical implementation. It only requires getting one page backward according to the offset of the last item on the current page. Written in SQL might be similar to

SELECT * FROM LIST_TABLE WHERE id > offset_id LIMIT n;

1. Elevator method

Another way to obtain data is to provide precise page turning in the product, such as 1, 2, 3...n. Users can also enter the number of pages directly in the navigation. Elevators are used in most scenarios in China, but the technical implementation cost of elevators is relatively high.

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

When using the elevator method, when the user specifies to turn to page n, there is no direct way to address the location. Instead, it is necessary to count one by one from the first floor, and scan to count*page before actually starting to obtain data, which leads to low efficiency.

Traditional paging technology (elevator method)

First, the front end needs to pass your paging entity and 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;

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

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

SELECT * FROM my_table WHEREx = y ORDER BY date_colLIMIT (pageIndex - 1) * pageSize, pageSize;

The results of 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 pages 4:i32 totalRecod, //total number of records}

In traditional query methods, only the pageIndex value, that is, the limit offset and the num offset, changes in each request.

Such as limit 0,10; limit 10,10; …. limit10000,10;

The above changes will cause the execution time of each query to deviate. The larger the offset value, the longer the time required. For example, if limit10000,10 is used, 10010 data items need to be read to obtain the desired 10 data items.

Optimization Methods

We know from traditional methods that the key to efficiency is that the program traverses a lot of unnecessary data. Once we find the key point, we start from there.

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 needs of users, so we need to find other ways to optimize the elevator form.

Optimization based on traditional methods

The optimization methods mentioned above are either difficult to meet user needs or too complicated to implement. Therefore, if the amount of data is not particularly large, such as millions of data, there is actually no need to use the above optimization methods.

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

OrderBy Optimization

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

This statement uses the ORDER BY keyword, so it is very important what is being sorted. If you are sorting the auto-incrementing ID, then this statement does not need to be optimized. If it is an index or even a non-index, then it needs to be optimized.

First of all, you have to make sure it is indexed, otherwise it will be really slow. Then if it is an index, but it is not ordered like the auto-increment id, then it should be rewritten as the following statement.

SELECT * FROM pa_dc_flow INNER JOIN (SELECT id FROM pa_dc_flow ORDER BY subject_code DESC LIMIT 100000, 5) A Spa_dc_flow_id USING (id);

Below is the EXPLAIN of two SQL statements

From the figure we can see that the second SQL can scan fewer pages.

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

If you want to use an index for 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 should either create a composite index with the field in the where condition [When creating a composite index here, it is necessary to pay attention to the column order of the composite index as (where field, order by field), so as to meet the left-most column principle. The reason may be that the order by field can be counted in the where query condition! ], or it itself must be referenced in the where condition!

Table asubject_code is a normal field with an index on it, and id is the auto-increment primary key

select * from a order by subject_code // index is not used select id from a order by subject_code // index can be used select subject_code from a order by subject_code // index can be used select * from a where subject_code = XX order by subject_code // index can be used

This means that order by should avoid using file system sorting. Either place the order by field after the select, or use the order by field in the where condition, or create a composite index for the order by field and the where condition field!

The second SQL statement cleverly uses the second method to utilize the index. select id from a order by subject_code, this method

Count optimization

When the amount of data is very large, you can actually output the approximate total data by using the explain statement. It does not actually execute the SQL, but makes an estimate.

Summarize

The above is the MySQL paging performance exploration introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!