即使有索引，使用偏移量限制（“LIMIT 500000, 10”）仍然很慢吗？

Question

我有一张表，其中有一个int字段，我们称之为createTime。这张表由数百万条记录组成。现在我想运行以下查询：

select * from `table` order by `createTime` desc limit 500000, 10

我已经为 createTime 创建了索引，但查询运行极慢。原因是什么？我该如何改善它？

以下是EXPLAIN的内容：

id 1
select_type simple
table table
type index
possible_keys null
key createTime
key_len 4
ref null
rows 500010
extra

关于偏移量，当其较小时，运行速度更快。

- Sebastian Nowak

---

你尝试过使用EXPLAIN关键字来查看MySql正在做什么吗？例如，EXPLAIN select * from table order by createTime desc limit 500000, 10。顺便问一下，createTime是什么数据类型？ - dash

在创建索引之前，性能如何？没有偏移量的限制呢？有较小偏移量的限制呢？ - user166390

抱歉，我的意思是typical createTime列包含哪些值-给一些示例。另外，您是否在使用ISAM、InnoDB？ - dash

写成 select "createTime" from "table" order by "createTime" desc limit 500000, 10 与写成其他方式有什么不同吗？（抱歉，我不知道如何在评论中嵌入反引号。） - Neil

这是MyISAM，createTime包含PHP中time()函数的结果 - 因此是自1970年以来的秒数。我已更新问题以提供更多细节。 - Sebastian Nowak

Answer 1

一般规则：对于大表，避免使用OFFSET。

随着偏移量的增加，查询执行的时间逐渐增加，这可能意味着处理非常大的表需要极长的时间。原因是因为偏移量基于表中行的物理位置，而该位置未经过索引。因此，要查找偏移量为 x 的行，数据库引擎必须遍历从 0 到 x 的所有行。

通常的经验法则是“在 limit 子句中永远不要使用 offset”。对于小表，您可能不会注意到任何区别，但是对于超过一百万行的表，您将看到巨大的性能提升。

- Lightness Races in Orbit

---

我更倾向于“避免大的OFFSET”。 - ypercubeᵀᴹ

@ypercube：但是对于小表来说，大的“OFFSET”是可以接受的。 - Lightness Races in Orbit

你的意思是当OFFSET大于表格大小时吗？是的，我想是这样。虽然没有意义但没关系。 - ypercubeᵀᴹ

我的（也许过于敏感的）评论是因为我发现那篇文章的标题有点误导人。在大表中使用小偏移量和适当的索引可能是可以的。（下面的文本也同意这一点） - ypercubeᵀᴹ

@ypercube：我的（通常是机智的回应式）评论高兴地忽略了你并没有错的观点。 :) - Lightness Races in Orbit

@TomalakGeret'kal：在LIMIT中，你会使用什么代替OFFSET或OFFSET呢？ - Martin.

Answer 2

如果您有一个唯一的列，那么可以加快此过程。理想情况下，它应该是createTime本身。

SELECT "table".*
  FROM "table"
  INNER JOIN (
    SELECT "createTime"
      FROM "table"
      ORDER BY "createTime" DESC
      LIMIT 500000, 10
  ) AS "limit" ON "table"."createTime" = "limit"."createTime"

如果createTime不是唯一的，但您有另一列是唯一的，那么您可能需要在createTime和另一列上创建一个复合索引，以便使此查询运行更高效。

SELECT "table".*
  FROM "table"
  INNER JOIN (
    SELECT "createTime", "unique"
      FROM "table"
      ORDER BY "createTime" DESC
      LIMIT 500000, 10
  ) AS "limit" ON "table"."unique" = "limit"."unique"

- Neil

---

谢谢，但是这个查询仍然需要几秒钟才能运行。这对于一个网站来说是不可接受的。是否有任何更快的方式来基于 createTime 列实现分页？ - Sebastian Nowak

Answer 3

我认为索引不会改变任何事情。使用offset, limit意味着“读取偏移量+限制数据集并丢弃（偏移量数量）”。如果您真的想要对这样一个大表进行分页或类似操作，您应该使用一种可以在查询的WHERE部分中限制结果的方法。这些类型的查询将受益于适当的索引。

使用日期时间，一种解决方案可能是使用时间段来显示您的数据。例如，您可以为一周中的每一天显示链接，并构建您的查询，如“WHERE createDate > '2011-12-11' AND crateDate < '2011-12-12'。

- hacksteak25