問題描述

有人在回答中提到了 MySQLi multi_query 函數(shù)，聲稱這比循環(huán)遍歷 3 個(gè)單獨(dú)的查詢要好.我試圖在谷歌上搜索一些答案，但沒有真正得到任何真正滿足我好奇心的東西，所以我希望你們能更好地了解使用它的原因，而不是節(jié)省幾行代碼.

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. multi_query 在底層做了什么?
2. multi_query 是否只是簡單地訪問服務(wù)器 x 次并聚合結(jié)果?
3. 是否存在單個(gè)查詢可能比多個(gè)查詢更有效的情況?

我知道為每個(gè) 100 萬個(gè)項(xiàng)目訪問數(shù)據(jù)庫 3 次并將其粉碎成一個(gè)巨大的對(duì)象不利于內(nèi)存使用，但我知道它的存在必須有一個(gè)原因，我也確信有是應(yīng)該避免的時(shí)候.我希望能更好地理解它，以便在需要時(shí)將其放入我的技巧包中.

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.

感謝您的時(shí)間！

推薦答案

• multi_query 到底在做什么? - 只需將所有查詢一次發(fā)送到服務(wù)器，而不是一次發(fā)送一個(gè)，并一次性檢索所有結(jié)果.沒有比這更復(fù)雜的了.

  • 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 是否只是簡單地點(diǎn)擊服務(wù)器 x 次并聚合結(jié)果? - 它點(diǎn)擊"服務(wù)器兩次 - 一次發(fā)送查詢，一次檢索結(jié)果.

    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.

    是否存在單個(gè)查詢可能比多個(gè)查詢更有效的情況? - 取決于您如何定義高效".multi_query() 在網(wǎng)絡(luò)上很輕，但內(nèi)存很重，在循環(huán)中運(yùn)行 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.

    對(duì)于許多返回大型結(jié)果集的 SELECT 語句，內(nèi)存消耗的損失可能大大超過網(wǎng)絡(luò)方面的收益，并且大多數(shù)時(shí)候您最好發(fā)出查詢并一次處理一個(gè)結(jié)果集 - 盡管這完全取決于您對(duì)數(shù)據(jù)的處理方式.但是如果你需要運(yùn)行很多 UPDATE 語句，很可能 multi_query() 會(huì)更好，因?yàn)榉祷刂抵皇浅晒?失敗并且內(nèi)存消耗會(huì)很輕.

    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.

    您必須權(quán)衡所有因素，例如您在做什么、預(yù)計(jì)需要多長時(shí)間、(數(shù)據(jù)庫)服務(wù)器和客戶端之間的網(wǎng)絡(luò)延遲、服務(wù)器上的可用資源(主要是內(nèi)存)和客戶等，等等......并根據(jù)具體情況進(jìn)行處理.

    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.

    我發(fā)現(xiàn)這個(gè)一些性能測(cè)試的記錄做了一個(gè)不久前，結(jié)論是使用 multi_query() 可以提高整體效率.然而測(cè)試用例只是運(yùn)行 4 個(gè)查詢，每個(gè)查詢SELECT一個(gè)結(jié)果，更高效"的定義就是更快".沒有針對(duì)大量查詢或更大結(jié)果集的測(cè)試，雖然速度很重要，但它并不是最重要的 - 如果我給它無限量的內(nèi)存，我可以使任何東西運(yùn)行得非常快，但是嘗試同時(shí)做任何事情都會(huì)失敗.這也不是真實(shí)世界的測(cè)試，因?yàn)樽罱K結(jié)果可以通過單個(gè) JOIN ed 查詢來實(shí)現(xiàn).不過，它確實(shí)會(huì)帶來一些有趣的閱讀.

    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.

    我個(gè)人覺得這有點(diǎn)學(xué)術(shù)性，因?yàn)槿绻?em>一次運(yùn)行大量語句，則 90% 的時(shí)間它們只會(huì)在傳遞的數(shù)據(jù)和查詢結(jié)構(gòu)方面有所不同將保持不變 - 這是準(zhǔn)備好的語句的明顯候選.

    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.

    這篇關(guān)于mysqli::multi_query 是否比多個(gè)單個(gè)查詢更有效?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！

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