問(wèn)題描述

當(dāng)塞巴斯蒂安說(shuō)他要斷開(kāi)連接時(shí)，我感到很害怕&在每次使用 mysqli_multi_query() 之間重新連接@可以 mysqli_multi_query執(zhí)行 UPDATE 語(yǔ)句? 因?yàn)檫@似乎不是最佳實(shí)踐.

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 后跟查詢(xún) 在他的案例中說(shuō)明斷開(kāi)連接速度更快在每次使用 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().

我想問(wèn)一下，當(dāng)程序員應(yīng)該選擇新連接"還是下一個(gè)結(jié)果"方法時(shí)，是否有人有進(jìn)一步的第一手知識(shí)或基準(zhǔn)證據(jù)來(lái)建議一個(gè)近似的截止"(基于查詢(xún)量或其他東西).

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.

我也很高興聽(tīng)到任何/所有與速度無(wú)關(guān)的問(wèn)題.Craig 使用連接函數(shù)對(duì)速度有影響嗎?

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 語(yǔ)句之間是否存在速度差異:

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

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

- 對(duì) -

我建議的 while 語(yǔ)句:

a while statement that I'm suggesting:

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

- 對(duì) -

在運(yùn)行第一個(gè) multi_query 之前，為每個(gè)預(yù)期的 multi_query 創(chuàng)建一個(gè)新連接.我剛剛測(cè)試了這個(gè)，兩個(gè) mysqli_multi_query() 沒(méi)有錯(cuò)誤 = 不需要 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");

- 對(duì) -

在每個(gè) mysqli_multi_query() 之間打開(kāi)和關(guān)閉，如 Sebastien 和 Craig:

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

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

- 對(duì) -

有人有其他選擇來(lái)測(cè)試嗎?

Anyone have another option to test against?

推薦答案

責(zé)怪不是 next_result() 而是查詢(xún)自己.代碼運(yùn)行所需的時(shí)間取決于執(zhí)行實(shí)際查詢(xún)所需的時(shí)間.

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() 返回控制的速度相當(dāng)快，這并不意味著所有查詢(xún)都在那個(gè)時(shí)候執(zhí)行了. 恰恰相反，到時(shí)候 mysqli_multi_query() 完成，只執(zhí)行了第一個(gè)查詢(xún). 而所有其他查詢(xún)都在 mysql 端排隊(duì)等待異步執(zhí)行.

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.

由此您可以得出結(jié)論，next_result() 調(diào)用本身不會(huì)添加任何超時(shí) - 它只是在等待下一個(gè)查詢(xún)完成.如果查詢(xún)本身需要時(shí)間，那么 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.

知道您可能已經(jīng)知道選擇哪種方式:如果您不關(guān)心結(jié)果，您可能只是關(guān)閉連接.但事實(shí)上，它只會(huì)掃除地毯下的污垢，將所有緩慢的查詢(xún)留在原處.因此，最好保持 next_result() 循環(huán)到位(尤其是因?yàn)槟仨殭z查錯(cuò)誤/受影響的行/等)但加快查詢(xún)本身.

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.

所以，事實(shí)證明，要解決next_result() 的問(wèn)題，您必須實(shí)際解決查詢(xún)速度的常規(guī)問(wèn)題.所以，這里有一些建議:

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. 對(duì)于選擇查詢(xún)，它通常是索引/解釋分析，已經(jīng)在其他答案中解釋過(guò).
2. 對(duì)于 DML 查詢(xún)，尤其是批量運(yùn)行，還有其他方式:

說(shuō)到 Craig 的案例，它與已知的 innodb 寫(xiě)入速度問(wèn)題非常相似.默認(rèn)情況下，innodb 引擎設(shè)置為非常謹(jǐn)慎的模式，在引擎確保前一次成功完成之前，不會(huì)執(zhí)行后續(xù)寫(xiě)入.因此，它使寫(xiě)入非常緩慢(大約只有 10 個(gè)查詢(xún)/秒).對(duì)此的常見(jiàn)解決方法是一次進(jìn)行所有寫(xiě)入.對(duì)于插入查詢(xún)，有很多方法:

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:

• 您可以使用多個(gè)值插入語(yǔ)法
• 您可以使用 LOAD DATA INFILE 查詢(xún)
• 您可以將所有查詢(xún)包裝在一個(gè)事務(wù)中.

雖然僅更新和刪除事務(wù)仍然是可靠的方式.因此，作為通用解決方案，可以提供這樣的解決方法

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 *
                
【網(wǎng)站聲明】本站部分內(nèi)容來(lái)源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問(wèn)題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請(qǐng)聯(lián)系我們刪除處理，感謝您的支持！