MySQL 分级授权信息查询优化方案

问题分析

您需要高效查询用户被授权的分级结构(D、E、F级别)并支持分页功能。这类层级授权查询在权限管理系统中很常见，但如果不优化容易导致性能问题。

优化方案

1. 数据库表结构优化

假设您的授权表结构如下：

CREATE TABLE user_authorization (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    level_type ENUM('D', 'E', 'F') NOT NULL,
    level_id INT NOT NULL,
    grant_time DATETIME NOT NULL,
    INDEX idx_user_level (user_id, level_type, level_id),
    INDEX idx_level_type (level_type, level_id)
);

2. 高效查询方案

方案一：使用JOIN优化

SELECT a.level_type, a.level_id, l.name AS level_name
FROM user_authorization a
JOIN level_data l ON a.level_type = l.level_type AND a.level_id = l.level_id
WHERE a.user_id = ?
ORDER BY a.level_type, a.level_id
LIMIT ?, ?;

方案二：使用子查询分页（大数据量更优）

SELECT a.level_type, a.level_id, l.name AS level_name
FROM (
    SELECT level_type, level_id
    FROM user_authorization
    WHERE user_id = ?
    ORDER BY level_type, level_id
    LIMIT ?, ?
) a
JOIN level_data l ON a.level_type = l.level_type AND a.level_id = l.level_id;

3. 缓存策略

对于频繁访问的授权数据，可以使用缓存： - Redis缓存用户的分级授权信息 - 设置合理的过期时间(如5-10分钟) - 当授权变更时主动清除缓存

4. 分页优化技巧

-- 使用"记住上次位置"分页法(性能最佳)
SELECT a.level_type, a.level_id, l.name AS level_name
FROM user_authorization a
JOIN level_data l ON a.level_type = l.level_type AND a.level_id = l.level_id
WHERE a.user_id = ? 
AND (a.level_type > ? OR (a.level_type = ? AND a.level_id > ?))
ORDER BY a.level_type, a.level_id
LIMIT ?;

5. 复合索引建议

确保有以下索引：

ALTER TABLE user_authorization ADD INDEX idx_user_level_paging (user_id, level_type, level_id);

性能对比

实施建议

1. 根据数据量选择合适的分页方法
2. 确保索引正确建立
3. 考虑引入缓存层
4. 对于超大数据集，考虑分区表或分库分表

需要更具体的优化建议，请提供您的实际表结构和查询模式。