在上一篇文章中，通過分析執行計劃的欄位說明，大體說了一下索引優化過程中的一些註意點，那麼如何才能避免索引失效呢？本篇文章將來討論這個問題。避免索引失效的常見方法 1.對於複合索引的使用，應按照索引建立的順序使用，儘量不要跨列（最佳左首碼原則）為了說明問題，我們仍然使用上一篇文章中的test01表 ...

在上一篇文章中，通過分析執行計劃的欄位說明，大體說了一下索引優化過程中的一些註意點，那麼如何才能避免索引失效呢？本篇文章將來討論這個問題。

避免索引失效的常見方法

1.對於複合索引的使用，應按照索引建立的順序使用，儘量不要跨列（最佳左首碼原則）

為了說明問題，我們仍然使用上一篇文章中的test01表，其表結構如下所示：

mysql> desc test01;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(4)      | YES  | MUL | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| passwd | char(20)    | YES  |     | NULL    |       |
| inf    | char(50)    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> show index from test01;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
 Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
---------+---------------+
| test01 |          1 | t_idx1   |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |
         |               |
| test01 |          1 | t_idx1   |            2 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |
         |               |
| test01 |          1 | t_idx1   |            3 | passwd      | A         |           0 |     NULL | NULL   | YES  | BTREE      |
         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
---------+---------------+
3 rows in set (0.01 sec)

如果常規的SQL寫法，三個索引全覆蓋，沒有任何問題：

mysql> explain select * from test01 where id = 1 and name = 'zz' and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 129     | const,const,const |    1 |   100.00 | NULL
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
1 row in set, 1 warning (0.00 sec)

但是如果跨列使用，如下所示：

mysql> explain select * from test01 where id = 1 and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
    |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using index condit
ion |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
1 row in set, 1 warning (0.00 sec)

通過觀察，發現key_len已經從129變成5了，說明只有id使用到了索引，而passwd並沒有用到索引。
接下來我們看一種更糟糕的情況：

mysql> explain select * from test01 where id = 1 order by passwd;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
--------------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
                    |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
--------------------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using index condit
ion; Using filesort |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
--------------------+
1 row in set, 1 warning (0.00 sec)

上述語句中，Extra欄位中出現了Using filesort，之前說過，這是非常差的一種寫法，如果我們做一下改動：

mysql> explain select * from test01 where id = 1 order by name,passwd;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
    |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using index condit
ion |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
1 row in set, 1 warning (0.00 sec)

與上面的SQL相比較，只是在order by 里，加上了name，Using filesort就去掉了，所以這裡的不能跨列，指的是where 和order by之間不能跨列，否則會出現很糟糕的情況。

2.不要在索引上進行任何函數操作

包括但不限於sum、trim、甚至對欄位進行加減乘除計算。

mysql> explain select id from test01 where id = 1 and trim(name) ='zz' and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

因為在name欄位上，進行了trim函數操作，所以name失效，連帶著後面的passwd也失效了，因為key_len = 5。
所以此處透露出兩個信息點：

在索引欄位上進行函數操作，會導致索引失效；
複合索引如果前面的欄位失效，其後面的所有欄位索引都會失效。

3.複合索引不要使用is null或is not null，否則其自身和其後面的索引全部失效。

仍然是看一個例子：

mysql> explain select id from test01 where id = 1 and name is not null and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

因為name使用了is not null，所以導致name和passwd都失效了，從key_len = 5可以看出。

4.like儘量不要在前面加%

這一點之前在說明range級別的時候有提到過，此處再次說明一下。

mysql> explain select * from test01 where id = 1 and name like '%a%' and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
    |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | Using index condit
ion |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
----+
1 row in set, 1 warning (0.00 sec)

在上例中，name欄位使用了like '%a%'，所以導致name和passwd都失效，只有id使用到了索引。
為了對比，如果把前面的%去掉，看看什麼結果：

mysql> explain select * from test01 where id = 1 and name like 'a%' and passwd = '123';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
----+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra
    |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
----+
|  1 | SIMPLE      | test01 | NULL       | range | t_idx1        | t_idx1 | 129     | NULL |    1 |   100.00 | Using index condit
ion |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
----+
1 row in set, 1 warning (0.00 sec)

可以看到，此時key_len = 129，說明三個欄位都用到了。

5.儘量不要使用類型轉換，包括顯式的和隱式的

正常的索引應該是這樣：

mysql> explain select * from test01 where id = 1 and name = 'zz' and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 129     | const,const,const |    1 |   100.00 | NULL
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------
+
1 row in set, 1 warning (0.00 sec)

但是如果改一下，把passwd = '123'改成passwd = 123，讓MySQL自己去做類型轉換，將123轉換成'123'，那結果是怎樣的呢？

mysql> explain select * from test01 where id = 1 and name = 'zz' and passwd = 123;
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
----------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref         | rows | filtered | Extra
          |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
----------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 68      | const,const |    1 |   100.00 | Using index
condition |
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
----------+
1 row in set, 2 warnings (0.00 sec)

發現key_len = 68，最後一個passwd失效了。

6.儘量不要使用or

or會使or前面的和後面的索引同時失效，這點比較變態，所以要特別註意：

mysql> explain select * from test01 where id = 1 or name = 'zz';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test01 | NULL       | ALL  | t_idx1        | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到因為使用了or，導致索引為NULL，type級別為ALL。

如果一定要使用or，應該怎樣補救呢？
補救的辦法是儘量用到索引覆蓋。比如我們把原來SQL中的select * 中的 * 號替換成具體的欄位，這些欄位能夠覆蓋索引，那麼對索引優化也有一定的提升：

mysql> explain select id, name, passwd from test01 where id = 1 or name = 'zz';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra
       |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | index | t_idx1        | t_idx1 | 129     | NULL |    1 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

從上例中可以看到，where條件沒做任何改變，但是type級別已經提升到了index，也是用到了索引。

7.in經常會使索引失效，應該慎用

mysql> explain select id, name, passwd from test01 where id = 1 and name in ('zz', 'aa');
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra
       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | ref  | t_idx1        | t_idx1 | 5       | const |    2 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

從上例中，不難看出，key_len = 5，所以name索引失效了，原因就是name使用了in。
為什麼說經常會使索引失效呢？因為in也不一定總使索引失效，如下麵的例子：

mysql> explain select id, name, passwd from test01 where id in (1,2,3) and name = 'zz';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra
       |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
|  1 | SIMPLE      | test01 | NULL       | index | t_idx1        | t_idx1 | 129     | NULL |    1 |   100.00 | Using where; Using
 index |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

可以看到此時key_len = 129，說明用到了全部索引。以上情況之所以出現，其實還是索引失效了，但是雖然id索引失效，但是name索引並沒有失效，所以上面的句子等價於：select id, name, passwd from test01 where name = 'zz';。這與之前說的複合索引只要前面的失效，後面都失效並不太一致，所以對於in，應該謹慎使用。

對於關聯表查詢的情況，應該遵循“小表驅動大表”的原則

總而言之，就是左連接給左表建索引，右連接給右表建索引，內連接的話給數據量小的表建索引。

還需要說明一點的是，索引並不是越多越好

因為索引的數據結構是B樹，畢竟要占記憶體空間，所以如果索引越多，索引越大，對硬碟空間的消耗其實是巨大的，而且如果表結構需要調整，意味著索引也要同步做調整，否則會導致不可預計的問題出現。
因此，在實際開發中，對於創建索引，應充分考慮到具體的業務情況，根據業務實現來創建索引，對於有些比較特殊的複雜SQL，建議在代碼里進行一定的邏輯處理後再進行常規的索引查詢。
舉個例子，比如test01表中，需要判斷inf欄位是否包含“上海”欄位，如果在SQL里實現，則必然是如下的邏輯：

select id,name,passwd,inf from test01 where id = 1 and name = 'zz' and passwd = '123' and inf like '%上海%'

這條sql就比較恐怖了，且不說inf本來不是索引，而且有like '%上海%'這種糟糕的寫法，所以我們完全可以使用下麵的方法代替。
先使用下麵的SQL查出所有欄位：

select id,name,passwd,inf from test01 where id = 1 and name = 'zz' and passwd = '123'

然後在代碼里判斷inf欄位是否包含上海欄位，如C語言實現如下：

if (strstr(inf, "上海") != NULL)
{
	//do something
}

這樣一來，雖然只是多了一步簡單的邏輯判斷，但是對於SQL優化的幫助其實是巨大的。

MySQL優化之避免索引失效的方法