《MYSQL數(shù)據(jù)庫mysql中explain用法詳解》要點(diǎn)：
本文介紹了MYSQL數(shù)據(jù)庫mysql中explain用法詳解，希望對您有用。如果有疑問，可以聯(lián)系我們。

如果在select語句前放上關(guān)鍵詞explain,mysql將解釋它如何處理select,提供有關(guān)表如何聯(lián)接和聯(lián)接的次序.

explain的每個(gè)輸出行提供一個(gè)表的相關(guān)信息,并且每個(gè)行包括下面的列：

1,id?? select識別符.這是select的查詢序列號.
2,select_type 可以為一下任何一種類型
simple? 簡單select(不使用union或子查詢)
primary?? 最外面的select
union??? union中的第二個(gè)或后面的select語句
dependent union? union中的第二個(gè)或后面的select語句,取決于外面的查詢
union result? union的結(jié)果.
subquery 子查詢中的第一個(gè)select
dependent subquery? 子查詢中的第一個(gè)select,取決于外面的查詢
derived??? 導(dǎo)出表的select(from子句的子查詢)
3,table? 輸出的行所引用的表.
4,type? 聯(lián)接類型.下面給出各種聯(lián)接類型,按照從最佳類型到最壞類型進(jìn)行排序：
system? 表僅有一行(=系統(tǒng)表).這是const聯(lián)接類型的一個(gè)特例.
?const? 表最多有一個(gè)匹配行,它將在查詢開始時(shí)被讀取.因?yàn)閮H有一行,在這行的列值可被優(yōu)化器剩余部分認(rèn)為是常數(shù).const表很快,因?yàn)樗鼈冎蛔x取一次!
eq_ref 對于每個(gè)來自于前面的表的行組合,從該表中讀取一行.這可能是最好的聯(lián)接類型,除了const類型.它用在一個(gè)索引的所有部分被聯(lián)接使用并且索引是unique或primary key
ref? 對于每個(gè)來自于前面的表的行組合,所有有匹配索引值的行將從這張表中讀取.如果聯(lián)接只使用鍵的最左邊的前綴,或如果鍵不是unique或primary key(換句話說,如果聯(lián)接不能基于關(guān)鍵字選擇單個(gè)行的話),則使用ref.如果使用的鍵僅僅匹配少量行,該聯(lián)接類型是不錯(cuò)的.
ref可以用于使用=或<=>操作符的帶索引的列.
possible_keys 如果該列是null,則沒有相關(guān)的索引.在這種情況下,可以通過檢查where子句看是否它引用某些列或適合索引的列來提高你的查詢性能.如果是這樣,創(chuàng)造一個(gè)適當(dāng)?shù)乃饕⑶以俅斡胑xplain檢查查詢key 列顯示mysql實(shí)際決定使用的鍵(索引).如果沒有選擇索引,鍵是null.要想強(qiáng)制mysql使用或忽視possible_keys列中的索引,在查詢中使用force index、use index或者ignore index.
5,rows? rows列顯示mysql認(rèn)為它執(zhí)行查詢時(shí)必須檢查的行數(shù).

以上簡單介紹了mysql中explain語句的用法,希望對大家有所幫助.

對mysql explain講的比較清楚的MYSQL必讀

在 explain的幫助下,您就知道什么時(shí)候該給表添加索引,以使用索引來查找記錄從而讓select 運(yùn)行更快.
如果由于不恰當(dāng)使用索引而引起一些問題的話,可以運(yùn)行 analyze table來更新該表的統(tǒng)計(jì)信息,例如鍵的基數(shù),它能幫您在優(yōu)化方面做出更好的選擇.MYSQL必讀

explain 返回了一行記錄,它包括了 select語句中用到的各個(gè)表的信息.這些表在結(jié)果中按照mysql即將執(zhí)行的查詢中讀取的順序列出來.mysql用一次掃描多次連接(single- sweep,multi-join)的方法來解決連接.這意味著mysql從第一個(gè)表中讀取一條記錄,然后在第二個(gè)表中查找到對應(yīng)的記錄,然后在第三個(gè)表中查找,依次類推.當(dāng)所有的表都掃描完了,它輸出選擇的字段并且回溯所有的表,直到找不到為止,因?yàn)橛械谋碇锌赡苡卸鄺l匹配的記錄下一條記錄將從該表讀取,再從下一個(gè)表開始繼續(xù)處理.
在mysql version 4.1中,explain輸出的結(jié)果格式改變了,使得它更適合例如 union語句、子查詢以及派生表的結(jié)構(gòu).更令人注意的是,它新增了2個(gè)字段： id和 select_type.當(dāng)你使用早于mysql4.1的版本就看不到這些字段了.
explain結(jié)果的每行記錄顯示了每個(gè)表的相關(guān)信息,每行記錄都包含以下幾個(gè)字段：MYSQL必讀

id
本次 select 的標(biāo)識符.在查詢中每個(gè) select都有一個(gè)順序的數(shù)值.
select_type
select 的類型,可能會有以下幾種：
simple: 簡單的 select (沒有使用 union或子查詢)MYSQL必讀

primary: 最外層的 select.MYSQL必讀

union: 第二層,在select 之后使用了 union.MYSQL必讀

dependent union: union 語句中的第二個(gè)select,依賴于外部子查詢MYSQL必讀

subquery: 子查詢中的第一個(gè) selectMYSQL必讀

dependent subquery: 子查詢中的第一個(gè) subquery依賴于外部的子查詢MYSQL必讀

derived: 派生表 select(from子句中的子查詢)MYSQL必讀

table
記錄查詢引用的表.MYSQL必讀

type
表連接類型.以下列出了各種不同類型的表連接,依次是從最好的到最差的：MYSQL必讀

system:表只有一行記錄(等于系統(tǒng)表).這是 const表連接類型的一個(gè)特例.MYSQL必讀

const:表中最多只有一行匹配的記錄,它在查詢一開始的時(shí)候就會被讀取出來.由于只有一行記錄,在余下的優(yōu)化程序里該行記錄的字段值可以被當(dāng)作是一個(gè)恒定值.const表查詢起來非常快,因?yàn)橹灰x取一次!const 用于在和 primary key 或unique 索引中有固定值比較的情形.下面的幾個(gè)查詢中,tbl_name 就是 c表了：
select * from tbl_name where primary_key=1; select * from tbl_namewhere primary_key_part1=1 and primary_key_part2=2;MYSQL必讀

eq_ref:從該表中會有一行記錄被讀取出來以和從前一個(gè)表中讀取出來的記錄做聯(lián)合.與const類型不同的是,這是最好的連接類型.它用在索引所有部分都用于做連接并且這個(gè)索引是一個(gè)primary key 或 unique 類型.eq_ref可以用于在進(jìn)行"="做比較時(shí)檢索字段.比較的值可以是固定值或者是表達(dá)式,表達(dá)示中可以使用表里的字段,它們在讀表之前已經(jīng)準(zhǔn)備好了.以下的幾個(gè)例子中,mysql使用了eq_ref 連接來處理 ref_table：MYSQL必讀

select * from ref_table,other_table whereref_table.key_column=other_table.column; select * fromref_table,other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_part2=1;MYSQL必讀

ref: 該表中所有符合檢索值的記錄都會被取出來和從上一個(gè)表中取出來的記錄作聯(lián)合.ref用于連接程序使用鍵的最左前綴或者是該鍵不是 primary key 或 unique索引(換句話說,就是連接程序無法根據(jù)鍵值只取得一條記錄)的情況.當(dāng)根據(jù)鍵值只查詢到少數(shù)幾條匹配的記錄時(shí),這就是一個(gè)不錯(cuò)的連接類型. ref還可以用于檢索字段使用 =操作符來比較的時(shí)候.以下的幾個(gè)例子中,mysql將使用 ref 來處理ref_table：
select * from ref_table where key_column=expr; select * fromref_table,other_table whereref_table.key_column=other_table.column; select * fromref_table,other_table whereref_table.key_column_part1=other_table.column andref_table.key_column_part2=1;MYSQL必讀

ref_or_null: 這種連接類型類似 ref,不同的是mysql會在檢索的時(shí)候額外的搜索包含null 值的記錄.這種連接類型的優(yōu)化是從mysql4.1.1開始的,它經(jīng)常用于子查詢.在以下的例子中,mysql使用ref_or_null 類型來處理 ref_table：
select * from ref_table where key_column=expr or key_column is null;MYSQL必讀

unique_subquery: 這種類型用例如一下形式的 in 子查詢來替換 ref：
value in (select primary_key from single_table where some_expr)MYSQL必讀

unique_subquery: 只是用來完全替換子查詢的索引查找函數(shù)效率更高了.MYSQL必讀

index_subquery: 這種連接類型類似 unique_subquery.它用子查詢來代替in,不過它用于在子查詢中沒有唯一索引的情況下,例如以下形式：
value in (select key_column from single_table where some_expr)MYSQL必讀

range: 只有在給定范圍的記錄才會被取出來,利用索引來取得一條記錄.key字段表示使用了哪個(gè)索引.key_len字段包括了使用的鍵的最長部分.這種類型時(shí) ref 字段值是 null.range用于將某個(gè)字段和一個(gè)定植用以下任何操作符比較時(shí) =, <>, >,>=, <, <=, is null, <=>, between, 或 in：
select * from tbl_name where key_column = 10; select * fromtbl_name where key_column between 10 and 20; select * from tbl_namewhere key_column in (10,20,30); select * from tbl_name wherekey_part1= 10 and key_part2 in (10,20,30);MYSQL必讀

index: 連接類型跟 all 一樣,不同的是它只掃描索引樹.它通常會比 all快點(diǎn),因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小.mysql在查詢的字段知識單獨(dú)的索引的一部分的情況下使用這種連接類型.MYSQL必讀

all: 將對該表做全部掃描以和從前一個(gè)表中取得的記錄作聯(lián)合.這時(shí)候如果第一個(gè)表沒有被標(biāo)識為const的話就不大好了,在其他情況下通常是非常糟糕的.正常地,可以通過增加索引使得能從表中更快的取得記錄以避免all.

possible_keys

possible_keys字段是指 mysql在搜索表記錄時(shí)可能使用哪個(gè)索引.注意,這個(gè)字段完全獨(dú)立于explain 顯示的表順序.這就意味著 possible_keys里面所包含的索引可能在實(shí)際的使用中沒用到.如果這個(gè)字段的值是null,就表示沒有索引被用到.這種情況下,就可以檢查 where子句中哪些字段那些字段適合增加索引以提高查詢的性能.就這樣,創(chuàng)建一下索引,然后再用explain 檢查一下.詳細(xì)的查看章節(jié)"14.2.2 alter tablesyntax".想看表都有什么索引,可以通過 show index from tbl_name來看.

key

key字段顯示了mysql實(shí)際上要用的索引.當(dāng)沒有任何索引被用到的時(shí)候,這個(gè)字段的值就是null.想要讓mysql強(qiáng)行使用或者忽略在 possible_keys字段中的索引列表,可以在查詢語句中使用關(guān)鍵字force index, use index,或 ignore index.如果是 myisam 和 bdb 類型表,可以使用 analyzetable 來幫助分析使用使用哪個(gè)索引更好.如果是 myisam類型表,運(yùn)行命令 myisamchk --analyze也是一樣的效果.詳細(xì)的可以查看章節(jié)"14.5.2.1 analyze tablesyntax"和"5.7.2 table maintenance and crash recovery".MYSQL必讀

key_len

key_len 字段顯示了mysql使用索引的長度.當(dāng) key 字段的值為 null時(shí),索引的長度就是 null.注意,key_len的值可以告訴你在聯(lián)合索引中mysql會真正使用了哪些索引.MYSQL必讀

ref

ref 字段顯示了哪些字段或者常量被用來和 key配合從表中查詢記錄出來.MYSQL必讀

rows
rows 字段顯示了mysql認(rèn)為在查詢中應(yīng)該檢索的記錄數(shù).MYSQL必讀

extraMYSQL必讀

本字段顯示了查詢中mysql的附加信息.以下是這個(gè)字段的幾個(gè)不同值的解釋：MYSQL必讀

distinct:mysql當(dāng)找到當(dāng)前記錄的匹配聯(lián)合結(jié)果的第一條記錄之后,就不再搜索其他記錄了.MYSQL必讀

not exists:mysql在查詢時(shí)做一個(gè) left join優(yōu)化時(shí),當(dāng)它在當(dāng)前表中找到了和前一條記錄符合 left join條件后,就不再搜索更多的記錄了.下面是一個(gè)這種類型的查詢例子：
select * from t1 left join t2 on t1.id=t2.id where t2.id isnull;MYSQL必讀

假使 t2.id 定義為 not null.這種情況下,mysql將會掃描表 t1并且用 t1.id 的值在 t2 中查找記錄.當(dāng)在 t2中找到一條匹配的記錄時(shí),這就意味著 t2.id 肯定不會都是null,就不會再在 t2 中查找相同 id值的其他記錄了.也可以這么說,對于 t1 中的每個(gè)記錄,mysql只需要在t2 中做一次查找,而不管在 t2 中實(shí)際有多少匹配的記錄.MYSQL必讀

range checked for each record (index map: #)MYSQL必讀

mysql沒找到合適的可用的索引.取代的辦法是,對于前一個(gè)表的每一個(gè)行連接,它會做一個(gè)檢驗(yàn)以決定該使用哪個(gè)索引(如果有的話),并且使用這個(gè)索引來從表里取得記錄.這個(gè)過程不會很快,但總比沒有任何索引時(shí)做表連接來得快.MYSQL必讀

using filesort: mysql需要額外的做一遍從而以排好的順序取得記錄.排序程序根據(jù)連接的類型遍歷所有的記錄,并且將所有符合 where條件的記錄的要排序的鍵和指向記錄的指針存儲起來.這些鍵已經(jīng)排完序了,對應(yīng)的記錄也會按照排好的順序取出來.詳情請看"7.2.9how mysql optimizes order by".
using indexMYSQL必讀

字段的信息直接從索引樹中的信息取得,而不再去掃描實(shí)際的記錄.這種策略用于查詢時(shí)的字段是一個(gè)獨(dú)立索引的一部分.MYSQL必讀

using temporary: mysql需要?jiǎng)?chuàng)建臨時(shí)表存儲結(jié)果以完成查詢.這種情況通常發(fā)生在查詢時(shí)包含了groupby 和 order by 子句,它以不同的方式列出了各個(gè)字段.
using whereMYSQL必讀

where子句將用來限制哪些記錄匹配了下一個(gè)表或者發(fā)送給客戶端.除非你特別地想要取得或者檢查表種的所有記錄,否則的話當(dāng)查詢的extra 字段值不是 using where 并且表連接類型是 all 或 index時(shí)可能表示有問題.MYSQL必讀

如果你想要讓查詢盡可能的快,那么就應(yīng)該注意 extra 字段的值為usingfilesort 和 using temporary 的情況.MYSQL必讀

你可以通過 explain 的結(jié)果中 rows字段的值的乘積大概地知道本次連接表現(xiàn)如何.它可以粗略地告訴我們mysql在查詢過程中會查詢多少條記錄.如果是使用系統(tǒng)變量 max_join_size 來取得查詢結(jié)果,這個(gè)乘積還可以用來確定會執(zhí)行哪些多表select 語句.
下面的例子展示了如何通過 explain提供的信息來較大程度地優(yōu)化多表聯(lián)合查詢的性能.
假設(shè)有下面的 select 語句,正打算用 explain 來檢測：
explain select tt.ticketnumber, tt.timein, tt.projectreference,tt.estimatedshipdate, tt.actualshipdate, tt.clientid,tt.servicecodes, tt.repetitiveid, tt.currentprocess,tt.currentdppers tt.recordvolume, tt.dpprinted, et.country,et_1.country, do.custname from tt, et, et as et_1, do wherett.submittime is null and tt.actualpc = et.employid andtt.assignedpc = et_1.employid and tt.clientid = do.custnmbr;MYSQL必讀

在這個(gè)例子中,先做以下假設(shè)：MYSQL必讀

要比較的字段定義如下：
table? column? columntype
tt? actualpc char(10)
tt? assignedpc char(10)
tt? clientid char(10)
et? employid char(15)
do? custnmbr char(15) MYSQL必讀

數(shù)據(jù)表的索引如下：
table? index
tt? actualpc
tt? assignedpc
tt? clientid
et? employid (primary key)
do? custnmbr (primary key) MYSQL必讀

tt.actualpc 的值是不均勻分布的.MYSQL必讀

在任何優(yōu)化措施未采取之前,經(jīng)過 explain分析的結(jié)果顯示如下：
table type possible_keys key key_len ref rows extra
et all primarynull null null 74
do all primary null null null 2135
et_1 allprimary null null null 74
tt all assignedpc, null null null 3872 clientid, actualpc range checked for each record (key map: 35)MYSQL必讀

由于字段 type 的對于每個(gè)表值都是all,這個(gè)結(jié)果意味著mysql對所有的表做一個(gè)迪卡爾積；這就是說,每條記錄的組合.這將需要花很長的時(shí)間,因?yàn)樾枰獟呙杳總€(gè)表總記錄數(shù)乘積的總和.在這情況下,它的積是74 * 2135 * 74 * 3872 = 45,268,558,720條記錄.如果數(shù)據(jù)表更大的話,你可以想象一下需要多長的時(shí)間.
在這里有個(gè)問題是當(dāng)字段定義一樣的時(shí)候,mysql就可以在這些字段上更快的是用索引(對isam類型的表來說,除非字段定義完全一樣,否則不會使用索引).在這個(gè)前提下,varchar和 char是一樣的除非它們定義的長度不一致.由于 tt.actualpc 定義為char(10),et.employid 定義為 char(15),二者長度不一致.
為了解決這個(gè)問題,需要用 alter table 來加大 actualpc的長度從10到15個(gè)字符：
mysql> alter table tt modify actualpc varchar(15);MYSQL必讀

現(xiàn)在 tt.actualpc 和 et.employid 都是 varchar(15)
了.再來執(zhí)行一次 explain 語句看看結(jié)果：
table type possible_keys key key_len ref rows extra
tt allassignedpc, null null null 3872 using clientid, where actualpc
do all primary null null null 2135 range checked for each record (keymap: 1)
et_1 all primary null null null 74 range checked for eachrecord (key map: 1) et eq_ref primary primary 15 tt.actualpc 1MYSQL必讀

這還不夠,它還可以做的更好：現(xiàn)在 rows值乘積已經(jīng)少了74倍.這次查詢需要用2秒鐘.
第二個(gè)改變是消除在比較 tt.assignedpc = et_1.employid 和 tt.clientid= do.custnmbr 中字段的長度不一致問題：
mysql> alter table tt modify assignedpc varchar(15), ->modify clientid varchar(15);MYSQL必讀

現(xiàn)在 explain 的結(jié)果如下：
table type possible_keys key key_len ref rows extra
et all primary null null null 74
tt ref assignedpc, actualpc 15 et.employid 52 using clientid, where actualpc
et_1 eq_ref primary primary 15 tt.assignedpc 1
do eq_ref primary primary 15 tt.clientid 1MYSQL必讀

這看起來已經(jīng)是能做的最好的結(jié)果了.
遺留下來的問題是,mysql默認(rèn)地認(rèn)為字段 tt.actualpc的值是均勻分布的,然而表 tt并非如此.幸好,我們可以很方便的讓mysql分析索引的分布：
mysql> analyze table tt;MYSQL必讀

到此為止,表連接已經(jīng)優(yōu)化的很完美了,explain 的結(jié)果如下：
table type possible_keys key key_len ref rows extra
tt all assignedpc null null null 3872 using clientid, where actualpc
et eq_ref primary primary 15 tt.actualpc 1
et_1 eq_ref primary primary 15 tt.assignedpc 1
do eq_ref primary primary 15 tt.clientid 1MYSQL必讀

請注意,explain 結(jié)果中的 rows字段的值也是mysql的連接優(yōu)化程序大致猜測的,請檢查這個(gè)值跟真實(shí)值是否基本一致.如果不是,可以通過在select 語句中使用 straight_join 來取得更好的性能,同時(shí)可以試著在from分句中用不同的次序列出各個(gè)表.
MYSQL必讀

以下為補(bǔ)充資料：

借助explain,可以知道：
1)何時(shí)必須為表加入索引以得到一個(gè)使用索引找到記錄的更快的SELECT.
2)優(yōu)化器是否以一個(gè)最佳次序聯(lián)結(jié)表.為了強(qiáng)制優(yōu)化器對一個(gè)SELECT語句使用一個(gè)特定聯(lián)結(jié)次序,增加一個(gè)STRAIGHT_JOIN子句.
官方的關(guān)于explain的文檔在http://dev.mysql.com/doc/refman/5.1/en/using-explain.html(英文)MYSQL必讀

mysql explain詳解MYSQL必讀

mysql explain 使用的方法MYSQL必讀

EXPLAIN tbl_name
或：
EXPLAIN [EXTENDED] SELECT select_optionsMYSQL必讀

前者可以得出一個(gè)表的字段結(jié)構(gòu)等等,后者主要是給出相關(guān)的一些索引信息,而今天要講述的重點(diǎn)是后者.MYSQL必讀

舉例
MYSQL必讀

id
select查詢的序列號MYSQL必讀

select_type
select查詢的類型,主要是區(qū)別普通查詢和聯(lián)合查詢、子查詢之類的復(fù)雜查詢.MYSQL必讀

table
輸出的行所引用的表.MYSQL必讀

type
聯(lián)合查詢所使用的類型.
type顯示的是訪問類型,是較為重要的一個(gè)指標(biāo),結(jié)果值從好到壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般來說,得保證查詢至少達(dá)到range級別,最好能達(dá)到ref.MYSQL必讀

possible_keys
指出MySQL能使用哪個(gè)索引在該表中找到行.如果是空的,沒有相關(guān)的索引.這時(shí)要提高性能,可通過檢驗(yàn)WHERE子句,看是否引用某些字段,或者檢查字段不是適合索引.MYSQL必讀

key
顯示MySQL實(shí)際決定使用的鍵.如果沒有索引被選擇,鍵是NULL.MYSQL必讀

key_len
顯示MySQL決定使用的鍵長度.如果鍵是NULL,長度就是NULL.文檔提示特別注意這個(gè)值可以得出一個(gè)多重主鍵里mysql實(shí)際使用了哪一部分.MYSQL必讀

ref
顯示哪個(gè)字段或常數(shù)與key一起被使用.MYSQL必讀

rows
這個(gè)數(shù)表示mysql要遍歷多少數(shù)據(jù)才能找到,在innodb上是不準(zhǔn)確的.MYSQL必讀

Extra
如果是Only index,這意味著信息只用索引樹中的信息檢索出的,這比掃描整個(gè)表要快.
如果是where used,就是使用上了where限制.
如果是impossible where 表示用不著where,一般就是沒查出來啥.
如果此信息顯示Using filesort或者Using temporary的話會很吃力,WHERE和ORDER BY的索引經(jīng)常無法兼顧,如果按照WHERE來確定索引,那么在ORDER BY時(shí),就必然會引起Using filesort,這就要看是先過濾再排序劃算,還是先排序再過濾劃算.
常見的一些名詞解釋MYSQL必讀

Using filesort
MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行.MYSQL必讀

Using index
從只使用索引樹中的信息而不需要進(jìn)一步搜索讀取實(shí)際的行來檢索表中的列信息.MYSQL必讀

Using temporary
為了解決查詢,MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來容納結(jié)果.MYSQL必讀

ref
對于每個(gè)來自于前面的表的行組合,所有有匹配索引值的行將從這張表中讀取MYSQL必讀

ALL
完全沒有索引的情況,性能非常地差勁.MYSQL必讀

index
與ALL相同,除了只有索引樹被掃描.這通常比ALL快,因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小.MYSQL必讀

SIMPLE
簡單SELECT(不使用UNION或子查詢)MYSQL必讀

歡迎參與《MYSQL數(shù)據(jù)庫mysql中explain用法詳解》討論，分享您的想法，維易PHP學(xué)院為您提供專業(yè)教程。

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/12309.html