您的位置 首页 php

性能优化反思:不要在for循环中操作DB 进阶版

简单且朴素的原则:不要在for循环中操作DB,包括关系型数据库和NoSql。

我们应该根据自己的业务场景,在for循环之前批量拿到数据,用尽量少的sql查询批量查到结果。 在for循环中进行数据的匹配组装。

场景说明

  1. 我们允许用户选择职业,系统预制了一批职业标签;又开放了自定义职业标签的功能,不限制自定义标签的次数。允许用户编辑资料时选择2个职业标签。
  2. 发现用户自定义的职业真的五花八门,随着业务增长,数量级越来越大;比如目前职业标签是2千个,以后可能有2万个,甚至20万个。
  3. 这种情况下,我们上一篇提到的 在for循环之前批量查询全量数据,在for循环中用自定义函数匹配,避免在for循环中操作DB 的方式命中率太低了,造成了极大的浪费。
  4. 比如:每个列表返回30个用户信息,每个用户选择了2个职业标签,最大标签数量是60;而我全量查到的职业标签数量是2千,命中率只有3%;如果职业标签达到2万个,命中率就只有0.3%了。

解题思路

  1. 首先,在for循环中不操作DB,这个大原则不变
  2. 上述问题的核心是命中率太低,就是全量查了很多用不到的数据
  3. 解决思路就是只批量查询命中的标签数据:
    1. 取到30个用户在user表中保存的职业id
    2. 30个用户的id去重后重组
    3. 在职业表通过whereIn查询匹配的职业标签
    4. 其他逻辑不变,替换的只是数据源:之前的数据源是全量数据,优化后的数据源是精准命中的数据。

思路清晰之后,开始coding

代码示例:

为了行文紧凑,代码段中省略了和文章无关的代码,用竖着的三个.省略。

  1. 核心代码:抽取 renderUserInfo ,统一输出用户信息,这个函数在for循环中调用,获得数据源在for循环之前。
 <?phpnamespace App\Render;...class CommonRender extends BaseRender{    public static function renderUserinfo($data, $hobbyInfo = [],$professionInfo = [])    {        $hobbyInfo = !empty($hobbyInfo) ? $hobbyInfo : HobbyInfo::getAllInfo();        //特殊处理,因为职业用户可以自定义 数字一直增长 不全量查数据;$professionInfo为空时不是批量查询,只查单条记录        $professionInfo = !empty($professionInfo) ? $professionInfo : (isset($data['profession']) ? ProfessionInfo::getByIds($data['profession']) : []);        if (!is_array($data)) {            return [];        }        $ret = [            .            .            .            //优化之前//          'hobby' => !isset($data['hobby']) ? [] : HobbyInfo::getByIds($data['hobby']),//          'profession' => !isset($data['profession']) ? [] : ProfessionInfo::getByIds($data['profession']),//优化之后            'hobby' => !isset($data['hobby']) ? [] : self::_renderHobby($data['hobby'], $hobbyInfo),            'profession' => !isset($data['profession']) ? [] : self::_renderProfession($data['profession'], $professionInfo),            .            .            .        return $ret;    }}  
  1. isset() 判断,避免传入的数据不存在,提示数组越界。

我还整理了一篇 如何避免数组下标越界 ,有兴趣可以阅读一下。

 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;}  
  1. 调用 commonRender() 的代码,即展示数据源是怎么来的。
 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;}  
  1. 封装的工具方法,通过id数组批量获得数据,做了特殊判断,兼容值为空的情况。
 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();}  

核心代码就是上述4部分

性能对比

以此举例:每次列表返回30个用户信息,每个用户选择了2个职业标签,最大标签数量是60;

优化之前:全量查到的职业标签数量为2千,命中率只有3%;如果职业标签达到2万个,命中率就只有0.3%了。

优化之后:全量查到的职业标签数量为2千,命中率为100%;如果职业标签达到2万个,命中率仍然为100%。

反思总结

程序设计一定要结合业务场景,没有绝对正确的程序设计;

随着业务增长原本稳健的程序设计也可能遇到问题,技术人必须能和业务一起成长。

文章来源:智云一二三科技

文章标题:性能优化反思:不要在for循环中操作DB 进阶版

文章地址:https://www.zhihuclub.com/31955.shtml

关于作者: 智云科技

热门文章

评论已关闭

41条评论

  1. What’s up, yup this piece of writing is in fact fastidious and I have learned
    lot of things from it about blogging. thanks.

  2. Exclusion criteria were impotence due to radical prostatectomy, pelvic surgery, penile deformities, or recent history of stroke or spinal cord trauma, severe cardiovascular diseases and or clinically significant renal or hepatic insufficiency

  3. ie cialis handing out art supplies on Pinterest nights To obtain detailed description of the product you need to keep in mind that there is some difference between online and offline drugstores

  4. All calculators have been verified against cancer patients diagnosed between 1987-2007 in the United States Surveillance, Epidemiology and End-Results database

  5. 1 In PCOS and infertility, testosterone levels are elevated, combined with Metabolic Syndrome and insulin resistance. The currently available evidence suggests that addition of estrogen to progesterone in the luteal phase does not increase the probability of pregnancy.

  6. If you ever have a question about your fertility, remember that your primary care physician or gynecologist is an excellent source of information.

  7. To validate the results obtained on mTRPA1, wild type human hTRPA1 expressing CHO were tested by treatment with OXA 2018 Jun; 8 6 767 772

  8. viagra motrin for cold sores Then check the Covered California rate calculator to see whether you qualify for federal premium subsidies based on your income

  9. Natural Steroids with proven effectiveness; Organic components are safe for your health; Contain a lot of useful vitamins and minerals; Finishing cycle doesn t lead to rollback of results; No need for post cycle therapy; Free worldwide delivery; And absolutely NO Needles or Prescriptions Complications associated with them develop unpredictably and carry a high mortality rate

  10. Another example of plasticity at the photoreceptor bipolar cell synapse is the AAV mediated gene therapy to replace retinoschisin RS1 in adult mice Ou et al

  11. Department of Health and Human Services The Health Consequences of Smoking 50 Years of Progress A Report of the Surgeon General

  12. 331 Z BEC ADVANCE WYETH CHC Vitaminas asociadas con minerales Fco 0 Scientific ratings DIF Micromedex Established Probable Suspected Possible Unlikely excellent 1

  13. Escobar del Rey F, Morreale de Escobar G The effect of propylthiouracil, methylthiouracil and thiouracil on the peripheral metabolism of L thyroxine in thyroidectomized L thyroxine maintained rats

  14. 2 Tie2 HSCs arrow can be seen along the endosteal surface in Cxcl12 WT BM, but they were scattered in perisinusoidal region in Cxcl12 cKO BM

  15. Although he did not know the truth, Zhao Ling asked him to say that in the face of the temptation of a top quality spirit stone, he naturally did not know how to refuse A and show significant deficits in body size C and body weight D at their time of death

  16. A carrier assisted ChIP seq method for estrogen receptor chromatin interactions from breast cancer core needle biopsy samples

  17. Therefore, defining postmenopausal only on the basis of an age of 60 years may be erroneous Do I have any on whay advice on what the signs r of getting my hormones back on track

  18. If youГў re keeping score at home, that would be the same Galea who has been the patron saint of human growth hormone, and has had his own well documented problems with the law

  19. Finally, however, we have some decent treatments that do not seem to be well known 92, though with substantial heterogeneity across studies I 2 71

  20. They used some of the mini mid level to re sign Pablo Prigioni, who was considered a higher priority

  21. Macrolides, tetracyclines, and newer fluoroquinolones have been found to have the highest efficacy against mycoplasma infections

  22. We reveal that cyclin E2 overexpression is a marker for resistance to fulvestrant both in vitro and in patient samples

  23. The mortality associated with severe metabolic alkalosis is substantial; a mortality rate of 45 in patients with an arterial blood pH of 7 Enoxaparin is in a class of medications called low molecular weight heparins

  24. Sometimes broken bones or the problems caused by broken bones are very serious phenytoin will decrease the level or effect of estropipate by P glycoprotein MDR1 efflux transporter

  25. In all probability, a combination of baroreceptor resetting and vascular remodeling occurred, and both mechanisms contributed to the fall in MAP after CBU

  26. 28, among the1709 high risk postmenopausal women who received anastrozole to prevent breast cancer IBIS II study 4 reported dry eye vs

  27. These symptoms can happen in any pregnancy, so they are not definitive signs of twins 2019 Apr 14; 11 7 2151 2176

  28. Not knowing his sperm issues and that not being my specialty I don t know how appropriate Clomid is for him

  29. Cosco TD, Doyle F, Ward M et al 2012 Latent structure of the Hospital Anxiety And Depression Scale a 10 year systematic review

  30. found similar seroprotection rates to seasonal influenza vaccination between 22 patients treated with multi kinase inhibitors sunitinib or sorafenib and 11 healthy controls 20

网站地图