典型案例
有两张表，表结构如下：
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字段。
插入数据：
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)
当我们进行下面的语句时：
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(不反向转换，主要是为了防止数据截断).
因此，相当于执行了：
select * from student_score where convert(name using utf8mb4)=$la.name
而我们知道，当索引字段一旦使用了隐式类型转换，那么索引就失效了，mysql优化器将会使用全表扫描的方式来执行这个sql。
要解决这个问题，可以有以下两种方法：
a、修改字符集。
b、修改sql语句。
给出修改字符集的方法：
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索引失效的资料请关注www.887551.com其它相关文章！