php - 优化具有多个属于它的记录的记录的创建

Question

描述

我正在使用 ReactJS、Laravel 和 MySQL 编写全栈网络应用程序，允许用户创建测验.

我的数据库结构:

测验表

create table quizzes (
    id bigint unsigned auto_increment primary key,
    title varchar(255) not null,
    description text null,
    duration smallint unsigned not null,
    is_open tinyint(1) default 0 not null,
    shuffle_questions tinyint(1) default 0 not null,
    user_id bigint unsigned not null,
    lesson_id bigint unsigned not null,
    created_at timestamp null,
    updated_at timestamp null,
    constraint quizzes_lesson_id_foreign foreign key (lesson_id) references lessons (id) on delete cascade,
    constraint quizzes_user_id_foreign foreign key (user_id) references users (id) on delete cascade
) collate = utf8mb4_unicode_ci;

问题表

create table questions (
    id bigint unsigned auto_increment primary key,
    title text not null,
    description text null,
    image varchar(255) null,
    type enum ('radio', 'checkbox', 'text', 'image') not null,
    is_required tinyint(1) default 0 not null,
    points tinyint unsigned default '0' not null,
    quiz_id bigint unsigned not null,
    created_at timestamp null,
    updated_at timestamp null,
    constraint questions_quiz_id_foreign foreign key (quiz_id) references webagu_24082021.quizzes (id) on delete cascade
) collate = utf8mb4_unicode_ci;

答案表

create table answers (
    id bigint unsigned auto_increment primary key,
    value varchar(1024) null,
    is_correct tinyint(1) default 0 not null,
    quiz_id bigint unsigned not null,
    question_id bigint unsigned not null,
    created_at timestamp null,
    updated_at timestamp null,
    constraint answers_question_id_foreign foreign key (question_id) references questions (id) on delete cascade,
    constraint answers_quiz_id_foreign foreign key (quiz_id) references quizzes (id) on delete cascade
) collate = utf8mb4_unicode_ci;

当用户按下“保存测验”按钮时来自 UI 的数据

//....
axios
    .post('/quizzes', { "quiz": QuizData, "questions": QuestionsData, "answers": AnswersData })
    .then(res => {
        if(201 === res.status) alert('Quiz saved!');
        console.log(res.data)
    });
//....

测验 Controller store方法

public function store(Request $request): JsonResponse
{
    $quizData = $request->input('quiz');
    $questions = $request->input('questions');
    $answers = $request->input('answers');

    $groupedAnswers = Utils::groupBy('_question_id', $answers);

    //DB::beginTransaction();

    $quizData['user_id'] = \auth('api')->user()->id;
    $quiz = Quiz::create($quizData);

    $new_questions = [];
    $new_answers = [];

    foreach ($questions as $question) {
        $question['quiz_id'] = $quiz->id;
        $new_question = Question::create($question);
        $new_questions[] = $new_question;

        $qid = $question['_question_id'];

        if (isset($groupedAnswers[$qid])) {
            $question_answers = $groupedAnswers[$qid];

            foreach ($question_answers as $answer) {
                $answer['quiz_id'] = $quiz->id;
                $answer['question_id'] = $new_question->id;

                $new_answer = Answer::create($answer);
                $new_answers[] = $new_answer;
            }
        }
    }

    //DB::commit();

    $resData = ['quiz' => $quiz, 'questions' => $new_questions, 'answer' => $new_answers];

    return response()->json($resData, 201);
}

我目前的代码算法:

1. 创建测验对象
2. 在foreach 循环中将Quiz::id 分配给Question 对象quiz_id 外键列并创建<
3. 在内部 foreach 循环中，将 Question::id 分配给 Answer 对象 question_id 外键列并创建

问题

此算法创建Q(问题计数)* A(答案计数) SQL 查询 - 这非常慢。

例如，如果测验包含 50 个问题，每个问题有 4 个答案变体，则查询将包含 50 * 4 = 200 个 SQL 查询。

那么，如何改变这个糟糕的解决方案以使其工作得更快呢？

Answer 1

实际上，如果您有 50 个问题，您有:

• 1 查询以创建一个测验
• 50 个查询以创建问题
• 200 个查询以创建答案

总计:251 个查询。

---

如果我没有弄错你的编码，你可以像这样优化你的查询(示例 50 个问题，我在评论区解释):

$input_quiz      = $request->input('quiz');
$input_questions = $request->input('questions');
$input_answers   = $request->input('answers');
$groupedAnswers  = Utils::groupBy('_question_id', $input_answers);

/*********************/

// Create a quiz (1 query)
$quiz = Quiz::create($input_quiz); 

// Create questions (50 queries)
$questions = $quiz->questions()->createMany($input_questions);

// Prepare answers data
$answers = [];

// Loop $questions
foreach ($questions as $key => $question){

    // If I'm not mistaken, the index on the input 
    // will be equal to $questions (starting at 0)
    $qid = $input_questions[$key]['_question_id'];

    if(isset($groupedAnswers[$qid])){
        $question_answers = $groupedAnswers[$qid];

        // Modify answer
        foreach ($question_answers as $_answer){
            $_answer['quiz_id']     = $quiz->id;
            $_answer['question_id'] = $question->id;
            $_answer['created_at']  = now();    // Laravel insert not saved created_at column
            $_answer['updated_at']  = now();    // Laravel insert not saved updated_at column
            $answers[]              = $_answer; // Push it
        }
    }
}

// Then, we will bulk insert using the insert method (1 query)
$answers = Answer::insert($answers);

现在，你有:

• 1 查询以创建一个测验
• 50 个查询以创建问题
• 1 查询以创建答案

总计:52 个查询。

---

我在这种情况下所做的是，只使用 3 个查询。但是要考虑很多，比如使用临时列。不过，我认为您不需要走那么远。