mysqli::multi_query 是否比多个单个查询更有效?

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.

感谢您的时间！

推荐答案

• multi_query 到底在做什么? - 只需将所有查询一次发送到服务器，而不是一次发送一个，并一次性检索所有结果.没有比这更复杂的了.

  • 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.

    multi_query 是否只是简单地点击服务器 x 次并聚合结果? - 它点击"服务器两次 - 一次发送查询，一次检索结果.

    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.

    是否存在单个查询可能比多个查询更有效的情况? - 取决于您如何定义高效".multi_query() 在网络上很轻，但内存很重，在循环中运行 query() 反之亦然.

    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.

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

    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.

    我发现这个一些性能测试的记录做了一个不久前，结论是使用 multi_query() 可以提高整体效率.然而测试用例只是运行 4 个查询，每个查询SELECT一个结果，更高效"的定义就是更快".没有针对大量查询或更大结果集的测试，虽然速度很重要，但它并不是最重要的 - 如果我给它无限量的内存，我可以使任何东西运行得非常快，但是尝试同时做任何事情都会失败.这也不是真实世界的测试，因为最终结果可以通过单个 JOIN ed 查询来实现.不过，它确实会带来一些有趣的阅读.

    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.

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

    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.