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

我自己也有同样的问题。鉴于您想要收集大量此类数据而不是一组特定的 30 个数据，您可能会运行一个循环并将偏移量增加 30。

所以你可以做的是：

持有一组数据的最后一个id(30) (eg lastId = 530) 添加条件WHERE id > lastId limit 0,30

所以你总是可以有一个零偏移。你会惊讶于性能的提升。

---

较高的偏移量会减慢查询速度是正常的，因为查询需要计算前 OFFSET + LIMIT 条记录（并且只占用其中 LIMIT 条记录）。该值越高，查询运行的时间越长。

查询不能直接到 OFFSET，因为首先，记录的长度可能不同，其次，删除的记录可能存在间隙。它需要在途中检查和计算每条记录。

假设 id 是 MyISAM 表的主键，或者是 InnoDB 表上的唯一非主键字段，您可以使用以下技巧加速它：

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 不能直接转到第 10000 条记录（或您建议的第 80000 个字节），因为它不能假设它是这样打包/排序的（或者它具有 1 到 10000 的连续值）。尽管实际上可能是这样，但 MySQL 不能假设没有漏洞/间隙/删除的 id。

因此，正如鲍勃所指出的，MySQL 必须先获取 10000 行（或遍历 id 上索引的第 10000 个条目），然后才能找到要返回的 30。

编辑：为了说明我的观点

请注意，虽然

SELECT * FROM large ORDER BY id LIMIT 10000, 30 

会很慢（呃），

SELECT * FROM large WHERE id >  10000 ORDER BY id LIMIT 30 

将是 fast(er)，并且会返回相同的结果，前提是没有丢失 id（即间隙）。

---

我发现了一个有趣的例子来优化 SELECT 查询 ORDER BY id LIMIT X,Y。我有 3500 万行，所以需要 2 分钟才能找到一系列行。

这是诀窍：

select id, name, address, phone
FROM customers
WHERE id > 990
ORDER BY id LIMIT 1000;

只需将 WHERE 与您获得的最后一个 id 放在一起，就可以大大提高性能。对我来说，从 2 分钟到 1 秒 :)

其他有趣的技巧：http://www.iheavy.com/2013/06/19/3-ways-to-optimize-for-paging-in-mysql/

它也适用于字符串

---

这两个查询的耗时部分是从表中检索行。从逻辑上讲，在 LIMIT 0, 30 版本中，只需要检索 30 行。在 LIMIT 10000, 30 版本中，评估 10000 行并返回 30 行。可以对我的数据读取过程进行一些优化，但请考虑以下几点：

如果查询中有 WHERE 子句怎么办？引擎必须返回所有符合条件的行，然后对数据进行排序，最后得到 30 行。

还要考虑在 ORDER BY 序列中未处理行的情况。必须对所有符合条件的行进行排序以确定要返回的行。

---

对于那些对比较和数字感兴趣的人:)

实验 1：数据集包含大约 1 亿行。每行包含几个 BIGINT、TINYINT 以及两个包含大约 1k 个字符的 TEXT 字段（故意）。

蓝色 := SELECT * FROM post ORDER BY id LIMIT {offset}, 5

橙色 := @Quassnoi 的方法。 SELECT t.* FROM (SELECT id FROM post ORDER BY id LIMIT {offset}, 5) AS q JOIN post t ON t.id = q.id

当然，第三种方法，... WHERE id>xxx LIMIT 0,5 在这里没有出现，因为它应该是常数时间。

实验2：类似的事情，只是一行只有3个BIGINT。

绿色 := 之前的蓝色

red := 之前的橙色

https://i.stack.imgur.com/bjxjA.png

---