你不得不知的 MySQL 索引優化分析

尋夢新聞LINE@每日推播熱門推薦文章，趣聞不漏接❤️

作者 | ITDragon 龍

出處 | http://www.cnblogs.com/itdragon/

為什麼你寫的 sql 查詢慢？為什麼你建的索引常失效？通過本章內容，你將學會 MySQL 性能下降的原因，索引的簡介，索引創建的原則，explain 命令的使用，以及 explain 輸出字段的意義。助你了解索引，分析索引，使用索引，從而寫出更高性能的 sql 語句。還在等什麼子？卷起袖子就是幹！

案例分析

我們先簡單了解一下非關係型數據庫和關係型數據庫的區別。

MongoDB 是 NoSQL 中的一種。NoSQL 的全稱是 Not only SQL，非關係型數據庫。它的特點是性能高，擴張性強，模式靈活，在高並發場景表現得尤為突出。但目前它還只是關係型數據庫的補充，它在數據的一致性，數據的安全性，查詢的複雜性問題上和關係型數據庫還存在一定差距。

MySQL 是關係性數據庫中的一種，查詢功能強，數據一致性高，數據安全性高，支持二級索引。但性能方面稍遜與 MongoDB，特別是百萬級別以上的數據，很容易出現查詢慢的現象。這時候需要分析查詢慢的原因，一般情況下是工程師 sql 寫的爛，或者是沒有鍵索引，或者是索引失效等原因導致的。

公司 ERP 系統數據庫主要是 MongoDB（最接近關係型數據的 NoSQL），其次是 Redis，MySQL 只占很少的部分。現在又重新使用 MySQL，歸功於阿里巴巴的奇門系統和聚石塔系統。考慮到訂單數量已經是百萬級以上，對 MySQL 的性能分析也就顯得格外重要。

我們先通過兩個簡單的例子來入門。後面會詳細介紹各個參數的作用和意義。

說明：需要用到的 sql 已經放在了 github 上了，喜歡的同學可以點一下 star，哈哈。

https://github.com/ITDragonBlog/daydayup/tree/master/MySQL/

場景一：訂單導入，通過交易號避免重復導單

業務邏輯：訂單導入時，為了避免重復導單，一般會通過交易號去數據庫中查詢，判斷該訂單是否已經存在。

最基礎的 sql 語句

mysql> select * from itdragon_order_list where transaction_id = “81X97310V32236260E”;

+——-+——————–+——-+——+———-+————–+———-+——————+————-+————-+————+———————+

+——-+——————–+——-+——+———-+————–+———-+——————+————-+————-+————+———————+

|10000| 81X97310V32236260E |6.6| 6.13 |1| 10 |ok | ok |auto | 1 |itdragon | 2017-08-18 17:01:49 |

+——-+——————–+——-+——+———-+————–+———-+——————+————-+————-+————+———————+

mysql> explain select * from itdragon_order_list where transaction_id = “81X97310V32236260E”;

+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+

+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+

+—-+————-+———————+————+——+—————+——+———+——+——+———-+————-+

查詢的本身沒有任何問題，在線下的測試環境也沒有任何問題。可是，功能一旦上線，查詢慢的問題就迎面而來。幾百上千萬的訂單，用全表掃描？啊？哼!

怎麼知道該 sql 是全表掃描呢？通過 explain 命令可以清楚 MySQL 是如何處理 sql 語句的。列印的內容分別表示：

id : 查詢序列號為 1。
select_type : 查詢類型是簡單查詢，簡單的 select 語句沒有 union 和子查詢。
table : 表是 itdragon_order_list。
partitions : 沒有分區。
type : 連接類型，all 表示採用全表掃描的方式。
possible_keys : 可能用到索引為 null。
key : 實際用到索引是 null。
key_len : 索引長度當然也是 null。
ref : 沒有哪個列或者參數和 key 一起被使用。
Extra : 使用了 where 查詢。

因為數據庫中只有三條數據，所以 rows 和 filtered 的信息作用不大。這里需要重點了解的是 type 為 ALL，全表掃描的性能是最差的，假設數據庫中有幾百萬條數據，在沒有索引的幫助下會異常卡頓。

初步優化：為 transaction_id 創建索引

mysql> create unique index idx_order_transaID on itdragon_order_list (transaction_id);

mysql> explain select * from itdragon_order_list where transaction_id = “81X97310V32236260E”;

+—-+————-+———————+————+——-+——————–+——————–+———+——-+——+———-+——-+

+—-+————-+———————+————+——-+——————–+——————–+———+——-+——+———-+——-+

+—-+————-+———————+————+——-+——————–+——————–+———+——-+——+———-+——-+

這里創建的索引是唯一索引，而非普通索引。

唯一索引列印的 type 值是 const。表示通過索引一次就可以找到。即找到值就結束掃描返回查詢結果。

普通索引列印的 type 值是 ref。表示非唯一性索引掃描。找到值還要繼續掃描，直到將索引文件掃描完為止。(這里沒有貼出代碼)，顯而易見，const 的性能要遠高於 ref。並且根據業務邏輯來判斷，創建唯一索引是合情合理的。

再次優化：覆蓋索引

mysql> explain select transaction_id from itdragon_order_list where transaction_id = “81X97310V32236260E”;

+—-+————-+———————+————+——-+——————–+——————–+———+——-+——+———-+————-+

+—-+————-+———————+————+——-+——————–+——————–+———+——-+——+———-+————-+

+—-+————-+———————+————+——-+——————–+——————–+———+——-+——+———-+————-+

這里將 select * from 改為了 select transaction_id from 後，Extra 顯示 Using index，表示該查詢使用了覆蓋索引，這是一個非常好的消息，說明該 sql 語句的性能很好。若提示的是 Using filesort(使用內部排序) 和 Using temporary(使用臨時表) 則表明該 sql 需要立即優化了。

根據業務邏輯來的，查詢結構返回 transaction_id 是可以滿足業務邏輯要求的。

場景二：訂單管理頁面，通過訂單級別和訂單錄入時間排序

業務邏輯：優先處理訂單級別高，錄入時間長的訂單。

既然是排序，首先想到的應該是 order by，還有一個可怕的 Using filesort 等著你。

最基礎的 sql 語句

mysql> explain select * from itdragon_order_list order by order_level,input_date;

+—-+————-+———————+————+——+—————+——+———+——+——+———-+—————-+

+—-+————-+———————+————+——+—————+——+———+——+——+———-+—————-+

+—-+————-+———————+————+——+—————+——+———+——+——+———-+—————-+

首先，採用全表掃描就不合理，還使用了文件排序 Using filesort，更加拖慢了性能。

MySQL 在 4.1 版本之前文件排序是採用雙路排序的算法，由於兩次掃描磁盤，I/O 耗時太長。後優化成單路排序算法。其本質就是用空間換時間，但如果數據量太大，buffer 的空間不足，會導致多次 I/O 的情況。其效果反而更差。與其找運維同事修改 MySQL 配置，還不如自己乖乖地建索引。

初步優化：為 order_level,input_date 創建復合索引

mysql> create index idx_order_levelDate on itdragon_order_list (order_level,input_date);

mysql> explain select * from itdragon_order_list order by order_level,input_date;

+—-+————-+———————+————+——+—————+——+———+——+——+———-+—————-+

+—-+————-+———————+————+——+—————+——+———+——+——+———-+—————-+

+—-+————-+———————+————+——+—————+——+———+——+——+———-+—————-+

創建復合索引後你會驚奇的發現，和沒創建索引一樣？？？都是全表掃描，都用到了文件排序。是索引失效？還是索引創建失敗？我們試著看看下面列印情況

mysql> explain select order_level,input_date from itdragon_order_list order by order_level,input_date;

+—-+————-+———————+————+——-+—————+———————+———+——+——+———-+————-+

+—-+————-+———————+————+——-+—————+———————+———+——+——+———-+————-+

+—-+————-+———————+————+——-+—————+———————+———+——+——+———-+————-+

將 select * from 換成了 select order_level,input_date from 後。type 從 all 升級為 index，表示（full index scan）全索引文件掃描，Extra 也顯示使用了覆蓋索引。可是不對啊！！！！檢索雖然快了，但返回的內容只有 order_level 和 input_date 兩個字段，讓業務同事怎麼用？難道把每個字段都建一個復合索引？

MySQL 沒有這麼笨，可以使用 force index 強制指定索引。在原來的 sql 語句上修改 force index(idx_order_levelDate) 即可。

mysql> explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;

+—-+————-+———————+————+——-+—————+———————+———+——+——+———-+——-+

+—-+————-+———————+————+——-+—————+———————+———+——+——+———-+——-+

+—-+————-+———————+————+——-+—————+———————+———+——+——+———-+——-+

再次優化：訂單級別真的要排序麼？

其實給訂單級別排序意義並不大，給訂單級別添加索引意義也不大。因為 order_level 的值可能只有，低，中，高，加急，這四種。對於這種重復且分布平均的字段，排序和加索引的作用不大。

我們能否先固定 order_level 的值，然後再給 input_date 排序？如果查詢效果明顯，是可以推薦業務同事使用該查詢方式。

mysql> explain select * from itdragon_order_list where order_level=3order by input_date;

+—-+————-+———————+————+——+———————+———————+———+——-+——+———-+———————–+

+—-+————-+———————+————+——+———————+———————+———+——-+——+———-+———————–+

|1| SIMPLE |itdragon_order_list | NULL |ref | idx_order_levelDate |idx_order_levelDate | 5 |const | 1 |100| Using index condition |

+—-+————-+———————+————+——+———————+———————+———+——-+——+———-+———————–+

和之前的 sql 比起來，type 從 index 升級為 ref(非唯一性索引掃描)。索引的長度從 68 變成了 5，說明只用了一個索引。ref 也是一個常量。Extra 為 Using index condition 表示自動根據臨界值，選擇索引掃描還是全表掃描。總的來說性能遠勝於之前的 sql。

上面兩個案例只是快速入門，我們需嚴記一點：優化是基於業務邏輯來的。絕對不能為了優化而擅自修改業務邏輯。如果能修改當然是最好的。

索引簡介

官方定義：索引（Index）是幫助 MySQL 高效獲取數據的數據結構。

大家一定很好奇，索引為什麼是一種數據結構，它又是怎麼提高查詢的速度？我們拿最常用的二叉樹來分析索引的工作原理。看下面的圖片：

你不得不知的 MySQL 索引優化分析-雪花新聞

創建索引的優勢：

1. 提高數據的檢索速度，降低數據庫 IO 成本：使用索引的意義就是通過縮小表中需要查詢的記錄的數目從而加快搜尋的速度。

2. 降低數據排序的成本，降低 CPU 消耗：索引之所以查的快，是因為先將數據排好序，若該字段正好需要排序，則真好降低了排序的成本。

創建索引的劣勢：

1. 占用存儲空間：索引實際上也是一張表，記錄了主鍵與索引字段，一般以索引文件的形式存儲在磁盤上。

2. 降低更新表的速度：表的數據發生了變化，對應的索引也需要一起變更，從而減低的更新速度。否則索引指向的物理數據可能不對，這也是索引失效的原因之一。

3. 優質索引創建難：索引的創建並非一日之功，也並非一直不變。需要頻繁根據用戶的行為和具體的業務邏輯去創建最佳的索引。

索引分類

我們常說的索引一般指的是 BTree（多路搜尋樹）結構組織的索引。其中還有聚合索引，次要索引，復合索引，前綴索引，唯一索引，統稱索引，當然除了 B + 樹外，還有哈希索引（hash index）等。

單值索引：一個索引只包含單個列，一個表可以有多個單列索引

唯一索引：索引列的值必須唯一，但允許有空值

復合索引：一個索引包含多個列，實際開發中推薦使用

實際開發中推薦使用復合索引，並且單表創建的索引個數建議不要超過五個

基本語法：

創建：

create[unique] indexindexName ontableName (columnName…)

altertableName add[unique] index[indexName] on(columnName…)

刪除：

dropindex[indexName] ontableName

查看：

showindexfromtableName

哪些情況需要建索引：

1. 主鍵，唯一索引

2. 經常用作查詢條件的字段需要創建索引

3. 經常需要排序、分組和統計的字段需要建立索引

4. 查詢中與其他表關聯的字段，外鍵關係建立索引

哪些情況不要建索引：

1. 表的記錄太少，百萬級以下的數據不需要創建索引

2. 經常增刪改的表不需要創建索引

3. 數據重復且分布平均的字段不需要創建索引，如 true,false 之類。

4. 頻發更新的字段不適合創建索引

5. where 條件里用不到的字段不需要創建索引

性能分析

MySQL 自身瓶頸

MySQL 自身參見的性能問題有磁盤空間不足，磁盤 I/O 太大，服務器硬件性能低。

1. CPU：CPU 在飽和的時候一般發生在數據裝入記憶體或從磁盤上讀取數據時候

2. IO：磁盤 I/O 瓶頸發生在裝入數據遠大於記憶體容量的時候

3. 服務器硬件的性能瓶頸：top,free,iostat 和 vmstat 來查看系統的性能狀態

explain 分析 sql 語句

使用 explain 關鍵字可以模擬優化器執行 sql 查詢語句，從而得知 MySQL 是如何處理 sql 語句。

+—-+————-+——-+————+——+—————+—–+———+——+——+———-+——-+

+—-+————-+——-+————+——+—————+—–+———+——+——+———-+——-+

select 查詢的序列號，包含一組可以重復的數字，表示查詢中執行 sql 語句的順序。一般有三種情況：

第一種：id 全部相同，sql 的執行順序是由上至下；

第二種：id 全部不同，sql 的執行順序是根據 id 大的優先執行；

第三種：id 既存在相同，又存在不同的。先根據 id 大的優先執行，再根據相同 id 從上至下的執行。

select_type

select 查詢的類型，主要是用於區別普通查詢，聯合查詢，嵌套的複雜查詢

simple：簡單的 select 查詢，查詢中不包含子查詢或者 union

primary：查詢中若包含任何複雜的子查詢，最外層查詢則被標記為 primary

subquery：在 select 或 where 列表中包含了子查詢

derived：在 from 列表中包含的子查詢被標記為 derived（衍生）MySQL 會遞歸執行這些子查詢，把結果放在臨時表里。

union：若第二個 select 出現在 union 之後，則被標記為 union，若 union 包含在 from 子句的子查詢中，外層 select 將被標記為：derived

union result：從 union 表獲取結果的 select

partitions

表所使用的分區，如果要統計十年公司訂單的金額，可以把數據分為十個區，每一年代表一個區。這樣可以大大的提高查詢效率。

type

這是一個非常重要的參數，連接類型，常見的有：all , index , range , ref , eq_ref , const , system , null 八個級別。性能從最優到最差的排序：system > const > eq_ref > ref > range > index > all。

對 java 工程師來說，若保證查詢至少達到 range 級別或者最好能達到 ref 則算是一個優秀而又負責的工程師。

all：（full table scan）全表掃描無疑是最差，若是百萬千萬級數據量，全表掃描會非常慢。

index：（full index scan）全索引文件掃描比 all 好很多，畢竟從索引樹中找數據，比從全表中找數據要快。

range：只檢索給定範圍的行，使用索引來匹配行。範圍縮小了，當然比全表掃描和全索引文件掃描要快。sql 語句中一般會有 between，in，>，< 等查詢。

ref：非唯一性索引掃描，本質上也是一種索引訪問，返回所有匹配某個單獨值的行。比如查詢公司所有屬於研發團隊的同事，匹配的結果是多個並非唯一值。

eq_ref：唯一性索引掃描，對於每個索引鍵，表中有一條記錄與之匹配。比如查詢公司的 CEO，匹配的結果只可能是一條記錄，

const：表示通過索引一次就可以找到，const 用於比較 primary key 或者 unique 索引。因為只匹配一行數據，所以很快，若將主鍵至於 where 列表中，MySQL 就能將該查詢轉換為一個常量。

system：表只有一條記錄（等於系統表），這是 const 類型的特列，平時不會出現，了解即可

possible_keys

顯示查詢語句可能用到的索引 (一個或多個可能為 null)，不一定被查詢實際使用。僅供參考使用。

key

顯示查詢語句實際使用的索引。若為 null，則表示沒有使用索引。

key_len

顯示索引中使用的字節數，可通過 key_len 計算查詢中使用的索引長度。在不損失精確性的情況下索引長度越短越好。key_len 顯示的值為索引字段的最可能長度，並非實際使用長度，即 key_len 是根據表定義計算而得，並不是通過表內檢索出的。

ref

顯示索引的哪一列或常量被用於查找索引列上的值。

rows

根據表統計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數，值越大越不好。

extra

Using filesort：說明 MySQL 會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL 中無法利用索引完成的排序操作稱為「文件排序」。出現這個就要立刻優化 sql。

Using temporary：使用了臨時表保存中間結果，MySQL 在對查詢結果排序時使用臨時表。常見於排序 order by 和分組查詢 group by。出現這個更要立刻優化 sql。

Using index：表示相應的 select 操作中使用了覆蓋索引（Covering index），避免訪問了表的數據行，效果不錯！如果同時出現 Using where，表明索引被用來執行索引鍵值的查找。如果沒有同時出現 Using where，表示索引用來讀取數據而非執行查找動作。

覆蓋索引（Covering Index）：也叫索引覆蓋，就是 select 的數據列只用從索引中就能夠取得，不必讀取數據行，MySQL 可以利用索引返回 select 列表中的字段，而不必根據索引再次讀取數據文件。

Using index condition：在 5.6 版本後加入的新特性，優化器會在索引存在的情況下，通過符合 RANGE 範圍的條數和總數的比例來選擇是使用索引還是進行全表遍歷。

Using where：表明使用了 where 過濾。

Using join buffer：表明使用了連接緩存。

impossible where： where 語句的值總是 false，不可用，不能用來獲取任何元素。

distinct：優化 distinct 操作，在找到第一匹配的元組後即停止找同樣值的動作。

filtered

一個百分比的值，和 rows 列的值一起使用，可以可能出查詢執行計劃 (QEP) 中的前一個表的結果集，從而確定 join 操作的循環次數。小表驅動大表，減輕連接的次數。

通過 explain 的參數介紹，我們可以得知：

1. 表的讀取順序 (id)

2. 數據讀取操作的操作類型 (type)

3. 哪些索引被實際使用 (key)

4. 表之間的引用 (ref)

5. 每張表有多少行被優化器查詢 (rows)

性能下降的原因

從工程師的角度

1. 查詢語句寫的不好

2. 沒建索引，索引建的不合理或索引失效

3. 關聯查詢有太多的 join

從服務器的角度

1. 服務器磁盤空間不足

2. 服務器調優配置參數設置不合理

總結

1. 索引是排好序且快速查找的數據結構。其目的是為了提高查詢的效率。

2. 創建索引後，查詢數據變快，但更新數據變慢。

3. 性能下降的原因很可能是索引失效導致。

4. 索引創建的原則，經常查詢的字段適合創建索引，頻繁需要更新的數據不適合創建索引。

5. 索引字段頻繁更新，或者表數據物理刪除容易造成索引失效。

6. 擅用 explain 分析 sql 語句

7. 除了優化 sql 語句外，還可以優化表的設計。如盡量做成單表查詢，減少表之間的關聯。設計歸檔表等。

到這里，MySQL 的索引優化分析就結束了，有什麼不對的地方，大家可以提出來。如果覺得不錯可以點一下讚。

尋夢新聞LINE@每日推播熱門推薦文章，趣聞不漏接❤️

更多文章