## 一、執行計劃 explain這個命令來查看一個SQL語句的執行計劃，查看該SQL語句有沒有使用上了索引，有沒有做全表掃描等； 舉例來說： ``` select * from card_info; explain select * from card_info; ``` 一個例子：  解釋： | 列名 | 說明 | | --- | --- | | id | 選擇標識符 | |select_type |表示查詢的類型。| |table |輸出結果集的表 | |partitions |匹配的分區 | |type |表示表的連接類型 | |possible_keys |表示查詢時，可能使用的索引 | |key |表示實際使用的索引 | |key_len |索引字段的長度 | |ref |列與索引的比較 | |rows |掃描出的行數(估算的行數) | |filtered |按表條件過濾的行百分比 | |Extra |執行情況的描述和說明 | ### **id** 查詢的序號，包含一組數字，表示查詢中執行select子句或操作表的順序 1. id相同，執行順序從上往下； 2. id不同，id值越大，優先級越高，越先執行； ### **select_type** 表示查詢中每個select子句的類型； 查詢類型，主要用于區別普通查詢，聯合查詢，子查詢等的復雜查詢 1. simple ——簡單的select查詢，查詢中不包含子查詢或者UNION 2. primary ——查詢中若包含任何復雜的子部分，最外層查詢被標記 3. subquery/dependent subquery——在select或where列表中包含了子查詢； 4. derived——在from列表中包含的子查詢被標記為derived（衍生），MySQL會遞歸執行這些子查詢，把結果放到臨時表中； 5. union——如果第二個select出現在UNION之后，則被標記為UNION，如果union包含在from子句的子查詢中，外層select被標記為derived，故在union中第二個及之后的select。 6. union result:UNION 臨時表檢索結果的select。 ### **table** 輸出的行所引用的表； ### **partitions** 如果查詢基于分區表，將會顯示訪問的是哪個區； ### **type** 顯示連接類型，顯示查詢使用了何種類型，按照從最佳到最壞類型排序： 1.system：表中僅有一行（=系統表）這是const聯結類型的一個特例； 2.const：表示通過索引一次就找到，const用于比較primary key或者unique索引。因為只匹配一行數據，所以如果將主鍵置于where列表中，mysql能將該查詢轉換為一個常量； 3.eq_ref:唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配常見于唯一索引或者主鍵掃描，常用于連接查詢。簡單查詢不會出現該類型； 4.ref:非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，是使用普通索引或者唯一性索引的部分前綴，它返回所有匹配某個單獨值的行，可能會找多個符合條件的行，屬于查找和掃描的混合體； 5.range:只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引，一般就是where語句中出現了between,in等范圍的查詢。這種范圍掃描索引掃描比全表掃描要好，因為它開始于索引的某一個點，而結束另一個點，不用全表掃描； 6.index:index 與all區別為index類型只遍歷索引樹。通常比all快，因為索引文件比數據文件小很多； 7.all：遍歷全表以找到匹配的行； >[danger] > 1、性能按照type排序：system > const > eq_ref > ref > range > index > ALL； > 2、注意:一般保證查詢至少達到range級別，最好能達到ref； ### **possible_keys** 指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用（該查詢可以利用的索引，如果沒有任何索引顯示 null）； ### **Key** 顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL。查詢中如果使用覆蓋索引，則該索引和查詢的select字段重疊。 要想強制mysql使用或者忽視possible_key列中的索引，在查詢中使用force index、use index或者ignore index。 ### **key_len** 表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的）； >[danger] 不損失精確性的情況下，長度越短越好 ### **ref** 顯示索引的哪一列被使用了，如果有可能是一個常數，哪些列或常量被用于查詢索引列上的值； ### **rows** 估算出結果集行數，表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數； ### **filtered** 指返回結果的行占需要讀到的行(rows列的值)的百分比； ### **Extra** 包含不適合在其他列中顯示，但是十分重要的額外信息； 1、Using filesort：說明mysql會對數據適用一個外部的索引排序。而不是按照表內的索引順序進行讀取。MySQL中無法利用索引完成排序操作稱為“文件排序”； 2、Using temporary:使用了臨時表保存中間結果，mysql在查詢結果排序時使用臨時表。常見于排序order by和分組查詢group by； 3、Using index:表示相應的select操作用使用覆蓋索引，避免訪問了表的數據行。如果同時出現using where，表名索引被用來執行索引鍵值的查找；如果沒有同時出現using where，表名索引用來讀取數據而非執行查詢動作； 4、Using where :表明使用where過濾； 5、using join buffer:使用了連接緩存； 6、impossible where:where子句的值總是false，不能用來獲取任何元組； 7、select tables optimized away：在沒有group by子句的情況下，基于索引優化Min、max操作或者對于MyISAM存儲引擎優化count（*），不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化； 8、distinct：優化distinct操作，在找到第一匹配的元組后即停止找同樣值的動作； >[danger] 性能按照extra排序 > Using index>Using index condition>Using where>Using join buffer (Block Nested Loop)>Using filesort>Using temporary>Start temporary, End temporary>FirstMatch(tbl_name) ## 二、優化手段 1. SQL語句中IN包含的值不應過多，不能超過200個，200個以內查詢優化器計算成本時比較精準，超過200個是估算的成本，另外建議能用between就不要用in，這樣就可以使用range索引了。 2. SELECT語句務必指明字段名稱：SELECT * 增加很多不必要的消耗（cpu、io、內存、網絡帶寬）；增加 了使用覆蓋索引的可能性；當表結構發生改變時，前斷也需要更新。所以要求直接在select后面接上字段名； 3. 當只需要一條數據的時候，使用limit 1； 4. 排序時注意是否能用到索引； 5. 使用or時如果沒有用到索引，可以改為union all 或者union； 6. 如果in不能用到索引，可以改成exists看是否能用到索引； 7. 不要用全表update或非索引條件update，會導致鎖表，引起性能問題；例如，如下語句`update SysStaff set sts=:sts where expDate !='' and expDate<=:now`，必須確保expDate 字段為索引，方可使用，否則，每次執行會鎖表； 8. 只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那么這一列對于此復合索引就是無效的。所以我們在數據庫設計時不要讓字段的默認值為NULL； 9. 創建復合索引時應該將最常用作限制條件的列放在最左邊，依次遞減，因為如果在area、age兩列上創建復合索引的話將帶來更高的效率。如果我們創建了(area, age,salary)的復合索引，那么其實相當于創建了(area,age,salary)、(area,age)、(area)三個索引，這被稱為最佳左前綴特性； 10. 如果取值范圍有限，那么也不必建立索引，比如性別可能就只有兩個值，建索引不僅沒什么優勢，還會影響到更新速度，這被稱為過度索引；