mysql之explain

標籤：

話說工欲善其事，必先利其器。今天就簡單介紹下EXPLAIN。

內容導航

• id
• select_type
• table
• type
• possible_keys
• key
• key_len
• ref
• rows
• Extra

 

環境準備MySQL版本：

建立測試表

CREATE TABLE people(    id bigint auto_increment primary key,    zipcode char(32) not null default ‘‘,    address varchar(128) not null default ‘‘,    lastname char(64) not null default ‘‘,    firstname char(64) not null default ‘‘,    birthdate char(10) not null default ‘‘);CREATE TABLE people_car(    people_id bigint,    plate_number varchar(16) not null default ‘‘,    engine_number varchar(16) not null default ‘‘,    lasttime timestamp);

插入測試資料

insert into people(zipcode,address,lastname,firstname,birthdate)values(‘230031‘,‘anhui‘,‘zhan‘,‘jindong‘,‘1989-09-15‘),(‘100000‘,‘beijing‘,‘zhang‘,‘san‘,‘1987-03-11‘),(‘200000‘,‘shanghai‘,‘wang‘,‘wu‘,‘1988-08-25‘)insert into people_car(people_id,plate_number,engine_number,lasttime)values(1,‘A121311‘,‘12121313‘,‘2013-11-23 :21:12:21‘),(2,‘B121311‘,‘1S121313‘,‘2011-11-23 :21:12:21‘),(3,‘C121311‘,‘1211SAS1‘,‘2012-11-23 :21:12:21‘)

建立索引用來測試

alter table people add key(zipcode,firstname,lastname);

 

EXPLAIN 介紹

 先從一個最簡單的查詢開始：

Query-1explain select zipcode,firstname,lastname from people;

EXPLAIN輸出結果共有id,select_type,table,type,possible_keys,key,key_len,ref,rows和Extra幾列。

id

Query-2explain select zipcode from (select * from people a) b;

id是用來順序標識整個查詢中SELELCT 語句的，通過上面這個簡單的巢狀查詢可以看到id越大的語句越先執行。該值可能為NULL，如果這一行用來說明的是其他行的聯合結果，比如UNION語句：

Query-3explain select * from people where zipcode = 100000 union select * from people where zipcode = 200000;

 

select_type

SELECT語句的類型，可以有下面幾種。

SIMPLE

最簡單的SELECT查詢，沒有使用UNION或子查詢。見Query-1。

 

PRIMARY

在嵌套的查詢中是最外層的SELECT語句，在UNION查詢中是最前面的SELECT語句。見Query-2和Query-3。

 

UNION

UNION中第二個以及後面的SELECT語句。 見Query-3。

 

DERIVED

派生表SELECT語句中FROM子句中的SELECT語句。見Query-2。

 

UNION RESULT

一個UNION查詢的結果。見Query-3。

 

DEPENDENT UNION

顧名思義，首先需要滿足UNION的條件，及UNION中第二個以及後面的SELECT語句，同時該語句依賴外部的查詢。

Query-4explain select * from people where id in  (select id from people where zipcode = 100000 union select id from people where zipcode = 200000 );

Query-4中select id from people where zipcode = 200000的select_type為DEPENDENT UNION。你也許很奇怪這條語句並沒有依賴外部的查詢啊。

這裡順帶說下MySQL最佳化器對IN操作符的最佳化，最佳化器會將IN中的uncorrelated subquery最佳化成一個correlated subquery（關於correlated subquery參見這裡）。

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

類似這樣的語句會被重寫成這樣：

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

所以Query-4實際上被重寫成這樣：

Query-5explain select * from people o where exists  (select id from people where zipcode = 100000 and id = o.id union select id from people where zipcode = 200000  and id = o.id);

題外話：有時候MySQL最佳化器這種太過“聰明” 的做法會導致WHERE條件包含IN()的子查詢語句效能有很大損失。可以參看《高效能MySQL第三版》6.5.1關聯子查詢一節。

 

SUBQUERY

子查詢中第一個SELECT語句。

Query-6explain select * from people  where id =  (select id from people where zipcode = 100000);

 

DEPENDENT SUBQUERY

和DEPENDENT UNION相對UNION一樣。見Query-5。

除了上述幾種常見的select_type之外還有一些其他的這裡就不一一介紹了，不同MySQL版本也不盡相同。

 

table

顯示的這一行資訊是關於哪一張表的。有時候並不是真正的表名。

Query-7explain select * from (select * from (select * from people a) b ) c;

可以看到如果指定了別名就顯示的別名。

<derivedN>N就是id值，指該id值對應的那一步操作的結果。

還有<unionM,N>這種類型，出現在UNION語句中，見Query-4。

注意：MySQL對待這些表和普通表一樣，但是這些“暫存資料表”是沒有任何索引的。

 

type

type列很重要，是用來說明表與表之間是如何進行關聯操作的，有沒有使用索引。MySQL中“關聯”一詞比一般意義上的要寬泛，MySQL認為任何一次查詢都是一次“關聯”，並不僅僅是一個查詢需要兩張表才叫關聯，所以也可以理解MySQL是如何訪問表的。主要有下面幾種類別。

const

當確定最多隻會有一行匹配的時候，MySQL最佳化器會在查詢前讀取它而且唯讀取一次，因此非常快。const只會用在將常量和主鍵或唯一索引進行比較時，而且是比較所有的索引欄位。people表在id上有一個主鍵索引，在(zipcode,firstname,lastname)有一個二級索引。因此Query-8的type是const而Query-9並不是：

Query-8explain select * from people where id=1;

Query-9explain select * from people where zipcode = 100000;

注意下面的Query-10也不能使用const table，雖然也是主鍵，也只會返回一條結果。

Query-10explain select * from people where id >2;

 

system

這是const連線類型的一種特例，表僅有一行滿足條件。

Query-11explain select * from (select * from people where id = 1 )b;

<derived2>已經是一個const table並且只有一條記錄。

 

eq_ref

eq_ref類型是除了const外最好的連線類型，它用在一個索引的所有部分被聯結使用並且索引是UNIQUE或PRIMARY KEY。

需要注意InnoDB和MyISAM引擎在這一點上有點差別。InnoDB當資料量比較小的情況type會是All。我們上面建立的people 和 people_car預設都是InnoDB表。

Query-12explain select * from people a,people_car b where a.id = b.people_id;

我們建立兩個MyISAM表people2和people_car2試試：

CREATE TABLE people2(    id bigint auto_increment primary key,    zipcode char(32) not null default ‘‘,    address varchar(128) not null default ‘‘,    lastname char(64) not null default ‘‘,    firstname char(64) not null default ‘‘,    birthdate char(10) not null default ‘‘)ENGINE = MyISAM;CREATE TABLE people_car2(    people_id bigint,    plate_number varchar(16) not null default ‘‘,    engine_number varchar(16) not null default ‘‘,    lasttime timestamp)ENGINE = MyISAM;

Query-13explain select * from people2 a,people_car2 b where a.id = b.people_id;

我想這是InnoDB對效能權衡的一個結果。

eq_ref可以用於使用 = 操作符比較的帶索引的列。比較值可以為常量或一個使用在該表前面所讀取的表的列的運算式。如果關聯所用的索引剛好又是主鍵，那麼就會變成更優的const了：

Query-14explain select * from people2 a,people_car2 b where a.id = b.people_id and b.people_id = 1;

 

ref

這個類型跟eq_ref不同的是，它用在關聯操作只使用了索引的最左首碼，或者索引不是UNIQUE和PRIMARY KEY。ref可以用於使用=或<=>操作符的帶索引的列。

為了說明我們重建立立上面的people2和people_car2表，仍然使用MyISAM但是不給id指定primary key。然後我們分別給id和people_id建立非唯一索引。

reate index people_id on people2(id);create index people_id on people_car2(people_id);

然後再執行下面的查詢：

Query-15explain select * from people2 a,people_car2 b where a.id = b.people_id and a.id > 2;

Query-16explain select * from people2 a,people_car2 b where a.id = b.people_id and a.id = 2;

Query-17explain select * from people2 a,people_car2 b where a.id = b.people_id;

Query-18explain select * from people2 where id = 1;

看上面的Query-15，Query-16和Query-17，Query-18我們發現MyISAM在ref類型上的處理也是有不同策略的。

對於ref類型，在InnoDB上面執行上面三條語句結果完全一致。

 

fulltext

連結是使用全文索引進行的。一般我們用到的索引都是B樹，這裡就不舉例說明了。

 

ref_or_null

該類型和ref類似。但是MySQL會做一個額外的搜尋包含NULL列的操作。在解決子查詢中經常使用該聯結類型的最佳化。（詳見這裡)。

Query-19mysql> explain select * from people2 where id = 2 or id is null;

Query-20explain select * from people2 where id = 2 or id is not null;

注意Query-20使用的並不是ref_or_null，而且InnnoDB這次表現又不相同（資料量大的情況下有待驗證）。

 

index_merger

該聯結類型表示使用了索引合并最佳化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。關於索引合并最佳化看這裡。

 

unique_subquery

該類型替換了下面形式的IN子查詢的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery是一個索引尋找函數，可以完全替換子查詢，效率更高。

 

index_subquery

該聯結類型類似於unique_subquery。可以替換IN子查詢，但只適合下列形式的子查詢中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

 

range

只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range:

Query-21explain select * from people where id = 1 or id = 2;

注意在我的測試中：發現只有id是主鍵或唯一索引時type才會為range。

這裡順便挑剔下MySQL使用相同的range來表示範圍查詢和列表查詢。

explain select * from people where id >1;

 explain select * from people where id in (1,2);

但事實上這兩種情況下MySQL如何使用索引是有很大差別的：

我們不是挑剔：這兩種訪問效率是不同的。對於範圍條件查詢，MySQL無法使用範圍列後面的其他索引列了，但是對於“多個等值條件查詢”則沒有這個限制了。

——出自《高效能MySQL第三版》

 

index

該聯結類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引檔案通常比資料檔案小。這個類型通常的作用是告訴我們查詢是否使用索引進行排序操作。

Query-22explain select * from people order by id;

至於什麼情況下MySQL會利用索引進行排序，等有時間再仔細研究。最典型的就是order by後面跟的是主鍵。

 

ALL

最慢的一種方式，即全表掃描。

 

總的來說：上面幾種連線類型的效能是依次遞減的（system>const），不同的MySQL版本、不同的儲存引擎甚至不同的資料量表現都可能不一樣。

 

possible_keys

possible_keys列指出MySQL能使用哪個索引在該表中找到行。

 

key

key列顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

 

key_len

key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。使用的索引的長度。在不損失精確性的情況下，長度越短越好 。

 

ref

ref列顯示使用哪個列或常數與key一起從表中選擇行。

 

rows

rows列顯示MySQL認為它執行查詢時必須檢查的行數。注意這是一個預估值。

 

Extra

Extra是EXPLAIN輸出中另外一個很重要的列，該列顯示MySQL在查詢過程中的一些詳細資料，包含的資訊很多，只選擇幾個重點的介紹下。

Using filesort 

MySQL有兩種方式可以產生有序的結果，通過排序操作或者使用索引，當Extra中出現了Using filesort 說明MySQL使用了後者，但注意雖然叫filesort但並不是說明就是用了檔案來進行排序，只要可能排序都是在記憶體裡完成的。大部分情況下利用索引排序更快，所以一般這時也要考慮最佳化查詢了。

 

Using temporary

說明使用了暫存資料表，一般看到它說明查詢需要最佳化了，就算避免不了暫存資料表的使用也要盡量避免硬碟暫存資料表的使用。

 

Not exists

MYSQL最佳化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行， 就不再搜尋了。

 

Using index 

說明查詢是覆蓋了索引的，這是好事情。MySQL直接從索引中過濾不需要的記錄並返回命中的結果。這是MySQL服務層完成的，但無需再回表查詢記錄。

 

Using index condition

這是MySQL 5.6出來的新特性，叫做“索引條件推送”。簡單說一點就是MySQL原來在索引上是不能執行如like這樣的操作的，但是現在可以了，這樣減少了不必要的IO操作，但是只能用在二級索引上，詳情點這裡。

 

Using where

使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給使用者。

注意：Extra列出現Using where表示MySQL伺服器將儲存引擎返回服務層以後再應用WHERE條件過濾。

 

EXPLAIN的輸出內容基本介紹完了，它還有一個擴充的命令叫做EXPLAIN EXTENDED，主要是結合SHOW WARNINGS命令可以看到一些更多的資訊。一個比較有用的是可以看到MySQL最佳化器重構後的SQL。

 

mysql之explain