如何对5亿行表按父子层级排序并统计产品行号？

针对你5亿行的大表需求，核心是先构建每个节点的层级路径，再基于路径实现父子排序，同时结合ROW_NUMBER()按产品分区生成行号。由于每个父节点仅对应一个子节点，每个产品下的层级是独立单链，这个场景非常适合用递归CTE（Common Table Expression）处理，同时需要兼顾大表的性能优化。

解决方案SQL示例（适配MySQL 8+/PostgreSQL等支持递归CTE的数据库） #

WITH RECURSIVE product_hierarchy AS (
    -- 第一步：获取所有产品的根节点（Parent为NULL的节点）
    SELECT 
        Id, 
        Parent, 
        Product,
        CAST(Id AS VARCHAR(1000)) AS hierarchy_path  -- 存储层级路径，用字符串拼接节点ID
    FROM your_table
    WHERE Parent IS NULL

    UNION ALL

    -- 第二步：递归遍历子节点，拼接层级路径
    SELECT 
        t.Id, 
        t.Parent, 
        t.Product,
        CONCAT(ph.hierarchy_path, '|', t.Id) AS hierarchy_path  -- 用分隔符避免ID拼接冲突
    FROM your_table t
    INNER JOIN product_hierarchy ph 
        ON t.Parent = ph.Id
)
-- 第三步：按产品分区，基于层级路径排序生成行号
SELECT 
    Id,
    Parent,
    Product,
    ROW_NUMBER() OVER (PARTITION BY Product ORDER BY hierarchy_path) AS `Row`
FROM product_hierarchy
ORDER BY Product, `Row`;

逻辑说明 #

1. 递归构建层级路径：
   • 先筛选出每个产品的根节点（Parent为NULL），初始路径为节点自身ID。
   • 递归关联子节点，将子节点ID拼接到父节点的路径后，形成完整的层级路径（比如Product A的路径为a1aa|bx2a|aa2p|zzba）。通过路径排序，就能保证父子节点的顺序。
2. 分区生成行号：
   • 用ROW_NUMBER() OVER (PARTITION BY Product ORDER BY hierarchy_path)，按产品分组，在每个分组内按层级路径排序，生成从1开始的连续行号，完全匹配你的需求。

大表性能优化建议 #

针对5亿行的超大规模表，必须通过索引和执行计划优化避免性能瓶颈：

• 给Parent字段建立B树索引：递归过程中需要频繁通过Parent关联父节点，索引能将关联操作的时间复杂度从O(n)降到O(log n)。
• 给Product字段建立分区索引：按Product分区后，递归和行号计算可以在分区内独立执行，减少数据扫描范围。
• 优化路径存储：如果ID是固定长度的字符串/数字，可以用二进制或固定长度的拼接方式，减少路径字段的内存占用，提升排序效率。
• 物化递归结果：如果数据库支持，可以将递归CTE的结果临时写入分区表，避免重复计算。

示例验证 #

用你提供的测试数据执行上述SQL，会得到完全符合期望的结果：

内容的提问来源于stack exchange，提问作者Diogo de Bem