MySQL组合LIKE与等于/IN时查询极慢，OR组合性能优化求助

问题根源 #

单独使用两个过滤条件时查询快，但用OR组合后变慢，核心原因有这几点：

• 索引无法高效复用：两个条件分别依赖不同表的索引（products.products_name的索引、products_rooms.room_id + products_id的复合索引），数据库优化器无法同时利用这两个索引处理OR逻辑，只能退化为全表扫描或低效的索引合并，遍历更多数据。
• 跨表OR的执行计划低效：OR条件同时涉及products表和products_rooms表的字段，加上原本的LEFT JOIN，优化器很难生成高效的关联过滤逻辑，会额外增加表关联的计算开销。
• GROUP BY的额外负载：OR条件会扩大结果集范围，后续的GROUP BY products_id需要处理更多重复数据，比单独条件时的分组操作耗时更长。

优化方案 #

1. 用UNION ALL替换OR逻辑 #

将两个条件拆分为独立查询，再合并结果，让每个子查询都能使用最优索引：

SELECT * FROM (
    -- 匹配产品名称含kitchen的记录
    SELECT p.*, pd.*
    FROM products p
    LEFT JOIN products_description pd ON pd.products_id = p.products_id
    WHERE p.products_status = 1 
      AND p.products_name LIKE '%kitchen%'
    
    UNION ALL
    
    -- 匹配关联到room_id=5的记录
    SELECT p.*, pd.*
    FROM products p
    LEFT JOIN products_description pd ON pd.products_id = p.products_id
    INNER JOIN products_rooms pr ON pr.products_id = p.products_id
    WHERE p.products_status = 1 
      AND pr.room_id = 5
) temp
GROUP BY temp.products_id

注：如果两个子查询可能产生重复的products_id，用UNION ALL比UNION更高效，最后通过外层GROUP BY去重即可。

2. 优化索引配置 #

• 给products表添加复合索引：(products_status, products_name)，帮助快速过滤状态正常且名称匹配的记录。
• 给products_rooms表添加复合索引：(room_id, products_id)，让room_id=5的过滤和关联products表的操作更高效。

3. 调整JOIN类型 #

第二个子查询中，因为pr.room_id=5的条件会过滤掉未关联的记录，所以把LEFT JOIN products_rooms改为INNER JOIN，减少不必要的NULL值处理开销。

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