为什么MYSQL更高的LIMIT偏移量会减慢查询速度? [英] Why does MYSQL higher LIMIT offset slow the query down?

问题描述

简而言之:具有超过1600万条记录[2GB大小]的表.当使用ORDER BY * primary_key *

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*

所以

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

获取的费用远远少于

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

那只能订购30条记录，而且无论如何都一样.因此，这不是ORDER BY的开销.
现在，当获取最新的30行时，大约需要180秒.如何优化该简单查询?

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?

推荐答案

较高的偏移量通常会使查询变慢，这是正常的，因为查询需要从第一个OFFSET + LIMIT个记录中扣除(并且只记录其中的LIMIT个) .该值越高，查询运行的时间越长.

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.

查询不能直接转到OFFSET，因为，首先，记录的长度可以不同，其次，删除的记录可能会有间隙.它需要按其方式检查和计数每条记录.

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.

假设id是MyISAM表的PRIMARY KEY，则可以使用以下技巧来加快速度:

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

查看本文:

• MySQL ORDER BY/LIMIT性能:后排查找

这篇关于为什么MYSQL更高的LIMIT偏移量会减慢查询速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！