說起 MySQL 的查詢優化，相信大家收藏了一堆奇技淫巧：不能使用 SELECT *、不使用 NULL 字段、合理創建索引、為字段選擇合適的數據類型.....

[[230118]]

你是否真的理解這些優化技巧？是否理解它背后的工作原理？在實際場景下性能真有提升嗎？我想未必。

因而理解這些優化建議背后的原理就顯得尤為重要，希望本文能讓你重新審視這些優化建議，并在實際業務場景下合理的運用。

MySQL 邏輯架構

如果能在頭腦中構建一幅 MySQL 各組件之間如何協同工作的架構圖，將有助于深入理解 MySQL 服務器。下圖是 MySQL 的邏輯架構圖：

MySQL 邏輯架構

MySQL 的邏輯架構整體分為三層，最上層為客戶端層，并非 MySQL 所獨有，諸如：連接處理、授權認證、安全等功能均在這一層處理。

MySQL 的大多數核心服務均在中間這一層，包括查詢解析、分析、優化、緩存、內置函數(比如：時間、數學、加密等函數)。所有的跨存儲引擎的功能也在這一層實現：存儲過程、觸發器、視圖等。

最下層為存儲引擎，負責 MySQL 中的數據存儲和提取。和 Linux 下的文件系統類似，每種存儲引擎都有其優勢和劣勢。

中間的服務層通過 API 與存儲引擎通信，這些 API 接口屏蔽了不同存儲引擎間的差異。

MySQL 查詢過程

我們總是希望 MySQL 能夠獲得更高的查詢性能，最好的辦法是弄清楚 MySQL 是如何優化和執行查詢的。

一旦理解了這一點，就會發現：很多的查詢優化工作實際上就是遵循一些原則讓 MySQL 的優化器能夠按照預想的合理方式運行。

當向 MySQL 發送一個請求的時候，MySQL 到底做了些什么呢？

MySQL 查詢過程

 

客戶端/服務端通信協議

MySQL 的客戶端/服務端通信協議是“半雙工”的：在任一時刻，要么是服務器向客戶端發送數據，要么是客戶端向服務器發送數據，這兩個動作不能同時發生。

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

客戶端用一個單獨的數據包將查詢請求發送給服務器，所以當查詢語句很長的時候，需要設置 max_allowed_packet 參數。

但是需要注意的是，如果查詢實在是太大，服務端會拒絕接收更多數據并拋出異常。

與之相反的是，服務器響應給用戶的數據通常會很多，由多個數據包組成。但是當服務器響應客戶端請求時，客戶端必須完整的接收整個返回結果，而不能簡單的只取前面幾條結果，然后讓服務器停止發送。

因而在實際開發中，盡量保持查詢簡單且只返回必需的數據，減小通信間數據包的大小和數量是一個非常好的習慣，這也是查詢中盡量避免使用 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> select * from t_message limit 10; 
...省略結果集 
 
mysql> show status like 'last_query_cost'; 
+-----------------+-------------+ 
| Variable_name   | Value       | 
+-----------------+-------------+ 
| Last_query_cost | 6391.799000 | 
+-----------------+-------------+ 

示例中的結果表示優化器認為大概需要做 6391 個數據頁的隨機查找才能完成上面的查詢。

這個結果是根據一些列的統計信息計算得來的，這些統計信息包括：每張表或者索引的頁面個數、索引的基數、索引和數據行的長度、索引的分布情況等等。

有非常多的原因會導致 MySQL 選擇錯誤的執行計劃，比如統計信息不準確、不會考慮不受其控制的操作成本（用戶自定義函數、存儲過程）。

MySQL 認為的最優跟我們想的不一樣（我們希望執行時間盡可能短，但 MySQL 值選擇它認為成本小的，但成本小并不意味著執行時間短）等等。

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

• 重新定義表的關聯順序（多張表關聯查詢時，并不一定按照 SQL 中指定的順序進行，但有一些技巧可以指定關聯順序）。
• 優化 MIN() 和 MAX() 函數（找某列的最小值，如果該列有索引，只需要查找 B+Tree 索引最左端，反之則可以找到最大值，具體原理見下文）。
• 提前終止查詢（比如：使用 LIMIT 時，查找到滿足數量的結果集后會立即終止查詢）。
• 優化排序（在老版本 MySQL 會使用兩次傳輸排序，即先讀取行指針和需要排序的字段在內存中對其排序，然后再根據排序結果去讀取數據行，而新版本采用的是單次傳輸排序，也就是一次讀取所有的數據行，然后根據給定的列排序。對于 I/O 密集型應用，效率會高很多）。

隨著 MySQL 的不斷發展，優化器使用的優化策略也在不斷的進化，這里僅僅介紹幾個非常常用且容易理解的優化策略，其他的優化策略，大家自行查閱吧。

查詢執行引擎

在完成解析和優化階段以后，MySQL 會生成對應的執行計劃，查詢執行引擎根據執行計劃給出的指令逐步執行得出結果。

整個執行過程的大部分操作均是通過調用存儲引擎實現的接口來完成，這些接口被稱為 handler API。

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

存儲引擎接口提供了非常豐富的功能，但其底層僅有幾十個接口，這些接口像搭積木一樣完成了一次查詢的大部分操作。

返回結果給客戶端

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

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

結果集返回客戶端是一個增量且逐步返回的過程。有可能 MySQL 在生成第一條結果時，就開始向客戶端逐步返回結果集了。

這樣服務端就無須存儲太多結果而消耗過多內存，也可以讓客戶端第一時間獲得返回結果。

需要注意的是，結果集中的每一行都會以一個滿足①中所描述的通信協議的數據包發送，再通過 TCP 協議進行傳輸，在傳輸過程中，可能對 MySQL 的數據包進行緩存然后批量發送。

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

• 客戶端向 MySQL 服務器發送一條查詢請求
• 服務器首先檢查查詢緩存，如果命中緩存，則立刻返回存儲在緩存中的結果，否則進入下一階段。
• 服務器進行 SQL 解析、預處理、再由優化器生成對應的執行計劃。
• MySQL 根據執行計劃，調用存儲引擎的 API 來執行查詢。
• 將結果返回給客戶端，同時緩存查詢結果。

性能優化建議

看了這么多，你可能會期待給出一些優化手段，是的，下面會從 3 個不同方面給出一些優化建議。

但請等等，還有一句忠告要先送給你：不要聽信你看到的關于優化的“絕對真理”，包括本文所討論的內容，而應該是在實際的業務場景下通過測試來驗證你關于執行計劃以及響應時間的假設。

Scheme 設計與數據類型優化

選擇數據類型只要遵循小而簡單的原則就好，越小的數據類型通常會更快，占用更少的磁盤、內存，處理時需要的 CPU 周期也更少。

越簡單的數據類型在計算時需要更少的 CPU 周期，比如，整型就比字符操作代價低，因而會使用整型來存儲 ip 地址，使用 DATETIME 來存儲時間，而不是使用字符串。

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

• 通常來說把可為 NULL 的列改為 NOT NULL 不會對性能提升有多少幫助，只是如果計劃在列上創建索引，就應該將該列設置為 NOT NULL。
• 對整數類型指定寬度，比如 INT(11)，沒有任何卵用。INT 使用 32 位（4 個字節）存儲空間，那么它的表示范圍已經確定，所以 INT(1) 和 INT(20) 對于存儲和計算是相同的。
• UNSIGNED 表示不允許負值，大致可以使正數的上限提高一倍。比如 TINYINT 存儲范圍是 -128 ~ 127，而 UNSIGNED TINYINT 存儲的范圍卻是 0 - 255。
• 通常來講，沒有太大的必要使用 DECIMAL 數據類型。即使是在需要存儲財務數據時，仍然可以使用 BIGINT。
• 比如需要精確到萬分之一，那么可以將數據乘以一百萬然后使用 BIGINT 存儲。這樣可以避免浮點數計算不準確和 DECIMAL 精確計算代價高的問題。
• 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

怎么理解這兩個特征？MySQL 將每個節點的大小設置為一個頁的整數倍（原因下文會介紹），也就是在節點空間大小一定的情況下，每個節點可以存儲更多的內結點，這樣每個結點能索引的范圍更大更精確。

所有的葉子節點使用指針鏈接的好處是可以進行區間訪問，比如上圖中，如果查找大于 20 而小于 30 的記錄，只需要找到節點 20，就可以遍歷指針依次找到 25、30。

如果沒有鏈接指針的話，就無法進行區間查找。這也是 MySQL 使用 B+Tree 作為索引存儲結構的重要原因。

MySQL 為何將節點大小設置為頁的整數倍，這就需要理解磁盤的存儲原理。磁盤本身存取就比主存慢很多，再加上機械運動損耗（特別是普通的機械硬盤），磁盤的存取速度往往是主存的幾百萬分之一。

為了盡量減少磁盤 I/O，磁盤往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個字節，磁盤也會從這個位置開始，順序向后讀取一定長度的數據放入內存，預讀的長度一般為頁的整數倍。

頁是計算機管理存儲器的邏輯塊，硬件及 OS 往往將主存和磁盤存儲區分割為連續的大小相等的塊，每個存儲塊稱為一頁（許多 OS 中，頁的大小通常為 4K）。主存和磁盤以頁為單位交換數據。當程序要讀取的數據不在主存中時，會觸發一個缺頁異常，此時系統會向磁盤發出讀盤信號，磁盤會找到數據的起始位置并向后連續讀取一頁或幾頁載入內存中，然后一起返回，程序繼續運行。

MySQL 巧妙利用了磁盤預讀原理，將一個節點的大小設為等于一個頁，這樣每個節點只需要一次 I/O 就可以完全載入。

為了達到這個目的，每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也存儲在一個頁里，加之計算機存儲分配都是按頁對齊的，就實現了讀取一個節點只需一次 I/O。

假設 B+Tree 的高度為 h，一次檢索最多需要 h-1 次 I/O（根節點常駐內存），復雜度 O(h) = O(logmN)。

實際應用場景中，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 也提供了類似于平衡二叉樹的旋轉功能。

當 Leaf Page 已滿但其左右兄弟節點沒有滿的情況下，B+Tree 并不急于去做拆分操作，而是將記錄移到當前所在頁的兄弟節點上。

通常情況下，左兄弟會被先檢查用來做旋轉操作。就比如上面第二個示例，當插入 70 的時候，并不會去做頁拆分，而是左旋操作。

左旋操作

通過旋轉操作可以最大限度的減少頁分裂，從而減少索引維護過程中的磁盤的 I/O 操作，也提高索引維護效率。

需要注意的是，刪除節點跟插入節點類似，仍然需要旋轉和拆分操作，這里就不再說明。

高性能策略

通過上文，相信你對 B+Tree 的數據結構已經有了大致的了解，但 MySQL 中的索引是如何組織數據的存儲呢？

以一個簡單的示例來說明，假如有如下數據表：

CREATE TABLE People( 
   last_name varchar(50) not null, 
   first_name varchar(50) not null, 
   dob date not null, 
   gender enum(`m`,`f`) not null, 
   key(last_name,first_name,dob) 
); 

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

索引如何組織數據存儲

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

MySQL 不會使用索引的情況：非獨立的列

“獨立的列”是指索引列不能是表達式的一部分，也不能是函數的參數。比如：

select * from where id + 1 = 5 

我們很容易看出其等價于 id = 4，但是 MySQL 無法自動解析這個表達式，使用函數是同樣的道理。

前綴索引

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

多列索引和索引順序

在多數情況下，在多個列上建立獨立的索引并不能提高查詢性能。理由非常簡單，MySQL 不知道選擇哪個索引的查詢效率更好。

所以在老版本，比如 MySQL 5.0 之前就會隨便選擇一個列的索引，而新的版本會采用合并索引的策略。

舉個簡單的例子，在一張電影演員表中，在 actor_id 和 film_id 兩個列上都建立了獨立的索引，然后有如下查詢：

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

老版本的 MySQL 會隨機選擇一個索引，但新版本做如下的優化：

• 當出現多個索引做相交操作時（多個 AND 條件），通常來說一個包含所有相關列的索引要優于多個獨立索引。
• 當出現多個索引做聯合操作時（多個 OR 條件），對結果集的合并、排序等操作需要耗費大量的 CPU 和內存資源，特別是當其中的某些索引的選擇性不高，需要返回合并大量數據時，查詢成本更高。所以這種情況下還不如走全表掃描。

select film_id,actor_id from film_actor where actor_id = 1   
 
union all  
 
select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1 

因此 explain 時如果發現有索引合并（Extra 字段出現 Using union），應該好好檢查一下查詢和表結構是不是已經是最優的。

如果查詢和表都沒有問題，那只能說明索引建的非常糟糕，應當慎重考慮索引是否合適，有可能一個包含所有相關列的多列索引更適合。

前面我們提到過索引如何組織數據存儲的，從圖中可以看到多列索引時，索引的順序對于查詢是至關重要的。

很明顯應該把選擇性更高的字段放到索引的前面，這樣通過第一個字段就可以過濾掉大多數不符合條件的數據。

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

理解索引選擇性的概念后，就不難確定哪個字段的選擇性較高了，查一下就知道了，比如：

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

是應該創建(staff_id，customer_id)的索引還是應該顛倒一下順序？執行下面的查詢，哪個字段的選擇性更接近 1 就把哪個字段索引前面就好。

select count(distinct staff_id)/count(*) as staff_id_selectivity, 
 
      count(distinct customer_id)/count(*) as customer_id_selectivity, 
 
      count(*) from payment 

多數情況下使用這個原則沒有任何問題，但仍然注意你的數據中是否存在一些特殊情況。

舉個簡單的例子，比如要查詢某個用戶組下有過交易的用戶信息：

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

MySQL 為這個查詢選擇了索引(user_group_id,trade_amount)，如果不考慮特殊情況，這看起來沒有任何問題。

但實際情況是這張表的大多數數據都是從老系統中遷移過來的，由于新老系統的數據不兼容，所以就給老系統遷移過來的數據賦予了一個默認的用戶組。

這種情況下，通過索引掃描的行數跟全表掃描基本沒什么區別，索引也就起不到任何作用。

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

避免多個范圍條件

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

select user.* from user where login_time > '2017-04-01' and age between 18 and 30; 

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

覆蓋索引

如果一個索引包含或者說覆蓋所有需要查詢的字段的值，那么就沒有必要再回表查詢，這就稱為覆蓋索引。

覆蓋索引是非常有用的工具，可以極大的提高性能，因為查詢只需要掃描索引會帶來許多好處：

• 索引條目遠小于數據行大小，如果只讀取索引，極大減少數據訪問量。
• 索引是有按照列值順序存儲的，對于 I/O 密集型的范圍查詢要比隨機從磁盤讀取每一行數據的 IO 要少的多。

使用索引掃描來排序

MySQL 有兩種方式可以生產有序的結果集：

• 對結果集進行排序的操作。
• 按照索引順序掃描得出的結果自然是有序的，如果 explain 的結果中 type 列的值為 index 表示使用了索引掃描來做排序。

掃描索引本身很快，因為只需要從一條索引記錄移動到相鄰的下一條記錄。但如果索引本身不能覆蓋所有需要查詢的列，那么就不得不每掃描一條索引記錄就回表查詢一次對應的行。

這個讀取操作基本上是隨機 I/O，因此按照索引順序讀取數據的速度通常要比順序地全表掃描要慢。

在設計索引時，如果一個索引既能夠滿足排序，又滿足查詢，是最好的。只有當索引的列順序和 ORDER BY 子句的順序完全一致，并且所有列的排序方向也一樣時，才能夠使用索引來對結果做排序。

如果查詢需要關聯多張表，則只有 ORDER BY 子句引用的字段全部為第一張表時，才能使用索引做排序。

ORDER BY 子句和查詢的限制是一樣的，都要滿足最左前綴的要求（有一種情況例外，就是最左的列被指定為常數，下面是一個簡單的示例），其他情況下都需要執行排序操作，而無法利用索引排序。

// 最左列為常數，索引：(date,staff_id,customer_id) 
select  staff_id,customer_id from demo where date = '2015-06-01' order by staff_id,customer_id 

冗余和重復索引

冗余索引是指在相同的列上按照相同的順序創建的相同類型的索引，應當盡量避免這種索引，發現后立即刪除。

比如有一個索引(A，B)，再創建索引(A)就是冗余索引。冗余索引經常發生在為表添加新索引時，比如有人新建了索引(A，B)，但這個索引不是擴展已有的索引(A)。

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

刪除長期未使用的索引

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

關于索引這個話題打算就此打住，最后要說一句，索引并不總是最好的工具，只有當索引幫助提高查詢速度帶來的好處大于其帶來的額外工作時，索引才是有效的。

對于非常小的表，簡單的全表掃描更高效。對于中到大型的表，索引就非常有效。

對于超大型的表，建立和維護索引的代價隨之增長，這時候其他技術也許更有效，比如分區表。最后的最后，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 關聯執行的策略非常簡單，它對任何的關聯都執行嵌套循環關聯操作，即先在一個表中循環取出單條數據，然后在嵌套循環到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為為止。然后根據各個表匹配的行，返回查詢中需要的各個列。

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

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

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

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6); 
outer_row = outer_iterator.next; 
while(outer_row) { 
   inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c; 
   inner_row = inner_iterator.next; 
   while(inner_row) { 
       output[inner_row.yy,outer_row.xx]; 
       inner_row = inner_iterator.next; 
   } 
   outer_row = outer_iterator.next; 
} 

可以看到，最外層的查詢是根據 A.xx 列來查詢的，A.c 上如果有索引的話，整個關聯查詢也不會使用。

再看內層的查詢，很明顯 B.c 上如果有索引的話，能夠加速查詢，因此只需要在關聯順序中的第二張表的相應列上創建索引即可。

優化 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 film ORDER BY title LIMIT 50,5 
) AS tmp USING(film_id); 

這里的延遲關聯將大大提升查詢效率，讓 MySQL 掃描盡可能少的頁面，獲取需要訪問的記錄后在根據關聯列回原表查詢所需要的列。

有時候如果可以使用書簽記錄上次取數據的位置，那么下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用 OFFSET，比如下面的查詢：

SELECT id FROM t LIMIT 10000, 10; 
改為： 
SELECT id FROM t WHERE id > 10000 LIMIT 10; 

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

優化 UNION

MySQL 處理 UNION 的策略是先創建臨時表，然后再把各個查詢結果插入到臨時表中，最后再來做查詢。

因此很多優化策略在 UNION 查詢中都沒有辦法很好的時候，經常需要手動將 WHERE、LIMIT、ORDER BY 等字句“下推”到各個子查詢中，以便優化器可以充分利用這些條件先優化。

除非確實需要服務器去重，否則就一定要使用 UNION ALL，如果沒有 ALL 關鍵字，MySQL 會給臨時表加上 DISTINCT 選項，這會導致整個臨時表的數據做唯一性檢查，這樣做的代價非常高。

當然即使使用 ALL 關鍵字，MySQL 總是將結果放入臨時表，然后再讀出，再返回給客戶端。

雖然很多時候沒有這個必要，比如有時候可以直接把每個子查詢的結果返回給客戶端。

結語

理解查詢是如何執行以及時間都消耗在哪些地方，再加上一些優化過程的知識，可以幫助大家更好的理解 MySQL，理解常見優化技巧背后的原理。

希望本文中的原理、示例能夠幫助大家更好的將理論和實踐聯系起來，更多的將理論知識運用到實踐中。

最后給大家留兩個思考題吧，可以在腦袋里想想答案，這也是大家經常掛在嘴邊的，但很少有人會思考為什么？

• 有非常多的程序員在分享時都會拋出這樣一個觀點：盡可能不要使用存儲過程，存儲過程非常不容易維護，也會增加使用成本，應該把業務邏輯放到客戶端。既然客戶端都能干這些事，那為什么還要存儲過程？
• JOIN 本身也挺方便的，直接查詢就好了，為什么還需要視圖呢？