如何优化Sequelize ORM中健身应用的数据创建查询速度？

数据结构说明 #

• User 与 Program 为一对一关系
• Program 包含多个 Workout
• Workout 与 Exercise 是多对多关系
• Workout 还包含多个 Superset，Superset 与 Exercise 也为多对多关系

性能问题 #

我正在开发一款健身应用，算法会创建包含9个Workout的Program，每个Workout包含6-8个Exercise，每个Exercise需从数据库匹配合适条目并关联到Workout。整体会创建1个Program、9个Workout及54-72个Exercise，但整个流程耗时20-25秒，速度过慢。已尝试使用raw、attributes选项优化，以下是创建单个Workout的算法代码：

public static async generateWorkout(body: WorkoutRequestApi, currentDay: number, workoutCounter: number) {

        let existing: string[] = [];
        let exercise: ExercisesModels | null;
        const workoutId: string = randomUUID();

        await Workout.create({
            uuid: workoutId,
            isFinished: false,
            programId: body.progId,
            type: body.programOptions.types[workoutCounter]
        })
        await Superset.create({uuid: workoutId})

        for (let i = 0; i < body.programOptions.workouts[currentDay].length; i++) {

            console.log("DAY: " + currentDay + " I: " + i)
            const currentExerciseModel = body.programOptions.workouts[currentDay][i];
            let unique: boolean = false;
            console.log(currentExerciseModel)

            if (currentExerciseModel.isSuperset) {

                while (!unique) {

                    const firstExercise: ExercisesModels | null = await ExerciseUtil.findWorkoutExercise(currentExerciseModel, currentExerciseModel.hasPriority ? true : false)
                    const secondExercise: ExercisesModels | null = await ExerciseUtil.findFixedExercise(body.programOptions.workouts[currentDay][i + 1])
                    if (firstExercise && secondExercise) {
                        if (!existing.includes(firstExercise.uuid)) {

                            unique = true
                            existing.push(firstExercise.uuid)
                            existing.push(secondExercise.uuid)

                            console.log("INSERTING " + firstExercise.uuid + " and " + secondExercise.uuid)

                            exercise = await this.modelToExercises(firstExercise, {
                                    reps: currentExerciseModel.reps,
                                    sets: currentExerciseModel.sets,
                                    order: i
                                }
                            )
                            console.log(exercise)
                            await SupersetExercise.create({
                                ExerciseUuid: exercise.uuid,
                                SupersetUuid: workoutId
                            })

                            exercise = await this.modelToExercises(secondExercise, {
                                    reps: currentExerciseModel.reps,
                                    sets: currentExerciseModel.sets,
                                    order: i+1
                                }
                            )
                            await SupersetExercise.create({
                                ExerciseUuid: exercise.uuid,
                                SupersetUuid: workoutId
                            })
                        }
                    }
                }


                i++;

                if (i >= body.programOptions.workouts[currentDay].length)
                    break

            } else {
                if (currentExerciseModel.isFixed === true) {
                    console.log("FIXED EXERCISE")
                    exercise = await ExerciseUtil.findFixedExercise(currentExerciseModel);

                    if (exercise) {
                        console.log("FOUND FIXED" + exercise.name)

                        exercise = await this.modelToExercises(exercise, {
                                reps: currentExerciseModel.reps,
                                sets: currentExerciseModel.sets,
                                order: i
                            }
                        )
                        try {
                            await WorkoutExercise.create({
                                WorkoutUuid: workoutId,
                                ExerciseUuid: exercise.uuid
                            });
                        } catch (e) {
                            console.log(<Error>e)
                        }


                    }


                } else {
                    while (!unique) {

                        exercise = await ExerciseUtil.findWorkoutExercise(currentExerciseModel, i <= 2)

                        if (exercise) {
                            if (!existing.includes(exercise.uuid)) {
                                unique = true
                                existing.push(exercise.uuid)


                                exercise = await this.modelToExercises(exercise, {
                                    reps: currentExerciseModel.reps,
                                    sets: currentExerciseModel.sets,
                                    order: i
                                })


                                try {
                                    await WorkoutExercise.create({
                                        WorkoutUuid: workoutId,
                                        ExerciseUuid: exercise.uuid
                                    });
                                } catch (e) {
                                    console.log(<Error>e)
                                }
                            }
                        }

                    }

                }


            }
        }
}

优化建议 #

1. 用批量创建替代单条数据库请求 #

目前代码里每个create都是单独的数据库调用，9个Workout加上几十条关联数据，会产生上百次请求，这是耗时的核心原因。改成批量创建：

• 提前收集所有需要创建的SupersetExercise、WorkoutExercise数据，最后调用bulkCreate一次性插入
• 示例：

// 初始化数组
const supersetExerciseList = [];
// 循环中收集数据
supersetExerciseList.push({
  ExerciseUuid: exercise.uuid,
  SupersetUuid: workoutId
});
// 循环结束后批量插入
await SupersetExercise.bulkCreate(supersetExerciseList);

2. 并行执行数据库查询 #

代码中很多查询是串行执行的（比如超级组里的两个Exercise查询），用Promise.all并行处理可以减少等待时间：

// 原串行写法
const firstExercise = await ExerciseUtil.findWorkoutExercise(...)
const secondExercise = await ExerciseUtil.findFixedExercise(...)
// 改成并行
const [firstExercise, secondExercise] = await Promise.all([
  ExerciseUtil.findWorkoutExercise(currentExerciseModel, currentExerciseModel.hasPriority),
  ExerciseUtil.findFixedExercise(body.programOptions.workouts[currentDay][i + 1])
])

3. 优化existing数组的查重效率 #

用Set替代Array存储已选Exercise的UUID，Set.has()的时间复杂度是O(1)，比Array.includes()的O(n)高效得多：

let existing = new Set<string>();
// 检查是否已存在
if (!existing.has(firstExercise.uuid)) {
  // 添加到集合
  existing.add(firstExercise.uuid);
}

4. 移除不必要的日志输出 #

大量console.log会增加IO开销，生产环境建议全部移除，开发时也尽量减少非必要的打印。

5. 优化Exercise查询逻辑 #

• 检查ExerciseUtil.findWorkoutExercise和findFixedExercise是否添加了合适的数据库索引，比如按Exercise类型、优先级等字段建索引，加速查询
• 如果固定Exercise可以提前批量查询，不要在循环内重复发起查询请求

6. 用事务包裹整个创建流程 #

将Workout的创建、关联数据的插入全部放在数据库事务中，既能保证数据一致性，也能减少数据库提交的开销：

import { sequelize } from './your-sequelize-config'; // 替换成你的Sequelize实例

await sequelize.transaction(async (t) => {
  // 创建Workout时传入事务
  await Workout.create({
    uuid: workoutId,
    isFinished: false,
    programId: body.progId,
    type: body.programOptions.types[workoutCounter]
  }, { transaction: t });
  
  // 其他创建操作同样传入transaction: t
  await Superset.create({uuid: workoutId}, { transaction: t });
  
  // 批量创建也支持事务
  await SupersetExercise.bulkCreate(supersetExerciseList, { transaction: t });
});

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