这是个非常实用的性能优化案例，结合你的MySQL 5.7环境和表结构数据情况，咱们来拆解背后的核心原因：

1. 大幅减少数据加载与内存占用 #

你原来的写法直接关联table4和table5，MySQL执行时会默认加载这两个表的所有字段到内存中（哪怕你最终只用到id）。而优化后的子查询select id from table4只提取需要的id列，数据量瞬间缩小——哪怕table4只有16000行，如果它有很多字段（比如文本、大字段），节省的内存和磁盘IO会非常可观。

对于你的场景，table1子查询结果有400万行，和大体积的table4做LEFT JOIN时，内存开销会被放大很多；换成只存id的小临时表后，JOIN过程中内存占用大幅降低，避免了频繁的磁盘交换，自然速度更快。

2. 降低无索引表的全表扫描代价 #

table4的id没有索引，这是关键痛点之一：

• 原写法直接关联table4时，MySQL做JOIN需要对table4做全表扫描，而且要读取每一行的所有字段，IO成本很高；
• 优化后的子查询只读取id列，全表扫描时只需要加载存储id的数据页——相比加载整个表的所有数据页，IO次数和耗时都会大幅减少。

哪怕table4只有16000行，全表扫描只取id的代价也远低于扫描整个表，更别说和400万行数据做JOIN时的累积效应了。

3. MySQL 5.7优化器的针对性优化 #

MySQL 5.7的查询优化器会将这个只取id的子查询处理成物化临时表：它会先执行子查询，把id结果存储在一个内存临时表（或磁盘临时表，如果数据量稍大）里，后续的LEFT JOIN直接用这个临时表做关联。

这种物化临时表的优势在于：

• 临时表只包含id列，结构极简，JOIN时的匹配速度更快；
• 避免了优化器可能对全表关联做出的低效执行计划（比如重复扫描table4）。

4. 对比有索引表的差异 #

你提到table2、table3的id有索引，所以直接关联它们没问题——因为优化器可以通过索引快速定位匹配的行，不需要全表扫描，也不需要加载多余字段。但table4没有索引，只能全表扫描，这时候“只取需要的列”就成了关键的优化点。

总结一下：你的优化本质是把“全表关联”转化为“小临时表关联”，通过减少数据传输、降低IO开销、利用优化器的物化特性，大幅提升了LEFT JOIN的性能。

内容的提问来源于stack exchange，提问作者Paul