使用单个联接查询而不是多个查询来提高性能? [英] Using a single joined query instead of multiple queries for performance?

问题描述

假设我有一个数据库，其中包含带有标签的博客文章，并且我想检索带有标签的博客文章.

Suppose I have a database which contains blog posts which have tags and I want to retrieve a blog post with its tags.

我可以做两个查询:

SELECT title, text, date 
  FROM POSTS 
 WHERE id = 1

和

SELECT tag 
  FROM TAGS 
 WHERE post_id = 1

但是我也可以在单个查询中做到这一点:

However I can also do it in a single query:

SELECT title, text, date, tag 
  FROM POSTS, TAGS 
 WHERE posts.id = 1 
  AND tags.post_id = posts.id

后者的使用有点浪费，因为它传输相同的标题，文本和日期列的次数是博客帖子具有的标签的次数的两倍，但它可能会更快，因为只有一次到服务器的行程.

The latter is a bit wasteful, because it transfers the same title, text and date columns as many times as many tags the blog post have, but it may be faster, because there is only one trip to the server.

后一种查询是否有其他选择，可以避免传输重复数据?还是没什么大不了的，我还是应该使用它，因为传输几百个未使用的额外字节比进行两个单独的查询便宜吗?

Is there some alternative for the latter query which avoids transferring duplicate data? Or is it not a big deal and I should use it anyway, because transferring a few hundred unused extra bytes is cheaper than making two separate queries?

推荐答案

MySQL优化并非如此简单.您会发现有时多个查询(可能在中间有一个临时表)比单个查询要快得多，尤其是在进行复杂的联接/聚合时.因此，不要以为单个查询总是会更快，因为它不会.

MySQL optimization isn't quite as straightforward as this. You'll find that sometimes multiple queries (possibly with a temp table in the middle) is much faster than a single query, especially with complex joins / aggregations going on. So, don't assume that a single query will always be faster, because it won't.

然而，单个查询通常比多个查询快或快，并且可以更简洁地表达您的工作.不必担心线路上的几个字节，与正在进行的其他所有操作相比，它们是微不足道的.

However, a single query is often just as fast or faster than multiple queries, and it expresses what you're doing much more succinctly. Don't worry about a handful of bytes across the wire, they are trivial in comparison to everything else that's going on.

这篇关于使用单个联接查询而不是多个查询来提高性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！