MySQL多表关联on和where速度对比实测谁更快

MySQL多表关联on和where速度对比实测谁更快

背景

• 今天发现有人在讨论：两张MySQL的数据表按照某一个字段进行关联的时候查询，我们使用on和where哪种查询方式更快。
• 百闻不如一见，我们来亲自测试下。

先说结论

• Where、对等查询的join速度基本一致，不对等查询时一般join更慢。

不存在不等记录

• 我们有两张表，分别是member和member_class，数据结构如下图，其中 member.class_id 和 member_class.id 是关联字段

• 

• 

• 我们分别向两张表写入了20万条数据，其中member.class_id 都一定存在于member_class.id中（不存在不等记录）

查全表

• 

• 

• 为了实验更加相对准确，我们对每条结果查询20次：where查询耗时分别为（秒）：0.253, 0.256, 0.256, 0.252, 0.257, 0.252, 0.260, 0.265, 0.253, 0.252, 0.254, 0.257, 0.254, 0.257, 0.243, 0.250, 0.252, 0.252, 0.255, 0.284on查询耗时分别为（秒）：0.247, 0.260, 0.250, 0.246, 0.271, 0.247, 0.251, 0.247, 0.243, 0.247, 0.247, 0.245, 0.249, 0.246, 0.247, 0.253, 0.248, 0.254, 0.251, 0.247, 0.250where查询平均为0.2557s，on查询平均为0.2498s；两者基本一致，相差几乎可以忽略。

存在不等记录

• 我们新建一个member_v2表，其中class_id只有20%是member_class.id中存在的，如下图：

• 

• 我们继续查询全表测试：

• 

• 

• 能看出来非常明显的差距，LEFT JOIN由于存在大量不等记录，出现大量的NULL值，此时中间表查询效率变得更低，而选择WHERE查询能够在查询时自动过滤不等记录。所以查询时间也更快一些。

结论

• 在对等查询过程中，ON的查询更快；在不对等查询时，WHERE更快。