MySQL4下高性能过滤层级权限表无有效父节点数据的方案

核心思路 #

因为业务侧权限层级最多4层，且MySQL 4不支持递归CTE，存储过程递归的性能也很差，直接采用固定层级自关联的方案实现，全流程走索引等值关联，没有额外开销，性能最高，完全匹配老版本MySQL的语法支持范围。

前置准备 #

首先给权限表建关联用的索引，这步是性能基础，只需要执行一次：

-- 如果id已经是主键，不需要重复建第一个索引，主键默认自带索引
CREATE INDEX idx_perm_id ON permissions(id);
CREATE INDEX idx_perm_parentid ON permissions(parentId);

注意：原SQL框架中用ON p.id = toto.id OR p.id = titi.id OR p.id = tutu.id的写法在MySQL4中性能极差，优化器无法为OR跨表关联命中索引，会触发权限表全表扫描，大数据量下根本跑不动，必须替换。

最终实现SQL #

直接替换原SQL的关联逻辑即可，不需要改动你原有SET部分的更新赋值逻辑：

UPDATE aggregat_table at
  -- 先把三个业务表的有效权限ID去重合并，避免OR关联的性能问题
  INNER JOIN (
    SELECT id FROM toto
    UNION
    SELECT id FROM titi
    UNION
    SELECT id FROM tutu
  ) valid_perm
  -- 关联当前权限节点
  INNER JOIN permissions p1 
    ON p1.id = valid_perm.id
  -- 关联1级父节点（直接上级）
  LEFT JOIN permissions p2 
    ON p1.parentId = p2.id
  -- 关联2级父节点（爷爷节点）
  LEFT JOIN permissions p3 
    ON p2.parentId = p3.id
  -- 关联3级父节点（曾祖父节点，覆盖最多4层权限的业务场景）
  LEFT JOIN permissions p4 
    ON p3.parentId = p4.id
  WHERE
    -- 根权限直接保留
    p1.parentId = 0
    -- 2级权限：父节点存在且父节点是根
    OR (p1.parentId <> 0 AND p2.id IS NOT NULL AND p2.parentId = 0)
    -- 3级权限：父、爷节点都存在，爷爷节点是根
    OR (p2.parentId <> 0 AND p3.id IS NOT NULL AND p3.parentId = 0)
    -- 4级权限：父、爷、曾祖节点都存在，曾祖节点是根
    OR (p3.parentId <> 0 AND p4.id IS NOT NULL AND p4.parentId = 0)
  SET -- 此处保留原有更新赋值逻辑即可

逻辑说明 #

• 所有关联都是等值匹配，完全命中提前建好的索引，MySQL4优化器可以正常生成高效执行计划，不会出现全表扫描，百万级数据下也能快速跑完
• 过滤逻辑完全匹配需求：只要权限链路中任意一层父节点缺失，对应LEFT JOIN的结果就会返回NULL，无法满足WHERE条件，会被直接过滤。用给出的示例数据验证：
  • id=5的父节点是4，不存在，p2返回NULL，被过滤
  • id=7的父节点是6，不存在，p2返回NULL，被过滤
  • id=8的父节点是7，7的父节点6不存在，p3返回NULL，被过滤
  • 最终保留的正好是id=1、2、3三个有效权限，和预期结果完全一致

扩展说明 #

如果后续业务权限层级最多增加到5层，只需要多补一次LEFT JOIN关联p5节点，再在WHERE中增加对应层级的判断分支即可，维护成本极低。

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