记录解决MySQL LEFT JOIN 查询慢

发表于 更新于 分类于

环境

  • Linux CentOS7 操作系统
  • Mysql5.7

背景

在和前端开发人员过页面的时候，发现其中一个页面响应很慢，定位原因是API接口响应要24s左右，这响应速度太慢了。排查后端代码发现涉及到的查询SQL语句是一个简单的左连接查询，按之前的经验不能这么慢呢，所以在SQL IDE中排查。

先说一下涉及的表，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 书架表,数量量：4条
create table cdlm_bookshelf
(
    bookshelf_id         varchar(32) not null comment '书架唯一标识',
    user_id              varchar(32) not null comment '用户唯一标识',
    resource_id          varchar(32) not null comment '资源ID',
    resource_type        varchar(16) not null comment '资源类型',
    resource_name        varchar(512) not null comment '资源名称',
    cover_id             varchar(32) comment '资源封面,附件表id',
    read_position        varchar(128) comment '阅读位置',
    read_percent         int comment '阅读百分比(0-100)',
    primary key (bookshelf_id)
);
-- 附件表，数据量：5136453条
CREATE TABLE t_attachment_info (
  attachment_id varchar(40) NOT NULL COMMENT '附件id',
  attachment_name varchar(2000) DEFAULT NULL COMMENT '附件名称',
  attachment_type varchar(200) DEFAULT NULL COMMENT '附件类型',
  attachment_size double DEFAULT NULL COMMENT '附件大小',
  attachment_path varchar(200) DEFAULT NULL COMMENT '附件路径(相对)'
  PRIMARY KEY (attachment_id)
);

查询的语句：

1
2
3
4
5
SELECT b.*, a.attachment_path AS cover_path
FROM cdlm_bookshelf_test b
LEFT JOIN t_attachment_info a
ON b.cover_id = a.attachment_id
WHERE b.user_id='7f52280cbdd04e29ae899884a4a2193a';

查询耗时，10.3秒左右：

1
2
3
4
5
6
7
8
9
10
1 queries executed, 1 success, 0 errors, 0 warnings

查询：SELECT b.*, a.attachment_path AS cover_path FROM cdlm_bookshelf_test b left JOIN t_attachment_info a ON b.cover_id = a.attachmen...

共 5 行受到影响

执行耗时   : 10.318 sec
传送时间   : 0 sec
总耗时      : 10.318 sec

分析

首先想到的肯定是用EXPLAIN 关键词分析执行情况，结果如下：

通过对执行计划结果的分析t_attachment_info表是全表扫描呀，这肯定慢。对照我们的查询语句，为什么是全表扫描没用到索引呢? t_attachment_info表中的attachment_id 是主键，有主键索引。那就是cdlm_bookshelf_test 表中的cover_id 没用索引导致的了，那我们就给创建个索引试试。

创建索引

给cdlm_bookshelf_test 表中的cover_id 创建索引

1
2
ALTER TABLE cdlm_bookshelf_test
  ADD INDEX idx_cover_Id (cover_id);

验证一下效果，结果花了11.3秒左右，更慢了，这是啥情况呢？继续用EXPLAIN 分析执行情况

从执行计划结果看，没啥变化呢？但是我看到join buffer 。难道是配置的问题？那就查看并配置一下join_buffer_size。

join_buffer_size配置

查看MySQL的配置，之前并没有配置join_buffer_size的值，那就是使用的默认值。那我们给她配置一个大一点值，再观察下效果。

1
2
# myslq 配置文件
join_buffer_size=2M

结果，查询数据时间确实变短了，现在5-6秒了，但这也很慢，同样查看执行计划，一样没变化。

我又找个一个之前的左连接查询语句，速度很快。所以这个参数确实能提升查询速度，但不是该语句慢的核心原因。到这我只能利用搜索引擎了。

字段类型不一致

查询到一篇文章的情况是，左连接关联的字段的类型不一致导致查询很慢。字段类型不一致数据库会先做类型转换再去关联查询，所以就慢。而我的查询语句关联字段类型是一致，都是varchar,只是长度是不一样的。难道是这样原因？修改cdlm_bookshelf_test 表中的cover_id 字段的长度也是40

1
2
ALTER TABLE cdlm_bookshelf_test
  CHANGE cover_id cover_id VARCHAR (40)  NULL COMMENT '资源封面,附件表id';

结果没啥变化。说明字段的长度不是影响因素。继续

字段字符集不一致

另一篇文章提到了，字段字符集不一致，也会导致查询慢。赶紧查看了所涉及的表的字符集是否不一致。

果然表的字符集是不一样的。

t_attachment_info是utf-8，而cdlm_bookshelf_test是utf8mb4。

导致表的字符集不一致的原因是，cdlm_bookshelf_test是新创建的，而t_attachment_info表是从旧系统导入进来的，这是历史原因了。那赶紧动手先修改字符集使其保持一致。

1
2
3
4
5
6
7
-- 先修改表
ALTER TABLE cdlm_bookshelf_test
  CHARSET = utf8;

-- 修改字段
alter table cdlm_bookshelf_test convert to character set utf8;

查询验证，只需要0.008秒。这才是正常的速度!

1
2
3
4
5
6
7
8
9
1 queries executed, 1 success, 0 errors, 0 warnings

查询：SELECT b.*, a.attachment_path AS cover_path FROM cdlm_bookshelf_test b left JOIN t_attachment_info a ON b.cover_id = a.attachmen...

共 5 行受到影响

执行耗时   : 0.008 sec
传送时间   : 0 sec
总耗时      : 0.008 sec

再看一下执行计划：

利用了eq_ref，只是对主键或者唯一索引的扫描，只读取了一行数据。

总结

本文主要是记录影响MySQL left join 查询慢的点，有索引、join_buffer_size、关联字段类型、字段字符集。先记录这些点，慢慢的可以深入其内部的工作原理，了解和掌握更多知识。

参考

MySQL 性能优化：EXPLAIN 执行计划与 join

mysql left join优化 left join查询慢