Explain關(guān)鍵字是我們?cè)谧鰏ql性能調(diào)休的時(shí)候用到的，語(yǔ)法很簡(jiǎn)單就是在Select前面加上Explain。explain的返回結(jié)果有這些列：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。

image.png

Explain 的第一列 Id

Id代筆著sql的執(zhí)行順序，Id不同，sql的執(zhí)行順序是，先執(zhí)行Id大的Sql，后執(zhí)行Id小的sql;
Id相同，Sql的執(zhí)行順序是，同上到下依此執(zhí)行。

EXPLAIN 
SELECT a.cxy_name from tb_cxy01 a where a.cxy_sex > (
    SELECT cxy_sex from tb_cxy01 where tb_cxy01.cxy_name = 'ayueyue'
) a

image.png

這個(gè)查詢就先執(zhí)行id=2的子查詢,查tb_cxy01表得到結(jié)果表a,然后再?gòu)谋韆中執(zhí)行查詢。

Explain 的第二列 Select_type

表示查詢中每個(gè)子句的類型， 常見的有下面5種類型
1：Simple : 簡(jiǎn)單的SELECT查詢,如下面的Case

  EXPLAIN SELECT * from tb_cxy01

image.png

2：Primary: 子查詢的最外層查詢
3：SUBQUERY: 子查詢，子查詢中的第一個(gè)SELECT，結(jié)果不依賴于外部查詢)

  EXPLAIN
  SELECT * from tb_cxy01 a where a.cxy_sex > (
       SELECT tb_cxy01.cxy_sex from tb_cxy01 where tb_cxy01.cxy_sex > 13
   )

image.png

查詢中若包含任何復(fù)雜的子部分，最外層的select被標(biāo)記為PRIMARY,如：最外層的tb_cxy01別名為a,查詢解析時(shí)被設(shè)置為primary;而內(nèi)層的查詢被設(shè)置成subquery,如我們括號(hào)里面的select查詢。
4：UNION

EXPLAIN
SELECT a.cxy_name,a.cxy_sex from tb_cxy01 a where a.cxy_sex > 90
UNION
SELECT a.cxy_name,a.cxy_sex from tb_cxy01 a where a.cxy_id > 10

image.png

5：DERIVED
我們外層查詢的from后面是用的表是來(lái)自內(nèi)層查詢返回的臨時(shí)表時(shí)，那這個(gè)臨時(shí)表就被
稱為derived表。

EXPLAIN
SELECT * from (
       SELECT tb_cxy01.cxy_sex from tb_cxy01 where tb_cxy01.cxy_name = 'ayueyue'
 ) a

image.png

image.png

Explain 的第二列 type

查詢時(shí)使用的索引類型，既然是索引類型那就必須得創(chuàng)建索引，如果表上沒有索引就不存在索引優(yōu)化這種說(shuō)法。Type共有7種，性能由好到差分別是System, const, eq_ref,ref, range, index, all。
1：System: 查詢的是系統(tǒng)表，并且系統(tǒng)表中只有一條數(shù)據(jù)的時(shí)候?；蛘遜erived表只有一條數(shù)據(jù)的主查詢，所以System級(jí)別一般是達(dá)不到的。
2：Const: 查詢匹配的數(shù)據(jù)有且只有一條，并且查詢的where條件只能是primary key或者unique key。所以基本上也是達(dá)不到的。如：下面的Sql,通過(guò)primay key來(lái)查詢數(shù)據(jù)

EXPLAIN
    SELECT tb_cxy01.cxy_sex from tb_cxy01 where tb_cxy01.cxy_id = 13

image.png

然后我們給表tb_cxy01的cxy_name創(chuàng)建一個(gè)普通索引，再來(lái)查看查詢時(shí)使用的索引類型

create index iNormal_cxy_name on tb_cxy01(cxy_name)
EXPLAIN
     SELECT tb_cxy01.cxy_sex from tb_cxy01 where tb_cxy01.cxy_name = 'ayueyue'

image.png

  EXPLAIN 
  SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_name = 'abc'

image.png

我們發(fā)現(xiàn)這時(shí)候索引類型并不是eq_ref,而是ref.然后查了一下mysql的官方文檔：

image.png

文檔中說(shuō)的是這張表和前面的一張表組合后生成的一條數(shù)據(jù)。示例中也是2表聯(lián)查。所以猜想應(yīng)該是eq_ref適用的場(chǎng)景是2表聯(lián)查。然后修改Sql如下：

  EXPLAIN 
SELECT a.cxy_name,b.house_master FROM tb_cxy01 a INNER JOIN tb_house01 b ON a.cxy_id = b.house_master

image.png

這時(shí)我可以看出先執(zhí)行了表tb_house,因?yàn)槲覜]有對(duì)house_master創(chuàng)建索引，所以是全表掃描。對(duì)于表tb_cxy01執(zhí)行的是eq_ref(2表聯(lián)合查詢，另外一張表使用的是primary key或unique key時(shí)可以達(dá)到eq_ref級(jí)別)
4：ref，通過(guò)索引查詢返回的結(jié)果可以是多個(gè)，0個(gè)或1個(gè)。這個(gè)索引級(jí)別就很好達(dá)到。也是我們?cè)谧鯯ql優(yōu)化時(shí)盡量去達(dá)到。
給tb_cxy01表的name字段添加普通索引，tb_house表的master字段添加普通索引，然后初始化數(shù)據(jù)如下：

image.png

編寫sql 如下：

EXPLAIN 
SELECT a.cxy_name,b.house_master FROM tb_cxy01 a, tb_house01 b where a.cxy_name = b.house_master

image.png

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_name = 'lisi'

image.png

（所以，ref可以適用于單表也可以適用于聯(lián)表查詢。即可以適用于唯一索引也可以適用于普通索引。返回的數(shù)據(jù)可以是0條也可以是多條。）
5：range,檢索指定范圍的行，where條件一般是between, in , < , >

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_id BETWEEN 11 and 13

image.png

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_id > 11

image.png

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_id in ( 11,12,13,14)

image.png

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_id > 90

image.png

意思就是把cxy_id這一列的所有數(shù)據(jù)遍歷了一遍。
7：all, 沒有使用到索引，把整個(gè)表中的每一列的數(shù)據(jù)都遍歷了一遍,如我們表中cxy_name字段沒有添加索引，然后我們來(lái)執(zhí)行下面的sql

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_name > 'asdfasdf'

image.png

Explain 的Possible_keys,Key 字段

Possible_keys: 可能會(huì)用到的索引，預(yù)測(cè)此次查詢可能會(huì)使用的索引
Key:當(dāng)前查詢實(shí)際用到的索引

Explain 的Key_len字段

索引的長(zhǎng)度（查詢時(shí)候使用到的索引的長(zhǎng)度），用于判斷復(fù)合索引是否被完全使用。
1:不為null的char類型字段創(chuàng)建索引
下面來(lái)創(chuàng)建一張表tb_key_len,創(chuàng)建字段col2, 定長(zhǎng)類型，20個(gè)字符，不能為null；然后執(zhí)行如下sql

create index index_col2 on tb_key_len(col2)
EXPLAIN 
SELECT * from tb_key_len WHERE col2= 'abc'

image.png

從執(zhí)行的解釋來(lái)看key_len的長(zhǎng)度是60。 因?yàn)閏ol2是char(20),在mysql utf8中每個(gè)字符占用3個(gè)字節(jié)，所以key_len = 20 * 3 = 60;
2:可以為null的char類型字段創(chuàng)建索引
創(chuàng)建字段col3, 定長(zhǎng)類型，20個(gè)字符，可以為null；然后執(zhí)行如下sql

  create index index_col3 on tb_key_len(col3)
EXPLAIN 
SELECT * from tb_key_len WHERE col3= 'cde'

image.png

這次執(zhí)行，key_len變成了61，是因?yàn)閏ol3是可以允許為null的，所以mysql會(huì)額外使用一個(gè)字節(jié)來(lái)標(biāo)識(shí)這個(gè)索引的字段是可以為null的，61 = 20 * 3 + 1(可以為null);
3:不為null的varchar類型字段創(chuàng)建索引
創(chuàng)建字段col4, varchar類型，20個(gè)字符，不可以為null；然后執(zhí)行如下sql

create index index_col4 on tb_key_len(col4)
EXPLAIN 
SELECT * from tb_key_len WHERE col4= 'wsx'

image.png

這次解釋執(zhí)行后，key_len變成了62，因?yàn)閙ysql在創(chuàng)建索引時(shí)候，如果遇到了變長(zhǎng)類型的字段，會(huì)再增加2個(gè)字節(jié)來(lái)標(biāo)識(shí)這個(gè)索引使用的字段是可變長(zhǎng)度的：62 = 20 * 3 + 2(可變長(zhǎng)度)
3:可以為null的varchar類型字段創(chuàng)建索引
創(chuàng)建字段col5, varchar類型，20個(gè)字符，可以為null；然后執(zhí)行如下sql

create index index_col5 on tb_key_len(col5)
EXPLAIN 
SELECT * from tb_key_len WHERE col5= 'wsx'

image.png

到這里后，大家可能會(huì)有疑問(wèn)，剛才測(cè)試的都是varchar, char,但是int吶？ 復(fù)合索引那？
下面我們?cè)黾?個(gè)字段col6 int(11) null, col7 int(11) not null,并對(duì)col6和col7創(chuàng)建復(fù)合索引，執(zhí)行下面sql:

create index index_col86_col7 on tb_key_len(col6,col7)
EXPLAIN 
 SELECT * from tb_key_len WHERE col6= 1

image.png

我們發(fā)現(xiàn)key_len是5 ，因?yàn)閏ol6時(shí)可以為null的mysql會(huì)花一個(gè)字節(jié)來(lái)標(biāo)識(shí)，所以，col6字段的索引應(yīng)該是4,同時(shí)因?yàn)槲覀兊腟ql中只使用了一個(gè)索引，所以是4+1 = 5;
然后我們?cè)賵?zhí)行一個(gè)sql來(lái)驗(yàn)證：

EXPLAIN 
 SELECT * from tb_key_len WHERE col6= 1 and col7 =0

image.png

這次key_len變成了9，因?yàn)槲覀兪褂昧藦?fù)合索引，col6,col7,長(zhǎng)度就是 4 + 4 +1;所以int類型的字段索引長(zhǎng)度固定是4個(gè)字節(jié)。

Explain 的ref字段

查詢時(shí)使用的索引參照的是哪個(gè)字段，如果沒有走索引顯示為null, 如果索引條件是常量顯示的是const,如果索引使用的是某個(gè)字段則顯示的是db.tbl.field
1:單表常量查詢

EXPLAIN 
SELECT * from tb_cxy01 tc where tc.cxy_name = 'jkl'

image.png

我們?cè)谧侄蝐xy_name上創(chuàng)建索引，然后再查詢的時(shí)候使用常量’jkl’來(lái)查詢，ref字段顯示的是const。
2:不使用索引查詢
字段cxy_sex上不創(chuàng)建索引，然后執(zhí)行如下語(yǔ)句：

EXPLAIN 
SELECT * from tb_cxy01 tc where tc.cxy_sex = 90

image.png

從explain的解析上可以看出來(lái)，當(dāng)查詢語(yǔ)句沒有使用索引時(shí)，執(zhí)行的是全表掃描，同時(shí)ref字段顯示的是null
2:二張表聯(lián)合查詢
我們?cè)趫?zhí)行一個(gè)2表聯(lián)合查詢的Sql語(yǔ)句,字段cxy_name,house_master都創(chuàng)建了索引。

EXPLAIN 
SELECT * from tb_cxy01 tc INNER JOIN tb_house01 th on tc.cxy_name = th.house_master  
where tc.cxy_sex = 90

image.png

從執(zhí)行結(jié)果來(lái)看，mysql先執(zhí)行了tb_cxy01的查詢，因?yàn)閏xy_sex沒有創(chuàng)建索引所以是全表掃描,同樣ref的結(jié)果就是null.然后又對(duì)tb_house執(zhí)行查詢，使用的house_master字段，因?yàn)樵撟侄蝿?chuàng)建了索引，所以key是house_naster索引，同樣ref顯示就是db.tbl.field。因?yàn)閔ouse_master的取值是依賴與tb_cxy表的cxy_name, 所以ref顯示的是：cdb_cxy.tc.cxy_name

Explain 的Row字段

顯示執(zhí)行計(jì)劃中查詢了多少行

Explain 的Extra字段

執(zhí)行計(jì)劃的附加信息，下面就解釋一下Using filesort，這個(gè)是我們平常寫sql時(shí)常出現(xiàn)的一個(gè)屬性，也是sql執(zhí)行的性能殺手
Using filesort的出現(xiàn)一般是因?yàn)槌霈F(xiàn)了2次排序，下面執(zhí)行2個(gè) Case方便理解
1:where的字段和order by的字段是同一個(gè)

EXPLAIN 
SELECT * from tb_cxy01 tc where tc.cxy_sex = 90 ORDER BY tc.cxy_sex DESC

image.png

上面的Sql查詢的條件是cxy_sex, 我們排序的字段也是cxy_sex,mysql就可以先做查詢?nèi)缓髮?duì)查詢出來(lái)的結(jié)果再做排序處理。所以Extra字段顯示的是using where
2:where的字段和order by的字段不是同一個(gè)

EXPLAIN 
SELECT * from tb_cxy01 tc where tc.cxy_sex = 90 ORDER BY tc.cxy_name DESC

image.png

EXPLAIN
SELECT * from tb_new tn where tn.pcode = '' ORDER BY tn.ppwd

image.png

發(fā)現(xiàn)當(dāng)我們where pcode, order by ppwd時(shí)，出現(xiàn)了using filesort
然后我們?cè)賵?zhí)行下面這個(gè)sql

EXPLAIN
SELECT * from tb_new tn where tn.pcode = '' ORDER BY tn.premark

image.png