在上一篇文章中，通過分析執行計劃的欄位說明，大體說了一下索引優化程序中的一些注意點，那么如何才能避免索引失效呢？本篇文章將來討論這個問題，
避免索引失效的常見方法
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優化的幫助其實是巨大的，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/62722.html
標籤：MySQL
上一篇：MySQL優化之執行計劃
下一篇：mysql常用函式