9大策略，搞定MySQL多表JOIN性能优化

工作中，我们有时会遇到 MySQL join 表太多的情况，可能来自两个背景，一个是历史老代码，一个是去 o(Oracle) 改造，从 Oracle 迁移到 MySQL 的 SQL。

多张表的 join 很可能会带来问题，引发生产事故，增加后期维护成本。一个新系统上线时可能测不出问题，但随着数据量的增加，问题就会逐渐暴露出来了。

阿里开发手册中明确规定禁止三个表禁止 join。

那对于 MySQL 中 join 表多的 SQL，一般该怎么优化呢？

多个表使用 join 语句的根本原因是业务代码需要整合多张表里面的字段才能完成处理。那具体怎样优化呢？先来模拟一个多表 join 的 SQL，这里我们创建 5 张表：

CREATE TABLE`test1` (
`id`TINYINT(3) NOTNULLCOMMENT'主键ID',
`a`VARCHAR(20) DEFAULTNULL,
`b`VARCHAR(20) DEFAULTNULL,
`c`VARCHAR(200) DEFAULTNULL,
`d`TINYINT(3) DEFAULTNULL,
`create_time`TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'创建时间',
`update_time`TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'更新时间',
  PRIMARY KEY (`id`),
KEY`a` (`a`),
KEY`b` (`b`),
KEY`c` (`c`),
KEY`d` (`d`


    
)
) ENGINE=INNODBDEFAULTCHARSET=utf8

CREATETABLE test2 LIKE test1;
CREATETABLE test3 LIKE test1;
CREATETABLE test3 LIKE test1;
CREATETABLE test4 LIKE test1;

假如我们有这样一个包括多个表 join 的 SQL:

SELECT t1.id ,t1.a,t2.b,t3.c,t4.d FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000 JOIN test4 t4 ON t1.c=t4.c;

1.拆分 SQL

把多张表 join 的 SQL 拆解成多个 join 语句，在应用代码中进行组合。比如拆解成 2 个 SQL:

SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b;
SELECT t1.id ,t1.a,t4.d FROM test1 t1 JOIN test4 t4 ON t1.c=t4.c;

在业务代码中对两个 SQL 结果进行组合。

2.使用临时表

在上面的优化中，我们使用了 SQL 拆分的方式。如果 test3 表的数据量比较大，比如有 100万。但 test3 表使用到的结果集只有 1000 条，可以使用临时表：

CREATE TEMPORARY TABLE temp_t3(id TINYINT PRIMARY KEY, b VARCHAR(20),INDEX(b))ENGINE


    
=INNODB;
SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN temp_t3 t3 ON t1.b=t3.b;
SELECT t1.id ,t1.a,t4.d FROM test1 t1 JOIN test4 t4 ON t1.c=t4.c;

3.使用冗余字段

比如我们把 test4 表的 d 字段冗余到 test1 表中，假定字段名叫 t4c，这样就可以减少一个 join（当然，这样违反范式了）。最后只用下面的 SQL 就可以了：

SELECT t1.id ,t1.a,t2.b,t3.c,t1.t4c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000;

这样需要先在 test1 表中增加新字段 t4c，然后把 t4c 字段的值从 test4 表中更新过去。

改造需要注意两点，一个是评估更新字段的开销，第二个是要注意数据一致性，每次更新 test4 表中的 d 字段时也需要同步更新 test1 表中的 t4c 字段。

4.用好索引

join 语句对索引的使用非常重要，我们要注意下面几点：

驱动表（MySQL 会选择 where 语句筛选出记录少的表作为驱动表）和被驱动表的 join 列都应该有索引；
如果 join 语句涉及表的多个列，可以考虑为这些列建一个复合索引，比如下面 SQL：

SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c;

避免索引失效，比如 = 两端数据类型不同、使用函数、表达式等情况要避免；
优化 join 顺序，如果我们能确定哪个表做驱动表更合适，这时我们可以考虑使用 straight_join；

SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 


    
straight_join test2 t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c;

order by、limit 使用到的列尽量加上索引；
通过执行计划查看索引使用情况。

5.修改查询语句

如果某一个 join 表只是判断数据行是否存在，不需要使用表里面的字段时，我们可以考虑使用 exists 或 in 语句进行优化。对于下面这个 SQL:

SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000 JOIN test4 t4 ON t1.c=t4.c;

可以优化成如下 SQL：

SELECT t1.id ,t1.a,t2.b,t3.c FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000 WHERE EXISTS(SELECT id FROM test4 t4 WHERE t4.d=t1.d);

6.减少结果集

减少结果集，也是一种优化手段：

通过增加 where 条件来让驱动表结果集降到最小；
限制返回给应用的数据量，比如对返回结果做分页；
对于返回结果的列，如果不用则去掉，这样对 join_buffer 的使用也会有好处。

7.修改数据库配置

当然，也可以修改数据库一些配置，比如 join_buffer_size、tmp_table_size，增加 join_buffer 和临时表大小，但是数据库参数的修改影响范围太大了，尤其是对于老系统，坑很多，不好做影响分析，所以不建议使用。

8.引入大数据工具

如果 join 表的数据量都很大，我们也可以考虑引入大数据工具，比如 ETL、数据湖，将表数据抽取到数据仓库（比如 ClickHouse）中进行加工后把数据结果提供出来。当然，这样存在的问题是数据时效性低。

9.汇总表

如果查询时效性要求不高，可以通过定时任务把查询结果放到一张汇总表，查询的时候直接查询这张汇总表。也可以把结果放到缓存，从缓存中查询。

CREATE TABLE`test_join_result` (
`id`TINYINT(3) NOTNULLCOMMENT'主键ID',
`a`VARCHAR(20) DEFAULTNULL,
`b`VARCHAR(20) DEFAULTNULL,
`c`VARCHAR(200) DEFAULTNULL,
`d`TINYINT(3) DEFAULTNULL,
`e`TINYINT(1) DEFAULTNULL,
`create_time`TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'创建时间',
`update_time`TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'更新时间',
  PRIMARY KEY (`id`)
) ENGINE=INNODBDEFAULTCHARSET=utf8

--定时任务执行下面 SQL
insertinto test_join_result(id,a,b,c,d) SELECT t1.id ,t1.a,t2.b,t3.c,t4.d FROM test1 t1 JOIN test2 t2 ON t1.a=t2.a JOIN test3 t3 ON t1.b=t3.b AND t3.id <= 1000JOIN test4 t4 ON t1.c=t4.c;