工作中遇见一次两表联查时效率特别慢的情况,查了半天才找到真正的原因——关联字段的字符编码集不一致!!!一开始查的时候有看过联表的字段类型是否一致,却忽略了编码集的问题。
两表数据量都接近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 |