How to quickly query 10 million pieces of data in MySQL

Source of the article ： Learn through http://www.bdgxy.com/

General paging query

When we encounter big data query in our daily work , The first reaction is to use paging queries .

mysql Support limit Statement to select the specified number of data , and oracle have access to rownum To select

mysql The paging query statement is as follows ：

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

• The first parameter is used to specify the offset of the first return record line
• The second parameter specifies the maximum number of rows to return records
  • When the same offset , More data , The longer it takes
  • When the same amount of data , The greater the offset , The longer it takes

How to optimize

After the summary above , We can clearly see that when the offset is large , When there's a lot of data , There is still a lot of time to query , So we will optimize these two methods

Large offset

Adopt sub query method

We can locate the offset position first id, Then query the data

select * from test limit 1000000,10
select id from test limit 1000000,1
select * from test where id>=(select id from test limit 1000000,1)limit 10

? Through execution, we can find , The first one takes the most time , The third one is a little better than the first one , And subqueries use indexes faster .

But it only applies to id Incremental situation

use id Limiting mode

This method is more demanding ,id Must be continuously increasing , And it has to be calculated id The scope of the , And then use between,sql as follows ：

select * from test where id between 1000000 and 1000100 limit 100;
select * from test where id>=1000000 limit 100

The results are quick

here limit Yes, the number is limited , No offset is used

Optimize the problem of large amount of data

• The amount of data returned will also directly affect the speed
• Reduce unnecessary columns , Query efficiency will be significantly improved
  

This is about Mysql This is the article on how to quickly query 10 million pieces of data in , More about Mysql For quick query, please search rookie tutorial www.piaodoo.com Previous articles or continue to browse the relevant articles below. I hope you can support rookie tutorials in the future www.piaodoo.com！