MySQL Big Data volume paging optimization

Suppose there is a tens of millions of tables, taking 1 to 10 data;

SELECT * FROM table limit 0,10;select * FROM table limit 1000, 10;

The two-statement query time should be completed in milliseconds;

SELECT * FROM table limit 3000000, 10;

You may not have thought that this statement was performed between 5s or so;

Why is the difference so big?

Maybe MySQL is not as smart as you think, for example, you want to query 10 data after 300w start, MySQL will read 300w plus 10 so much data, but filtered back to the last 10!!!

So if we solve this problem, here we summarize three common methods;

The first kind of simple rude, is not allowed to view such a data, such as Baidu is such

Up to 76 pages will not let you flip, this way is to solve from the business;

The second method, when querying the next page, passes the row ID of the previous page as a parameter to the client program, and then SQL changes to the

SELECT * FROM table where id>3000000 limit 10;

The execution of this statement is also done in milliseconds, id>300w actually let MySQL jump directly here, do not sequentially scan all the rows

If your table's primary key ID is self-increasing and there are no deletions and breakpoints in the middle, then there is another way, like 100 pages of 10 data

SELECT * FROM table where id>100*10 limit 10;

The last third method: deferred correlation

We are here to analyze why this statement is slow and slow.

SELECT * FROM table limit 3000000, 10;

The mystery is in this * inside, this table in addition to the ID key must have other fields such as name age, because select * so mysql along the ID of the primary key to go back row to take data, go for a bit of data;

If you change the statement to

Select ID from table limit 3000000, 10;

You'll find that time is cut in half, and then we take the ID to fetch 10 data separately;

The statement is changed to this:

Select table.* from table inner JOIN (SELECT ID from table limit 3000000,10) as TMP on Tmp.id=table.id;

These three methods first considered the second second, and the third one had no choice.

MySQL Big Data volume paging optimization