如何优化含OR条件与关联表过滤的MySQL查询性能

一、最优索引设计 #

针对你的表结构和查询场景，建议添加以下索引：

• tblRecipient：
  • 复合索引 idx_user_letter (userId, letterId)：直接定位指定用户关联的所有信件ID，避免全表扫描
  • 复合索引 idx_street_letter (street, letterId)：快速匹配指定街道对应的信件ID
    （拆分两个复合索引，而非单字段索引，是为了让每个条件分支都能直接获取关联的letterId，避免后续索引合并的高开销）
• tblLetter：
  • 确保id为主键（InnoDB默认聚簇索引，关联查询效率最高）
  • 复合索引 idx_expire_created (expire, created)：先过滤未过期的信件，同时直接利用索引完成created desc的排序，无需额外排序操作，且InnoDB索引会包含主键，无需回表查询id和created字段

二、查询语句优化：用UNION ALL替代OR #

OR条件会触发index_merge策略，大数据量下合并开销极高，建议拆分查询用UNION ALL规避：

SELECT DISTINCT L.id, L.created
FROM (
    -- 单独处理用户ID条件
    SELECT R.letterId
    FROM tblRecipient R
    WHERE R.userId = 234
    UNION ALL
    -- 单独处理街道条件
    SELECT R.letterId
    FROM tblRecipient R
    WHERE R.street = 's2'
) AS sub
JOIN tblLetter L ON L.id = sub.letterId
WHERE L.expire > NOW()
ORDER BY L.created DESC LIMIT 100;

• 每个子查询都会精准匹配对应的复合索引，快速获取目标信件ID
• 若同一个信件ID不会同时满足两个条件，可将UNION ALL改为UNION，自动去重，省去外层DISTINCT的开销

三、关于index_merge的规避 #

index_merge是MySQL针对OR条件的索引合并策略，但数十万行结果集的合并（如sort-union）会产生极高的CPU和IO开销。通过上述拆分查询的方式，可主动绕过索引合并，让每个查询分支都走最优索引，彻底消除合并成本。

四、数据模型限制分析 #

对于拥有数十万封信件的用户，确实需要遍历该用户关联的所有信件ID，但通过索引优化，已将遍历范围缩小到仅目标用户/街道的信件数据，而非全表。结合tblLetter的idx_expire_created索引，MySQL可直接在索引中过滤未过期数据并按创建时间排序，配合LIMIT 100，优化器会提前终止排序，无需处理全部结果集，性能可大幅提升。

额外建议：执行ANALYZE TABLE tblRecipient, tblLetter;更新表统计信息，确保优化器能正确选择最优索引。

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