MySQL查詢優(yōu)化需要經(jīng)過解析慨蓝、預處理和優(yōu)化三個步驟。在這些過程中端幼，都有可能發(fā)生錯誤礼烈。本篇文章不會深入討論錯誤處理，而是幫助理解 MySQL 執(zhí)行查詢的方式婆跑，以便可以寫出更好的查詢語句此熬。

解析器和預處理器

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

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

查詢優(yōu)化器

經(jīng)過解析器和預處理器后咆蒿，解析樹就被確定是有效的了，可以被優(yōu)化器進行處理并最終轉變?yōu)橐粋€查詢計劃币绩。一個具有相同結果的查詢通常有很多種執(zhí)行方式蜡秽，而優(yōu)化器的職責是找出其中最優(yōu)的選項。

MySQL使用基于代價估計的優(yōu)化器缆镣，這意味著它視圖預測眾多執(zhí)行計劃的代價芽突，并選擇代價最低的那個。最初的單位成本是隨機的4KB 數(shù)據(jù)頁讀取董瞻，而現(xiàn)在變得更為復雜寞蚌，包括了如執(zhí)行 WHERE比較條件的代價田巴。可以通過顯示 Last_query_cost 會話變量來查看查詢優(yōu)化器估計查詢語句的代價挟秤。

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

顯示的 Last_query_cost 意味著優(yōu)化器估計需要執(zhí)行對應次數(shù)的隨機數(shù)據(jù)頁訪問才能完成查詢壹哺。這是基于如下統(tǒng)計估算的結果：

• 數(shù)據(jù)表或索引占據(jù)的數(shù)據(jù)頁數(shù)；
• 索引的候選值艘刚；
• 數(shù)據(jù)行管宵、鍵及鍵值分布對應的數(shù)據(jù)長度。

優(yōu)化器并不會考慮估計內(nèi)容的緩存——它假設每次都從磁盤 I/O 讀取結果攀甚。優(yōu)化器并不是每次都能選擇最優(yōu)的執(zhí)行計劃箩朴，原因如下：

• 統(tǒng)計本身可能是錯誤的。服務端的統(tǒng)計結果依賴于存儲引擎秋度，而存儲引擎可能十分準確也可能很不準確炸庞。例如，InnoDB 由于其 MVCC 架構荚斯，并不保留數(shù)據(jù)表的準確行數(shù)埠居。
• 估計的代價和實際運行的代價并不等價，因此即便統(tǒng)計是準確的事期，查詢的代價與 MySQL 的估計也會或多或少存在偏差滥壕。一個讀取更多數(shù)據(jù)頁的查詢計劃也可能代價更低，例如如果是有序的磁盤 I/O 訪問就會更快刑赶，又或是結果本身就已經(jīng)在緩存中捏浊。因此懂衩，優(yōu)化器本身并不知道查詢會引起多少次 I/O 操作撞叨。
• MySQL 人為的優(yōu)化也許與我們期待的不同。我們要的可能是更快的執(zhí)行時間浊洞，而 MySQL 并不是只追求快牵敷，它是最求最小化代價。因此法希，通過代價并不一定科學枷餐。
• MySQL并不考慮并發(fā)中的查詢，而這可能會影響查詢運行的速度苫亦。
• MySQL 并不是一直都按代價估計做優(yōu)化毛肋。有時候僅僅是遵循一些規(guī)則，例如如果有一個全文匹配條件（MATCH 方法）則使用全文索引屋剑。即便是有一個更快的的其他索引和非全文條件查詢润匙，MySQL 也不會按更快的方式執(zhí)行查詢。
• 優(yōu)化器對于不歸它控制的操作的代價并不會考慮唉匾，例如執(zhí)行存儲過程或自定義函數(shù)孕讳。
• 優(yōu)化器并不總是能夠估計每一個執(zhí)行計劃匠楚，有些時候它會忽略一個更優(yōu)的計劃。

MySQL 查詢優(yōu)化器是其中非常復雜的一部分厂财，使用了很多優(yōu)化方式將查詢語句轉換成為一個查詢執(zhí)行計劃芋簿。通常有兩種優(yōu)化方式：靜態(tài)優(yōu)化和動態(tài)優(yōu)化。靜態(tài)優(yōu)化可以簡單地通過檢查解析樹進行璃饱。例如与斤，優(yōu)化器可以將 WHERE 條件通過數(shù)學運算規(guī)則轉換成一個等式。靜態(tài)優(yōu)化與具體的值無關荚恶，例如 WHERE條件的常量值幽告。他們執(zhí)行一次后會一直有效，即便是查詢語句使用了不同的值再次執(zhí)行裆甩∪咚可以理解為是“編譯時優(yōu)化”。

相反嗤栓，動態(tài)優(yōu)化是基于具體的情景的冻河，并依賴于多種因素。例如茉帅，WHERE 條件中的值或索引中對應的數(shù)據(jù)行數(shù)叨叙。這個過程在每次查詢都需要重新估計，可以理解為是“運行時優(yōu)化”堪澎。以下是一些 MySQL 的典型優(yōu)化方式：

• 聯(lián)合查詢重新排序：數(shù)據(jù)表并不一定需要按照查詢語句的順序聯(lián)合擂错。決定最優(yōu)的聯(lián)合查詢次序是十分重要的優(yōu)化。
• 將外聯(lián)接轉換為內(nèi)聯(lián)接：一個外聯(lián)接并不一定需要按外聯(lián)接查詢樱蛤。有些因素钮呀，例如 WHERE 條件和數(shù)據(jù)表結構可以將外聯(lián)接查詢等價于內(nèi)聯(lián)接。MySQL 可以識別這些情況昨凡，并重寫聯(lián)合查詢爽醋。
• 應用數(shù)學等價公式：MySQL 應用數(shù)學等價轉換簡化表達式”慵梗可以做到展開和減少常量蚂四，排除不可能的情況和常量表達式。例如哪痰，表達式(5=5 AND a>5)會精簡為(a>5)遂赠。同樣的，(a<b AND b=c) ADN a = 5會轉換為 b > 5 AND b=c AND a=5.這些規(guī)則對帶條件的查詢十分有用晌杰。
• COUNT()跷睦，MIN()和 MAX()優(yōu)化：索引和空值列通常可以幫助 MySQL 優(yōu)化這些函數(shù)乎莉。例如送讲，查找二叉樹最左側一列的最小值時奸笤，MySQL 可以只請求索引的第一行數(shù)據(jù)。甚至可以在查詢優(yōu)化階段完成這個事情哼鬓，而對于剩余的查詢當作是常量值监右。而對于查詢最大值也是一樣，只需要讀取最后u 一行即可异希。如果服務端使用了這種優(yōu)化健盒，可以在 EXPLAIN 中看到“Select tables optimized away”。這意味著優(yōu)化器將數(shù)據(jù)表從查詢計劃中移除并用常量替代了称簿。類似地扣癣，COUNT(*)查詢在沒有指定 WHERE 條件時也可以在某些存儲引擎被優(yōu)化（例如 MyISAM，會一直保存數(shù)據(jù)表的準確行數(shù)）憨降。
• 評估和精簡常量表達式：一旦 MySQL 檢測到一個表達式可以精簡為一個常量父虑，那在優(yōu)化階段就會完成該操作。例如授药，一個用戶定義的變量如果在查詢過程中沒有變化士嚎，就可以轉換為常量。令人驚奇的是悔叽，在優(yōu)化階段，有些你認為是一個查詢的語句也會被轉換為常量娇澎。一個例子就是 索引上的MIN()笨蚁。這種情況也可以擴展到對主鍵或獨立索引的常量查詢。如果 WHERE 條件對這樣的索引指定了常量趟庄，優(yōu)化器會知道 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 表中查找對應的數(shù)據(jù)行虑鼎。由于 是按主鍵film_id查詢的，MySQL 知道只有一行數(shù)據(jù)键痛。 因此炫彩，此時的查詢分析結果的 ref 是常量。在第二步中絮短，MySQL 會將 film_id 作為已知值江兢，因此對 film_actor 的查詢的 ref 也是常量。其他類似的場景還有 WHERE丁频，USING或 ON 條件中的約束條件是等式杉允。在這個例子中邑贴，MySQL 知道 USING條件的 film_id 在查詢語句中都是相同的值，這個值必須和 WHERE條件的 film_id 相同叔磷。

• 覆蓋索引：MySQL 有時候會利用索引數(shù)據(jù)而避免讀數(shù)行數(shù)據(jù)拢驾，如果索引包含了查詢所需的全部列的話。
• 子查詢優(yōu)化：MySQL 能夠將一些類型的子查詢轉換為更有效的變體形式改基，從而簡化它們?yōu)樗饕樵兌皇窍嗷オ毩⒌牟樵儭?/li>
• 提前中止：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 表尿背。但對于其他數(shù)據(jù)庫則未必會有這樣的優(yōu)化效果端仰。

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

實際上景醇，MySQL 使用的優(yōu)化手段比上述列舉的多得多臀稚，這里沒法一一列舉。只是需要記住 MySQL 的優(yōu)化器的復雜性及其智能化程度三痰。因此吧寺，應當讓優(yōu)化器發(fā)揮其作用窜管，而不是無限優(yōu)化查詢語句直到 MySQL 的優(yōu)化器沒有用武之地。當然稚机，雖然 MySQL 的優(yōu)化器很聰明微峰，但是它給出的并不一定是最優(yōu)結果，有些時候你知道最優(yōu)結果抒钱，而 MySQL 未必知道蜓肆。這種情況下，你可以對查詢語句進行優(yōu)化從而幫助 MySQL 完成優(yōu)化工作谋币，而有些時候則需要增加查詢的提示仗扬，或是重寫查詢，修改數(shù)據(jù)表設計或增加索引蕾额。