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

database

在上一篇文章中,通过分析执行计划的字段说明,大体说了一下索引优化过程中的一些注意点,那么如何才能避免索引失效呢?本篇文章将来讨论这个问题。

避免索引失效的常见方法

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优化之避免索引失效的方法 的全部内容, 来源链接: utcz.com/z/532915.html

回到顶部