MySQL 5.7左连接空集为何导致查询性能大幅下降？

查询语句 #

select Address.*
from Address
left join (
    select lotNumber, max(jobId) as id
    from Address
    where jobId is not null
    group by lotNumber
) latestJob on latestJob.lotNumber = Address.lotNumber

表结构 #

CREATE TABLE `Address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `streetNumber` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `street` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lotNumber` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `jobId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_Address_lotNumber` (`lotNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=1032717 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

执行计划 #

+----+-------------+-----------------+------------+-------+-------------------------------+-------------------------------+---------+---------------------------+---------+----------+-------------+
| id | select_type | table           | partitions | type  | possible_keys                 | key                           | key_len | ref                       | rows    | filtered | Extra       |
+----+-------------+-----------------+------------+-------+-------------------------------+-------------------------------+---------+---------------------------+---------+----------+-------------+
|  1 | PRIMARY     | Address         | NULL       | ALL   | NULL                          | NULL                          | NULL    | NULL                      | 1027850 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived2>      | NULL       | ref   | <auto_key0>                   | <auto_key0>                   | 183     | Address.lotNumber         |      10 |   100.00 | NULL        |
|  2 | DERIVED     | Address         | NULL       | index | idx_Address_lotNumber         | idx_Address_lotNumber         | 183     | NULL                      | 1027850 |    90.00 | Using where |
+----+-------------+-----------------+------------+-------+-------------------------------+-------------------------------+---------+---------------------------+---------+----------+-------------+

问题背景与疑问 #

当前Address表约有100万条记录，且所有记录的jobId均为NULL，因此左连接的子查询返回空集。

• 子查询单独执行耗时约0.07秒
• 整个左连接查询耗时约2.22秒
• 仅查询Address.*（无连接）耗时约0.07秒

按预期，连接空集的总耗时应接近子查询+基础查询的时间（0.07+0.07=0.14秒），但实际多出来的2秒耗时来自哪里？如何优化这个低效的连接操作？

原因分析 #

从执行计划可定位核心问题：

1. 主查询对Address表执行了全表扫描（type: ALL），未利用现有索引，遍历100万条记录本身就有开销。
2. 即使子查询返回空集，MySQL仍会对主表的每一条记录执行一次与派生表<derived2>的匹配逻辑——这个匹配过程没有实际意义，但仍会消耗CPU和IO资源，累积成额外的2秒耗时。
3. 子查询虽使用了idx_Address_lotNumber索引，但主查询的全表扫描是性能瓶颈的关键。

优化方案 #

方案1：直接简化查询（当前场景最优） #

由于所有jobId均为NULL，子查询必然返回空集，左连接不会带来任何额外数据，可直接跳过连接逻辑：

select Address.* from Address;

该语句耗时与无连接的原始查询一致，完全规避无效连接开销。

方案2：优化索引适配未来业务场景 #

如果后续表中会出现jobId非空的记录，需要保留左连接逻辑，可通过索引优化：

• 创建联合索引idx_Address_jobId_lotNumber(jobId, lotNumber)，让子查询更快过滤jobId非空的记录并完成分组。
• 强制主查询使用lotNumber索引，避免全表扫描：

select Address.*
from Address force index(idx_Address_lotNumber)
left join (
    select lotNumber, max(jobId) as id
    from Address
    where jobId is not null
    group by lotNumber
) latestJob on latestJob.lotNumber = Address.lotNumber;

方案3：动态适配业务场景 #

如果需要同时兼容jobId全为空和非空的场景，可通过EXISTS判断动态切换查询逻辑：

select Address.*
from Address
left join (
    select lotNumber, max(jobId) as id
    from Address
    where jobId is not null
    group by lotNumber
) latestJob on latestJob.lotNumber = Address.lotNumber
where exists (select 1 from Address where jobId is not null)
union all
select Address.* from Address
where not exists (select 1 from Address where jobId is not null);

当jobId全为NULL时，会直接执行后半部分的简单查询，避免无效连接。

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