[[405189]]

本文轉載自微信公眾號「碼上Java」，作者msJava。轉載本文請聯系碼上Java公眾號。

1. 如果索引進行了表達式計算，會失效

我們可以使用EXPLAIN關鍵字來查看 MySQL 中一條 SQL 語句的執行計劃，比如：

EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id+1 = 900001 

運行結果：

+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       | 
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 
|  1 | SIMPLE      | product_comment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996663 |   100.00 | Using where | 
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 

你能看到如果對索引進行了表達式計算，索引就失效了。這是因為我們需要把索引字段的取值都取出來，然后依次進行表達式的計算來進行條件判斷，因此采用的就是全表掃描的方式，運行時間也會慢很多，最終運行時間為 2.538 秒。

為了避免索引失效，我們對 SQL 進行重寫：

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900000 

運行時間為 0.039 秒。

2. 如果對索引使用函數，會失效

比如我們想要對 comment_text 的前三位為 abc 的內容進行條件篩選，這里我們來查看下執行計劃：

EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc' 

運行結果：

+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       | 
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 
|  1 | SIMPLE      | product_comment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996663 |   100.00 | Using where | 
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 

你能看到對索引字段進行函數操作，造成了索引失效，這時可以進行查詢重寫：

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE 'abc%' 

使用 EXPLAIN 對查詢語句進行分析：

+----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 
| id | select_type | table           | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 | 
+----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 
|  1 | SIMPLE      | product_comment | NULL       | range | comment_text  | comment_text | 767     | NULL |  213 |   100.00 | Using index condition | 
+----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 

你能看到經過查詢重寫后，可以使用索引進行范圍檢索，從而提升查詢效率。

3. 在 WHERE 子句中，如果在 OR 前的條件列進行了索引，而在 OR 后的條件列沒有進行索引，會失效。

比如下面的 SQL 語句，comment_id 是主鍵，而 comment_text 沒有進行索引，因為 OR 的含義就是兩個只要滿足一個即可，因此只有一個條件列進行了索引是沒有意義的，只要有條件列沒有進行索引，就會進行全表掃描，因此索引的條件列也會失效：

EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900001 OR comment_text = '462eed7ac6e791292a79' 

運行結果：

+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       | 
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 
|  1 | SIMPLE      | product_comment | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 996663 |    10.00 | Using where | 
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 

如果我們把 comment_text 創建了索引會是怎樣的呢?

+----+-------------+-----------------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+ 
| id | select_type | table           | partitions | type        | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                                          | 
+----+-------------+-----------------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+ 
|  1 | SIMPLE      | product_comment | NULL       | index_merge | PRIMARY,comment_text | PRIMARY,comment_text | 4,767   | NULL |    2 |   100.00 | Using union(PRIMARY,comment_text); Using where | 
+----+-------------+-----------------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+ 

你能看到這里使用到了 index merge，簡單來說 index merge 就是對 comment_id 和 comment_text 分別進行了掃描，然后將這兩個結果集進行了合并。這樣做的好處就是避免了全表掃描。

4. 當我們使用 LIKE 進行模糊查詢的時候，后面不能是 %，會失效。

EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE '%abc' 

運行結果：

+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       | 
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 
|  1 | SIMPLE      | product_comment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996663 |    11.11 | Using where | 
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 

這個很好理解，如果一本字典按照字母順序進行排序，我們會從首位開始進行匹配，而不會對中間位置進行匹配，否則索引就失效了。

5. 索引列與 NULL 或者 NOT NULL 進行判斷的時候會失效。

這是因為索引并不存儲空值，所以最好在設計數據表的時候就將字段設置為 NOT NULL 約束，比如你可以將 INT 類型的字段，默認值設置為 0。將字符類型的默認值設置為空字符串 (’’)。

總結

除了以上情況索引會失效，我們在使用聯合索引的時候要注意最左原則。 

最左原則也就是需要從左到右的使用索引中的字段，一條 SQL 語句可以只使用聯合索引的一部分，但是需要從最左側開始，否則也會失效。