[TOC] # 例子 ~~~ mysql> explain select * from servers; +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ row in set (0.03 sec) ~~~ expain出來的信息有10列，分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面對這些字段出現的可能進行解釋： * id: SELECT 查詢的標識符. 每個 SELECT 都會自動分配一個唯一的標識符. * select_type: SELECT 查詢的類型. * table: 查詢的是哪個表 * partitions: 匹配的分區 * type: join 類型 * possible_keys: 此次查詢中可能選用的索引 * key: 此次查詢中確切使用到的索引. * ref: 哪個字段或常數與 key 一起被使用 * rows: 顯示此查詢一共掃描了多少行. 這個是一個估計值. * filtered: 表示此查詢條件所過濾的數據的百分比 * extra: 額外的信息 # id 我的理解是SQL執行的順序的標識,SQL從大到小的執行 1. id相同時，執行順序由上至下 2. 如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行 3. id如果相同，可以認為是一組，從上往下順序執行；在所有組中，id值越大，優先級越高，越先執行 # select_type 示查詢中每個select子句的類型 (1) SIMPLE(簡單SELECT,不使用UNION或子查詢等) (2) PRIMARY(查詢中若包含任何復雜的子部分,最外層的select被標記為PRIMARY) (3) UNION(UNION中的第二個或后面的SELECT語句) (4) DEPENDENT UNION(UNION中的第二個或后面的SELECT語句，取決于外面的查詢) (5) UNION RESULT(UNION的結果) (6) SUBQUERY(子查詢中的第一個SELECT) (7) DEPENDENT SUBQUERY(子查詢中的第一個SELECT，取決于外面的查詢) (8) DERIVED(派生表的SELECT, FROM子句的子查詢) (9) UNCACHEABLE SUBQUERY(一個子查詢的結果不能被緩存，必須重新評估外鏈接的第一行) # table 顯示這一行的數據是關于哪張表的，有時不是真實的表名字,看到的是derivedx(x是個數字,我的理解是第幾步執行的結果) ~~~ mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b; +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | | | 3 | DERIVED | t1 | const | PRIMARY,idx_t1_id | PRIMARY | 4 | | 1 | | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ ~~~ # type 表示MySQL在表中找到所需行的方式，又稱“訪問類型”。 結果值從好到壞依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 常用的類型有： ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好） * system:表僅有一行(=系統表)。這是const聯接類型的一個特例。 * const:表最多有一個匹配行,它將在查詢開始時被讀取。因為僅有一行,在這行的列值可被優化器剩余部分認為是常數。const表很快,因為它們只讀取一次! * eq_ref:對于每個來自于前面的表的行組合,從該表中讀取一行。這可能是最好的聯接類型,除了const類型,此類型通常出現在多表的 join 查詢, 表示對于前表的每一個結果, 都只能匹配到后表的一行結果. 并且查詢的比較操作通常是 =, 查詢效率較高。 * ref:對于每個來自于前面的表的行組合,所有有匹配索引值的行將從這張表中讀取,此類型通常出現在多表的 join 查詢, 針對于非唯一或非主鍵索引, 或者是使用了 最左前綴 規則索引的查詢.。 * ref_or_null:該聯接類型如同ref,但是添加了MySQL可以專門搜索包含NULL值的行。 * index_merge:該聯接類型表示使用了索引合并優化方法。 * unique_subquery:該類型替換了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個索引查找函數,可以完全替換子查詢,效率更高。 * index_subquery:該聯接類型類似于unique_subquery。可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) * range:只檢索給定范圍的行,使用一個索引來選擇行。 * index:該聯接類型與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引文件通常比數據文件小。 * ALL:對于每個來自于先前的表的行組合,進行完整的表掃描 # possible_keys 指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用 該列完全獨立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。 如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創造一個適當的索引并且再次用EXPLAIN檢查查詢 # Key key列顯示MySQL實際決定使用的鍵（索引） 如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。 # key_len 表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的） 不損失精確性的情況下，長度越短越好 * 字符串 * char(n): n 字節長度 * varchar(n): 如果是 utf8 編碼, 則是 3 n + 2字節; 如果是 utf8mb4 編碼, 則是 4 n + 2 字節. * 數值類型: * TINYINT: 1字節 * SMALLINT: 2字節 * MEDIUMINT: 3字節 * INT: 4字節 * BIGINT: 8字節 * 時間類型 * DATE: 3字節 * TIMESTAMP: 4字節 * DATETIME: 8字節 * 字段屬性: NULL 屬性 占用一個字節. 如果一個字段是 NOT NULL 的, 則沒有此屬性. # ref 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值 # rows 表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數 # Extra 該列包含MySQL解決查詢的詳細信息,有以下幾種情況： * Using join buffer：改值強調了在獲取連接條件時沒有使用索引，并且需要連接緩沖區來存儲中間結果。如果出現了這個值，那應該注意，根據查詢的具體情況可能需要添加索引來改進能。 * Impossible where：這個值強調了where語句會導致沒有符合條件的行。 * Select tables optimized away：這個值意味著僅通過使用索引，優化器可能僅從聚合函數結果中返回一行 * Distinct:MySQL發現第1個匹配行后,停止為當前的行組合搜索更多的行。 * Not exists:MySQL能夠對查詢進行LEFT JOIN優化,發現1個匹配LEFT JOIN標準的行后,不再為前面的的行組合在該表內檢查更多的行。 * range checked for each record (index map: #):MySQL沒有發現好的可以使用的索引,但發現如果來自前面的表的列值已知,可能部分索引可以使用。 * Using filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行,MySQL中無法利用索引完成的排序操作稱為“文件排序”。 * Using index:從只使用索引樹中的信息而不需要進一步搜索讀取實際的行來檢索表中的列信息。 * Using temporary:為了解決查詢,MySQL需要創建一個臨時表來容納結果,表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢。 * Using where:列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql服務器將在存儲引擎檢索行后再進行過濾 * Using sort_union(...), Using union(...), Using intersect(...):這些函數說明如何為index_merge聯接類型合并索引掃描。 * Using index for group-by:類似于訪問表的Using index方式,Using index for group-by表示MySQL發現了一個索引,可以用來查 詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜索硬盤訪問實際的表 # 總結： * EXPLAIN不會告訴你關于觸發器、存儲過程的信息或用戶自定義函數對查詢的影響情況 * EXPLAIN不考慮各種Cache * EXPLAIN不能顯示MySQL在執行查詢時所作的優化工作 * 部分統計信息是估算的，并非精確值 * EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT后查看執行計劃。