MySQL 索引失效是一個相當普遍的問題，尤其在處理慢查詢時特別需要注意是否存在索引失效的情況。
排查索引失效問題的第一步，必須定位要分析的 SQL 語句，然後透過EXPLAIN來檢視其執行計劃。主要關注type、key和extra這幾個欄位。
我們需根據 key、type 和 extra 判斷 SQL 查詢是否利用了索引。若是，是否為覆蓋索引、索引下推、整體索引掃描，或是索引跳躍掃描等情況。
通常，最佳化的索引使用情況包括以下幾種：
首先，key 欄位必須有值，不得為 NULL。
其次，type 應該是 ref、eq_ref、range、const 等幾種型別。
此外，extra 欄位如果為 NULL 或者包含"using index"、"using index condition"都是可以接受的情況。
如果執行計劃顯示一條 SQL 語句沒有有效利用索引，例如 type = ALL，key = NULL，extra = Using where。
那麼就需要進一步分析未能有效利用索引的原因。需要了解的是，是否需要使用索引以及應該使用哪個索引，這是由 MySQL 的最佳化器決定的，它會根據成本估算做出決策。
以下是導致未能有效利用索引的幾種可能情況：
索引未正確建立：當查詢語句中的 where 條件涉及的欄位未建立索引，或者索引未滿足最左字首匹配條件時，就未能正確建立索引。
索引區分度不足：如果索引的區分度不高，可能導致未使用索引，因為在這種情況下，利用索引並不能有效提升查詢效率。
表過小：當表中的資料量很少時，最佳化器可能認為全表掃描的成本不高，因此可能選擇不使用索引。
查詢語句中使用了函式或欄位型別不匹配等情況導致索引失效。
這時候我們需要從頭開始逐一分析：
如果索引未正確建立，根據 SQL 語句建立適當的索引。如果未遵守最左字首原則，調整索引或修改 SQL 語句。
若索引區分度不高，考慮選擇另一個更合適的索引欄位。
對於表過小的情況，最佳化影響可能不大，因此是否使用索引可以不做過多最佳化。
排查具體的失效原因，然後有針對性地調整 SQL 語句。
可能導致索引失效的情況
假設我們有一張表（以下 SQL 實驗基於 MySQL 5.7）：
CREATE TABLE `mytable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `age` int(11) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `age` (`age`), KEY `create_time` (`create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into mytable(id,name,age,create_time) values (1,"paidaxing",20,now()); insert into mytable(id,name,age,create_time) values (2,"paidaxing1",21,now()); insert into mytable(id,name,age,create_time) values (3,"paidaxing2",22,now()); insert into mytable(id,name,age,create_time) values (4,"paidaxing3",20,now()); insert into mytable(id,name,age,create_time) values (5,"paidaxing4",14,now()); insert into mytable(id,name,age,create_time) values (6,"paidaxing5",43,now()); insert into mytable(id,name,age,create_time) values (7,"paidaxing6",32,now()); insert into mytable(id,name,age,create_time) values (8,"paidaxing7",12,now()); insert into mytable(id,name,age,create_time) values (9,"paidaxing8",1,now()); insert into mytable(id,name,age,create_time) values (10,"paidaxing9",43,now());
索引列參與計算
select * from mytable where age = 12; +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | mytable | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
以上 SQL 語句是可以利用索引的，但如果在欄位中增加計算操作，就可能導致索引失效：
select * from mytable where age +1 = 12; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
但如果計算的形式如下，仍然可以利用索引：
select * from mytable where age = 12 - 1; +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | mytable | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
對索引列進行函式操作
SELECT * FROM mytable WHERE create_time = '2023-04-01 00:00:00'; +----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | mytable | NULL | ref | create_time | create_time | 6 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
以上 SQL 語句可以利用索引，但如果在欄位中新增函式操作，可能會導致索引失效：
SELECT * FROM mytable WHERE YEAR(create_time) = 2022; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
使用 OR
SELECT * FROM mytable WHERE name = 'paidaxing' and age > 18; +----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | mytable | NULL | const | name,age | name | 202 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
但如果使用 OR，並且 OR 條件中的兩側包含<或者>運算子時，可能會導致索引失效，例如：
SELECT * FROM mytable WHERE name = 'paidaxing' OR age > 18; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | ALL | name,age | NULL | NULL | NULL | 10 | 40.00 | Using where | +-
但如果 OR 條件的兩側都是等號判斷，並且兩個欄位都有索引，仍然可以利用索引，例如：
mysql> explain SELECT * FROM mytable WHERE name = 'paidaxing' OR age = 18; +----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | mytable | NULL | index_merge | name,age | name,age | 202,5 | NULL | 2 | 100.00 | Using union(name,age); Using where | +----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+
like 操作
SELECT * FROM mytable WHERE nick like '%paidaxing%'; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 11.11 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ SELECT * FROM mytable WHERE nick like '%paidaxing'; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 11.11 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ SELECT * FROM mytable WHERE nick like 'paidaxing%'; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | range| nick | nick | NULL | NULL | 10 | 100.00 | Using index condition | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ SELECT * FROM mytable WHERE nick like 'paida%xing'; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | range| nick | nick | NULL | NULL | 10 | 100.00 | Using index condition | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
以上四種 LIKE 模式中，"paidaxing%"和"paida%xing"這兩種可以利用索引，但是如果是"%paidaxing%"和"%paidaxing"就無法利用索引。
隱式型別轉換
select * from mytable where name = 1; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | ALL | name | NULL | NULL | NULL | 10 | 10.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
以上情況中，如果表中的 name 欄位是 varchar 型別，但我們使用 int 型別進行查詢，這會導致索引失效。
然而，有一個特例是，如果欄位型別是 int 型別，而查詢條件中新增了單引號或雙引號，MySQL 會將引數轉換為 int 型別，這種情況下也可以利用索引。
select * from mytable where age = '1'; +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | mytable | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
不等於比較
SELECT * FROM mytable WHERE age != 18; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | ALL | age | NULL | NULL | NULL | 10 | 100.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
但並非所有情況都是如此，例如在以下情況下，使用 ID 進行!=比較時，可能會利用索引：
SELECT * FROM mytable WHERE id != 18; +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 12 | 100.00 | Using where | +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
在使用!=（不等於）運算子時，索引是否失效與多個因素有關，包括索引的選擇性、資料的分佈情況等，並不能簡單地因為使用了!=運算子就說導致了索引失效。
is not null
以下情況是索引失效的：
SELECT * FROM mytable WHERE name is not null +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | mytable | NULL | ALL | name | NULL | NULL | NULL | 10 | 90.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
order by
SELECT * FROM mytable order by age +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
當進行 ORDER BY 操作時，如果資料量非常小，資料庫可能會選擇在記憶體中進行排序，而不是使用索引。
in
使用 IN 操作時，有時會走索引，有時則不會。一般來說，當 IN 子句中的值較少時，資料庫可能會選擇使用索引進行最佳化；但如果 IN 子句中的選項較多，可能就不會使用索引。
mysql> explain select * from mytable where name in ("paidaxing");
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | const | name | name | 202 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
mysql> explain select * from mytable where name in ("paidaxing","pdx");
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | mytable | NULL | range | name | name | 202 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
mysql> explain select * from mytable where name in ("paidaxing","pdx","x");
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | name | NULL | NULL | NULL | 11 | 27.27 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
