如何在mysql中查询按日期排序的固定行数？

Question

我正在使用mysql查询数据库。我想要做的不是查询整个数据库，而是只查询按时间戳排序的最后1000行。

我已经尝试使用这个查询，它不工作，我想要的。我知道限制是用来返回固定数量的选定元素的。但这不是我想要的。我想查询一个固定数量的元素。

select * from mutable where name = 'myschema' ORDER BY start_time DESC LIMIT 1000;

有什么帮助吗？

Answer 1

tl;dr排序较少的Data

我猜您的mutable表有一个名为mutable.mutable_id的自动递增主键。

然后，您可以执行以下操作：

                 SELECT mutable_id 
                   FROM mutable 
                  WHERE name = 'myschema' 
                  ORDER BY start_time DESC 
                  LIMIT 1000;

它为您提供了所有相关行的ids的结果集。然后，ORDER BY ... LIMIT工作只需要对mutable_id和start_time值进行排序，而不是对整个表进行排序。因此，它在MySql服务器中占用的空间和时间更少。

然后使用该查询检索详细信息：

 SELECT *
   FROM mutable
  WHERE mutable_id IN (
                 SELECT mutable_id
                   FROM mutable 
                  WHERE name = 'myschema' 
                  ORDER BY start_time DESC 
                  LIMIT 1000
        )
  ORDER BY start_time DESC;

这将获取您需要的所有数据，而无需扫描和排序整个表。

如果在name和start_time上创建索引，子查询将更快:查询可以随机访问索引到适当的name，然后逐个扫描start_time条目，直到找到1000个。不需要排序；索引是预先排序的。

CREATE INDEX x_mutable_start_time ON mutable (name, start_time);

如果你使用的是MySQL 8，你可以创建一个降序索引，而且速度会更快。

CREATE INDEX x_mutable_start_time ON mutable (name, start_time DESC);

Answer 2

这只适用于auto_increment

诀窍是对较少的数据进行排序，就像O. Jones提到的那样。问题在于告诉MySQL怎么做。

MySQL无法知道“最后1000条记录”是什么，除非它根据查询对它们进行排序。这正是你想要避免的，所以你需要告诉MySQL如何找到“最后1000条记录”。

这个技巧包括告诉MySQL从哪个auto_increment开始查找数据。问题是您使用的是时间戳，所以我不确定这是否适合您的特定用例。

下面是查询：

SELECT * FROM mutable 
WHERE `name` = 'myschema' 
AND id > (SELECT MAX(id) - 1000 FROM mutable WHERE `name` = 'myschema') 
ORDER BY start_time DESC LIMIT 1000;

问题：

• auto_increments有差距。这些数字不是连续的，它们是唯一的，通过顺序递增算法计算。要获得更好的结果，请增加减法次数。您可能会得到1000个结果，但可能会得到500个结果-这取决于您的数据集
• 如果没有auto_increment，这是无用的
• 如果需要事先将插入的时间戳从大到低排序，这是无用的

优点：-主键用于定义取值范围(其中id > x)，因此数据集缩减将是最快的。