Mysql uses limit paging when the amount of data is large. As the page number increases, the query efficiency becomes lower. This article shares with you the author's performance optimization method when using mysql to perform paging queries with tens of millions of data. It is a very good article and I hope it can help everyone.
Experiment
1. Directly use limit start, count paging statement:
select * from order limit start, count
When the start page is small, there is no performance problem with the query. Let's look at the execution time of paging starting from 10, 100, 1000, and 10000 (20 entries per page), as follows:
select * from order limit 10, 20 0.016秒 select * from order limit 100, 20 0.016秒 select * from order limit 1000, 20 0.047秒 select * from order limit 10000, 20 0.094秒
We have seen that as the start record increases, the time also increases. This shows that the paging statement limit is closely related to the start page number. Then we put the start record Change it to 40w and see
select * from order limit 400000, 20 3.229 seconds
Look at the time we took the last page of records
select * from order limit 800000, 20 37.44 seconds
Obviously this kind of time is intolerable.
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 limit statement of mysql is It is very convenient, but it is not suitable for direct use on tables with many records.
2. Performance optimization method for limit paging problem
Use the covering index of the table to speed up the paging query
We all know that if the statement that uses the index query only If that index column (covering index) is included, the query will be faster in this case.
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, there is also a related index cache in Mysql. It is better to use the cache when the concurrency is high.
In our example, we know that the id field is the primary key, and naturally includes the default primary key index. Now let's see how effective the query using the covering index is:
This time we query the data on the last page (using the covering index, only containing the id column), as follows:
select id from order limit 800000, 20 0.2 seconds
Compared to the 37.44 seconds of querying all columns, the speed is increased by about 100 times
So if we also want There are two ways to query all columns. One is in the form of id>=, and the other is to use join. Let’s look at 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 of writing
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.
Have you learned it? Hurry up and give it a try.
Related recommendations:
mysql tens of millions of data query
mysql tens of millions of count statistics comparison_MySQL
MySQL million-level paging optimization (Mysql million-level fast paging)_MySQL
The above is the detailed content of mysql tens of millions of data paging query performance optimization_Mysql. For more information, please follow other related articles on the PHP Chinese website!