mysqli :: multi_query是否比几个单个查询更有效? [英] Is mysqli::multi_query more efficient than several single queries?

问题描述

有人在一个答案中提出了MySQLi multi_query函数，声称它比循环3个单独的查询更好.我曾尝试向Google寻求某种答案，但并没有真正满足我的好奇心，因此我希望你们可能对使用它的原因有更好的见解，而不是节省几行代码.

这就是我想知道的:

1. multi_query在后台做什么?
2. multi_query是否只是简单地访问了服务器x次并汇总了结果?
3. 是否存在单个查询比多个查询更有效的情况?

我知道对数据库进行3次敲击，每次100万个条目并将其粉碎成一个大对象不利于内存使用，但是我知道它必须存在，并且我也肯定存在是应该避免的时候.我希望能更好地理解它，以便在有需要时可以把它放到我的小把戏中.

感谢您的时间！

解决方案

• What is multi_query doing under the hood?-只需一次将所有查询发送到服务器，而不是一次发送一个，然后一次性检索所有结果.没有比这更复杂的了.

• Does multi_query simply hit the server x number of times and aggregates the results?-两次命中"服务器-一次发送查询，一次检索结果.

• Is there a case where single queries may be more efficient than multiple queries?-取决于如何定义效率". multi_query()在网络上很轻，但内存很大，在循环中运行query()反之亦然.

  对于许多返回大型结果集的SELECT语句，内存消耗的损失可能远远超过网络方面的收益，在大多数情况下，您最好发出查询并处理结果集一次一个-尽管这完全取决于您对数据的处理方式.但是，如果您需要运行许多UPDATE语句，则multi_query()可能会更好，因为返回值仅是成功/失败，并且内存消耗将很小.

  您必须权衡所有因素，例如您在做什么，期望花费多长时间，(数据库)服务器和客户端之间的网络延迟，服务器上的可用资源(主要是内存)以及客户等)，并视情况而定.

我发现这一些性能测试的记录完成了之前的结论是，使用multi_query()可以提高整体效率. 但是该测试用例仅运行4个查询，每个查询SELECT产生单个结果，而更有效"的定义就是更快".没有针对更大数量的查询或更大结果集的测试，尽管速度很重要，但它并不是万能的，也并非万事大吉-如果我给它无限的内存，我可以使任何事情运行得非常快，但是尝试同时执行任何操作将失败.这也不是一个真实的测试，因为最终结果可以通过单个JOIN查询获得.确实，这确实使一些有趣的阅读.

我个人认为这有点学术性，因为如果您一次运行大量的语句，则90％的时间它们只会在传递的数据和查询结构上有所不同.将保持不变-这显然是准备好的语句的候选者.

Someone brought up the MySQLi multi_query function in an answer claiming that it would be better than looping through 3 separate queries. I tried to Google some sort of answer to this but didn't really get anything that really satisfied my curiosity so I hope you guys may have better insights into the reasons for using it rather than saving a few lines of code.

So here's what I am wondering:

1. What is multi_query doing under the hood?
2. Does multi_query simply hit the server x number of times and aggregates the results?
3. Is there a case where single queries may be more efficient than multiple queries?

I know that hitting the database 3 times for a million items each and smashing it into one huge object isn't good for memory usage, but I know that there has to be a reason for its existence and I am also sure there are times when it should be avoided. I hope to better understand it so that I can put that into my bag of tricks when the need arises.

Thanks for your time!

解决方案

• What is multi_query doing under the hood? - Just sending all the queries to the server at once instead of one at a time, and retrieving all the results in one go. Nothing more complicated than that.

• Does multi_query simply hit the server x number of times and aggregates the results? - It "hits" the server twice - once to send the queries and once to retrieve the results.

• Is there a case where single queries may be more efficient than multiple queries? - depends how you define "efficient". multi_query() is light on the network but memory heavy, running query() in a loop vice versa.

  For many SELECT statements that return large result sets the loss in memory consumption is likely to vastly outweigh the gain in terms of the network and most of the time you'd be better to issue the queries and process the result sets one at a time - although this depends on exactly what you are doing with the data. But if you needed to run many UPDATE statements, it is likely that multi_query() would be better since the return values are just success/fail and the memory consumption will be light.

  You would have to weigh up all the factors like what you are doing, how long you expect it to take, the network latency between the (database) server and client, the available resources (mostly memory) on the server and the client, etc, etc... and take it on a case by case basis.

I found this record of some performance testing done a while ago, where the conclusion is that there is an overall efficiency gain to be found from using multi_query(). However the test case was simply running 4 queries, each one SELECTing a single result, and the definition of "more efficient" is simply "faster". There is no testing for larger numbers of queries or larger result sets and, while speed is important, it is not the be-all and end-all - I can make anything run incredibly fast if I give it an unlimited amount of memory, but an attempt to do anything concurrently will fail miserably. It is also not a real world test, since the end result could be achieved with a single JOINed query. It does make for some interesting read though.

Personally I feel this is somewhat academic because if you are running a large set of statements at once, 90% of the time they will vary only in the data that is being passed and the query structure will remain the same - which is an obvious candidate for prepared statements.

这篇关于mysqli :: multi_query是否比几个单个查询更有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！