闭包表解决上下级关系级联查询性能问题

闭包表解决上下级关系级联查询性能问题

• 代码示例：

1.用户表增加上级id

ALTER TABLE SystemUser
ADD parentId int;

2.增加闭包表

CREATE TABLE UserClosure (
    ancestorId int NOT NULL,
    descendantId int NOT NULL,
    depth INT NOT NULL,
    PRIMARY KEY (ancestorId, descendantId)
);
-- 索引加速
CREATE INDEX idx_descendant
    ON UserClosure (descendantId);

3.mybatis的xml文件方法

--修改上级时的事务方法1
<delete id="delete4updateParent" >
    DELETE FROM UserClosure
    WHERE descendantId IN (SELECT descendantId FROM UserClosure WHERE ancestorId = #{ancestorId})
      AND ancestorId IN (SELECT ancestorId FROM UserClosure WHERE descendantId = #{ancestorId} AND ancestorId != descendantId);
</delete>
--修改上级时的事务方法2
<insert id="insert4updateParent" >
    INSERT INTO UserClosure (ancestorId, descendantId, depth)
    SELECT super.ancestorId, sub.descendantId, super.depth+sub.depth+1
    FROM UserClosure AS super
             CROSS JOIN UserClosure AS sub
    WHERE super.descendantId = #{descendantId}
      AND sub.ancestorId = #{ancestorId};
</insert>

--带上级id创建用户，上级必需要存在自闭包数据记录如上级id：0，记录（0，0，0）
<insert id="createUserWithParentId" >
    INSERT INTO UserClosure (ancestorId, descendantId, depth)
    SELECT ancestorId, #{ancestorId}, depth+1 FROM UserClosure
    WHERE descendantId = #{descendantId}
    UNION ALL
    SELECT #{ancestorId}, #{ancestorId}, 0;
</insert>

4.数据表：

···
原有查询
···

union SELECT t.* FROM ticket t JOIN userclosure uc ON t.handler_id = uc.descendantid WHERE uc.ancestorid = #{userId}