Paging Performance Explore-mysql

Paging technology is very common in the development, I just went to the company to do two projects are used in the paging technology, so think about the paging technology in-depth exploration.

There are several common paging methods:

1. Escalator mode

Escalator mode in the navigation usually only provide the previous page/next page of these two modes, some products do not even provide a previous page function, only provide a "more/more" way, there is a pull-down automatic loading more ways, technically can be summed up into the escalator way.
The escalator method is relatively simple and efficient in technology implementation, and a page can be obtained from the last offset of the current page. Written in SQL may be similar to

SELECT * FROM LIST_TABLE WHERE id > offset_id LIMIT n;

1. Elevator mode

Another way of data acquisition in the product is reflected in a precise page, such as 1,2,3......N, while in the navigation can also be entered directly by the user N page. Most of the domestic scene adopts the elevator method, but the elevator method is relatively expensive in the technology realization.

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

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

Traditional paging technology (elevator mode)

First the front end needs to be passed to your paging entity, and the query criteria

//分页实体struct FinanceDcPage{    1: i32 pageSize,                                    //页容量    2: i32 pageIndex,                                   //当前页索引}

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 page bars to the front end:

SELECT * FROM my_table WHERE x = y ORDER BY date_col LIMIT (pageIndex - 1) * pageSize, pageSize;

Results queried by the above two SQL statements need to be returned to the front end of the paging entity, as well as a single page result set

//分页实体struct FinanceDcPage{    1: i32 pageSize,                                    //页容量    2: i32 pageIndex,                                   //当前页索引    3: i32 pageTotal,                                   //总页数    4: i32 totalRecod,                                  //总条数}

Traditional Query method, each request changes only pageindex value, that is, the offset of limit offset,num

such as limit 0, 10; Limit 10, 10; .... limit 10000, 10;

The changes above will cause a deviation in the time each query executes, and the greater the offset value, the longer it will take, such as the limit 10000,10 need to read 10,010 data to get the 10 data you want.

Optimization method

In the traditional approach, we learned that the key to efficiency is that the program traverses a lot of unwanted data and finds the key points to start with.

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

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

Here is an article about the elevator mode optimization, because I do not rise to the project to do this optimization, so directly on his way.

1. Why is the page-turning technology of super-long list data complex
2. Why the page-turning technology of super-long list data is complex (II.)

Optimization based on the traditional way

The above mentioned optimization method, either difficult to meet the user's needs, or is too complex to achieve, so if the data volume is not particularly large, such as Balayin data, there is no need to use the above optimization methods.

Traditional methods are sufficient, but traditional methods may also 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 important to sort what, and if you are sorting the self-increment ID, then this statement does not need to be optimized, and if it is an index or even non-indexed, it needs to be optimized.

First you have to make sure it's indexed, or it's really slow. Then if he is an index, but it is not as orderly as the self-increment ID, then it should be written in 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);

Here are the EXPLAIN for two SQL

As we can see from the diagram, the second SQL can sweep a lot of pages less.

In fact, this involves an order by optimization problem, and the first SQL does not take advantage of the Subject_code index. If you change to select Subject_code ... The index is used. The following is an optimization of order by.

The field after order by, if you want to go through the index, you must establish a composite index with a field in the Where condition!! Or the field after Orcer by. If you want to go through the index sort, it is either with the field in the Where condition to create a composite index "when you create a composite index, you need to be aware that the column order of the composite index is (where field, order by field), so that the left column principle is met, The reason may be the order by field and can be counted in the Where query condition! ", or it should be referenced in the Where condition itself!

Table A Subject_code is a normal field with an index on it, and the ID is the self-increment primary key

select  * from  a order  by  subject_code//Unused index select  ID from  a order  by  subject_ Code//can use index select  subject_code from  a order  by  subject_code//Can be used on index select  * from  a  where  Subject_code = XX order  by  subject_ Code//Can be used on index   

This means that order by avoids using the file system sort, either by appearing the field of the order by in the Select, or by using the order by field in the Where condition, or by creating a composite index of the order by field and the Where Condition field!

See ORDER BY keyword optimization

The second SQL is a clever use of the second way to use the index. Select ID from a order by Subject_code, this way

Count optimization

When the amount of data is very large, you can actually output a total of approximate data, using the explain statement, he did not really go to execute SQL, but the estimate

2015-5-12 19:27:34

Brave,happy,thanksgiving!

Paging Performance Explore-mysql