1、Mysql Query Optimizer

這個名稱在前言部分我在Mysql的整體架構中介紹過，稱為查詢優化器；這個查詢優化器在絕大多數的公司，是不會做任何修改和擴展的，因為業務不需要，大牛請不起等因素，也就除了阿里這些大廠變態到把這玩意改了自己用，

Mysql中有專門負責優化Select陳述句的優化器模塊，主要功能是：通過計算分析系統中手記到的統計資訊，為客戶端請求的Query提供Mysql認為最優化的執行計劃，認為最優的檢索方式，但是不一定是DBA認為是最優的，所以這部分是調整起來最耗時間的，

當客戶端向Mysql請求一條查詢的時候，命令決議器會完成請求的分類，區別出是Select操作，并轉發給Mysql Query Optimizer時，Optimizer首先會對整個Query進行優化，處理掉一些常量運算式的運算，直接換算成常量值，或者根據查詢條件出現的順序進行個位置交換，如去掉一些無用或者顯而易見的條件，結構調整等，然后分析Query的Hint資訊（如果存在的話），看顯示Hint資訊是不是可以完全確定Query的執行計劃，如果沒有Hint，或者Hint不足以完全確定執行計劃，則會讀取所涉及物件的統計資訊，根據Query進行寫相應的計算分析，最后得出執行計劃，這個程序，需要了解的重點是，Optimizer決議的結果和我們預期的結果不一定一樣！

那Mysql是如何把自己認為最優的結果反饋給程式員或者DBA的呢，進而讓Mysql更好的優化？

2、Mysql常見的性能瓶頸

CPU方面：CPU在很繁忙的時候，一般是在資料裝入記憶體或者從磁盤上讀取資料的時候，Mysql得到的CPU處理的時間片，分配的執行緒就遠遠不夠，

IO：磁盤的IO也發生在裝入的資料遠大于記憶體導致磁盤IO高，Sql就會很慢，哪怕是一個最簡單的查詢陳述句，

服務器性能瓶頸，可以通過top，free，iostat，vmstat看下服務器的當前狀態，是否需要升級配置，

3、Explain執行計劃

概念：使用 EXPLAIN 關鍵字可以模擬優化器執行 SQL 查詢陳述句，從而知道 MySQL 是如何處理你的SQL 陳述句的，進而分析你的查詢陳述句或是表結構的性能瓶頸，

Explain能干這些事情：

• 表的讀取順序
• 資料讀取的操作型別
• 哪些索引可以使用
• 哪些索引實際被使用
• 表之間的參考
• 每張表有多少行被優化器查詢

用法： Explain+SQL 陳述句

假設我有個部門表：tbl_emp

執行下這個sql可以看出來：

這些欄位包含的什么意思呢，通過這些欄位怎么能知道我當前的sql執行是什么樣的，需不需要優化？怎么玩？

3.1、id

概念：select查詢的序列號，包含一組數字，表示查詢中執行的select子句或者操作表的順序，重點：子句+操作順序

場景：

id相同，執行順序由上至下

比如這個sql：explain select t2.* from t1, t2, t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column = '';

這個Sql你認為的from 順序是t1，t2，t3，但是不然，Mysql自己認為的是t1,t3,t2這樣的順序，所以當id相同時，執行順序由上至下！

id不同的時候，如果是子查詢，id的序號遞增，id值越大優先級越高，越先被執行

比如這個sql：看下select_type列，有個subquery是子查詢的意思，id=2和3的子查詢，序號遞增，優先級越高越先被執行，所以執行順序是t3, t1, t2；并且仔細看這個sql，子查詢正常會優先被執行，里面的結果出來，才會執行外面的，

id既有相同，也有不同，同時存在的時候；永遠是數字大的優先級最高，遇到相同id，自上而下順序執行，

比如這個sql：因為執行計劃中既有id相同，又有id不同，此時記住數字大的優先級最高，所以先執行id=2，對應的表是t3表，其實對于sql，這個剛好是內部的子查詢先執行；然后對于id相同的兩個id=1，順序至上而下，先是table表為<derived2>，derived意思是衍生表的意思，后面的數字2表示從id=2這個表t3衍生出來的，仔細看sql，from的子查詢，別名叫s1，這個是衍生表，來源于t3表，這樣就好理解了，然后繼續執行t2表，這就是執行順序的分析，

所以這句話應該就可以明白Explain可以干的第一件事：

3.2、select_type

概念：查詢的型別，主要是用于區別普通查詢，聯合查詢，子查詢等復雜查詢；

有這些屬性：simple，primary，subquery，derived，union，union result，

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

primary：查詢中若包含任何復雜的子部分，最外層查詢則被標記為primary，通常理解為外層查詢

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

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

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

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

所以Explain也起到這個作用：

---

3.3、table

這里就不說了，表示當前的表，或者是衍生表

3.4、type

概念：訪問型別排列，顯示查詢使用了何種型別，最好到最差的狀態依次是，

system > const > eq_ref > ref > range > index > All

還有一些我沒標出來，目前我是基本沒接觸到除了上面這些之外的，用到再去了解吧，

如果發現ALL，且你的資料在百萬級別以上，請你務必優化，因為ALL全表掃描！！但是你的記錄只有10條，你說要不要？那沒必要，不要為了優化而優化，形成過度優化，

system：表只有一行記錄，等于系統表，這個開發中，別指望了，

const：表示通過索引一次就找到了，const用于比較primary key或者unique索引，因為這個只匹配一行資料，所以很快；比如將主鍵查詢放在where后，就可以迅速的將該查詢轉為一個常量，

比如這個sql，t1表執行后，是const，因為id主鍵條件在where后面，

eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配，常見于主鍵或者唯一索引掃描，

比如這個sql，先查t2表，查到一條與t1.id記錄相匹配的一條記錄，這個時候，會走eq_ref，這里和const有點類似，但是又不一樣，const著重強調主鍵索引，但是這個強調在某個表中，這個索引鍵id，其他表中只有一條記錄可以匹配；比如查一個公司的ceo，只有一條記錄匹配，

ref：非唯一性索引掃描，回傳匹配某個單獨值的所有行，本質上也是一種索引訪問，它回傳所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以它應該屬于查找和掃描的混合體，

比如這個sql：對表建立了復合索引，執行計劃col1='ac'這個記錄，和上面eq_ref不同的是，回傳的是這個col列的所有等于ac的所有行，這個col的索引，是非唯一性索引，值不是唯一的，比如類似name='zhangsan'的人有很多行，

range：只檢索給定范圍的行，使用一個索引來選擇行，key列顯示使用了哪個索引，一般就是在你的where陳述句中出現了between、<、>、in等查詢，這種范圍掃描索引，比全表掃描要好，因為它只需要開始于索引的某一點，而結束于另一個點，不用掃描全部索引；

比如這個sql：查between and的范圍，還有in某個范圍，type的屬性都為range， index：Full Index Scan，index與ALL的區別為index型別只遍歷索引樹，這通常比ALL快，因為索引檔案通常比資料檔案小，也就是說雖然ALL和Index都是讀全表，但是Index是從索引中讀取的，而all是從硬碟中讀取；需要遍歷整個索引樹，就像你翻字典，不像上面可以直接定位查字母，而是從A查到Z，一個一個字母索引查，直到查到對應的索引；

比如這個sql：id已經是索引，查t1表的所有id欄位，是不是要遍歷整個索引表，

ALL：Full Table Scan，將遍歷全表以找到匹配的行，

最明顯的sql，什么索引都沒有，select * from t1; 幾百萬甚至上億的資料，直接裂開！

一般來說，保證查詢至少到達range級別，最好能達到eq_ref，

---

3.5、possible_keys和key

概念：possible_keys顯示可能應用在這張表上的索引，一個或者多個，查詢涉及到的欄位若存在索引，則該索引會被列出，但是不一定被查詢使用！！！

key是實際在Mysql自己認為這條sql，真正實際用到的索引是什么，如果為NULL，則沒有使用索引，若查詢中使用了覆寫索引，則該索引僅出現在key串列中！

比如像這種理論上用到了2個索引，實際上只用到一個索引的情況也很常見，

什么是覆寫索引呢？

來看個經典的場景：理論上沒有用到索引，為NULL，但是實際上key用到這個索引idx_col1_col2，這個就描述了剛剛的話，若查詢中使用了覆寫索引，則該索引僅出現在key串列中，簡單說來，覆寫索引就是你查的欄位個數，順序和索引一模一樣；查col1，col2，和建立當前這個復合索引的個數，順序，一一對應，這個索引是

create index idx_col1_col2 on t1(col1, col2);

3.6、key_len

概念：表示索引中使用的位元組數，可以通過該列計算查詢中使用的索引長度，在不損失精確性的情況下，長度越短越好，key_len顯示的值為索引欄位的最大可能長度，并非實際使用長度，即key_ken是根據表定義計算而得，不是通過表內檢索出的，

有個例子，比如我要通過相親的資訊來找物件，目前我只知道性別，篩選了一批女性，這個時候索引長度假如為2，那不能是個女的就可以，我要身材好的，那我再加個索引，查出來了一批，這個時候索引長度為6，依次下去當我想要更多的資訊，精度越精細，得到最后一個屬于我的女人的時候，這個時候索引長度會很長，有點類似時間換空間的概念，

這個圖就類似我舉的例子

3.7、ref

概念：顯示索引的哪一列被使用了，如果可能的話，是一個常數，哪些列或者常量被用于查找索引列上的值，

什么意思呢？看這個圖，t1先加載后，這個ref值是const常量，對應sql的條件是t1.other_column=一個常量值，t2和t3表示參考test庫下的t1表下的ID欄位；

再回顧之前的Explain能做什么，是不是也恍然大明白，

3.8、rows

概念：根據表統計資訊以及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數，

比如這個sql，t2表是沒有索引的；看下這個where后面的t1.id = t2.id這個是eq_ref唯一性掃描，只有一行匹配，再看下t2.col1='ac'；所以沒建立索引的時候，一共要找640+1行才可以找到結果；

如果加了索引呢？給t2加了索引后，整個查找的行數變成了142+1，可想而知，索引加了后，需要讀取的行越少，效果越好！

這也印證了上面最后一個Explain的點：

3.9、Extra

概念：包含不適合在其他列中顯示但十分重要的資訊；

Extra的值有這些：Using filesort，Using temporary，Using where，Using index

Using filesort：說明Mysql會對資料使用一個外部的索引排序，而不是按照表內的索引順序進行讀取，Mysql中無法利用索引完成的排序操作稱為“檔案排序”，

我一直有說，索引等于排序+查找，Mysql說你給我建立了索引，我按照你說的方式去建立樓層，索引，我原本要按照這個順序，可是在某種情況下我卻用不到你給的索引，那么我只能另起爐灶，把這些順序自己內部再排序一次，開發中遇到這個filesort，幾乎就很坑爹，甚至九死一生，查詢巨慢基本可以導致功能不可用，

舉個例子，我這里有個sql，已經讓Mysql建立好索引，idx_col1_col2_col3，比如1樓2樓3樓這么建樓，查詢中有col1，where條件也有col1='ac'，1樓用到了，所以type=ref參考了索引，ref欄位是個const因為用到了常量ac，但是這個Extra欄位出現了Using filesort！！！

原因是這樣的，不好意思，你給的索引Mysql在檢索方面，已經部分使用到了，而在排序的order by col3，Mysql沒有用到，而索引增加的是檢索和排序的優化，排序為什么Mysql用不到，排序的時候最好遵守所建索引的個數和順序！！所以問題出現在這，索引順序是1.2.3，但是你條件和排序后面跟的是1,3，2斷層了！

再對比個sql，order by后面是col2,col3，所以條件+排序整個的順序和個數，剛好符合1樓，2樓，3樓這樣的索引順序；此時filesort沒了，后者性能，高于前者！如果可以，盡快優化！

Using temporary：在filesort是九死一生，在這個temporary就更嚴重了，前者只是幫你內部排了個序，后者幫你建立了個內部的臨時表！可能要為你處理一些資料先拷貝到臨時表里再去折騰，再洗掉臨時表！常見于排序order by和分組查詢group by，

看個例子，這個sql索引一定用到，只要key這個欄位不為NULL，group by后面直接到col2這個欄位了，會有人問，哎前面的where查詢不是有了col的條件嗎，這個我后面會說，在索引中，范圍查找會失效，那么就等價于前面的where col1 in...這個1樓失效了，你讓我直接group by 2樓去分組，我當然就走不到索引上了，所以自然而然產生了filesort，還有temporary，

我們可以這么優化，取消了臨時表的創建，臨時表的存在，是非常耗性能的！！

Using index：這個指標是非常好的，表示相應的select操作中使用了覆寫索引，避免訪問了表的資料行，效果不錯，如果同時出現了Using where，表明索引被用來執行索引鍵值的查找，如果沒有同時出現Using where，表示索參考來讀取資料而非執行查找動作，

看看這個sql：哥們我建立的索引是1和2，你只查2說明你用到了部分索引 ，如果同時出現了Using where，表明索引被用來執行索引鍵值的查找，

再看這個sql：如果沒有同時出現Using where，表示索參考來讀取資料而非執行查找動作，這個sql就是讀取資料，沒有where條件；

覆寫索引再完善下概念：查詢的列要被所建的索引覆寫！！或者部分滿足，比如我建立col1和col2，我只查1或者2，也是覆寫索引的一種！！

注意：如果要使用覆寫索引，一定要注意select串列中只列出需要的列，不可寫select *，因為如果將所有的欄位一起做索引，會導致索引過大，性能查詢下降！！

這三個最重要的欄位，是調優中最常見且最重要的，希望大家掌握！！其他不重要的我這里就不多闡述了，用到再查吧，

本章介紹了Explain的詳細知識點，下一章節開始拿案例進行調優！！