MySQL最佳化原理

說起MySQL的查詢優化，相信大家累積一堆技巧：不能使用SELECT *、不使用NULL字段、合理創建索引、為欄位選擇合適的資料類型..... 你是否真的理解這些最佳化技巧？是否理解背後的工作原理？在實際場景下效能真有提升嗎？我想未必。因而理解這些優化建議背後的原理就特別重要，希望本文能讓你重新審視這些優化建議，並在實際業務場景下合理的運用。

 

MySQL邏輯架構

#如果能在腦中建構一幅MySQL各元件之間如何協同運作的架構圖，有助於深入理解MySQL伺服器。下圖展示了MySQL的邏輯架構圖。

 

 

#MySQL邏輯架構，來自：高效能MySQL

## 

 

MySQL邏輯架構整體分為三層，最上層為客戶端層，並非MySQL所獨有，諸如：

連線處理、授權認證、安全性等功能均在這一層處理。

 

MySQL大多數核心服務都在中間這一層，包括查詢解析、分析、最佳化、

快取、內建函數(例如：時間、數學、加密等函數)。所有的跨儲存引擎的功能也在這一層實現：預存程序、觸發器、檢視等。

 

最下層為儲存引擎，其負責MySQL中的資料儲存與擷取。和

Linux下的檔案系統類似，每種儲存引擎都有其優點和缺點。中間的服務層透過API與儲存引擎通信，這些API介面屏蔽了不同儲存引擎間的差異。

 

MySQL查詢流程

我們總是希望MySQL能夠獲得更高的查詢效能，最好的方法就是弄清楚MySQL是如何最佳化和執行查詢的。一旦了解這一點，就會發現：很多的查詢最佳化工作其實就是遵循一些原則讓MySQL的優化器能夠按照預想的合理方式運作而已。

 

當傳送一個請求給MySQL的時候，MySQL到底做了些什麼呢？

 

 

MySQL查詢流程

 

#用戶端/服務端通訊協定

MySQL客戶端/服務端通訊協定是「半雙工」的：在任一時刻，要麼是伺服器向客戶端發送數據，要麼是客戶端向伺服器發送數據，這兩個動作不能同時發生。一旦一端開始

發送訊息，另一端要接收完整個訊息才能回應它，所以我們無法也無須將一個訊息切成小塊獨立發送，也沒有辦法進行流量控制。

 

客戶端用一個單獨的資料包將查詢請求傳送給伺服器，所以當查詢語句很長的時候，需要設定max_

allowed_pa​​cket參數。但是要注意的是，如果查詢實在太大，服務端會拒絕接收更多資料並拋出異常。

 

與之相反的是，伺服器回應給使用者的資料通常會很多，由多個資料包組成。但是當伺服器回應客戶端請求時，客戶端必須完整的接收整個回傳結果，而不能簡單的只取前面幾個結果，然後讓伺服器停止傳送。因而在實際開發中，盡量保持查詢簡單且只返回必需的數據，減小通信間數據包的大小和數量是一個非常好的習慣，這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一。

 

查詢快取

在解析一個查詢語句前，如果查詢快取是開啟的，那麼MySQL會檢查這個查詢語句是否會命中查詢快取中的資料。如果目前查詢恰好命中查詢快取，在檢查一次使用者權限後直接傳回快取中的結果。這種情況下，查詢不會被解析，也不會產生執行計劃，更不會執行。

 

MySQL將快取存放在一個引用表格（不要理解成table，可以認為是類似於HashMap的資料結構），透過一個雜湊值索引，這個雜湊值透過查詢本身、目前要查詢的資料庫、客戶端協定版本號等一些可能影響結果的資訊計算得來。所以兩個查詢在任何字元上的不同（例如：空格、註解），都會導致快取不會命中。

 

如果查詢包含任何使用者自訂函數、儲存函數、使用者變數、暫存資料表、mysql函式庫中的系統表，其查詢結果

都不會被快取。例如函數NOW()或CURRENT_DATE()會因為不同的查詢時間，傳回不同的查詢結果，再例如包含CURRENT_USER或CONNECION_ID()的查詢語句會因為不同的使用者而傳回不同的結果，將這樣的查詢結果快取起來沒有任何的意義。

 

既然是緩存，就會失效，那麼查詢快取何時失效呢？ MySQL的查詢快取系統會追蹤查詢中涉及的每個表​​，如果這些表（資料或結構）發生變化，那麼和這張表相關的所有快取資料都將失效。正因為如此，在任何的寫入操作時，MySQL必須將對應表的所有快取都設為失效。如果查詢快取非常大或碎片很多，這個操作可能會帶來很大的系統消耗，甚至導致系統僵死一會兒。而且查詢快取對系統的額外消耗也不僅僅在寫入操作，讀取操作也不例外：

• 任何的查詢語句在開始之前都必須經過檢查，即使這條SQL語句永遠不會命中緩存

• 如果查詢結果可以被緩存，那麼執行完成後，會將結果存入緩存，也會帶來額外的系統消耗

#基於此，我們要知道並不是什麼情況下查詢快取都會提高系統效能，快取和失效都會帶來額外消耗，只有當快取帶來的資源節約大於其本身消耗的資源時，才會為系統帶來性能提升。但要如何評估開啟快取是否能帶來效能提升是一件非常困難的事情，也不在本文討論的範疇內。如果系統確實存在一些效能問題，可以嘗試開啟查詢緩存，並在資料庫設計上做一些最佳化，例如：

• 用多個小表代替一個大表，注意不要過度設計

• 批次插入取代循環單條插入

• #合理控制快取空間大小，一般來說其大小設定為幾十兆比較適合

• 可以透過SQL_CACHE和SQL_NO_CACHE來控制某個查詢語句是否需要進行快取

最後的忠告是不要輕易打開查詢緩存，特別是寫密集型應用。如果你實在是忍不住，可以將query_cache_type設定為DEMAND，這時只有加入SQL_CACHE的查詢才會走緩存，其他查詢則不會，這樣可以非常自由地控制哪些查詢需要被緩存。

 

當然查詢快取系統本身是非常複雜的，這裡討論的也只是很小的一部分，其他更深入的話題，例如：快取是如何使用記憶體的？如何控制記憶體的碎片化？事務對查詢快取有何影響等等，讀者可以自行閱讀相關資料，這裡權當拋磚引玉吧。

 

語法解析與預處理

MySQL透過關鍵字將SQL語句進行解析，並產生一顆對應的解析樹。這個過程解析器主要透過語法規則來驗證和解析。例如SQL中是否使用了錯誤的關鍵字或是關鍵字的順序是否正確等等。預處理則會根據MySQL規則進一步檢查解析樹是否合法。例如檢查要查詢的資料表和資料列是否存在等等。

 

查詢最佳化

經過前面的步驟產生的語法樹被認為是合法的了，並且由優化器將其轉換成查詢計劃。多數情況下，一條查詢可以有很多種執行方式，最後都回傳對應的結果。優化器的作用就是找到這其中最好的執行計劃。

 

MySQL使用基於成本的最佳化器，它嘗試預測一個查詢使用某種執行計劃時的成本，並選擇其中成本最小的一個。在MySQL可以透過查詢目前會話的last_query_cost的值來得到其計算目前查詢的成本。

Mysql程式碼

1. mysql> select * from t_message limit 10;  

2. ...省略結果集

3. 
   

4. mysql> show status like 'last_query_cost';  

5. #+----
   +---- -------------+-------------+  

6. | Variable_name   | Value       |  

7. #+----------------+-------------+  

8. #| Last_query_cost | 6391.799000 |  

9. +----------------+------------ -+  

 

範例中的結果表示最佳化器認為大概要做6391個資料頁的隨機尋找才能完成上面的查詢。這個結果是根據一些列的統計資料計算得來的，這些統計資料包括：每張表格或索引的頁面個數、索引的基數、索引和資料行的長度、索引的分佈情況等等。

 

有非常多的原因會導致MySQL選擇錯誤的執行計劃，例如統計資訊不準確、不會考慮不受其控制的操作成本（使用者自訂函數、儲存過程）、MySQL認為的最優跟我們想的不一樣（我們希望執行時間盡可能短，但MySQL值選擇它認為成本小的，但成本小並不意味著執行時間短）等等。

 

MySQL的查詢最佳化器是一個非常複雜的元件，它使用了非常多的最佳化策略來產生一個最優的執行計劃：

• 重新定義表格的關聯順序（多張表關聯查詢時，不一定會按照SQL中指定的順序進行，但有一些技巧可以指定關聯順序）

• 優化MIN()和MAX()函數（找某列的最小值，如果該列有索引，只需要找出B+Tree索引最左端，反之則可以找到最大值，具體原理見下文）

• 提前終止查詢（例如：使用Limit時，查找到滿足數量的結果集後會立即終止查詢）

• 優化排序（在舊版MySQL會使用兩次傳輸排序，即先讀取行指標和需要排序的欄位在記憶體中對其排序，然後再根據排序結果去讀取資料行，而新版本採用的是單次傳輸排序，也就是一次讀取所有的資料行，然後根據給定的列排序。策略也在不斷的進化，這裡僅介紹幾個非常常用且容易理解的優化策略，其他的優化策略，大家自行查閱吧。

 

查詢執行引擎

在完成解析和最佳化階段以後，MySQL會產生對應的執行計劃，查詢執行引擎根據執行計劃給出的指令逐步執行得出結果。整個執行過程的大部分操作都是透過呼叫儲存引擎實現的介面來完成，這些介面被稱為han

dl

erAPI。查詢過程中的每一張表由一個handler實例表示。實際上，MySQL在查詢優化階段就為每一張表創建了一個handler實例，優化器可以根據這些實例的接口來獲取表的相關信息，包括表的所有列名、索引統計信息等。儲存引擎介面提供了非常豐富的功能，但其底層僅有幾十個接口，這些接口像搭積木一樣完成了一次查詢的大部分操作。

 

傳回結果給客戶端

查詢執行的最後一個階段就是將結果傳回給客戶端。即使查詢不到數據，MySQL仍然會傳回這個查詢的相關信息，例如改查詢影響到的行數以及執行時間等等。

 

如果查詢快取被開啟且這個查詢可以被緩存，MySQL也會將結果存放到快取中。

 

結果集回傳客戶端是一個增量且逐步回傳的過程。有可能MySQL在產生第一條結果時，就開始逐步到客戶端回傳結果集了。這樣服務端就無須儲存太多結果而消耗過多內存，也可以讓客戶端第一時間獲得回傳結果。需要注意的是，結果集中的每一行都會以一個滿足①中所描述的通訊協定的資料包發送，再透過TCP協定傳輸，在傳輸過程中，可能對MySQL的資料包進行快取然後批次發送。

 

回頭總結一下MySQL整個查詢執行過程，總的來說分成6個步驟：

• 客戶端向MySQL伺服器發送一則查詢請求

• 伺服器先檢查查詢快取，如果命中緩存，則立刻傳回儲存在快取中的結果。否則進入下一階段

• 伺服器進行SQL解析、預處理、再由優化器產生對應的執行計劃

• MySQL根據執行計劃，呼叫儲存引擎的API來執行查詢

• 將結果傳回給客戶端，同時快取查詢結果

##效能最佳化建議

看了這麼多，你可能會期待給出一些優化手段，是的，下面會從3個不同方面給出一些優化建議。但請等等，還有一句忠告要先送給你：不要聽信你看到的關於優化的“絕對真理”，包括本文所討論的內容，而應該是在實際的業務場景下通過測試來驗證你關於執行計劃以及回應時間的假設。

 

Scheme設計與資料類型最佳化

選擇資料類型只要遵循小而簡單的原則就好，越小的資料類型通常會更快，佔用更少的磁碟、內存，處理時所需的CPU週期也更少。越簡單的資料類型在計算時需要更少的CPU週期，例如，整數就比字元操作代價低，因而會使用整數來儲存ip位址，使用DATE

TIME來儲存時間，而不是使用字串。

 

這裡總結幾個可能容易理解錯誤的技巧：

• 通常來說把可為NULL的列改為NOT NULL不會對效能提升有多少幫助，只是如果計劃在列上建立索引，就應該將該列設為NOT NULL。

• 對

  整數型別指定寬度，例如INT(11)，沒有任何卵用。 INT使用16為儲存空間，那麼它的表示範圍已經確定，所以INT(1)和INT(20)對於儲存和計算是相同的。

• UNSIGNED表示不允許負值，大致可以使正數的上限提高一倍。例如TINYINT儲存範圍是通常來講，沒有太大的必要使用DECIMAL資料型別。即使在需要儲存財務資料時，仍然可以使用BIGINT。例如需要精確到萬分之一，那麼可以將資料乘以一百萬然後使用TIMESTAMP使用4個位元組儲存空間，DATETIME使用8個位元組儲存空間。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的範圍小得多，而且TIMESTAMP的值因時區不同而不同。

• 大多數情況下沒有使用枚舉類型的必要，其中一個缺點是枚舉的字串列表是固定的，添加和

  刪除字串（枚舉選項）必須使用ALTER TABLE（如果隻隻是在清單末端追加元素，則不需要重建表）。

• schema的欄位不要太多。原因是儲存引擎的API工作時需要在伺服器層和儲存引擎層之間通過行緩衝格式拷貝數據，然後在伺服器層將緩衝內容解碼成各個列，這個轉換過程的代價是非常高的。如果列太多而實際使用的列又很少的話，有可能會導致CPU佔用過高。

• 大表ALTER TABLE非常耗時，MySQL執行大部分

  修改表結果操作的方法是用新的結構建立一個張空表，從舊表中查將所有的資料插入新表，然後再刪除舊表。尤其當記憶體不足而表又很大，而且還有很大索引的情況下，耗時更久。當然有些奇淫技巧可以解決這個問題，有興趣可自行查閱。

建立高效能索引

索引是提高MySQL查詢效能的一個重要途徑，但過多的索引可能會導致過高的磁碟使用率以及過高的記憶體佔用，從而影響應用程式的整體效能。應盡量避免事後才想起添加索引，因為事後可能需要監控大量的SQL才能定位到問題所在，而且添加索引的時間肯定是遠大於初始添加索引所需的時間，可見索引的添加也是非常有技術含量的。

 

接下來將向你展示一系列創建高效能索引的策略，以及每個策略背後的工作原理。但在此之前，先了解一些與索引相關的演算法和資料結構，將有助於更好的理解後文的內容。

 

索引相關的資料結構與演算法

通常我們所說的索引是指B-Tree索引，它是目前關係型資料庫中查找資料最為常用且有效的索引，大多數儲存引擎都支援這種索引。使用B-Tree這個術語，是因為MySQL在CREATE TABLE或其它語句中使用了這個關鍵字，但實際上不同的儲存引擎可能使用不同的資料結構，例如InnoDB就是使用的B+ Tree。

 

B+Tree中的B是指balance，意為平衡。要注意的是，B+樹索引並不能找到一個給定鍵值的具體行，它找到的只是被查找數據行所在的頁，接著數據庫會把頁讀入到內存，再在內存中進行查找，最後得到要找的資料。

 

在介紹B+Tree前，先了解二元查找樹，它是經典的資料結構，其左子樹的值總是小於根的值，右子樹的值總是大於根的值，如下圖①。若要在這課樹中找出值為5的記錄，其大致流程：先找到根，其值為6，大於5，所以找出左子樹，找出3，而5大於3，接著找3的右子樹，總共找了3次。同樣的方法，如果找出值為8的記錄，也需要找3次。所以二元查找樹的平均查找次數為(3 + 3 + 3 + 2 + 2 + 1) / 6 = 2.3次，而順序查找的話，查找值為2的記錄，僅需要1次，但查找值為8的記錄則需要6次，所以順序查找的平均查找次數為：(1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.3次，因為大多數情況下二元查找樹的平均查找速度比順序查找要快。

 

 

二元尋找樹與平衡二元樹

 

由於二元查找樹可以任意構造，同樣的值，可以構造出如圖②的二元查找樹，顯然這棵二元樹的查詢效率和順序查找差不多。若想二元查找數的查詢效能最高，需要這棵二元查找樹是平衡的，也即平衡二元樹（AVL樹）。

 

平衡二元樹首先需要符合二元查找樹的定義，其次必須滿足任何節點的兩個子樹的高度差不能大於1。顯然圖②不滿足平衡二元樹的定義，而圖①是一課平衡二元樹。平衡二元樹的查找效能是比較高的（效能最好的是最優二元樹），查詢效能越好，維護的成本就越大。例如圖①的平衡二元樹，當使用者需要插入一個新的值9的節點時，就需要做出以下變動。

 

 

#平衡二元樹旋轉

 

透過一次左旋操作就插入後的樹重新變成平衡二元樹是最簡單的情況了，實際應用場景中可能需要旋轉多次。至此我們可以考慮一個問題，平衡二元樹的查找效率還不錯，實作也非常簡單，對應的維護成本還能接受，為什麼MySQL索引不直接使用平衡二元樹？

 

隨著資料庫中資料的增加，索引本身大小隨之增加，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存的磁碟上。這樣的話，索引查找過程中就要產生磁碟I/O消耗，相對於記憶體存取，I/O存取的消耗量要高幾個數量級。可以想像一下一棵幾百萬個節點的二元樹的深度是多少？如果將這麼大深度的一顆二元樹放磁碟上，每讀取一個節點，需要一次磁碟的I/O讀取，整個查找的耗時顯然是不能夠接受的。那麼如何減少查找過程中的I/O訪問次數呢？

 

一種行之有效的解決方法是減少樹的深度，將二元樹變成m叉樹（多路搜尋樹），而B+Tree就是一種多路搜尋樹。在理解B+Tree時，只需要理解其最重要的兩個特徵即可：第一，所有的關鍵字（可以理解為資料）都儲存在葉子節點（Leaf Page），非葉子節點（Index Page）並不儲存真正的數據，所有記錄節點都是按鍵值大小順序存放在同一層葉子節點上。其次，所有的葉子節點由指標連接。如下圖為高度為2的簡化了的B+Tree。

 

 

#簡化B+Tree

 

## #######簡化B+Tree###### ###

怎麼理解這兩個特徵？ MySQL將每個節點的大小設定為一個頁的整數倍（原因下文會介紹），也就是在節點空間大小一定的情況下，每個節點可以儲存更多的內結點，讓每個結點能索引的範圍更大更精確。所有的葉子節點使用指標連結的好處是可以進行區間訪問，例如上圖中，如果查找大於20而小於30的記錄，只需要找到節點20，就可以遍歷指針依次找到25、30。如果沒有連結指標的話，就無法進行區間查找。這也是MySQL使用B+Tree作為索引儲存結構的重要原因。

 

MySQL為何將節點大小設為頁的整數倍，這就需要理解磁碟的儲存原理。磁碟本身存取比主存慢很多，在加上機械運動損耗（特別是普通的機械硬碟），磁碟的存取速度往往是主存的幾百萬分之一，為了盡量減少磁碟I/O ，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個字節，磁碟也會從這個位置開始，順序向後讀取一定長度的資料放入內存，預讀的長度一般為頁的整數倍。

引用

頁是電腦管理記憶體的邏輯區塊，硬體及OS往往將主記憶體和磁碟儲存區分割為連續的大小相等的區塊，每個儲存區塊稱為一頁（在許多OS中，頁的大小通常為4K）。主存和磁碟以頁為單位交換資料。當程式要讀取的資料不在主記憶體時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤訊號，磁碟會找到資料的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續運行。

 

MySQL巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入。為了達到這個目的，每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也存儲在一個頁裡，加之計算機存儲分配都是按頁對齊的，就實現了讀取一個節點只需一次I/O。假設B+Tree的高度為h，一次檢索最多需要h-1I/O（根節點常駐記憶體），複雜度$O(h) = O(\log_{M}N)$。實際應用場景中，M通常較大，常常超過100，因此樹的高度一般都比較小，通常不超過3。

 

最後簡單了解下B+Tree節點的操作，在整體上對索引的維護有一個大概的了解，雖然索引可以大大提高查詢效率，但維護索引仍要花費很大的代價，因此合理的創建索引也就特別重要。

 

仍以上面的樹為例，我們假設每個節點只能儲存4個內節點。首先要插入第一個節點28，如下圖所示。

 

 

leaf page與index page都沒有滿

 

接著插入下一個節點70，在Index Page中查詢後得知應該插入到50 - 70之間的葉子節點，但葉子節點已滿，這時候就需要進行也分裂的操作，當前的葉子節點起點為50，所以根據中間值來拆分葉子節點，如下圖所示。

 

 

Leaf Page分割

 

最後插入一個節點95，這時候Index Page和Leaf Page都滿了，就需要做兩次拆分，如下圖。

 

 

#Leaf Page與Index Page分割

 

分割後最終形成了這樣一顆樹。

 

 

#最終樹

 

B+Tree為了保持平衡，對於新插入的值需要做大量的拆分頁操作，而頁的拆分需要I/O操作，為了盡可能的減少頁的拆分操作，B+Tree也提供了類似於平衡二元樹的旋轉功能。當LeafPage已滿但其左右兄弟節點沒有滿的情況下，B+Tree並不急於去做拆分操作，而是將記錄移到目前所在頁的兄弟節點上。通常情況下，左兄弟會先檢查用來做旋轉操作。就例如上面第二個範例，插入70的時候，並不會去做頁拆分，而是左旋操作。

 

 

#左旋運算

 

透過旋轉作業可以最大限度的減少頁分裂，從而減少索引維護過程中的磁碟的I/O操作，也提高索引維護效率。需要注意的是，刪除節點跟插入節點類型，仍然需要旋轉和分割操作，這裡就不再說明。

 

高效能策略

透過上文，相信你對B+Tree的資料結構已經有了大致的了解，但MySQL中索引是如何組織資料的儲存呢？以簡單的範例來說明，假如有下列資料表：

Mysql程式碼

1. #CREATE TABLE People(  

## last_name varchar(50) not null,  
#    first_name varchar(50) not null,  

o##n

 

#    gender enum(`m`,`f`) not null,  

    

key

(

);  

 

對於表中每一行數據，索引中包含了last_name、first_name、dob列的值，下圖展示了索引是如何組織資料儲存的。

  

#索引如何組織資料存儲，來自：高效能MySQL

 

1. #可以看到，索引首先根據第一個字段來排列順序，當名字相同時，則根據第三個字段，即出生日期來排序，正是因為這個原因，才有了索引的“最左原則”。
    

1、MySQL不會使用索引的情況：非獨立的欄位

「獨立的欄位」是指索引欄位不能是

表達式的一部分，也不能是函數的參數

。比如：

Mysql代碼

select * from where id + 1 = 5  

 

我們很容易看出其等價於id = 4，但MySQL無法自動解析這個表達式，

使用函數

是同樣的道理。

1.  
   2、前綴索引

如果列很長，通常可以索引開始的部分字符，這樣可以有效節約索引空間，從而提高索引效率。

 

3、多列索引和索引順序

在多數情況下，在多個欄位上建立獨立的索引並不能提高查詢效能。理由非常簡單，MySQL不知道選擇哪個索引的查詢效率更好，所以在舊版本，例如MySQL5.0之前就會隨便選擇一個列的索引，而新的版本會採用合併索引的策略。舉個簡單的例子，在一張電影演員表中，在actor_id和film_id兩個欄位上都建立了獨立的索引，然後有以下查詢：

1. Mysql程式碼

2. select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1  
    
3. 老版本的MySQL會隨機選擇一個索引，但新版本做下列的最佳化：
   Mysql程式碼

select film_id,actor_id from film_actor where actor_id = 1    

un ion
•  all   

select film_id,actor_id from film_actor where film_id = 1 and actor_id

#當出現多個索引做相交運算時（多個AND條件），通常來說一個包含所有相關欄位的索引要優於多個獨立索引。

當出現多個索引做聯合操作時（多個OR條件），對結果集的合併、排序等操作需要耗費大量的CPU和記憶體資源，特別是當其中的某些索引的選擇性不高，需要返回合併大量資料時，查詢成本更高。所以這種情況下還不如走全表掃描。

######因此explain時如果發現有索引合併（Extra欄位出現Using union），應該好好檢查一下查詢和表格結構是不是已經是最優的，如果查詢和表格都沒有問題，那隻能說明索引建的非常糟糕，應慎重考慮索引是否合適，有可能一個包含所有相關列的多列索引更適合。 ###### ######前面我們提到索引如何組織資料儲存的，從圖中可以看到多列索引時，索引的順序對於查詢是至關重要的，很明顯應該把選擇性更高的欄位放到索引的前面，這樣透過第一個欄位就可以過濾掉大多數不符合條件的資料。 ######引用###

索引選擇性是指不重複的索引值和資料表的總記錄數的比值，選擇性越高查詢效率越高，因為選擇性越高的索引可以讓MySQL在查詢時過濾掉更多的行。唯一索引的選擇性是1，這時最好的索引選擇性，效能也是最好的。

 

 

理解索引選擇性的概念後，就不難確定哪個欄位的選擇性較高了，查一下就知道了，例如：

Mysql代碼

1. SELECT * FROM payment where staff_id = 2 and customer_id = 584  

## (staff_id,customer_id)的索引還是應該顛倒一下順序？執行下面的查詢，哪個欄位的選擇性比較接近1就把哪個欄位索引前面就好。

Mysql代碼

select 

count
1. (distinct staff_id)/count(*) as staff_id_selectivity,  
          count(distinct customer_id)/count(*) as customer_id_selectivity,  

2.     #多數情況下使用這個原則沒有任何問題，但仍然注意你的數據中是否存在一些特殊情況。舉個簡單的例子，例如要查詢某個用戶群組下有過交易的用戶資訊：
3. Mysql程式碼

select user_id from trade where user_group_id = 1 and trade_amount > 0  

 

MySQL為這個查詢選擇了索引(user_group_id,trade_amount)，如果不考慮特殊情況，這看起來沒有任何問題，但實際情況是這張表的大多數資料都是從舊系統中遷移過來的，由於新舊系統的資料不相容，所以就給舊系統遷移過來的資料給了一個預設的使用者群組。在這種情況下，透過索引掃描的行數跟全表掃描基本上沒什麼區別，索引就起不到任何作用。

1.  
   推廣開來說，經驗法則和推論在多數情況下是有用的，可以指導我們開發和設計，但實際情況往往會更複雜，實際業務場景下的某有些特殊情況可能會摧毀你的整個設計。

 

4、避免多個範圍條件

實際開發中，我們會經常使用多個範圍條件，例如想查詢某個時間段內登入過的用戶：

Mys代碼

select user.* from user where login_time > '2017-04-01' anduser age between# 18 ando#  

# 

這個查詢有一個問題：它有兩個範圍條件，login_time列和age列，MySQL可以使用login_time列的索引或age列的索引，但無法同時使用它們。

 

1. 5、覆寫索引
   如果索引包含或說覆寫所有需要查詢的欄位的值，那麼就沒有必要再回表查詢，這就稱為覆蓋索引。覆寫索引是非常有用的工具，可以極大的提高效能，因為查詢只需要掃描索引會帶來許多好處：

索引條目遠小於資料行大小，如果只讀取索引，極大減少資料存取量

索引是有按照列值順序儲存的，對於I/O密集型的範圍查詢要比隨機從磁碟讀取每一行數據的IO要少的多

6、使用索引掃描來排序

• MySQL有兩種方式可以產生有序的結果集，其一是對結果集進行排序的操作，其二是依照索引順序掃描所得的結果自然是有序的。如果explain的結果中type列的值為index表示使用了索引掃描來做排序。
   
• 掃描索引本身很快，因為只需要從一條索引記錄移動到相鄰的下一筆記錄。但如果索引本身無法覆寫所有需要查詢的列，那麼就必須每掃描一筆索引記錄就回表查詢一次對應的行。這個讀取操作基本上是隨機I/O，因此按照索引順序讀取資料的速度通常要比順序地全表掃描慢。
   

在設計索引時，如果一個索引既能夠滿足排序，有滿足查詢，是最好的。

 

只有當索引的列順序和ORDER BY子句的順序完全一致，並且所有列的排序方向也一樣時，才能夠使用索引來排序結果。如果查詢需要關聯多張表，則只有ORDER BY子句所引用的欄位全部為第一張表時，才能使用索引做排序。 ORDER BY子句和查詢的限制是一樣的，都要滿足最左前綴的要求（有一種情況例外，就是最左的列被指定為常數，下面是一個簡單的示例），其他情況下都需要執行排序操作，而無法利用索引排序。

Mysql程式碼

1. // 最左邊列為常數，索引：(date,staff_id,customer_id)  

2. #select staff_id,customer_id from demo where date = '2015-06-01' order by staff_id,customer_id  

##-白#冗餘索引是指在相同的欄位上依照相同的順序建立的相同類型的索引，應盡量避免此索引，發現後立即刪除。例如有一個索引(A,B)，再建立索引(A)就是冗餘索引。冗餘索引常發生在為表格新增索引時，例如有人新建了索引(A,B)，但這個索引不是擴充已有的索引(A)。

 

大多數情況下都應該盡量擴展現有的索引而不是建立新索引。但有極少情況下出現效能方面的考慮需要冗餘索引，例如擴展已有索引而導致其變得過大，從而影響到其他使用該索引的查詢。

 

8、刪除長期未使用的索引

定期刪除一些長時間未使用過的索引是一個非常好的習慣。

 

關於索引這個主題打算就此打住，最後要說一句，索引並不總是最好的工具，只有當索引幫助提高查詢速度帶來的好處大於其帶來的額外工作時，索引才是有效的。對於非常小的表，簡單的全表掃描更有效率。對於中到大型的表，索引就非常有效。對於超大型的表，建立和維護索引的代價隨之增長，這時候其他技術也許更有效，例如分區表。最後的最後，explain後再提測是一種美德。

 

特定類型查詢最佳化

優化COUNT()查詢

COUNT()可能是被大家誤解最多的函數了，它有兩種不同的作用，其一是統計某個列值的數量，其二是統計行數。統計列值時，要求列值是非空的，它不會統計NULL。如果確認括號中的表達式不可能為空時，實際上就是在統計行數。最簡單的就是當使用COUNT(*)時，並不是我們所想像的那樣擴展成所有的列，實際上，它會忽略所有的列而直接統計所有的行數。

 

我們最常見的誤解也就在這兒，在括號內指定了一列卻希望統計結果是行數，而且還常常誤以為前者的表現會更好。但實際上並非這樣，如果要統計行數，直接使用COUNT(*)，意義清晰，且效能更好。

 

有時候某些業務場景並不需要完全精確的COUNT值，可以用近似值來代替，EXPLAIN出來的行數就是一個不錯的近似值，而且執行EXPLAIN並不需要真正去執行查詢，所以成本非常低。通常來說，執行COUNT()都需要掃描大量的行才能獲取到精確的數據，因此很難優化，MySQL層面還能做得也就只有覆蓋索引了。如果不還能解決問題，只有從架構層面解決了，例如新增總計表，或是使用

redis
• 這樣的外部快取系統。
   
• 優化關聯查詢
  在大數據場景下，表格與表格之間透過一個冗餘欄位來關聯，要比直接使用JOIN有更好的效能。如果確實需要使用關聯查詢的情況下，需要特別注意的是：############確保ON和USING字句中的欄位上有索引。在建立索引的時候就要考慮到關聯的順序。當表A和表B用列c關聯的時候，如果優化器關聯的順序是A、B，那麼就不需要在A表的對應列上建立索引。沒有用到的索引會帶來額外的負擔，一般來說，除非有其他理由，只需要在關聯順序中的第二張表的相應列上建立索引（具體原因下文分析）。 ############確保任何的GROUP BY和ORDER BY中的表達式只涉及到一個表中的列，這樣MySQL才有可能使用索引來最佳化。 ###

要理解最佳化關聯查詢的第一個技巧，就需要理解MySQL是如何執行關聯查詢的。當前MySQL關聯執行的策略非常簡單，它對任何的關聯都執行嵌套循環關聯操作，即先在一個表中循環取出單條數據，然後在嵌套循環到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為。然後根據各個表匹配的行，返回查詢中需要的各個列。

 

太抽象了？以上面的範例來說明，例如有這樣的一個查詢：

Mysql程式碼

1. #SELECT A.xx,B.yy   

#FROM A INNER JOIN B USING(c)  
WHERE A.xx IN (5,6)  

2.  

假設MySQL依照查詢中的關聯順序A、B來進行關聯操作，那麼可以用下面的偽代碼表示MySQL如何完成這個查詢：

3. Mysql程式碼

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);  
」
#while
6. (outer_row) {  

7.     inner_iterator = SELECT B.yy FROMB.WHERE B.c 

#    inner_row = inner_iterator.next;  
    while(inner_row) ,outer_row.xx];  
        inner_row = inner_iterator.next;  

10.  = outer_iterator.next;  

11. }  

 

可以看到，最外層的詢問是根據A.xx列來查詢的，A.c上如果有索引的話，整個關聯查詢也不會使用。再看內層的查詢，很明顯B.c上如果有索引的話，能夠加速查詢，因此只需要在關聯順序中的第二張表的相應列上建立索引即可。

 

優化LIMIT分頁

當需要分頁操作時，通常會使用LIMIT加上偏移量的辦法實現，同時加上適當的ORDER BY字句。如果有對應的索引，通常效率會不錯，否則，MySQL需要做大量的檔案排序操作。

 

一個常見的問題是當偏移量非常大的時候，例如：LIMIT 10000 20這樣的查詢，MySQL需要查詢10020筆記錄然後只回傳20筆記錄，前面的10,000筆都將被拋棄，這樣的代價非常高。

 

優化這個查詢一個最簡單的辦法就是盡可能的使用覆寫索引掃描，而不是查詢所有的欄位。然後根據需要做一次關聯查詢再傳回所有的列。對於偏移量很大時，這樣做的效率會提升非常大。考慮下面的查詢：

Mysql代碼

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

 

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

Mysql程式碼

1. SELECT film.film_id, film.description  

2. FROM film INNER JOIN (  

3.     SELECT film_id FROM#fil5 

   ##) AS tmp USING(film_id);  

4.  

這裡的延遲關聯將大幅提升查詢效率，讓MySQL掃描盡可能少的頁面，取得需要存取的記錄後在根據關聯列回原表查詢所需的列。

 

有時候如果可以使用書籤記錄上次取資料的位置，那麼下次就可以直接從該書籤記錄的位置開始掃描，這樣就可以避免使用OFF

SET

，比如下面的查詢：

Mysql代碼

SELECT id FROM t LIMIT 10000, 10;  

1. 改為：  

2. SELECT id FROM t WHERE id > 10000 LIMIT 10;  

3. # LIMIT 10;  

#>

其他最佳化的方法還包括使用預先計算的總表，或關聯到一個冗餘表，冗餘表中只包含主鍵列和需要做排序的列。

 

最佳化UNION

MySQL處理UNION的策略是先建立暫存表，然後再把各個查詢結果插入到暫存表中，最後再來查詢。因此很多最佳化策略在UNION查詢中都沒有辦法很好的時候。經常需要手動將WHERE、LIMIT、ORDER BY等字句「下推」到各個子查詢中，以便優化器可以充分利用這些條件先優化。

 

除非確實需要伺服器去重，否則就一定要使用UNION ALL，如果沒有ALL關鍵字，MySQL會為臨時表加上DISTINCT選項，這會導致整個臨時表的數據做唯一性檢查，這樣做的代價非常高。當然即使使用ALL關鍵字，MySQL總是將結果放入臨時表，然後再讀出，然後再傳回給客戶端。雖然很多時候沒有這個必要，例如有時候可以直接把每個子查詢的結果回傳給客戶端。

 

結語

理解查詢是如何執行以及時間都消耗在哪些地方，再加上一些最佳化流程的知識，可以幫助大家更好的理解MySQL，理解常見優化技巧背後的原理。希望本文中的原理、範例能幫助大家更好的將理論和實務連結起來，更多的將理論知識運用到實務上。

 

其他也沒啥說的了，給大家留兩個思考題吧，可以在腦袋裡想想答案，這也是大家常掛在嘴邊的，但很少有人會思考為什麼？

• 有非常多的程式設計師在分享時都會拋出這樣一個觀點：盡可能不要使用預存程序，預存程序非常不容易維護，也會增加使用成本，應該把業務邏輯放到客戶端。既然客戶端都能做這些事，那為什麼還要預存流程？

• JOIN本身也蠻方便的，直接查詢就好了，為什麼還需要檢視呢？

 

 

參考資料

[1] 姜承堯著；MySQL技術內幕-InnoDB儲存引擎；機械工業出版社，2013
[2] Baron Scbwartz 等；寧海元周振興等譯；高性能MySQL（第三版）; 電子工業出版社，2013
[3] 由 B-/B+樹看MySQL索引結構

以上是MySQL最佳化原理的詳細內容。更多資訊請關注PHP中文網其他相關文章！