MySQL之SQL層面性能優化

一、說明

以下測試結果經過本人實際測試得出，數據絕對真實、可靠。由於受相關硬件資源的限制，不同硬件配置、軟件平臺下可能測試數據有所不同，但數據總體趨勢是相同的。

該測試主要針對MySQL層面的優化，並沒有詳細涉及到MySQL架構、操作系統內核、硬件級別的優化。

測試結果全部通過截圖展示，結果主要包括MySQL在不同情況下的運行效率及cpu、內存、磁盤IO等一些系統參數的變化，通過Explain來進行MySQL查詢計劃來分析。

二、測試環境

· 操作系統 ： ubuntu 15.4

· 虛擬機：vmware9.0

· CPU : 4核心

· 內存：6GB

· MySQL版本：5.6.25(強烈建議使用5.6以上的版本)

· 存儲引擎：Innodb

測試表 ：

每張表後面的數字表示該數據表的數據量。

表結構如下：

user_info:

user:

說明：user_info表中的user_id 對應與user表的id字段，但並沒有外鍵關聯

三、測試報告及分析

首先來看一看沒有為表建其它索引時，MySQL的查詢執行性能

對應的explain查詢計劃如下：

說明：

畫紅線部分是在位畫紅線部分的語句執行後馬上執行的結果，為什麼第二次執行會比第一次執行快呢？因為第一次執行時，由於數據庫剛啟動 ，Innodb內存池中尚沒有數據，所以需要從磁盤中加載數據到內存池，第二次執行時，由於Innodb內存池中的LRU列表已經緩存了全表的數據，所以直接從內存池中操作數據返回即可，去掉了第一次所需要的磁盤IO，所以性能更佳。

從上面的數據也可以看出來，當數據量很大時，如5000萬數據，大於Innodb內存池的容量時。MySQL會使用swap交換內存，將部分內存中數據寫入到磁盤中的swap分區中，這樣會產生大量的磁盤IO，所以無論執行多少次，速度都很慢。

不過，我們也可以看出，MySQL對100萬內的數據進行全表掃描，性能還是可以被我們所接收的。

我們再來看下為age創建索引的情況：

對就的explain查詢計劃如下：

可以看到，此時MySQL查詢優化器選擇了age當作索引來查找數據。

對應的查詢性能如下：

大家對比數據可以看到，有索引和無索引的情況，當數據未緩存在Innodb內存池時（未畫紅線）有索引會比無索引快，但隨著數據量的增大，越不明顯。一旦數據緩存在內存池時，有索引的查詢速度是非常快的，對比數據，基本是相關一個數據級。但對於大數據量，如5000萬數據，通過age

篩選後的數據還是比較多時，Innodb會把表中的所有數據加載到內存。一旦內存池無法容納所以數據時，將會導致大量的數據頁被刷出swap分區，所以也不難理解對於5000萬數據，兩次執行的速度相差無幾。再看上圖，似乎為age添加了索引，當需要從磁盤加載數據頁時，查詢時間還是比較長，因為2秒以上的查詢都基本上是不可接受的。大家再看下用user_id作為索引的查詢性能：

為了說明問題，此處每條語句就執行一次來測試。

大家可以看到，使用user_id作為索引速度是非常快的。但為什麼選取的索引不同，得到的查詢結果會有這麼大的差別呢。下面大家請看一下user_id作為索引時的查詢計劃：

數據量為1000萬時，類型type同樣為ref，但rows這一列，age作為索引時，rows顯示為276888，而user_id列卻為1。這就是差別，由於user_id的在數據值上分佈是唯一的，也就是說通過索引查找到user_id為495000的時候，直接去加載數據行即可。

如果B+樹的高度為3，那麼通過user_id搜索成本為：6次邏輯IO+1次隨機IO

而對於通過age來索引的話，那麼產生的隨機IO就多達27萬多次了，相比起來，那肯定會慢很多。

對比全表掃描，有索引一定快嗎？

下面來看一個測試：

查詢狀態為啟用的用戶的信息。

無索引：

有索引：

對比有索引和無索引的查詢結果，可以發現有索引status比無索引status慢了很多。究其原因是什麼呢？大家先看看有索引status的explain出來的查詢執行計劃：

可以看到rows列達到了483萬，實際會更多。也就是說通過status索引，會產生幾百萬的隨機IO，(status的數值只有1或者0,在表中的數據分佈比例為9：1，所以會有大量值為1的status）。而在沒有status索引的情況下，MySQL採用全表掃描，因為全表掃描產生的是順序IO,順序IO的速度遠遠要比隨機IO要快，所以便會產生上面這樣的結果了。

通過上面的測試，到現在我們可以總結幾點：

· 全表掃描不一定會慢。

· 創建索引和使用索引時，儘量選擇過濾性最好的列，如上面的user_id。

· 唯一性太差的列，不建議創建索引，如上面的status。

· 不要對大表進行全表掃描，因為這會極容易把Innodb內存池LRU列表中的熱點數據刷回到磁盤。這會導致後續大量的查詢需要重新從磁盤中進行加載。

那麼，索引是不是創建得越多越好呢？

大家看看下面的例子

查詢地址為廣州，且手機號為136開頭的用戶信息。為address和phone分別創建索引。

explain查詢計劃如下:

看可以到key列上只有address,看來MySQL並沒有同時利用address和phone索引。

大家先不用管Extra中的Using index condition。其實在5.6之前，為多個單獨列創建索引，MySQL優化器只會選擇一個索引，即使MySQL優化器使用了Index_merge，收到的效果可能會比使用單個索引差。而在5.6之後，情況發生了變量，這個會在後續講解。

所以我們可以得出如下結論：

· 除非主要的查詢條件只有一個，否則不要為太多的單獨列創建索引。

· 儘量創建組合索引。

如何有效使用組合索引？

大家來看看使用組合索引的查詢性能：

查詢地址為廣州，年齡為25歲的用戶信息，組合索引(address,age).

explain如下：

可以看出，此時候整個組合索引是可以利用上的。

查詢1000萬和查詢5000萬的查詢性能如下：

對於上面的結果， Query_ID為1，3的查詢時間為什麼會這麼長？原因有二，一：1，3是在清除操作系統的Page Cache和重新啟動MySQL服務而測試的，Innodb內存池中尚沒有數據，需要從磁盤中加載。二、由於使用的是組合索引，會導致加載的索引頁比單索引頁要多，間接導致了磁盤驅動器單位時間發送的塊數量減少，而且存儲引擎在索引比較時，也會作更多的計算。但只要索引頁在內存中了，不管數據量是1000萬還是5000萬，執行都是很快的。

接下來看看通過組合索引，利用索引排序的測試結果:

查詢地址為上海用戶的信息，並按照年齡降序排序。索引address

explain如下：

測試結果如下：

當使用組合索引（address,age）的時候

explain如下:

測試結果如下：

在分析之前，大家看下vmstat中的情況

address索引的vmstat情況：

(address,age)索引的vmstat情況：

通過單索引address,與組合索引（address,age）的explain比較。單索引中Extra出現了using filesort信息，這表明MySQL Server在存儲引擎返回數據時，還要進行一次排序操作。而在組合索引中卻沒有出現，此處說明了這個SQL查詢利用了索引本身的排序，因為在address一定時，age是已經排序好的，這是由B-tree的性質決定的。

因此，這裡分兩種情況來比較：

· 數據頁如果是已經緩存在內存池的情況，利用組合索引來排序的性能是最好的。因為要獲取數據時，數據本身就是已經排序好了。

· 如果第一次需要從磁盤中加載數據頁到內存時，那麼組合索引的性能是比單索引差很多的，通過上面的測試結果截圖就可以看出。但為什麼會這樣呢？大家可以對比上面vmstat的截圖，通過bi選項可以發現，組合索引的bi值基本比單索引的bi值慢15-20倍。組合索引在B+樹的存儲形式如(key1,key2),(key1,key3)....(keyn,keym)。在進行二叉查找時，當key1,key2完成匹配時，才認為是成功的

。如果數據有K項，那麼組合索引就會比單索引多查詢比較K/2次；其次，組合索引的索引頁比單索引要多，這也會一定情況上增大磁盤的隨機IO。這也不難看出對於組合索引，每秒由磁盤驅動器發送的blocks數量遠比單索引要少，總體來算，加載所需時間是遠高於單索引的。

通過組合索引使用覆蓋索引

查詢年齡為25歲的用戶的姓名，地址信息。索引：(age,name,address)

該查詢可以通過下面兩種方式進行

· select * from user_info_1000w where age = 25;

explain如下：

· select name,address from user_info_1000w where age = 25

explain如下：

測試性能對比如下：

對比兩種方式的explain結果，Extra一個是null,一個是Using index。Using index說明了MySQL查詢優化器使用了覆蓋索引，即所要查詢到的結果直接通過索引就能返回。通過測試結果可以看出，充分利用到覆蓋索引，查詢性能是極佳的。但大部分時間，很多開發人員喜歡用select * 來代替select name,address等。這是一個不好的習慣，因為這樣寫是無法利用到覆蓋索引的，並且也會增加服務器發送到客戶端的數據量，延長髮送時間。因此我們應該儘量避免使用select * 。

有一點要注意的時，如有兩個索引分別為phone和age。

例如想查詢年齡小於20歲的用戶的手機號碼，SQL如下：

select phone from user_info_1000w where age < 20

上面這SQL是無法利用索引的，因為phone和age是兩個單獨的索引，他們之間是沒有任何關聯的，這點要注意一下。

通過組合索引使用ICP

查詢地址為深圳，且手機號以5213結尾用戶信息

。索引：（address,phone）%5213

explain如下：

5.6之前的版本explain如下：

性能對比如下：

5.6版本或以後:

5.6版本之前：

MySQL5.6版本開始支持ICP(Index Condition Pushdown)，通過explain中的Extra中的Using index condition可以看到查詢器使用了ICP。上面的例子中由於phone沒有使用前綴匹配，因此無法使用phone列的索引，5.6版本之前的做法是將address為shenzhen的記錄通過存儲引擎全部加載到MySQL server中，然後再通過MySQL server進行phone條件的過濾。但在5.6後的版本，MySQL通過將where條件後的索引下推到存儲引擎層。這樣數據過濾儘量在存儲引擎處進行，存儲引擎通過索引將不匹配的索引過濾掉，然後再從磁盤中加載符合條件的記錄，再返回到MySQL server。這樣做的好處是能極大減少訪問磁盤的次數，而且能減少在MySQL server層的過濾操作。

注意：ICP只支持 type為rang、ref、eq_ref、ref_or_null類型的查詢,且只支持輔助索引。

通過組合索引使用MRR

查詢年齡為60到65歲之間，且在廣州的用戶信息。索引：（age,address）

禁用MRR或使用5.6之前的版本的explain如下：

啟用MRR的explain如下：

性能結比如下：

禁用MRR或使用5.6之前的版本：

啟用MRR：

由上面explain可以看出，當看到Extra中出現Using MRR即說明MySQL查詢優化器使用了MRR（Mutil-Range Read）MRR的主要目的是減少磁盤的隨機IO。它只適用於輔助索引，通過將輔助索引放入緩存，然後根據RowId（主鍵）進行排序，然後再根據排序好的順序進行加載數據。這時候加載變成了較為順序的IO。MRR的另一個作用是可以將鍵值對拆分，如上面的查詢結果，由於沒有啟用MRR，所以只要是符合age 在60到65之間的數據都會被加載，這樣就會加載很多無用的數據進來，對性能有較大的影響。但啟用了MRR, MySQL將鍵值對拆分，如（60,guangzhou）,(61,guangzhou),(62,guangzhou)...這種形式，這樣會把大量無用的數據過濾掉，大量地減少磁盤IO。從上面測試也可以看出，啟用MRR基本比沒啟用MRR快4倍。

但大家也可以看出，一單數據全部被加載進Innodb

，再次執行時，無論是否啟動MRR，速度都是基本一樣的。因為MRR主要優化的是磁盤IO,再次執行時，沒有磁盤的IO操作，MRR也就起不了作用了。

另一個使用MRR的測試例子如下:

查詢手機號為136開頭的用戶信息，索引：phone

開啟MRR ： set @@optimizer_switch='mrr=on,mrr_cost_based=off';

使用MRR的測試結果：

沒有使用MRR的測試結果：

沒有使用MRR的vmstat監控：

使用了MRR的vmstat 監控：

可以看出，沒有使用MRR的vmstat中的bi值只有2000左右，即產生了大量的磁盤隨機IO，而使用了MRR的查詢結果，很快就把所需要的數據頁加載到內存中，因為經過了RowId的排序，產生的IO都是較為順序的。所以，產生的磁盤隨機IO越多，那麼MRR的比較就越明顯。

通過上面的對組合索引的測試，我們可以總結以下幾點：

· 創建的組合索引儘量多覆蓋掉where查詢語句的條件，儘量多並不代表全部，要根據實際情況下將哪些列創建為組合索引，儘量將過濾條件大的放在組合索引前面。

· 當SQL語句中涉及到排序時，儘量利用組合索引本身的排序，避免在內存中再進行一次排序，特別要儘量避免大數據量下的排序。

· 儘量不要使用select * ，儘量使用覆蓋索引完成對數據的查詢。

· 儘量讓SQL查詢語句利用MySQL 5.6所帶來的ICP, MRR等特性。

· 創建組合索引，儘可能多地能適合多個不同SQL的查詢。

表連接JOIN

查詢年齡為25歲到30歲的用戶的用戶名，手機號和登錄次數。索引：age

內部表聯結條件無索引，explain如下：

測試結果如下：

在看看使用左外連接的情況，explain如下：

從上面的結果可以看出，聯合條件為user_50w.id=user_info_50w.user_id。在inner join時查詢優化器使用了有索引的user_50w作為內部表。而即使我們使用left join指定user_50w為主表時，查詢優化器依然將user_50w作為內部表。這是為什麼呢？第一、因為將user_50w作為內部表時，當進行聯結的時候，可以利用到聚集索引，避免了全表掃描;第二、where過濾條件的字段是屬於user_info_50w表的，也就是說兩表通過on條件聯結得到結果後，即使user_50w存在與user_info_50w不匹配的數據，也會通過age的過濾條件被過濾掉，因為user_info_50w中的age在25和30範圍內的數據肯定是和user_50w中的表的數據匹配的。所以此時的left join收到的效果是會和join一樣的，MySQL肯定是會選擇最優的方法去進行查詢。

上面的測試在最終結果記錄數要3萬左右，即使在第一次要從磁盤加載數據頁，速度還是在毫秒級別的。

大家再來看看內外表都需要全表掃描，且表的數據量都為50萬的時候。

explain如下：

這SQL到生產環境上面肯定是毀滅性的。消耗的時間不可估量。

在聯結條件沒有使用索引的時候，如果兩表的數據量都很大，則內部會產生一個巨大的笛卡爾乘積表，根據是不可接受的。

由於MySQL的JOIN 只支持（NLJ）Nested Loop JOIN，（MySQL目前還不支持Hash Join）NLJ又分為Simple Nested Loop和Block Nested Loop。SNL是用外部表的每一條記錄與內部表進行匹配，如果聯結條件可以利用索引，那麼SNL速度是很快的。BNL，如上面的explain所示，在聯結條件沒有索引時，MySQL將外部表的一部分記錄，如10行記錄放進join buffer。然後內部表一次性與這10行記錄進行匹配，BNL在很大程度上減少內部循環的次數，在一定程度上能提高性能。除非外部表的數據量不大，否則，應儘量避免MySQL選擇SNL算法。

MySQL 5.6開始支持BKA(Batched Key Access)算法，BKA利用了MRR接口。下面看看BKA的測試結果

查詢用戶登錄地址與註冊地址一時的名字、手機號、地址、登錄次數。索引：address,id

開啟BKA:

set @@optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

explain如下：