MySQL 查詢運作模式
學習 Tuning 要先知道資料庫怎麼運作
前言
隨著大數據的發展，SQL 作為一種成熟的查詢語言逐漸回到人們視野的中心來，將 SQL 的轉換成高效執行是可以降低整個查詢時間的，在 OLAP(線上分析處理) 任務中，時間就是一切。這篇文章主要研究了 MySQL 在一個查詢進來的時候，作了哪些工作，以此為基礎來探討日後我打算學習的查詢優化方法。
一個查詢所進行的路徑
查詢的執行路徑。以下是MySQL服務器中如何執行查詢的圖表。
我們可以從上圖中看到 MySQL 作一次查詢時，會經過 7 個工序，最後返回一個結果給 Client。下文會一一介紹每個工序的功用。
Client 如何跟 MySQL Server 要資料
當我們啟動一個查詢的時候，會透過 Client Server Protocol 跟 MySQL Server 要資料，這個過程是一個 half-duplex，意思是說，要等到查詢的整個結果回來之後，才可以根據這個查詢結果作下一個操作。這個意思是說當我們執行
SELECT * FROM [Table] ;時，如果結果尚未回傳，整個查詢的過程是被鎖住的， Client 如果要修改查詢內容必須等到整個查詢結果回來才能進行下一步。
我看過有人先把所有資料撈回來，再透過商業邏輯去撈取他所需要的資料，這是非常沒有效率的，他應該透過 WHERE 述句去讓整個查詢取出必要的資料即可。
因此，當 Client 將查詢請求發送到 MySQL Server 時，MySQL Server 只會回應 Client 所要求的資料。
Query Cache
當 MySQL 回傳過一個查詢的結果之後，他會將這個結果隨著查詢述句存到 Query Cache 中。 Query Cache 的功用在於當 Client 端如果再次發送相同的查詢到 MySQL Server 中，這樣他就可以直接把 Query Cache 儲存的結果直接送回到 Client 端，就不再次執行這個查詢。但是這個查詢述句要完全一樣，包含文字間的空白數量以及判斷條件等。如果 MySQL Engine 發現不同，就會讓這個查詢述句進到 Parser 跟 Preprocessor 中。
Parser
Parser 的主要職責是接入查詢述句並將其劃分為多個標記或運算符，接著建構出一棵 Parse Tree。這個 Parse Tree 會根據 MySQL 的定義進行語法驗證SQL 述句中各種關鍵字和運算符以及語意的順序是否合法。舉例來說， ORDER BY 這個關鍵字永遠必須接在 SELECT 敘述後面， 以及 FROM 子句永遠接在 SELECT 子句後面。當查詢述句是合法的，該查詢工作會進到下個階段：Preprocessor。
Preprocessor
在上個工作確保查詢述句是合法的，接著預處理器必須檢查各種權限，以及檢查其他額外的語意是否合法。舉例來說，撿查這個查詢要訪問的表是否存在，包含所查詢的欄位或是 Aliases 是否存在。
Parser 跟 Preprocessor 在整個 MySQL 查詢中佔有重要一席之地，但是他們執行所需的時間其實不多。
Query Optimizer
Query Optimizer 轉換 Parse Tree 變成查詢計畫(Query plan)，然後評估所有可以執行的查詢的方式，從中找出最好的方式來進行查詢工作的執行，主要是相同一個查詢可以用很多種運算方式去達成。在 MySQL 中，Optimizer 的演算法是使用 Cost-based 演算法。Optimizer 會去猜測以及預測每個查詢分別所需的成本，然後挑出成本最小的查詢方法去運作，以這樣來提升整個查詢的效能。
Optimizer在整個評估過程中，並沒有把 Query Cache 的影響納入計算中，主要是 MySQL 假設所有的讀取事件發生時都是因為 Disc I/O 的操作。
Query Execution Engine
當我們優化過整個查詢以及建立其查詢計畫之後，他會將其丟入 Query Execution Engine 中。多數的 Database 會將 query 轉換成 bytecode 格式執行，但是在 MySQL 中，整個執行計畫會轉換成資料結構的格式。MySQL遵循查詢執行計劃中給予的指令來獲得所需的結果。接著，Query Execution Engine 調用 FEDERATED Storage Engine 所提供的 Handler API 介面，每個查詢都有其 Handler API 的實例。這些 API 接合了包含了 MyISAM 跟 InnoDB 等，取得需要的資訊後再傳回到 Query Execution Engine。
如果 Storage Engine 處於 statistics 狀態，尚未更新狀態的話，Query Execution Engine 可能會執行第二優化的查詢計畫來作查詢。
注意到如果，statistics 狀態如果經常出現，有可能是 Disk I/O 性能很差。
Storage Engine
每個存儲引擎都有不同的結構和不同的屬性，有些不同的 MySQL Storage Engine 會有不同的限制。這裡要注意的另一件事是，如果多個 Storage Engine 有任何共有的屬性，這些屬性不會儲存在 Storage Engine 中，而是存在 MySQL Server 中。舉例來說， Date/Time 等內容就會存在 Server 中。
回傳結果到 Client
當 Query Execution Engine 完成工作後，會把得到的結果回傳到 Client ，即便查詢的結果為空，Query Execution Engine 一樣會回傳空結果到 Client 。總之，Client 請求任何附加的查詢都會在 Query Execution Engine 完成運算後得到結果
另外一件事情是，如果這個查詢可以被暫存，MySQL 會把這個查詢結果一併儲存到前面提到的 Query Cache 中，下次當 Client 端發送相同的查詢到 MySQL Server 中，就不用再重複一次上述的查詢流程，直接把 Query Cache 中的資料回傳到 Client 端。
結論
本篇主要探討資料庫執行一次查詢時會做哪些事情。其中我們每次的查詢都會先訪問Query Cache ，接著檢查該 SQL 語句及訪問的表是否合法，接著， MySQL Optimizer 會使用基於成本的演算法使任何查詢能夠規劃出最佳計劃，加速整個搜尋，然後執行整個 Query Execution，這時 Storage Engine 在查詢性能中起著關鍵作用， Optimizer 依賴於它來取得各種關鍵資訊。最後 Query Execution Engine 返還查詢結果外，也同時把結果儲存到 Query Cache 中，完成這次的查詢工作。
