Discussion on the realization of mysql using limit paging optimization scheme

Mysql limit Paging Statement Usage

Compared with Oracle, MS and SqlServer, mysql's paging method is simple and makes people want to cry.

--Grammar:

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

--Examples:

select * from table limit 5; -- Before returning 5 Row 

select * from table limit 0,5; -- Ibid. before return 5 Row 

select * from table limit 5,10; -- Return 6-15 Row 

How to Optimize limit

When the offset of a query statement offset is very large, such as select * from table limit 10000, 10, it is best not to use limit directly, but to obtain id of offset first, and then use limit size directly to obtain data. The effect will be much better.

Such as:

select * From customers Where customer_id >=(
select customer_id From customers Order By customer_id limit 10000,1
) limit 10;

1. Test the experiment

mysql paging directly uses limit start, count paging statements:

select * from product limit start, count

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

select * from product limit 10, 20 0.016 Seconds 
select * from product limit 100, 20 0.016 Seconds 
select * from product limit 1000, 20 0.047 Seconds 
select * from product limit 10000, 20 0.094 Seconds 

We've seen that as the starting record increases, so does the time, which shows that the paging statement limit has a lot to do with the starting page number,
Then let's change the starting record to 40w (that is, about 1.5% of the record)

select * from product limit 400000, 20 3.229 Seconds 

Look at the time when we took the last page of the record

select * from product limit 866613, 20 37.44 Seconds 

Pages like this have the largest page number. Obviously, this kind of time is intolerable.

We can also sum up two things:

The query time of the limit statement is proportional to the position of the starting record. The limit statement of mysql is very convenient, but it is not suitable for direct use of tables with many records.

2. Performance optimization method for limit paging problem

2.1 Speeding up paging queries with override indexes of tables

As we all know, statements that utilize index queries that contain only that index column (override index) can be queried quickly.

Because there is an optimization algorithm for index search, and the data is on the query index, there is no need to find the relevant data address, which saves a lot of time.

In addition, Mysql also has related index cache, so it is better to use cache when concurrency is high.

In our example, we know that the id field is the primary key, which naturally contains the default primary key index. Now let's see how the query using the override index works:
This time, we query the last page of data (using the override index, only containing the id column), as follows:

select id from product limit 866613, 20 

The query time is 0.2 seconds, which is about 100 times faster than the 37.44 seconds when all columns are queried.

So if we want to query all the columns as well, there are two ways.

id > The form of =:

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

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

Using join

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

The inquiry time is also very short, praise!

In fact, both of them use one principle, so the effect is similar.