Shulou(Shulou.com)06/01 Report--

This article mainly tells you how to optimize the performance of MySQL 10 million data paging query. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on how to optimize the performance of MySQL 10 million data paging query can bring you some practical help.

Experiment

1. Directly use limit start, count paging statements:

Select * from order limit start, count

When the starting page is small, there is no performance problem with the query. Let's take a look at the execution time of paging from 10,100,1000 and 10000 (20 entries 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 starting record increases, the time increases, which shows that the paging statement limit has a lot to do with the starting page number, so let's change the starting record to 40w.

Select * from order limit 400000, 20 3.229 seconds

Let's take a look at the time we took the last page of the record.

Select * from order limit 800000, 20 37.44 seconds

Obviously, this kind of time is unbearable.

We can also sum up two things:

1) the query time of the limit statement is proportional to the location of the starting record.

2) the limit statement of mysql is very convenient, but it is not suitable for tables with many records.

two。 A performance Optimization method for limit Paging problem

Use the overlay index of the table to speed up the paging query

We all know that if only that index column is included in a statement that takes advantage of an index query (overriding the index), then the query is fast.

Because there is an optimization algorithm for using the 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, so it is better to use caching 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 overlay index works:

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

Select id from order limit 800000, 20 0.2 seconds

It is about 100 times faster than the 37.44 seconds of querying all columns.

So if we also want to query all columns, there are two ways, one is in the form of id > =, and the other is to use join to 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, .

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.

How to optimize MySQL 10 million-level data paging query performance will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.