一、Explain

1.1 explain作用

在sql語句前添加explain，作用是查看mysql對這條sql的執(zhí)行計劃信息。

思考：MYSQL執(zhí)行SQL語句時一定按這個執(zhí)行計劃執(zhí)行么？

1.2 explain列說明

在一條簡單SQL前面添加explain查看有哪些列，如下：

id

每個select對應一個id值，其值是按 select 出現(xiàn)的順序增長的。

注：id值越大執(zhí)行優(yōu)先級越高，id相同則從上往下執(zhí)行，id為NULL最后執(zhí)行

select_type

每個select對應一個select_type，表示select的復雜度，有：

SIMPLE：簡單查詢。查詢不包含子查詢和union，如上圖

    PRIMARY：對于包含UNION、UNION ALL或者子查詢的大查詢來說，它是由幾個小查詢組成的，其中最左邊的那個查詢的select_type值就是PRIMARY
    SUBQUERY：包含在 select 中的子查詢(不在 from 子句中)
    DERIVED：對于包含‘派生表’的查詢
    UNION：在 union 中的第二個和隨后的 select

table

    這一列表示 explain 的一行正在訪問哪個表。
    當 from 子句中有子查詢時，table列是 <derivenN> 格式，表示當前查詢依賴 id=N 的查詢，于是先執(zhí)行 id=N 的查 詢。
    當有 union 時，UNION RESULT 的 table 列的值為<union1,2>，1和2表示參與 union 的 select 行id。

partiitons

    匹配的分區(qū)信息

type

    這一列表示關聯(lián)類型或訪問類型
    效率從最優(yōu)到最差分別為:system > const > eq_ref > ref > range > index > ALL
    SQL性能優(yōu)化的目標：至少要達到range級別，要求是ref級別，最好是consts級別。
    system：當表中只有一條記錄并且該表使用的存儲引擎的統(tǒng)計數(shù)據(jù)都是精確地，表最多有一個匹配行，讀取1次，速度比較快。
    const：system是 const的特例，表里只有一條元組匹配時為system
    eq_ref：primary key 或 unique key 索引的所有部分被連接使用 ，最多只會返回一條符合條件的記錄。
    ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會 找到多個符合條件的行。
    range：用索引獲取某些范圍區(qū)間的記錄。

select_type

    每個select對應一個select_type，表示select的復雜度
    SIMPLE：簡單查詢。查詢不包含子查詢和union，如上圖
    PRIMARY：對于包含UNION、UNION ALL或者子查詢的大查詢來說，它是由幾個小查詢組成的，其中最左邊的那個查詢的select_type值就是PRIMARY
    SUBQUERY：包含在 select 中的子查詢(不在 from 子句中)
    DERIVED：對于包含‘派生表’的查詢
    UNION：在 union 中的第二個和隨后的 select

possible_keys

    標識某個表查詢時可能使用哪些索引來查找。        

key

    實際使用哪個索引。
    當possible_keys有值，而key沒有值時，可能是因為表數(shù)據(jù)很少，mysql認為沒有必要走索引，直接全表查詢了。
    當possible_keys為null時，可根據(jù)實際情況在where條件中添加索引來提升查詢效率。

key_len（key_len值計算）

     實際使用到的索引的字節(jié)數(shù)，幫我們檢查是否充分利用上了索引，對于聯(lián)合索引有一定的參考意義。

    比如有列n和address的聯(lián)合索引（表my_datas字段有id, n, address 和 time） 

     key_len=5，通過計算索引占的字節(jié)數(shù)來判斷出查詢使用了聯(lián)合索引中的第一個列。

key_len的計算：（舉幾個類型）

測試表test1 

字符串：char(n)和varchar(n)，5.0.3以后版本中，n均代表字符數(shù)，而不是字節(jié)數(shù)，如果是utf-8，一個數(shù)字或字母占1個字節(jié)，一個漢字占3個字節(jié)

• char(n)：如果存漢字長度就是 4n 字節(jié)（若可為空 則+1）

  

     col1_char是char(4)，那么len應該是 4*4 + 可為空1 = 17
     explain中key_len值為17
  

• varchar(n)：如果存漢字則長度是 4n + 2 字節(jié)（若可為空 則+1），加的2字節(jié)用來存儲字符串長度，因為 varchar是變長字符串。

  

     col2_varchar是varchar(32)，那么len應該是 32 * 4 + 2 + 1 = 131
     explain中key_len值為131
  

 數(shù)值類型：

• tinyint：1字節(jié)（若可為空 則+1）

  

     col3_tinyint是tinyint，那么len應該是 1+可為空1 = 2
     explain中key_len值為2   
  

• smallint：2字節(jié)（若可為空 則+1）

  

     col4_smallint是smallint，那么len應該是 2+可為空1 = 3
     explain中key_len值為3
  

• int：4字節(jié)（若可為空 則+1）

  

     col5_int是int，那么len應該是 4+可為空1 = 5
     explain中key_len值為5
  

• bigint：8字節(jié) （若可為空 則+1）

  

     col6_bigint是bigint，那么len應該是 8+可為空1 = 9
     explain中key_len值為9
  

 時間類型：

• date：3字節(jié)（若可為空 則+1）

  

     col7_date是date，那么len應該是 3 + 可為空1 = 4
     explain中key_len值為4
  

• timestamp：4字節(jié)（若可為空 則+1）

  

     col8_timestamp是timestamp，那么len應該是 4+可為空1 = 5
     explain中key_len值為5
     datetime：無小數(shù)秒位數(shù)，占5個字節(jié)。datetime(n) 其中n是保留的小數(shù)秒位數(shù)，額外占的存儲空間分別為
     n=0時      額外空間0字節(jié)
     n=1(或2)  額外空間1字節(jié)
     n=3(或4)  額外空間2字節(jié)
     n=5(或6)  額外空間3字節(jié)
  
     col9_datetime是datetime，那么len應該是 5+可為空1 = 6
     explain中key_len值為6
  

    注：

     - myisam 表，單列索引，最大長度不能超過 1000 bytes，否則會報警，但是創(chuàng)建成功，最終創(chuàng)建的是前綴索引（取前333個字符）；
     - myisam 表，組合索引，索引長度和不能超過 1000 bytes，否則會報錯，創(chuàng)建失??；
     - innodb 表，單列索引，超過 767 bytes的，給出warning，最終索引創(chuàng)建成功，取前綴索引（取前 255 字符）；
     - innodb 表，組合索引，各列長度不超過 767 bytes ，如果有超過 767 bytes 的，則給出報警，索引最后創(chuàng)建成功， 但是對于超過 767 字節(jié)的列取前綴索引，與索引列順序無關，總和不得超過 3072 ，否則失敗，無法創(chuàng)建；

ref

    這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見的有:const(常量)、字段名（庫名.表名.列名 如test.test.col1_char)

rows

    這一列是mysql估計要讀取并檢測的行數(shù)，值越小越優(yōu)
    注意這個不是結果集里的行數(shù)                

filtered

    通過索引掃描表估計要讀取并檢測的行數(shù)rows。
    使用額外的查詢條件對rows行的數(shù)據(jù)進行過濾行得有行數(shù)n占rows的比例，
    即 n/rows * 100%

Extra

    sql執(zhí)行計劃比較重要的參考信息，常見重要信息如下：

• 1 Using index：使用索引覆蓋
   
  索引覆蓋：查詢的字段信息從這條sql使用的索引（輔助索引）樹中獲取。如：

  

  查詢的字段是索引（col4_smallint）中的字段col4_smallint信息
  也就是說，不需要通過輔助索引找到主鍵，再通過主鍵樹獲取想要的信息

• 2 Using where：使用where查詢數(shù)據(jù)，需要回表去獲取需要的數(shù)據(jù)

  

• 3 Using index condition：相當于索引覆蓋后通過主鍵回表查詢，再通過where過濾
   

  

• 4 Using temporary：創(chuàng)建一張臨時表來處理查詢

  

• 5 Using filesort：顧名思義，使用文件（磁盤中）排序。mysql做了優(yōu)化，數(shù)據(jù)較少時排序是在內在中進行的，數(shù)據(jù)量較大時才會在磁盤中進行排序。出現(xiàn)這種情況，就要考慮添加索引來優(yōu)化SQL了。

  

     col1_char 未創(chuàng)建索引，mysql先預覽整個表對col1_char進行排序和對應的主鍵值序列，再通過主鍵值回主鍵索引樹查詢數(shù)據(jù)返回。
      對 col1_char 添加索引之后，執(zhí)行計劃結果如下：

  

  6 Select tables optimized away：使用函數(shù)來查詢某個索引信息時

• 

 總結

到此這篇關于輕松上手MYSQL之SQL優(yōu)化之Explain詳解的文章就介紹到這了,更多相關SQL優(yōu)化Explain內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論