刷新 mysqli_multi_query() 的速度/最佳实践

问题描述

当塞巴斯蒂安说他要断开连接时，我感到很害怕&在每次使用 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 语句:

a while statement that I'm suggesting:

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() 之间打开和关闭，如 Sebastien 和 Craig:

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?

推荐答案

责怪不是 next_result() 而是查询自己.代码运行所需的时间取决于执行实际查询所需的时间.

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 查询，尤其是批量运行，还有其他方式:

说到 Craig 的案例，它与已知的 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 *