MYSQL執行計畫

標籤：重要   檢索   row   項目   使用   href   問題   ora   tab   

引言：

實際項目開發中，由於我們不知道實際查詢的時候資料庫裡發生了什麼事情，資料庫軟體是怎樣掃描表、怎樣使用索引的，因此，我們能感知到的就只有

sql語句啟動並執行時間，在資料規模不大時，查詢是瞬間的，因此，在寫sql語句的時候就很少考慮到效能的問題。但是當資料規模增大，如千萬、億的時候，我們運

行同樣的sql語句時卻發現遲遲沒有結果，這個時候才知道資料規模已經限制了我們查詢的速度。所以，查詢最佳化和索引也就顯得很重要了。

問題：

當我們在查詢前能否預先估計查詢究竟要涉及多少行、使用哪些索引、已耗用時間呢？答案是能的，MySQL提供了相應的功能和文法來實現該功能。

分析：

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

explain select * from table where table.id = 1 

運行上面的sql語句後你會看到，下面的表頭資訊：

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

 

EXPLAIN列的解釋

table 
顯示這一行的資料是關於哪張表的

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

 

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

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

const ：表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行，這個值實際就是常數，因為MYSQL先讀這個值然後把它當做常數來對待。

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

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

range：這個連線類型使用索引返回一個範圍中的行，比如使用>或<尋找東西時發生的情況。

index：這個連線類型對前面的表中的每一個記錄聯合進行完全掃描（比ALL更好，因為索引一般小於表資料）。

ALL：這個連線類型對於前面的每一個記錄聯合進行完全掃描，這一般比較糟糕，應該盡量避免。

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

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

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

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

rows 
MYSQL認為必須檢查的用來返回請求資料的行數

Extra 
關於MYSQL如何解析查詢的額外資訊。將在表4.3中討論，但這裡可以看到的壞的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，結果是檢索會很慢

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

Distinct ：一旦mysql找到了與行相聯合匹配的行，就不再搜尋了。

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

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

Using filesort ：看到這個的時候，查詢就需要最佳化了。mysql需要進行額外的步驟來發現如何對返回的行排序。它根據連線類型以及儲存排序索引值和匹配條件的全部行的行指標來排序全部行。

Using index ：列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表返回的，這發生在對錶的全部的請求列都是同一個索引的部分的時候。

Using temporary ：看到這個的時候，查詢需要最佳化了。這裡，mysql需要建立一個暫存資料表來儲存結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上。

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

 

連結: 

MYSQL執行計畫