初步理解MySQL（5.6）的執行計畫

標籤：style   io   os   使用   ar   strong   for   資料   sp   

聲明：以下均來自於MySQL英文手冊5.6。

1.MySQL所有的join都是使用 nest-loop join 演算法（嵌套迴圈演算法）。

2.對於一組joins，MySQL的join演算法會從第一個表讀取一行，然後一直往後逐個表找匹配行，如果某一行能夠從第一個表開始，之後每個表都能找到匹配的行，則輸出該“大行”；然後原路返回到之前的表直到能找到一張中包含匹配行的表為止，然後繼續向後面的每個表找匹配的行。

3.MySQL中索引的使用會被索引的cardinality所影響，cardinality就是基數，集合的勢的意思，太低會導致索引被棄用，舉個例子，如果向sex這種取值太單一的欄位建立索引，該索引可能不會被使用，因為基數太小了。可以通過Analyze Table tbl_name，來分析表，更新表的統計資料（InnoDB,MyISAM一般會自動更新）。

4.

執行計畫中各列的解釋：

列名	解釋	特殊說明
id	select的標識符	select在查詢中的序號，同序號就表明是一組，序號的組越大越先執行，越外層數值越小，如果是union結果則是NULL，同組的話按照從上到下的順序執行。
select_type	select類型	沒有子查詢或union時都是simple，否則會有primary和union之類的，這裡要注意帶有uncacheable的類型，表示無法緩衝，外層行切換會導致重新計算該select
table	輸出行的所屬表	表名或<unionM,N>，<derivedN>，<subqueryN>
partitions	匹配的分區	涉及到表的分區
type	join類型	第5點有詳細說明
possible_keys	可能被選擇的索引	用於尋找行的索引，獨立於執行順序的，這意味著不一定會使用，只是可能
key	實際被選擇的索引	可能會出現不在possible_keys的的key的情況，就是如果索引覆蓋了被選擇的列，即便該索引不能用於尋找行，但也會使掃描更快，因此MySQL也會使用
key_len	被選擇的鍵的長度	MySQL在多部分索引中使用的部分的長度，可能有多個值
ref	需要與索引比較的列	列或者常數
rows	估計要被檢驗的行數	InnoDB中不一定精確，只是一個估計值
filtered	被表的條件所過濾的行的百分比	估計
Extra	額外資訊	內容太多，需要再查文檔吧

5.type(join的類型)：

system（表只有一行），

const（表最多隻有一行匹配）,

eq_ref（每次與之前的表合并行都只在該表讀取一行，這是除了system，const之外最好的一種，特點是使用=，而且索引的所有部分都參與join且索引是主鍵或非空唯一鍵的索引），

ref（如果每次只匹配少數行，那就是比較好的一種，使用=或<=>，可以是左覆蓋索引或非主鍵或非唯一鍵），

fulltext（全文檢索搜尋）,

ref_or_null（與ref類似，但包括NULL）,

index_merge（表示出現了索引合并最佳化，這個比較複雜，目前的理解是合并單表的範圍索引掃描），

unique_subquery（把形如“select primary_key”的子查詢替換），

index_subquery（把形如”select key_column“的子查詢替換），

range（常數範圍），

index（一種情況是索引覆蓋的全表掃描，只需查索引（3中表格所指出的key的特例），另一種是）,

all（全表掃描）。

6.評估查詢效能可以通過計算磁碟定址次數：

小表一般一次定址可以讀取一行，因為索引可能被緩衝，

大表則可以通過下列公式：log(row_count) /log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) +1。

如果是寫入的話，通常需要四次定址，其中一次定址是尋找插入新索引的地方，通常更新索引需要兩次（B樹插入新節點後調整的平均次數是兩次？Why？統計學原理？），最後一次寫入改行。

7.SELECT @@optimizer_switch;或show variables like ‘optimizer_switch‘;可以查看最佳化器的一些選項。

8.什麼是執行計畫：

The set of operations that the optimizer chooses to perform the most efficient query is called the “query execution plan”, also known as the EXPLAIN plan。

最佳化器為了最有效執行查詢而選擇的一系列操作被稱為執行計畫。

初步理解MySQL（5.6）的執行計畫