MySQL索引失效的典型案例

典型案例

有两张表，表结构如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE  TABLE  `student_info` (    `id`  int (11)  NOT  NULL ,    ` name `  varchar (10)  DEFAULT  NULL ,    PRIMARY  KEY  (`id`),    KEY  `idx_name` (` name `) ) ENGINE=InnoDB  DEFAULT  CHARSET=utf8mb4   CREATE  TABLE  `student_score` (    `id`  int (11)  NOT  NULL ,    ` name `  varchar (10)  DEFAULT  NULL ,    `score`  int (11)  DEFAULT  NULL ,    PRIMARY  KEY  (`id`),    KEY  `idx_name` (` name `) ) ENGINE=InnoDB  DEFAULT  CHARSET=utf8

其中一张是info表，一张是score表，其中score表比info表多了一列score字段。

插入数据：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

mysql>  insert  into  student_info  values  (1, 'zhangsan' ),(2, 'lisi' ),(3, 'wangwu' ),(4, 'zhaoliu' ); Query OK, 4  rows  affected (0.01 sec) Records: 4  Duplicates: 0  Warnings: 0   mysql>  insert  into  student_score  values  (1, 'zhangsan' ,60),(2, 'lisi' ,70),(3, 'wangwu' ,80),(4, 'zhaoliu' ,90); Query OK, 4  rows  affected (0.01 sec) Records: 4  Duplicates: 0  Warnings: 0   mysql>  select  *  from  student_info; + ----+----------+ | id |  name      | + ----+----------+ |  2 | lisi     | |  3 | wangwu   | |  1 | zhangsan | |  4 | zhaoliu  | + ----+----------+ 4  rows  in  set  (0.00 sec)   mysql>  select  *  from  student_score ; + ----+----------+-------+ | id |  name      | score | + ----+----------+-------+ |  1 | zhangsan |    60 | |  2 | lisi     |    70 | |  3 | wangwu   |    80 | |  4 | zhaoliu  |    90 | + ----+----------+-------+ 4  rows  in  set  (0.00 sec)

当我们进行下面的语句时：

?

1 2 3 4 5 6 7 8 9 10 11

mysql> explain  select  B.*          from         student_info A,student_score B          where  A. name =B. name  and  A.id=1; + ----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+ | id | select_type |  table  | partitions | type  | possible_keys    |  key      | key_len | ref   |  rows  | filtered | Extra       | + ----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+ |  1 | SIMPLE      | A     |  NULL        | const |  PRIMARY ,idx_name |  PRIMARY  | 4       | const |    1 |   100.00 |  NULL         | |  1 | SIMPLE      | B     |  NULL        |  ALL    |  NULL              |  NULL     |  NULL     |  NULL   |    4 |   100.00 | Using  where  | + ----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+ 2  rows  in  set , 1 warning (0.00 sec)

为什么B.name上有索引，但是执行计划里面第二个select表B的时候，没有使用索引，而用的全表扫描？？？

解析：

该SQL会执行三个步骤：

1、先过滤A.id=1的记录，使用主键索引，只扫描1行LA

2、从LA这一行中找到name的值“zhangsan”，

3、根据LA.name的值在表B中进行查找，找到相同的值zhangsan，并返回。

其中，第三步可以简化为：

select * from student_score  where name=$LA.name

这里，因为LA是A表info中的内容，而info表的字符集是utf8mb4，而B表score表的字符集是utf8。

所以

在执行的时候相当于用一个utf8类型的左值和一个utf8mb4的右值进行比较，因为utf8mb4完全包含utf8类型(长字节包含短字节)，MySQL会将utf8转换成utf8mb4(不反向转换，主要是为了防止数据截断).

因此，相当于执行了：

?

1	select  *  from  student_score   where  CONVERT ( name  USING utf8mb4)=$LA. name

而我们知道，当索引字段一旦使用了隐式类型转换，那么索引就失效了，MySQL优化器将会使用全表扫描的方式来执行这个SQL。

要解决这个问题，可以有以下两种方法：

a、修改字符集。

b、修改SQL语句。

给出修改字符集的方法：

?

1 2 3 4 5 6 7 8 9 10 11 12

mysql>  alter  table  student_score  modify  name  varchar (10)   character  set  utf8mb4 ; Query OK, 4  rows  affected (0.03 sec) Records: 4  Duplicates: 0  Warnings: 0   mysql> explain  select  B.*  from  student_info A,student_score B  where  A. name =B. name  and  A.id=1; + ----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+ | id | select_type |  table  | partitions | type  | possible_keys    |  key       | key_len | ref   |  rows  | filtered | Extra | + ----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+ |  1 | SIMPLE      | A     |  NULL        | const |  PRIMARY ,idx_name |  PRIMARY   | 4       | const |    1 |   100.00 |  NULL   | |  1 | SIMPLE      | B     |  NULL        | ref   | idx_name         | idx_name | 43      | const |    1 |   100.00 |  NULL   | + ----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+ 2  rows  in  set , 1 warning (0.01 sec)

修改SQL的方法，大家可以自己尝试。

附：常见索引失效的情况

一、对列使用函数，该列的索引将不起作用。

二、对列进行运算(+，-，*，/，! 等)，该列的索引将不起作用。

三、某些情况下的LIKE操作，该列的索引将不起作用。

四、某些情况使用反向操作，该列的索引将不起作用。

五、在WHERE中使用OR时，有一个列没有索引，那么其它列的索引将不起作用。

六、隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误。

七、使用not in ,not exist等语句时。

八、当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。

九、当B-tree索引 is null不会失效,使用is not null时,会失效,位图索引 is null,is not null 都会失效。

十、联合索引 is not null 只要在建立的索引列（不分先后）都会失效。

以上就是MySQL索引失效的典型案例的详细内容，更多关于MySQL索引失效的资料请关注服务器之家其它相关文章！

原文链接：https://mp.weixin.qq.com/s/VtsS0vPmqjPNaJhy8IPYmQ