前言
上篇介紹了 MySQL 怎麼用 B+Tree 儲存資料以及 Clustered Index & Secondary Index 的差別後，是不是會很好奇 MySQL 實際上怎麼在那麼多 Index Tree 中查詢資料的勒？假設你的 Table 有很多 Index MySQL 會怎麼選擇勒？這篇就讓我們稍稍探索一下 MySQL 是如何執行一個 Query 的吧。
p.s 還沒看 Ep1 的建議先看喔。
內文 — 我的 SQL 怎麼執行我的查詢
首先讓我們看看以下的例子：
有一個 orders table，在 user_id & status 上分別加上 idx_user_id & idx_status 兩個 index，那麼在執行以下 Query 時，MySQL 會兩個 index 都用到嗎？還是只會用到其中一個？
SELECT * FROM orders WHERE user_id = 10 AND status = 2;大部分情況而言，一個 Query 只用一個 index 查詢，以上述例子而言，MySQL 可能會選擇使用 idx_user_id 這個 index，因為 idx_user_id 能過濾掉較多的資料，當 MySQL 從 Secondary Index Tree 獲取 primary key 後，因為 SELECT * 的原因，MySQL 必須再去 Clustered Index Tree 獲取完整資料 (回 Clustered Index 找資料稱為 Key Lookup)。
Key Lookup 指的是，一次查詢在 Secondary Index 無法完成，需要回 Clustered Index 的 Leaf Node 找其他欄位資料。
透過上面的範例你是否有以下的疑惑勒？(沒有沒關係，反正我有)
問題一：MySQL 是怎麼決定要怎麼執行這個 Query？
問題二：有辦法知道 MySQL 執行 Query 的細節嗎？
問題三：要怎麼建 Index 才能應付多欄位的查詢條件？
就讓我們來一個一個回答看看吧。
問題一：MySQL 是怎麼決定要怎麼執行這個 Query？
首先就像一般人做事會先計畫一樣，MySQL 在執行 Query 前也會先規劃出一個 Execution Plan，而要規劃出這個 Plan 總共有三個步驟：
步驟 1. Parser : 解析 SQL 語法確保合法後，會將 SQL 轉換成 Tree 的結構，就像是 Compiler 將 code 轉換成 Abstract syntax tree，DB 會將 SQL 轉換成 Query Tree。
步驟 2. Optimizer : 得到 Query Tree 之後，優化器會透過該 Tree 的結構去制定 Execution Plan，這個 Plan 包括很多事情，例如用哪個 index，如何 join table 以及排序資料等，Optimizer 會制定出多個 Plan 並從中選一個 “它認為的” 最優解，判斷最優解的因素有很多，例如 Plan 的複雜性，Table Size ，欄位的 cardinality，IO 次數等等。
步驟 3. Executor : 最後一步就是執行 Execution Plan 啦，執行的部分主要涉及兩塊 engine:
- storage engine : 也就是 innodb 的角色，負責從 disk 拉資料出來。
- transaction engine : 負責確保 ACID 原則。
OK，既然現在知道 Execution Plan 的存在，總會好奇 Plan 的細節吧，不能 MySQL 自己規劃不跟我說吧，好東西要分享的啊。
問題二：有辦法知道 MySQL 執行 Query 的細節嗎？
想知道 MySQL Execution Plan 的細節嗎？那就叫他解釋解釋！
MySQL 有一個 Explain 語法能夠將 Execution Plan 看得清清楚楚，接下來就來講講，Explain 能讓你看到什麼資訊吧！
首先最直白的資訊就是 prossible_keys ＆ key：
prossible_keys，query 中可以用的 inedx
key，query 中實際用的 index
除了確認使用的 index 之外，如何使用 index 也是很重要的資訊，我們可以透過 type & extra 獲得更多細節：
type，代表 Execution Plan 怎麼查詢資料的
- const : 查詢一筆資料 (e.g WHERE id = 1)
- system : table 裡面只有一筆資料
- eq_ref : 查詢條件是來自於不同 table 的 column
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;- ref : 剛 eq_ref 很像，但是 ref_table.key_column 可能不是唯一值或主鍵。
自己感覺 ref & eq_ref 比較不常見，真正在優化 Query 時，比較常見的可能是以下 Type：
- ALL : 可怕的 Full Table Scan，也就是依照 page 建立的順序讀資料，有點像是一個 File 從第一個字慢慢往下查。
- index : 第二可怕的 Full Index Scan，掃描完整的 index tree，雖然都是 scan 所有資料，但與 Full Table Scan 相比，Full Index Scan 不會 scan 到 leaf node 所以 scan 的資料量會比較小。
SELECT * FROM orders;
會用到 Full Table ScanSELECT user_id FROM orders;
因為 select 只有 user_id 所以不需要回 clustered index，只需要 scan idx_user_id index tree，所以會用 Full Index Scan
像上面比較可怕的 Query 要優化成哪樣的 type 呢：
- range : 使用 index 查詢一段範圍內的資料，同樣都是用 index tree，但 range 效能會快很多，因為不會 scan 所有資料。
- index_merge : 在多個 range scan 的時候，例如 OR 條件，可以併行地去不同 index tree 查資料後在 merge 起來。
總得來說，type 的查詢速度排序為 const=system > eq_ref > ref > range ≈ index_merge > index > ALL。
看完 type 之後還感受不出來效能問題在哪裡的話，那麼還可以看看 extra：
extra，一些額外描述 MySQL 怎麼執行 Query的訊息
- using index : 不用 Key Lookup，直接用 index tree 中的 non-leaf node 的資料就能回傳。
- using filesort : 無法使用 index 排序，需要花 CPU 在 application layer 排序。
- using where : 表示 index tree 過濾完資料後，還需要花 CPU 在 application layer 在過濾一次。
- using temporary : query 過程中如果有用到 Union，Sub Query 或 Join 的方式會產生一個臨時的表，可能在記憶體也可能在 Disk，取決於臨時表的大小，通常建立 temporary table 都會消耗額外的資源。
- using index condition : 使用到 index condition pushdown (ICP)的優化，通常會發生在使用 composite index 時的優化，待會會詳細解釋。
除了上述資訊外，rows 跟 filtered 也是滿有趣的資訊：
rows ，實際會 Scan 的資料筆數。
filtered， Scan 後，回傳的資料百分比。
如果 Query 的 WHERE 條件不是 index， Full Table Scan 會造成 rows 的筆數非常多，隨後會在 application layer 中過濾資料，所以 filtered 的比率就會低於 100 %。
如果 release_year 不是 indexEXPLAIN SELECT * FROM film WHERE release_year = 2006;key: NULL
rows: 1000
filtered: 10.00
Extra: Using where
接下來有一個資訊是專門給 composite index 用的，key_len：
key_len，query 使用到 composite index 時，不一定會所有 index 都用到，因此 key_len 代表 index 使用的長度，單位為 bytes。
最後一些個人覺得比較不重要的資訊有，id，select_type & table_type 感興趣的可以去這裡看說明。
問題三：要怎麼建 Index 才能應付多欄位的查詢條件？
知道 MySQL 如何執行 Query 後，讓我們看看以下 Query 應該怎麼建立 index 吧。
SELECT * FROM orders WHERE user_id = 1 AND status = 1AND created_at <'2020-10-10';為了讓 Query 能夠更精準，我們可以透過建立 composite index (idx_user_id_status_created_at) 來囊括上面三個 columns，而在建立 composite index 時，column 在 index 中的順序非常重要。
例如， idx_user_id_status_created_at(user_id, status, created) ＆idx_user_id_created_at_status(user_id, created, status) 這兩個 index 在上面的 query 中，效能可是會不一樣的，讓我們看看以下案例：
我有以下這些資料
CASE 1: idx_user_id_status_created_at(user_id, status, created) 排序出來的資料會是：
SELECT * FROM orders WHERE user_id = 1 AND status = 1 AND created_at < '2021-10-10';使用上面查詢時，user_id = 1 AND status = 1 過濾後的資料 created_at 欄位還是有序的，因此 created_at < '2020-10-10' 仍可使用 Binary Search，此時 MySQL 的 Query 會用到完整的 composite index，也就是 user_id，status 以及 created_at 三個欄位都用 Binary Search 找到對應的 rows。
CASE 2: idx_user_id_created_at_status(user_id, created_at, status) 排序出來的資料會是：
你們可以發現 user_id = 1 AND created_at < '2020-10-10' 過濾後的資料 status 變成無序的資料 (1,2,1)，因此 MySQL 無法在 status 上使用 Binary Search，最終 MySQL 只會用到 user_id & created_at 這兩個 column，無法用到完整的 composite index。
也就是說，在建立 composite index 時須用 range search 的情境來定義 column 在 composite index 的順序。
那麼 user_id & status 都沒有 range search 情境，順序對調是否沒差，在上面的查詢是這樣沒錯，但聰明的你一定會想到，如果是 idx_status_user_id 的話，SELECT * FROM orders WHERE user_id = 1; 查詢是無法用到 index，因為建立出來的資料會是這樣：
很明顯 user_id 是無序資料，此外當你有了 idx_user_id_status_created_at 這個 index，就不用另外建立 idx_user_id，因為 SELECT * FROM orders WHERE user_id = 1; 這樣的查詢，也可以使用到 idx_user_id_status_created_at。
出個小題目， last_name & first_name composite index 的順序應該要是如何？
答案是 idx_name(last_name, first_name)，詳細原因可以參考這裡。
最後讓我們來回顧一下，Explain extra 訊息中的 using index condition 提到的 index condition push down (ICP)優化，讓我看一下案例：
在 idx_user_id_status_created_at index 下使用
SELECT * FROM orders WHERE user_id = 1 AND status BETWEEN 1 AND 3 AND created_at > '2020-10-10';由於 status 是 range search，所以 composite index 無法用到 created_at 欄位，因此在沒有使用到 ICP 優化的情況， db engine 從 Secondary Index 過濾完資料，MySQL 再 Key Lookup 找出完整資料後，會在 application 在過濾一次，因此 extra 訊息會顯示 using where，但如果使用到 ICP 優化 db engine 在 Secondary Index 中的查詢會是：
step 1 : 使用 binary search 過濾 user_id & status 兩個欄位
step 2 : 再一筆筆過濾 created_at > '2020-10-10' 的資料
使用 ICP 的好處是，透過 Secondary Index 過濾更多資料，需要 Key Lookup 的資料也會變少，也不需要在 application 過濾資料。
總結
- MySQL 在執行 Query 時會先制定 Execution Plan，透過 Explain 語法可以看 Execution Plan 的細節。
- Explain 後的資料，key，type 以及 extra 等訊息會顯示 MySQL 如何使用 index 查詢。
- composite index 可以應付複雜的查詢，但 column 的順序很重要。
- Index Condition Pushdown 是用在 composite index 的一種 index 查詢優化方式。
下集預告
看完 Ep1 & Ep2 後，我想大家應該多少對 index 有了更多的了解，除了 index 以及查詢優化外，在 db 的領域中 transaction 也是一門值得討論的學問，接下來的篇章中，我們就來聊聊 MySQL 是如何實現 transaction 的 ACID 特性吧。
