MySQL查詢執行的基礎

標籤：

　　當希望MySQL能夠以更高的效能執行查詢時，最好的辦法就是弄清楚MySQL是如何最佳化和執行查詢的。一旦理解這一點，很多查詢最佳化實際上就是遵循一些原則讓最佳化器能夠按照預想的合理的方式運行。

　　換句話說，是時候回頭看看我們之前討論的內容了：MySQL執行一個查詢的過程。當向MySQL發送一個請求的時候，MySQL到底做了什麼。

　　1 用戶端發送一條查詢給伺服器。

　　2 伺服器首先檢查緩衝，如果命中緩衝，則立即返回儲存在緩衝的結果，否則進入下一階段。

　　3 伺服器進行sql解析，預先處理，再由最佳化產生器產生對應的執行計畫。

　　4 MySQL根據最佳化器產生的執行計畫，調用儲存引擎的api來執行查詢。

　　5 強結果返回給用戶端。

　　上面的每一步都比想象的負載，我們在後續章節中將繼續討論。我們會看到在每一個階段查詢出來處於何種狀態。查詢最佳化工具是其中特別複雜也是特別難理解的部分。還有很多例外的情況，例如，當查詢使用綁定變數之後，執行路徑會有所不同，我們將在下一章討論這一點。

 

一 MySQL用戶端/伺服器通訊協定

　　一般來說，不需要去理解MySQL通訊協定的內部實現細節，只需要大致理解通訊協定是如何工作的。MySQL用戶端和伺服器之間的通訊協定是“半雙工 ”的，這意味著，在任何一個時刻，要麼是由伺服器向用戶端發送資料，要麼是由用戶端向伺服器發送資料，這兩個動作不能同時發生。所以，我們無法也無須將一個訊息切成小塊來獨立發送。

　　這種協議讓MySQL通訊簡單快速，但是也從很多地方限制住了MySQL。一個明顯的限制是，這意味著無法進行流量控制。一旦一端開始發生訊息，另一端要接收完整個訊息才能響應它。這就像來回的拋球遊戲：任何時刻只有一個人能控制球，而且只有控制球的一方才能將球拋回去（發送訊息）。

　　用戶端用一個單獨的資料包將查詢傳給伺服器。這也是為什麼當查詢的語句很長的時候參數max_allowed_packet 就特別重要了。一旦用戶端發送了請求，它能做的事情，就只是等待結果了。

　　相反的，一般伺服器響應給客戶的資料通常很多，由多個資料包組成。當伺服器開始響應用戶端請求時，用戶端必須完整的接受整個返回結果，而不能簡單的只取前面幾條結果，然後然伺服器停止發送資料，這種情況下，用戶端若接收完整的結果，然後取前面幾條需要的結果，或者接收完幾條結果後，就粗暴的中斷連線，都不是好主意。這也是在必要的時候一定要在查詢語句中加上limit限制的原因。

　　換一種方式解釋這種行為：當用戶端從伺服器取資料時，看起來是一個資料拉去的過程，但實際上是MySQL在向用戶端推送資料的過程。用戶端不斷的接收從伺服器推送的資料，，用戶端也無法讓伺服器停下來。

　　多數串連MySQL的庫函數都可以獲得全部結果集並緩衝到記憶體裡，還可以逐行擷取需要的資料。預設一般是獲得全部結果集並緩衝到記憶體中。MySQL通常需要等待所有的資料都已經發送給用戶端，才能釋放這條查詢所佔的資源，所有接受全部結果通常可以減少伺服器壓力，讓查詢能夠早點結束，早點釋放相應的資源。

　　當使用多數串連MySQL的庫函數從MySQL擷取資料時，其結果看起來都像是從MySQL伺服器擷取的資料，而實際上都是從這個庫函數的緩衝讀取資料。多數情況下這沒什麼問題，但是如果需要返回一個很大的結果集的時候，這樣走並不好，因為庫函數會花費很多時間和記憶體來儲存所有的結果集。如果能儘早的開始處理這些結果集，就能大大減少記憶體的消耗，這種情況下可以不使用緩衝記錄結果而是直接處理。這樣走的缺點是，對於伺服器來說，需要查詢完成後才能釋放資源，所以在和用戶端互動的整個過程中，伺服器的資源都是被這個查詢所佔用的。

　　查詢狀態

　　對於一個MySQL的串連，或者說是一個線程，任何時刻都有一個狀態，該狀態表示了MySQL當前正在做什麼。有很多種方式能查看當前的狀態，最賤的的是使用SHOW FULL PROCESSLIST 命令（該命令返回結果中的Command列就表示當前的狀態）。在一個查詢的生命週期中，轉檯會變回很多次。MySQL官方收藏對這些狀態值的含義有最權威的解釋，下面將這些狀態列出來，並做一個簡單的解釋。

　　Sleep

　　線程正在等待用戶端發送新的請求

　　Query

　　線程正在執行查詢或者正在將結果發送給用戶端。

　　Locked

　　在MySQL伺服器層，該線程正在等待表鎖。在儲存引擎實現的鎖，例如Innodb的行鎖，並不會體現在該線程狀態中。對於myisam來說這是一個比較典型的狀態，但在其他沒有行鎖的引擎中也會長出現。

　　Analyzing and statistics

　　線程正在收集儲存引擎的統計資訊，並產生查詢的執行計畫。

　　Copying to tmp table【on disk】

　　線程正在執行查詢，並且將其結果集都複製到一個暫存資料表中，這種狀態一般要麼是做Group by 操作，要麼是檔案排序操作，或者是UNION 操作。如果這個狀態後面還有on disk 標記，那麼表示MySQL正在將一個記憶體暫存資料表放到磁碟上。

　　Sorting result

　　線程正在對結果集進行排序。

　　Sending data

　　這表示多種情況：線程可能是在多個狀態之間傳送資料，或者結果集，或者在向用戶端返回資料。

　　瞭解這些狀態的戒備含義非常有用，這可以讓你更快的瞭解當前誰正在持球。在一個繁忙的伺服器上，可能會看到大量的不正常狀態，例如statistics 正在佔用大量的時間。這通常表示，某個地方有異常了。

 

 

 

 

 

　　

MySQL查詢執行的基礎