mysql字符串子查询或join连接查询索引不生效

 

项目中有个sql语句，大致如下（由于不方便用公司项目代码，以下居临时编造）：

SELECT * FROM `student` WHERE major_code IN (SELECT code FROM `major` where department_code='Computer');

这里major_code字段建立了索引，但用EXPLAIN发现索引并无生效。经过多方查询，最后得知原因是：

两个表的编码不一致，如major表为CHARSET=utf8，student表为CHARSET=utf8mb4。修正编码后，索引生效。

（我大意了，我没有闪）

 

下面记录下解决问题的过程：

表结构如下：

CREATE TABLE `major` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '编码',
  `name` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称',
  `remark` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注',
  `department_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '系编码',
  `department_name` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '系名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT COMMENT='专业表';

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
  `sex` varchar(8) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '性别',
  `no` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '学生号，如20208105',
  `grade` varchar(8) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '年级，如2020',
  `class` varchar(8) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '班级，如81',
  `major_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '专业编码',
  `major_name` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '专业名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT COMMENT='学生表';

一、首先怀疑IN子查询导致索引不生效

先用以下直接写值的方式，执行后是有走索引的，说明建的索引没问题

SELECT * FROM `student` WHERE major_code IN ('GraphicArtistDesigner');

SELECT * FROM `student` WHERE major_code = 'GraphicArtistDesigner';

二、接着改用exist方式进行查询，结果索引并无生效

SELECT * FROM `student` A WHERE EXISTS (SELECT code FROM `major` B where B.code=A.major_code AND B.department_code='Computer');

三、既然不行，换个方式，用join查询总不会有子查询导致索引失效的问题吧。好了，写完后一测，啪啪啪打脸，整个人懵逼了，居然不生效

SELECT A.* FROM `student` A join `major` B on B.code=A.major_code AND B.department_code='Computer';

 

按自己经验，以上sql绝对没问题。但问题确不知出在哪，没办法，上网继续找找其他同志有没碰到类似问题。这次换个关键字搜索“join连接查询不走索引”，没想到峰回路转，看到了这篇博文：

https://blog.csdn.net/everda/article/details/77476716

一查，果然编码不一致（由于不同同事建的表，并建表时指定了编码），直接修改编码一测，问题解决。

感觉这文章的博主，也在此记录下此经历，望对大家有用