mysql explain用法

explain顯示了mysql如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。
使用方法，在select語句前加上explain就可以了，如：

explain select * from statuses_status where id=11;

創建測試表：

CREATE TABLE people(id int 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 int, 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 index(zipcode,firstname,lastname);

?explain介紹

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

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

?explain輸出結果共有id,select_type,table,type,possible_keys,key,key_len,ref,rows和Extra幾列。每一列分別代表什么意思呢，請看下面的解釋。

select_type 表示查詢中每個select語句的類型（簡單 OR復雜），可以有下面幾種

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

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

??????????????????? 查詢中若包含任何復雜的子部分，最外層查詢則被標記為：PRIMARY

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

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

c.UNION：UNION中第二個以及后面的SELECT語句。見Query-3。

d.DERIVED：派生表SELECT語句中FROM子句中的SELECT語句。見Query-2。

e.UNION RESULT：一個UNION查詢的結果。見Query-3。

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

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

g.SUBQUERY：子查詢中第一個SELECT語句。

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

table ：顯示的這一行信息是關于哪一張表的。有時候并不是真正的表名。

Query-7
explain 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-8
explain select * from people where id=1;

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

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

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

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

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

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

eq_ref

唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描。eq_ref類型是除了const外最好的連接類型。
創建員工表Employee和經理表Manager

create table Employee
(ID int auto_increment,Ename varchar(32), Age int, Salary float, MID int, Primary key (ID) ); create table Manager ( MID int, Name varchar(32), Primary key(MID) );

Query-12
explain select * from Employee A,Manager B where A.MID=B.MID;

MID對于表Manager是唯一的，主鍵索引，來與employee連接，故type為eq_ref。

ref

非唯一性索引掃描，返回匹配某個單獨值的所有行。常見于使用非唯一索引即唯一索引的非唯一前綴進行的查找。
這個類型跟eq_ref不同的是，它用在關聯操作只使用了索引的最左前綴，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的帶索引的列。

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

zipcode、firstname和lastname組成索引，這里只使用了name，即只使用了唯一性索引的一部分，故為ref。

fulltext
鏈接是使用全文索引進行的。一般我們用到的索引都是B樹，這里就不舉例說明了。
ref_or_null
該類型和ref類似。但是MySQL會做一個額外的搜索包含NULL列的操作。在解決子查詢中經常使用該聯接類型的優化。

range

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

索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行，常見于between、<、>，IN等的查詢。

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

Query-15
explain select * from people where id>1;

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

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

按索引掃描表，雖然還是全表掃描，但優點是索引是有序的。index與ALL區別為index類型只遍歷索引樹。

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

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

總結

explain的type列從最差到最好依次是：
ALL：全表掃描。
index：索引掃描。?
range：索引范圍掃描。
ref ：非唯一性索引掃描。?
eq_ref ：唯一性索引掃描。?
const,system：將查詢轉換為一個常量。?
null：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條件過濾。

轉自：http://www.cnblogs.com/xiaoxi/p/5688096.html