含OR运算符与子查询的MySQL查询性能优化求助

问题原因拆解 #

1. JOIN+OR查询慢的核心原因 #

虽然给关联键和LastChanged加了索引，但OR条件连接不同表的字段时，数据库通常无法高效合并多个索引的过滤结果。比如你的查询中，OriginalContracts.LastChanged > 指定时间和ContractAccessories.LastChanged > 指定时间是两个独立的过滤条件，数据库可能会放弃索引，转而进行全表扫描+连接，或者使用低效的索引合并策略，最终导致300ms的耗时。

2. IN+UNION子查询嵌入后慢的原因 #

单独子查询快是因为它只需要扫描两个表的LastChanged索引并去重，但嵌入主查询后，问题通常出在：

• 若子查询返回的ID数量过大，IN操作会变成逐行匹配，无法利用主键索引的高效查找；
• 部分数据库会把IN子查询转换为相关子查询（即主查询每一行都执行一次子查询），而非先执行子查询得到ID列表再匹配；
• UNION后的结果集没有被优化为可快速匹配的结构，导致主查询关联时额外开销。

针对性优化方案 #

方案1：用EXISTS替代IN+UNION（优先推荐） #

EXISTS是半连接逻辑——只要找到匹配的记录就停止扫描，比IN更适合这种跨表存在性判断，且能更好利用复合索引：

SELECT oc.*
FROM OriginalContracts oc
WHERE oc.LastChanged > '2024-01-01 00:00:00' -- 替换为你的指定时间
OR EXISTS (
    SELECT 1
    FROM ContractAccessories ca
    WHERE ca.ContractId = oc.Id
      AND ca.LastChanged > '2024-01-01 00:00:00'
)

索引配合：给ContractAccessories创建复合索引(ContractId, LastChanged)，这样子查询能先通过ContractId定位到关联行，再过滤LastChanged，完全走索引扫描。

方案2：预生成ID集再关联主表 #

如果EXISTS优化效果不明显，可以先把符合条件的合同ID去重后存入临时表/CTE，再和主表做JOIN：

-- 用CTE的写法（支持CTE的数据库：MySQL 8+/PostgreSQL/SQL Server等）
WITH UpdatedContractIds AS (
    SELECT Id FROM OriginalContracts WHERE LastChanged > '指定时间'
    UNION -- 用UNION自动去重，比UNION ALL后DISTINCT高效
    SELECT ContractId FROM ContractAccessories WHERE LastChanged > '指定时间'
)
SELECT oc.*
FROM OriginalContracts oc
JOIN UpdatedContractIds uci ON oc.Id = uci.Id

如果是MySQL 5.x这类不支持CTE的版本，改用临时表：

CREATE TEMPORARY TABLE temp_contract_ids (
    Id INT PRIMARY KEY -- 主键自动创建索引，加快JOIN
);
INSERT INTO temp_contract_ids
SELECT Id FROM OriginalContracts WHERE LastChanged > '指定时间'
UNION
SELECT ContractId FROM ContractAccessories WHERE LastChanged > '指定时间';

SELECT oc.*
FROM OriginalContracts oc
JOIN temp_contract_ids tci ON oc.Id = tci.Id;

DROP TEMPORARY TABLE temp_contract_ids;

方案3：调整索引策略 #

• 确保OriginalContracts的LastChanged字段有单独索引；
• ContractAccessories的复合索引必须是(ContractId, LastChanged)，而非反过来——因为查询时先通过ContractId关联主表，再过滤时间，索引顺序要匹配查询逻辑。

优化验证要点 #

查看执行计划，确认以下几点：

• 过滤LastChanged时出现Using index或Using index condition（说明走了索引）；
• JOIN/EXISTS操作的类型是eq_ref或ref（高效的索引查找）；
• 没有出现Using filesort或Using temporary（除非是UNION去重的必要操作）。

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