高效获取用户授权分级结构并支持分页查询的方案

问题分析

获取用户被授权的分级结构并支持分页查询需要考虑以下几个关键点： 1. 数据结构设计 - 如何表示分级结构 2. 权限验证 - 如何确定用户对各级节点的访问权限 3. 查询效率 - 如何高效获取数据并支持分页 4. 缓存策略 - 如何减少重复计算

解决方案

1. 数据结构设计

推荐使用以下两种模型之一：

方案A：闭包表(Closure Table)

CREATE TABLE hierarchy (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  ...其他属性
);

CREATE TABLE hierarchy_closure (
  ancestor INT,
  descendant INT,
  depth INT,
  PRIMARY KEY (ancestor, descendant),
  FOREIGN KEY (ancestor) REFERENCES hierarchy(id),
  FOREIGN KEY (descendant) REFERENCES hierarchy(id)
);

方案B：路径枚举(Path Enumeration)

CREATE TABLE hierarchy (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  path VARCHAR(1000), -- 如 "/1/4/7/" 表示1→4→7
  ...其他属性
);

2. 权限验证设计

CREATE TABLE user_hierarchy_permission (
  user_id INT,
  node_id INT,
  permission_type ENUM('read', 'write', 'admin'),
  PRIMARY KEY (user_id, node_id),
  FOREIGN KEY (node_id) REFERENCES hierarchy(id)
);

3. 查询实现

使用闭包表的分页查询示例

-- 获取用户有权限访问的节点及其子节点(分页)
SELECT h.* 
FROM hierarchy h
JOIN hierarchy_closure hc ON h.id = hc.descendant
JOIN user_hierarchy_permission up ON up.node_id = hc.ancestor
WHERE up.user_id = ? AND up.permission_type = 'read'
GROUP BY h.id
ORDER BY h.id
LIMIT ? OFFSET ?;

使用路径枚举的分页查询示例

-- 获取用户有权限访问的节点及其子节点(分页)
SELECT h.*
FROM hierarchy h
JOIN (
  SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING(h2.path, LENGTH(h1.path)), '/', 1) as node_id
  FROM hierarchy h1
  JOIN user_hierarchy_permission up ON up.node_id = h1.id
  JOIN hierarchy h2 ON h2.path LIKE CONCAT(h1.path, '%')
  WHERE up.user_id = ? AND up.permission_type = 'read'
) AS accessible_nodes ON accessible_nodes.node_id = h.id
ORDER BY h.id
LIMIT ? OFFSET ?;

4. 性能优化建议

1. 索引优化：

   • 确保所有关联字段都有索引
   • 对于路径枚举，考虑对path字段添加前缀索引

2. 缓存策略：

   • 缓存用户权限树结构
   • 实现多级缓存(Redis + 本地缓存)

3. 预计算：

   • 定期预计算热门用户的权限视图
   • 考虑使用物化视图

4. 分页优化：

   • 使用基于游标的分页而非OFFSET
   • 实现"无限滚动"式分页

实现示例(伪代码)

def get_authorized_hierarchy(user_id, page=1, page_size=10):
    # 检查缓存
    cache_key = f"user_hierarchy:{user_id}:{page}:{page_size}"
    cached = cache.get(cache_key)
    if cached:
        return cached

    # 数据库查询
    query = """
        WITH RECURSIVE user_accessible_nodes AS (
            -- 基础查询：用户直接有权限的节点
            SELECT node_id FROM user_hierarchy_permission 
            WHERE user_id = %s AND permission_type = 'read'

            UNION

            -- 递归查询：所有子节点
            SELECT hc.descendant
            FROM user_accessible_nodes uan
            JOIN hierarchy_closure hc ON hc.ancestor = uan.node_id
        )
        SELECT h.* FROM hierarchy h
        JOIN user_accessible_nodes uan ON h.id = uan.node_id
        ORDER BY h.id
        LIMIT %s OFFSET %s
    """
    offset = (page - 1) * page_size
    results = db.execute(query, (user_id, page_size, offset))

    # 设置缓存
    cache.set(cache_key, results, timeout=300)

    return results

扩展考虑

1. 权限继承：实现权限沿层级继承的机制
2. 实时更新：当权限变更时，如何使缓存失效
3. 批量查询：支持一次查询多个用户的分级结构
4. 审计日志：记录权限访问历史

选择具体实现方案时，应根据您的具体业务场景、数据规模和性能要求来决定。