使用背景
當sql運作比較耗時的時候,可以進行sql優化,比如加索引,調整sql的結構等等。我們看sql運作的狀态等資訊時,可以通過執行計劃來參考。
explain + sql 語句檢視 執行計劃。
例如:EXPLAIN SELECT * FROM `g_play_log` WHERE `user_id` = '6178962'
上圖為sql的執行計劃查詢結果,通過結果可以看到走的是全表查詢(type=ALL),需要涉及到的資料行數643420,這樣查詢的效率不高,可以對表做一個簡單的優化之後,再看看執行計劃。因為通過where條件,條件為user_id,可以對user_id建一個索引,建好索引後,再看執行計劃的結果如下。
通過兩次的結果對比可以看到,加上索引之後,查詢會走索引(type=ref,key=user_id_index),需要涉及到的資料行數61,這樣查詢的效率會大大的提高。
執行計劃字段說明
select_type:
SIMPLE(簡單查詢),
PRIMARY(最外層查詢),
SUBQUERY(映射為子查詢),
DERIVED(子查詢),
UNION(聯合),
UNION RESULT(使用聯合的結果)
table : 正在通路的表名
type:
ALL(全資料表掃描),
index(全索引表掃描),
RANGE(對索引列進行範圍查找),
INDEX_MERGE(合并索引,使用多個單列索引搜尋),
REF(根據索引查找一個或多個值),
EQ_REF(搜尋時使用primary key 或 unique類型),
CONST(常量,表最多有一個比對行,因為僅有一行,在這行的列值可被優化器剩餘部分認為是常數,const表很快,因為它們隻讀取一次),
SYSTEM(系統,表僅有一行(=系統表)。這是const聯接類型的一個特例)
說明:性能:all
possible_keys:可能使用的索引
key:真實使用的索引
key_len:MySQL中使用索引位元組長度
rows:mysql 預估為了找到所需的行而要讀取的行數
extra:
Using index(此值表示mysql将使用覆寫索引,以避免通路表),
Using where(mysql 将在存儲引擎檢索行後再進行過濾,許多where條件裡涉及索引中的列,當它讀取索引時,就能被存儲引擎檢驗,是以不是所有帶where子句的查詢都會顯示“Using where”。有時“Using where”的出現就是一個暗示:查詢可受益于不同的索引),
Using temporary(mysql 對查詢結果排序時會使用臨時表),
Using filesort(mysql會對結果使用一個外部索引排序,而不是按索引次序從表裡讀取行。mysql有兩種檔案排序算法,這兩種排序方式都可以在記憶體或者磁盤上完成,explain不會告訴你mysql将使用哪一種檔案排序,也不會告訴你排序會在記憶體裡還是磁盤上完成),
Range checked for each record(index map: N)(沒有好用的索引,新的索引将在聯接的每一行上重新估算,N是顯示在possible_keys列中索引的位圖,并且是備援的)
使用索引的情況
1.使用 like 語句時,%在右邊才會使用索引。
2.or 條件中有未建立索引的列索引失效。
3.條件的類型不一緻索引失效。
4.使用!=時部分情況索引失效。(如果是主鍵,則會走索引)
5.使用>時部分情況索引失效。(如果是主鍵或索引是整數類型,則會走索引)
6.order by部分情況索引失效。(如果 order by 的列是主鍵或索引是整數類型,則會走索引)
7.組合索引(遵循最左字首)
# 若 name 和 email 組成組合索引
create index ix_name_phone on user(name, phone);
# 使用結果
name and phone -- 使用索引
phone and name -- 不使用索引
name -- 使用索引
phone -- 不使用索引