MySQL 调优：直接 JOIN or 先子查询再 JOIN？

1. 问题背景

最近，一个朋友的项目经理指出他的 SQL 写得有问题。
朋友的 SQL 大致如下，他的想法是常规操作，直接使用 JOIN … ON … 做联表查询：

select needed cols... from t1 
inner join t2 on t1.col12 = t2.col12 
inner join t3 on t2.col23 = t3.col23;

项目经理的建议是修改为：

select needed cols... from t1
inner join (select needed cols... from t2) tmp2 on t1.col12 = t1.col12
inner join (select needed cols... from t3) tmp3 on t2.col23 = t3.col23;

经理的想法是先用子查询查出各个从表中需要展示的列，再用 JOIN … ON … 做联表查询。
乍一看很有道理，先用子查询选出从表中需要展示的列，形成一张列较少的临时表，再进行 inner join ，似乎确实能够节省查询时间。那么，事实是否真的如该经理所愿呢？实践是检验真理的唯一标准。

2. 测试数据

2.1 建表

建立三张表：user、company、address，各表中的列如下图所示。
为了插入测试数据方便，对于各表既没有设置主键或外键，也没有手动建立索引。
在本例中，company 与 user 是 一对多关系，address 与 company 是一对多关系；即表示一个公司中可能有多名员工，一个地区可能有多家公司。
user 表中的 cid 对应 company 表的 cid，company 表中的 cid 对应 address 表的 aid。

2.2 插入数据

本例在 user 表中插入 2W 条数据，company 和 address 表中各插入 20W 条数据。
下图 SQL 的查询结果表示，uid = 10, uname = ‘梅西’, position = ‘前锋’, area = ‘足球’, cid = 2 的重复数据有2017条，以此类推。

3. 测试耗时

3.1 SQL 编写

为了保证测试的公平性，需要使用控制变量的思想，即保证直接 JOIN 和子查询 JOIN 两种 SQL 语句的查询结果是一致的。两种 SQL 编写如下：
直接 JOIN：

SELECT u.uid, u.uname, c.cname, a.city FROM t_user u 
INNER JOIN t_company c ON u.cid = c.cid 
INNER JOIN t_address a ON c.aid = a.aid;

先子查询再 JOIN：

SELECT u.uid, u.uname, c.cname, a.city FROM t_user u 
INNER JOIN (SELECT cid, cname, aid FROM t_company) c ON u.cid = c.cid 
INNER JOIN (SELECT aid, city FROM t_address) a ON c.aid = a.aid;

另外，为了消除 MySQL 缓存池的影响，在每一次 SQL 查询执行之前，都清理 MySQL 的查询缓存：

RESET QUERY CACHE;

3.2 开始测试

第一轮测试

直接 JOIN 耗时 5.071 s，先子查询再 JOIN 耗时 5.395 s

第二轮测试

直接 JOIN 耗时 5.084 s，先子查询再 JOIN 耗时 5.412 s

第三轮测试

直接 JOIN 耗时 5.068 s，先子查询再 JOIN 耗时 5.547 s


三轮测试下来的数据如下：

Test Round	Direct Join	Subquery then Join
Round 1	5.063 s	5.395 s
Round 2	5.084 s	5.412 s
Round 3	5.068 s	5.547 s
Avg	5.072 s	5.451 s

根据三次测试的样本结果，直接 JOIN 查询比先子查询再 JOIN 快了 7.5%
所以该经理的理论非常值得怀疑。直接 JOIN 查询虽然会关联更多的无关列，但子查询不关联无关列的代价是增加了建立、销毁临时表的开销，两权相害取其轻，而后者的开销在本文的实验中被证明是更大的，所以该经理的想法是值得商榷的，如果经过多场景大数据量下的反复试验，先子查询再 JOIN 相比于直接 JOIN 仍是耗时更多的一方，那么该想法则应当被彻底舍弃。

参考文献

1. StackOverFlow: SQL Joins Vs SQL Subqueries (Performance)?
2. MySQL Official Document: Rewriting Subqueries as Joins