問題描述

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

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 是否只是簡單地訪問服務器 x 次并聚合結果?
3. 是否存在單個查詢可能比多個查詢更有效的情況?

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

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一個結果，更高效"的定義就是更快".沒有針對大量查詢或更大結果集的測試，雖然速度很重要，但它并不是最重要的 - 如果我給它無限量的內存，我可以使任何東西運行得非?？欤菄L試同時做任何事情都會失敗.這也不是真實世界的測試，因為最終結果可以通過單個 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.

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

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