使用Sequelize关联查询MySQL时大量权限数据致请求耗时异常过长

你的查询耗时剧增核心原因大概率是多关联嵌套导致的笛卡尔积爆炸，同时可能伴随关联表缺少索引的问题：

1. 当你同时include多个关联（company、companies、RoleData+permissions）时，Sequelize会生成多表JOIN的SQL。如果permissions有50条数据，加上其他关联的表数据，最终返回的结果集行数会是各关联数据量的乘积（比如company关联3个模型各1条，companies有2条，permissions50条，结果集就是132*50=300行）。Sequelize需要把这几百行重复数据重新组装成嵌套的JSON对象，这个过程会消耗大量CPU和内存，导致耗时飙升。
2. 如果RolePermission中间表的roleId和permissionId没有建立索引，查询权限时会触发全表扫描，数据量越大越慢。

1. 拆分查询，避免笛卡尔积 #

把原本一次性的多关联查询拆分成多个独立查询，手动组装结果，彻底避免JOIN带来的笛卡尔积问题：

// 先查用户基础数据+公司相关关联
const userBase = await this.Model.findByPk(user.id, {
    attributes: { exclude: ['companiesIds'] },
    include: [
        {
            model: Company,
            as: 'company',
            required: true,
            include: [/* 3个模型 */]
        },
        {
            model: Company,
            as: 'companies',
            attributes: [... attributes ...],
            through: { attributes: [] }
        }
    ]
});

// 单独查询该用户角色的权限
const rolePermissions = await Roles.findByPk(userBase.RoleData.id, {
    attributes: [],
    include: [
        {
            model: Permissions,
            as: 'permissions',
            attributes: ['id', 'action_name'],
            through: { attributes: [] }
        }
    ]
});

// 手动组装数据
const userData = {
    ...userBase.toJSON(),
    RoleData: {
        ...userBase.RoleData.toJSON(),
        permissions: rolePermissions.permissions
    }
};

2. 给关联表添加索引 #

在RolePermission模型的定义中，给roleId和permissionId添加索引：

// RolePermission模型定义示例
const RolePermission = sequelize.define('RolePermission', {
    // 其他字段...
}, {
    indexes: [
        { fields: ['roleId'] },
        { fields: ['permissionId'] },
        // 可选：添加联合索引，优化双向查询
        { fields: ['roleId', 'permissionId'], unique: true }
    ]
});

同时确保User表的roleId字段也有索引，加快和Roles表的关联查询。

3. 使用separate: true让关联查询单独执行 #

在Permissions的include配置中添加separate: true，告诉Sequelize单独发送查询获取权限，而不是和主查询JOIN，避免笛卡尔积：

const userData = await this.Model.findByPk(user.id, {
    attributes: { exclude: ['companiesIds'] },
    include: [
        {
            model: Company,
            as: 'company',
            required: true,
            include: [/* 3个模型 */]
        },
        {
            model: Company,
            as: 'companies',
            attributes: [... attributes ...],
            through: { attributes: [] }
        },
        {
            model: Roles,
            as: 'RoleData',
            required: false,
            attributes: ['id', 'name', 'priority'],
            include: [
                {
                    model: Permissions,
                    as: 'permissions',
                    attributes: ['id', 'action_name'],
                    through: { attributes: [] },
                    separate: true, // 关键配置：单独查询权限
                    limit: null // 如果有默认limit需要去掉，确保获取所有权限
                },
            ]
        }
    ]
});

4. 排查冗余数据传输 #

确保所有attributes配置只保留需要的字段，不要返回冗余数据，减少网络传输和Sequelize的对象处理开销。

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