MySQL基于单字段多分组生成跨组选项全组合的高效实现咨询

方案适配性说明 #

• 直接适配现有业务表结构，不需要修改任何字段
• 自动兼容动态业务场景：单个产品的分组数量、每个分组下的选项数量变化时，不用手动调整计算逻辑
• 相比原来PHP层逐次查询、逐组数组组装的实现，整体数据库查询次数从“产品总数+所有产品的分组数总和”收敛为单轮库内计算，内存占用大幅下降

核心实现逻辑（基于MySQL8.0+） #

利用递归CTE自动逐层跨组拼接笛卡尔积，中间结果用JSON类型存储，最终拆解为对应分组的独立列输出，完整SQL如下：

-- 1. 预处理：固定产品下的分组排序、分组内选项排序
WITH group_rank AS (
    SELECT 
        Id_Product,
        `Group`,
        DENSE_RANK() OVER (PARTITION BY Id_Product ORDER BY `Group`) AS group_seq
    FROM product_option_config
    WHERE Id_Product = 1 -- 替换为待计算的产品ID
    GROUP BY Id_Product, `Group`
),
option_ranked AS (
    SELECT 
        poc.Id_Product,
        gr.group_seq,
        poc.`Group`,
        poc.Id_Option,
        poc.Name,
        ROW_NUMBER() OVER (PARTITION BY poc.Id_Product, poc.`Group` ORDER BY poc.Id_Option) AS option_seq
    FROM product_option_config poc
    INNER JOIN group_rank gr 
        ON poc.Id_Product = gr.Id_Product AND poc.`Group` = gr.`Group`
),
-- 2. 递归逐层拼接笛卡尔积
combos AS (
    -- 递归锚点：加载第一个分组的所有选项作为初始组合
    SELECT 
        Id_Product,
        group_seq AS max_group_seq,
        CAST(
            JSON_OBJECT(
                CONCAT('g', group_seq), 
                JSON_OBJECT('opt_id', Id_Option, 'opt_name', Name)
            ) AS JSON
        ) AS combo_data
    FROM option_ranked
    WHERE group_seq = 1

    UNION ALL

    -- 递归部分：将已有组合与下一分组的所有选项交叉拼接
    SELECT 
        or2.Id_Product,
        or2.group_seq AS max_group_seq,
        JSON_MERGE_PATCH(
            c.combo_data,
            CAST(
                JSON_OBJECT(
                    CONCAT('g', or2.group_seq), 
                    JSON_OBJECT('opt_id', or2.Id_Option, 'opt_name', or2.Name)
                ) AS JSON
            )
        ) AS combo_data
    FROM combos c
    INNER JOIN option_ranked or2 
        ON c.Id_Product = or2.Id_Product 
        AND or2.group_seq = c.max_group_seq + 1
    WHERE c.max_group_seq < (SELECT MAX(group_seq) FROM group_rank WHERE Id_Product = c.Id_Product)
)
-- 3. 最终输出：拆解JSON为对应分组的独立选项列
SELECT 
    Id_Product,
    JSON_EXTRACT(combo_data, '$.g1.opt_id') AS qty_opt_id,
    JSON_UNQUOTE(JSON_EXTRACT(combo_data, '$.g1.opt_name')) AS qty_opt_name,
    JSON_EXTRACT(combo_data, '$.g2.opt_id') AS size_opt_id,
    JSON_UNQUOTE(JSON_EXTRACT(combo_data, '$.g2.opt_name')) AS size_opt_name,
    JSON_EXTRACT(combo_data, '$.g3.opt_id') AS weigth_opt_id,
    JSON_UNQUOTE(JSON_EXTRACT(combo_data, '$.g3.opt_name')) AS weigth_opt_name
FROM combos
WHERE max_group_seq = (SELECT MAX(group_seq) FROM group_rank WHERE Id_Product = combos.Id_Product);

性能优化说明 #

• 给(Id_Product, Group, Id_Option)建联合索引，10万级数据量下，单产品的全组合计算耗时可控制在百毫秒内
• 计算前增加结果量校验：单产品所有分组的选项数乘积超过阈值（如10000）时直接跳过，避免生成超大结果集拖垮数据库
• 针对MySQL5.7及以下不支持递归CTE的版本，可在PHP层先查询单个产品的所有去重分组，动态生成多表内连接SQL（即每个分组对应一张子查询表，通过Id_Product关联做内连接），这种动态JOIN的笛卡尔积计算效率远高于PHP层遍历数组组装
• 原PHP方案可同步优化：去掉逐产品、逐分组的多次查询，改为一次性拉取所有待计算产品的全部选项行，在PHP内存中完成组装，用关联数组做索引减少重复数据加载，进一步降低内存占用

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