MySQL查詢優化之優化器工作流程以及優化的執行計劃生成

檢視查詢成本

查詢上一次SQL的成本，單位頁

SHOW STATUS LIKE 'Last_query_cost'

上述結果表示 MySQL 的優化器認為大概需要 12個資料頁的隨機查詢才能完成上述的查詢。

優化器做了什麼？

語法樹被認為合法時，優化器會將其轉化成執行計劃。一條查詢可以有很多種執行方式，最後都返回相同結果。優化器的作用就是找到這其中最好的執行計劃。MySQL 使用基於成本的優化器，它將嘗試預測一個查詢使用某種執行計劃時的成本，並選擇其中成本最小的一個。

可能會導致優化器選擇了錯誤的執行計劃？

【1】統計資訊不準確：MySQL 依賴儲存引擎提供的統計資訊來評估成本，但有的偏差可能非常大。例如，InnoDB 因為其 MVCC 的架構，並不能維護一個資料表的行數的精確統計資訊。

【2】執行計劃中的成本估算不等同實際執行的成本：所以即使統計資訊精準，優化器給出的執行計劃也可能不是最優的。例如某個執行計劃雖然需要讀取更多的頁面，但是它的成本卻更小。因為如果這些頁面都是順序讀或者頁面都已經在記憶體中的話，那麼它的訪問成本將很小。MySQL 層面並不知道哪些頁面在記憶體中、哪些在磁碟上，所以查詢實際執行過程中到底需要多少次物理 I/O 是無法得知的。

【3】MySQL 的最優可能和我們想的最優不一樣：我們希望執行時間儘可能短，但是MySQL 只是基於其成本模型選擇最優的執行計劃，有時候並不是最快的執行方式。所以，我們根據執行成本選擇執行計劃並不是完美的模型。

【4】MySQL 從不考慮其他併發執行的查詢：可能會影響到當前查詢的速度。

【5】MySQL 也並不是任何時候都是基於成本的優化：有時也基於一些固定的規則。

【6】MySQL 不會考慮不受其控制的操作的成本：例如執行儲存過程或者使用者自定義函式的成本。

【7】優化器有時候無法估算所有可能的執行計劃，所以它可能錯過實際上最優的執行計劃。 MySQL 的查詢優化器使用了很多優化策略來生成一個最優的執行計劃。優化側率可以簡單分為兩種：靜態優化和動態優化。靜態優化可以直接對解析樹進行分析，並完成優化。動態優化則和查詢的上下文有關，也可能和很多其它因素有關，例如WHERE 條件中的取值、索引中條目對應的資料行數等。這需要在每次查詢的時候都重新評估，可以認為這是 “執行時優化”。

下面是一些 MySQL 能夠處理的優化型別：

【1】重新定義關聯表的順序：資料表的關聯並不總是按照在查詢中指定的順序進行。決定關聯的順序是優化器很重要的一部分功能。

【2】使用等價變換規則：MySQL 可以使用一些等價變換來簡化並規範表示式。它可以合併和減少一些比較，還可以移除一些恆成立和一些恆不成立的判斷。例如：（5=5 AND a>5）將被改寫為 a>5；

【3】將外連線轉化成內連線：並不是所有的 OUTER JOIN 語句都必須以外連線的方式執行。

【4】優化 COUNT()、MIN() 和 MAX()：要找到某一列的最小值，只需要查詢對應 B-Tree 索引最左端的記錄，MySQL 可以直接獲取索引的第一行記錄。在優化器生成執行計劃的時候就可以利用這一點，在 B-Tree 索引中，優化器會將這個表示式作為一個常數對待。

【5】預估並轉化為常數表示式：當 MySQL 檢測到一個表示式可以轉化為常數的時候，就會一直把該表示式作為常數進行優化處理。

【6】覆蓋索引掃描：當索引中的列包含所有查詢的列時，MySQL 就可以使用索引返回需要的資料，而無須查詢對應的資料行。

【7】子查詢優化：MySQL 在某些情況下可以將子查詢轉換一種效率更高的形式，從而減少多個查詢多次對資料進行訪問。

【8】提前終止查詢：如果發現已經滿足查詢需求，MySQL 總是能夠立刻終止查詢。典型的例子就是 LIMIT 子句。

【9】列表IN() 的比較：很多資料庫系統中，IN()完全等同於多個 OR 條件子句，因為這兩個是完全等價的。在MySQL中不成立，IN()列表中的資料先排序，然後通過二分查詢的方式來確定列表中的值是否滿足條件，這是一個 O(logn)複雜度的操作，等價地轉化成 OR 查詢的複雜度為 O(n)，對於 IN() 列表有大量取值的時候，MySQL 的處理速度將會更快。

資料庫會根據執行計劃，調儲存引擎的 API 來執行查詢

優化器：將語法樹轉化成執行計劃。一條查詢可以由多種執行方式，最終都返回相同的結果。優化器的作用就是找到其中最好的執行計劃。MySQL 基於成本的優化器，它嘗試預測一個查詢使用某種執行計劃的成本，並選擇成本最小的一個。可以通過 SHOW STATUS LIKE 'Last_query_cost' 值得知 MySQL 計算的當前查詢的成本。