【MySQL】索引未生效的 4 個原因（函數運算、模糊查詢、OR 邏輯、資料分佈）

https://www.flickr.com/photos/8058098@N07/2718571627/

即便建立了索引，看似能夠增進查詢效率，然而在有些情況 MySQL 仍會選擇全表掃描。本文整理出 4 個會讓索引失效的因素。

一、範例資料介紹

以下是本文的範例資料。

科系表包含編號與名稱，共 2 個欄位。

	CREATE TABLE `dept`(
	`id` INT PRIMARY KEY,
	`name` VARCHAR(10)
	);
	INSERT INTO `dept`
	VALUES (1, "會計"), (2, "資管"), (3, "企管");

view raw create-and-insert-dept.sql hosted with ❤ by GitHub

學生表包含編號、名字、科系編號、總學分與狀態，共 5 個欄位。

	CREATE TABLE `student`(
	`id` INT PRIMARY KEY,
	`name` VARCHAR(10),
	`dept_id` INT,
	`total_credits` INT,
	`status` VARCHAR(10)
	);
	INSERT INTO `student`
	VALUES
	(1, "Vincent", 3, 162, "畢業"), (2, "Dora", 1, 170, "畢業"),
	(3, "Mario", 2, 154, "在學"), (4, "Winnie", 1, 167, "畢業"),
	(5, "Anna", 2, 158, "畢業"), (6, "David", 1, 65, "在學"),
	(7, "Linda", 3, 122, "休學"), (8, "Vicky", 3, 162, "畢業"),
	(9, "Susan", 1, 170, "畢業"), (10, "Luigi", 2, 154, "在學"),
	(11, "Mark", 1, 167, "畢業"), (12, "Andy", 2, 158, "畢業"),
	(13, "Peter", 1, 65, "在學"), (14, "Oreo", 3, 122, "休學"),
	(15, "Cathy", 3, 162, "畢業"), (16, "Gina", 1, 170, "畢業"),
	(17, "Mary", 2, 154, "在學"), (18, "Jimmy", 1, 167, "畢業"),
	(19, "Willam", 2, 158, "畢業"), (20, "Rowling", 1, 65, "在學"),
	(21, "Leo", 3, 122, "休學");

view raw create-and-insert-student.sql hosted with ❤ by GitHub

二、案例一：查詢條件是欄位的運算結果

如果查詢條件包含索引欄位的運算結果，則索引將不會生效。

以下是針對學生表的名字欄位建立索引。

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

上一篇：【MySQL】聯合索引與最左前綴法則