Deferred loading resolves paging performance problems caused by offset too large

When we show the contents of a list, it is inevitable that you will encounter paging problems, because the number of content in the list may be many, but the user can see the size of the interface is limited, it is not possible to display all the content of an interface, from the back end of a one-time too much data will cause additional pressure on the back end.

In general, the data stored in MySQL we have two ways of paging: id-based pagination and offset based paging.

As an example of the SQL I encountered in the actual production environment, the related fields are used in a, B, C ... for confidentiality issues. Replace.

SELECT
  *
from
  table_a use INDEX (index_a)
WHERE
  A = xxx and
  b = xxx and
  C in (XXX) Order
B Y
  D DESC
LIMIT
  33380, one

KEY ' index_a ' (' A ', ' B ', ' d ', ' C ')

When MySQL uses offset paging, it first goes to the Level two index (INDEX_A) to find the ID of the Offset+limit row record that satisfies the condition, and then finds the corresponding row record in the clustered index according to the ID, and takes out the Offset+limit row data. Finally lose the offset line, leaving only the limit line, so the efficiency is poor when the offset is very large, because too much unnecessary data is accessed in the clustered index.

The execution time for this statement is probably 8s, and the ID page is different from the above. ID paging is every time to the back-end request data, with the last page ID, so we go to MySQL fishing data when the where conditions add a id>last_id,limit 33380, 11 to limit 11, according to the ID to order_by. This can be less to the clustered index to get a lot of data. The above example can take less than 3w, only take the 11 required.

The limit for ID paging.
ID paging often requires compromises on the product, because if the data is based on the last ID of the previous page, we will not be able to jump to the specified page, such as skip to page 5th, etc., and use more buttons when loading the data.

If the above SQL service is changed to ID paging, you need to change the product paging design, the front-end may also need to adjust, the changes will be a little larger. There is a way to delay loading to solve the problem of paging performance caused by offset too large.

We split the SQL in the example above into two sentences:

1.
SELECT
  table_a.id
from
  table_a use INDEX (index_a)
WHERE
  A = xxx and
  B = xxx and
  C in (XXX)
  ORDER by D DESC
LIMIT
  33380;

2.
Select * from table_a where ID in (IDS)

Two query add up time is about 50ms, compared to the previous 8s, a lot faster, for the following reasons:

The first query goes to overwrite the index because all queries have columns in the Level two index (INDEX_A). Instead of accessing the data rows in the clustered index, the efficiency is very fast, we can get the IDs of the 11 records we need, and then the second SQL takes the data from the clustered index in the ID.