高效能mysql 第6章 查詢效能最佳化

標籤：mil   amp   ima   回話   頁面   rom   color   count()   最佳化器   

在mysql的執行計畫中：

 

id

id用來表示執行順序，id相同的為一組，先執行id數字大的組，然後執行數字小的組。在id相同的一組內，順序由上而下執行。

type

表示MySQL在表中找到所需行的方式，又稱"訪問類型"，常見類型如下：

 

由左至右，由最差到最好。

ALL代表全表掃描，index代表索引全掃描，range索引範圍掃描，ref是非唯一性索引掃描，常見的是作用在=的比較上，但是非唯一。eq_ref：唯一性索引掃描。

possible_keys

指出MySQL能使用哪個索引在表中找到行，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢使用。註：如果使用覆蓋索引掃描，此處為空白。

key

顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL

key_len

表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度

rows：

表示MySQL根據表統計資訊及索引選用情況，估算的找到所需的記錄所需要讀取的行數

ref：

暫時觀察，只有當type是ref的時候這個才有值。一般是const或者列。

extra列：

use index：這裡並不是表示使用了索引的意思。使用了索引可以通過key列來查看，而且key列顯示了使用索引的名稱。use index表示使用覆蓋索引掃描。就是說，沒有訪問資料檔案，所有的資料都是從索引檔案中擷取。

use where:這裡並不是表示使用了where條件的意思，而是說服務層從儲存引擎擷取資料之後再進行where過濾。

Using index condition：表示使用索引條件去讀取表中的資料，先掃描索引，然後根據索引指向的主健去讀取對應的資料

Using filesort：使用了本地檔案進行排序

Using temporary：使用了暫存資料表

Impossible WHERE：出現在最佳化階段，最佳化器根據表定義可以判斷出where條件根本不可能成立，比如主健不可能為空白

 Using join buffer (Block Nested Loop)：mysql使用了最佳化過的nest loop演算法，一次讀取多個塊.

 

查詢快取：

在解析一個sql之前，如果查詢快取是開啟的，mysql會去檢查這個查詢（根據sql的hash作為key）是否存在緩衝中，如果命中的話，那麼這個sql將會在解析，產生執行計畫之前返回結果。

查詢最佳化工具：

oracle使用基於cost的最佳化器。

可以使用last_query_cost來擷取當前回話的上一個查詢的cost：

1. /*使用SQL_NO_CACHE禁用查詢快取*/
2. select SQL_NO_CACHE count(*) from t_person;
3. show status like ‘last_query_cost‘;

返回的結果10.499表示mysql查詢最佳化工具認為大概需要10個資料頁的隨機尋找才能完成這個查詢。這個結果是根據一系列的資料得出的，如每個表或者索引的頁面個數，索引的基數，索引和資料行的長度，索引分布情況。

由於統計資訊的不準確，或者mysql本身的實現機制，有些情況下，計算的成本並不準確。

mysql能夠處理的最佳化有：

• 重新定義關聯表的順序
• 將外串連轉換為內串連
• 使用等價變換規則 如（5=5 and a> 5）被改寫成（a>5）
• 最佳化count(),min(),max()，如對有索引的列取min只需要取b-tree中找第一個節點就可以了。
• 預估並轉化為常數運算式。不會改變的函數如上面提到的min函數會被轉化為常數。

高效能mysql 第6章 查詢效能最佳化