记录一次MySQL联表查询优化过程

工作中遇见一次两表联查时效率特别慢的情况，查了半天才找到真正的原因——关联字段的字符编码集不一致！！！一开始查的时候有看过联表的字段类型是否一致，却忽略了编码集的问题。

两表数据量都接近100w，关联字段均已创建索引，查询时要花费几秒以上的时间，而单表查询时花费不过几十毫秒。通过explain看到 第二条的extra为

Using where; Using index; Using join buffer (Block Nested Loop) ，问题就出在这个Using join buffer (Block Nested Loop)中，正常关联字段都建立了索引的话extra应该是Using index

表结构

CREATE TABLE `employee` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `employee_no` varchar(20) DEFAULT NULL COMMENT '员工编号',
  `employee_name` varchar(20) DEFAULT NULL COMMENT '员工名字',
  `mobile` varchar(16) DEFAULT NULL COMMENT '手机号',
  `position` varchar(50) DEFAULT NULL COMMENT '职务信息',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别 1表示男性，2表示女性',
  `email` varchar(255) DEFAULT NULL COMMENT '邮箱',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `is_delete` tinyint(4) DEFAULT NULL COMMENT '是否删除 : 0=否，1=是',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_employee_no` (`employee_no`) USING BTREE,
  KEY `idx_employee_name` (`employee_name`) USING BTREE,
  KEY `idx_mobile` (`mobile`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

CREATE TABLE `employee_dept_relation` (
  `id` bigint(21) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `employee_no` varchar(20) DEFAULT NULL COMMENT '员工编号',
  `dept_code` varchar(20) DEFAULT NULL COMMENT '员工所属机构编号',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_dept_code` (`dept_code`) USING BTREE,
  KEY `idx_employee_no` (`employee_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工和部门关系表' ;

查询语句

select a.employee_no,a.employee_name,a.mobile,a.sex,b.dept_code from employee a, employee_dept_relation b where a.employee_no=b.employee_no and a.is_delete=0 and a.employee_name = "员工A"

表中员工A同名的有13个

explain 计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a		ref	uk_employee_no,idx_employee_name	idx_employee_name	259	const	13	10	Using where
1	SIMPLE	b		index		idx_employee_no	95		977217	100	Using where; Using index; Using join buffer (Block Nested Loop)

 可以看出这里驱动表为a，并且a的type为ref，说明走了二级索引，没有什么问题，但是b表type类型为index，说明进行了全索引扫描，仅次于全部扫描了，而且Extra中 Using join buffer (Block Nested Loop) ，说明联表的索引没有生效，参考这个篇文章《join表连接的三种算法思想》。

 于是开始查找索引无效的原因，在网上找了很久排除了索引列含有null值，索引列类型不一致，最终查看了一下字符集才发现两边一个表是utf8一个是utf8mb4，之前之所以没有注意到是因为在DDL语句中字符集的设置是在表上，没有对字段设置。这两个表中employee表是从远端拉取的，拉取过程中发现表中有多个字段带有表情符号，所以同事处理的时候直接在表上设置了字符集。另外一张表，是本地其他途径同步的，默认是库的编码，这就造成了两边字符不一致。

 将a表的empoyee_no字符集和排序规则改为和b表一致之后，再次执行，发现只需要0.063s即可。

更改字符集后explain计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a		ref	uk_employee_no,idx_employee_name	idx_employee_name	259	const	13	10	Using where
1	SIMPLE	b		ref	idx_employee_no	idx_employee_no	62	a.employee_no	1	100	Using index condition

 可以看到两张表的type都是ref，走了索引。速度大大提升，到这里基本满足需求了，其实还可以继续优化，比如对employee_dept_relation表的employee_no和dept_code建联合索引 ，再次执行只需要0.021s。

建立联合索引后 explain计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a		ref	uk_employee_no,idx_employee_name	idx_employee_name	259	const	13	10	Using where
1	SIMPLE	b		ref	idx_employee_no	idx_employee_no	62	a.employee_no	1	100	Using where; Using index