[TOC] ***** MySQL執行一個查詢的過程  1.客戶端發送一條查詢給服務器。 2.服務器先檢查查詢緩存，如果命中了緩存，返回結果。 3.服務器端進行SQL解析、預處理，由優化器生成執行計劃。 4.MySQL調用存儲引擎的API來執行計劃。 5.將結果返回給客戶端。 ## 6.4.1　MySQL客戶端/服務器通信協議 MySQL客戶端和服務器之間的通信協議是“半雙工”的，這意味著，在任何一個時刻，要么是由服務器向客戶端發送數據，要么是由客戶端向服務器發送數據。 一旦一端開始發生消息，另一端要接收完整個消息才能響應它。 客戶端用一個單獨的數據包將查詢傳給服務器。這也是為什么當查詢的語句很長的時候，參數max_allowed_packet就特別重要了(如果查詢太大，服務端會拒絕接收更多的數據并拋出相應錯誤。) 一般服務器響應給用戶的數據通常很多，由多個數據包組成。當服務器開始響應客戶端請求時，客戶端必須完整地接收整個返回結果，而不能簡單地只取前面幾條結果，然后讓服務器停止發送數據。 多數連接MySQL的庫函數默認一般是獲得全部結果集并緩存到內存中。MySQL通常需要等所有的數據都已經發送給客戶端才能釋放這條查詢所占用的資源，所以接收全部結果并緩存通常可以減少服務器的壓力，讓查詢能夠早點結束、早點釋放相應的資源。 但是如果需要返回一個很大的結果集的時候，這樣做并不好，因為庫函數會花很多時間和內存來存儲所有的結果集。如果能夠盡早開始處理這些結果集，就能大大減少內存的消耗，這種情況下可以不使用緩存來記錄結果而是直接處理。這樣做的缺點是，對于服務器來說，需要查詢完成后才能釋放資源，所以在和客戶端交互的整個過程中，服務器的資源都是被這個查詢所占用的(你可以使用SQL_BUFFER_RESULT，后面將再介紹這點。) ***** **查詢狀態** 對于一個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正在將一個內存臨時表放到磁盤上。 The thread is 線程正在對結果集進行排序。 Sending data 這表示多種情況：線程可能在多個狀態之間傳送數據，或者在生成結 集，或者在向客戶端返回數據 ## 6.4.2　查詢緩存 在解析一個查詢語句之前，如果查詢緩存是打開的，那么MySQL會優先檢查這個查詢是否命中查詢緩存中的數據。這個檢查是通過一個對大小寫敏感的哈希查找實現的。 如果當前的查詢恰好命中了查詢緩存，那么在返回查詢結果之前MySQL會檢查一次用戶權限。 如果權限沒有問題，MySQL會跳過所有其他階段，直接從緩存中拿到結果并返回給客戶端。這種情況下，查詢不會被解析，不用生成執行計劃，不會被執行。 ## 6.4.3　查詢優化處理 查詢的生命周期的下一步是將一個SQL轉換成一個執行計劃，MySQL再依照這個執行計劃和存儲引擎進行交互。這包括多個子階段：解析SQL、預處理、優化SQL執行計劃。這個過程中任何錯誤（例如語法錯誤）都可能終止查詢。 **語法解析器和預處理** MySQL解析器將使用MySQL語法規則驗證和解析查詢，預處理器則根據一些MySQL規則進一步檢查解析樹是否合法，下一步預處理器會驗證權限。 **查詢優化器** 現在語法樹被認為是合法的了，并且由優化器將其轉化成執行計劃。一條查詢可以有很多種執行方式，最后都返回相同的結果。優化器的作用就是找到這其中最好的執行計劃。 MySQL使用基于成本的優化器，它將嘗試預測一個查詢使用某種執行計劃時的成本，并選擇其中成本最小的一個。最初，成本的最小單位是隨機讀取一個4K數據頁的成本，后來（成本計算公式）變得更加復雜，并且引入了一些“因子”來估算某些操作的代價，如當執行一次WHERE條件比較的成本。可以通過查詢當前會話的Last_query_cost的值來得知MySQL計算的當前查詢的成本 **** 這個結果表示MySQL的優化器認為大概需要做1040個數據頁的隨機查找才能完成上面的查詢。這是根據一系列的統計信息計算得來的：每個表或者索引的頁面個數、索引的基數（索引中不同值的數量）、索引和數據行的長度、索引分布情況。優化器在評估成本的時候并不考慮任何層面的緩存，它假設讀取任何數據都需要一次磁盤I/O。 **有很多種原因會導致MySQL優化器選擇錯誤的執行計劃，如下所示：** * 統計信息不準確。MySQL依賴存儲引擎提供的統計信息來評估成本，但是有的存儲引擎提供的信息是準確的，有的偏差可能非常大。例如，InnoDB因為其MVCC的架構，并不能維護一個數據表的行數的精確統計信息。 * 執行計劃中的成本估算不等同于實際執行的成本。所以即使統計信息精準，優化器給出的執行計劃也可能不是最優的。例如有時候某個執行計劃雖然需要讀取更多的頁面，但是它的成本卻更小。因為如果這些頁面都是順序讀或者這些頁面都已經在內存中的話，那么它的訪問成本將很小。MySQL層面并不知道哪些頁面在內存中、哪些在磁盤上，所以查詢實際執行過程中到底需要多少次物理I/O是無法得知的。 * MySQL的最優可能和你想的最優不一樣。你可能希望執行時間盡可能的短，但是 MySQL只是基于其成本模型選擇最優的執行計劃，而有些時候這并不是最快的執行方式。所以，這里我們看到根據執行成本來選擇執行計劃并不是完美的模型。 * MySQL從不考慮其他并發執行的查詢，這可能會影響到當前查詢的速度。 * MySQL也并不是任何時候都是基于成本的優化。有時也會基于一些固定的規則，例如，如果存在全文搜索的MATCH()子句，則在存在全文索引的時候就使用全文索引。即使有時候使用別的索引和WHERE條件可以遠比這種方式要快，MySQL也仍然會使用對應的全文索引。 * MySQL不會考慮不受其控制的操作的成本，例如執行存儲過程或者用戶自定義函數的成本。 * 后面我們還會看到，優化器有時候無法去估算所有可能的執行計劃，所以它可能錯過實際上最優的執行計劃。 **MySQL的查詢優化器優化策略可以簡單地分為兩種，一種是靜態優化，一種是動態優化。** * 靜態優化可以直接對解析樹進行分析，并完成優化。例如，優化器可以通過一些簡單的代數變換將WHERE條件轉換成另一種等價形式。靜態優化不依賴于特別的數值，如WHERE條件中帶入的一些常數等。靜態優化在第一次完成后就一直有效，即使使用不同的參數重復執行查詢也不會發生變化。可以認為這是一種“編譯時優化”。 * 動態優化則和查詢的上下文有關，也可能和很多其他因素有關，例如WHERE條件中的取值、索引中條目對應的數據行數等。這需要在每次查詢的時候都重新評估的時候都重新評估，可以認為這是“運行時優化”。 在執行語句和存儲過程的時候，動態優化和靜態優化的區別非常重要。MySQL對查詢的靜態優化只需要做一次，但對查詢的動態優化則在每次執行時都需要重新評估。有時候甚至在查詢的執行過程中也會重新優化。 **下面是一些MySQL能夠處理的優化類型： 重新定義關聯表的順序** 數據表的關聯并不總是按照在查詢中指定的順序進行。決定關聯的順序是優化器很重要的一部分功能，本章后面將深入介紹這一點。 **將外連接轉化成內連接** 并不是所有的OUTER JOIN語句都必須以外連接的方式執行。諸多因素，例如WHERE條件、庫表結構都可能會讓外連接等價于一個內連接。 MySQL能夠識別這點并重寫查詢，讓其可以調整關聯順序。 **內連接** 內連接是從結果表中刪除與其他被連接表中沒有匹配行的所有行，所以內連接可能會丟失信息。 **使用等價變換規則** MySQL可以使用一些等價變換來簡化并規范表達式。它可以合并和減少一些比較，還可以移除一些恒成立和一些恒不成立的判斷。例如，（5=5 AND a>5）將被改寫為a>5。類似的，如果（a<b AND b=c） AND a=5 則 AND a>5）將被改寫為a>5。類似的，如果有（a<b AND b=c） AND a=5 則會改寫為 b>5 AND b=c AND a=5。 **優化COUNT()、MIN()和MAX()** 索引和列是否可為空通常可以幫助MySQL優化這類表達式。例如，要找到某一列的最小值，只需要查詢對應B-Tree索引最左端的記錄，MySQL可以直接獲取索引的第一行記錄。在優化器生成執行計劃的時候就可以利用這一點，在B-Tree索引中，優化器會將這個表達式作為一個常數對待。類似的,如果要查找一個最大值，也只需讀取B-Tree索引的最后一條記錄。如果MySQL使用了這種類型的優化，那么在EXPLAIN中就可以看到“Select tables optimized away”。果要查找一個最大值，也只需讀取B-Tree索引的最后一條記錄。如果MySQL使用了這種類型的優化，那么在EXPLAIN中就可以看到“Select tables optimized away”。從字面意思可以看出，它表示優化器已經從執行計劃中移除了該表，并以一個常數取而代之。沒有任何WHERE條件的COUNT（*）查詢通常也可以使用存儲引擎提供的一些優化（例如，MyISAM維護了一個變量來存放數據表的行數）。