有些時候雖然有索引，但是不被優化器選擇使用，下面是開發過程中遇到的不能使用索引的幾種情況：
1.以%開頭的like查詢不能夠利用B-tree索引，執行計劃中key的值為NULL表示沒有使用索引。
mysql> explain select * from actor where last_name like `%NI%`;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 200 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.42 sec)資料庫中InnoDB引擎預設使用B-Tree索引，再B-Tree索引結構中，以%開頭的查詢自然就法利用索引了。
一般都推薦使用全文索引（Full-text)來解決類似的全文檢索問題。（這裡沒有給出全文索引的解決方案，可以自己查一下）
除此之外大家還可以利用InnoDB的表都是聚族表的特點，採取一種輕量級別的解決方式：
一般情況下，索引都會比表小，掃描索引比掃描表更快（特殊情況下，索引會比表更大）。
在InnoDB表上二級索引idx_last_name實際上儲存欄位last_name和主鍵actor_id,那麼理想的訪問方式應該是先掃描二級索引idx_last_name獲得滿足條件 last_name like `%NI%`的主鍵actor_id列表，之後根據主鍵回表去檢索記錄，這樣就避免了全表掃描演員表actor產生的大量IO請求。
mysql> explain select * from (select actor_id from actor where last_name like `%NI%`) a,actor b where a.actor_id = b.actor_id;
+----+-------------+-------+------------+--------+---------------+---------------------+---------+-----------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------------------+---------+-----------------------+------+----------+--------------------------+
| 1 | SIMPLE | actor | NULL | index | PRIMARY | idx_actor_last_name | 137 | NULL | 200 | 11.11 | Using where; Using index |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.actor.actor_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------------------+---------+-----------------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)通過執行計劃中可以看到，內層查詢的Using index 代表索引覆蓋掃描，之後通過主鍵join操作去演員表actor中獲取最終查詢結果，理論上比直接掃描全表掃描更快一些。
2.資料型別出現隱式轉換的時候也不會使用索引
特別是在當列型別是字串時，那麼一定記得在where條件中把字元常量的值用引號引起來，否則即便這個列上有索引，MYSQL也不會使用，因為MYSQL預設把常量值進行轉換後才進行檢索。
例如：演員表actor中姓氏欄位last_name是字元型的，但是SQL語句的條件值1是一個數值型別，因此即便存在索引idx_last_name,MYSQL也不能正確使用索引，而是進行全表掃描。
mysql> explain select * from actor where last_name =1;
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | actor | NULL | ALL | idx_actor_last_name | NULL | NULL | NULL | 200 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.08 sec)加上引號之後，再執行一次，就發先使用上索引了。
mysql> explain select * from actor where last_name =`1`;
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | actor | NULL | ref | idx_actor_last_name | idx_actor_last_name | 137 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.10 sec)3.複合索引查詢條件不符合最左匹配原則，是不會使用複合索引的。
最左匹配原則是要滿足複合索引最左邊的欄位，如果查詢條件不是第一個複合索引的第一個欄位，則不符合最左匹配原則。
mysql> explain select * from payment where amount =3.98 and last_update =`2006-02-15 22:12:32`;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | payment | NULL | ALL | NULL | NULL | NULL | NULL | 16086 | 1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.04 sec)4.如果MySQL估計使用索引比全表掃描慢，則不使用索引。
例如:查詢以“S”開頭的標題的電影，需要返回的記錄比例較大，MySQL就預估索引掃描還不如全表掃描更快：
mysql> update film_text set title =concat(`s`,title);
Query OK, 1000 rows affected (3.47 sec)
Rows matched: 1000 Changed: 1000 Warnings: 0
mysql> explain select * from film_text where title like `s%`;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film_text | NULL | ALL | idx_title_description | NULL | NULL | NULL | 1000 | 11.11 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
在MySQL 5.6版本中，能夠通過Trace 清晰地看到優化器的選擇過程，對用時少的進行選擇。
**5.用or分割開的條件，如果or前的條件中有列索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。**
mysql> explain select * from payment where customer_id =203 or amount =3.96;
+----+-------------+---------+------------+------+--------------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | payment | NULL | ALL | idx_fk_customer_id | NULL | NULL | NULL | 16086 | 10.15 | Using where |
+----+-------------+---------+------------+------+--------------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)因為or後面的條件列中沒有索引，那麼後面的查詢肯定要走全表掃描，在存在全表掃描的情況下，就沒有必要多做一次索引掃描增加I/O訪問，一次全表掃過濾條件就足夠了。
當or前後兩個條件都有索引時，是會用到索引的。
mysql> explain select * from payment where customer_id= 203 or amount =3.96;
+----+-------------+---------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+
| 1 | SIMPLE | payment | NULL | index_merge | idx_fk_customer_id,idx_amount | idx_fk_customer_id,idx_amount | 2,3 | NULL | 21 | 100.00 | Using union(idx_fk_customer_id,idx_amount); Using where |
+----+-------------+---------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+
1 row in set, 1 warning (0.05 sec)索引的總結：如果在查詢條件中，有欄位進行全表掃描，那麼索引也就不會被使用。條件中使用複合索引時，要遵守複合索引的使用規則，不然索引也不會被使用。