In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.