“This is the second day of my participation in the Gwen Challenge in November. See 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.

Take a chestnut

The scene that

  1. Services need to obtain user details in multiple scenarios, some of which can be obtained directly by querying the user table, some of which need to be obtained by querying the association table, and some of which only save the associated ID and do not create the association table separately, and need to write the function value separately.
  2. Since it needs to be called in multiple scenarios, it is a basic optimization idea to encapsulate it into a public method and let multiple scenarios uniformly call the public method.
  3. As mentioned above, we need to analyze which operations are time-consuming, how to optimize time-consuming operations, and whether the number of SQL queries can be reduced.

For example:

  1. In the following code example, we have encapsulated the CommonRender class, all the methods that can be output uniformly are here
  2. The following code annotates before and after optimization
  3. Before optimization: in each query according to the saved ID, go to the database query; If the list page returns 30 pieces of data at a time, this section will require 30 SQL queries.
  4. After optimization: batch value was used in advance, and a function named _renderHobby was written. Only 1 SQL was required.
  5. This greatly reduces SQL queries and improves program response speed.
<? php namespace App\Render; . . . class CommonRender extends BaseRender { public static function renderUserinfo($data, $hobbyInfo = []) { if (! is_array($data)) { return []; } $ret = [ 'uid' => !isset($data['id']) ? 0 : $data['id'], 'userid' => !isset($data['userid']) ? '' : $data['userid'], 'username' => !isset($data['username']) ? '' : $data['username'], 'usericon' => !isset($data['usericon']) ? [] : $data [' usericon],... / / / / 'hobby' = > before optimization! Isset ($data [' hobby '])? [] : HobbyInfo: : getByIds ($data [' hobby ']), / / 'hobby' = > after optimization! Isset ($data [' hobby '])? [] : self::_renderHobby($data['hobby'], $hobbyInfo), . . . if (! empty($ret['birth'])) { $ret['zodiacSign'] = Utility::getZodiacSign($ret['birth']); } else { $ret['zodiacSign'] = '';  } return $ret; } protected static function _renderHobby($userHobby, $hobbyInfo) { $ret = [];  if ($userHobby) { $userHobbyIds = explode(',', $userHobby);  foreach ($userHobbyIds as $key => $userHobbyId) { $ret[$key] = $hobbyInfo[$userHobbyId]; } } return $ret; Public static function renderListCardUserinfo($data) {..}}Copy the code

Further optimization

The above code has been optimized for performance, but it’s not elegant enough.

There are many scenarios for obtaining single user information, such as editing, logging in, viewing single user information, etc. In this case, do I still batch query in advance every time? That’s a lot of work to do.

Let’s further optimize:

  1. Render encapsulates a layer inside the render method. If there is no external input or empty array, query db again to get the required data source.
<? php namespace App\Render; Public static function renderUserinfo($data, $hobbyInfo = []) {public static function renderUserinfo($data, $hobbyInfo = []) { Batch query external incoming, reduce the number of SQL queries; $hobbyInfo =! Single query in render empty($hobbyInfo) ? $hobbyInfo : HobbyInfo::getAllInfo(); if (! is_array($data)) { return []; } $ret = [ 'uid' => !isset($data['id']) ? 0 : $data['id'], 'userid' => !isset($data['userid']) ? '' : $data['userid'], 'username' => !isset($data['username']) ? '' : $data['username'], 'usericon' => !isset($data['usericon']) ? [] : $data [' usericon],... / / / / 'hobby' = > before optimization! Isset ($data [' hobby '])? [] : HobbyInfo: : getByIds ($data [' hobby ']), / / 'hobby' = > after optimization! Isset ($data [' hobby '])? [] : self::_renderHobby($data['hobby'], $hobbyInfo), . . . if (! empty($ret['birth'])) { $ret['zodiacSign'] = Utility::getZodiacSign($ret['birth']); } else { $ret['zodiacSign'] = '';  } return $ret; } protected static function _renderHobby($userHobby, $hobbyInfo) { $ret = [];  if ($userHobby) { $userHobbyIds = explode(',', $userHobby);  foreach ($userHobbyIds as $key => $userHobbyId) { $ret[$key] = $hobbyInfo[$userHobbyId]; } } return $ret; Public static function renderListCardUserinfo($data) {..}}Copy the code
  1. In this way, the methods used to obtain individual user profiles do not need to be modified.
Public function editUserInfo(Request) {$userInfo = userInfo ::editUserById($this-> _userID, $Request); return [ 'user' => CommonRender::renderUserinfo($userInfo) + UserInfo::formatCoverAndPickedFootprint($userInfo) ]; }Copy the code

The performance comparison

Batch access to user information comparison: immediate performance improvement.

  1. For example, 30 user data are taken each time, and 30 db queries are required for the expectation part before hobbies and occupations are obtained.

  2. After optimization, only 3 db queries are required.

public static function getBatchUserIntro($userid, $userList) { $retData = []; if (empty($userList)) { return $retData; $hobbyInfo = hobbyInfo ::getAllInfo(); $professionInfo = ProfessionInfo::getAllInfo(); $expectInfo = ExpectInfo::getAllInfo(); foreach ($batchUserInfo as $item) { $retData[$item['userid']] = array_merge( ['wxnumber' => Utility::maskWxnumber($item['wxnumber'], $batchExchangeStatus[$item['userid']] == UserUserWeixinExchange::TYPE_TRUE)] + CommonRender::renderUserinfo($item, $hobbyInfo, $professionInfo, $expectInfo); } . . . return $retData; }Copy the code

Pay attention to

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

Participating in interactive

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