MySQL-Explain-有解無憂

Explain

是什么（查看執行計劃）
- 使用EXPLAIN關鍵字可以模擬優化器執行SQL陳述句，從而知道MySQL是
  如何處理你的SQL陳述句的，分析你的查詢陳述句或是結構的性能瓶頸
- 官網介紹
能干嘛
- 表的讀取順序
- 資料讀取操作的操作型別
- 哪些索引可以使用
- 哪些索引被實際使用
- 表之間的參考
- 每張表有多少行被優化器查詢
怎么玩
- Explain+SQL陳述句
  
  EXPLAIN SELECT * FROM studymysql.tbl_emp;
- 執行計劃包含的資訊
各個欄位解釋
- id
  - select查詢的序列號,有幾個select就由幾個id，包含一組數字，表示查詢中執行select子句或操作表的順序
  - 三種情況
    - id相同，執行順序由上至下
    - id不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行
    - id相同不同，同時存在
      
      注意： union結果總是放在一個匿名臨時表中，臨時表不在SQL總出現，因此它的id是NULL，
- select_type
  - 有哪些
  - 查詢的型別，主要用于區別普通查詢、聯合查詢、子查詢等的復雜查詢
  1.SIMPLE ：簡單的select查詢，查詢中不包含子查詢或者UNION
  
  2.PRIMARY ：查詢中若包含任何復雜的子部分，最外層查詢則被標記為PRIMARY
  
  3.SUBQUERY ：在SELECT或者WHERE串列中包含了子查詢
  
  4.DERIVED ：在FROM串列中包含的子查詢被標記為DERIVED（衍生），MySQL會遞回執行這些子查詢，把結果放在臨時表里，
  
  5.UNION ：若第二個SELECT出現在UNION之后，則被標記為UNION;若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為：DERIVED
  
  6.UNION RESULT ：從UNION表獲取臨時表檢索結果的SELECT
```
explain select * from tbl_emp a
left join tbl_dept b
on a.deptId=b.id
union
select * from tbl_emp a
right join tbl_dept b
on a.deptId=b.id;
```
- table
  - 顯示這一行的資料是關于哪張表的
  - table列是 <derivenN> 格式，表示當前查詢依賴 id=N 的查詢，于是先執行 id=N 的查詢
  - 當有 union 時，UNION RESULT 的 table 列的值為 <union1,2>，1和2表示參與 union 的 select 行id，
- type
  - 表示關聯型別或是訪問型別，即MySQL決定如何查找表中的行
  - 訪問型別排列：從最好到最差依次是：system>const>eq_ref>ref>range>index>ALL
  - 顯示查詢使用了何種型別
    1. system ：表只有一行記錄（等于系統表），這是const型別的特例，平時不會出現，這個也可以忽略不計
    2. const ：表示通過索引一次就找到了，const用于比較primary key或者unique索引，因為只匹配一行資料，所以很快，如將主鍵至于where串列中，MySQL就能將該查詢轉換為一個常量
    3. eq_ref ：唯一性索引，對于每個索引鍵，表中只有一條記錄與之匹配，常見于主鍵或唯一索引掃描
      
      explain select * from tbl_emp as a left join tbl_dept as b on a.deptId=b.id;
      
      以上：a所有的主鍵索引被連接使用，最多回傳一個符合條件的值（一個員工只屬于一個部門）
    4. ref：非唯一索引( 普通索引或者聯合索引的部分前綴 )掃描，回傳匹配某個單獨值的所有行，
      本質上也是一種索引訪問，它回傳所有匹配某個單獨值的行，然而，
      它可能會找到多個符合條件的行，所以他應該屬于查找和掃描的混合體
      
      explain select * from tbl_emp where deptId="1";
      
      以上，有兩個部門id（普通索引）為1的值
      
      explain select * from t1 where name="12";
      
      以上，idx_name_age是name和age的聯合索引，使用部分前綴（name)type為ref，（age)type不是ref
    5. range ：只檢索給定范圍的行，使用一個索引來選擇行，key列顯示使用了哪個索引，一般就是在你的where陳述句中出現了between、<、>、in等的查詢，這種范圍掃描索引掃描比全表掃描要好，因為他只需要開始索引的某一點，而結束語另一點，不用掃描全部索引
    6. index ：Full Index Scan,index與ALL區別為index型別只遍歷索引樹，這通常比ALL快，因為索引檔案通常比資料檔案小，
      （也就是說雖然all和index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的）
      
      explain select t1.name,t1.age from t1;
      
      以上：只是掃描了idx_name_age索引
    7. all ：FullTable Scan,將遍歷全表以找到匹配的行
      
      備注：一般來說，得保證查詢只是達到range級別，最好達到ref
- possible_keys
  - 顯示可能應用在這張表中的索引,一個或多個，
- 查詢涉及的欄位上若存在索引，則該索引將被列出，但不一定被查詢實際使用
- key
  - 實際使用的索引，如果為null則沒有使用索引
  - 查詢中若使用了覆寫索引，則索引和查詢的select欄位重疊
- key_len
  - 表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度，在不損失精確性的情況下，長度越短越好
  - key_len顯示的值為索引最大可能長度，并非實際使用長度，即key_len是根據表定義的每個屬性的型別計算而得，不是通過表內檢索出的
    - 字串
      
      char(n)：n位元組長度
      
      varchar(n)：2位元組存盤字串長度，如果是utf-8，則長度 3n + 2
    - 數值型別
      
      tinyint：1位元組
      
      smallint：2位元組
      
      int：4位元組
      
      bigint：8位元組　　
    - 時間型別　
      
      date：3位元組
      
      timestamp：4位元組
      
      datetime：8位元組
    - 如果欄位允許為 NULL，需要1位元組記錄是否為 NULL
- ref
  - 顯示索引那一列被使用了，如果可能的話，是一個常數，那些列或常量被用于查找索引列上的值
    
    常見的有：const（常量），func，NULL，欄位名
- rows
  - 根據表統計資訊及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數越少越好
- Extra
  - 包含不適合在其他列中顯示但十分重要的額外資訊
    1.Using filesort:說明mysql會對資料使用一個外部的索引排序，而不是按照表內的索引順序進行讀取，
    
    MySQL中無法利用索引完成排序操作成為“檔案排序”
    
    2.Using temporary:
    - 使用了臨時表保存中間結果，MySQL在對查詢結果排序時使用臨時表，出現這種情況一般是要進行優化的，首先是想到用索引來優化，常見于排序order by 和分組查詢 group by
      
      explain select distinct gender from t1;
    ? 以上： gender沒有索引，此時創建了張臨時表來distinct，優化：創建和gender有關聯的索引
    
    3.USING index
    - 表示相應的select操作中使用了覆寫索引（Coveing Index）,避免訪問了表的資料行，效率不錯！
      
      如果同時出現using where，表明索引被用來執行索引鍵值的查找；
      如果沒有同時出現using where，表面索參考來讀取資料而非執行查找動作，
    4.Using where
    - 表面使用了where過濾
    5.using join buffer
    - 使用了連接快取
    6.impossible where
    - where子句的值總是false，不能用來獲取任何元組
    7.select tables optimized away
    - 在沒有GROUPBY子句的情況下，基于索引優化MIN/MAX操作或者
      對于MyISAM存盤引擎優化COUNT(*)操作，不必等到執行階段再進行計算，
      查詢執行計劃生成的階段即完成優化，
    8.distinct
    - 優化distinct，在找到第一匹配的元組后即停止找同樣值的作業

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/1101.html

標籤：MySQL

上一篇：heidisql新建用戶分配資料庫權限

下一篇：MySQL鎖這塊石頭似乎沒有我想的那么重