理解MySQL查詢優化處理過程

Posted on by

MySQL查詢優化需要經過解析、預處理和優化三個步驟。在這些過程中，都有可能發生錯誤。本篇文章不會深入討論錯誤處理，而是幫助理解 MySQL 執行查詢的方式，以便可以寫出更好的查詢語句。

解析器和預處理器

一開始，MySQL 的解析器將查詢語句拆分成一系列指令並從中構建一棵“解析樹”。解析器使用 MySQL 的SQL 語法去翻譯和驗證查詢語句。例如，解析器保證瞭查詢中的指令是有效且次序正確，並且會檢查那種類似字符串引號未配對的錯誤。

預處理器則檢查構建好的解析樹中那些解析器無法處理的語義信息。例如，檢查數據表和列是否存在，並且處理字段名稱和別名以保證列引用沒有歧義。接下來，預處理器會檢查權限，通常這會非常快（除非你的服務端有一大堆權限配置）。

查詢優化器

經過解析器和預處理器後，解析樹就被確定是有效的瞭，可以被優化器進行處理並最終轉變為一個查詢計劃。一個具有相同結果的查詢通常有很多種執行方式，而優化器的職責是找出其中最優的選項。

MySQL使用基於代價估計的優化器，這意味著它視圖預測眾多執行計劃的代價，並選擇代價最低的那個。最初的單位成本是隨機的4KB 數據頁讀取，而現在變得更為復雜，包括瞭如執行 WHERE比較條件的代價。可以通過顯示 Last_query_cost 會話變量來查看查詢優化器估計查詢語句的代價。

SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor;
SHOW STATUS LIKE 'Last_query_cost';

顯示的 Last_query_cost 意味著優化器估計需要執行對應次數的隨機數據頁訪問才能完成查詢。這是基於如下統計估算的結果：

  • 數據表或索引占據的數據頁數；
  • 索引的候選值；
  • 數據行、鍵及鍵值分佈對應的數據長度。

優化器並不會考慮估計內容的緩存——它假設每次都從磁盤 I/O 讀取結果。優化器並不是每次都能選擇最優的執行計劃，原因如下：

  • 統計本身可能是錯誤的。服務端的統計結果依賴於存儲引擎，而存儲引擎可能十分準確也可能很不準確。例如，InnoDB 由於其 MVCC 架構，並不保留數據表的準確行數。
  • 估計的代價和實際運行的代價並不等價，因此即便統計是準確的，查詢的代價與 MySQL 的估計也會或多或少存在偏差。一個讀取更多數據頁的查詢計劃也可能代價更低，例如如果是有序的磁盤 I/O 訪問就會更快，又或是結果本身就已經在緩存中。因此，優化器本身並不知道查詢會引起多少次 I/O 操作。
  • MySQL 人為的優化也許與我們期待的不同。我們要的可能是更快的執行時間，而 MySQL 並不是隻追求快，它是最求最小化代價。因此，通過代價並不一定科學。
  • MySQL並不考慮並發中的查詢，而這可能會影響查詢運行的速度。
  • MySQL 並不是一直都按代價估計做優化。有時候僅僅是遵循一些規則，例如如果有一個全文匹配條件（MATCH 方法）則使用全文索引。即便是有一個更快的的其他索引和非全文條件查詢，MySQL 也不會按更快的方式執行查詢。
  • 優化器對於不歸它控制的操作的代價並不會考慮，例如執行存儲過程或自定義函數。
  • 優化器並不總是能夠估計每一個執行計劃，有些時候它會忽略一個更優的計劃。

MySQL 查詢優化器是其中非常復雜的一部分，使用瞭很多優化方式將查詢語句轉換成為一個查詢執行計劃。通常有兩種優化方式：靜態優化和動態優化。靜態優化可以簡單地通過檢查解析樹進行。例如，優化器可以將 WHERE 條件通過數學運算規則轉換成一個等式。靜態優化與具體的值無關，例如 WHERE條件的常量值。他們執行一次後會一直有效，即便是查詢語句使用瞭不同的值再次執行。可以理解為是“編譯時優化”。

相反，動態優化是基於具體的情景的，並依賴於多種因素。例如，WHERE 條件中的值或索引中對應的數據行數。這個過程在每次查詢都需要重新估計，可以理解為是“運行時優化”。以下是一些 MySQL 的典型優化方式：

  • 聯合查詢重新排序：數據表並不一定需要按照查詢語句的順序聯合。決定最優的聯合查詢次序是十分重要的優化。
  • 將外聯接轉換為內聯接：一個外聯接並不一定需要按外聯接查詢。有些因素，例如 WHERE 條件和數據表結構可以將外聯接查詢等價於內聯接。MySQL 可以識別這些情況，並重寫聯合查詢。
  • 應用數學等價公式：MySQL 應用數學等價轉換簡化表達式。可以做到展開和減少常量，排除不可能的情況和常量表達式。例如，表達式(5=5 AND a>5)會精簡為(a>5)。同樣的，(a 5 AND b=c AND a=5.這些規則對帶條件的查詢十分有用。
  • COUNT()，MIN()和 MAX()優化：索引和空值列通常可以幫助 MySQL 優化這些函數。例如，查找二叉樹最左側一列的最小值時，MySQL 可以隻請求索引的第一行數據。甚至可以在查詢優化階段完成這個事情，而對於剩餘的查詢當作是常量值。而對於查詢最大值也是一樣，隻需要讀取最後u 一行即可。如果服務端使用瞭這種優化，可以在 EXPLAIN 中看到“Select tables optimized away”。這意味著優化器將數據表從查詢計劃中移除並用常量替代瞭。類似地，COUNT(*)查詢在沒有指定 WHERE 條件時也可以在某些存儲引擎被優化（例如 MyISAM，會一直保存數據表的準確行數）。
  • 評估和精簡常量表達式：一旦 MySQL 檢測到一個表達式可以精簡為一個常量，那在優化階段就會完成該操作。例如，一個用戶定義的變量如果在查詢過程中沒有變化，就可以轉換為常量。令人驚奇的是，在優化階段，有些你認為是一個查詢的語句也會被轉換為常量。一個例子就是 索引上的MIN()。這種情況也可以擴展到對主鍵或獨立索引的常量查詢。如果 WHERE 條件對這樣的索引指定瞭常量，優化器會知道 MySQL 會在查詢開始就查找對應的值。然後，就會在剩餘的查詢中把這個值當做常量處理。下面是一個例子：
EXPLAIN SELECT film.film_id, film_actor.actor_id
FROM sakila.film
	INNER JOIN sakila.film_actor USING(film_id)
WHERE film.film_id = 1;

MySQL 會將這個查詢拆分為2步，因此分析結果會有兩行。第一步是是在 film 表中查找對應的數據行。由於 是按主鍵film_id查詢的，MySQL 知道隻有一行數據。 因此，此時的查詢分析結果的 ref 是常量。在第二步中，MySQL 會將 film_id 作為已知值，因此對 film_actor 的查詢的 ref 也是常量。其他類似的場景還有 WHERE，USING或 ON 條件中的約束條件是等式。在這個例子中，MySQL 知道 USING條件的 film_id 在查詢語句中都是相同的值，這個值必須和 WHERE條件的 film_id 相同。

  • 覆蓋索引：MySQL 有時候會利用索引數據而避免讀數行數據，如果索引包含瞭查詢所需的全部列的話。
  • 子查詢優化：MySQL 能夠將一些類型的子查詢轉換為更有效的變體形式，從而簡化它們為索引查詢而不是相互獨立的查詢。
  • 提前中止：MySQL 可以在滿足查詢結果後提前中止查詢過程。最明顯的例子是 LIMIT條件。也有一些其他的提前中止的情形。例如，MySQL 檢測導一個可能條件後，可以中止整個查詢，如下面的例子所示：
EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id=1;

在分析結果中的 Extra字段會看到“Impossible WHERE noticed after reading const tables”。在其他情形也會有提前中止的情況，例如：

SELECT film.film_id
FROM sakila.film
	LEFT OUTER JOIN sakila.film_actor USING(film_id)
WHERE sakila.film_actor.film_id IS NULL;

這個查詢排除那些有演員的電影。每部電源都可能有多名演員，但是隻要找到一名演員後，MySQL 就會停止處理當前的這部電影，而去處理下一部。對於 DISTINCT，NOT EXISTS 也會有類似的情況。

  • 等效傳遞：MySQL 會識別導查詢語句中保持的列是否是等效的。例如，在 JOIN 條件中，WHERE 條件會影響導相同的列，如下面的查詢：
SELECT film.film_id
FROM sakila.film
	INNER JOIN sakila.film_actor USING(film_id)
WHERE film.film_id > 500;

MySQL 會知道 WHERE 條件的約束不僅適用於 film 表，同樣也適用於 film_actor 表。但對於其他數據庫則未必會有這樣的優化效果。

  • IN 查詢比較：對於很多數據庫服務器，IN 查詢比等價為多個 OR 條件，在邏輯上二者是等效的。但在 MySQL 中不是這樣，MySQL會對 IN 查詢的列表值進行排序，並使用二分查找法去檢查查詢值是否在列表中。這會使得算法復雜度從 O(n)降低導 O(log n)。

實際上，MySQL 使用的優化手段比上述列舉的多得多，這裡沒法一一列舉。隻是需要記住 MySQL 的優化器的復雜性及其智能化程度。因此，應當讓優化器發揮其作用，而不是無限優化查詢語句直到 MySQL 的優化器沒有用武之地。當然，雖然 MySQL 的優化器很聰明，但是它給出的並不一定是最優結果，有些時候你知道最優結果，而 MySQL 未必知道。這種情況下，你可以對查詢語句進行優化從而幫助 MySQL 完成優化工作，而有些時候則需要增加查詢的提示，或是重寫查詢，修改數據表設計或增加索引。

以上就是理解MySQL查詢優化處理過程的詳細內容，更多關於MySQL 查詢優化的資料請關註WalkonNet其它相關文章！

推薦閱讀：