快速/最佳实践刷新mysqli_multi_query() [英] Speed/best practice flushing mysqli_multi_query()

问题描述

当塞巴斯蒂安(Sebastien)表示他要断开连接&时，我感到很害怕.每次使用mysqli_multi_query() @ mysqli_multi_query可以执行UPDATE语句之间重新连接吗?，因为这似乎不是最佳做法.

I cringed when Sebastien stated he was disconnecting & reconnecting between each use of mysqli_multi_query() @ Can mysqli_multi_query do UPDATE statements? because it just didn't seem like best practice.

但是，Craig @ mysqli multi_query后跟查询断开连接和连接的速度更快在每次使用mysqli_multi_query()而不是使用mysqli_next_result()之间重新连接.

However, Craig @ mysqli multi_query followed by query stated in his case that it was faster to disconnect & reconnect between each use of mysqli_multi_query() than to employ mysqli_next_result().

我想问的是，当程序员应该选择新连接"还是下一个结果"方法时，是否有人有进一步的第一手知识或基准证据来建议一个近似的临界值"(基于查询量等).

I would like to ask if anyone has further first-hand knowledge or benchmark evidence to suggest an approximate "cutoff" (based on query volume or something) when a programmer should choose the "new connection" versus "next result" method.

我也很高兴听到与速度无关的任何/所有担忧.克雷格(Craig)使用连接功能对速度有影响吗?

I am also happy to hear any/all concerns not pertaining to speed. Does Craig's use of a connecting function have any bearing on speed?

Craig的while语句之间是否存在速度差异:

Is there a speed difference between Craig's while statement:

while ($mysqli->next_result()) {;}

-与-

我建议的一会儿声明:

while(mysqli_more_results($mysqli) && mysqli_next_result($mysqli));

-与-

在运行第一个multi_query之前，为每个预期的multi_query创建一个新的连接.我刚刚对此进行了测试，并且两个mysqli_multi_query()没有错误=不需要close():

creating a new connection for each expected multi_query, before running first multi_query. I just tested this, and the two mysqli_multi_query()s were error free = no close() needed:

$mysqli1=mysqli_connect("$host","$user","$pass","$db");
$mysqli2=mysqli_connect("$host","$user","$pass","$db");

-与-

在每个mysqli_multi_query()之间(如塞巴斯蒂安和克雷格)打开和关闭:

Opening and closing between each mysqli_multi_query() like Sebastien and Craig:

$mysqli = newSQL();
$mysqli->multi_query($multiUpdates);
$mysqli->close();

-与-

还有其他选择可以测试吗?

Anyone have another option to test against?

推荐答案

不是要责备而是要自我查询.您的代码运行时间取决于实际查询执行的时间.

It is not next_result() to blame but queries themselves. The time your code takes to run relies on the time actual queries take to perform.

尽管mysqli_multi_query()返回控制非常快，但是这并不意味着所有查询都在那个时候执行了.相反，在mysqli_multi_query()完成时，只有第一个查询.所有其他查询都在mysql端排队等待异步执行.

Although mysqli_multi_query() returns control quite fast, it doesn't mean that all queries got executed by that time. Quite contrary, by the time mysqli_multi_query() finished, only first query got executed. While all other queries are queued on the mysql side for the asynchronous execution.

由此您可能会得出结论，next_result()调用本身并不会添加任何超时-它只是在等待下一个查询完成.如果查询本身需要时间，那么next_result()也必须等待.

From this you may conclude that next_result() call doesn't add any timeout by itself - it's just waiting for the next query to finish. And if query itself takes time, then next_result() have to wait as well.

知道您已经可以选择哪种方法:如果您不关心结果，则可以关闭连接.但实际上，这只是在地毯下扫灰尘，将所有缓慢的查询留在原地.因此，最好将next_result()循环保留在适当的位置(特别是因为无论如何都必须检查错误/受影响的行/等等)，但要自己加快查询速度.

Knowing that you already may tell which way to choose: if you don't care for the results, you may just close the connection. But in fact, it'll be just sweeping dirt under the rug, leaving all the slow queries in place. So, it's better to keep next_result() loop in place (especially because you have to check for errors/affected rows/etc. anyway) but speed up the queries themselves.

因此，事实证明，要解决next_result()问题，您实际上必须解决常规的查询速度问题.因此，这里有一些建议:

So, it turns out that to solve the problem with next_result() you have to actually solve the regular problem of the query speed. So, here are some recommendations:

1. 对于选择查询，通常进行索引/解释分析，其他答案中已经对此进行了解释.
2. 对于DML查询(尤其是分批运行)，还有其他方法:

说到克雷格的情况，这非常类似于已知的innodb写速度问题.默认情况下，innodb引擎设置为非常谨慎的模式，在该模式下，直到引擎确保成功完成前一个写入后，才执行后续写入.因此，它使写入速度非常慢(大约只有10个查询/秒).常见的解决方法是一次完成所有写入操作.对于插入查询，有很多方法:

Speaking of Craig's case, it's quite much resembling the known problem of speed of innodb writes. By default, innodb engine is set up into very cautious mode, where no following write is performed until engine ensured that previous one were finished successfully. So, it makes writes awfully slow (something like only 10 queries/sec). The common workaround for this is to make all the writes at once. For insert queries there are plenty of methods:

• 您可以使用多个值插入语法
• 您可以使用LOAD DATA INFILE查询
• 您可以将所有查询包装在事务中.

尽管仅更新和删除事务仍然是可靠的方法.因此，作为一种通用解决方案，可以提供这种解决方法

While for updating and deleting only transaction remains reliable way. So, as a universal solution such a workaround can be offered

 $multiSQL = "BEGIN;{$multiSQL}COMMIT;";
 $mysqli->multi_query($multiSQL);
 while ($mysqli->next_result()) {/* check results here */}

如果在您的情况下不起作用/不适用，那么我建议为循环运行的单个查询更改mysqli_multi_query()，调查并优化速度，然后返回到multi_query.

If it doesn't work/inapplicable in your case, then I'd suggest to change mysqli_multi_query() for the single queries run in a loop, investigate and optimize the speed and then return to multi_query.

这篇关于快速/最佳实践刷新mysqli_multi_query()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！