Mysql效能最佳化神神器explain。一文通透
前言
SQL語句在不同的人手中會寫出不同的語句形式，比如經常遇到的SQL慢查詢，這時候往往需要針對SQL進行最佳化。 而Mysql中為保證SQL語句能夠高效的執行，提供了一個Explain的命令，用來對SQL語句進行語義分析，供開發者來針對SQL進行最佳化。
資料準備
為了方便整個流程的執行，首先建立好測試資料。
建立資料表
SQL中的執行涉及到單表與多表的聯合執行，本次建立兩張表用來模擬該情況，更多的多表聯合執行與兩張表執行計劃是一樣的。
CREATE TABLE `users` （ `id` int（11） NOT NULL AUTO_INCREMENT， `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP， `name` varchar（20） NOT NULL DEFAULT ‘’ COMMENT ‘使用者名稱’， `sex` tinyint（4） NOT NULL DEFAULT ‘1’ COMMENT ‘性別’， `phone` varchar（11） NOT NULL COMMENT ‘手機號’， `desc` varchar（200） NOT NULL DEFAULT ‘’ COMMENT ‘介紹’， primary key （`id`）） ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT ‘使用者表’；CREATE TABLE `order`（ `id` int（11） NOT NULL AUTO_INCREMENT， `phone` varchar（11） NOT NULL COMMENT ‘手機號’， `name` varchar（20） NOT NULL COMMENT ‘使用者名稱’， primary key （`id`）） ENGINE =InnoDB default CHARSET=utf8 COMMENT ‘訂單’；
插入資料
為了方便本次沒有使用SQL語句，而是使用儲存過程建立資料，簡單快速也方便。
# 建立 儲存過程create procedure insert_user_data（）begindeclare i int ；declare name varchar（20）；declare phone_num varchar（11）；set @SURNAME = ‘王李張劉陳楊黃趙吳周徐孫馬朱胡郭何高林羅鄭梁謝宋唐位許韓馮鄧曹彭曾蕭田董潘袁於蔣蔡餘杜葉程蘇魏呂丁任沈姚盧姜崔鍾譚陸汪範金石廖賈夏韋傅方白鄒孟熊秦邱江尹薛閻段雷侯龍史陶黎賀顧毛郝龔邵萬錢嚴覃武戴莫孔向湯’；set @NAME = ‘丹舉義之樂書乾雲亦從代以偉佑俊修健傲兒元光蘭冬冰冷凌凝凡凱初力勤千卉半華南博又友同向君聽和哲嘉國堅城夏夜天奇奧如妙子存季孤宇安宛宸寒尋爾堯山嵐峻巧平幼康建開弘強彤彥彬彭心憶志念懷憐恨惜慕成擎敏文新旋旭昊明易昕映春昱晉曉晗晟景晴智曼朋朗傑松楓柏柔柳格桃夢楷槐正水沛波澤潔洋濟浦浩海濤潤涵淵源溥濮瀚靈燦炎煙燁然煊煜熙熠玉珊珍理琪琴瑜瑞瑤瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫紹經綠群翠翰致航良芙芷蒼苑若茂榮蓮菡菱萱蓉藍蕊蕾薇蝶覓訪誠語谷豪賦超越軒輝達遠邃醉金鑫錦問雁雅雨雪霖霜露青靖靜風飛香馳騫高鴻鵬鶴黎’；set i =1；while i < 100000 do SET phone_num = concat（‘1’， substring（cast（3 + （rand（） * 10） % 7 AS char（50））， 1， 1）， right（left（trim（cast（rand（） AS char（50）））， 11）， 9））； set name = concat（substr（@surname，floor（rand（）*length（@surname）/3+1），1）， substr（@NAME，floor（rand（）*length（@NAME）/3+1），1）， substr（@NAME，floor（rand（）*length（@NAME）/3+1），1））； insert into users（create_time，name， sex，phone ，`desc`） values（now（），name，rand（）*1，phone_num ，‘test’）； insert into `order`（phone，name） values（phone_num，name）； set i=i+1；end while；end# 執行儲存過程call insert_user_data（）；# 刪除儲存過程drop procedure if exists insert_user_data ；
建立儲存過程後，有需要修改就直接使用刪除儲存過程，再重新建立即可。
explain命令使用
explain的執行命令explain select * from users where id =1 \G；展示如下：
*************************** 1。 row *************************** id： 1 select_type： SIMPLE table： users partitions： NULL type： constpossible_keys： PRIMARY key： PRIMARY key_len： 4 ref： const rows： 1 filtered： 100。00 Extra： NULL1 row in set， 1 warning （0。00 sec）
一共12個欄位，各個欄位的含義如下：
id： 每一個查詢語句都會生成識別符號，執行順序是id從大到小執行
select_type： 查詢的型別，裡面包含多種型別跳轉到select_type
table： 查詢的表名，包含關聯的表資訊
partitions：匹配的分割槽
type： 表示Mysql在表中找到的所需行的方式，這裡是表示使用索引的方式。type
possible_keys： 查詢語句可能用到的索引
key： 查詢語句真正使用的索引
key_len： 表示索引中使用的位元組數，注意顯示的是索引欄位中最大可能長度，而不是實際使用長度
ref： 上述表的連線方式，哪些列或常量被用於查詢索引列上的值
rows： 估算查詢到所需記錄的需要讀取的行數
extra： 該列查詢中包含的其他額外詳細資訊。
有些欄位有更多的型別，以下是詳細講解。
select_type
用來表示每個查詢型別，常用型別如下：
SIMPLE： 最簡單的查詢方式，單表查詢，不包含UNION以及子查詢，例如select * from users where id =1
PRIMAPY： 表示次查詢是是最外層的查詢。有子查詢的時候展示。explain select * from users where phone=（select phone from order where id = 10）；
UNION： 表示次查詢是UNION的第二或隨後查詢方式，查詢語句中存在union關鍵字explain select * from users where id = 10 union select * from users where id = 20；
DEPENDENT UNION：UNION中的第二個或後面的查詢語句，取決於外面的查詢
UNION RESULT，UNION的結果。
SUBQUERY： 子查詢中的第一個SELECT。
DEPENDENT SUBQUERY： 子查詢中的第一個第一個SELECT，取決於外面的查詢，當子查詢依賴外部的查詢結果時會有該內容展示explain select * from users where phone=（select phone from order where id = users。id ） and id =10；。
在這裡面最常見的型別就屬於SIMPLE型別，我們經常使用的多表查詢也是SIMPLE型別。例如explain select * from users left join order o on users。phone = o。phone where users。id =10
type
type欄位幫助我們來定位查詢是否高效，是全表掃描還是索引掃描。 不同的type，代表的效能不一樣，順序如下： ALL < index < range ~ index_merge < ref < eq_ref < const < system
常用型別如下：
ALL： 全表掃描，當資料庫中的資料巨大時，一個查詢還是使用全表掃描的方式，這個查詢對資料庫的壓力影響是巨大的，解決方式是透過新增索引來避免。explain select * from users；，可以看到全表掃描掃了200多萬行的資料。
Index：
「」
，只掃描所有的索引，而不掃描資料，相對全表掃描來說已經降低部分資料量。同時在Extra欄位顯示Index。explain select id from users；查詢語句中id是主鍵索引，則只查詢的是索引資料。
range：建立在索引的基礎上進行資料過濾查詢，這些能使用索引的識別符號有=，<，>，<=，>=， BETWEEN，IN運算子中。explain select phone from users where id > 10 and id < 20 ； SQL語句中使用>和<來限定where條件使用的還是range，
「」
ref： 查詢中使用
「非唯一索引」
查詢，同時在ref列顯示使用
「哪個列或者常數」
。雖然使用了索引，但該索引列的值是可以存在多個的，如phone列出現相同的手機號。explain select * from order where phone =‘16485461071’
ref_eq：用法類似，但比ref好點的是，該型別是知道結果集只有一條。直接知道結果集是一條記錄的索引是主鍵索引與唯一索引，使用該型別是在多表查詢時，
「條件中包含主鍵或唯一索引的條件」
。explain select * from users， order where users。id=order。id
const： 主鍵值作為where的條件查詢，Mysql最佳化器會將這次查詢轉為一個常量看待explain select * from order where id =10；
system： const型別的一個特例，當表中只有一行資料時，會使用system型別
rows
查詢中所需要掃描的行數，我們使用各種索引，最佳化都是為了減少掃描的行數。
ref
表示在查詢時，表的連線匹配條件，可以是常量，也可以是查詢的列explain select * from users， order where users。id=order。id；
extra
extra 表示更多的sql查詢資訊，extra是Mysql查詢計劃中查詢資訊重要補充。extra的型別如下：
Distinct： 在查詢到第一行後，不再進行匹配查詢更多的資料，對應到查詢中的distinct去重查詢。
Using filesort： 代表MYSQL使用的是記憶體排序或者檔案排序，並且該排序沒有使用到索引。可以使用合適的索引來修改order by ，group by語句中的條件。
Using temporary： 使用臨時表儲存中間結果，常用與Group by ，Order by語句查詢中。同樣的儘量避免使用臨時表來儲存中間結果。
Not exists： 在某些 LEFT JOIN 連線中，MYSQL使用最佳化器進行最佳化，改變原有的QUERY的組成最佳化部分，減少資料訪問的次數。
Using index： 查詢時不需要回表，直接透過索引就可以獲得查詢的資料。
Using union： 使用or連線各個索引條件時，表明資訊表示從處理結果中獲取並集。
Using intersect： 使用and連線各個索引條件時，表明資訊表示從處理結果中獲取並集。
Using sort_union/Using sort_intersection：出現在and/or語句中，先查詢主鍵資訊，再將結果進行排序合併的資料讀取中。
Using where： 使用Where 字句來限制資料的返回，注意：使用Using Where表示是Mysql伺服器將儲存引擎返回服務層後再進行條件過濾。
Using join buffer： 使用了連線快取，一共兩種：塊巢狀迴圈連線Block Nested Loop，以及Index Nested-Loop Join使用索引查詢。
總結
明白SQL的查詢計劃，當再寫SQL時，多多使用explain語句來看下SQL的查詢計劃是怎樣的，心中對SQL的執行有大概的瞭解，方能得心運手。
本文中用到的SQL語句上傳到github中需要的自取
