Optimizing the performance of paging query for MySQL with tens of millions of data

When the amount of data in MySQL is large, limit paging is used. As the page number increases, the query efficiency decreases.

experiment

1. Directly use the limit start, count paging statement:

select * from order limit start, count

When the starting page is small, the query has no performance issues. Let's look at the execution time of paging from 10, 100, 1000, and 10000 (20 records per page), as follows:

select * from order limit 10, 20 0.016 seconds select * from order limit 100, 20 0.016 seconds select * from order limit 1000, 20 0.047 seconds select * from order limit 10000, 20 0.094 seconds

We have seen that as the number of starting records increases, the time also increases. This shows that the paging statement limit is closely related to the starting page number. So let's change the starting record to 40w and see.

select * from order limit 400000, 20 3.229秒

Let's look at the time when we took the last page of records.

select * from order limit 800000, 20 37.44秒

Obviously this time is unbearable.

We can also conclude two things from this:

1) The query time of the limit statement is proportional to the position of the starting record

2) The MySQL limit statement is very convenient, but it is not suitable for direct use for tables with many records.

2. Performance optimization method for limit paging problem

Use the table's covering index to speed up paging queries

We all know that if the statement that uses an index query only contains that index column (covering index), the query will be very fast.

Because index search has an optimized algorithm and the data is on the query index, there is no need to look for the relevant data address, which saves a lot of time. In addition, MySQL also has related index cache, and the effect will be better if the cache is used when the concurrency is high.

In our example, we know that the id field is the primary key, so the default primary key index is included. Now let's see how the query using the covering index performs:

This time we query the data of the last page (using a covering index, which only contains the id column), as follows:

select id from order limit 800000, 20 0.2秒

Compared to the 37.44 seconds it takes to query all columns, the speed is increased by more than 100 times.

If we also want to query all columns, there are two ways, one is id>= form, the other is to use join, see the actual situation:

SELECT * FROM order WHERE ID > =(select id from order limit 800000, 1) limit 20

The query time is 0.2 seconds, which is a qualitative leap, haha

Another way to write

SELECT * FROM order a JOIN (select id from order limit 800000, 20) b ON a.ID = b.id

The query time is also very short