含多子查询的MySQL慢查询优化方案求助

需求说明 #

需要列出分属不同集合（Set）的产品（Product），并按每个集合轮流展示一个产品的规则排序。

产品与集合的对应关系如下：

期望输出结果：

• Set 01 - Product 01
• Set 02 - Product 06
• Set 03 - Product 11
• Set 01 - Product 02
• Set 02 - Product 07
• Set 03 - Product 12
• Set 01 - Product 03
• Set 02 - Product 08
• Set 03 - Product 13
• Set 02 - Product 09
• Set 03 - Product 14
• Set 03 - Product 15

初始查询（性能良好） #

初始查询语句性能表现正常：

SELECT * FROM
    (SELECT *, (@row_num := if(@set_id = set_id, @row_num + 1, if(@set_id := set_id, 1, 1) ) ) AS rn
FROM (
    select ps.product_id, ps.set_id, ps.order FROM
    `product_set` ps JOIN sets s ON ps.set_id = s.id
    ORDER BY ps.set_id desc, ps.order) ps

CROSS JOIN (SELECT @set_id := -1, @row_num:= 0) params ) AS product_set

    WHERE `product_set`.`set_id` IN 
        (select `set_id` FROM `sets` WHERE `sets`.`status_id` = 50) 

慢查询问题（添加多条件后耗时增加） #

添加多个IN子查询过滤条件后，查询耗时增至约1.8秒，存在性能问题：

SELECT * FROM
    (SELECT *, (@row_num := if(@set_id = set_id, @row_num + 1, if(@set_id := set_id, 1, 1) ) ) AS rn
FROM (
    select `ps`.`product_id`, `ps`.`set_id`, `ps`.`order` FROM
    `product_set` ps JOIN `sets` s ON `ps`.`set_id` = `s`.`id`
    ORDER BY ps.set_id desc, ps.order) ps

CROSS JOIN (SELECT @set_id := -1, @row_num:= 0) params ) AS product_set

    WHERE `product_set`.`set_id` IN 
        (select `set_id` FROM `sets` WHERE `sets`.`status_id` = 50) 
    
    AND 
    `product_set`.`product_id` IN 
        (select `product_id` FROM `product_state` 
                                WHERE `product_state`.`state_id` = 1 
                                  AND `product_state`.`status_id` = 17)

    AND 
    `product_set`.`product_id` IN 
        (select `product_id` FROM `customer_product` WHERE `customer_product`.`customer_id` IN
            (select `id` FROM `customers` WHERE `type_id` IN (1, 5))
        ) 
    AND `product_set`.`product_id` IN 
        (select `id` FROM `products` WHERE `products`.`camera_id` IN (1, 2)) 
    
    AND `product_set`.`product_id` IN 
        (select `product_id` FROM `customer_product` WHERE `customer_id` IN (1, 17, 23, 32)) 
    
    AND `product_set`.`product_id` IN
        (select `product_id` FROM `product_categories` WHERE `category_id` IN (124, 127, 116, 117))
    
    AND `product_set`.`product_id` IN 
        (select `product_id` FROM `product_categories` WHERE  `category_id` IN (111))
    
    ORDER BY `product_set`.`rn` asc , `product_set`.`set_id` desc;

已完成的优化（耗时降至208ms） #

将IN子查询改为JOIN后，查询耗时从1.8秒降至208ms，优化后的语句如下：

SELECT * FROM
    (SELECT *, (@row_num := if(@set_id = set_id, @row_num + 1, if(@set_id := set_id, 1, 1) ) ) AS rn
     FROM (
              select `ps`.`product_id`, `ps`.`set_id`, `ps`.`order` FROM
                  `product_set` ps JOIN `sets` s ON `ps`.`set_id` = `s`.`id`
              ORDER BY ps.set_id desc, ps.order) ps
CROSS JOIN (SELECT @set_id := -1, @row_num:= 0) params ) AS product_set 

JOIN 
    (SELECT DISTINCT `id` FROM `sets` WHERE `sets`.`status_id` = 50) 
        AS q1 ON q1.id = `product_set`.`set_id`
JOIN
    (SELECT DISTINCT `product_id` FROM `product_state` 
     WHERE `product_state`.`state_id` = 1 
     AND `product_state`.`status_id` = 17) 
        AS q2 ON q2.product_id = product_set.product_id
JOIN 
    (SELECT DISTINCT `id` FROM `products` WHERE `products`.`camera_id` IN (1, 2)) 
        AS q3 ON q3.id = product_set.product_id 
JOIN 
    (SELECT DISTINCT `product_id` FROM `customer_product` WHERE `customer_id` IN (1, 17, 23, 32))
        AS q4 ON q4.product_id = product_set.product_id 
JOIN 
    (SELECT DISTINCT `product_id` FROM `product_categories` WHERE `category_id` IN (124, 127, 116, 117))
        AS q5 ON q5.product_id = product_set.product_id
JOIN 
    (SELECT DISTINCT `product_id` FROM `product_categories` WHERE `category_id` IN (111))
        AS q6 ON q6.product_id = product_set.product_id
JOIN 
    (SELECT DISTINCT `product_id` FROM `customer_product` JOIN 
        (SELECT DISTINCT `id` FROM `customers` WHERE `type_id` IN (1, 5)) 
            AS q8 ON q8.id = `customer_product`.`customer_id`) 
        AS q7 ON q7.product_id = product_set.product_id
    
ORDER BY `product_set`.`rn` asc , `product_set`.`set_id` desc; 

适配测试数据的查询语句：

SELECT * FROM
    (SELECT *, (@row_num := if(@set_id = set_id, @row_num + 1, if(@set_id := set_id, 1, 1) ) ) AS rn
     FROM (
              select `ps`.`product_id`, `ps`.`set_id`, `ps`.`order` FROM
                  `product_set` ps JOIN `sets` s ON `ps`.`set_id` = `s`.`id`
              ORDER BY ps.set_id desc, ps.order) ps
CROSS JOIN (SELECT @set_id := -1, @row_num:= 0) params ) AS product_set 

JOIN 
    (SELECT DISTINCT `id` FROM `sets` WHERE `sets`.`status_id` = 50) 
        AS q1 ON q1.id = `product_set`.`set_id`
JOIN
    (SELECT DISTINCT `product_id` FROM `product_state` 
     WHERE `product_state`.`state_id` = 1 
     AND `product_state`.`status_id` = 17) 
        AS q2 ON q2.product_id = product_set.product_id
JOIN 
    (SELECT DISTINCT `id` FROM `products` WHERE `products`.`camera_id` IN (1, 2)) 
        AS q3 ON q3.id = product_set.product_id 
JOIN 
    (SELECT DISTINCT `product_id` FROM `customer_product` WHERE `customer_id` IN (1, 5, 6, 8))
        AS q4 ON q4.product_id = product_set.product_id 
JOIN 
    (SELECT DISTINCT `product_id` FROM `product_categories` WHERE `category_id` IN (3, 4, 6, 7))
        AS q5 ON q5.product_id = product_set.product_id
JOIN 
    (SELECT DISTINCT `product_id` FROM `product_categories` WHERE `category_id` IN (11))
        AS q6 ON q6.product_id = product_set.product_id
JOIN 
    (SELECT DISTINCT `product_id` FROM `customer_product` JOIN 
        (SELECT DISTINCT `id` FROM `customers` WHERE `type_id` IN (1, 5)) 
            AS q8 ON q8.id = `customer_product`.`customer_id`) 
        AS q7 ON q7.product_id = product_set.product_id
ORDER BY `product_set`.`rn` asc , `product_set`.`set_id` desc; 

进一步性能优化建议 #

• 提前过滤数据，缩小数据集：将所有过滤条件移到最内层的product_set查询中，先筛选符合条件的产品集合关联数据，再进行行号计算，避免先处理全量数据再过滤，减少窗口函数/变量计算的开销。
• 替换用户变量为窗口函数（MySQL 8.0+支持）：用ROW_NUMBER() OVER (PARTITION BY set_id ORDER BY order)替代用户变量计算rn，这是MySQL 8.0的标准特性，性能更稳定，避免用户变量依赖隐式排序的问题：

  SELECT *, ROW_NUMBER() OVER (PARTITION BY set_id ORDER BY `order`) AS rn
  FROM (
      -- 内层过滤后的product_set数据
  ) ps
  

• 移除不必要的DISTINCT：检查每个JOIN子查询，若关联字段是主键或唯一键，DISTINCT是多余的，比如sets表的id是主键，SELECT id FROM sets WHERE status_id=50不需要DISTINCT，减少去重的性能开销。
• 合并重复的JOIN条件：比如两个product_categories的过滤条件可以合并为一个子查询SELECT product_id FROM product_categories WHERE category_id IN (3,4,6,7,11)，减少JOIN次数，降低查询复杂度。
• 添加针对性索引：
  • 给product_set添加复合索引：(set_id, order, product_id)，支持内层查询的排序和字段快速获取。
  • 给过滤表添加覆盖索引：
    • sets(status_id, id)
    • product_state(state_id, status_id, product_id)
    • products(camera_id, id)
    • customer_product(customer_id, product_id)
    • product_categories(category_id, product_id)
    • customers(type_id, id)
• 分析执行计划：用EXPLAIN ANALYZE查看查询执行计划，确认索引是否被正确使用，是否存在全表扫描、临时表或文件排序的瓶颈，针对性调整。

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