“This is the fourth day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

How to improve the running speed of the program and reduce the server pressure is a problem that the server development must face.

Simple and unsophisticated rule: Do not operate on DB, including relational databases and NoSql, in a for loop.

Depending on your business scenario, get the data in bulk before the for loop, and get the results in bulk with as few SQL queries as possible. Data matching is assembled in the for loop.

Last article performance optimization reflection: Do not operate DB in the for loop, recommended to the home page also received everyone interactive comments, continue to work hard, advanced version.

Note: continue the last document of the demo arrangement, do not repeat the code, we suggest that read the first content.

The scene that

  1. We allow the user to choose a career, the system prefabricated a number of career labels; Also open the function of custom career tag, no limit on the number of custom tags. Allows users to select 2 career tabs while editing profiles.
  2. Discover that there is really a wide variety of user-defined occupations, orders of magnitude larger and larger as the business grows; For example, there are 2,000 career labels now, and in the future there could be 20,000, or even 200,000.
  3. In this case, the way we used to batch query the full data before the for loop and use custom functions to match the data in the for loop is too low hit ratio to be wasteful.
  4. For example, each list returns 30 user information, and each user selects 2 career tags. The maximum number of tags is 60. The total number of career tags I looked up was 2,000, with a 3% hit rate; If the class tag reaches 20,000, the hit rate is only 0.3%.

Their thinking

  1. First, we don’t operate on DB in the for loop, and that’s the same general principle
  2. The core of the above problem is the low hit rate, that is, the full check of a lot of data that is not used
  3. The solution is to batch query only matched label data:
    1. Get the career ids saved by 30 users in the User table
    2. 30 user ids were removed and reassembled
    3. In the career table, query by having for the matching career label
    4. Other logic remains unchanged, but only the data source is replaced: the original data source is full data, and the optimized data source is precisely hit data.

Once I had a clear idea, I started coding

Code examples:

For the sake of compactness, code that is not relevant to the article is omitted from the code section, using three vertical ones. Omitted.

  1. Core code: extractionrenderUserInfoThis function is called in the for loop to get the data source before the for loop.
<? php namespace App\Render; . . . class CommonRender extends BaseRender { public static function renderUserinfo($data, $hobbyInfo = [],$professionInfo = []) { $hobbyInfo = ! empty($hobbyInfo) ? $hobbyInfo : HobbyInfo::getAllInfo(); // Special processing, because professional users can customize the number to always grow incomplete check data; $professionInfo =! $professionInfo =! $professionInfo =! empty($professionInfo) ? $professionInfo : (isset($data['profession']) ? ProfessionInfo::getByIds($data['profession']) : []); if (! is_array($data)) { return []; } $ret = [... / / / / 'hobby' = > before optimization! Isset ($data [' hobby '])? [] : HobbyInfo::getByIds($data['hobby']), // 'profession' => !isset($data['profession']) ? [] : ProfessionInfo::getByIds($data['profession']), isset($data['hobby'])? [] : self::_renderHobby($data['hobby'], $hobbyInfo), 'profession' => !isset($data['profession']) ? [] : self::_renderProfession($data['profession'], $professionInfo), . . . return $ret; } }Copy the code
  1. isset()Check, in case the incoming data does not exist, indicating that the array is out of bounds.

I’ve also put together an article on how to keep array subscripts out of bounds for those interested.

protected static function _renderProfession($userProfession, $professionInfo)
{
    $ret = [];
    if ($userProfession) {
        $userProfessionIds = explode(',', $userProfession);
        foreach ($userProfessionIds as $key => $userProfessionId) {
            if (isset($professionInfo[$userProfessionId])) {
                $ret[$key] = $professionInfo[$userProfessionId];
            }
        }
    }
    return $ret;
}
Copy the code
  1. callcommonRender()To show how the data source comes from.
public static function getBatchUserIntro($userid, $userList) { $retData = []; if (empty($userList)) { return $retData; } . . . $hobbyInfo = HobbyInfo::getAllInfo(); $professionIds = array_column($batchUserInfo, 'profession'); $professionIds = implode(',', $professionIds); $professionIds = array_unique(explode(',', $professionIds)); $professionInfo = ProfessionInfo::batchGetByIds($professionIds); foreach ($batchUserInfo as $item) { $retData[$item['userid']] = CommonRender::renderUserinfo($item, $hobbyInfo, $professionInfo, $expectInfo); } return $retData; }Copy the code
  1. Package tool method, through the ID array batch data, made a special judgment, compatible value is empty.
Public static function batchGetByIds($ids = []) {foreach ($ids as $key => $id) {if (empty($id)) { unset($ids[$key]); } } if (empty($ids)) { return []; } return self::query()->selectRaw('id,name,pid') ->whereIn('id', $ids) ->get() ->keyBy('id') ->toArray(); }Copy the code

The core code is the above four parts

The performance comparison

Take this as an example: each list returns 30 user information, each user selects 2 occupation tags, the maximum number of tags is 60;

Before optimization: the number of professional labels checked in full is 2,000, and the hit rate is only 3%; If the class tag reaches 20,000, the hit rate is only 0.3%.

After optimization: the number of professional labels detected in full quantity is 2,000, and the hit rate is 100%; If you hit 20,000 class tags, the hit rate is still 100%.

Reflection summary

Program design must be combined with business scenarios, there is no absolutely correct program design;

As the business grows, otherwise robust programming can run into problems, and technical people must be able to grow with the business.

Participating in interactive

If you have any good ideas, please feel free to comment in the comments section