Mysql中可以通過explain來查看一個sql的執行計劃,通常結果如下
這裡我們來詳細介紹下執行計劃中type的含義,它是表示mysql如何去獲取數據的,類型有多種,按照性能來看結果如下
null->system->const->eq_ref->ref->fulltext->ref_or_null->index_merge ->range->index->ALL
一般我們看到type在range之下就表示或許有優化的餘地,但優化也要按照實際情況去做,并不是說看到all了就說明sql一定有問題,比如我們去查詢一個數據量小的堆表那type肯定就是all了,但數據隻有10條,所有要進行sql優化首先要對各個參數都非常了解之後,還要根據實際情況來優化。
All全表掃描,這個比較簡單明了,就是整個表數據都用到了,所有就是scan,一般如果有某個表隻是需要一部分少量數據,但是該表卻用了scan,并且影響到性能了,那就有優化的必要了,但如果該表很小 或者并沒導緻性能下降,那麼scan也問題不大。總之針對執行慢的sql ,如果發現有all出現,就要引起我們的注意
explain
SELECT * FROM sakila.city
全索引表掃描,這裡注意是全索引表,也就是整個索引都用到了,隻不過不是scan表數據,而是遍曆整個索引表,我們在來看下city表中city_id是主鍵,如果我們需要通過該主鍵查找數據是不用遍曆整顆索引樹的,但我們如果要排序呢? 看下面sql
SELECT *
FROM sakila.city
order by city_id;
很顯然,這裡用到了index,但這個sql是需要優化的嗎? 答案是不需要,而且這個sql還是最好的,不信我們換個排序字段試試
explain
SELECT * FROM sakila.city
order by city
對同樣的數據進行排序,使用不同的字段排序所消耗的資源是不同的,用主鍵排序 雖然是index 但其實它節省了sort的開支,而使用非主鍵字段排序則需要額外的sort損耗,看第二個圖中 Extra字段 顯示了 Using filesort,這個操作是非常耗資源的,也是我們日常sql優化的重點
Range對索引進行範圍查找,這裡重點是查詢使用到索引了,并且數據肯定在總數據中占比較小,能從索引中使用一個較小範圍獲得數據,哪怕數據是分散的。
City表數據一共有600條記錄,city_id 從1自增到600,我們先寫個sql來展示range使用
explain
SELECT * FROM sakila.city
where country_id <10
;
我們看到type的确是range,我們把10 改成50看下效果
explain
SELECT * FROM sakila.city
where country_id <50
;
就是将數字變化一下,但執行計劃似乎全變了,首先type變成了all,也就是全表掃描了,或許查詢引擎認為都50了總條數才600,那就直接掃描表吧不用再費勁走索引了,注意兩個圖的filtered字段和extra字段, filtered是我們需要的數據和實際返回的數據的比例,這裡應該我們需要50條但實際返回了600條,比例45.83 并不正确,注意這裡也是給你一個評估值,我們可以參考,當filtered不是100%時候,就提醒我們要注意了,extra 的值也是從using index condition變了 using where,前者對索引進行篩選,後者是使用書簽查找,具體細節大家可以從網上查找,注意這些都是給我們的提示,并不一定說using where 一定慢,而using index condition就一定快,還是要實際問題實際分析。
INDEX_MERGE合并索引,使用多個索引搜索條件的的結果進行交集運行,比如city表中country_id是一個索引,city也是一個索引那麼下面的語句就會将兩個索引的結果先交集運算,再用這個交集去取數據
explain
SELECT * FROM sakila.city
where country_id =10
or city like 'a%'
;
和ref類似,隻不過多了一個null的判斷,ref即先從非聚集索引中獲取所需數據範圍,但是又有字段并不再索引中,需要跳轉到聚集索引獲取其他字段數據,也就是我們說的書簽跳轉,我們來看你下面的sql
explain
SELECT * FROM sakila.city
where country_id =1
or country_id is null;
注意這裡 country_id 是一個非唯一的非聚集索引,并且country_id是可以為空的,所以才能用到ref_or_null,如果country_id不為空,則使用的是 ref,哪怕我們sql中有or country_id is null; 也是如此
Fulltext查詢過程中用到了fulltext索引,一般我們是用不到這個的,當我們建立了全文索引并使用它時就會使用該類型
現在我們在city表的 city字段上添加一個全文索引并進行查詢
SELECT * FROM sakila.city
where match(city) against('Addis')
;
非聚集索引查詢需要一次頁簽跳轉,性能也不錯
explain
SELECT * FROM sakila.city
where country_id=2;
兩個表關聯,關聯字段為一個表的主鍵或唯一索引,另一個表沒有索引,這個時候就會在主鍵字段的表上使用eq_ref,比如我們有兩個表 一個城市city表 一個國家country表,進行關聯後,因為city表的country_id字段并沒有創建相應的索引,所以執行計劃就變成了先遍曆city表然後用city表的country_id去country表進行關聯
explain
SELECT *
FROM sakila.city as c
inner join sakila.country as co on co.country_id=c.country_id
從執行計劃的順序來看,這種執行計劃的效率并不很好,如果給city表的country_id添加一個索引後,效率會提高很多,而且這種情況也不複合先小表後大表的執行建議
Const使用主鍵或唯一索引的seek操作,返回一條數據,效率是很好的
explain
SELECT *
FROM sakila.city
where city_id=10
更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!