MySQL 返回一个大查询还是几个较小的查询更好？

Is it better to return one big query or a few smaller ones?

提问by Mohamad

I'm using MySQL to store video game data. I have tables for titles, platforms, tags, badges, reviews, developers, publishers, etc...

我正在使用 MySQL 来存储视频游戏数据。我有标题、平台、标签、徽章、评论、开发人员、出版商等的表格......

When someone is viewing a game, is it best to have have one query that returns all the data associated with a game, or is it better to use several queries? Intuitively, since we have reviews, it seems pointless to include them in the same query since they'll need to be paginated. But there are other situations where I'm unsure if to break the query down or use two queries...

当有人观看游戏时，最好使用一个查询来返回与游戏相关的所有数据，还是使用多个查询更好？直观地说，由于我们有评论，将它们包含在同一个查询中似乎毫无意义，因为它们需要分页。但是在其他情况下，我不确定是要分解查询还是使用两个查询...

I'm a bit worried about performance since I'm now joining to games the following tables: developers, publishers, metatags, badges, titles, genres, subgenres, classifications... to grab game badges, (from games_badges; many-to-many to games table, and many to many to badges table) I can either do another join, or run a separate query.... and I'm unsure what is best....

我有点担心性能，因为我现在加入了以下表格的游戏：开发者、出版商、元标签、徽章、标题、流派、子流派、分类……以获取游戏徽章，（来自 games_badges；many-to - 多到游戏表，多到多到徽章表）我可以做另一个连接，或者运行一个单独的查询......我不确定什么是最好的......

采纳答案by zerkms

There is no panacea.

没有灵丹妙药。

1. Always try to get only necessary data.
2. There is no answer whether one big or several small queries is better. Each case is unique and to answer this question you should profile your application and examine queries' EXPLAINs

1. 始终尝试只获取必要的数据。
2. 没有答案是一个大查询还是几个小查询更好。每个案例都是独一无二的，要回答这个问题，您应该分析您的应用程序并检查查询EXPLAIN的

回答by Explosion Pills

It is significantly faster to use one query than to use multiple queries because the startup of a query and calculation of the query plan itself is costly and running multiple queries in a row slows the server more each time. Obviously you should only get the data that you actually need, but fewer queries is always better.

使用一个查询比使用多个查询要快得多，因为查询的启动和查询计划本身的计算成本很高，并且连续运行多个查询每次都会使服务器变慢。显然，您应该只获取您实际需要的数据，但查询越少越好。

So if you are going to show 20 games on a page, you can speed up the query (still using only one query) with a LIMIT clause and only run that query again later when they get to the next page. That or you can just make them wait for the query to complete and have all of the data there at once. One big wait or several little waits.

因此，如果您要在一个页面上显示 20 场比赛，您可以使用 LIMIT 子句加快查询速度（仍然只使用一个查询），并且仅在稍后到达下一页时再次运行该查询。或者您可以让他们等待查询完成并立即获得所有数据。一个大等待或几个小等待。

tl;dr use as few queries as possible.

tl;dr 使用尽可能少的查询。

回答by srcspider

This is generally a processing problem.

这通常是一个处理问题。

• If making one query would imply retrieving thousands of entries, call several queries to have MySQL do the processing (sums, etc.).
• If making multiple queries involves making tens or hundreds of them, then call a single query.

• 如果进行一个查询意味着检索数千个条目，请调用多个查询让 MySQL 进行处理（总和等）。
• 如果进行多个查询涉及进行数十个或数百个查询，则调用单个查询。

Obviously you're always facing both of these since neither is a goto option if you're asking the question, so the choices really are:

显然，你总是面临这两种情况，因为如果你问这个问题，这两个选项都不是转到选项，所以选择真的是：

1. Pick the one you can take the hit on
2. Cache or mitigate it as much as you can so that you take a hit very rarely
3. Try to insert preprocessed data in the database to help you process the current data
4. Do the processing as part of a cron and have the application only retrieve the data
5. Take a few steps back and explore other possible approaches that don't require the processing

1. 选择一个你可以接受的
2. 尽可能多地缓存或减轻它，以便您很少受到打击
3. 尝试在数据库中插入预处理过的数据，帮助你处理当前的数据
4. 作为 cron 的一部分进行处理并让应用程序只检索数据
5. 退后几步，探索其他不需要处理的可能方法