//
MySQL索引失效了？可能你遇到了这种情况
//
这两天有一个公众号观众问起来这篇文章中的一个问题，突然发现这个案例挺经典的，这里发出来再回顾一下。
有两张表，表结构如下：
代码语言：javascript复制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字段。
插入数据：
代码语言：javascript复制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)当我们进行下面的语句时：
代码语言：javascript复制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(不反向转换，主要是为了防止数据截断).
因此，相当于执行了：
代码语言：javascript复制select * from student_score where CONVERT(name USING utf8mb4)=$LA.name
而我们知道，当索引字段一旦使用了隐式类型转换，那么索引就失效了，MySQL优化器将会使用全表扫描的方式来执行这个SQL。
要解决这个问题，可以有以下两种方法：
a、修改字符集。
b、修改SQL语句。
给出修改字符集的方法：
代码语言：javascript复制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的方法，大家可以自己尝试。
时间原因，就这么多吧。
