引言：

實(shí)際項(xiàng)目開發(fā)中，由于我們不知道實(shí)際查詢的時候數(shù)據(jù)庫里發(fā)生了什么事情，數(shù)據(jù)庫軟件是怎樣掃描表、怎樣使用索引的，因此，我們能感知到的就只有

sql語句運(yùn)行的時間，在數(shù)據(jù)規(guī)模不大時，查詢是瞬間的，因此，在寫sql語句的時候就很少考慮到性能的問題。但是當(dāng)數(shù)據(jù)規(guī)模增大，如千萬、億的時候，我們運(yùn)

行同樣的sql語句時卻發(fā)現(xiàn)遲遲沒有結(jié)果，這個時候才知道數(shù)據(jù)規(guī)模已經(jīng)限制了我們查詢的速度。所以，查詢優(yōu)化和索引也就顯得很重要了。

問題：

當(dāng)我們在查詢前能否預(yù)先估計(jì)查詢究竟要涉及多少行、使用哪些索引、運(yùn)行時間呢？答案是能的，mysql提供了相應(yīng)的功能和語法來實(shí)現(xiàn)該功能。

分析：

MySql提供了EXPLAIN語法用來進(jìn)行查詢分析，在SQL語句前加一個"EXPLAIN"即可。比如我們要分析如下SQL語句：

explain select * from table where table.id = 1

table | type | possible_keys | key | key_len | ref | rows | Extra

EXPLAIN列的解釋

table 
顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

type 
這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALL

說明：不同連接類型的解釋（按照效率高低的順序排序）

system：表只有一行：system表。這是const連接類型的特殊情況。

const ：表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因?yàn)橹挥幸恍校@個值實(shí)際就是常數(shù)，因?yàn)镸YSQL先讀這個值然后把它當(dāng)做常數(shù)來對待。

eq_ref：在連接中，MYSQL在查詢時，從前面的表中，對每一個記錄的聯(lián)合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用。

ref：這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊前綴）時發(fā)生。對于之前的表的每一個行聯(lián)合，全部記錄都將從表中讀出。這個類型嚴(yán)重依賴于根據(jù)索引匹配的記錄多少—越少越好。

range：這個連接類型使用索引返回一個范圍中的行，比如使用>或<查找東西時發(fā)生的情況。

index：這個連接類型對前面的表中的每一個記錄聯(lián)合進(jìn)行完全掃描（比ALL更好，因?yàn)樗饕话阈∮诒頂?shù)據(jù)）。

ALL：這個連接類型對于前面的每一個記錄聯(lián)合進(jìn)行完全掃描，這一般比較糟糕，應(yīng)該盡量避免。

possible_keys 
顯示可能應(yīng)用在這張表中的索引。如果為空，沒有可能的索引。可以為相關(guān)的域從WHERE語句中選擇一個合適的語句

key 
實(shí)際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MYSQL會選擇優(yōu)化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強(qiáng)制使用一個索引或者用IGNORE INDEX（indexname）來強(qiáng)制MYSQL忽略索引

key_len 
使用的索引的長度。在不損失精確性的情況下，長度越短越好

ref 
顯示索引的哪一列被使用了，如果可能的話，是一個常數(shù)

rows 
MYSQL認(rèn)為必須檢查的用來返回請求數(shù)據(jù)的行數(shù)

Extra 
關(guān)于MYSQL如何解析查詢的額外信息。將在表4.3中討論，但這里可以看到的壞的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，結(jié)果是檢索會很慢

說明：extra列返回的描述的意義

Distinct ：一旦mysql找到了與行相聯(lián)合匹配的行，就不再搜索了。

Not exists ：mysql優(yōu)化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標(biāo)準(zhǔn)的行，就不再搜索了。

Range checked for each Record（index map:#） ：沒有找到理想的索引，因此對從前面表中來的每一個行組合，mysql檢查使用哪個索引，并用它來從表中返回行。這是使用索引的最慢的連接之一。

Using filesort ：看到這個的時候，查詢就需要優(yōu)化了。mysql需要進(jìn)行額外的步驟來發(fā)現(xiàn)如何對返回的行排序。它根據(jù)連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行。

Using index ：列數(shù)據(jù)是從僅僅使用了索引中的信息而沒有讀取實(shí)際的行動的表返回的，這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候。

Using temporary ：看到這個的時候，查詢需要優(yōu)化了。這里，mysql需要創(chuàng)建一個臨時表來存儲結(jié)果，這通常發(fā)生在對不同的列集進(jìn)行ORDER BY上，而不是GROUP BY上。

Where used ：使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會發(fā)生，或者是查詢有問題。

因此，弄明白了explain語法返回的每一項(xiàng)結(jié)果，我們就能知道查詢大致的運(yùn)行時間了，如果查詢里沒有用到索引、或者需要掃描的行過多，那么可以感到明顯的延遲。因此需要改變查詢方式或者新建索引。mysql中的explain語法可以幫助我們改寫查詢，優(yōu)化表的結(jié)構(gòu)和索引的設(shè)置，從而最大地提高查詢效率。當(dāng)然，在大規(guī)模數(shù)據(jù)量時，索引的建立和維護(hù)的代價也是很高的，往往需要較長的時間和較大的空間，如果在不同的列組合上建立索引，空間的開銷會更大。因此索引最好設(shè)置在需要經(jīng)常查詢的字段中。