MySQL根据特定顺序分页大数据

Question

早上好

我有一个包含几百万行的表，我需要查看按时间戳排序的数据。

当我尝试这样做时

SELECT * FROM table ORDER BY date DESC offset 0 LIMIT 200

MySQL 将对所有数据进行排序，然后以 200 行作为响应，这是一个性能问题。因为每次我想滚动页面时都订购所有东西是不明智的!

您对我们如何提高性能有任何想法吗？

Answer 1

首先你需要创建一个基于日期字段的索引。这允许按顺序检索行，而不必在每次发出请求时都对整个表进行排序。

其次，您对结果集的研究越深入，基于索引的分页就会变得越慢。举例说明:

• ORDER BY indexedcolumn LIMIT 0, 200 非常快，因为它只需要扫描索引的 200 行。

• ORDER BY indexedcolumn LIMIT 200, 200速度比较快，但是需要扫描索引的400行。

• ORDER BY indexedcolumn LIMIT 660000, 200 非常慢，因为它需要扫描索引的 660,200 行。

  注意:即便如此，这仍然比根本没有索引要快得多。

您可以通过几种不同的方式解决此问题。

1. 实现基于值的分页，这样您就可以根据上一页最后一个结果的值进行分页。例如:

   WHERE indexedcolumn>[lastval] ORDER BY indexedcolumn LIMIT 200 将 [lastval] 替换为当前页面最后一个结果的值。索引允许随机访问特定值，并从该值向前或向后进行。

2. 只允许用户查看前 X 行(例如 1000)。如果他们想要的值是第 2529 个值，这就不好了。

3. 想出一些合理的方式来分解您的大表，例如按首字母、年份等，这样用户就不必再遇到数百万行的整个结果集，而是需要向下钻取首先进入一个特定的子集，这将是一个更小的集合并且可以更快地排序。

如果您将 WHERE 和 ORDER BY 组合在一起，您需要在索引设计中反射(reflect)这一点，以使 MySQL 能够继续从索引中受益以进行排序。例如，如果您的查询是:

SELECT * FROM mytable WHERE year='2012' ORDER BY date LIMIT 0, 200

那么您的索引将需要按该顺序位于两列(年份、日期)上。

如果您的查询是:

SELECT * FROM mytable WHERE firstletter='P' ORDER BY date LIMIT 0, 200

那么您的索引将需要按该顺序位于两列(首字母、日期)上。

这个想法是，多列索引允许按任何列排序，只要您在条件中将前面的列指定为常量(单个值)即可。因此，如果您将 A 和 B 指定为 WHERE 条件中的常量，则 A、B、C、D 和 E 上的索引允许按 C 排序。 A 和 B 不能是范围。