一、Explain

1.1 explain作用

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

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

1.2 explain列說明

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

id

每個(gè)select對(duì)應(yīng)一個(gè)id值，其值是按 select 出現(xiàn)的順序增長的。

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

select_type

每個(gè)select對(duì)應(yīng)一個(gè)select_type，表示select的復(fù)雜度，有：

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

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

table

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

partiitons

    匹配的分區(qū)信息

type

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

select_type

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

possible_keys

    標(biāo)識(shí)某個(gè)表查詢時(shí)可能使用哪些索引來查找。        

key

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

key_len（key_len值計(jì)算）

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

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

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

key_len的計(jì)算：（舉幾個(gè)類型）

測試表test1 

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

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

  

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

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

  

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

 數(shù)值類型：

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

  

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

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

  

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

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

  

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

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

  

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

 時(shí)間類型：

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

  

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

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

  

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

    注：

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

ref

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

rows

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

filtered

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

Extra

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

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

  

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

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

  

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

  

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

  

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

  

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

  

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

• 

 總結(jié)

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

最新評(píng)論