为什么把偏移量保存在mysql,为什么MYSQL更高的LIMIT偏移量会减慢查询速度？

Scenario in short: A table with more than 16 million records [2GB in size]. The higher LIMIT offset with SELECT, the slower the query becomes, when using ORDER BY *primary_key*

So

SELECT * FROM large ORDER BY `id` LIMIT 0, 30

takes far less than

SELECT * FROM large ORDER BY `id` LIMIT 10000, 30

That only orders 30 records and same eitherway. So it's not the overhead from ORDER BY.

Now when fetching the latest 30 rows it takes around 180 seconds. How can I optimize that simple query?

解决方案

It's normal that higher offsets slow the query down, since the query needs to count off the first OFFSET + LIMIT records (and take only LIMIT of them). The higher is this value, the longer the query runs.

The query cannot go right to OFFSET because, first, the records can be of different length, and, second, there can be gaps from deleted records. It needs to check and count each record on its way.

Assuming that id is a PRIMARY KEY of a MyISAM table, you can speed it up by using this trick:

SELECT t.*

FROM (

SELECT id

FROM mytable

ORDER BY

id

LIMIT 10000, 30

) q

JOIN mytable t

ON t.id = q.id

See this article: