MySQL索引失效的典型案例(索引失效)

典型案例

有两张表，表结构如下：

CREATETABLE`student_info`(
`id`int(11)NOTNULL,
`name`varchar(10)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4
CREATETABLE`student_score`(
`id`int(11)NOTNULL,
`name`varchar(10)DEFAULTNULL,
`score`int(11)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`idx_name`(`name`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8

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

插入数据：

mysql>insertintostudent_infovalues(1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(4,'zhaoliu');
QueryOK,4rowsaffected(0.01sec)
Records:4Duplicates:0Warnings:0
mysql>insertintostudent_scorevalues(1,'zhangsan',60),(2,'lisi',70),(3,'wangwu',80),(4,'zhaoliu',90);
QueryOK,4rowsaffected(0.01sec)
Records:4Duplicates:0Warnings:0
mysql>select*fromstudent_info;
+----+----------+
|id|name|
+----+----------+
|2|lisi|
|3|wangwu|
|1|zhangsan|
|4|zhaoliu|
+----+----------+
4rowsinset(0.00sec)
mysql>select*fromstudent_score;
+----+----------+-------+
|id|name|score|
+----+----------+-------+
|1|zhangsan|60|
|2|lisi|70|
|3|wangwu|80|
|4|zhaoliu|90|
+----+----------+-------+
4rowsinset(0.00sec)

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

mysql>explainselectB.*
from
student_infoA,student_scoreB
whereA.name=B.nameandA.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|Usingwhere|
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+
2rowsinset,1warning(0.00sec)

为什么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(不反向转换，主要是为了防止数据截断).

因此，相当于执行了：

select*fromstudent_scorewhereCONVERT(nameUSINGutf8mb4)=$LA.name

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

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

a、修改字符集。

b、修改SQL语句。

给出修改字符集的方法：

mysql>altertablestudent_scoremodifynamevarchar(10)charactersetutf8mb4;
QueryOK,4rowsaffected(0.03sec)
Records:4Duplicates:0Warnings:0
mysql>explainselectB.*fromstudent_infoA,student_scoreBwhereA.name=B.nameandA.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|
+----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+
2rowsinset,1warning(0.01sec)

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