https://www.flickr.com/photos/8058098@N07/2718571627/
查詢不會總是只有一個條件而已，面對多條件的查詢，只包含單一欄位的索引能提供的幫助有限。因此我們可用多個欄位建立「聯合索引」，讓 MySQL 在索引中快速掌握符合條件的資料主鍵，減少回表查詢的成本。
本文將示範建立聯合索引，並搭配閱讀執行計劃，觀察不同索引的效果。接著認識最左前綴法則，了解聯合索引部份失效的可能因素。
一、範例資料介紹
以下是本文的範例資料表，描述了學生資料。有編號、名字、科系、總學分、入學日與狀態，共 6 個欄位。
並且使用「科系」欄位來建立索引。
CREATE INDEX `idx_student_dept` ON `student`(`dept`);
二、指定使用的索引
本文接下來還會建立其他索引。讓我們先了解如何指定查詢時要使用的索引，而不是讓 MySQL 自行決定。如此才能方便觀察不同索引的效果。
在查詢指令的 FROM 關鍵字後方，使用 USE INDEX 關鍵字，可「建議」MySQL 使用指定的索引，但未必會採用，範例如下。
使用 IGNORE INDEX 關鍵字，則是請 MySQL 不要使用該索引，範例如下。
三、聯合索引（Compounded Index）
（一）介紹
當使用多個欄位建立一個索引時，這種索引我們稱之為「聯合索引」、「複合索引」或「組合索引」。
聯合索引的用途，是在面臨多欄位的查詢條件時，提供更多資訊幫助 MySQL 在索引中尋找資料，藉此減少回表查詢的成本，提升效率。
以下指令分別是針對「科系 → 總學分」與「科系 → 狀態」的組合建立聯合索引。要注意的是，欄位順序是要經過考量的。
CREATE INDEX `idx_student_dept_credits` ON `student`(`dept`, `total_credits`); CREATE INDEX `idx_student_dept_status` ON `student`(`dept`, `status`);
我們知道索引是將資料的欄位值排序後，存進 B+ tree 樹狀結構。它的葉節點會儲存用來排序的欄位值，以及資料的主鍵。聯合索引也是如此，它是根據多個欄位來排序。
以下是將範例資料的「科系 + 總學分」欄位，建立成聯合索引，存放到 B+ tree 的示意圖。
從圖中的葉節點由左往右看，能發現聯合索引依照第一個欄位值做遞增排序。相同者，再比較第二個欄位值，依此類推。
站在全局的角度來看，由於「科系」是第一欄位，因此相同科系的資料在樹中都是被擺放在一起。然而「總學分」是第二欄位，只有在相同科系的局部範圍，才有順序可言，我們稱之「全局無序、局部有序」。
（二）案例一：第一欄位相等、第二欄位相等
以下是查詢會計系的畢業生。
EXPLAIN SELECT * FROM `student` USE INDEX (`idx_student_dept_status`) WHERE `dept` = "會計" AND `status` = "畢業";
執行計劃節錄如下：
| type | filtered | Extra |
|---|---|---|
| ref | 100.00 | NULL |
科系屬於第一欄位，值相同的資料在索引中會被放在相鄰的位置。因此聯合索引能幫助 MySQL 先定位到那些科系相符的資料。
而狀態為第二欄位，在索引中屬於局部有序，因此在同一科系的葉節點範圍，MySQL 也能快速找到符合下一個條件的資料。
由於索引中就已經能取得符合所有條件的資料主鍵，因此後續回表查詢時，便能直接找到原始資料，不必做多餘的讀取，故執行計劃的 filtered 為 100。
那麼，如果不使用聯合索引，改用單一欄位索引，效果會是如何呢？
EXPLAIN SELECT * FROM `student` USE INDEX (`idx_student_dept`) WHERE `dept` = "會計" AND `status` = "畢業";
執行計劃節錄如下：
| type | filtered | Extra |
|---|---|---|
| ref | 14.29 | Using where |
在索引中只能找到科系相符的資料主鍵，然而科系符合，並不代表狀態符合。因此 MySQL 回表查詢時，需額外比對狀態的欄位值。故執行計劃的 filtered 並非 100。而 Extra 也寫著「Using where」。
（三）案例二：第一欄位相等、第二欄位範圍
以下是查詢總學分滿 160 的會計系學生，使用了聯合索引。
EXPLAIN SELECT * FROM `student` USE INDEX (`idx_student_dept_credits`) WHERE `dept` = "會計" AND `total_credits` >= 160;
執行計劃節錄如下：
| type | filtered | Extra |
|---|---|---|
| range | 100.00 | Using index condition |
索引結構中，在第一欄位值相同的前提下，第二欄位值是有序的，因此聯合索引對這次的查詢也有幫助。在回表查詢前，MySQL 已經掌握符合所有條件的資料主鍵了。
而執行計劃的「Extra」寫著「Using index condition」，代表 MySQL 在索引中進行值範圍的比較，也就是總學分。
那麼，如果當初未建立聯合索引，只有「科系」這個單一欄位索引可用，效果會是如何呢？
EXPLAIN SELECT * FROM `student` USE INDEX (`idx_student_dept`) WHERE `dept` = "會計" AND `total_credits` >= 160;
執行計劃節錄如下：
| type | filtered | Extra |
|---|---|---|
| ref | 33.33 | Using where |
由於科系符合，不代表總學分的條件也符合。因此回表查詢時，需額外比對總學分的欄位值，故 filtered 的值並非 100.00。
四、最左前綴法則
（一）介紹
本文目前已手動建立 3 個索引，整理如下：
| 索引名稱 | 第一欄位 | 第二欄位 |
|---|---|---|
| idx_student_dept | 科系 | - |
| idx_student_dept_credits | 科系 | 總學分 |
| idx_student_dept_status | 科系 | 狀態 |
這些索引的最左邊欄位，也就是第一個欄位，都是「科系」。它是建立索引時被排序的第一順位，所以在 B+ tree 結構中，相同科系的資料，是被放在一起的。
因此只要這些以科系為第一順位聯合索引存在，其實就不需要另一個單一欄位索引了。畢竟若只是要查詢某個科系的學生，條件寫法為 WHERE dept = "會計"，此時不論用哪個索引，效果都一樣。
然而，若查詢條件只有「總學分」一個欄位，條件寫法為 WHERE total_credits >= 160，則現有的聯合索引並幫不上忙。理由是總學分為第二欄位，在 B+ tree 結構中屬於局部有序，站在全局的角度，反而沒有順序可言。
由此可歸納出「最左前綴法則」。假設聯合索引包含「A → B → C」三個欄位的順位，則該索引將自動兼具「A」和「A → B」兩種索引的效果。
然而查詢條件若跳過前面順位的欄位，或者索引欄位使用了範圍條件，則只有前面順位的索引欄位會生效。此時 MySQL 無法從索引中直接定位出符合條件的資料，只能先縮小尋找範圍，再另外比對欄位值。
為了接下來的舉例，我們再建立「科系 → 入學日 → 狀態」組合的聯合索引。
CREATE INDEX `idx_student_dept_admission_status` ON `student`(`dept`, `admission_date`, `status`);
（二）案例一：因跳過欄位而部份失效
以下是查詢資管系的休學學生，使用了聯合索引。
EXPLAIN SELECT * FROM `student` USE INDEX (`idx_student_dept_admission_status`) WHERE `dept` = "資管" AND `status` = "休學";
執行計劃節錄如下：
| type | filtered | Extra |
|---|---|---|
| ref | 14.29 | Using index condition |
將查詢條件與索引欄位做比對，讀者可發現在「科系」之後，跳過了「入學日」，直接以「狀態」作為另一個條件。於是僅科系這個索引欄位會生效，MySQL 只能先找到科系相符的資料。
而執行計畫的 Extra 又寫著「Using index condition」，我們可推論出 MySQL 是先在索引中逐一進行狀態欄位值的比對，才進行回表查詢。此處的 filter 並非 100 說明了這點。
（三）案例二：因範圍條件而部份失效
以下是查詢 2014 年 9 月 15 日「以前」入學，現在已畢業的會計系學生，使用了聯合索引。
EXPLAIN SELECT * FROM `student` USE INDEX (`idx_student_dept_admission_status`) WHERE `dept` = "會計" AND `admission_date` <= "2014-09-15" AND `status` = "畢業";
執行計劃節錄如下：
| type | filtered | Extra |
|---|---|---|
| range | 14.29 | Using index condition |
此處第一欄位「科系」為相等條件，第二欄位「入學日」為範圍條件，因此只有這兩個欄位生效。理由是當第一欄位固定下來，第二欄位才能顯現出有序。在索引中，MySQL 會在相同科系的不同入學日，各自比對狀態欄位的值。
以下將條件改為 2014 年 9 月 15 日「當天」入學，現在已畢業的會計系學生。
EXPLAIN SELECT * FROM `student` USE INDEX (`idx_student_dept_admission_status`) WHERE `dept` = "會計" AND `admission_date` = "2014-09-15" AND `status` = "畢業";
執行計劃節錄如下：
| type | filtered | Extra |
|---|---|---|
| ref | 100.00 | NULL |
第三欄位「狀態」，必須在前兩個順位的欄位是相等條件時，才會生效。MySQL 在索引中不必做額外的欄位比對，即可掌握符合所有條件的資料主鍵，因此 filtered 為 100。
下一篇：【MySQL】索引未生效的原因
留言
張貼留言