mysql效能分析之explain的用法

mysql效能分析之explain的用法
之前是一直沒有聽過explain這個關鍵字的， 最近因為專案中總是會有慢查詢的一些操作， 所以請教了旁邊的同事幫忙排查下原因， 看到同事用explain來分析一些sql語句， 感覺好像發現了新大陸一樣。

於是自己也來學習下並總結自己學到的一些知識，如有問題歡迎拍磚。

首先來做個測試， 我這裡有一張表： t_jiakao_keyword, 資料量在60W左右。
1， select * from t_jiakao_keyword

然後使用explain進行各種查詢：

通過主鍵id去查詢，其中有幾個關鍵的指標需要關注：
首先看下 type 這列的結果，如果有型別是 ALL 時，表示預計會進行全表掃描（full table scan）。通常全表掃描的代價是比較大的，建議建立適當的索引，通過索引檢索避免全表掃描。此外，全索引掃描（full index scan）的代價有時候是比全表掃描還要高的，除非是基於InnoDB表的主鍵索引掃描。

再來看下 Extra 列的結果，如果有出現 Using temporary 或者 Using filesort 則要多加關注：
Using temporary，表示需要建立臨時表以滿足需求，通常是因為GROUP BY的列沒有索引，或者GROUP BY和ORDER BY的列不一樣，也需要建立臨時表，建議新增適當的索引。

Using filesort，表示無法利用索引完成排序，也有可能是因為多表連線時，排序欄位不是驅動表中的欄位，因此也沒辦法利用索引完成排序，建議新增適當的索引。

Using where，通常是因為全表掃描或全索引掃描時（type 列顯示為 ALL 或 index），又加上了WHERE條件，建議新增適當的索引。
暫時想到上面幾個，如果有遺漏，以後再補充。

其他狀態例如：Using index、Using index condition、Using index for group-by 則都還好，不用緊張。

最後放出所有欄位的一些說明及含義。sql查詢最重要的是合理使用索引， 關鍵點還是在索引！
http://www.138edu.com/28010/study_56882.shtml
http://www.138edu.com/28010/study_56884.shtml
http://www.138edu.com/28010/study_56885.shtml
http://www.138edu.com/28010/study_56886.shtml
http://www.138edu.com/28010/study_56888.shtml
http://www.138edu.com/28010/study_56889.shtml
http://www.138edu.com/28010/study_56890.shtml
http://www.138edu.com/28010/study_56892.shtml
http://www.138edu.com/28010/study_56893.shtml
http://www.138edu.com/28010/study_56894.shtml
http://www.138edu.com/28010/study_56895.shtml
http://www.138edu.com/28010/study_56897.shtml
http://www.138edu.com/28010/study_56898.shtml
http://www.138edu.com/28010/study_56899.shtml
http://www.138edu.com/28010/study_56901.shtml
http://www.138edu.com/28010/study_56902.shtml
http://www.138edu.com/28010/study_56903.shtml
http://www.138edu.com/28010/study_56905.shtml
http://www.138edu.com/28010/study_56906.shtml
http://www.138edu.com/28010/study_56907.shtml
http://www.138edu.com/28010/study_56909.shtml
http://www.138edu.com/28010/study_56910.shtml
http://www.138edu.com/28010/study_56911.shtml
http://www.138edu.com/28010/study_56913.shtml
http://www.138edu.com/28010/study_56914.shtml
http://www.138edu.com/28010/study_56915.shtml
http://www.138edu.com/28010/study_56917.shtml
http://www.138edu.com/28010/study_56918.shtml
http://www.138edu.com/28010/study_56919.shtml
http://www.138edu.com/28010/study_56921.shtml
http://www.138edu.com/28010/study_56922.shtml
http://www.138edu.com/28010/study_56924.shtml
http://www.138edu.com/28010/study_56925.shtml
http://www.138edu.com/28010/study_56926.shtml
http://www.138edu.com/28010/study_56928.shtml
http://www.138edu.com/28010/study_56929.shtml
http://www.138edu.com/28010/study_56930.shtml
http://www.138edu.com/28010/study_56932.shtml
http://www.138edu.com/28010/study_56933.shtml
http://www.138edu.com/28010/study_56935.shtml
http://www.138edu.com/28010/study_56936.shtml
http://www.138edu.com/28010/study_56937.shtml
http://www.138edu.com/28010/study_56939.shtml
http://www.138edu.com/28010/study_56940.shtml
http://www.138edu.com/28010/study_56942.shtml
http://www.138edu.com/28010/study_56943.shtml
http://www.138edu.com/28010/study_56944.shtml
http://www.138edu.com/28010/study_56946.shtml
http://www.138edu.com/28010/study_56947.shtml
http://www.138edu.com/28010/study_56949.shtml
http://www.138edu.com/28010/study_56950.shtml
http://www.138edu.com/28010/study_56951.shtml
http://www.138edu.com/28010/study_56953.shtml
http://www.138edu.com/28010/study_56954.shtml
http://www.138edu.com/28010/study_56956.shtml
http://www.138edu.com/28010/study_56957.shtml
http://www.138edu.com/28010/study_56958.shtml
http://www.138edu.com/28010/study_56960.shtml
http://www.138edu.com/28010/study_56961.shtml
http://www.138edu.com/28010/study_56963.shtml
http://www.138edu.com/28010/study_56964.shtml
http://www.138edu.com/28010/study_56965.shtml
http://www.138edu.com/28010/study_56967.shtml
http://www.138edu.com/28010/study_56968.shtml
http://www.138edu.com/28010/study_56970.shtml
http://www.138edu.com/28010/study_56971.shtml
http://www.138edu.com/28010/study_56973.shtml
http://www.138edu.com/28010/study_56974.shtml
http://www.138edu.com/28010/study_56976.shtml
http://www.138edu.com/28010/study_56977.shtml
http://www.138edu.com/28010/study_56979.shtml
http://www.138edu.com/28010/study_56980.shtml
http://www.138edu.com/28010/study_56981.shtml
http://www.138edu.com/28010/study_56983.shtml
http://www.138edu.com/28010/study_56985.shtml
http://www.138edu.com/28010/study_56986.shtml
http://www.138edu.com/28010/study_56987.shtml
http://www.138edu.com/28010/study_56989.shtml
http://www.138edu.com/28010/study_56990.shtml
http://www.138edu.com/28010/study_56992.shtml
http://www.138edu.com/28010/study_56993.shtml
http://www.138edu.com/28010/study_56995.shtml
http://www.138edu.com/28010/study_56996.shtml
http://www.138edu.com/28010/study_56998.shtml
http://www.138edu.com/28010/study_56999.shtml
http://www.138edu.com/28010/study_57001.shtml
http://www.138edu.com/28010/study_57002.shtml
http://www.138edu.com/28010/study_57004.shtml
http://www.138edu.com/28010/study_57005.shtml
http://www.138edu.com/28010/study_57006.shtml
http://www.138edu.com/28010/study_57008.shtml
http://www.138edu.com/28010/study_57009.shtml
http://www.138edu.com/28010/study_57011.shtml
http://www.138edu.com/28010/study_57012.shtml
http://www.138edu.com/28010/study_57014.shtml
http://www.138edu.com/28010/study_57015.shtml
http://www.138edu.com/28010/study_57017.shtml
http://www.138edu.com/28010/study_57018.shtml
http://www.138edu.com/28010/study_57020.shtml
http://www.138edu.com/28010/study_57022.shtml
http://www.138edu.com/28010/study_57023.shtml
http://www.138edu.com/28010/study_57025.shtml
http://www.138edu.com/28010/study_57026.shtml
http://www.138edu.com/28010/study_57028.shtml
http://www.138edu.com/28010/study_57029.shtml
http://www.138edu.com/28010/study_57031.shtml
http://www.138edu.com/28010/study_57032.shtml
http://www.138edu.com/28010/study_57034.shtml
http://www.138edu.com/28010/study_57035.shtml
http://www.138edu.com/28010/study_57037.shtml
http://www.138edu.com/28010/study_57038.shtml
http://www.138edu.com/28010/study_57040.shtml
http://www.138edu.com/28010/study_57042.shtml
http://www.138edu.com/28010/study_57043.shtml
http://www.138edu.com/28010/study_57045.shtml
http://www.138edu.com/28010/study_57046.shtml
http://www.138edu.com/28010/study_57048.shtml
http://www.138edu.com/28010/study_57050.shtml
http://www.138edu.com/28010/study_57051.shtml
http://www.138edu.com/28010/study_57053.shtml
http://www.138edu.com/28010/study_57054.shtml
http://www.138edu.com/28010/study_57056.shtml
http://www.138edu.com/28010/study_57057.shtml
http://www.138edu.com/28010/study_57059.shtml
http://www.138edu.com/28010/study_57061.shtml
http://www.138edu.com/28010/study_57062.shtml
http://www.138edu.com/28010/study_57064.shtml
http://www.138edu.com/28010/study_57065.shtml
http://www.138edu.com/28010/study_57067.shtml
http://www.138edu.com/28010/study_57068.shtml
http://www.138edu.com/28010/study_57070.shtml
http://www.138edu.com/28010/study_57071.shtml
http://www.138edu.com/28010/study_57073.shtml
http://www.138edu.com/28010/study_57075.shtml
http://www.138edu.com/28010/study_57077.shtml
http://www.138edu.com/28010/study_57078.shtml
http://www.138edu.com/28010/study_57080.shtml
http://www.138edu.com/28010/study_57081.shtml
http://www.138edu.com/28010/study_57083.shtml
http://www.138edu.com/28010/study_57084.shtml
http://www.138edu.com/28010/study_57086.shtml
http://www.138edu.com/28010/study_57087.shtml
http://www.138edu.com/28010/study_57089.shtml
http://www.138edu.com/28010/study_57090.shtml
http://www.138edu.com/28010/study_57092.shtml
http://www.138edu.com/28010/study_57093.shtml
http://www.138edu.com/28010/study_57095.shtml
http://www.138edu.com/28010/study_57096.shtml
http://www.138edu.com/28010/study_57099.shtml
http://www.138edu.com/28010/study_57100.shtml
http://www.138edu.com/28010/study_57102.shtml
http://www.138edu.com/28010/study_57103.shtml
http://www.138edu.com/28010/study_57105.shtml
http://www.138edu.com/28010/study_57106.shtml
http://www.138edu.com/28010/study_57108.shtml
http://www.138edu.com/28010/study_57109.shtml
http://www.138edu.com/28010/study_57111.shtml
http://www.138edu.com/28010/study_57112.shtml
http://www.138edu.com/28010/study_57114.shtml
http://www.138edu.com/28010/study_57115.shtml
http://www.138edu.com/28010/study_57117.shtml
http://www.138edu.com/28010/study_57118.shtml
http://www.138edu.com/28010/study_57120.shtml
http://www.138edu.com/28010/study_57121.shtml
http://www.138edu.com/28010/study_57123.shtml
http://www.138edu.com/28010/study_57125.shtml
http://www.138edu.com/28010/study_57126.shtml
http://www.138edu.com/28010/study_57128.shtml
http://www.138edu.com/28010/study_57129.shtml
http://www.138edu.com/28010/study_57131.shtml
http://www.138edu.com/28010/study_57132.shtml
http://www.138edu.com/28010/study_57134.shtml
http://www.138edu.com/28010/study_57135.shtml
http://www.138edu.com/28010/study_57137.shtml
http://www.138edu.com/28010/study_57138.shtml
http://www.138edu.com/28010/study_57140.shtml
http://www.138edu.com/28010/study_57141.shtml
http://www.138edu.com/28010/study_57143.shtml
http://www.138edu.com/28010/study_57145.shtml
http://www.138edu.com/28010/study_57146.shtml
http://www.138edu.com/28010/study_57148.shtml
http://www.138edu.com/28010/study_57149.shtml
http://www.138edu.com/28010/study_57151.shtml
http://www.138edu.com/28010/study_57152.shtml
http://www.138edu.com/28010/study_57154.shtml
http://www.138edu.com/28010/study_57155.shtml
http://www.138edu.com/28010/study_57157.shtml
http://www.138edu.com/28010/study_57158.shtml
http://www.138edu.com/28010/study_57160.shtml
http://www.138edu.com/28010/study_57161.shtml
http://www.138edu.com/28010/study_57162.shtml
http://www.138edu.com/28010/study_57164.shtml
http://www.138edu.com/28010/study_57165.shtml
http://www.138edu.com/28010/study_57166.shtml
http://www.138edu.com/28010/study_57168.shtml
http://www.138edu.com/28010/study_57169.shtml
http://www.138edu.com/28010/study_57171.shtml
http://www.138edu.com/28010/study_57172.shtml
http://www.138edu.com/28010/study_57174.shtml
http://www.138edu.com/28010/study_57175.shtml
http://www.138edu.com/28010/study_57177.shtml
http://www.138edu.com/28010/study_57178.shtml
http://www.138edu.com/28010/study_57180.shtml
http://www.138edu.com/28010/study_57182.shtml
http://www.138edu.com/28010/study_57183.shtml
http://www.138edu.com/28010/study_57185.shtml
http://www.138edu.com/28010/study_57186.shtml
http://www.138edu.com/28010/study_57188.shtml
http://www.138edu.com/28010/study_57190.shtml
http://www.138edu.com/28010/study_57191.shtml
http://www.138edu.com/28010/study_57193.shtml
http://www.138edu.com/28010/study_57194.shtml
http://www.138edu.com/28010/study_57196.shtml
http://www.138edu.com/28010/study_57197.shtml
http://www.138edu.com/28010/study_57199.shtml
http://www.138edu.com/28010/study_57201.shtml
http://www.138edu.com/28010/study_57202.shtml
http://www.138edu.com/28010/study_57204.shtml
http://www.138edu.com/28010/study_57205.shtml
http://www.138edu.com/28010/study_57207.shtml
http://www.138edu.com/28010/study_57209.shtml
http://www.138edu.com/28010/study_57210.shtml
http://www.138edu.com/28010/study_57212.shtml
http://www.138edu.com/28010/study_57213.shtml
http://www.138edu.com/28010/study_57215.shtml
http://www.138edu.com/28010/study_57216.shtml
http://www.138edu.com/28010/study_57218.shtml
http://www.138edu.com/28010/study_57220.shtml
http://www.138edu.com/28010/study_57222.shtml
http://www.138edu.com/28010/study_57223.shtml
http://www.138edu.com/28010/study_57225.shtml
http://www.138edu.com/28010/study_57226.shtml
http://www.138edu.com/28010/study_57227.shtml
http://www.138edu.com/28010/study_57229.shtml
http://www.138edu.com/28010/study_57230.shtml
http://www.138edu.com/28010/study_57232.shtml
http://www.138edu.com/28010/study_57233.shtml
http://www.138edu.com/28010/study_57235.shtml
http://www.138edu.com/28010/study_57236.shtml
http://www.138edu.com/28010/study_57238.shtml
http://www.138edu.com/28010/study_57240.shtml
http://www.138edu.com/28010/study_57241.shtml
http://www.138edu.com/28010/study_57243.shtml
http://www.138edu.com/28010/study_57244.shtml
http://www.138edu.com/28010/study_57246.shtml
http://www.138edu.com/28010/study_57248.shtml
http://www.138edu.com/28010/study_57249.shtml
http://www.138edu.com/28010/study_57251.shtml
http://www.138edu.com/28010/study_57252.shtml
http://www.138edu.com/28010/study_57254.shtml
http://www.138edu.com/28010/study_57256.shtml
http://www.138edu.com/28010/study_57257.shtml
http://www.138edu.com/28010/study_57259.shtml
http://www.138edu.com/28010/study_57260.shtml
http://www.138edu.com/28010/study_57262.shtml
http://www.138edu.com/28010/study_57263.shtml
http://www.138edu.com/28010/study_57265.shtml
http://www.138edu.com/28010/study_57266.shtml
http://www.138edu.com/28010/study_57268.shtml
http://www.138edu.com/28010/study_57270.shtml
http://www.138edu.com/28010/study_57271.shtml
http://www.138edu.com/28010/study_57273.shtml
http://www.138edu.com/28010/study_57275.shtml
http://www.138edu.com/28010/study_57276.shtml
http://www.138edu.com/28010/study_57278.shtml
http://www.138edu.com/28010/study_57280.shtml
http://www.138edu.com/28010/study_57284.shtml
http://www.138edu.com/28010/study_57286.shtml
http://www.138edu.com/28010/study_57287.shtml
http://www.138edu.com/28010/study_57288.shtml
http://www.138edu.com/28010/study_57290.shtml
http://www.138edu.com/28010/study_57294.shtml
http://www.138edu.com/28010/study_57295.shtml
http://www.138edu.com/28010/study_57296.shtml
http://www.138edu.com/28010/study_57298.shtml
http://www.138edu.com/28010/study_57301.shtml
http://www.138edu.com/28010/study_57302.shtml
http://www.138edu.com/28010/study_57304.shtml
http://www.138edu.com/28010/study_57305.shtml
http://www.138edu.com/28010/study_57307.shtml
http://www.138edu.com/28010/study_57309.shtml
http://www.138edu.com/28010/study_57310.shtml
http://www.138edu.com/28010/study_57311.shtml
http://www.138edu.com/28010/study_57313.shtml
http://www.138edu.com/28010/study_57314.shtml
http://www.138edu.com/28010/study_57316.shtml
http://www.138edu.com/28010/study_57317.shtml
http://www.138edu.com/28010/study_57319.shtml
http://www.138edu.com/28010/study_57320.shtml
http://www.138edu.com/28010/study_57322.shtml
http://www.138edu.com/28010/study_57323.shtml
http://www.138edu.com/28010/study_57325.shtml
http://www.138edu.com/28010/study_57326.shtml
http://www.138edu.com/28010/study_57328.shtml
http://www.138edu.com/28010/study_57329.shtml
http://www.138edu.com/28010/study_57331.shtml
http://www.138edu.com/28010/study_57332.shtml
http://www.138edu.com/28010/study_57333.shtml
http://www.138edu.com/28010/study_57334.shtml
http://www.138edu.com/28010/study_57335.shtml
http://www.138edu.com/28010/study_57336.shtml
http://www.138edu.com/28010/study_57337.shtml
http://www.138edu.com/28010/study_57338.shtml
http://www.138edu.com/28010/study_57339.shtml
http://www.138edu.com/28010/study_57340.shtml
http://www.138edu.com/28010/study_57341.shtml
http://www.138edu.com/28010/study_57342.shtml
http://www.138edu.com/28010/study_57343.shtml
http://www.138edu.com/28010/study_57344.shtml
http://www.138edu.com/28010/study_57345.shtml
http://www.138edu.com/28010/study_57346.shtml
http://www.138edu.com/28010/study_57347.shtml
http://www.138edu.com/28010/study_57348.shtml
http://www.138edu.com/28010/study_57349.shtml
http://www.138edu.com/28010/study_57350.shtml
http://www.138edu.com/28010/study_57351.shtml
http://www.138edu.com/28010/study_57352.shtml
http://www.138edu.com/28010/study_57354.shtml
http://www.138edu.com/28010/study_57355.shtml
http://www.138edu.com/28010/study_57359.shtml
http://www.138edu.com/28010/study_57360.shtml
http://www.138edu.com/28010/study_57361.shtml
http://www.138edu.com/28010/study_57362.shtml
http://www.138edu.com/28010/study_57363.shtml
http://www.138edu.com/28010/study_57359.shtml
http://www.138edu.com/28010/study_57360.shtml
http://www.138edu.com/28010/study_57361.shtml
http://www.138edu.com/28010/study_57362.shtml
http://www.138edu.com/28010/study_57363.shtml
http://www.138edu.com/28010/study_57364.shtml
http://www.138edu.com/28010/study_57365.shtml
http://www.138edu.com/28010/study_57366.shtml
http://www.138edu.com/28010/study_57367.shtml
http://www.138edu.com/28010/study_57368.shtml
http://www.138edu.com/28010/study_57369.shtml
http://www.138edu.com/28010/study_57370.shtml
http://www.138edu.com/28010/study_57371.shtml
http://www.138edu.com/28010/study_57372.shtml
http://www.138edu.com/28010/study_57373.shtml
http://www.138edu.com/28010/study_57375.shtml
http://www.138edu.com/28010/study_57376.shtml
http://www.138edu.com/28010/study_57378.shtml
http://www.138edu.com/28010/study_57380.shtml
http://www.138edu.com/28010/study_57381.shtml
http://www.138edu.com/28010/study_57383.shtml
http://www.138edu.com/28010/study_57384.shtml
http://www.138edu.com/28010/study_57386.shtml
http://www.138edu.com/28010/study_57387.shtml
http://www.138edu.com/28010/study_57389.shtml
http://www.138edu.com/28010/study_57390.shtml
http://www.138edu.com/28010/study_57392.shtml
http://www.138edu.com/28010/study_57394.shtml
http://www.138edu.com/28010/study_57395.shtml
http://www.138edu.com/28010/study_57397.shtml
http://www.138edu.com/28010/study_57398.shtml
http://www.138edu.com/28010/study_57400.shtml
http://www.138edu.com/28010/study_57401.shtml
http://www.138edu.com/28010/study_57403.shtml
http://www.138edu.com/28010/study_57404.shtml
http://www.138edu.com/28010/study_57406.shtml
http://www.138edu.com/28010/study_57407.shtml
http://www.138edu.com/28010/study_57409.shtml
http://www.138edu.com/28010/study_57410.shtml
http://www.138edu.com/28010/study_57412.shtml
http://www.138edu.com/28010/study_57413.shtml
http://www.138edu.com/28010/study_57415.shtml
http://www.138edu.com/28010/study_57416.shtml
http://www.138edu.com/28010/study_57418.shtml
http://www.138edu.com/28010/study_57420.shtml
http://www.138edu.com/28010/study_57421.shtml
http://www.138edu.com/28010/study_57423.shtml
http://www.138edu.com/28010/study_57424.shtml
http://www.138edu.com/28010/study_57425.shtml
http://www.138edu.com/28010/study_57427.shtml
http://www.138edu.com/28010/study_57428.shtml
http://www.138edu.com/28010/study_57430.shtml
http://www.138edu.com/28010/study_57431.shtml
http://www.138edu.com/28010/study_57433.shtml
http://www.138edu.com/28010/study_57434.shtml
http://www.138edu.com/28010/study_57435.shtml
http://www.138edu.com/28010/study_57437.shtml
http://www.138edu.com/28010/study_57438.shtml
http://www.138edu.com/28010/study_57440.shtml
http://www.138edu.com/28010/study_57441.shtml
http://www.138edu.com/28010/study_57443.shtml
http://www.138edu.com/28010/study_57444.shtml
http://www.138edu.com/28010/study_57446.shtml
http://www.138edu.com/28010/study_57447.shtml
http://www.138edu.com/28010/study_57449.shtml
http://www.138edu.com/28010/study_57450.shtml
http://www.138edu.com/28010/study_57451.shtml
http://www.138edu.com/28010/study_57453.shtml
http://www.138edu.com/28010/study_57454.shtml
http://www.138edu.com/28010/study_57456.shtml
http://www.138edu.com/28010/study_57457.shtml
http://www.138edu.com/28010/study_57459.shtml
http://www.138edu.com/28010/study_57460.shtml
http://www.138edu.com/28010/study_57462.shtml
http://www.138edu.com/28010/study_57463.shtml
http://www.138edu.com/28010/study_57465.shtml