为什么 MYSQL 较高的 LIMIT 偏移量会减慢查询速度?

2021-11-20 00:00:00 performance limit mysql sql-order-by

简而言之场景:一个包含超过 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 表的主键，或 InnoDB 表上唯一的非主键字段，您可以使用以下技巧加快速度:

Assuming that id is the primary key of a MyISAM table, or a unique non-primary key field on an InnoDB 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 ORDERBY/LIMIT 性能:延迟行查找

相关文章