MySQL优化之避免索引失效的方法
在上一篇文章中，通过分析执行计划的字段说明，大体说了一下索引优化过程中的一些注意点，那么如何才能避免索引失效呢？本篇文章将来讨论这个问题。
避免索引失效的常见方法
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优化的帮助其实是巨大的。
