sql - 使用GROUP BY执行SELECT的更好方法

Question

嗨，我写了一个有效的查询：

SELECT `comments`.* FROM `comments` 
RIGHT JOIN (SELECT MAX( id ) AS id, core_id, topic_id 
FROM comments GROUP BY core_id, topic_id order by id desc) comm 
ON comm.id = comments.id LIMIT 10

我想知道是否有可能（以及如何）重写它以获得更好的性能。
谢谢

Answer 1

方法1-改进原始查询
我确信，在这种情况下，INNER JOIN就足够了，没有理由做< cc>（如果RIGHT JOIN存在于id，它也将存在于comm）。commentss可导致better performance。
此外，您真的希望将INNER JOIN推入LIMIT 10（顺便说一下，将它与comm放在一起）：
首先，不把ORDER BY和LIMIT 10放在一起，就不会得到最近发布到主题中的10个主题（子查询的顺序不一定会保留到您要保存的最终结果中）
此外，在最里面的聚合子查询中执行ORDER BY将鼓励基于成本的优化器支持nested loops（确切地说，是10个）而不是hash或merge joins（对于任何大小适当的comm表，10个嵌套循环是最快的）
因此，您的查询应重写为：

SELECT `comments`.* FROM `comments` 
INNER JOIN (
 SELECT MAX( id ) AS id, core_id, topic_id 
 FROM comments
 GROUP BY core_id, topic_id
 ORDER BY id DESC
 LIMIT 10
) comm 
ON comm.id = comments.id
ORDER BY comments.id

最后，使用 LIMIT查看查询正在执行的操作。不要忘记检查是否已在 LIMIT上创建了索引，以帮助处理 comments嵌套循环。
方法2-另一种方法
请注意，尽管上面的查询仍然可能比原来的查询快，但是如果最里面的子查询导致表的完整扫描 EXPLAIN，那么它可能仍然是一个重要的瓶颈。这实际上取决于数据库在一起看到 comments.id、 JOIN和 comm时的智能程度。
如果 comments表示子查询正在执行表扫描，那么您可以尝试将SQL和应用程序级逻辑结合起来以获得最佳性能，前提是我正确理解了您的需求，并且您希望标识在十个不同主题中发布的十条最新注释：

# pseudo-code
core_topics_map = { }
command = "SELECT * FROM comments ORDER BY id DESC;"
command.execute
# iterate over the result set, betting that we will be able to break
#  early, bringing only a handful of rows over from the database server
while command.fetch_next_row do
  # have we identified our 10 most recent topics?
  if core_topics_map.size >= 10 then
    command.close
    break
  else
    core_topic_key = pair(command.field('core_id'), command.field('topic_id'))
    if not defined?(core_topics_map[core_topic_key]) then
      core_topics_map[core_topic_key] = command.field('id')
    end
  end
done
# sort our 10 topics in reverse chronological order
sort_by_values core_topics_map

在大多数情况下（也就是说，如果您的应用程序的数据库驱动程序在从 GROUP BY返回控制之前没有尝试将所有行缓冲到内存中），上面的操作将只获取少量行，始终使用索引，而不涉及表扫描。
方法3-混合方法
如果十秒钟前我知道最近十条评论是什么，那么当我以后再问这个问题时，我能不能聪明一点？除非可以从数据库中删除注释，否则答案是yes，因为我知道，当我再次提问时，所有注释ID都将大大大于或等于上次查询中获得的最旧注释ID。
因此，我可以使用一个附加条件来重写最里面的查询，使其更具选择性， ORDER BY：

SELECT `comments`.* FROM `comments` 
INNER JOIN (
 SELECT MAX( id ) AS id, core_id, topic_id 
 FROM comments
 WHERE id >= :last_lowest_id
 GROUP BY core_id, topic_id
 ORDER BY id DESC
 LIMIT 10
) comm 
ON comm.id = comments.id
ORDER BY comments.id

第一次运行查询时，使用 LIMIT表示 EXPLAIN。查询将按降序返回最多10行。在应用程序中，将最后一行的 execute放在一边，下次运行查询时将其值重用为 WHERE id >= :last_lowest_id，然后重复（再次，将最近一次查询返回的最后一行的 0放在一边等），这将基本上使查询递增，并且非常快。
例子：
在 :last_lowest_id设置为 id的情况下首次运行查询
返回10行ID: :last_lowest_id
保存 id
在 :last_lowest_id设置为 0的情况下第二次运行查询
返回10行ID: 129, 100, 99, 88, 83, 79, 78, 75, 73, 70
保存 70
等。
方法4-另一种方法
如果您希望在 :last_lowest_id表中执行 70的次数比 130, 129, 100, 99, 88, 83, 79, 78, 75, 73表中执行 73的次数多得多，请考虑在 SELECT ... ORDER BY id DESC LIMIT 10表中投入更多的工作以使 INSERT更快。因此，您可以在 commentsetc.表中添加一个索引的 INSERT列，每当您 SELECT表中有注释时，也可以考虑将相应主题的 updated_at值更新为 topics。然后，您可以很容易地选择最近更新的10个主题（返回10行的一个简单而简短的索引扫描），使用 INSERT表进行内部连接以获取这10个主题的 comments（在选择最大的10个主题之前（如在原始和方法1中）比为所有主题获取 updated_at的效率要高很多），然后再次在 NOW()获取这10个列的其余值。
我希望方法4的总体性能与方法2和3相当。如果需要获取任意主题（例如，通过分页， updated_at），或者如果可以删除主题或评论（无需更改即可支持主题删除；若要正确支持评论删除，则应使用最新未删除评论的 comments值在评论 MAX(id)和 MAX(id)上更新主题的 comments，则必须使用方法4主题）