什么是更快的，具有更多PHP的大型联接查询或具有较少PHP的多个小型选择? [英] What is faster, a big joined query with more PHP or multiple small selects with less PHP?

问题描述

我正在运行一个cron任务，该任务对MySQL服务器进行很多查询.最大的问题是服务器有时运行极慢.

I'm running a cron task which makes lots of queries to a MySQL server. The big issue is that the server runs extremely slowly sometimes.

我有一个比较大的查询，它们之间还有4个表连接在一起，还有4个较小的查询，它们带有natural join的内容也攻击了第一个表.引发这些查询之后，我将处理结果并使用PHP将它们分组.

I've got a relatively big query with 4 tables left joined between them, and 4 smaller queries with natural joins that also attack the first table. After throwing those queries, I then process the results and group them using PHP.

我正在计划以某种方式将这5个查询混合到一个大查询中，然后在需要时让PHP快速执行sort().

What I'm planning is to somehow mix those 5 queries into just one big query, and then letting PHP do some quick sort()s when I need to do so.

在过滤和排序方面，我还被告知MySQL查询的运行速度比PHP快，但是当谈论有7或8个左联接时，我对此感到非常担心.有关这些查询的其他一些规格(由于公司政策而无法复制):

I'm also told MySQL queries run faster than PHP in terms of filtering and sorting, but I'm reasonably worried about it when talking about having 7 or 8 left joins. Some more specs about these queries (which I can't copy because of company policies):

• 每个获取的行和字段将至少访问一次.
• 每个查询都基于一个主表和一些"wing"表运行.
• 每个查询使用相同的GROUP BY规则.
• 当前，PHP代码将一些辅助查询结果拆分为多个数组.如果使用大型查询，还应该按多个参数对结果进行排序.

• Every fetched row and field will be visited at least once.
• Every query runs based on a single main table, with some "wing" tables.
• Every query uses the same GROUP BY rule.
• Currently the PHP code splits some secondary queries results into multiple arrays. It should, if using a big query, also sort the results by multiple parameters.

因此，由于这些问题，并且可能是经验法则:

So, due to these problems, and maybe as a rule of thumb:

什么是更快的查询，一个包含更多PHP的大型联合查询或一个包含PHP较少的多个小型选择?

推荐答案

根据经验，查询越少越好.将查询传递给MySQL会产生开销，但是查询却很复杂.但是php在某些方面的速度出奇地快，并且如果您不使用索引进行排序(如果您有效地将几个查询的结果进行有效排序，这听起来可能)，则php中的排序性能可能相当甚至更好

As a rule of thumb, the less queries the better. There is an overhead for passing a query to MySQL, however complex the query is. However php is surprisingly fast for some things, and if you are not using indexes for a sort (which sounds possible if you are effectively sorting the results of several queries unioned together) the performance of a sort in php might well be comparable or even better.

最大的不同在于，您可以获取一个查询的结果，然后对第一个查询中的每个返回的行执行另一个查询.在这种情况下，很容易让查询数量很快消失而不会引起注意.在工作中，我发现了一个菜单生成脚本，该脚本具有一个查询以获取高级菜单项，然后对每个高级项进行另一个查询以获取子菜单项.可以很容易地将其重写为联接，但是令人惊讶的部分是性能差异，生成菜单所需的时间从0.2秒减少到0.002秒.

Where there is a big difference is where you get the results of one query and then perform another query for each returned row on the first query. It is quite easy in this situation for the number of queries to get out of hand rather rapidly without being noticed. At work I found a menu generation script that had one query to get the high level menu items and then another query for each high level item to get the child menu items. This was easily rewritten as a join, but the surprising part is the performance difference, with the time taken to generate the menu dropping from 0.2 seconds to 0.002 seconds.

但这是个案决定.我有一个要求要根据levenshtein计算得出的值返回一些值(基本上是2个字符串的不同程度的分数).使用mysql自定义函数，这是可能的，并大大减少了返回的行数，但速度很慢. php levenshtein函数的速度大大提高了，事实证明，返回数倍的行然后在php中处理它们以获得levenshtein值，然后删除不再需要的记录，效率更高.

But it is a case by case decision. I had a requirement to return some values based on a levenshtein calculated value (essentially a score of how different 2 strings are). Using a mysql custom function this was possible and greatly reduced the number of rows returned but was quite slow. The php levenshtein function is massively faster, and it proved to be more efficient to return several times as many rows and then process them in php to get the levenshtein value and then drop the no longer required records.

在您描述的情况下，我怀疑差异可能很小.看来您将只执行4个查询，而不是1个更复杂的查询.但是，如果没有看到表结构和查询(不幸的是您无法提供)，就很难确定.进行单个合理的复杂查询可能会很有效，但是在并非绝对必要的地方忽略排序，然后在php中执行排序(将带有用户定义比较的usort用于此操作很有用).

In the situation you describe I suspect the difference might be marginal. It would appear you will only be doing 4 queries rather than 1 more complex query. However without seeing the table structures and queries (which unfortunately you can't provide) it is difficult to be certain. It might well be efficient to do a single reasonable complex query but ignore the sorting where not strictly necessary, and then perform that in php (usort with a user defined comparison can be useful for this).

还有一个问题，就是复杂的查询更难以维护.尽管有很多人可以将php脚本固定在一起或可以理解简单的SQL查询，但是可以理解复杂的SQL查询的人数却非常少.

There is the further issue that a complex query is more difficult to maintain. While there are plenty of people who can nail a php script together or who can understand a simple SQL query, the number who can understand complex SQL queries is worryingly small.

这篇关于什么是更快的，具有更多PHP的大型联接查询或具有较少PHP的多个小型选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！