MySQL无需子查询的多表JOIN实现方案及性能咨询

你编写的原始查询语句如下：

select * from notification where status = 0 and (
 notif_id in (select notif_id from notif_user where user_id = 1) OR 
 notif_id in (select notif_id from notif_group where group_id = 1))

一、原写法的性能问题说明 #

MySQL 8.0虽然对普通IN子查询做了半连接优化，但当前用OR连接两个跨表IN子查询的写法，存在明确的性能隐患：

• 优化器无法将两个独立的IN子查询合并为高效的单路径执行计划，大表场景下容易出现子查询结果物化的临时表IO开销，扫描行数会远高于关联查询写法。
• 执行计划稳定性差，当notif_user、notif_group表的数据分布变化时，很容易出现索引失效、全表扫描的问题，性能抖动明显。
• 如果子查询返回的notif_id存在重复值，IN逻辑会触发额外的去重校验，带来不必要的CPU开销。

注：如果三张表数据量都在万级以下，且已经建好对应关联字段的索引，该写法不会有明显的性能问题，可以正常使用。

二、JOIN等价改写方案 #

可以通过JOIN方式改写为无嵌套子查询的形式，由于是OR匹配逻辑（通知要么关联指定用户、要么关联指定用户组），直接JOIN会产生重复行，需要搭配去重逻辑才能和原查询结果完全等价，共两种可用写法：

写法1：LEFT JOIN + DISTINCT（完全无嵌套子查询） #

SELECT DISTINCT n.*
FROM notification n
LEFT JOIN notif_user nu
  ON n.notif_id = nu.notif_id
  AND nu.user_id = 1
LEFT JOIN notif_group ng
  ON n.notif_id = ng.notif_id
  AND ng.group_id = 1
WHERE n.status = 0
  AND (nu.notif_id IS NOT NULL OR ng.notif_id IS NOT NULL);

逻辑说明：通过左连接分别匹配用户关联、用户组关联的记录，过滤掉两边都没匹配上的行，最后用DISTINCT去掉同时满足两个条件产生的重复通知。

写法2：INNER JOIN + UNION（大表场景推荐，性能更高） #

SELECT n.*
FROM notification n
INNER JOIN notif_user nu
  ON n.notif_id = nu.notif_id
WHERE n.status = 0
  AND nu.user_id = 1
UNION
SELECT n.*
FROM notification n
INNER JOIN notif_group ng
  ON n.notif_id = ng.notif_id
WHERE n.status = 0
  AND ng.group_id = 1;

逻辑说明：把OR条件拆成两个独立的内连接查询，分别查询匹配用户的通知、匹配用户组的通知，最后用UNION自带的去重能力合并结果，每个子查询都可以独立命中对应表的索引，大数据量下执行效率远高于原写法和写法1。

索引优化建议 #

不管使用哪种写法，都建议创建以下联合索引避免回表，最大化查询性能：

• notification表：idx_status_notifid(status, notif_id)
• notif_user表：idx_userid_notifid(user_id, notif_id)
• notif_group表：idx_groupid_notifid(group_id, notif_id)

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