mysql總結之explain

mysql總結之explain

explain主要用於sql語句中的select查詢,可以顯示的檢視該sql語句索引的命中情況,從而更好的利用索引、優化查詢效率。

    Explain語法如下:explain [extended] select …

其中extended是選用的,如果使用的extended,那麼explain之後就可以使用show warnings檢視相應的優化資訊,也就是mysql內部實際執行的query。

列名

描述

說明

相關連結

id

若沒有子查詢和聯合查詢,id則都是1。

Mysql會按照id從大到小的順序執行query,在id相同的情況下,則從上到下執行。

 

select_type

select型別。

 

常見型別

table

輸出的行所引用的表。

有時看到的是<derivedN>,其中N對應的是id列的值。

 

type

Mysql的存取方法,連線訪問型別。

 

常見型別

possible_keys

在查詢過程中可能用到的索引。

在優化初期建立該列,但在以後的優化過程中會根據實際情況進行選擇,所以在該列列出的索引在後續過程中可能沒用。該列為NULL意味著沒有相關索引,可以根據實際情況看是否需要加索引。

 

key

訪問過程中實際用到的索引。

有可能不會出現在possible_keys中(這時可能用的是覆蓋索引,即使query中沒有where)。possible_keys揭示哪個索引更有效,key是優化器決定哪個索引可能最小化查詢成本,查詢成本基於系統開銷等總和因素,有可能是“執行時間”矛盾。如果強制mysql使用或者忽略possible_keys中的索引,需要在query中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

 

key_len

顯示使用索引的位元組數。

由根據表結構計算得出,而不是實際資料的位元組數。如ColumnA(char(3)) ColumnB(int(11)),在utf-8的字符集下,key_len=3*3 4=13。計算該值時需要考慮字元列對應的字符集,不同字符集對應不同的位元組數。

mysql5.1.5下latin1、utf8、gbk字元數、位元組數、漢字的對應關係

ref

顯示了哪些欄位或者常量被用來和 key 配合從表中查詢記錄出來。顯示那些在index查詢中被當作值使用的在其他表裡的欄位或者constants。

  

rows

估計為返回結果集而需要掃描的行。

不是最終結果集的函式,把所有的rows乘起來可估算出整個query需要檢查的行數。有limit時會不準確。(為毛?)

 

Extra

mysql查詢的附加資訊。

 

常見資訊

select型別:

simple:query中不包含子查詢或聯合查詢。

primary:包含子查詢或聯合查詢的query中,最外層的select查詢。

subquery:子查詢在select的目標裡,不在from中,子查詢的第一個select。

例如:EXPLAIN SELECT (SELECT actor_id FROM actor) FROM film_actor

dependent subquery:子查詢內層的第一個select,依賴於外部查詢的結果集。

例如:EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor)

EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor WHERE actor_id=1)

EXPLAIN SELECT * FROM actor WHERE actor_id = (SELECT actor_id FROM film_actor WHERE actor_id=1)

uncacheable subquery:表示子查詢,但返回結果不能被cache,必須依據外層查詢重新計算。(在什麼情況下會出現這個?)

derived:子查詢在from子句中,執行查詢的時候會把子查詢的結果集放到臨時表(衍生表)。

例如:EXPLAIN SELECT * FROM (SELECT * FROM actor) AS a。此時table列會顯示<derivedN>,其中N對應id列的值。

union:在聯合查詢中第二個及其以後的select對應的型別。

例如:EXPLAIN SELECT * FROM film_actor UNION ALL SELECT * FROM actor

如果union包含在一個from子查詢裡面,則from子查詢中的第一個select標記為derived。

例如:EXPLAIN SELECT * FROM ( SELECT * FROM film_actor UNION ALL SELECT * FROM actor) a

union result:從union臨時表獲取結果集合。例如上面兩個查詢結果集中的最後一行。<union1,2,…>其中1,2,…所標識的id列表代表id列,當id列表長度超過20個之後就會省略後面的<union1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,…>。例子如上圖。

dependent union:子查詢中的union,且為union中第二個select開始的後面所有select,同樣依賴於外部查詢的結果集。

例如:EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor UNION ALL SELECT actor_id FROM film_actor)

uncacheable union:表示union第二個或以後的select,但結果不能被cache,必須依據外層查詢重新計算。(在什麼情況下會出現)

按照效率從高到低出幾種常見的type型別:

NULL:mysql在優化過程中分解query,執行時甚至不用再訪問表資料或者索引,比如id=-1。

例如:EXPLAIN SELECT * FROM actor WHERE actor_id = -1

system:查詢的表僅有一行。這是const聯接型別的一個特例。(在沒有任何索引的情況下,只有一條資料,MyISAM會顯示system,InnoDB會顯示ALL)

const:最多會有一條記錄匹配。因為僅有一行,在這行的列值可被優化器剩餘部分認為是常數。const表很快,因為它們只讀取一次。發生在有一個unique key或者主鍵,並且where子句給它設定了一個比較值。

例如:EXPLAIN SELECT * FROM actor WHERE actor_id = 1(其中actor_id是主鍵)

eq_ref:使用這種索引查詢,最多返回一條符合條件的記錄。會在使用主鍵或者唯一性索引訪問資料時看到,除了const型別這可能是最好的聯接型別。

例如:EXPLAIN SELECT * FROM actor, actorsex WHERE actor.actor_id = actorsex.actor_id(其中actor_id是actor、actorsex的主鍵,且actorsex中只有一條記錄,如果多於一條記錄就不是eq_ref)

ref:這是一種索引訪問。只有當使用一個非唯一性索引或者唯一性索引的非唯一性字首(換句話說,就是無法根據該值只取得一條記錄)時才會發生,將索引和某個值相比較,這個值可能是一個常數,也可能是來自前一個表裡的多表查詢的結果值。如果使用的鍵僅僅匹配少量行,該聯接型別是不錯的。

例如:EXPLAIN SELECT * FROM film_actor,actor    WHERE film_actor.actor_id=actor.actor_id AND film_actor.actor_id=1

ref_or_null:類似ref。不同的是Mysql會在檢索的時候額外的搜尋包含 NULL 值的記錄,他意味著mysql必須進行二次查詢,在初次查詢的結果中找出NULL條目。

index_merge:查詢中使用兩個或多個索引,然後對索引結果進行合併。在這種情況下,key列包含所有使用的索引,key_len包含這些索引的最長的關鍵元素。

select * from test where column1 = 1 or column2 = 2(沒試出來!555555)

unique_subquery:用來優化有子查詢的in,並且該子查詢是通過一個unique key選擇的。子查詢返回的欄位組合是主鍵或者唯一索引。

例如:EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM actor)

index_subquery:該聯接型別類似於unique_subquery,子查詢中的返回結果欄位組合是一個索引或索引組合,但不是一個主鍵或者唯一索引。

例如:EXPLAIN SELECT * FROM film_actor WHERE film_id IN (SELECT film_id FROM film_actor)

range:在一定範圍內掃描索引。如where中帶有between或者>,此時ref列為NULL。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比較關鍵字列時,可以使用range。

index:按索引次序掃描資料。因為按照索引掃描所以會避免排序,但也會掃描整表資料,若隨機讀取開銷會更大。如果extra列顯示using index,說明使用的是覆蓋索引(覆蓋索引:包含所有滿足查詢需要的資料列的索引)。對於InnoDB表特別有用,此時只訪問索引資料即可,不用再根據主鍵資訊獲取原資料行,避免了二次查詢,而MyISAM表優化效果相對InnoDB來說沒有那麼的明顯。

all:按行掃描全表資料,除非查詢中有limit或者extra列顯示使用了distinct或notexists等限定詞。

Extra資訊 :

distinct:當mysql找到第一條匹配的結果值時,就停止該值的查詢,然後繼續該列其他值的查詢。

not exists:在左連線中,優化器可以通過改變原有的查詢組合而使用的優化方法。當發現一個匹配的行之後,不再為前面的行繼續檢索,可以部分減少資料訪問的次數。例如,表t1、t2,其中t2.id為not null,對於SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;由於 t2.id非空,所以只可能是t1中有,而t2中沒有,所以其結果相當於求差。left join原本是要兩邊join,現在Mysql優化只需要依照 t1.id在t2中找到一次t2.id即可跳出。

const row not found:涉及到的表為空表,裡面沒有資料。

Full scan on NULL key:是優化器對子查詢的一種優化方式,無法通過索引訪問NULL值的時候會做此優化。

Impossible Having:Having子句總是false而不能選擇任何列。例如having 1=0

Impossible WHERE:Where子句總是false而不能選擇任何列。例如where 1=0

Impossible WHERE noticed after reading const tables:mysql通過讀取“const/system tables”,發現Where子句為false。也就是說:在where子句中false條件對應的表應該是const/system tables。這個並不是mysql通過統計資訊做出的,而是真的去實際訪問一遍資料後才得出的結論。當對某個表指定了主鍵或者非空唯一索引上的等值條件,一個query最多隻可能命中一個結果,mysql在explain之前會優先根據這一條件查詢對應記錄,並用記錄的實際值替換query中所有用到來自該表屬性的地方。

例如:select * from a,b where a.id = 1 and b.name = a.name

執行過程如下:先根據a.id = 1找到一條記錄(1, ‘name1’),然後將b.name換成’name1’,然後通過a.name = ‘name1’查詢,發現沒有命中記錄,最終返回“Impossible WHERE noticed after reading const tables”。

No matching min/max row:沒有行滿足如下的查詢條件。

例如:EXPLAIN SELECT MIN(actor_id) FROM actor WHERE actor_id > 3(只有兩條記錄)

actor_id為唯一性索引時,會顯示“No matching min/max row”,否則會顯示“using where”。

no matching row in const table:對一個有join的查詢,包含一個空表或者沒有資料滿足一個唯一索引條件。

No tables used:查詢沒有From子句,或者有一個From Dual(dual:虛擬表,是為了滿足select…from…習慣)子句。

例如:EXPLAIN SELECT VERSION()

Range checked for each record (index map: N):Mysql發現沒有好的index,但發現如果進一步獲取下一張join表的列的值後,某些index可以通過range等使用。Mysql沒找到合適的可用的索引。取代的辦法是,對於前一個表的每一個行連線,它會做一個檢驗以決定該使用哪個索引(如果有的話),並且使用這個索引來從表裡取得記錄。這個過程不會很快,但總比沒有任何索引時做表連線來得快。

Select tables optimized away:當我們使用某些聚合函式來訪問存在索引的某個欄位時,優化器會通過索引直接一次定位到所需要的資料行完成整個查詢。在使用某些聚合函式如min, max的query,直接訪問儲存結構(B樹或者B 樹)的最左側葉子節點或者最右側葉子節點即可,這些可以通過index解決。Select count(*) from table(不包含where等子句),MyISAM儲存了記錄的總數,可以直接返回結果,而Innodb需要全表掃描。Query中不能有group by操作。

unique row not found:對於SELECT … FROM tbl_name,沒有行滿足unique index或者primary key。從表中查詢id不存在的一個值會顯示Impossible WHERE noticed after reading const tables。

Using filesort:指Mysql將用外部排序而不是按照index順序排列結果。資料較少時從記憶體排序,否則從磁碟排序。Explain不會顯示的告訴客戶端用哪種排序。

Using index:表示Mysql使用覆蓋索引避免全表掃描,不需要再到表中進行二次查詢資料。注意不要和type中的index型別混淆。

Using index for group-by:類似Using index,所需資料只需要讀取索引,當query中有group by或distinct子句時,如果分組欄位也在索引中,extra就會顯示該值。

Using temporary:Mysql將建立一個臨時表來容納中間結果。在group by和order by的時,如果有必要的話。例如group by一個非鍵列,優化器會建立一個臨時表,有個按照group by條件構建的unique key,然後對於每條查詢結果(忽略group by),嘗試insert到臨時表中,如果由於unique key導致insert失敗,則已有的記錄就相應的updated。例如,name上沒有索引,SELECT name,COUNT(*) FROM product GROUP BY name,為了排序,Mysql就需要建立臨時表。此時一般還會顯示using filesort。

Using where:表示Mysql將對storage engine提取的結果進行過濾。例如,price沒有index,SELECT * FROM product WHERE price=1300.00。有許多where的條件由於包含了index中的列,在查詢的時候就可以過濾,所以不是所有帶where子句的查詢會顯示Using where。

Using join buffer:5.1.18版本以後才有的值。join的返回列可以從buffer中獲取,與當前表join。

例如:explain select * from t1,t2 where t1.col < 10 and t2.col < 10

Scanned N databases:指在處理information_schema查詢時,有多少目錄需要掃描。

例如:EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES

網上說這個查詢會顯示Scanned all databases,我試了下extra列是空。

Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table:指示從information_schema查詢資訊時有關檔案開啟的優化。 Skip_open_table:表資訊已經獲得,不需要開啟。 Open_frm_only:只開啟.frm檔案。 Open_trigger_only:只開啟.trg檔案。 Open_full_table:沒有優化。.frm,.myd和.myi檔案都開啟。

Using sort_union(…), Using union(…), Using intersect(…):都出現在index_merge讀取型別中。 Using sort_union:用兩個或者兩個以上的key提取資料,但優化器無法確保每個key會提取到一個自然排好序的結果,所以為了排除多餘的資料,需要額外的處理。例如,customer的state,(lname,fname)是key,但lname不是key,SELECT COUNT(*) FROM customer WHERE (lname = ‘Jones’) OR (state = ‘UT’),由於lname上面沒有key,所以使用(lname,fname),使得結果可能不按照順序,優化器需要額外的一些工作。 Using union:用兩個或者兩個以上的key提取資料,分別取得結果是已排序,通過合併就可以獲得正確結果。例如,customer中的state和(lname,fname)是key,SELECT COUNT(state) FROM customer WHERE (lname = ‘Jones’ AND fname=’John’) OR (state = ‘UT’)。 Using intersect:用兩個或者兩個以上的key提取資料,分別取得結果是已排序,通過求交就可以獲得正確結果。例如,customer中的state和(lname,fname)是key,SELECT COUNT(state) FROM customer WHERE (lname = ‘Jones’ AND fname=’John’) AND (state = ‘UT’)。

Using where with pushed condition:僅用在ndb上。Mysql Cluster用Condition Pushdown優化改善非索引欄位和常量之間的直接比較。condition被pushed down到cluster的資料節點,並在所有資料節點同時估算,把不合條件的列剔除避免網路傳輸。

mysql5.1.5下latin1、utf8、gbk字元數、位元組數、漢字的對應關係:

latin1:

1character=1byte, 1漢字=2character 一個欄位定義成varchar(200),可以儲存100個漢字或者200個字元,佔用200個位元組。尤其是當欄位內容是字母和漢字組成時,儘量假設欄位內容都是由漢字組成,據此來設定欄位長度。

utf8:

1character=3bytes, 1漢字=1character一個欄位定義成 varchar(200),則它可以儲存200個漢字或者200個字母,佔用600個位元組。

gbk:

1character=2bytes,1漢字=1character一個欄位定義成 varchar(200),則它可以儲存200個漢字或者200個字母,佔用400個位元組。

word版打包下載

您可能感興趣的文章:

MySQL資料庫引擎介紹、區別、建立和效能測試的深入分析mysql更改引擎(InnoDB,MyISAM)的方法MySQL儲存引擎總結MySQL查詢優化之explain的深入解析mysql中explain用法詳解MySQL效能分析及explain的使用說明MySQL資料庫設定遠端訪問許可權方法小結mysql 新增、刪除使用者和許可權分配詳細解讀MySQL中的許可權mysql學習之引擎、Explain和許可權的深入講解