mysql - 使用 UNION ALL 和 ORDER BY 优化查询

Question

我有 3 个表(例如 a、b、c)，指示不同项目的事件(例如评论、点赞等)以及每个事件的时间。我本质上试图做一种新闻提要，首先显示最近的事件。我为所有三个表构建了一个 UNION ALL 将所有事件分组在一起，然后构建了一个 GROUP BY 以确保相同项目的事件不会显示两次并按时间 DESC 排序。此函数使用无限滚动，因此查询也必须能够适当移动。

我想知道是否有任何方法可以优化这个(每个表大约 500-900K 并且还在增长)。截断的代码如下所示。

SELECT time,item_id FROM (
   SELECT a.time AS time, a.item_id FROM a 
      UNION ALL 
   SELECT b.time AS time, b.item_id FROM b 
      UNION ALL 
   SELECT c.time AS time, c.item_id FROM c
) temp 
GROUP BY item_id 
ORDER BY time DESC 
LIMIT 10

Answer 1

您编写的查询将创建一个非常大的临时表。然后，您可以按该临时表中的列进行排序。您应该尝试限制每个表，也许像这样:

SELECT time,item_id FROM (
   SELECT a.time AS time, a.item_id FROM a LIMIT 10 ORDER BY time DESC 
      UNION ALL 
   SELECT b.time AS time, b.item_id FROM b LIMIT 10 ORDER BY time DESC 
      UNION ALL 
   SELECT c.time AS time, c.item_id FROM c LIMIT 10 ORDER BY time DESC 
) temp 
GROUP BY item_id 
ORDER BY time DESC 
LIMIT 10

您需要确保 time 在每个表上都有一个索引。

不过我不太喜欢这样做，因为可能很难准确地“滚动”结果。

当转到“下一页”时，您可能需要考虑添加 WHERE 子句，例如 WHERE a/b/c.item_id > num 而不是 LIMIT 偏移量，长度。这将有助于提高准确性。

编写查询时，您应该在查询前加上 EXPLAIN 前缀，以查看如何处理查询。这将使您更好地了解正在发生的情况:是否正在创建临时表？它有多大？使用什么索引？等等...

另一种方法可能是使用 MySQL trigger填充单个“feed”表。