在mysql數(shù)據(jù)庫(kù)中為我們提供了explain方法可以通過它來(lái)幫助我們分析我們的sql語(yǔ)句瓶殃。
explain select * from customer;
explain參數(shù)說(shuō)明
select_type:select語(yǔ)句的類型，主要包括
SIMPLE:簡(jiǎn)單SELECT(不使用UNION或子查詢)
PRIMARY:最外面的SELECT
UNION:UNION中的第二個(gè)或后面的SELECT語(yǔ)句
DEPENDENT UNION:UNION中的第二個(gè)或后面的SELECT語(yǔ)句,取決于外面的查詢
UNION RESULT:UNION 的結(jié)果
SUBQUERY:子查詢中的第一個(gè)SELECT
DEPENDENT SUBQUERY:子查詢中的第一個(gè)SELECT,取決于外面的查詢
DERIVED:導(dǎo)出表的SELECT(FROM子句的子查詢)
table:查詢的表
possible_keys:指出MySQL能使用哪個(gè)索引在該表中找到行
key:顯示MySQL實(shí)際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL
key_len:使用索引的長(zhǎng)度
type：聯(lián)接類型。這個(gè)參數(shù)很重要稠炬。下面給出比較常用的幾種連接類型:
const:常數(shù)查找房待，如:主鍵践盼，唯一索引思瘟，會(huì)很快，因?yàn)樗鼈冎蛔x取一次!
eq_ref:對(duì)于每個(gè)來(lái)自于前面的表的行組合,從該表中讀取一行。這可能是最好的聯(lián)接類型,除了const類型剩拢。
ref:基于連接的查找
range:基于索引的范圍查找
index:基于索引的掃描线得。該聯(lián)接類型與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小裸扶。
ALL:對(duì)于每個(gè)來(lái)自于先前的表的行組合,進(jìn)行完整的表掃描框都。
rows ：掃描的行
extra :該列包含MySQL解決查詢的詳細(xì)信息,這個(gè)參數(shù)也很重要，主要情況有：
Distinct:MySQL發(fā)現(xiàn)第1個(gè)匹配行后,停止為當(dāng)前的行組合搜索更多的行呵晨。
Not exists:MySQL能夠?qū)Σ樵冞M(jìn)行LEFT JOIN優(yōu)化,發(fā)現(xiàn)1個(gè)匹配LEFT JOIN標(biāo)準(zhǔn)的行后,不再為前面的的行組合在該表內(nèi)檢查更多的行。
range checked for each record (index map: #):MySQL沒有發(fā)現(xiàn)好的可以使用的索引,但發(fā)現(xiàn)如果來(lái)自前面的表的列值已知,可能部分索引可以使用熬尺。
Using filesort:文件排序摸屠，需要利用額外的空間。
Using index:從只使用索引樹中的信息而不需要進(jìn)一步搜索讀取實(shí)際的行來(lái)檢索表中的列信息粱哼。
Using temporary:需要用臨時(shí)表來(lái)容納結(jié)果季二。
Using where:WHERE 子句用于限制哪一個(gè)行匹配下一個(gè)表或發(fā)送到客戶。
Using sort_union(...), Using union(...), Using intersect(...):這些函數(shù)說(shuō)明如何為index_merge聯(lián)接類型合并索引掃描揭措。
Using index for group-by:類似于訪問表的Using index方式,Using index for group-by表示MySQL發(fā)現(xiàn)了一個(gè)索引,
可以用來(lái)查 詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜索硬盤訪問實(shí)際的表胯舷。
注意：
當(dāng)我們的執(zhí)行計(jì)劃所執(zhí)行的結(jié)果的extra中如果出現(xiàn)Using temporary或者Using filesort時(shí)，這說(shuō)明我們的sql語(yǔ)句就需要進(jìn)行優(yōu)化了绊含。對(duì)于Using temporary桑嘶，大家并不陌生，當(dāng)我們的查詢涉及多張表時(shí)躬充，需要將查詢結(jié)果放入第三張臨時(shí)表中來(lái)存放逃顶。這樣勢(shì)必會(huì)降低我們的查詢效率，所以當(dāng)遇到extra中為Using temporary時(shí)充甚，也許就是我們應(yīng)該優(yōu)化的時(shí)候了以政。
filesort
但針對(duì)于filesort這種情況，我們就沒有temporary熟悉了伴找。它究竟是什么情況下發(fā)生的呢盈蛮？
這個(gè) filesort 并不是說(shuō)通過磁盤文件進(jìn)行排序，而只是告訴我們進(jìn)行了一個(gè)排序操作技矮。即在MySQL Query Optimizer 所給出的執(zhí)行計(jì)劃
(通過 EXPLAIN 命令查看)中被稱為文件排序（filesort）
文件排序是通過相應(yīng)的排序算法,將取得的數(shù)據(jù)在內(nèi)存中進(jìn)行排序: MySQL需要將數(shù)據(jù)在內(nèi)存中進(jìn)行排序抖誉，
所使用的內(nèi)存區(qū)域也就是我們通過sort_buffer_size 系統(tǒng)變量所設(shè)置的排序區(qū)。
這個(gè)排序區(qū)是每個(gè)Thread 獨(dú)享的穆役，所以說(shuō)可能在同一時(shí)刻在MySQL 中可能存在多個(gè)
sort buffer 內(nèi)存區(qū)域寸五。
在MySQL中filesort 的實(shí)現(xiàn)算法實(shí)際上是有兩種：
雙路排序：是首先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和可以直接定位行數(shù)據(jù)的行指針信息，然后在sort buffer 中進(jìn)行排序耿币。
單路排序：是一次性取出滿足條件行的所有字段梳杏，然后在sort buffer中進(jìn)行排序。
在MySQL4.1版本之前只有第一種排序算法雙路排序，第二種算法是從MySQL4.1開始的改進(jìn)算法十性，
主要目的是為了減少第一次算法中需要兩次訪問表數(shù)
據(jù)的 IO 操作叛溢，將兩次變成了一次，但相應(yīng)也會(huì)耗用更多的sortbuffer 空間劲适。
當(dāng)然楷掉，MySQL4.1開始的以后所有版本同時(shí)也支持第一種算法，
MySQL主要通過比較我們所設(shè)定的系統(tǒng)參數(shù) max_length_for_sort_data的大小和Query 語(yǔ)句所取出的字段類型大小總和來(lái)判定需要使用哪一種排序算法霞势。
如果 max_length_for_sort_data更大烹植，則使用第二種優(yōu)化后的算法，反之使用第一種算法愕贡。所以如果希望 ORDER BY 操作的效率盡可能的高草雕，
一定要主義max_length_for_sort_data 參數(shù)的設(shè)置。曾經(jīng)就有同事的數(shù)據(jù)庫(kù)出現(xiàn)大量的排序等待固以，造成系統(tǒng)負(fù)載很高墩虹，而且響應(yīng)時(shí)間變得很長(zhǎng)，
最后查出正是因?yàn)镸ySQL 使用了傳統(tǒng)的第一種排序算法而導(dǎo)致憨琳，在加大了max_length_for_sort_data 參數(shù)值之后诫钓，
系統(tǒng)負(fù)載馬上得到了大的緩解，響應(yīng)也快了很多篙螟。
優(yōu)化Filesort
當(dāng)無(wú)法避免排序操作時(shí)菌湃，又該如何來(lái)優(yōu)化呢？很顯然闲擦，應(yīng)該盡可能讓 MySQL 選擇使用第二種單路算法來(lái)進(jìn)行排序慢味。
這樣可以減少大量的隨機(jī)IO操作，很大幅度地提高排序工作的效率墅冷。
1纯路、加大 max_length_for_sort_data 參數(shù)的設(shè)置
在 MySQL 中，決定使用老式排序算法還是改進(jìn)版排序算法是通過參數(shù) max_length_for_ sort_data 來(lái)決定的寞忿。
當(dāng)所有返回字段的最大長(zhǎng)度小于這個(gè)參數(shù)值時(shí)驰唬，
MySQL 就會(huì)選擇改進(jìn)后的排序算法，反之腔彰，則選擇老式的算法叫编。所以，如果有充足的內(nèi)存讓MySQL 存放須要返回的非排序字段霹抛，
就可以加大這個(gè)參數(shù)的值來(lái)讓 MySQL 選擇使用改進(jìn)版的排序算法搓逾。
2.去掉不必要的返回字段
當(dāng)內(nèi)存不是很充裕時(shí)，不能簡(jiǎn)單地通過強(qiáng)行加大上面的參數(shù)來(lái)強(qiáng)迫 MySQL 去使用改進(jìn)版的排序算法杯拐，否則可能會(huì)造成 MySQL
不得不將數(shù)據(jù)分成很多段霞篡，
然后進(jìn)行排序世蔗，這樣可能會(huì)得不償失。此時(shí)就須要去掉不必要的返回字段朗兵，讓返回結(jié)果長(zhǎng)度適應(yīng) max_length_for_sort_data 參數(shù)的限制污淋。
3、增大 sort_buffer_size 參數(shù)設(shè)置
增大 sort_buffer_size 并不是為了讓 MySQL選擇改進(jìn)版的排序算法余掖，而是為了讓MySQL盡量減少在排序過程中對(duì)須要排序的數(shù)據(jù)進(jìn)行分段寸爆，
因?yàn)榉侄螘?huì)造成 MySQL 不得不使用臨時(shí)表來(lái)進(jìn)行交換排序。
小結(jié)
以上就是有關(guān)explain執(zhí)行計(jì)劃的一個(gè)簡(jiǎn)單的介紹盐欺。通過查看sql語(yǔ)句的執(zhí)行計(jì)劃赁豆，我們可以很容易的分析出sql語(yǔ)句在哪些子句或者哪些列，
返回結(jié)果等地方應(yīng)該進(jìn)行著重的優(yōu)化找田，為我們提高程序性能帶來(lái)很大的方便歌憨！
