?引言

承接《MySQL高級 —— 高性能索引》，本篇博客將圍繞《高性能MySQL（第三版）》第六章內容進行總結和概括。

與索引的部分一樣，SQL優化也是廣大程序員深入MySQL的又一條必經之路。希望通過本篇博客的總結，能夠為我們成為“高級工程師”添磚加瓦。

本博客會從查詢設計的一些基本原則開始，然后介紹一些更加深入的查詢優化技巧，并介紹一些MySQL優化器的內部機制。

一、查詢變慢的原因

MySQL執行查詢時會有很多子任務，通常來說，查詢的生命周期大致可以分為：

從客戶端，到服務器，然后在服務器上進行解析，生成執行計劃，執行，并返回結果給客戶端。

其中最重要的一環是“執行”，這其中包含了大量為檢索數據到存儲引擎的調用以及調用后的數據處理，包括排序、分組等。

查詢性能低下最根本的原因是訪問的數據太多。

某些查詢不可避免地需要篩選大量的數據，但大多數情況，性能低下的查詢都可以通過減少訪問數據量的方式進行優化。

對于性能低下的查詢，一個有效的手段是通過下面兩步來進行分析：

1、確認應用程序是否在檢索大量超過需要的數據。通常意味著訪問太多的行，但也會有時候訪問太多的列。

2、確認MySQL服務器層是否在分析大量超過需要的數據行。

請求不需要的數據會造成多項資源的浪費，如網絡開銷、CPU以及內存資源等。一定不要有SELECT * 這樣的語句。

有時候，需要判斷MySQL是否做了大量額外的掃描，有三個指標衡量其開銷：

1、響應時間? ?

響應時間 = 服務時間 + 排隊時間。服務時間是查詢真正花費的時間，排隊時間是由于資源競爭，如IO或鎖，等待執行的時間。目前的MySQL無法精確計算各個部分的時間，因此，在不同類型的應用壓力下，響應時間沒有統一的規律或公式。

2、掃描行數? ?3、返回行數

分析掃描行數和返回行數，在一定程度上說明了查詢的效率高不高。

理想狀態，掃描行數和返回行數是相同的，但更常見的情況是，掃描的行數和返回的行數比值在 1:1 到 10:1之間。有時會更大。

這三個指標大致反映了MySQL內部執行查詢需要訪問多少數據。三個指標都會記錄在MySQL慢日志中，所以檢查慢日志記錄可以找出掃描行數過多的查詢。

1.1 掃描的行數和訪問類型

EXPLAIN 中的 type 列反應了訪問類型（參考《MySQL 優化 —— EXPLAIN 執行計劃詳解》）。

type 列有：全表掃描（ALL）、索引掃描（index）、范圍掃描（range）、唯一索引查詢（eq_ref）、常數引用（const）等。依次從慢到快，掃描的行數從多到少。

rows 列表示了MySQL預計執行查詢需要掃描的行數。比如，如果 type = ALL ，那么rows 列就是全表的行數。

Extra 列的“Using where” 表示MySQL 將通過 where 條件來篩選存儲引擎返回的記錄。

一般 MySQL 能夠使用如下三種方式應用 WHERE 條件，性能從好到壞依次是：

1、在索引中使用 WHERE 條件來過濾不匹配的記錄，在存儲引擎層完成。

2、使用索引覆蓋掃描（Extra 出現“Using index”），直接在索引中過濾不需要的記錄并返回命中結果。這是在MySQL服務層完成的，但無須再回表查詢。

3、從表中返回數據，然后過濾不滿足條件的記錄（Extra 出現“Using where”）。這個操作在MySQL服務層完成，MySQL需要先從表中讀取記錄，然后過濾。

一個合適的索引，可以讓查詢使用合適的訪問類型，盡可能只掃描需要的數據行。

當我們發現了查詢需要掃描大量的數據但是只返回了少數的行，那么通常可以嘗試下面的技巧去優化它：

1、使用索引覆蓋掃描。把所有需要的列都放到索引中，這樣存儲引擎無須回表獲取對應行就可以返回結果了。

2、改變庫表結構。例如使用單獨的匯總表

3、重寫這個復雜的查詢。

二、重構查詢的方式

在優化有問題的查詢時，目標應該是找到一個更優的方法獲取實際需要的結果，而不一定總是需要從MySQL獲取一模一樣的結果集。

設計查詢時，一個需要考慮的重要問題是，復雜查詢 或 多個簡單查詢。傳統實現中，總是強調在數據庫中完成盡可能多的工作，原因在于減少網絡通信、查詢解析和優化等被認為是代價很高的事情。在其他條件都相同的情況下，盡量減少查詢當然是好的。但有時候，將一個大查詢分解為多個小查詢也是必要的。

2.1 切分查詢

切分查詢是指，將大查詢切分成小查詢，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結果。

例如，DELETE刪除舊數據，如果一次刪除大量的舊數據，則可能需要一次鎖住很多數據，占滿整個事務日志，耗盡系統資源，阻塞很多小的但很重要的查詢。

DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH);

如果切分成多個小的執行語句，并設置適當的時間間隔，就可以大大降低對服務器的影響。

2.2 分解關聯查詢

很多高性能的應用都會對關聯查詢進行分解。

簡單的說，就是每一個表進行一次單表查詢，然后將結果在應用程序中進行關聯。

這樣做的好處有以下幾點：

1、讓緩存的效率更高。

2、執行單個查詢可以減少鎖的競爭。

3、在應用層做關聯，可以更容易對數據庫進行拆分，更容易做到高性能和可擴展。

4、查詢本身效率也可能有所提升。

5、可以減少冗余記錄的查詢。應用層做關聯，意味著對某些記錄只需要查詢一次，而在數據庫中做關聯，則可能需要反復地訪問這些記錄。

6、這樣做相當于在應用中實現了哈希關聯，而不是使用MySQL的嵌套循環關聯。

使用場景：

1、應用能夠方便地緩存單個查詢的結果。

2、數據分布于多個MySQL服務器上。

3、能夠使用IN()的方式代替關聯查詢。形如 select xxx from x where id in (select..) 這樣的子查詢。

4、當查詢中使用同一個數據表。自關聯。

分解關聯查詢舉例：?

SELECT * FROM tagJOIN tag_post ON tag_post.tag_id = tag.idJOIN post ON tag_post.post_id = post.id WHERE tag.tag = 'mysql';

可以分解為：

SELECT * FROM tag WHERE tag = 'mysql'; SELECT * FROM tag_post WHERE tag_id = 1234; SELECT * FROM post WHERE post.id IN(123, 456, 567, 9098, 8904);

三、查詢執行的工作原理

本節介紹MySQL是如何優化和執行查詢的，這有助于優化具體的查詢語句。

這是MySQL在收到查詢請求到返回結果的整個流程圖，以下是各個步驟的概括性描述：

1、客戶端發送一條查詢請求。

2、MySQL服務器先檢查查詢緩存，如果命中了緩存，則立刻返回緩存中的結果，否則進入下一步。

3、服務器進行SQL解析，預處理，再由優化器生成執行計劃。

4、MySQL根據優化器生成的執行計劃，調用存儲引擎的API 來執行查詢。

5、返回結果給客戶端。

其實，上面的每一步都比想象要復雜，其中，查詢優化器是重難點。另外還有一些例外情況，例如，當查詢使用綁定變量后，執行路徑會有所不同。

3.1 MySQL 客戶端/服務器通信協議

MySQL客戶端和服務器之間的通信協議是“半雙工”的，也就是說，在任何一個時刻，兩個動作不能同時發生。要么是由服務器向客戶端發送數據，要么是由客戶端向服務器發送數據。

這種協議讓MySQL通信簡單快速，但也會有一些限制，例如，無法進行流量控制。一旦一端開始發送消息，另一端要接收完整個消息才能響應它。就像來回拋接球的游戲。

客戶端用一個單獨的數據包將查詢傳給服務器。這也是為什么當查詢的語句很長的時候，參數max_allowed_packet 就特別重要了。一旦客戶端發送了請求，它能做的事情就只是等待結果了。但是，服務器響應給用戶的數據通常很多，由多個數據包組成。客戶端必須完整地接收整個返回結果，中間無法中斷。

3.2 查詢狀態

對于一個MySQL連接，或者說一個線程，任何時刻都有一個狀態，該狀態表示了MySQL當前正在做什么。

最簡單的查詢狀態的方法是：

SHOW FULL PROCESSLIST; -- 結果的 Command 列就表示當前的狀態

常見狀態：

Sleep：線程正在等待客戶端發送新的請求。

Query：線程正在查詢或正在將結果發送給客戶端。

Locked：在MySQL服務器層，該線程正在等待表鎖。在存儲引擎級別實現的鎖，例如InnoDB的行鎖，并不會體現在線程狀態中。對于MyISAM來說，這是一個非常典型的狀態，但在其他沒有行鎖的引擎中也經常會出現。

Analyzing and statistics：線程正在收集存儲引擎的統計信息，并生成查詢的執行計劃。

Copying to tmp table [on disk]：線程正在執行查詢，并且將其結果集都復制到一個臨時表中，這種狀態一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果這個狀態后面還有 “on disk”標記，那表示MySQL正在將一個內存臨時表放到磁盤上。

Sorting result：線程正在對結果集進行排序。

Sending data：可能表示多種情況，線程可能在多個狀態之間傳送數據，或者在生成結果集，或者在向客戶端發送數據。

3.3 查詢緩存

在解析一個查詢語句之前，如果查詢緩存是打開的 ，那么MySQL會優先檢查這個查詢是否命中查詢緩存中的數據。

這個檢查是通過一個對大小寫敏感的哈希查找實現的。即使只有一個字節不同，那也不會匹配緩存結果。

如果當前的查詢恰好命中查詢緩存，那么在返回查詢結果之前MySQL會檢查一次用戶權限。這個操作仍然無須解析查詢SQL語句，查詢緩存中已經存放了當前查詢需要訪問的表信息。如果權限無問題，MySQL就會直接返回結果，查詢不會解析，不會生成執行計劃，SQL不會被執行。

3.4?查詢優化處理

該步驟將SQL轉換為一個執行計劃，包括多個子階段：解析SQL、預處理、優化SQL執行計劃。MySQL會依照這個執行計劃和存儲引擎進行交互。

3.4.1 語法解析和預處理

首先，MySQL通過關鍵字將SQL語句進行解析，并生成一棵對應的“解析樹”。這一步驟主要是通過解析器和預處理器兩個關鍵性組件驗證一系列查詢語句的合法性。

解析器將使用MySQL語法規則驗證和解析查詢。例如，驗證關鍵字，包括正確性、順序等，引號是否前后正確匹配等。

預處理器則根據一些MySQL規則進一步檢查解析樹是否合法。例如，檢查數據表和數據列是否存在，解析名字和別名。

3.4.2 查詢優化器

語法解析預處理執行完成后，MySQL就會將解析樹交給優化器，由它生成執行計劃。

注意，一條查詢可以有很多種執行方式，最后都返回相同的結果，優化器的作用就是找到這其中最好的執行計劃。目前的MySQL都是使用基于計算成本的優化器。

它將嘗試預測一個查詢使用某種執行計劃時的成本，并選擇其中成本最小的一個。最初，成本的最小單位是隨機讀取一個4K數據頁的成本，后來，成本計算公式變得更加復雜、合理，并引入了一些“因子”來預估某些操作的代價。

可以通過查詢當前會話的 Last_query_cost 的值來得知MySQL計算當前查詢的成本。

上圖表示MySQL的優化器認為大約需要做1040個數據頁的隨機查找才能完成上面的查詢。

它是由一系列統計信息計算得來的：每個表或索引的頁面個數、索引的基數（索引中不同值的數量）、索引和數據行的長度、索引分布情況等。優化器在評估成本的時候并不考慮任何層面的緩存，它假設讀取任何數據都需要一次磁盤IO。

很多原因會導致MySQL優化器選擇錯誤的執行計劃

1、統計信息不準確。MySQL依賴存儲引擎提供的統計信息來評估成本，但有的存儲引擎提供的信息是準確的，有的可能偏差很大。例如，InnoDB因為其MVCC（多版本并發控制）的架構，并不能維護一個數據表的行數的精確統計信息。

2、執行計劃中的成本估算不等同于實際執行的成本。即使統計信息精確，優化器給出的執行計劃也可能不是最優的。

3、成本最優并不是時間最短。由于MySQL是基于“成本模型”擇優執行計劃，因此，有時候這樣的執行計劃并不一定是最快的，因此這樣的模型也并不是最完美的模型。

4、MySQL從不考慮其他并發執行的查詢，這可能會影響到當前查詢的速度。

5、MySQL也并不是任何時候都是基于成本的優化。有時候會基于一些固定的規則，例如，如果存在全文搜索的MATCH()子句，則在存在全文索引的時候就使用全文索引。即使有時候使用別的索引或WHERE條件可以遠比這種方式更快，MySQL也仍然會使用對應的全文索引。

6、MySQL不會考慮不受其控制的操作成本，例如執行存儲過程或用戶自定義的函數的成本。

7、優化器有時候無法估算所有可能的執行計劃，所以它可能錯過實際上最優的執行計劃。

MySQL優化器是一個非常復雜的部件，它使用了很多優化策略生成一個最優的執行計劃。這些優化策略分為兩類：靜態優化、動態優化。

靜態優化：可以直接對解析樹進行分析，完成優化。例如，優化器可以通過一些簡單的代數變換將WHERE 條件轉換為另一種等價形式。靜態優化不依賴于特定的數值（如WHERE中帶入的一些常量等），靜態優化在第一次完成后就一直有效。即使使用不同的參數重復執行查詢也不會發生變化。可以認為是一種“編譯時優化”。

動態優化：和查詢上下文有關，也可能和很多其他因素有關，例如WHERE條件中的取值、索引中條目對應的數據行數等。這需要在每次查詢的時候重新評估，有時候甚至在查詢的執行過程中也會重新優化，可以認為是“運行時優化”。

下面是一些MySQL能夠處理的常見優化場景：

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

2、將外連接轉化為內連接。并不是所有的OUTER JOIN 語句都必須以外連接的方式執行。諸多因素，例如WHERE條件、庫表結構都可能會讓外連接等價于一個內連接。MySQL能識別這點并重寫查詢，讓其可以調整關聯順序。

3、使用等價變換規則。MySQL可以使用一些等價變換規則來簡化并規范表達式。它會合并和減少一些比較，還可以移除一些恒成立或恒不成立的判斷。例如，(1 = 1 AND a > 5)將被改寫為a > 5。類似的，還有(a < b AND b = c) AND a = 5，會被改寫為 b > 5 AND b = c AND a = 5。這些規則對于我們編寫條件語句很有用。

4、優化COUNT()、MIN()、MAX()。索引和列是否可以為空通常可以幫助MySQL優化這類表達式。例如，找到某一列的最小值，只需要查詢對應B-Tree索引最左端的記錄，MySQL可以直接獲取索引的第一行記錄。在B-Tree索引中，優化器會將這個表達式MIN()當做一個常數對待，MAX()也是類似的。如果MySQL使用這種類型的優化，那么在EXPLAIN 中就可以看到“Select tables optimized away”，它表示優化器已經從執行計劃中移除了該表，并以一個常數取而代之。

5、預估并轉化為常數表達式。當MySQL檢測到一個表達式可以轉化為常數的時候，就會一直把該表達式作為常數進行優化處理。例如，一個用戶自定義變量在查詢中沒有發生變化時，就可以轉化為一個常數。數學表達式就是一個典型的例子。另外，剛剛提到的在索引列上查詢MIN、MAX，甚至是主鍵或唯一鍵查找語句，如果WHERE中使用了該類索引的常數條件，那么MySQL可以在查詢開始階段就先找到這些值，這樣優化器就能夠知道并轉化為常數表達式。例如：

詳解：MySQL分兩步執行上面的查詢（EXPLAIN的兩行輸出）。第一步從film中找到需要的行。因為在film_id上有主鍵索引，所以MySQL優化器知道這只會返回一條數據，優化器在生成執行計劃的時候，就已經通過索引信息知道將返回多少行數據。因為優化器已經明確知道有多少個值（WHERE條件中的值）需要做索引查詢，所以訪問類型type = const。

第二步，MySQL將第一步中返回的film_id列當做一個已知取值的列來處理。因為優化器清楚在第一步執行完成后，該值就會是明確的了。因此，和第一步中一樣，使用film_actor字段對表的訪問類型也是const。

6、覆蓋索引掃描。當索引中的列包含所有查詢中需要使用的列時，MySQL就可以使用索引返回需要的數據，而無需查詢對應的數據行。

7、子查詢優化。MySQL在某些情況下可以將子查詢轉化為一種更高效的形式，從而減少多個查詢多次對數據的訪問。

8、提前終止查詢。在發現已經滿足查詢需求時，MySQL總是能立刻終止查詢。一個典型是，使用了LIMIT子句。除此之外，MySQL還有幾種情況，例如發現一個不成立的條件，MySQL就會立刻返回空結果。如果發現某些特殊條件，則會提前終止查詢。當存儲引擎需要檢索“不同取值”或判斷存在性的時候。例如：

這個查詢會過濾掉所有有演員的電影。當查詢發現電影中只要有一個演員，就會繼續查詢下一條數據。類似這種“不同值/不存在”的優化一般可以用于DISTINCT、NOT EXIST()或者LEFT JOIN 類型的查詢。

7、等值傳播。如果兩個列的值通過等式關聯，那么MySQL能夠把其中一個列的WHERE條件傳遞到另一個列上。例如：

這里使用了film_id字段進行等值關聯，MySQL會知道這里的WHERE子句不僅適用于film表，同樣也適用于film_actor表。

8、列表IN()的比較。在很多數據庫系統中，IN()完全等同于多個OR條件的子句，因為這兩者是完全等價的。但MySQL中二者并不等價。MySQL會將IN()列表中的數據先進行排序，然后通過二分查找的方式來確定列表中的值是否滿足條件，時間復雜度是O(logn)，而OR 的操作時間復雜度是O(n)，因此，對于IN()列表中有大量取值的時候，MySQL的處理速度將會更快。

3.4.3 數據和索引的統計信息

MySQL架構由多個層次組成。在服務器層有查詢優化器，卻沒有保存數據和索引的統計信息。統計信息由存儲引擎實現，不同的存儲引擎可能會存儲不同的統計信息。某些引擎，例如Archive引擎，則根本沒有存儲任何統計信息。

因為服務器層沒有任何統計信息，所以MySQL查詢優化器在生成查詢的執行計劃時，需要向存儲引擎獲取相應的統計信息。

存儲引擎提供給優化器統計信息，包括每個表或者索引有多少個頁面、每個表的每個索引的基數是多少、數據行和索引長度、索引分布信息等。優化器根據這些信息選擇一個最優的執行計劃。

3.4.4 MySQL如何執行關聯查詢

MySQL中“關聯”一詞包含的意義比一般意義上理解的更廣泛。認為任何一個查詢都是一次“關聯”，而并不僅僅是一個查詢要到兩個表匹配才叫關聯。所以在MySQL中，每一個查詢、每一個片段（包括子查詢，甚至是單邊的select）都可能是關聯。

對于UNION查詢，MySQL先將一系列的單個查詢結果放到一個臨時表中，然后再重新讀出臨時表數據來完成UNION查詢。

在MySQL的概念中，每個查詢都是一次關聯，所以讀取結果臨時表也是一次關聯。

目前MySQL關聯執行的策略很簡單：MySQL對任何關聯都執行嵌套循環關聯操作，即MySQL先在一個表中循環取出單條數據，然后再嵌套循環到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止。然后根據各個表匹配的行，返回查詢中需要的各個列。MySQL會嘗試在最后一個關聯表中找到所有匹配的行，如果最后一個關聯表無法找到更多的行，MySQL就會返回上一層關聯表，看是否能夠找到更多的匹配記錄，以此類推迭代執行。

SELECT tb1.col1, tb2.col2 FROM tb1 INNER JOIN tb1 USING(col3) WHERE tb1.col1 IN(5, 6);

從本質上來說，MySQL對所有的類型的查詢都以同樣的方式運行。例如，MySQL在from子句中遇到子查詢時，先執行子查詢并將結果放到一個臨時表中，然后將這個臨時表當做一個普通表對待。注意：MySQL 的臨時表是沒有任何索引的，在編寫復雜的子查詢和關聯查詢的時候需要注意這一點，對UNION查詢也是一樣。

MySQL在執行UNION 查詢時也使用類似的臨時表，在遇到右外連接的時候，MySQL將其改寫成等價的左外連接。簡而言之，當前版本的MySQL會將所有查詢類型都轉換成類似的執行計劃。

3.4.5 執行計劃

MySQL生成查詢的一顆指令樹，然后通過存儲引擎執行完成這顆指令樹并返回結果。最終的執行計劃包含了重構查詢的全部信息。執行 SHOW WARNINGS，就可以看到重構出的查詢。

任何多表關聯都可以使用一顆樹表示，例如，在MySQL中，由于MySQL總是使用“嵌套循環關聯”的方式執行多表查詢，因此它的樹結構應該是如下圖所示的，是一顆左側深度優先樹：

3.4.6 關聯查詢優化器

MySQL優化器最重要一部分就是關聯查詢優化，它決定了多個表關聯時的順序。通常多表關聯，可以有多種不同的關聯順序獲得相同的結果。優化器會評估不同順序時的成本，最后選擇一個代價最小的關聯順序。

重新定義關聯順序是優化器非常重要的一部分功能。不過有時候，優化器給出的并不是最優的關聯順序，這時可以使用STRAIGHT_JOIN關鍵字重寫查詢，讓優化器按照你認為的最優關聯順序執行。絕大多數情況，優化器做出的選擇都比較合理。

關聯優化器會嘗試在所有的關聯順序中選擇一個成本最小的來生成執行計劃。如果可能，優化器會遍歷每一個表，然后逐個做嵌套循環計算每一顆可能的執行計劃樹的成本，最后返回一個最優的執行計劃。

如果有超過n個表的關聯，需要檢查n的階乘種關聯順序。稱之為執行計劃的“搜索空間”，而這個搜索空間的增長速度非常快，例如，如果10表關聯，共有3628800種。當搜索空間非常大的時候，優化器就不可能逐個評估關聯順序的成本，那么優化器就會使用“貪婪”搜索的方式查找“最優”的關聯順序。當需要關聯的表數量超過optimizer_search_depth的限制，就會選擇“貪婪搜索”模式。

有時候，查詢中的順序是存在一定的固定的，這時優化器可以根據這些固定的查詢規則，大幅減少搜索空間。例如，左連接、相關子查詢。這是因為，后面表的查詢需要依賴前面表的查詢結果。這種依賴關系通常可以幫助優化器大大減少需要掃描的執行計劃數量。

3.4.7 排序優化

當不能使用索引生成排序結果的時候，MySQL就需要自己進行排序。如果數量小，則在內存中進行；如果數量大則需要使用磁盤。不過，MySQL將這個過程統一成為“filesort”——文件排序，即使完全是內存排序不需要任何磁盤文件時也是如此。

如果需要排序的數據量小于“排序緩沖區”，MySQL使用內存進行“快速排序”操作。

如果內存不夠排序，那么MySQL會將數據分塊，對每個獨立的塊進行“快速排序”進行排序，并將各個塊的排序結果存放在磁盤上，然后將各個排好序的塊進行合并（merge），最后返回排序結果。

MySQL有兩種排序算法：

1、兩次傳輸排序（舊版本使用）：讀取行指針和需要排序的字段，對其進行排序，然后再根據排序結果讀取所需要的行。這需要進行兩次數據傳輸，即需要從數據表中讀取兩次數據。第二次讀取數據的時候，因為是讀取排序后的所有記錄，這會產生大量的隨機IO，所以兩次數據傳輸的成本非常高。

2、單次傳輸排序（新版本使用）：先讀取查詢所需的所有列，然后再根據給定列進行排序，最后直接返回排序結果。MySQL4.1 引入，對IO密集型應用效率非常高。其優點是無需任何隨機IO。缺點是，如果返回的列非常大、非常多，會額外占用大量空間，而這些列對排序操作本身沒有任何作用。當查詢需要所有列的總長度不超過參數“max_length_for_sort_data”，MySQL就會使用“單次傳輸排序”。

MySQL在進行文件排序時需要使用的臨時存儲空間可能會非常大。因為在排序時，MySQL會對每個排序記錄分配一個足夠長的定長空間來存放。如果表結構設計的不合理，排序消耗的臨時空間就可能比磁盤上的原表大很多倍。

關聯查詢時如果需要排序，MySQL會分兩種情況來處理。

如果ORDER BY 子句的所有列都來自關聯的第一個表，那么在關聯處理第一個表的時候就進行文件排序。EXPLAIN的 Extra 會出現“Using filesort”。

除此之外的所有情況，MySQL都會將關聯結果放入一個臨時表中，然后在所有關聯都結束后，再進行文件排序。EXPLAIN 的 Extra 會顯示“Using temporary；Using filesort”。

3.5 查詢執行引擎

在解析和優化階段，MySQL將生成查詢對應的執行計劃。MySQL的查詢執行引擎則根據這個執行計劃來完成整個查詢。

執行計劃是一種數據結構，而不是和很多其他的關系型數據庫那樣會生成對應的字節碼。

查詢執行階段并不復雜，MySQL只是簡單地根據執行計劃給出的指令逐步執行。在執行過程中，有大量的操作需要通過調用存儲引擎實現的接口來完成，這些接口稱為“handler API”。

查詢中的每一個表由一個handler的實例表示。實際上，MySQL在優化階段就為每個表創建了一個handler實例，優化器根據這些實例的接口可以獲取表的相關信息，包括表的所有列名、索引統計信息等。

注意，并不是所有的操作都由handler完成。例如，當MySQL需要進行表鎖的時候。handler可能會實現自己級別的、更細粒度的鎖，如InnoDB就實現了自己的行基本鎖。但這并不能代替服務器層的表鎖。因為所有存儲引擎共有的特性則由服務器層實現，比如時間日期函數、視圖、觸發器等。

3.6 返回結果給客戶端

查詢執行的最后一個階段是將結果返回給客戶端。即使查詢不需要返回結果給客戶端，MySQL仍然會返回這個查詢的一些信息，比如影響的行數。

如果查詢可以被緩存，那么MySQL在這個階段會將結果存放到查詢緩存中。

MySQL將結果集返回給客戶端是一個增量、逐步返回的過程。例如關聯操作，一旦服務器處理完最后一個關聯表，開始生成一條結果時，MySQL就開始向客戶端逐步返回結果集了。

這樣處理的好處有兩點：

1、服務器端無需存儲太多的結果，也就不會因為要返回太多結果而消耗太多內存。

2、可以讓MySQL客戶端第一時間獲得返回的結果。

結果集中的每一行都會以一個滿足MySQL客戶端/服務器通信協議的封包發送，再通過TCP協議進行傳輸。在TCP傳輸的過程中，可能對MySQL的封包進行緩存然后批量傳輸。

四、查詢優化的局限性

4.1 關聯子查詢

MySQL 的子查詢實現非常糟糕，其中 WHERE 子句中的 IN() 子查詢尤為突出。

如果希望查詢 actor_id = 1的演員參演的所有電影：

SELECT * FROM film WHERE film_id IN(SELECT film_id FROM film_actor WHERE actor_id = 1 );

按照正常邏輯，我們一般認為，先執行 IN() 中的子查詢，然后再由查詢出的 film_id 列表來作為篩選，過濾 film 中的數據。

但在?MySQL 5.6 之前不是這樣做的。MySQL會將相關的外層表壓到子查詢中，官方的描述是“rewrites a noncorrelated subquery as a correlated subquery” ，如下所示：

SELECT * FROM film WHERE EXISTS(SELECT * FROM film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id );

這時，對表 film_actor 的子查詢就需要根據 film_id 字段來關聯外部表 film，因為需要 film_id 字段，所以MySQL認為無法先執行這個子查詢。EXPLAIN 的 select_type 會出現 “DEPENDENT SUBQUERY” 關聯的子查詢。

這樣一來，本可以僅執行一次的子查詢，卻由于關聯的原因，MySQL 會對 film 進行全表掃描，然后根據返回的 film_id 逐個執行子查詢！

一種重構的方法是使用 JOIN ：

SELECT * FROM filmINNER JOIN film_actor USING(film_id) WHERE actor_id = 1;

另一種重構的方式是，保持原來使用子查詢的方式，但是通過GROUP_CONCAT() 在IN() 中構造一個由逗號分隔的列表。有時這比使用關聯的方式更快：

SELECT * FROM film WHERE film_id IN(SELECT GROUP_CONCAT(film_id) FROM film_actor WHERE actor_id = 1 );

但在MySQL 5.6 增加了一個新的特性，叫做 Subquery materialization——物化子查詢。可以參考：使用實體化優化子查詢。

materialization 意為“物化、實體化、具體化”，Subquery materialization 就是將子查詢實體化為一張表。簡單來說，MySQL5.6 加入的這個實體化子查詢的優化器特性，是將子查詢的結果組織成一張臨時表存儲到內存中，如果這個臨時表太大，還會退居磁盤。這樣一來，子查詢就可以僅執行一次，在后續執行過程中，直接引用臨時表中的數據即可。同時，優化器還可以在這個臨時表上使用哈希索引以盡可能提高性能。

另外，一般這類查詢都可以通過左外連接來重寫：

EXPLAIN SELECT student.* FROM student LEFT JOIN teacherON teacher.`class_id` = student.`class_id` WHERE lesson = 1;

由于MySQL并不是總是使用實體化來優化這類子查詢，因此，優化器有時候還是會以關聯子查詢的形式去執行查詢，所以，使用 LEFT JOIN 是一個不錯的選擇。

其實在執行計劃中最后一步的 Extra 中不論是物化子查詢還是 LEFT JOIN 查詢，都出現了“Using join buffer”，而且select_type 也都是兩個 SIMPLE，換句話說，物化子查詢實際上就是將子查詢作為一個臨時表，然后執行關聯操作，這和直接使用LEFT JOIN 進行關聯本質上是相同的，雖然物化子查詢比直接LEFT JOIN 的方式多了一步“內存物化”的操作，但卻可以使用哈希索引提高子查詢性能，因此，究竟哪種改寫方式更好，還需要以實際執行速度為準。

4.2 UNION 的限制

MySQL有時候無法將外層限制條件“下推”到內層，使得原本能夠限制部分返回結果的條件無法應用到內層查詢的優化上。

如果希望UNION 的各個子句能夠根據LIMIT只取部分結果集，或者希望能夠先排好序再進行合并的話，就需要在UNION的各個子句中分別使用這些子句。

4.3 等值傳遞

某些時候，等值傳遞會有額外消耗。例如，有一個非常大的 IN() 列表，而MySQL優化器發現WHERE 、ON 或 USING 子句，將這個列表的值和另一個表的某個列相關聯，那么優化器會將IN()列表都復制應用到關聯的各個表中。

因為各個表新增了過濾條件，優化器通常可以更高效地從存儲引擎過濾記錄。但是如果IN()列表非常大，則會導致優化和執行都非常慢。這種情況可能需要通過修改代碼來繞過它。

4.4 并行執行

MySQL無法利用多核特性來并行執行查詢。其他的關系型數據庫能夠提供這個特性，但MySQL做不到。不要嘗試尋找并行執行查詢的方法。

4.5 哈希關聯

MySQL并不支持哈希關聯，MySQL的所有關聯都是嵌套循環關聯。

五、查詢優化器的提示

MySQL提供了提示（hint）功能，可以在查詢中加入相應的提示，來控制最終的執行計劃。常用的有如下一些。

HIGH_PRIORITY 和 LOW_PRIORITY：這個提示告訴MySQL，當多個語句同時訪問某一個表的時候，哪些語句優先級高或低些。HIGH_PRIORITY可以用于SELECT和INSERT，LOW_PRIORITY在SELECT INSERT UDATE DELETE中都可以使用。

DELAYED：這個提示對INSERT 和 REPLACE有效。該提示會讓語句立刻返回，將即將入庫的數據先暫存緩沖區，待表空閑時批量寫入。一般可以用于日志系統。但并不是所有存儲引擎都支持，并且該提示會導致LAST_INSERT_ID()無法正常工作。

STRAIGHT_JOIN：該提示用于SELECT關鍵字之后，也可以放置在任何兩個關聯表的名字之間。第一個用法是讓查詢中所有的表按照在語句中出現的順序進行關聯。第二個用法是為了固定前后兩個表的關聯順序。

SQL_SMALL_RESULT和SQL_BIG_RESULT：用于SELECT中。SMALL告訴優化器結果集很小，可以將結果集放在內存中的索引臨時表，避免排序。如果是BIG，則告訴優化器，結果集很大，建議使用磁盤臨時表做排序操作。

SQL_BUFFER_RESULT：告訴優化器將查詢結果放入到一個臨時表，然后盡可能快的釋放表鎖。

SQL_CACHE和SQL_NO_CACHE：告訴MySQL這個結果集是否應該緩存在查詢緩存中。第七章詳細介紹。

SQL_CALC_FOUND_ROWS：讓MySQL返回的結果集包含更多信息。查詢中使用它，讓MySQL計算除去LIMIT子句后這個查詢要返回的結果集的總數，而實際上只返回LIMIT要求的結果集。可以通過函數FOUNT_ROW()獲取這個值。（后面優化部分有介紹）

FOR UPDATE和LOCK IN SHARE MODE：這不是真正的優化器提示。它們主要控制SELECT語句的鎖機制，但只對實現了行級鎖的存儲引擎有效，如InnoDB。

USE INDEX 、IGNORE INDEX 和 FORCE INDEX：告訴優化器使用或不使用哪些索引來查詢記錄。

optimizer_search_depth：控制優化器在窮舉執行計劃的限度。如果查詢長時間處于“Statistics”狀態，可以考慮降低該參數。

optimizer_prune_level：該參數默認打開，讓優化器會根據需要掃描的行數來決定是否跳過某些執行計劃。

optimizer_switch：包含了一些開啟/關閉優化器特性的標志位。

六、優化特定類型的查詢

6.1 優化COUNT()查詢

COUNT()是一個非常特殊的函數，有兩種非常不同的作用：它可以統計某個列值的數量，也可以統計行數。

統計列值時要求該列是非空的（不統計NULL）。

統計結果集的行數時，當MySQL確認括號內的表達式值不可能為空時，實際上就是在統計行數。最簡單的就是COUNT(*)。這里的通配符“*”并不會擴展成所有的列，實際上，它會忽略所有的列值而直接統計所有的行數。

最常見的錯誤是，在COUNT的括號內指定了一個列，卻希望統計結果集的行數。如果希望知道的是結果集的行數，最好使用COUNT(*)，這樣寫意義清晰，性能也會很好。

對于MyISAM，一個誤解是：MyISAM的COUNT()總是很快。但是有前提條件：即只要沒有任何WHERE條件的COUNT(*)才非常快。在MyISAM中，如果MySQL知道某列col不可能為NULL，那么MySQL內部會將COUNT(col)表達式優化為COUNT(*)。

當統計帶WHERE子句的結果集的行數，可以是統計某個列值的數量時，MyISAM的COUNT()和其他存儲引擎沒有任何不同。

利用MyISAM表COUNT(*)全表非常快的特性，優化下面的查詢：

SELECT COUNT(*) FROM city WHERE id > 5;

這個SQL無法利用MyISAM的這個特性，但是可以通過改寫來成如下，減少掃描行數：

SELECT (SELECT COUNT(*) FROM city) - COUNT(*) FROM city WHERE id <= 5;

另一個案例，在同一個查詢中統計同一個列的不同值的數量。例如通過一個查詢返回各種不同顏色的商品的數量。有兩種寫法：

SELECT SUM(IF(color = ‘blue’, 1, 0)) AS blue, SUM(IF(color = ‘red’, 1, 0)) AS red FROM items; SELECT COUNT(color = ‘blue’ OR NULL) AS blue, COUNT(color = ‘red’ OR NULL) AS red FROM tiems;

在某些不需要精確COUNT的場景，可以使用近似值來代替。

EXPLAIN出來的優化器估算的行數就是一個不錯的近似值，執行EXPLAIN 并不需要真正地執行查詢，所以成本很低。

通常，COUNT()都需要掃描大量的行（意味著要訪問大量數據），因此很難優化。除了前面的優化方法，MySQL層還能做的就只有索引覆蓋掃描，如果這樣還不夠，那么就需要考慮修改應用架構，可以增加匯總表或者增加外部緩存系統。

那么實際上可以發現，“快速”、“精確”、“實現簡單”，三者永遠只能滿足其二，必須舍掉其中一個。

6.2 優化關聯查詢

在優化關聯查詢的時候需要注意的幾點是：

1、確保ON 或 USING子句中的列上有索引。在創建索引的時候就要考慮到關聯的順序。當表A和表B用列c關聯的時候，如果優化器的關聯順序是B、A，那么就不需要在B表的對應列上建立索引。沒有用到的索引只會帶來額外的負擔。一般來說，除非有其他原因，否則只需要在關聯順序中的第二個表的相應列上創建索引。

2、確保任何的GROUP BY 和 ORDER BY 中的表達式只涉及到一個表中的列，這樣MySQL才能有可能使用索引來優化這個過程。

3、當升級MySQL的時候需要注意：關聯語法、運算符優先級等其他可能發生變化的地方。

6.3 優化子查詢

關于子查詢優化，我們給出的最重要的優化建議就是盡可能使用關聯查詢代替。但是根據前面的介紹，“盡可能使用關聯”并不是絕對的，如果使用的是5.6或更新的版本，可以忽略子查詢的優化。

6.4 優化 GROUP BY 和 DISTINCT

在很多場景下，MySQL都使用同樣的辦法優化這兩種查詢，事實上，MySQL優化器會在內部處理的時候相互轉化這兩類查詢。他們都可以使用索引來優化，這也是最有效的優化辦法。

MySQL中，當無法使用索引的時候，GROUP BY 使用兩種策略來完成：使用臨時表或文件排序來做分組。

這部分優化其實在目前普遍 MySQL5.7 的版本已經有松散索引掃描來完成，具體參考官網：

如果需要對關聯查詢做分組（GROUP BY），并且是按照查找表中的某個列進行分組，那么通常采用查找表的標識列分組的效率會比其他列更高。

6.5 優化 LIMIT 分頁

通常使用LIMIT加上偏移量的辦法來實現系統對分頁操作的要求。配合上合適的ORDER BY 子句，如果有對應的索引，通常效率會不錯，否則，MySQL需要做大量的文件排序操作。

一個很頭疼的問題是，當偏移量非常大的時候，例如LIMIT 10000, 20這樣的查詢，這時MySQL需要查詢10020條記錄然后只返回最后20條，前面的10000條記錄都將被拋棄。

要優化這樣的查詢，要么是在頁面中限制分頁的數量，要么是優化大偏移量的性能。

優化此類分頁查詢的一個最簡單的辦法就是盡可能的使用索引覆蓋掃描，而不是查詢所有的列。然后根據需要做一次關聯操作再返回所需的列。對于偏移量很大的時候，這樣做的效率會提升非常大。

SELECT film_id, description FROM film ORDER BY title LIMIT 50, 5;

如果這個表非常大，那么這個查詢最好改寫成下面的樣子：

SELECT film.film_id, film.description FROM film INNER JOIN (SELECT film_id FROM filmORDER BY title LIMIT 50, 5 ) AS lim USING(film_id);

這里的“延遲關聯”將大大提升查詢效率，它讓MySQL掃描盡可能少的頁面。這個技術也可以用于優化關聯查詢中的LIMIT。

LIMIT和 OFFSET的問題，其實是后者的問題，它會導致掃描大量不需要的行然后再拋棄掉。如果可以使用書簽記錄上次取數據的位置，那么下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用OFFSET。

例如，如果需要按照租借記錄做翻頁，那么可以根據最新一條租借記錄向后追溯，這種做法可行是因為租借記錄的主鍵是單調增長的。

首先使用下面的查詢獲取第一組結果：

SELECT * FROM rental ORDER BY rental_id DESC LIMIT 20;

假設返回了主鍵為16049到16030的租借記錄，那么下一頁查詢就可以從16030這個點開始：

SELECT * FROM rental WHERE rental_id < 16030 ORDER BY rental_id DESC LIMIT 20;

該技術的的好處是無論翻頁到多么后面，性能都會很好。

其他優化技術還包括使用預先計算的匯總表，或者關聯到一個冗余表，冗余表只包含主鍵列和需要做排序的數據列。

6.6 優化 UNION 查詢

MySQL總是通過創建并填充臨時表的方式來執行UNION查詢。因此很多優化策略在UNION查詢中都沒法很好的使用。經常需要手動將WHERE LIMIT ORDER BY 等子句“下推”到UNION的各個子查詢中。

除非確實需要服務器消除重復的行，否則就一定要使用UNION ALL，這非常重要。如果沒有ALL關鍵字，MySQL會給臨時表加上DISTINCT選項，這會導致對整個臨時表的數據做唯一性檢查，代價非常高。即使有ALL關鍵字，MySQL仍然會使用臨時表來存儲結果。事實上，MySQL總是將結果放入臨時表，然后再讀出，再返回給客戶端。

6.7 使用用戶自定義變量

用戶自定義變量是一個容易被遺忘的MySQL特性，但是如果能夠用好，發揮其潛力，在某些場景可以寫出非常高效的查詢語句。

單純的關系查詢將所有的東西都當成無序的數據集合，并且一次性操作它們。

用戶自定義變量是一個用來存儲內容的臨時容器，在連接MySQL的整個過程都存在。可以使用如下的SET和SELECT 語句來定義他們：

然后在任何可以使用表達式的地方使用這些自定義變量：

SELECT ... WHERE col <= @last_week;

使用自定義變量的一些注意事項：

1、使用自定義變量的查詢，無法使用查詢緩存。

2、不能在使用常量或標識符的地方使用自定義變量，例如表名、列名、LIMIT子句中。

3、自定義變量的生命周期在一個連接中有效，所以不能用它們來做連接間的通信。

4、如果使用連接池或持久化連接，自定義變量可能讓看起來毫無關系的代碼發生交互（如果這樣，通常是代碼bug或連接池bug，這類情況確有發生）。

5、MySQL5.0之前的版本，是大小寫敏感的。注意版本兼容問題。

6、不能顯式地聲明自定義變量的類型，且MySQL的用戶自定義變量是一個動態類型，也就是說用戶自定義變量的類型在賦值時會隨之改變。如果你希望變量是整數類型，那么最好在初始化的時候就賦值0，浮點型就賦值0.0，字符串就賦值’’。

7、MySQL優化器在某些場景下可能會將這些變量優化掉，這可能導致代碼不按預期的方式運行。

8、賦值的順序和賦值的時加點并不總是固定的，這依賴于優化器的決定。

9、賦值符號:=的優先級非常低，所以需要注意，賦值表達式應該使用明確的括號。

10、使用未定義變量不會產生任何語法錯誤，一旦發生問題會很難排查。要避免使用未定義變量。

?

總結

以上是生活随笔為你收集整理的MySQL高级 —— 查询性能优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。