https://www.flickr.com/photos/8058098@N07/2718571627/
即便建立了索引，看似能夠增進查詢效率，然而在有些情況 MySQL 仍會選擇全表掃描。本文整理出 4 個會讓索引失效的因素。
一、範例資料介紹
以下是本文的範例資料。
科系表包含編號與名稱，共 2 個欄位。
學生表包含編號、名字、科系編號、總學分與狀態，共 5 個欄位。
二、案例一：查詢條件是欄位的運算結果
如果查詢條件包含索引欄位的運算結果，則索引將不會生效。
以下是針對學生表的名字欄位建立索引。
CREATE INDEX `idx_student_name` ON `student`(`name`);
以下是查詢名字有「v」這個字的學生（不分大小寫）。
EXPLAIN
SELECT *
FROM `student`
WHERE LOWER(`name`) LIKE LOWER("%v%");
執行計劃節錄如下：
| type | key |
|---|---|
| ALL | NULL |
索引底層的樹狀結構，是儲存原始資料的欄位值。因此在查詢條件的 WHERE 子句中對欄位做運算，其結果根本不存在於索引中。於是 MySQL 以全表掃描的方式來進行查詢。
三、案例二：針對欄位做模糊查詢
字串的排序，是從第一個字、第二個字...這樣的順序來比較大小。當模糊查詢並未限定字串的開頭，那麼索引將不會生效。
以下是針對科系表的名稱欄位建立索引。
CREATE INDEX `idx_dept_name` ON `dept`(`name`);
以下是是查詢名稱結尾為「管理」的科系」。
EXPLAIN SELECT * FROM `dept` WHERE `name` LIKE "%管理";
執行計劃節錄如下：
| type | key | filtered | Extra |
|---|---|---|---|
| index | idx_dept_name | 33.33 | Using where; Using index |
此時執行計劃的 type 為 index，代表 MySQL 讀取了整個索引的資料。其實 index 這個量級已經趨近於全表掃描了。
以下改為查詢名稱開頭為「資訊」的科系」。
EXPLAIN SELECT * FROM `dept` WHERE `name` LIKE "資訊%";
執行計劃節錄如下：
| type | key | filtered | Extra |
|---|---|---|---|
| range | idx_dept_name | 100.00 | Using where; Using index |
雖然執行計劃的 type 為 range，但其實是合理的。畢竟字串跟數值一樣都能排序，勢必存在誰大誰小的特性。由於在進行模糊匹配時，已經限定字串的開頭了，那也就意味著是一種範圍條件。
四、案例三：OR 邏輯包含無索引的欄位
在查詢條件中，可使用 OR 關鍵字，將多個條件以「或」的邏輯組合起來。然而若其中一個欄位是沒有索引可用的，那麼其他原本可以利用的索引，也不會生效。
以下是對學生表的科系編號欄位建立索引。
CREATE INDEX `idx_student_dept` ON `student`(`dept_id`);
以下是查詢科系編號為 1，或所有已畢業的學生。
EXPLAIN SELECT * FROM `student` WHERE `dept_id` = 1 OR `status` = "畢業";
執行計劃節錄如下：
| type | possible_keys | key |
|---|---|---|
| ALL | idx_student_dept | NULL |
由於在學生表中，有針對科系編號建立索引，因此 MySQL 覺得可能會用到「idx_student_dept」索引。
然而 status 欄位並無索引，因此 MySQL 會因此進行全表掃描。既然都要掃描整張表的資料了，那科系編號這項條件不如就一併檢查，於是發生索引未被使用的情形。
這時只要針對 status 欄位建立索引，則兩個索引就都會生效了。
CREATE INDEX `idx_student_status` ON `student`(`status`);
執行計劃節錄如下：
| type | key | filtered | Extra |
|---|---|---|---|
| index_merge | idx_student_dept,idx_student_status | 100.00 | Using union(idx_student_dept,idx_student_status); Using where |
從執行計劃可推測出，MySQL 根據 OR 條件，拆分成兩個獨立的查詢指令。分別使用索引來查詢後，再將結果合併起來。
五、案例四：欄位值分佈狀況
即便建立了索引，然而根據查詢欄位的值的分佈狀況，若 MySQL 認為全表掃描的效率比較好，依然不會採用索引。
以下是對學生表的總學分欄位建立索引。
CREATE INDEX `idx_student_credit` ON `student`(`total_credits`);
以下是查詢學分數滿 100 的學生。
EXPLAIN SELECT * FROM `student` WHERE `total_credits` >= 100;
執行計劃節錄如下：
| type | possible_keys | key |
|---|---|---|
| ALL | idx_student_credit | NULL |
在這個例子中，由於符合條件的資料，所佔比例較大，因此 MySQL 評估後，決定以全表掃描的方式進行。而不是先在一般索引中找到主鍵，才回表查詢。
以下改為查詢學分數滿 160 的學生。
EXPLAIN SELECT * FROM `student` WHERE `total_credits` >= 160;
執行計劃節錄如下：
| type | possible_keys | key |
|---|---|---|
| range | idx_student_credit | idx_student_credit |
留言
張貼留言