UPDATE使用LEFT JOIN加IS NULL条件出现极低性能问题咨询

根本原因 #

从你提供的EXPLAIN执行计划可以直接定位问题：MySQL选择了foo作为左连接的驱动表，走*嵌套循环连接（Nested Loop Join）*执行逻辑：

• 第一步全表扫描foo表，逐行取出id字段，累计要处理约4.2万行数据
• 对每一个取出的foo.id，都需要到bar表中匹配是否存在对应的bar.foo_id。你现有的联合索引uidx_qux_foo的最左前缀是qux_id，不符合foo_id查询的最左匹配原则，因此无法走索引点查，每次匹配都需要全量扫描整个联合索引（也就是执行计划里type为index的索引全扫描），单次扫描13万行
• 累计总扫描行数就是4.2万 * 13万 ≈ 54.6亿，和慢查询日志里的57亿数值接近，误差来自统计信息的近似估算，这就是CPU占满、执行耗时极长的核心原因。

常见误区纠正 #

你认为“为bar.foo_id单独建索引不会生效”是错误判断：
这个LEFT JOIN + IS NULL的逻辑本质是查找所有在bar表中没有关联记录的foo行，只要bar.foo_id上有独立索引，或者有以foo_id为最左前缀的联合索引，每次匹配foo.id就可以走索引点查，单次匹配只需要扫描1~2行，总扫描行数直接降到4.2万级别，性能会提升数万倍。

可行优化方案 #

• 方案1（优先推荐）：在bar表上新建以foo_id为最左前缀的索引，比如单独建idx_bar_foo_id(foo_id)，或者结合业务需求建idx_bar_foo_qux(foo_id, qux_id)，只要foo_id在索引最左位置即可。修改后执行计划会自动走索引匹配，执行时间可以降到毫秒级。
• 方案2（无需修改Schema的临时方案）：将查询改写为NOT EXISTS语法，MySQL 5.7对NOT EXISTS的优化在很多场景下比左连接判空更稳定，改写后语句如下：

UPDATE foo
SET foo.flag = 1
WHERE NOT EXISTS (
    SELECT 1 FROM bar WHERE bar.foo_id = foo.id
);

• 方案3（你已经在用的临时方案）：先对bar表执行GROUP BY foo_id得到去重后的foo_id集合，再和foo表关联，本质是把被驱动表的行数从13万降到foo_id去重后的数量（最多4.2万），总扫描行数直接下降两个数量级。

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