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