When the performance of the site is experiencing bottlenecks

First, the problem description
1. Origin. I am doing an online test system project, I want the user to answer every question has corresponding record: First, record each question the correct, the wrong number of times, and the second is to record the user each wrong problem, used to form the user's wrong problem set.

2. Implementation. Because there are an indefinite number of questions in the test paper, I use the loop to determine if the user is answering correctly, record the data in the loop, and write to the database.

3. Bottlenecks. Since each submission of an answer sheet results in dozens of or more database write or update operations, it can take a lot of time.
Second, the problem analysis

1. Because each question should be recorded two times: one is to update the number of questions on the wrong, two times is recorded to the wrong focus, if each test paper has 20 questions, then each answer will be 40 database operations, resulting in a large database pressure.
2. Because each question is a different database record, it is difficult to batch update (some new records, some update records).
3. If a large number of users commit concurrently, the server may crash and be slow. Although my small site usually does not have so many people to patronize, but my own development of the system can not eventually become unusable waste bar, so must be optimized under!
Third, the solution of ideas and solutions

1. Because of the large number of database operations, my first consideration is to use Redis to improve performance.
2. Since the operation of updating data has both new and updated records, the update operation and operation must be logically separated.
3. I am using the thinkphp framework development, the model layer comes with a batch of new functions AddAll (), so the operation of the new data is solved with it, and then through the online search or write a function, assemble batch update SQL statements, like the following statement structure:
Updatecategories SET
Display_order = case ID
When 1 then 3
When 2 then 4
When 3 then 5
END,
title = case ID
When 1 Then ' New ' Title 1 '
When 2 Then ' New ' Title 2 '
When 3 Then ' New ' Title 3 '
END
WHERE ID in (All-in-a-

4. Use the Redis hash table to record the data to be updated or new, to trigger the database operation at the right time, to manipulate Redis data through PHP, and to delete those Redis data after successful execution to solve the bottleneck problem.

Attached: The following is an optimization of the error records of the test questions, similar to the optimized code used for the wrong set, so that only the former code is shown.
5.redis Recording Process:
$check? $field = ' r ': $field = ' w ';//check Right or wrong
$redis = new \redis ();
$redis->connect (' 127.0.0.1 ', 6379);
$redis->hincrby (' qid_check_log ', ' qid '. $qid. '. $field, 1); Key value Accumulation 1

6. Dump Redis data into mysql:
$redis = new \redis ();
$redis->connect (' 127.0.0.1 ', 6379);
$data _cache = $redis, Hgetall (' Qid_check_log ');
$temp = Array ();//data to be updated
$i = 0;

//Data to be added foreach ($data _cache as $key + = $num) {$arr = explode ('. ', $key); $qid = $arr [1]; $field = $arr [2]; $temp [$i] [' qid '] = $qid; $ids [] = $qid;//The question ID that needs to be updated $temp [$i] [$field] = $num; $redis-Hdel ($qid _check_log, $key); $i + +;} if (empty ($ids)) return true;//If there is no update, return//Get the original data $map[' qid ' = Array (' in ', $ids); $old _data = M (' Questionlog ') where ($map), select (), $old _data2 = Array (), foreach ($old _data as $one) {$old _data2[$one [' qid ']] = $one;} unset ($old _data);//merge data foreach ($temp as & $one) {if (Isset ($one [' R ')]) {$one [' r '] = $old _data2[$one [' qid ']][' r '] + $one [' R ']; }else{$one [' r '] = $old _data2[$one [' qid ']][' R ']; } if (Isset ($one [' W '])) {$one [' w '] = $old _data2[$one [' qid ']][' W '] + $one [' W ']; }else{$one [' w '] = $old _data2[$one [' qid ']][' W ']; }} $re = Batch_update (' Questionlog ', $temp, ' qid ');//Perform batch update PostScript: In fact, I did not design the program at the time of writing, if the design is reasonable, may not need Redis can also complete this optimization, but, Also just because of this opportunity, let me in the project really use Redis, feel its speed advantage, haha! 

When web site performance is experiencing bottlenecks